OleDb

[hangman]

Can I use an OleDb Connection to MSSQL?

[diltonm]

I noticed that the DASP KB has these articles?

KB Q10191, KB Q10023

[bruce]

yes.

[b]quote:Originally posted by hangman

Can I use an OleDb Connection to MSSQL?
</blockquote id="quote"></font id="quote">

[diltonm]

I wrote and tested recently and found that OleDB connecting to SQL Server is about twice as slow as using SqlCommand and SqlConnection objects.

As Bruce, said though, yes, you can use OleDb to connect to SQL Server. I have some sample code from the above tests I mentioned if you're interested:

[WebMethod]
public string TestInsertSupportDataIntoSQLServerUsingOLEDB()
{
string sRet = "";

try
{
//DiscountASP.net SQL Server
//string sDBstr = "Data Source=mssqlXXX;Use Encryption for Data=false;Initial Catalog=DB_XXXXX;User ID=foo;Password=foo";
//string sDBstr = "DSN=myDSNXXXX;uid=foo;pwd=foo";

//localhost SQL Server
string sDBstr = "Provider=SQLOLEDB;DSN=localhost;Use Encryption for Data=false;uid=foo;pwd=foo";
string sSQL = "select * from support";

OleDbConnection oCn = new OleDbConnection(sDBstr);
oCn.Open();
OleDbDataAdapter oDA = new OleDbDataAdapter(sSQL,oCn);
DataSet oDS = new DataSet();
oDA.Fill(oDS,"support");
OleDbCommandBuilder oCB = new OleDbCommandBuilder(oDA);

long startTicks = DateTime.Now.Ticks;
int iRecs = 1;
for(int loop=0; loop<iRecs; loop++)
{
DataRow oDR = oDS.Tables["support"].NewRow();
oDR["Contact_FullName"] = "ThisIsATest";
oDS.Tables["support"].Rows.Add(oDR);
oDR = null;
}
oDA.Update(oDS,"support");
long endTicks = DateTime.Now.Ticks;
long msTicks = (endTicks - startTicks) / 10000;

oDS.Dispose();
oCB.Dispose();
oDA.Dispose();
oCn.Dispose();
sRet = "Total execution time: " + msTicks.ToString();
}
catch(Exception sqlEx)
{
_sException = sqlEx.Message;
}

if(_sException.Length > 0)
{
sRet = _sException;
}

return sRet;
}

[bruce]

diltonm,

you are exactly correct.

ASP.net SQL db provider uses tds to connect to SQL. TDS is the underlying protocol for SQL communication, it bypass the data abstraction on the web server (oleDB or ODBC).

Unfortunately, this data provider is only accessible with ASP.net. If you are using ASP, you are stuck w/ oleDB.




[b]quote:Originally posted by diltonm

I wrote and tested recently and found that OleDB connecting to SQL Server is about twice as slow as using SqlCommand and SqlConnection objects.

As Bruce, said though, yes, you can use OleDb to connect to SQL Server. I have some sample code from the above tests I mentioned if you're interested:

[WebMethod]
public string TestInsertSupportDataIntoSQLServerUsingOLEDB()
{
string sRet = "";

try
{
//DiscountASP.net SQL Server
//string sDBstr = "Data Source=mssqlXXX;Use Encryption for Data=false;Initial Catalog=DB_XXXXX;User ID=foo;Password=foo";
//string sDBstr = "DSN=myDSNXXXX;uid=foo;pwd=foo";

//localhost SQL Server
string sDBstr = "Provider=SQLOLEDB;DSN=localhost;Use Encryption for Data=false;uid=foo;pwd=foo";
string sSQL = "select * from support";

OleDbConnection oCn = new OleDbConnection(sDBstr);
oCn.Open();
OleDbDataAdapter oDA = new OleDbDataAdapter(sSQL,oCn);
DataSet oDS = new DataSet();
oDA.Fill(oDS,"support");
OleDbCommandBuilder oCB = new OleDbCommandBuilder(oDA);

long startTicks = DateTime.Now.Ticks;
int iRecs = 1;
for(int loop=0; loop<iRecs; loop++)
{
DataRow oDR = oDS.Tables["support"].NewRow();
oDR["Contact_FullName"] = "ThisIsATest";
oDS.Tables["support"].Rows.Add(oDR);
oDR = null;
}
oDA.Update(oDS,"support");
long endTicks = DateTime.Now.Ticks;
long msTicks = (endTicks - startTicks) / 10000;

oDS.Dispose();
oCB.Dispose();
oDA.Dispose();
oCn.Dispose();
sRet = "Total execution time: " + msTicks.ToString();
}
catch(Exception sqlEx)
{
_sException = sqlEx.Message;
}

if(_sException.Length > 0)
{
sRet = _sException;
}

return sRet;
}

</blockquote id="quote"></font id="quote">