ADO.NET简介

ADO.NET包括以下几个命名空间：

• System.Data.SqlClient— Contains classes for connecting to Microsoft SQL Server version 7.0 or higher

• System.Data.OleDb— Contains classes for connecting to a data source that has an OLE DB provider

• System.Data.Odbc— Contains classes for connecting to a data source that has an ODBC driver

• System.Data.OracleClient— Contains classes for connecting to an Oracle database server

 

NOTE

It is expected that additional data provider-specific namespaces will be released over time. Microsoft has already released a separate set of classes for working with Microsoft SQL Server in the Compact Framework, and a separate set of classes for working with XML generated from SQL Server 2000. For more information on these additional namespaces, see the msdn.microsoft.com Web site.

Oracle has also released their own namespace for working with Oracle databases. You can download the Oracle provider for .NET (ODP.NET) from the Oracle Web site.

 

Why did Microsoft duplicate these classes, creating different versions for different types of databases? By creating separate sets of classes, Microsoft was able to optimize the classes.

For example, the OleDb classes use OLE DB providers to connect to a database. The SQL classes, on the other hand, communicate with Microsoft SQL Server directly on the level of the Tabular Data Stream (TDS) protocol. TDS is the low-level proprietary protocol used by SQL Server to handle client and server communication. By bypassing OLE DB and ODBC and working directly with TDS, you get dramatic performance benefits.

NOTE

You can use the classes from the System.Data.OleDb namespace with Microsoft SQL Server. You might want to do so if you want your ASP.NET page to be compatible with any database. For example, you might want your page to work with both Microsoft SQL Server and Oracle. However, you lose all the speed advantages of the SQL- and Oracle-specific classes if you use the System.Data.OleDb namespace.

 

There are several ways you can create a new parameter and associate it with a Command. For example, the following two statements create and add a new parameter to the SqlCommand object:

cmdSelect.Parameters.Add( "@firstname", "Fred" )
cmdSelect.Parameters.Add( New SqlParameter( "@firstname", "Fred" ) )

These two statements are completely equivalent. Both statements create a new SqlParameter with the name @firstname and the value Fred and add the new parameter to the parameters collection of the SqlCommand object.

Notice that we do not specify the SQL data type of the parameter in the case of either statement. If you don't specify the data type, it is automatically inferred from the value assigned to the parameter. For example, since the value Fred is a String, the SQL data type NVarchar is inferred. In the case of an OleDbParameter, the data type VarWChar would be automatically inferred. 

In some cases, you'll want to explicitly specify the data type of a parameter. For example, you might want to explicitly create a Varchar parameter instead of an NVarchar parameter. To do this, you can use the following statement:

cmdSelect.Parameters.Add( "@lname", SqlDbType.Varchar ).Value = "Johnson"

This statement specifies the SQL data type of the parameter by using a value from the SqlDbType enumeration. The SqlDbType enumeration is located in the System.Data namespace. Each of its values corresponds to a SQL data type.

In the case of an OleDbParameter, you would use a value from the OleDbType enumeration like this:

cmdSelect.Parameters.Add( "@lname", OleDbType.Varchar ).Value = "Johnson"

The OleDbType enumeration can be found in the System.Data.OleDb namespace.

Finally, you can specify the maximum size of a database parameter by using the following statement:

cmdSelect.Parameters.Add( "@lname", SqlDbType.Varchar, 15 ).Value = "Johnson"

This statement creates a parameter named @lname with a column size of 15 characters.

If you don't explicitly specify the maximum size of a parameter, the size is automatically inferred from the value assigned to the parameter.