How to Use the ODBC. Net managed provider in Visual C #. NET and connection strings

Applicable

This article was previusly published under q310488 for a Microsoft Visual Basic. Net version of this article, see 310985.

This article refers to the following Microsoft. NET Framework class library namespace:

• Microsoft. Data. ODBC

In this task

• Summary
• • Download the ODBC. Net managed provider
  • Create the project
  • Connection string Samples
  • Test the application
  • Troubleshooting
• References

Summary

This step-by-step article describes how to use the ODBC. Net managed provider in Visual C #. net. This article also managed des samples connection strings that you can use to access your data.

The ODBC. NET data provider is an add-on component to the Microsoft.. NET Framework software development kit (SDK ). it provides access to native ODBC drivers the same way that the ole db. NET data provider provides access to native ole db providers. although the ODBC. NET data provider is intended to work with all compliant ODBC drivers, it has only been tested with the following drivers:

• Microsoft SQL ODBC driver
• Microsoft ODBC driver for Oracle
• Microsoft jet odbc driver

Note: If the. NET Framework SDK (which is already ded with Visual Studio. NET) is not installed, the setup for this download fails. As part of the setup for this download,Microsoft. Data. ODBCNamespace is added to the Global Assembly Cache by using the gacutil.exe utility that is written with the. NET Framework SDK.

Back to the top

Download the ODBC. net provider

1. Download the ODBC. Net managed provider from the following Microsoft Web site:

   Http://msdn.microsoft.com/downloads/default.asp? Url =/downloads/sample. asp? Url =/MSDN-FILES/027/001/668/msdncompositedoc. xml

2. The ODBC. NET data provider also requires that you install Microsoft Data Access Components (MDAC) version 2.7 or later. You can download the latest version of MDAC from the following Microsoft Web site:

   Http://msdn.microsoft.com/library/default.asp? Url =/downloads/LIST/dataaccess. asp

3. After you install the ODBC. NET data provider, proceed to the next section to create the project.

Back to the top

Create the project

1. Start Microsoft Visual Studio net.
2. Create a New Visual C # windows application project. form1 is added to the project by default.
3. FromProjectMenu, clickAdd reference.
4. On. NetTab, clickMicrosoft. Data. ODBC. dll. AfterMicrosoft. Data. ODBC. dllAssembly appears in the list of selected components, clickOK.
5. Switch to Code view, and add the following code immediately after the otherUsingStatements:

       using System.Data;    using Microsoft.Data.Odbc;

6. Add fourButtonControls to form1, and label these controlsSQL Server,Jet,OracleAndDSNRespectively.

Back to the top

Connection string Samples

1. Add the following code toSQL ServerButton:
   {
   Odbcconnection CN;
   Odbccommand cmd;
   String mystring;
   
   Mystring = "select * from MERs ";
   
   CN = new odbcconnection ("driver = {SQL Server}; server = mysqlserver; uid = sa;
   Pwd = mypassword; database = northwind ;");
   
   Cmd = new odbccommand (mystring, CN );
   CN. open ();
   
   MessageBox. Show ("connected ");
   
   CN. Close ();
   
   }
   

   Add the following code toJetButton:

2.  
   {
           OdbcConnection cn;
           OdbcCommand cmd;
           string MyString;
   
           MyString="Select * from Titles";
    
           cn= new OdbcConnection("Driver={Microsoft Access Driver (*.mdb)};
               DBQ=D:/Program Files/Microsoft Office/Office10/Samples/Northwind.mdb;UID=;PWD=;");
                 
           cmd=new OdbcCommand(MyString,cn);
           cn.Open();
           MessageBox.Show("Connected");
   
           cn.Close();
        }
        

3. Add the following code toOracleButton:

    
    {
          OdbcConnection cn;
          OdbcCommand cmd;
          string MyString;
   
          MyString="Select * from Customers";
   
          cn= new OdbcConnection("Driver={Microsoft ODBC for Oracle};Server=myOracleServer;
                                  UID=demo;PWD=demo;");
   
          cmd=new OdbcCommand(MyString,cn);
          cn.Open();
   
          MessageBox.Show("Connected");
   
          cn.Close();
        }
                       

4. Add the following code toDSNButton:

    
    {
          OdbcConnection cn;
          OdbcCommand cmd;
          string MyString;
   
          MyString="Select * from Customers";
   
          cn= new OdbcConnection("dsn=myDSN;UID=myUid;PWD=myPwd;");
   
          cmd=new OdbcCommand(MyString,cn);
   
          cn.Open();
          MessageBox.Show("Connected");
   
          cn.Close();
        }             Modify the OdbcConnection strings as appropriate for your 

5. environment.

Back to the top

Test the client application

1. Press the F5 key to compile and run the application.
2. Click each button. You receive a message box, which states that you have successfully connected to your data.

Back to the top

Troubleshooting

If you encounter a problem when you connect to your data source (for example, if you use an incorrect password, user ID, or database name ), you receive the following generic error message unless you trap for a specific error message: An unhandled exception of Type 'Microsoft. data. ODBC. odbcexception 'occurred in Microsoft. data. ODBC. DLL. additional information: System Error

To provide more information about the error and to assist in troubleshooting, you can addTry-catch-finallyBlock to the Code. For example:

 

Try
{
CN. open ();
}
Catch (odbcexception ex)
{
MessageBox. Show (ex. Message); <br/> there shocould be no <br/>
}
Finally
{
CN. Close ();
}

Back to the top references

For more information about. Net managed providers, refer to the. NET developer's center or the following Microsoft Web site:

Inside. Net managed providers
Http://msdn.microsoft.com/library/default.asp? Url =/library/en-US/dndive/html/data010112001.asp

For more information about. net, visit the. NET newsgroups. The Microsoft. Public. DOTNET. Framework. odbcnet newsgroup has been set up for this release. Reference: http://support.microsoft.com/default.aspx? SCID = KB; en-US; q310488