Communicating with the Data Source

This section shows you how to communicate with a data source when you have an open connection. Communication with the data source typically takes the form of executing commands and retrieving data, and now with ADO.NET 2.0 you can query schema and metadata information.

Executing Commands

The command is the basic unit of work when communicating with a data source. Commands are used to execute simple SQL statements like the following:

SELECT * From Customers 

Commands are also used to execute stored procedures, update or delete data, and retrieve complex result sets. All commands inherit from the DbCommand class, whether they are for SQL Server, Oracle, or any other ADO.NET data provider.

The sequence of code when working with a DbCommand is as follows:

1.	Create an instance of a DbCommand derivative.
2.	Create command parameters and populate parameter values.
3.	Execute the command.
4.	Handle the output and/or results of the command.

The code in Listing 17.2 illustrates how to execute a SQL statement using parameters against a SQL Server database.

Listing 17.2. Executing a Parameterized SQL Statement Using a SqlCommand

using System; using System.Data; using System.Data.Common; using System.Data.SqlClient; using System.Collections.Generic; using System.Text; namespace Command1 { class Program {   static void Main(string[] args)   {     SqlConnectionStringBuilder cb = new SqlConnectionStringBuilder();     cb.IntegratedSecurity = true;     cb.DataSource = @".\SQLExpress";     cb.InitialCatalog = "TestDB";     SqlConnection conn = new SqlConnection(cb.ConnectionString);     conn.Open();     SqlCommand cmd = conn.CreateCommand();     cmd.CommandText =       "INSERT INTO Users(UserName, FirstName, LastName) " +       "VALUES(@UserName, @FirstName, @LastName)";     cmd.CommandType = CommandType.Text;     cmd.Parameters.Add(       new SqlParameter("@UserName", "Joe2"));     cmd.Parameters.AddWithValue("@FirstName", "Joe2");     cmd.Parameters.AddWithValue("@LastName", "User2");     cmd.ExecuteNonQuery();   } } } 

The preceding code uses two different methods for adding new SqlParameter instances. The first method, Add, takes a SqlParameter instance as an argument. The second method, AddWithValue, is a shortcut that lets you supply the parameter name and an object value. You can also use commands to execute stored procedures, as illustrated in Listing 17.3.

Listing 17.3. Executing a Stored Procedure Using a SqlCommand Object

using System; using System.Data; using System.Data.Common; using System.Data.SqlClient; using System.Collections.Generic; using System.Text; namespace Command2 { class Program { static void Main(string[] args) {    SqlConnectionStringBuilder sb = new SqlConnectionStringBuilder();    sb.IntegratedSecurity = true;    sb.InitialCatalog = "TestDB";    sb.DataSource = @".\SQLExpress";    SqlConnection conn = new SqlConnection(sb.ConnectionString);    conn.Open();    SqlCommand cmd = conn.CreateCommand();    cmd.CommandText = "sp_Create_User";    cmd.CommandType = CommandType.StoredProcedure;    cmd.Parameters.Add(new SqlParameter("@UserName", "Jane"));    cmd.Parameters.Add(new SqlParameter("@FirstName", "Jane"));    cmd.Parameters.Add(new SqlParameter("@LastName", "Doe"));    cmd.ExecuteNonQuery();   } } } 

The preceding code executes a stored procedure named sp_Create_User that contains an INSERT statement that looks similar to the one from the previous listing (17.2). Just as you can supply input parameters to a stored procedure, you can also obtain output parameters when executing stored procedures. This comes in handy when you have stored procedures that perform calculations or provide valuable data in output parameters. The code in Listing 17.4 shows the execution of a stored procedure called sp_Get_UserCount that returns the number of users stored in the database.

Listing 17.4. Using a Stored Procedure with Output Parameters

using System; using System.Data; using System.Data.Common; using System.Data.SqlClient; using System.Collections.Generic; using System.Text; namespace Command3 { class Program { static void Main(string[] args) {   SqlConnectionStringBuilder sb = new SqlConnectionStringBuilder();   sb.IntegratedSecurity = true;   sb.InitialCatalog = "TestDB";   sb.DataSource = @".\SQLExpress";   SqlConnection conn = new SqlConnection(sb.ConnectionString);   conn.Open();   SqlCommand cmd = conn.CreateCommand();   cmd.CommandText = "sp_Query_UserCount";   cmd.CommandType = CommandType.StoredProcedure;   cmd.Parameters.Add(new SqlParameter("@UserCount", SqlDbType.Int));   cmd.Parameters[0].Direction = ParameterDirection.Output;   cmd.ExecuteNonQuery();   Console.WriteLine(     string.Format("There are {0} users in the database.",       cmd.Parameters[0].Value));   Console.ReadLine();   }  } } 

Using DataReaders

So far you've seen how to use commands to execute SQL statements that take input parameters and return output parameters. What you haven't yet seen is how to use commands to obtain multiple rows of data, one of the most common uses for commands.

Result sets are obtained through the use of DataReaders. A data reader is a highly efficient object that traverses data in a forward-only fashion and is optimized for reading results of queries. There are many different kinds of data readers, all of which inherit from the DbDataReader class. The following is a list of the data readers that inherit directly from DbDataReader:

• DataTableReader A forward-only reader that traverses a DataTable.

• OdbcDataReader, OracleDataReader, SqlDataReader, SqlCeDataReader These are all data readers specific to their respective data providers.

To use a data reader, you must use one of the Execute methods of a command that returns a data reader. For example, the SqlCommand class provides the following methods that return various data readers:

• ExecuteReader Executes the command and returns a SqlDataReader instance.

• ExecuteXmlReader Executes the command and returns an XmlReader instance.

The code in Listing 17.5 illustrates the use of a simple data reader. The data reader allows you to access the columns in a result set either ordinarily (by numeric index), or by the field name itself. As you will see in Listing 17.5, there is a GetXXX method for each possible data type, as well as a GetObject if one of the other data types won't suffice.

Listing 17.5. Using a DataReader

using System; using System.Data; using System.Data.SqlClient; using System.Collections.Generic; using System.Text; namespace DataReader { class Program { static void Main(string[] args) {   SqlConnectionStringBuilder scb = new SqlConnectionStringBuilder();   scb.DataSource = @".\SQLExpress";   scb.InitialCatalog = "TestDB";   scb.IntegratedSecurity = true;   SqlConnection conn = new SqlConnection(scb.ConnectionString);   conn.Open();   SqlCommand cmd = conn.CreateCommand();   cmd.CommandText = "SELECT * From Users";   SqlDataReader rdr = cmd.ExecuteReader();   while (rdr.Read())   {     Console.WriteLine(       string.Format(       "User {0}, Full Name {1} {2}",       rdr.GetString(rdr.GetOrdinal("UserName")),       (string)rdr["FirstName"],       rdr.GetString(rdr.GetOrdinal("LastName"))));   }   rdr.Close();   conn.Close();   Console.ReadLine(); } } } 

In the preceding code, you can see that the GetString method will obtain the string in the column indicated by an ordinal position. To determine a named column's ordinal position, you can use the GetOrdinal method. Also, if you want direct object access to the columns of the reader, you can use array-indexing notation to access columns by name or by ordinal value. All DataReaders operate on a single row of data at any given time. This is what makes them so fast and memory efficient. To advance a reader from one record to the next, you can call the Read() method. This will fetch the next row from the data source. If the reader is at the end of the record set, the Read() method will return false. After you have advanced beyond a given row, you cannot go back to that row again. All DataReaders are forward-only and will dispose of all information regarding previous rows after they are advanced.

There are many reasons to use a DataReader in an application. For example, you might need to read through several thousand rows of data in order to arrive at some conclusion in your application. If you were to load all of those rows into a single DataSet at once, it would consume a lot of memory. The DataReader allows your application to work with a single row at a time, providing a fast, forward-only means of reading through large numbers of rows. If the data you're working with is smaller in nature, you can use the overhead of a DataSet without too much worry about impacting the performance of your application.

Using Schema Discovery

Schema Discovery is a new feature of ADO.NET introduced in version 2.0. Schema Discovery adds new functionality to the basic DbConnection class that provides for discovering detailed information about the database connection as well as the metadata stored on the server.

For example, using Schema Discovery, you can now easily obtain a list of all tables in the system, stored procedures, users, functions, and much more depending on what features the particular data provider supports.

Schema Discovery all takes place within the new method GetSchema. If you invoke this method with no arguments, you will receive in return a table that contains a list of all the possible arguments for the GetSchema method. This way, you can interrogate the connection to see what information you can obtain via GetSchema.

When you invoke GetSchema on a SQL 2005 database, you will receive a table that looks similar to the one in Table 17.3.

Table 17.3. Results of Invoking GetSchema() with No Arguments on SQL Server 2005

Collection Name	Number of Restrictions	Number of Identifier Parts
MetaDataCollections	0	0
DataSourceInformation	0	0
DataTypes	0	0
Restrictions	0	0
ReservedWords	0	0
Users	1	1
Databases	1	1
Tables	4	3
Columns	4	4
Views	3	3
ViewColumns	4	4
ProcedureParameters	4	1
Procedures	4	3
ForeignKeys	4	3
IndexColumns	5	4
Indexes	4	3
UserDefinedTypes	2	1

The number of restrictions and identifier parts is useful to an extent, but the really important information is the name of the collection. This string can be passed to GetSchema as an argument to retrieve information.

The NumberOfRestrictions column indicates the number of restrictions that appear in the Restrictions collection for the indicated collection. So, from Table 17.3, you can tell that there are two defined restrictions for the UserDefinedTypes collection, and both of those will appear in the results if you call GetSchema("Restrictions").

The NumberOfIdentifierParts column that you receive when you invoke GetSchema() with no arguments indicates the number of columns in the table that uniquely identify an entity. For example, when you call GetSchema on the Columns collection, you receive quite a bit of information. The first four columns (TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, and COLUMN_NAME) are the four pieces of information that uniquely identify a single column within that table. If you look at Table 17.3, you'll see that for the Columns collection, the number of identifier parts is indeed 4.

Table 17.4 shows all of the columns for each collection when invoking GetSchema. Keep in mind that this varies depending on your data provider. The information in Table 17.4 was obtained from SQL Express 2005.

Table 17.4. Columns Provided for Each Schema Collection Used with GetSchema()

Collection	Columns
MetaDataCollections	CollectionName, NumberOfRestrictions, NumberOfIdentifierParts (this is the default collection).
DataSourceInformation	Columns describing the data source itself, such as DataSourceProductName, DataSourceProductVersion, and many others indicating various behaviors. Contains one row.
DataTypes	TypeName, ProviderDbType, ColumnSize, CreateFormat, CreateParameters, DataType, IsAutoIncrementable, IsBestMatch, IsCaseSensitive, IsNullable, IsSearchable, and many more. One row per data type.
Restrictions	CollectionName, RestrictionName, RestrictionDefault, RestrictionNumber. One row per restrictionindicates a restriction on schema information.
ReservedWords	ReservedWord. One row per reserved word.
Users	uid, user_name, createdate, updatedate. One row per user.
Databases	database_name, dbid, create_date.
Tables	TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE. One row per table; all four columns indicate the uniqueness of a row.
Columns	TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION, COLUMN_DEFAULT, IS_NULLABLE, and so on. The first four columns indicate the uniqueness for a row.
Views	TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, CHECK_OPTION, IS_UPDATABLE.