The Question
How do I connect to an ODBC source through ADO.NET?
The Answer
Microsoft introduced ADO.NET together with the .NET Framework.
This new data access object is built upon the .NET Framework. When you install the
.NET Framework on your machine, two .NET providers will be available to you:
the SQL .NET Data Provider (optimized for accessing MS SQL Server database )
and the OLEDB .NET Data Provider for connections to other databases.
But this is not it all, because Microsoft has another data provider
and that is an ODBC .NET Data Provider which is an add-on component to the .NET
Framework.
This data provider will give you the ability to access native
ODBC drivers. This provider should work with all compliant ODBC drivers, but
Microsoft has on its Web site that this data provider has been tested only with
Microsoft SQL ODBC Driver, Microsoft ODBC Driver for ORACLE, and with Microsoft
JET ODBC Driver.
You can download ODBC .NET Data Provider from:
http://www.microsoft.com/downloads/details.aspx?FamilyId=6CCD8427-1017-4F33-A062-D165078E32B1&displaylang=en
Problem exists when you use this data provider as outlined in
the article:
FIX: ODBC Driver Manager Incorrectly Reports SQL_NO_DATA_FOUND for ANSI Driver (Q319243)
This problem is related to a field that has an empty string
in it.
After you downloaded and installed on your machine the ODBC .NET data provider, you can start building an application that will access data
using ODBC native drivers.
First of all, you must import the Microsoft.Data.Odbc namespace into your
application:
Imports Microsoft.Data.Odbc
To create a connection using the ODBC .NET Data Provider, we
are going to build a simple Windows Application in VB.
Create a new VB project and add a DataGrid control on Form. You can add an one button that will close an application.
Your form should looks like this one:
In the Solution Explorer Window, right click on the Project Name and select the Add Reference option.
In the Add Reference Window, scroll down and find the Microsoft.Data.ODBC.dll and double click on it. This dll will be added to Selected Components window and after that click on OK button.
This way, you added reference to ODBC .NET Data Provider. Next, you will need to write some code. Right click on the Form and select View Code. In the code window enter following code:
Imports Microsoft.Data.Odbc
Public Class Form1
Inherits System.Windows.Forms.Form
#Region " Windows Form Designer generated code "
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Dim strConnectionString As String
strConnectionString = "DRIVER={SQL Server};SERVER=localhost;UID=sa;DATABASE=pubs;"
Dim pConn As New OdbcConnection(strConnectionString)
Dim pInsertQuery As String = "SELECT * FROM Titles"
Dim adapter As New OdbcDataAdapter(pInsertQuery, pConn)
Dim titleDS As DataSet = New DataSet()
adapter.Fill(titleDS, "Titles")
DataGrid1.SetDataBinding(titleDS, "Titles")
DataGrid1.Show()
End Sub
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
End
End Sub
End Class.
|
When you are done with this, hit the F5 key and Visual Studio.NET
will start building the solution and after that will start (run) that solution.
If everything is OK, you should be able to see Titles Table records from the Pubs database from MS SQL database.
Summary
You can still use the ODBC. Microsoft released managed Beta ODBC.NET Data Provider. Maybe soon, we can have the final release.
-
Now, you have at least three Data Providers that you can use in your applications.