Paging

The SQL Client .NET Data Provider

The SQL Client .NET Data Provider is designed to provide the fastest possible access to Microsoft SQL Server and Microsoft Desktop Engine (MSDE) databases.

Named Parameters vs. Parameter Markers

The SQL Client .NET Data Provider supports named parameters rather than the ? parameter marker used by OLE DB and ODBC drivers. To build a parameterized query for the SQL Client .NET Data Provider that returns just the orders for a particular customer, you would use the following query:

SELECT OrderID, CustomerID, EmployeeID, OrderDate     FROM Orders WHERE CustomerID = @CustomerID

The OLE DB .NET Data Provider relies on positional parameter markers (?). You would use the following query to return the same data using the OLE DB .NET Data Provider:

SELECT OrderID, CustomerID, EmployeeID, OrderDate     FROM Orders WHERE CustomerID = ?

Why the difference in syntax? The OLE DB .NET Data Provider supports the generic parameter markers that have been used to construct parameterized queries in previous data access technologies (OLE DB and ODBC) to help developers who have experience working with those technologies. OLE DB and ODBC were designed as universal data access technologies. The goal was to write back-end independent code and have the underlying components translate the generic syntax into the database-specific code.

So why does the SQL Client .NET Data Provider rely on named parameters? Because that's what SQL Server actually requires. If you try to run a parameterized query in SQL Server Query Analyzer, you'll run into problems if you try to use ? parameter markers in your query.

When you use a parameterized query to access SQL Server data using the OLE DB .NET Data Provider, the SQL Server OLE DB provider will parse the query and replace the parameter markers with named parameters. The SQL Server OLE DB provider supports the generic standard and converts the query into the format that SQL Server expects. The SQL Client .NET Data Provider is designed to more closely map its features to that of SQL Server in order to get the best possible performance when you're working with a SQL Server database. As a result, the SQL Client .NET Data Provider does not parse your queries to convert the parameter markers to named parameters.

The back-end-specific .NET data providers are designed to deliver the best possible performance when communicating with that database. As a result, you sacrifice some level of portability to gain those performance benefits.

Connecting to a SQL Server Database Using a SqlConnection

The SQL Client .NET Data Provider's connection object is the SqlConnection object. You can use this object to connect to SQL Server and MSDE databases. You set the SqlConnection object's ConnectionString property explicitly or through the object's constructor, and then you call its Open method, as shown in the following code snippet:

Visual Basic .NET

Dim strConn As String strConn = "Data Source=(local)\NetSDK;Initial Catalog=Northwind;" & _           "Trusted_Connection=Yes;" Dim cn As New SqlConnection(strConn) cn.Open() cn.Close()

Visual C# .NET

string strConn; strConn = "Data Source=(local)\\NetSDK;Initial Catalog=Northwind;" +           "Trusted_Connection=Yes;"; SqlConnection cn = new SqlConnection(strConn); cn.Open(); cn.Close();

You might have noticed that the connection string in the previous code snippet looks nearly identical to those we've used when connecting to SQL Server and MSDE databases using the OleDbConnection object. The only difference is that you omit the Provider=... connection string attribute. For more information on the connection string attributes that are available when you use the SqlConnection object, see the MSDN documentation for the object's ConnectionString property.

The SqlConnection object also exposes two properties not available on the OleDbConnection object: the PacketSize and WorkstationId properties. These properties are read-only, but you can set values for each property via the SqlConnection object's ConnectionString property.

Retrieving the Results of a Query Using a SqlDataAdapter

You can retrieve the results of a query and store them in a DataSet or a DataTable by using the SqlDataAdapter object. You use the SqlDataAdapter object just like you use the OleDbDataAdapter object, with one notable exception. If you want to execute a parameterized query, you need to use named parameters, as described earlier in this appendix.

Visual Basic .NET

Dim strConn, strSQL As String strConn = "Data Source=(local)\NetSDK;Initial Catalog=Northwind;" & _           "Trusted_Connection=Yes;" strSQL = "SELECT OrderID, CustomerID, OrderDate FROM Orders " & _          "WHERE CustomerID = @CustomerID" Dim da As New SqlDataAdapter(strSQL, strConn) Dim param As SqlParameter param = da.SelectCommand.Parameters.Add("@CustomerID", SqlDbType.NChar, 5) param.Value = "ALFKI" Dim tbl As New DataTable("Orders") da.Fill(tbl)

Visual C# .NET

string strConn, strSQL; strConn = "Data Source=(local)\\NetSDK;Initial Catalog=Northwind;" +           "Trusted_Connection=Yes;"; strSQL = "SELECT OrderID, CustomerID, OrderDate FROM Orders " +          "WHERE CustomerID = @CustomerID"; SqlDataAdapter da = new SqlDataAdapter(strSQL, strConn); SqlParameter param; param = da.SelectCommand.Parameters.Add("@CustomerID", SqlDbType.NChar, 5); param.Value = "ALFKI"; DataTable tbl = new DataTable("Orders"); da.Fill(tbl);

Using the SqlCommand and SqlDataReader Objects

You can use the SqlCommand object to execute action queries or to retrieve the results of a query using a SqlDataReader object. The following code snippet demonstrates this functionality:

Visual Basic .NET

Dim strConn, strSQL As String strConn = "Data Source=(local)\NetSDK;Initial Catalog=Northwind;" & _           "Trusted_Connection=Yes;" strSQL = "SELECT OrderID, CustomerID, OrderDate FROM Orders " & _          "WHERE CustomerID = @CustomerID" Dim cn As New SqlConnection(strConn) Dim cmd As New SqlCommand(strSQL, cn) Dim param As SqlParameter param = cmd.Parameters.Add("@CustomerID", SqlDbType.NChar, 5) param.Value = "ALFKI" cn.Open() Dim rdr As SqlDataReader = cmd.ExecuteReader() Do While rdr.Read()     Console.WriteLine("OrderID = " & rdr.GetInt32(0))     Console.WriteLine("CustomerID = " & rdr.GetString(1))     Console.WriteLine("OrderDate = " & rdr.GetDateTime(2))     Console.WriteLine() Loop rdr.Close() cn.Close()

Visual C# .NET

string strConn, strSQL; strConn = "Data Source=(local)\\NetSDK;Initial Catalog=Northwind;" +           "Trusted_Connection=Yes;"; strSQL = "SELECT OrderID, CustomerID, OrderDate FROM Orders " +          "WHERE CustomerID = @CustomerID"; SqlConnection cn = new SqlConnection(strConn); SqlCommand cmd = new SqlCommand(strSQL, cn); SqlParameter param; param = cmd.Parameters.Add("@CustomerID", SqlDbType.NChar, 5); param.Value = "ALFKI"; cn.Open(); SqlDataReader rdr = cmd.ExecuteReader(); while (rdr.Read()) {     Console.WriteLine("OrderID = " + rdr.GetInt32(0));     Console.WriteLine("CustomerID = " + rdr.GetString(1));     Console.WriteLine("OrderDate = " + rdr.GetDateTime(2));     Console.WriteLine(); } rdr.Close(); cn.Close();

	The SqlCommand object does not support the Table value from the CommandType enumeration.

As noted in Chapter 12, the SqlCommand object also exposes an ExecuteXmlReader object. You can use this method to retrieve the results of a ...FOR XML query via an XmlReader object.

The GetSql<DataType> Methods and the SqlTypes Namespace

The SqlDataReader object exposes various Get<DataType> methods to return data into the different .NET data types, just as the OleDbDataReader does. But the SqlDataReader object also exposes additional Get<DataType> methods. These methods correspond to the various data types in the System.Data.SqlTypes namespace.

The following snippet of code retrieves data from a row in the Orders table and stores the contents of that row in data types that are part of the SqlTypes namespace: SqlInt32, SqlString, and SqlDateTime.

Visual Basic .NET

'Imports System.Data.SqlTypes Dim strConn, strSQL As String strConn = "Data Source=(local)\NetSDK;Initial Catalog=Northwind;" & _           "Trusted_Connection=Yes;" strSQL = "SELECT OrderID, CustomerID, OrderDate FROM Orders " & _          "WHERE OrderID = 10643" Dim cn As New SqlConnection(strConn) Dim cmd As New SqlCommand(strSQL, cn) Dim rdr As SqlDataReader Dim intOrderID As SqlInt32 Dim strCustomerID As SqlString Dim datOrderDate As SqlDateTime cn.Open() rdr = cmd.ExecuteReader(CommandBehavior.SingleRow) If rdr.Read Then     intOrderID = rdr.GetSqlInt32(0)     strCustomerID = rdr.GetSqlString(1)     datOrderDate = rdr.GetSqlDateTime(2) End If rdr.Close() cn.Close()

Visual C# .NET

//using System.Data.SqlTypes; string strConn, strSQL; strConn = "Data Source=(local)\\NetSDK;Initial Catalog=Northwind;" +           "Trusted_Connection=Yes;"; strSQL = "SELECT OrderID, CustomerID, OrderDate FROM Orders " +          "WHERE OrderID = 10643"; SqlConnection cn = new SqlConnection(strConn); SqlCommand cmd = new SqlCommand(strSQL, cn); SqlDataReader rdr; SqlInt32 intOrderID; SqlString strCustomerID; SqlDateTime datOrderDate; cn.Open(); rdr = cmd.ExecuteReader(CommandBehavior.SingleRow); if (rdr.Read()) {     intOrderID = rdr.GetSqlInt32(0);     strCustomerID = rdr.GetSqlString(1);     datOrderDate = rdr.GetSqlDateTime(2); } rdr.Close(); cn.Close();

Why use these provider-specific data types? There are two main reasons:

• Performance The data types in the SqlTypes namespace help your code run faster because the SQL Client .NET Data Provider uses these data types internally. If you retrieve data into .NET data types using calls such as GetInt32 or GetString, the SQL Client .NET Data Provider must convert the data. Using the provider-specific data types avoids this conversion. I've found that code that uses the SqlTypes namespace generally runs between 10 and 15 percent faster than code that uses the standard .NET data types.

• Simplified code Few developers like dealing with null values. The code snippet that used the standard .NET data types did not include null checks. If the rows contained null values in any of the columns referenced, the code would generate an exception. You can't store a null value in the .NET data types. You can use the IsDBNull method of the DataReader to make sure the column does not contain null values before retrieving that data.

  The provider-specific data types, however, can handle null values. Each class in the SqlTypes namespace exposes an IsNull method. So, you can store the results of your query into these data types without having to check for null data ahead of time. While you should still check for null values in your code, you can perform those checks later. Using the SqlTypes data types simplifies your SqlDataReader call, which means you can retrieve the results of your queries more quickly. This also allows you to close the SqlDataReader and free up your connection again more quickly.

Calling Stored Procedures

You can use the SqlCommand objects to call SQL Server and MSDE stored procedures. The SqlCommand object exposes a CommandType property that you can use to help simplify the code you use to call your stored procedures. You can set the SqlCommand object's CommandText property to the stored procedure name, set the CommandType property to StoredProcedure, and then call the stored procedure, as shown in the following code snippet:

Visual Basic .NET

Dim strConn As String strConn = "Data Source=(local)\NetSDK;Initial Catalog=Northwind;" & _           "Trusted_Connection=Yes;" Dim cn As New SqlConnection(strConn) Dim cmd As New SqlCommand("CustOrdersOrders", cn) cmd.CommandType = CommandType.StoredProcedure Dim param As SqlParameter param = cmd.Parameters.Add("@CustomerID", SqlDbType.NChar, 5) param.Value = "ALFKI" cn.Open() Dim rdr As SqlDataReader = cmd.ExecuteReader() Do While rdr.Read()     Console.WriteLine(rdr("OrderID")) Loop rdr.Close() cn.Close()

Visual C# .NET

string strConn; strConn = "Data Source=(local)\\NetSDK;Initial Catalog=Northwind;" +           "Trusted_Connection=Yes;"; SqlConnection cn = new SqlConnection(strConn); SqlCommand cmd = new SqlCommand("CustOrdersOrders", cn); cmd.CommandType = CommandType.StoredProcedure; SqlParameter param; param = cmd.Parameters.Add("@CustomerID", SqlDbType.NChar, 5); param.Value = "ALFKI"; cn.Open(); SqlDataReader rdr = cmd.ExecuteReader(); while (rdr.Read())     Console.WriteLine(rdr["OrderID"]); rdr.Close(); cn.Close();

If you use SQL Server's tracing tools, you'll discover that the SQL Client .NET Data Provider transforms this information into the following syntax:

EXEC CustOrdersOrders @CustomerID

If you want to get the best possible performance out of your code, you can use this same syntax and leave the CommandType property set to its default value of Text. To retrieve data using this syntax with output parameters, simply add the keyword OUT after the parameter name, as shown here:

EXEC MyStoredProcedure @InputParameter, @OutputParameter OUT

Retrieving Database Schema Information

The OleDbConnection object exposes a GetOleDbSchemaTable method that you can use to retrieve schema information for your database, such as a list of tables or columns. There is no direct equivalent to this feature in the SQL Client .NET Data Provider.

However, SQL Server does allow you to retrieve this data via the Information Schema views. You can execute the following query to retrieve information regarding the various tables on your SQL Server database:

SELECT * FROM INFORMATION_SCHEMA.TABLES

There are various views for retrieving information about tables, columns, stored procedures, constraints, and so forth. I've included some of the more common queries to help you get started.

To retrieve a list of table names:

SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES     WHERE TABLE_TYPE = 'BASE TABLE'

To retrieve a list of view names:

SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES     WHERE TABLE_TYPE = 'VIEW'

To retrieve a list of columns for the tables:

SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH,         NUMERIC_PRECISION, NUMERIC_SCALE     FROM INFORMATION_SCHEMA.COLUMNS      WHERE TABLE_NAME IN (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES                               WHERE TABLE_TYPE = 'BASE TABLE')     ORDER BY TABLE_NAME

To retrieve a list of procedure names:

SELECT SPECIFIC_NAME FROM INFORMATION_SCHEMA.ROUTINES     WHERE ROUTINE_TYPE = 'PROCEDURE'

To retrieve a list of the parameters for those stored procedures:

SELECT SPECIFIC_NAME, PARAMETER_NAME, PARAMETER_MODE, DATA_TYPE,        CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, NUMERIC_SCALE     FROM INFORMATION_SCHEMA.PARAMETERS     WHERE SPECIFIC_NAME IN (SELECT SPECIFIC_NAME                                 FROM INFORMATION_SCHEMA.ROUTINES                                 WHERE ROUTINE_TYPE = 'PROCEDURE')     ORDER BY SPECIFIC_NAME

For more information on using Information Schema views, see SQL Server Books Online.