Understanding DataTables


Understanding DataTables

A 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 Programmatically

Typically, 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 Properties

You can set the following four properties of a DataTable :

  • CaseSensitive ” Determines whether comparisons when searching, sorting, or filtering records are case sensitive (the default value is False )

  • MinimumCapacity ” Specifies the initial number of rows that the DataTable accepts (the default value is 25 )

  • PrimaryKey ” Specifies the array of columns to use as the primary keys for the DataTable

  • TableName ” Specifies the name of the 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 DataTable

A 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:

  • AllowDBNull ” Enables or disables Null values in the column (the default value is True )

  • AutoIncrement ” Creates a column that increments its values automatically

  • AutoIncrementSeed ” A Long value that represents the initial value for an autoincrement column

  • AutoIncrementStep ” A Long value that represents the increment used by an autoincrement column

  • ColumnName ” Specifies the name of the column

  • DataType ” Specifies the .NET data type of the column

  • DefaultValue ” Specifies the default value assigned to the column when no value is specified

  • Expression ” Creates calculated or aggregate columns

  • MaxLength ” Specifies the maximum length of a text column

  • ReadOnly ” A Boolean value that indicates whether the column allows updates

  • Unique ” Requires all values in the column to be unique (the default value is False )

You examine how to use several of these properties in the following sections.

Assigning Default Values to Columns

You 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.

graphics/12fig02.jpg

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 Unique

You 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 Columns

You 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.

graphics/12fig03.jpg

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 Columns

You 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.

graphics/12fig04.jpg

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:

  • Avg ” The average value of a column

  • Count ” A count of the number of rows in a column

  • Max ” The maximum value of a column

  • Min ” The minimum value of a column

  • StDev ” The standard deviation of values in a column

  • Sum ” The sum of values in a column

  • Var ” The variance of values in a column

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.

graphics/12fig05.jpg

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 DataTable

You 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.

graphics/12fig06.jpg

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 DataTables

If 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.

graphics/12fig07.jpg

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 Information

By 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 :

  • AllowDBNull ” Indicates whether a column accepts Null values

  • AutoIncrement ” Indicates whether a column is an autoincrement column

  • Constraints ” Contains a collection of constraints that represent the constraints of the underlying database table

  • MaxLength ” Indicates the maximum number of characters in a text column

  • PrimaryKey ” Contains an array of columns that represents the table's primary key (If a table does not have a primary key, but it has a unique constraint that prevents Null values, the unique columns are used instead)

  • ReadOnly ” Indicates whether a column can be modified

  • Unique ” Indicates whether a column can contain duplicate values

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:

  • Add ” Adds whatever additional columns are necessary to the DataTable when adding new rows (the default value)

  • AddWithKey ” Adds all necessary columns to the DataTable when adding new rows

  • Error ” Raises an error when adding rows that don't match the existing DataTable

  • Ignore ” Ignores extra columns when adding rows that contain columns that are not present in the DataTable

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 DataTable

Up 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 .

graphics/12fig08.jpg

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:

  • SelectCommand

  • InsertCommand

  • UpdateCommand

  • DeleteCommand

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.



ASP.NET Unleashed
ASP.NET 4 Unleashed
ISBN: 0672331128
EAN: 2147483647
Year: 2003
Pages: 263

Similar book on Amazon

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net