Editing Data on a Web Page | Microsoft ADO.NET (Core Reference) (PRO-Developer)

The ODBC .NET Data Provider

Shortly after the release of the .NET Framework, Microsoft released a third .NET data provider: the ODBC .NET Data Provider. This provider is designed to communicate with databases using ODBC drivers.

Because the initial release of the ODBC .NET Data Provider is not part of the .NET Framework, you can't simply create a new project in Visual Studio .NET and start using this new data provider. Once you've downloaded and installed the ODBC .NET Data Provider from the MSDN Web site, you must add a reference to the component in your project in the Add Reference dialog box, as shown in Figure A-1.

Figure A-1

Adding a reference to the ODBC .NET Data Provider

You might have noticed in Figure A-1 that the ODBC .NET Data Provider's namespace differs from that of the OLE DB and SQL Client .NET Data Providers. The objects in the ODBC .NET Data Provider reside in the Microsoft.Data.Odbc namespace, at least as of the initial release of the provider. Future releases of the .NET Framework will likely include this provider. As a result, the namespace might change to System.Data.Odbc in subsequent releases.

The code snippets in this section assume that you've added a reference to the ODBC .NET Data Provider in your project as well as your code module, using the construct for your language of choice (the Imports command for Visual Basic .NET developers and the using command for Visual C# .NET developers).

Connecting to Your Database Using an OdbcConnection

To connect to your database using the ODBC .NET Data Provider, you use the OdbcConnection class. You instantiate an OdbcConnection object, set its ConnectionString property (either explicitly or via the object's constructor), and call the object's Open method.

Here are a few examples of connection strings that you can use to connect to your database using the OdbcConnection object. For more information on how to connect to your database, see the documentation for the OdbcConnection object's ConnectionString property and the documentation for your ODBC driver of choice.

Connecting to a SQL Server database with a username and password:

Driver={SQL Server};Server=(local)\NetSDK;     Database=Northwind;UID=MyUserName;PWD=MyPassword;

Connecting to a SQL Server database using a trusted connection:

Driver={SQL Server};Server=(local)\NetSDK;     Database=Northwind;Trusted_Connection=Yes;

Connecting to a database using an ODBC data source name (DSN):

DSN=MyDataSource;

Connecting to a database using an ODBC file DSN:

FileDSN=MyFileDataSource;

The following code snippet connects to the .NET Framework SDK installation of the MSDE database on the local machine:

Visual Basic .NET

Dim strConn As String strConn = "Driver={SQL Server};Server=(local)\NetSDK;" & _            "Database=Northwind;Trusted_Connection=Yes;" Dim cn As New OdbcConnection(strConn) cn.Open() cn.Close()

Visual C# .NET

string strConn; strConn = "Driver={SQL Server};Server=(local)\\NetSDK;" +            "Database=Northwind;Trusted_Connection=Yes;"; OdbcConnection cn = new OdbcConnection(strConn); cn.Open(); cn.Close();

Working with Parameterized Queries

The ODBC .NET Data Provider supports parameterized queries in the same way that the OLE DB .NET Data Provider does. In your query string, you use the ? parameter marker to denote a parameter, and then you add a corresponding OdbcParameter object to the OdbcCommand object's Parameters collection. The ODBC .NET Data Provider does not support named parameters.

Retrieving the Results of a Query Using an OdbcDataAdapter

If you want to retrieve the results of a query and store them in a DataSet or DataTable object, you use the OdbcDataAdapter object:

Visual Basic .NET

Dim strConn, strSQL As String strConn = "Driver={SQL Server};Server=(local)\NetSDK;" & _            "Database=Northwind;Trusted_Connection=Yes;" strSQL = "SELECT OrderID, CustomerID, OrderDate FROM Orders " & _          "WHERE CustomerID = ?" Dim da As New OdbcDataAdapter(strSQL, strConn) Dim param As OdbcParameter param = da.SelectCommand.Parameters.Add("@CustomerID", OdbcType.NChar, 5) param.Value = "ALFKI" Dim tbl As New DataTable("Orders") da.Fill(tbl)

Visual C# .NET

string strConn, strSQL; strConn = "Driver={SQL Server};Server=(local)\\NetSDK;" +            "Database=Northwind;Trusted_Connection=Yes;"; strSQL = "SELECT OrderID, CustomerID, OrderDate FROM Orders " +          "WHERE CustomerID = ?"; OdbcDataAdapter da = new OdbcDataAdapter(strSQL, strConn); OdbcParameter param; param = da.SelectCommand.Parameters.Add("@CustomerID", OdbcType.NChar, 5); param.Value = "ALFKI"; DataTable tbl = new DataTable("Orders"); da.Fill(tbl);

Examining the Results of a Query Using an OdbcDataReader

The following code uses the same query, but it uses an OdbcCommand and an OdbcDataReader object to retrieve and display the results.

Visual Basic .NET

Dim strConn, strSQL As String strConn = "Driver={SQL Server};Server=(local)\NetSDK;" & _            "Database=Northwind;Trusted_Connection=Yes;" strSQL = "SELECT OrderID, CustomerID, OrderDate FROM Orders " & _          "WHERE CustomerID = ?" Dim cn As New OdbcConnection(strConn) Dim cmd As New OdbcCommand(strSQL, cn) Dim param As OdbcParameter param = cmd.Parameters.Add("@CustomerID", OdbcType.NChar, 5) param.Value = "ALFKI" cn.Open() Dim rdr As OdbcDataReader = 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 = "Driver={SQL Server};Server=(local)\\NetSDK;" +            "Database=Northwind;Trusted_Connection=Yes;"; strSQL = "SELECT OrderID, CustomerID, OrderDate FROM Orders " +          "WHERE CustomerID = ?"; OdbcConnection cn = new OdbcConnection(strConn); OdbcCommand cmd = new OdbcCommand(strSQL, cn); OdbcParameter param; param = cmd.Parameters.Add("@CustomerID", OdbcType.NChar, 5); param.Value = "ALFKI"; cn.Open(); OdbcDataReader 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();

Calling a Stored Procedure

In the initial release of the ODBC .NET Data Provider, the OdbcCommand object does not support the Table or StoredProcedure values from the CommandType enumeration. If you want to call a stored procedure using the ODBC .NET Data Provider, you have to learn the ODBC CALL syntax. Thankfully, the syntax is simple. Here's an example:

{? = CALL MyStoredProc(?, ?, ?)}

You use the keyword CALL before the name of the stored procedure. If you want to supply parameters for the stored procedure call—regardless of whether they're input, output, or both—you use the ? parameter marker. You separate the parameter markers with commas and enclose the list of parameter markers in parentheses. If you want to trap for the return value, preface the CALL keyword with ? =, just as you would to retrieve the return value of a function call in your code. Finally, surround the entire query in curly braces.

The following code snippet demonstrates how to call a parameterized stored procedure using the ODBC .NET Data Provider.

Visual Basic .NET

Dim strConn, strSQL As String strConn = "Driver={SQL Server};Server=(local)\NetSDK;" & _            "Database=Northwind;Trusted_Connection=Yes;" strSQL = "{CALL CustOrdersOrders(?)}" Dim cn As New OdbcConnection(strConn) Dim cmd As New OdbcCommand(strSQL, cn) Dim param As OdbcParameter param = cmd.Parameters.Add("@CustomerID", OdbcType.NChar, 5) param.Value = "ALFKI" cn.Open() Dim rdr As OdbcDataReader = cmd.ExecuteReader() Do While rdr.Read()     Console.WriteLine("OrderID = " & rdr.GetInt32(0))     Console.WriteLine("OrderDate = " & rdr.GetDateTime(1))     Console.WriteLine() Loop rdr.Close() cn.Close()

Visual C# .NET

string strConn, strSQL; strConn = "Driver={SQL Server};Server=(local)\\NetSDK;" +            "Database=Northwind;Trusted_Connection=Yes;"; strSQL = "{CALL CustOrdersOrders(?)}"; OdbcConnection cn = new OdbcConnection(strConn); OdbcCommand cmd = new OdbcCommand(strSQL, cn); OdbcParameter param; param = cmd.Parameters.Add("@CustomerID", OdbcType.NChar, 5); param.Value = "ALFKI"; cn.Open(); OdbcDataReader rdr = cmd.ExecuteReader(); while (rdr.Read()) {     Console.WriteLine("OrderID = " + rdr.GetInt32(0));     Console.WriteLine("OrderDate = " + rdr.GetDateTime(1));     Console.WriteLine(); } rdr.Close(); cn.Close();

Retrieving Database Schema Information

Unfortunately, there is no singular way to retrieve schema information from your database using the ODBC .NET Data Provider, at least not in its initial release. The ODBC .NET Data Provider does not support an equivalent of the OleDbConnection object's GetOleDbSchema method. You can still query the Information Schema views mentioned in the discussion of the SQL Client .NET Data Provider to retrieve schema information from SQL Server and MSDE databases, but these queries are not supported by all database systems. These limitations might be addressed in a future release of the provider.