Understanding DataTables
Understanding DataTablesA DataTable is a memory-resident representation of a database table in a DataSet . In the following sections, you will learn how to work with the properties and methods of a DataTable . For example, you will learn how to retrieve key and schema information for a DataTable , create relationships between multiple DataTables in a DataSet , and update the rows in a DataTable . Creating DataTables ProgrammaticallyTypically, you create a DataTable from an existing database table. However, you also have the option of programmatically creating a DataTable and adding it to a DataSet . You might want to do so, for example, if you want to create a purely memory-resident shopping cart and take advantage of a DataTable 's rich set of properties and methods when working with the shopping cart. A DataTable has a collection of rows, represented by its Rows property, and a collection of columns , represented by its Columns property. You can build a DataTable programmatically and populate it with records by adding DataColumns to the columns collection and adding DataRows to the rows collection. In Listing 12.5, for example, a new DataTable is created with four columns: ProductID , ProductName , ProductCategory , and ProductPrice . Next , two rows are added to the DataTable . The contents of the DataTable are then displayed. Listing 12.5 CreateDataTable.aspx<%@ Import Namespace="System.Data" %> <% Dim dtblCart As DataTable Dim dcolColumn As DataColumn Dim drowItem As DataRow ' Create DataTable dtblCart = New DataTable( "Cart" ) ' Create Columns dcolColumn = New DataColumn( "ProductID", GetType( Int32 ) ) dtblCart.Columns.Add( dcolColumn ) dcolColumn = New DataColumn( "ProductName", GetType( String ) ) dtblCart.Columns.Add( dcolColumn ) dcolColumn = New DataColumn( "ProductCategory", GetType( String ) ) dtblCart.Columns.Add( dcolColumn ) dcolColumn = New DataColumn( "ProductPrice", GetType( Decimal ) ) dtblCart.Columns.Add( dcolColumn ) ' Add Rows drowItem = dtblCart.NewRow() drowItem( "ProductID" ) = 12 drowItem( "ProductName" ) = "Anchor Steam" drowItem( "ProductCategory" ) = "Beer" drowItem( "ProductPrice" ) = 7.90 dtblCart.Rows.Add( drowItem ) drowItem = dtblCart.NewRow() drowItem( "ProductID" ) = 13 drowItem( "ProductName" ) = "Samuel Adams" drowItem( "ProductCategory" ) = "Beer" drowItem( "ProductPrice" ) = 8.67 dtblCart.Rows.Add( drowItem ) ' Display DataTable For each drowItem in dtblCart.Rows For each dcolColumn in dtblCart.Columns Response.Write( drowItem( dcolColumn ) ) Next Next %> The C# version of this code can be found on the CD-ROM. In Listing 12.5, an instance of the DataTable class ”named dtblCart ”is created. Next, four columns are added to the DataTable by adding DataColumns to the DataTable 's columns collection. Each column is created with a name and data type. Next, two rows are added to the DataTable . Calling the DataTable 's NewRow() method returns a new row with the correct column schema for the DataTable . After a value has been assigned to each column, the row is added to the DataTable 's rows collection. Finally, the contents of the DataTable are displayed by looping through the rows and columns collections of the DataTable . The value of each column for each row is displayed. Setting DataTable PropertiesYou can set the following four properties of a DataTable :
If you want to perform case-sensitive comparisons in a DataTable , you can modify the DataTable 's CaseSensitive property. For example, you might want to perform a case-sensitive comparison of a password that a user enters into a form against the rows of a DataTable . The MinimumCapacity property indicates the initial number of rows to create for the DataTable . If you are copying a large database table into a DataSet , you can improve the performance of the operation by assigning a higher value to this property. Finally, the PrimaryKey property enables you to specify one or more primary keys for a DataTable . Creating a primary key for a table is useful, for example, when you're defining relationships between DataTables in a DataSet . The page in Listing 12.6 illustrates how to use the CaseSensitive , MinimumCapacity , and PrimaryKey properties. Listing 12.6 DataTableProperties.aspx<%@ Import Namespace="System.Data" %> <Script Runat="Server"> Sub Page_Load Dim dtblPasswords As DataTable Dim dcolColumn As DataColumn Dim arrColumns( 1 ) As DataColumn Dim drowRow As DataRow Dim dvwPasswords As DataView Dim intCounter As Integer ' Create the DataTable dtblPasswords = New DataTable( "passwords" ) dtblPasswords.MinimumCapacity = 200 dtblPasswords.CaseSensitive = True ' Add the Password Column as a Primary Key dcolColumn = New DataColumn( "password", GetType( String ) ) dtblPasswords.Columns.Add( dcolColumn ) arrColumns( 0 ) = dcolColumn dtblPasswords.PrimaryKey = arrColumns ' Add some rows For intCounter = 1 to 100 drowRow = dtblPasswords.NewRow() drowRow( "password" ) = "SECRET" & intCounter.ToString() dtblPasswords.Rows.Add( drowRow ) drowRow = dtblPasswords.NewRow() drowRow( "password" ) = "secret" & intCounter.ToString() dtblPasswords.Rows.Add( drowRow ) Next ' Bind DataTable to DataGrid dvwPasswords = dtblPasswords.DefaultView dvwPasswords.RowFilter = "password='secret1'" dgrdPasswords.DataSource = dvwPasswords dgrdPasswords.DataBind() End Sub </Script> <html> <head><title>DataTableProperties.aspx</title></head> <body> <asp:DataGrid ID="dgrdPasswords" Runat="Server" /> </body> </html> The C# version of this code can be found on the CD-ROM. In the Page_Load subroutine contained in Listing 12.6, a DataTable is created with the name dtblPasswords . This DataTable is case sensitive and has a minimum capacity of 200 rows. Next, a single column named password is added to the DataTable . The password column is marked as the primary key column for the DataTable . The process of specifying a primary key column can be a little confusing. Because you might need to specify multiple columns for a table's primary key (a first and last name), you must assign an array of columns instead of a single column to the PrimaryKey property. In Listing 12.6, an array of DataColumns named arrColumns is assigned to the PrimaryKey property. After the DataTable has been created, 200 rows are added to the table. For each iteration of the counter loop, uppercase and lowercase versions of the same password are added. So the passwords SECRET1 , secret1 , SECRET2 , secret2 , and so on are added to the DataTable . Finally, a DataView filters out any row that does not contain the password secret1 . (You will learn about DataViews later in this chapter in the section titled "Understanding DataViews"). Because the DataTable is case sensitive, only the row that contains the password secret1 , not the row that contains SECRET1 , is retrieved and displayed. Setting Column Properties in a DataTableA DataColumn supports several useful properties. You can use these properties both when populating a DataSet with existing tables and when creating DataTables programmatically. Following is a list of some of the more useful DataColumn properties:
You examine how to use several of these properties in the following sections. Assigning Default Values to ColumnsYou can use the DefaultValue property to assign a default value to a DataColumn . For example, in the page in Listing 12.7, a default value is assigned to the Price column in the Title database table (see Figure 12.2). Listing 12.7 DefaultValue.aspx<%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.SqlClient" %> <Script Runat="Server"> Sub Page_Load Dim dstTitles As DataSet Dim conPubs As SqlConnection Dim dadTitles As SqlDataAdapter Dim dtblTitles As DataTable Dim dcolPrice As DataColumn Dim drowTitle As DataRow ' Grab Titles Table dstTitles = New DataSet() conPubs = New SqlConnection( "Server=localhost;Database=Pubs;UID=sa;PWD=secret" ) dadTitles = New SqlDataAdapter( "Select Title, Price From Titles", conPubs ) dadTitles.Fill( dstTitles, "Titles" ) ' Set Default Value dtblTitles = dstTitles.Tables( "Titles" ) dcolPrice = dtblTitles.Columns( "Price" ) dcolPrice.DefaultValue = 34.67 ' Add New Row drowTitle = dtblTitles.NewRow() drowTitle( "Title" ) = "ASP.NET Unleashed" dtblTitles.Rows.Add( drowTitle ) ' Bind to DataGrid dgrdTitles.DataSource = dstTitles dgrdTitles.DataBind() End Sub </Script> <html> <head><title>DefaultValue.aspx</title></head> <body> <asp:DataGrid ID="dgrdTitles" Runat="Server" /> </body> </html> The C# version of this code can be found on the CD-ROM. Figure 12.2. Assigning a default value. When a new row is added to the DataTable in Listing 12.7, and a value for the Price column is not supplied, the column assumes a default value of 34.67 . Making Columns UniqueYou can specify a unique column in a DataTable in two ways: You can use the Unique property of the DataColumn class, or you can create a unique constraint. You can indicate that a column should contain only unique values by using the Unique property like this: dcolColumn = myDataSet.Tables( "Titles" ).Columns( "Price" ) dcolColumn.Unique = True If you want to add a unique constraint to a table, you need to add the constraint to the Constraints collection of the DataTable class. In Listing 12.8, a unique constraint is added to the ProductName column of the Products database table. Listing 12.8 UniqueConstraint.aspx<%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.SqlClient" %> <Script Runat="Server"> Sub Page_Load Dim dstProducts As DataSet Dim conNorthwind As SqlConnection Dim dadProducts As SqlDataAdapter Dim dtblProducts As DataTable Dim objConstraint As UniqueConstraint ' Grab Products Table dstProducts = New DataSet() conNorthwind = New SqlConnection( "Server=localhost;Database=Northwind;UID=sa;PWD=secret" ) dadProducts = New SqlDataAdapter( "Select * from Products", conNorthwind ) dadProducts.Fill( dstProducts, "Products" ) ' Add Unique Constraint dtblProducts = dstProducts.Tables( "Products" ) objConstraint = New UniqueConstraint( dtblProducts.Columns( "ProductName" ) ) dtblProducts.Constraints.Add( objConstraint ) ' Bind to DataGrid dgrdProducts.DataSource = dstProducts dgrdProducts.DataBind() End Sub </Script> <html> <head><title>UniqueConstraint.aspx</title></head> <body> <asp:DataGrid ID="dgrdProducts" Runat="Server" /> </body> </html> The C# version of this code can be found on the CD-ROM. If the Products table includes duplicate product names , an error is generated when the unique constraint is added to the table. An error also is generated if you attempt to add a new row with a duplicate value for ProductName. Adding AutoIncrement ColumnsYou can use an AutoIncrement column to uniquely number each row in a DataTable . An AutoIncrement column automatically increments its value whenever a new row is added to a DataTable . By default, an AutoIncrement column starts with the value and increments by 1 for each new row. NOTE You can modify the initial value and change the number that is used to increment an AutoIncrement column by modifying its AutoIncrementSeed and AutoIncrementStep properties. Imagine that you have a SQL Server database table named Products that has an identity column named ProductID . Now, suppose that some records have been deleted in the Products table so that some holes now appear in the values of the identity column. You can fix up the table's identity column when loading the DataTable into a DataSet by creating a new AutoIncrement column to replace the identity column programmatically. The page in Listing 12.9 illustrates how to do so (see Figure 12.3). Listing 12.9 AutoIncrement.aspx<%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.SqlClient" %> <Script Runat="Server"> Sub Page_Load Dim dstProducts As DataSet Dim conNorthwind As SqlConnection Dim dadProducts As SqlDataAdapter Dim dtblProducts As DataTable Dim dcolProductID As DataColumn dstProducts = New DataSet() dtblProducts = New DataTable( "Products" ) dcolProductID = New DataColumn( "ProductID", GetType( Int32 ) ) dcolProductID.AutoIncrement = True dtblProducts.Columns.Add( dcolProductID ) dstProducts.Tables.Add( dtblProducts ) conNorthwind = New SqlConnection( "Server=localhost;Database=Northwind;UID=sa;PWD=secret" ) dadProducts = New SqlDataAdapter( "Select ProductName From Products", conNorthwind ) dadProducts.Fill( dstProducts, "Products" ) dgrdProducts.DataSource = dstProducts dgrdProducts.DataBind() End Sub </Script> <html> <head><title>AutoIncrement.aspx</title></head> <body> <asp:DataGrid ID="dgrdProducts" Runat="Server" /> </body> </html> The C# version of this code can be found on the CD-ROM. Figure 12.3. Creating an AutoIncrement column. The DataTable is created in the Page_Load subroutine in Listing 12.9. First, a Products table is created programmatically, and an AutoIncrement column is added to it. Next, the Products database table is added to the DataSet . At this point, the DataTable has no rows. The next step is to fill the Products database table with the values of the ProductName column from the database table. When the ProductName column is copied into the DataSet , the ProductID column is automatically assigned a unique value for each row. Finally, the DataSet is bound to a DataGrid and the improved Products table is displayed. The original ProductID column is replaced with a new and improved ProductID column that is not missing any values. Creating Calculated and Aggregate ColumnsYou can assign an expression to a DataColumn by using the Expression property. You can use this property to represent calculated columns or aggregate columns. A calculated column enables you to calculate a value based on values from other columns. A common use of calculated columns includes creating a tax or discount column that is calculated from a price column. The page in Listing 12.10, for example, demonstrates how you would create a calculated column named SalePrice (see Figure 12.4). Listing 12.10 CalculatedColumn.aspx<%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.SqlClient" %> <Script Runat="Server"> Sub Page_Load Dim dstProducts As DataSet Dim conNorthwind As SqlConnection Dim dadproducts As SqlDataAdapter Dim dcolSalePrice As DataColumn ' Grab the products table dstProducts = New DataSet() conNorthwind = New SqlConnection( "Server=localhost;UID=sa;PWD=secret;Database=Northwind" ) dadProducts = New SqlDataAdapter( "Select * From Products", conNorthwind ) dadProducts.Fill( dstProducts, "Products" ) ' Add the calculated column dcolSalePrice = New DataColumn( "SalePrice", GetType( Decimal ) ) dcolSalePrice.Expression = "UnitPrice - (UnitPrice * .10)" dstProducts.Tables( "Products" ).Columns.Add( dcolSalePrice ) ' Bind to the DataGrid dgrdProducts.DataSource = dstProducts dgrdProducts.DataBind() End Sub </Script> <html> <head><title>CalculatedColumn.aspx</title></head> <body> <asp:DataGrid ID="dgrdProducts" Runat="Server" /> </body> </html> The C# version of this code can be found on the CD-ROM. Figure 12.4. Creating a calculated column. In Listing 12.10, the Products table from the Northwind database is retrieved into a DataSet . Next, a calculated column, named SalePrice , is added to the Products database table in the DataSet . The SalePrice column reduces the UnitPrice by 10%. You can get pretty fancy with calculated columns. You can build a calculated column using a number of different operators and functions (such as ISNULL , SUBSTRING , LEN , and IFF ). For more information, see the .NET Framework SDK Documentation. You also can use the Expression property for aggregate values. You can use any of the following aggregate functions with calculated columns:
Typically, you use these aggregate functions when you have two tables in a parent/child relationship. For example, you might want to display a category table and, next to each category, display a count of the number of products in the category. You will learn how you can define relationships between tables later in this chapter in the section titled "Defining Relationships between DataTables." However, Listing 12.11 illustrates how you can use aggregate functions with the Categories and Products tables (see Figure 12.5). Listing 12.11 AggregateColumn.aspx<%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.SqlClient" %> <Script Runat="Server"> Sub Page_Load Dim dstNorthwind As DataSet Dim conNorthwind As SqlConnection Dim dadNorthwind As SqlDataAdapter Dim dcolProductCount As DataColumn ' Grab the Categories and Products table dstNorthwind = New DataSet() conNorthwind = New SqlConnection( "Server=localhost;UID=sa;PWD=secret;Database=Northwind" ) dadNorthwind = New SqlDataAdapter( "Select * From Categories", conNorthwind ) conNorthwind.Open() dadNorthwind.Fill( dstNorthwind, "Categories" ) dadNorthwind.SelectCommand = New SqlCommand( "Select * From Products", conNorthwind ) dadNorthwind.Fill( dstNorthwind, "Products" ) conNorthwind.Close() ' Add Parent/Child Relationship dstNorthwind.Relations.Add( _ "catprods", _ dstNorthwind.Tables( "Categories" ).Columns( "CategoryID" ), _ dstNorthwind.Tables( "Products" ).Columns( "CategoryID" ) ) ' Add the aggregate column dcolProductCount = New DataColumn( "ProductCount", GetType( Decimal ) ) dcolProductCount.Expression = "Count( Child.ProductID )" dstNorthwind.Tables( "Categories" ).Columns.Add( dcolProductCount ) ' Bind to the DataGrid dgrdNorthwind.DataSource = dstNorthwind dgrdNorthwind.DataMember = "Categories" dgrdNorthwind.DataBind() End Sub </Script> <html> <head><title>AggregateColumn.aspx</title></head> <body> <asp:DataGrid ID="dgrdNorthwind" Runat="Server" /> </body> </html> The C# version of this code can be found on the CD-ROM. Figure 12.5. Displaying an aggregate value. The Categories and Products tables in Listing 12.11 have a parent/child relationship. An aggregate column is added to the Categories database table that retrieves a count of the number of child products for each category. When the Categories table is bound to the DataGrid , a count of the number of products in each category is also displayed in the DataGrid . Computing Column Values in a DataTableYou can use the Compute method of the DataTable class to compute a value for a column. To do so, you can use any of the aggregate functions discussed in the preceding section, such as Sum() or Avg() . NOTE You can use the Compute method with only one DataTable column at a time. If you need to compute a value based on two or more columns, you should create a calculated column that contains multiple columns and compute the value based on the calculated column. Imagine that you want to display a list of products in a shopping cart, and you want to display the total price of the order. The page in Listing 12.12 illustrates how you can do so by using the Compute method (see Figure 12.6). Listing 12.12 Compute.aspx<%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.SqlClient" %> <Script Runat="Server"> Dim dstProducts As DataSet Sub Page_Load Dim conNorthwind As SqlConnection Dim dadProducts As SqlDataAdapter ' Grab Products Table dstProducts = New DataSet() conNorthwind = New SqlConnection( "Server=localhost;Database=Northwind;UID=sa;PWD=secret" ) dadProducts = New SqlDataAdapter( "Select Top 10 * from Products", conNorthwind ) dadProducts.Fill( dstProducts, "Products" ) ' Bind to DataGrid dgrdProducts.DataSource = dstProducts DataBind() End Sub Function OrderTotal() As String Dim dtblProducts As DataTable Dim intSumTotal As Integer dtblProducts = dstProducts.Tables( "Products" ) intSumTotal = dtblProducts.Compute( "Sum( UnitPrice )", Nothing ) Return intSumTotal.ToString( "c" ) End Function </Script> <html> <head><title>Compute.aspx</title></head> <body> <asp:DataGrid ID="dgrdProducts" Runat="Server" /> <p> <b>Order Total:</b> <asp:Label Text='<%# OrderTotal() %>' Runat="Server" /> </body> </html> The C# version of this code can be found on the CD-ROM. Figure 12.6. Displaying a computed value. The OrderTotal() function is bound to a Label control to display the total price of the order. Within the OrderTotal() function, the DataTable Compute method computes the sum of the values contained in the UnitPrice column. Two parameters are passed to the Compute method: an expression with an aggregate function and the value Nothing . The second parameter of the Compute method restricts the DataRows used with the aggregate function. For example, if you want to compute the total cost of only those products that are in stock, you would use the following statement: inSsumTotal = dtblProducts.Compute( "Sum( UnitPrice )", "UnitsInStock > 0" ) This statement computes a sum for only those rows where the UnitsInStock column has a value greater than . Defining Relationships Between DataTablesIf a DataSet contains multiple DataTables , you can define relationships between the DataTables by using the DataRelation class. The most common type of relationship is a parent/child relationship in which each record in the parent table has one or more corresponding records in the child table. For example, you might have a table of categories and a table of products where each category contains multiple products. To define a relationship between two tables, the two tables must share a common key. For example, both the Categories and Products tables could have a CategoryID column that links the records in the two tables together. A DataSet has a Relations property that represents a collection of DataRelation classes between the DataTables contained in the DataSet . To add a new relationship to a DataSet , you simply need to add a new DataRelation to the collection of DataRelation classes. The page in Listing 12.13, for example, displays the name of each category from the Categories table and displays the name of each product for each category from the Products table (see Figure 12.7). Listing 12.13 DataRelation.aspx<%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.SqlClient" %> <Script Runat="Server"> Sub Page_Load Dim dstNorthwind As DataSet Dim conNorthwind As SqlConnection Dim dadNorthwind As SqlDataAdapter Dim drowParent As DataRow Dim drowChild As DataRow ' Grab the Categories and Products table dstNorthwind = New DataSet() conNorthwind = New SqlConnection( "Server=localhost;UID=sa;PWD=secret;Database=Northwind" ) dadNorthwind = New SqlDataAdapter( "Select * From Categories", conNorthwind ) conNorthwind.Open() dadNorthwind.Fill( dstNorthwind, "Categories" ) dadNorthwind.SelectCommand = New SqlCommand( "Select * From Products", conNorthwind ) dadNorthwind.Fill( dstNorthwind, "Products" ) conNorthwind.Close() ' Add Parent/Child Relationship dstNorthwind.Relations.Add( _ "Categories_Products", _ dstNorthwind.Tables( "Categories" ).Columns( "CategoryID" ), _ dstNorthwind.Tables( "Products" ).Columns( "CategoryID" ) ) ' Display each Category and Child Products For each drowParent in dstNorthwind.Tables( "Categories" ).Rows lblOutput.Text &= "<h3>" & drowParent( "CategoryName" ) & "</h3>" For each drowChild in drowParent.GetChildRows( "Categories_Products" ) lblOutput.Text &= "<li>" & drowChild( "ProductName" ) Next Next End Sub </Script> <html> <head><title>DataRelation.aspx</title></head> <body> <asp:Label ID="lblOutput" Runat="Server" /> </body> </html> The C# version of this code can be found on the CD-ROM. Figure 12.7. Defining table relationships. In Listing 12.13, a DataRelation named Products_Categories is added to the DataSet 's collection of DataRelation objects. Next, a For...Next loop is used to loop through and display each row in the Categories database table. For each row, the GetChildRows() method is called to retrieve an array of child rows for that row. A For...Each loop is used to loop through and display each child product for a category. Retrieving DataTable Schema InformationBy default, when you call the Fill() method to create a DataTable from an existing database table, no key or schema information is retrieved. This means that a DataTable won't represent such information as the primary key of the database table. There are two ways that you can retrieve this information: you can use the FillSchema() method, or you can use the MissingSchemaAction property. The page in Listing 12.14, for example, uses the FillSchema() method to retrieve schema information for the Products database table. Listing 12.14 FillSchema.aspx<%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.SqlClient" %> <Script Runat="Server"> Sub Page_Load Dim dstProducts As DataSet Dim conNorthwind As SqlConnection Dim dadProducts As SqlDataAdapter Dim dtblProducts As DataTable Dim dcolPrimaryKey As DataColumn ' Retrieve Schema Information dstProducts = New DataSet() conNorthwind = New SqlConnection( "Server=localhost;UID=sa;PWD=secret;Database=Northwind" ) dadProducts = New SqlDataAdapter( "Select * From Products", conNorthwind ) dadProducts.FillSchema( dstProducts, SchemaType.Mapped, "Products" ) ' Display Primary Keys dtblProducts = dstProducts.Tables( "Products" ) For each dcolPrimaryKey in dtblProducts.PrimaryKey lblPrimaryKeys.Text &= "<li>" & dcolPrimaryKey.ColumnName Next End Sub </Script> <html> <head><title>FillSchema.aspx</title></head> <body> <asp:Label ID="lblPrimaryKeys" Runat="Server" /> </body> </html> The C# version of this code can be found on the CD-ROM. In Listing 12.14, a connection to the Northwind database is opened. Next, the FillSchema() method of the SqlDataAdapter is called to retrieve schema information on the Products database table. Finally, a For...Each loop is used to display each of the primary keys defined for the Products table. The FillSchema() method modifies several of the properties of a DataTable :
The FillSchema () method does not retrieve any rows from a database table. It just creates an empty table that contains the schema information. If you need to retrieve the schema for a table and data in a table in a single step, then you should use the MissingSchemaAction property. The MissingSchemaAction property can accept any of the following values from the MissingSchemaAction enumeration:
The page in Listing 12.15 illustrates how you can use the MissingSchemaAction property with the Products database table. Listing 12.15 MissingSchemaAction.aspx<%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.SqlClient" %> <Script Runat="Server"> Sub Page_Load Dim dstProducts As DataSet Dim conNorthwind As SqlConnection Dim dadProducts As SqlDataAdapter Dim dtblProducts As DataTable Dim dcolPrimaryKey As DataColumn ' Initialize DataAdapter conNorthwind = New SqlConnection( "Server=localhost;UID=sa;PWD=secret;Database=Northwind" ) dadProducts = New SqlDataAdapter( "Select * From Products", conNorthwind ) ' Set Missing Schema Action Property dadProducts.MissingSchemaAction = MissingSchemaAction.AddWithKey ' Retrieve Products dstProducts = New DataSet() dadProducts.Fill( dstProducts, "Products" ) dgrdProducts.DataSource = dstProducts dgrdProducts.DataBind() ' Display Primary Keys dtblProducts = dstProducts.Tables( "Products" ) For each dcolPrimaryKey in dtblProducts.PrimaryKey lblPrimaryKeys.Text &= "<li>" & dcolPrimaryKey.ColumnName Next End Sub </Script> <html> <head><title>MissingSchemaAction.aspx</title></head> <body> <asp:DataGrid id="dgrdProducts" Runat="Server" /> <p> <asp:Label ID="lblPrimaryKeys" Runat="Server" /> </body> </html> The C# version of this code can be found on the CD-ROM. Since the MissingSchemaAction property is set before calling the Fill() method in Listing 12.15, the new rows are added to the DataTable with all the schema information from the underlying database table. The page displays all the products and a list of the primary key columns. Updating Records in a DataTableUp to this point, you have used DataSets exclusively for retrieving and displaying database data. However, you also can use a DataSet to modify database data. The DataAdapter acts as a bridge between a DataSet and its data source. You've been using the DataAdapter 's Fill() method to copy data from a data source into a DataSet . The opposite operation ”copying changes from a DataSet to a data source ”can be accomplished by using the DataAdapter 's Update() method. The page in Listing 12.16, for example, retrieves the contents of the Titles database table into a DataSet . Next, rows are deleted, updated, and added to the DataTable that represents the Titles table. Finally, the Update method is called, and the changes to the DataTable are copied back to the original Titles database table (see Figure 12.8). Listing 12.16 UpdateDataSet.aspx<%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.SqlClient" %> <Script Runat="Server"> Sub Page_Load Dim dstPubs As DataSet Dim conPubs As SqlConnection Dim dadTitles As SqlDataAdapter Dim dtblTitles As DataTable Dim drowTitle As DataRow Dim objCommandBuilder As New SqlCommandBuilder ' Grab Titles Table dstPubs = New DataSet() conPubs = New SqlConnection( "Server=localhost;Database=Pubs;UID=sa;PWD=secret" ) dadTitles = New SqlDataAdapter( "Select * from Titles", conPubs ) dadTitles.Fill( dstPubs, "Titles" ) dtblTitles = dstPubs.Tables( "Titles" ) ' Display Original Titles Table dgrdOriginalTitles.DataSource = dstPubs dgrdOriginalTitles.DataBind() ' Add a Row drowTitle = dtblTitles.NewRow() drowTitle( "Title_id" ) = "xxxx" drowTitle( "Title" ) = "ASP.NET Unleashed" drowTitle( "Price" ) = 1200.00 drowTitle( "Type" ) = "Mystery" drowTitle( "PubDate" ) = #12/25/1966# dtblTitles.Rows.Add( drowTitle ) ' Delete the First Row dtblTitles.Rows( 0 ).Delete() ' Double the price of the Second Row drowTitle = dtblTitles.Rows( 2 ) drowTitle( "Price" ) *= 2 ' Generate the SQL Commands objCommandBuilder = New SqlCommandBuilder( dadTitles ) ' Update Titles Table dadTitles.Update( dstPubs, "Titles" ) ' Display New Titles Table dgrdNewTitles.DataSource = dstPubs dgrdNewTitles.DataBind() End Sub </Script> <html> <head><title>UpdateDataSet</title></head> <body> <h2>Original Titles Table</h2> <asp:DataGrid id="dgrdOriginalTitles" Runat="Server" /> <h2>New Titles Table</h2> <asp:DataGrid id="dgrdNewTitles" Runat="Server" /> </body> </html> The C# version of this code can be found on the CD-ROM. Figure 12.8. Updating a DataSet . In the Page_Load subroutine in Listing 12.16, the Titles database table is displayed in a DataGrid . Next, a new title is added to the Titles table, the first row of the Titles table is deleted, and the price of the second title is doubled . After these modifications are made, the Update() method is called. Calling the Update() method copies the changes from the DataSet to the underlying database. Finally, the modified Titles table is displayed in a second DataGrid . A DataAdapter has four properties that represent four database commands:
If you add a new row to a DataTable and call the Update method, the SQL statement represented by the InsertCommand property is executed. If you modify a row in a DataTable and call the Update method, the SQL statement represented by the UpdateCommand property is executed. And if you delete a row, the SQL command represented by the DeleteCommand property is executed. If you prefer, you can assign a SQL command to each of these properties yourself. You might want to do so, for example, if you want to use stored procedures to improve the performance of the Update method. In Listing 12.14, the SQL commands used with the Update method were generated automatically by the CommandBuilder class. Right before the Update method is called, notice that the DataAdapter is passed to the CommandBuilder class. Some requirements must be met when you're using the CommandBuilder class to automatically generate commands. First, the DataTable must represent a single database table. Second, it must represent a database table with a primary key or unique column.  |
EAN: 2147483647
Pages: 263