Connecting a .NET Datawindow to SQLServer

You may have noticed during the install of .NET DataWindow that there are no native drivers for SQL server. Sybase expects you to use OleDB to make the connection.

In case anyone is having trouble here is how to do it.

First you create the OleDBConnection object and pass that to an AdoTransaction Object. The AdoTransaction Object wraps up an ADO.Net users connection and Transaction objects so they can be used by the Datawindow.

Your code would look like this:

'Create a new OleDB Connection Object, Populate the ConnectionString Property and open the connection
Dim theConnection As New System.Data.OleDb.OleDbConnection
theConnection.ConnectionString = "Server=localhost;Provider=SQLOLEDB;Initial Catalog=Northwind;User ID=sa;Password=sa;"
theConnection.Open()

'Create a new AdoTransaction Object the constructor takes the connection object you created
Dim ADOTrans = New Sybase.DataWindow.AdoTransaction(theConnection)
'Binds the ADO.NET connection to the database interaction layer so that the AdoTransaction object can be used by a DataWindow
ADOTrans.BindConnection()

'Tell the DataWindow what Transaction Object to use
Me.DataWindowControl.SetTransaction(ADOTrans)