ADODB connection vs SqlConnection in C# use cases

Question

I am trying to convert an old project (originally written in VB6) to C#. I came across the following snippet of code (roughly) :

Dim  sSql     As String
Dim adoRs     As ADODB.Recordset

sSql = "EXEC " & PROC_GETEPORTPARAMETERVALUES & " " & iTemplateId & ", " & iViewId & ", " & 
g_sTenantSN

Set adoRs = Nothing

' Execute the query
Set adoRs = m_odbcDVConnection.OpenRecordSet(sSql, App.EXEName, Me.Name, csPROC_NAME)

If (adoRs.RecordCount > 0) Then

Do Until (adoRs.EOF)

'Some conditional statements within this block

Loop

End If

After doing some research online, I came up with the following code conversion for C#. I have used SqlConnection here. Eventhough I found that ADODB is available to use for C# as well. But not sure which one to use.

sSql = "EXEC " + PROC_GETEPORTPARAMETERVALUES + " " + iTemplateId + ", " + iViewId + ", " + 
        GlobalHelper.Tenant;

SqlConnection conn = new SqlConnection(ConfigurationManager.AppSettings["ConnectionString"]);

if (conn.State != ConnectionState.Open)
{
   conn.Open();
}

SqlCommand cmd = new SqlCommand(sSql);
cmd.CommandType = CommandType.Text;
SqlDataReader dr = cmd.ExecuteReader();

while (dr.Read())
{
   // Conditional statements
}

I am not entirely sure if the above is a proper converison is proper. But more importantly, I wanted to know when to use ADODB connection and when to use SqlConnection for C# projects.

From my understanding, in the above case, I think both the process executes a SQL command and reads records.

Consider using Dapper and Dapper Contrib with your new project. SqlConnection is preferred. Avoid string concatenation of your SQL statements; it makes them vulnerable to SQL injection attacks. Dapper has proper parameterization of the input parameters, which will avoid this problem. — Robert Harvey
– Robert Harvey, Commented Nov 18, 2021 at 13:30
Always use a managed SqlClient API instead of unmanaged ADODB in .NET apps. Frameworks like Dapper @RobertHarvey suggested can use that for the low level data access, — Dan Guzman
– Dan Guzman, Commented Nov 18, 2021 at 13:51
VB6 had nothing else than the COM-based ADO(DB). The ADODB you see in C# is the same COM object. But as pointed out already, whenever possible you should use the .NET Framework's SqlConnection (or OleDbConnection). There might be this odd daatbase solution from back in the day for which a ADO provider is available that hasn't been ported to .NET. That's the case when you resort to ADODB in C#. — Hel O'Ween
– Hel O'Ween, Commented Nov 18, 2021 at 14:07
Another use case for ADODB in .NET: when you have a COM-interop dll used by a VB6 app where the VB6 app is requesting data and expecting it as a ADODB.Recordset. — Brian M Stafford
– Brian M Stafford, Commented Nov 18, 2021 at 14:25

Charlieface · Accepted Answer · 2021-11-20 18:37:20Z

ADODB is not normally used in C#. You should always use SqlClient.

You have a number of issues with your converted code:

You are missing using blocks
No need to, nor should you, inject parameters. Pass them properly using SqlParameter
You should also use CommandType.StoredProcedure, and the command text is then just the procedure name. If you wanted to use Text it is the default, no need to set it.
There is no point checking if the connection is not open, of course it isn't as you just created it

using (SqlConnection conn = new SqlConnection(ConfigurationManager.AppSettings["ConnectionString"]))
using (SqlCommand cmd = new SqlCommand(PROC_GETEPORTPARAMETERVALUES))
{
    cmd.CommandType = CommandType.StoredProcedure;
    // Specify correct parameter type, add parameter length for strings
    cmd.Parameters.Add("@iTemplateId", SqlDbType.NOT_SURE_WHAT_YOU_WANT_HERE).Value = iTemplateId;
    cmd.Parameters.Add("@iViewId", SqlDbType.NOT_SURE_WHAT_YOU_WANT_HERE).Value = iViewId;
    conn.Open();
    using (SqlDataReader dr = cmd.ExecuteReader())
    {
        while (dr.Read())
        {
        // Conditional statements
        }
    }
}

Collectives™ on Stack Overflow

ADODB connection vs SqlConnection in C# use cases

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related