Creating a DataColumn and Adding It to a DataTable

The solution creates, configures, and adds a column to a DataTable using four different techniques:

• Adds a DataColumn to a DataTable and configures the column
• Creates and configures a DataColumn and adds it to a DataTable
• Adds a DataColumn to a DataTable and configures it using the Add() method of the ColumnCollection of the DataTable exposed through the Columns property
• Creates multiple DataColumn objects, configures them, and adds them to the DataTable using the AddRange() method of the ColumnCollection of the DataTable

Code:

 using System;
using System.Data;
namespace CreateDataColumnAddDataTable
{
    class Program
    {
        static void Main(string[] args)
        {
            DataTable dt = new DataTable();

            // Add the column to the DataTable to create
            DataColumn col1 = dt.Columns.Add();
            // Configure the column — integer with a default = 0 that
            // does not allow nulls
            col1.ColumnName= “Column-1”;
            col1.DataType = typeof(int);
            col1.DefaultValue = 0;
            col1.Unique = true;
            col1.AllowDBNull = false;

            // Create and configure the column
            DataColumn col2 = new DataColumn();
            // Configure the column — string with max length = 50
            col2.ColumnName = “Column-2”;
            col2.DataType = typeof(string);
            col2.MaxLength = 50;
            // Add the column to the DataTable
            dt.Columns.Add(col2);

            // Add a column directly using an overload of the Add()
            // method of the DataTable.Columns collection — the column
            // is a string with max length = 50
            dt.Columns.Add(“Column-3”, typeof(string)).MaxLength = 50;

            // Add multiple existing columns to the DataTable
            DataColumn col4 = new DataColumn(“Column-4”);
            // … configure column 4
            DataColumn col5 = new DataColumn(“Column-5”, typeof(int));
            // Add columns 4 and 5 to the DataTable
            dt.Columns.AddRange(new DataColumn[] { col4, col5 });

            // Output the columns in the DataTable to the console
            Console.WriteLine(“DataTable has {0} DataColumns named:”,
                dt.Columns.Count);
            foreach (DataColumn col in dt.Columns)
                Console.WriteLine(“\t{0}”, col.ColumnName);
            Console.WriteLine(“\nPress any key to continue.”);
            Console.ReadKey();
        }
    }
}

The DataColumn constructor has five overloads:

	DataColumn()
	DataColumn(string columnName)
	DataColumn(string columnName, Type dataType)
	DataColumn(string columnName, Type dataType, string expression)
	DataColumn(string columnName, Type dataType, string expression,
	    MappingType mappingType)


ColumnName
 Name of the column to be created.
DataType
 Column data type, from supported members of the Type class.
Expression
 Expression used to create the column.
MappingType
 Specifies how columns are mapped to elements or attributes when transformed to an XML document.

 The MappingType enumeration lets you specify Element, Attribute, SimpleContent, or Hidden.

 

 

DataColumn

DataColumn is a true object, inheriting from the System.Object namespace
A DataColumn is exactly what it sounds like: a column of data.

The DataColumn is the foundation of a DataTable and has very similar properties to a column in a relational database table.

The data in a DataTable is represented in the same manner. So, a DataTable is made up of DataColumns and DataRows.

DataTable contains a collection of DataColumns, and this could be considered the DataTable’s schema, or structure

DataColumn Properties

AllowDBNull

True or False, default is True. Determines whether the column will allow Null values. Null values represent the absence of a value and generally require special handling

AutoIncrement

True or False, default is False. This indicates whether the DataColumn will automatically increment a counter. When this value is True, a numeric value will be placed in this column. If the column is not of a Int16, Int32, or Int64, it will be coerced to Int32. If the DataTable is to be populated by an array, a Null must be placed in the array position corresponding to the

AutoIncrement

column in the DataTable.If an expression is already present when this property is set, an exception of type ArgumentException is thrown.

AutoIncrementSeed

Default is 1. This is the starting value of the first row in the column if the AutoIncrement property is set to True. AutoIncrementStep Default is 1. This is the value that the counter is incremented by for each new row in the DataColumn is the AutoIncrement property is True. Caption Caption for the column. If a caption is not specified, the ColumnName is returned. ColumnMapping Determines the MappingType of the column, which is used during the WriteXML method of the parent DataSet.These are the MappingTypes and their descriptions:

• Attribute XML attribute
• Element XML element
• Hidden Internal structure
• SimpleContent XmlText node

ColumnName

Name of the column in the DataColumnCollection. If a ColumnName is not specified before the column is added to the DataColumnCollection, the DataColumnName is set to the default (Column1, Column2, and so on).

Container

Returns the container of the component (inherited from MarshalByValueComponent). DataType Sets, or returns, the type of data in the column. These types are members of the System.Type class. Throws an exception of type ArgumentException if data is present in the DataColumn when the DataType is set.

DefaultValue

Determines the default value for a new row.

DesignMode

Returns a value indicating whether the component is in design mode (inherited from MarshalByValueComponent).

Expression

Defines an expression used to filter rows or create an aggregate column.

ExtendedProperties

Returns a collection of custom user information.

MaxLength

Defines the maximum length of a text column.

Namespace

Defines or returns the namespace of the DataColumn.

Ordinal

Returns the index or position of the column in the DataColumnCollection collection.

Prefix Defines or returns an XML prefix used to alias the namespace of the DataTable.

ReadOnly

True or False, default is False. Indicates whether the column allows changes once a row has been added to the table.

Site

Returns a reference to the parent. If Null reference or nothing, the DataColumn does not reside in a container (inherited from MarshalByValueComponent).

Table

Returns a reference to the DataTable of which the column belongs.

Unique

True or False, default is false. Determines if the values in each row of the column must be unique.

DataColumn Methods

Dispose

Releases resources used by the component (inherited from MarshalByValueComponent). Overloaded.

Equals

Returns True if two instances of the Object are equal (inherited from Object). Overloaded.

GetHashCode

Hash function useful for hashing algorithms and data structures similar to hash tables (inherited from Object).

GetService

Returns the implementer of iServiceProvider interface (inherited from MarshalByValueComponent).

GetType

Returns the type of the current instance (inherited from Object). ToString Returns the existing column Expression. Overridden.

Connecting to a Microsoft Access Database

 Use the OLE DB .NET data provider.

 The solution creates and opens a connection to a Microsoft Access database using the OLE DB .NET data provider. Information about the connection is displayed.

Code: using System; using System.Data; using System.Data.OleDb; namespace ConnectAccessDatabase { class Program { static void Main(string[] args) { string oledbConnectString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + @"C:\Documents and Settings\bill\My Documents\" + "Northwind 2007.accdb;"; using (OleDbConnection connection =new OleDbConnection (oledbConnectString)) { connection.Open( ); // Output some connection and database information. Console.WriteLine("Connection State:{0}",connection.State); Console.WriteLine("OLE DB Provider:{0}",connection.Provider); Console.WriteLine("Server Version:{0}",connection.ServerVersion); } Console.WriteLine("\nPress any key to continue."); Console.ReadKey( ); } } }

Connecting to SQL Server Using an IP Address

Use the Network Address and Network Library attributes of the connection string.

The solution creates and opens a connection to a SQL Server using its IP address. Information about the SQL Server is displayed from the properties of the SqlConnection object.

using System; using System.Data.SqlClient;   namespace ConnectIPAddressSqlServer {     class Program     {         static void Main(string[] args)         {             string connectString =                 "Network Library=dbmssocn;Network Address=127.0.0.1;" +                 "Integrated security=SSPI;Initial Catalog=AdventureWorks";              using (SqlConnection connection = new SqlConnection connectString))             {                 connection.Open(  );                  // Return some information about the server.                 Console.WriteLine(                     "ConnectionState = {0}\nDataSource = {1}\nServerVersion = {2}",                     connection.State, connection.DataSource,                     connection.ServerVersion);             }              Console.WriteLine("\nPress any key to continue.");             Console.ReadKey(  );         }     } }

ADO.NET Benefits

ADO.NET brings with it a number of benefits, which fall into the following categories:

Interoperability

The ability to communicate across heterogeneous environments.

Scalability

The ability to serve a growing number of clients without degrading system performance.

Productivity

The ability to quickly develop robust data access applications using ADO.NET’s rich and extensible component object model.

Performance

An improvement over previous ADO versions due to the disconnected data model.

Interoperability

All communication involves data exchange, whether the communication between distributed components is through a request/response methodology or a message-based facility. Current distributed systems assume that the components involved in the communication are using the same protocol and data format. This assumption is too restrictive for a client base to expand across an enterprise or for multiple companies. Data-access layers should impose no such restrictions.

In current Microsoft Windows Distributed interNet Applications (DNA) Architecture, application components pass data back and forth as ADO disconnected recordsets. The data-providing components, as well as the data-consuming components, are required to use the Component Object Model (COM). The payload, the actual content we are passing around, is packaged in a data format called Network Data Representation (NDR). These NDR packages are streamed between components.

There are two issues with current Windows DNA systems. The first is the requirement that both ends of the communication pipe have the COM library. The second issue is that it is difficult to set up and manage these communications across firewalls. If your middle-tier components are COM/DCOM-based and you are using them within your intranet, you are in good shape. To put it another way: if all your components use Microsoft technology, you’re fine. With the advent of electronic commerce (e-commerce), however, enterprise applications must interoperate with more than just Microsoft-development shops. ADO must improve for cross-platform components to seamlessly share data, breaking away from the limitations of COM/DCOM.

ADO.NET addresses the common data-exchange limitation by using XML as its payload data format. Since XML is text-based and simple to parse, it’s a good choice for a common, platform-independent, and transportable data format. Furthermore, because XML is nothing more than structured text, employing XML as the data format on top of the HTTP network protocol minimizes firewall-related problems. With ADO and its XML format, the clients do not have to know COM to de-serialize the packaged data. All they need is an XML parser, which is readily available in many flavors on many different platforms. The data producers and consumers need only adhere to the XML schema to exchange data among themselves.

Scalability

In a client/server model, it is typical for a client to acquire and hold onto a connection to the server until all requests are fulfilled. While this solution works fine in small- to medium-scale applications, it is not scalable across a large enterprise. As soon as the number of clients reaches a certain threshold, the server becomes the bottleneck, as database connections eat up network and CPU resources. ADO.NET moves away from the client/server model by promoting the use of disconnected datasets. When a client requests some data, the data is retrieved, it’s transferred to the client, and—as soon as possible—the connection is torn down. Since the connection between the client and the data source is short-lived, this technique allows more clients to request information from the server, thus solving the problem of limited connections.

You might think that setting up and tearing down connections is not a good idea since the cost of establishing a connection is usually high. This is a concern only in the absence of connection pooling. ADO.NET automatically keeps connections to a data source in a pool, so when an application thinks it is tearing down a connection, it’s actually returning it to the resource pool. This allows connections to be reused, avoiding the cost of reconstructing new connections from scratch.

Working with data in this disconnected fashion is not new to ADO programmers. The disconnected recordset was introduced in early versions of ADO. However, in ADO, it is up to the developer to implement this feature, whereas in ADO.NET, data is disconnected by nature.

ADO.NET has enhanced its predecessor by growing out of the client/server model and into the distributed components model. By using disconnected datasets as the paradigm for data exchange, ADO.NET is much more scalable than its predecessors.

Productivity

ADO.NET’s rich data access classes allow developers to boost their productivity. Current ADO developers should have no problems getting up to speed with the object model, because ADO.NET is a natural evolution of ADO. The core functionality remains the same. We still have the connection object, representing the pipeline through which commands are executed.^[2] With ADO.NET, the functionality is factored and distributed to each object in the model—much better than in previous versions of ADO. For example, the connection object is responsible only for connecting to and disconnecting from the data source. In ADO.NET, we can no longer execute a query directly through the connection object. Although some developers might miss this ability, it is a step in the right direction for cohesion of component development.

Along with the familiar connection and command objects, ADO.NET introduces a number of new objects, such as DataSet and DataAdapter. All of these objects are discussed earlier in this chapter.

ADO.NET also boosts developers’ productivity through extensibility. Because ADO.NET framework classes are managed code, developers can inherit and extend these classes to their custom needs. If you prefer not to do this low-level legwork, you can use the Visual Studio. NET data-design environment to generate these classes for you.

Visual Studio .NET is a great Rapid Application Development (RAD) tool for developing applications with ADO.NET. You can have the Component Designer generate ADO.NET typed DataSets. These typed DataSets are extended types, modeled for your data. You don’t have to reference database fields by their names or indices but instead by their names as properties of typed objects. For example, instead of oRow(customerName), you can use oRow.customerName. The generated code is much more readable, when compared to previous Microsoft code generators. In addition, these generated classes are type-safe, thus reducing the chances for errors and allowing compilers and the CLR to verify type usage.

In short, ADO.NET improves developers’ productivity through its rich and extensible framework classes. These features are complemented by the rich toolsets for ADO.NET in Visual Studio .NET, which enable rapid application development.

Performance

Because ADO.NET is mainly about disconnected datasets, the system benefits from improved performance and scalability. The database server is no longer a bottleneck when the number of connection requests goes up. Data Providers in ADO.NET also enable implicit connection pooling, which reduces the time required to open a connection.

Previous marshaling of recordsets required type conversion to make sure that the data types were all COM-based. Since the disconnected dataset is in XML format, there is no need for this type conversion during transport, as opposed to dealing with data in Network Data Representation format.

With the ADO.NET architecture, the data providers can be optimized for better performance by talking directly to the database server. .NET Framework Data Provider for SQL Server is an example of this as we can see later in this chapter.