Chapter 2 Building Tables of Data- ADO.NET 4

Chapter 2 Building Tables of Data- ADO.NET 4

After completing this chapter, you will be able to:

■ Understand the ADO.NET classes used to create tables
■ Create strongly typed columns within a table
■ Indicate the primary key for a table
■ Design a table graphically or in code

The focus of all data in ADO.NET is the table—or more correctly, the DataTable. This class, located at System.Data.DataTable, defines a single table in which strongly typed column definitions and runtime data rows appear. By itself, a DataTable isn’t very interesting; it’s just a memory-based repository for data. It becomes useful only when you start employing ADO.NET and standard .NET Framework methods and tools to process the data stored in each table and data row.

Note:- Some of the exercises in this chapter use the same sample project, a tool that exposes the structure of a DataTable. Although you can run the application after each exercise, the expected results for the full application might not appear until you complete all related exercises in the chapter.

Implementing Tables

As with everything else in .NET, tables in ADO.NET exist as instantiated objects. Whether
hand-written by you, produced by dragging and dropping items in the development environment,
or generated by one of the Visual Studio tools, the ADO.NET code you include in
your application exists to create and manage DataTable objects and other related objects.

Logical and Physical Table Implementations

ADO.NET’s DataTable object represents a logical implementation of a table of data. When
you visualize the data values within the table, you have an image of a spreadsheet-like table, with distinct cells for each text, numeric, date/time, or other type of value.

The physical implementation of a DataTable object is somewhat different. Instead of one
large grid layout, ADO.NET maintains tabular data as a collection of collections. Each
DataTable object contains a collection of ordered rows, each existing as an instance of a
DataRow object. Each row contains its own collection of items that holds the row’s (and ultimately the table’s) actual data values. A set of column definitions exists separately from the actual column values, although the definitions influence the values. Figure 2-1 shows the difference between the logical and physical structures of a data table.

The DataTable Class

The three main classes that make up a data table are DataTable, DataColumn, and DataRow. As expected, these classes define a table, its columns, and its data rows, respectively. The main discussion for the DataRow class appears in Chapter 3, “Storing Data in Memory.”

To define a table, create a new DataTable object, optionally supplying a table name.

C#

System.Data.DataTable unnamedTable = new System.Data.DataTable();

System.Data.DataTable namedTable = new System.Data.DataTable("Customer");

Visual Basic

Dim unnamedTable As New System.Data.DataTable()

Dim namedTable As New System.Data.DataTable("Customer")

After you create a DataTable, you can modify its TableName property and other relevant

properties as needed.

Note:- Both Visual Basic and C# include features that let you use namespace elements as if

they were globally named elements. Visual Basic accomplishes this on a file-by-file basis with the Imports keyword; C# includes the Using keyword for the same purpose. Visual Basic also includes a project-specific setting that automatically applies an Imports-like rule to each indicated namespace. (To use this feature, modify the items in the Imported Namespaces list on the References panel of Project Properties.) From this point forward, all code will assume that the System.Data and System.Data.SqlClient namespaces have been globalized in this way.

The DataTable class implements several useful events that it fires whenever data is added

to, removed from, or modified in the table. Table 2-1 shows the data-related events you can

plug into your code.

Creating a DataTable: C#

1. Open the “Chapter 2 CSharp” project from the installed samples folder. The project

includes two Windows.Forms classes: Switchboard and TableDetails.

2. Open the source code view for the Switchboard form. Locate the GetNoColumnTable

function. Add the following statement to that function to create a new DataTable:

return new DataTable("BoringTable");

3. Open the source code view for the TableDetails form. Locate the TableDetails_Load

routine. Just below the comment, “Show the table name,” add the following statement

to access the TableName property of the DataTable:

this.TableName.Text = ActiveTable.TableName;

4. Run the application. When the switchboard appears, click the Show Table with No
Columns button. The TableDetails form opens with a structural view of the columnless

table.

Adding Data Columns

Creating a DataTable instance is an essential first step in using ADO.NET, but a table that
contains no columns is rarely useful. The next task in table building is in populating the
DataTable object’s Columns collection with one or more DataColumn instances that represent the table’s columnar values.

Database Normalization

Before rushing into the task of adding columns, it is important to think about the
nature of the columns being added. If you are creating a standalone table that won’t
interact with other DataTable objects or some of the more abstract data tools that work
with ADO.NET, it is fine to throw any columns you need into the table. However, if you
are trying to replicate the types of table interactions frequently found in traditional relational
databases, you should ensure that your tables are optimized to take advantage
of key ADO.NET features.

Normalization is the process of crafting tables that ensure data integrity and take
advantage of the processing power of relational database systems. E.F. Codd, inventor
of the relational database model, proposed normalization as a method of eliminating
data anomalies that infect data during certain types of insert, update, and delete operations.
A discussion of normalization is beyond the scope of this book. If you’re not
familiar with the normalization process or the various “normal forms,” a few moments

spent reading about this essential craft will help support your data management needs.




The DataColumn class, found in the same System.Data namespace as the DataTable class,
defines a single column in your table’s schema. DataColumn objects can be created as
independent instances for inclusion in a DataTable object’s Columns collection, one for each
column in the table. At a useful minimum, you must provide at least the name and data type

of each column. The following code block creates a table with a single long-integer field:



C#

DataTable customer = new DataTable("Customer");
DataColumn keyField = new DataColumn("ID", typeof(long));
customer.Columns.Add(keyField);

Visual Basic

Dim customer As New DataTable("Customer")
Dim keyField As New DataColumn("ID", GetType(Long))

customer.Columns.Add(keyField)


The following System types are officially supported by DataColumn instances:

■Boolean
■Byte
■Char
■DateTime
■Decimal
■Double
■Int16
■Int32
■Int64
■SByte
■Single
■String
■TimeSpan
■UInt16
■UInt32
■UInt64
■Arrays of Byte (although there are some limitations)


You can also use the equivalent Visual Basic and C# data types.


Note:- You can use any other data types as the column type, but ADO.NET will place limitations—sometimes significant limitations—on a column if you don’t use one of the supported types. Also, nonsupported types are likely to be incompatible with the available data types found in any connected database. See the Visual Studio 2010 online documentation entry for “DataColumn.DataType Property” for full details on these and other limitations.


The DataTable object’s Columns collection includes an Add overload that simplifies the creation of columns. In this syntax, you pass the standard DataColumn arguments directly to the Add method.

C#

DataTable customer = new DataTable("Customer");
customer.Columns.Add("ID", typeof(long));
customer.Columns.Add("FullName", typeof(string));
customer.Columns.Add("LastOrderDate", typeof(DateTime));

Visual Basic

Dim customer As New DataTable("Customer")
customer.Columns.Add("ID", GetType(Long))
customer.Columns.Add("FullName", GetType(String))

customer.Columns.Add("LastOrderDate", GetType(Date))

The DataColumn class includes several useful properties that let you customize each column to suit your processing needs. Many of these properties enable features that parallel those common in relational database tables. Table 2-2 documents these helpful properties.

Table 2-2 Useful Properties in the DataColumn Class
Property	Description
AllowDBNull	A Boolean value that indicates whether database-style NULL values
	are permitted in this column in the actual data rows.
	A database-style NULL value is not the same as the Visual Basic
	Nothing value, nor is it equal to the null value in C#. Instead, they are
	more akin to the nullable data types available in both languages—
	but still not exactly the same.
	The .NET Framework includes a System.DBNull class that you can use
	to test for NULL values in ADO.NET fields. Use this object’s Value.
	Equals method to test a value for equivalence to DBNull.
	C#
	if (DBNull.Value.Equals(fieldValue))...
	Visual Basic
	If (DBNull.Value.Equals(fieldValue)) Then...
	Visual Basic also includes an intrinsic function, IsDBNull, that provides
	similar functionality.
	Visual Basic
	If (IsDBNull(fieldValue)) Then...
	By default, all ADO.NET fields allow NULL values.
AutoIncrement, AutoIncrementSeed, AutoIncrementStep	These three properties control the auto-increment functionality of a column. When enabled, new rows added to a DataTable automatically generate new values for auto-increment columns, with these new values based on the rules established by the AutoIncrementSeed and AutoIncrementStep properties. By default, the AutoIncrement property is set to False, which disables the functionality.
Caption	Provides a place to store a user-friendly title or description for the column. If unassigned, this property returns the value of the ColumnName property. Not all database platforms support the idea of a column caption. When connecting an ADO.NET DataTable instance to a database, this property might or might not be supported.
ColumnName	This is the name of the column, which is typically assigned through the DataColumn class’s constructor.
DataType	This property identifies the data type of the column, which is also normally assigned through the DataColumn class’s constructor. After data has been added to a DataTable, the data type for each column is set and can’t be modified.
DateTimeMode	For columns that track date and time information, this property defines the rules for storing the time and its related time-zone information.
DefaultValue	Any column in a DataTable can include a default value. This value is assigned to the relevant column any time a new row is created. You can replace the default value in a specific data row as needed.
MaxLength	For columns that store text data, this property indicates the maximum length of the text. By default, the maximum length is set to -1, which indicates no maximum length.
ReadOnly	Read-only columns cannot be modified in any data row that has already been added to a table. By default, columns can be updated as needed.
Unique	This Boolean property, when set to True, establishes a “unique value” constraint on the field. No two rows within the table will be allowed to have the same value. Also, NULL values aren’t allowed in columns marked as unique.

In addition to these column-level properties, the containing DataTable class includes a

PrimaryKey property that lets you indicate the column (or multiple columns) that make up

the table’s primary key. This property hosts an array of DataColumn objects taken directly

from the Columns collection of the same DataTable.

C#

DataTable customer = new DataTable("Customer");
customer.Columns.Add("ID", typeof(long));
customer.Columns.Add("FullName", typeof(string));
// ----- Use ID for the primary key.
customer.PrimaryKey = new DataColumn[] {customer.Columns["ID"]};

Visual Basic

Dim customer As New DataTable("Customer")
customer.Columns.Add("ID", GetType(Long))
customer.Columns.Add("FullName", GetType(String)
' ----- Use ID for the primary key.
customer.PrimaryKey = {customer.Columns("ID")}

Note:- The table-level PrimaryKey setting, the column-specific Unique property, and other similar limitations on the table’s data are known as constraints. ADO.NET includes a Constraint class that works in tandem with these property settings. Constraints are discussed in Chapter 5, “Bringing Related Data Together.”


Although each DataColumn added to the Columns collection of a DataTable defines the
columns that appear in a table, they don’t hold any actual row data. The individual columnspecific data values for each row are added through the DataTable object’s Rows collection. Chapter 3 contains details on adding data to a table.