CODEWITHSUNDEEP
c#sql-servervb6ado
battyman17
battyman17

Reputation: 65

ADODB connection vs SqlConnection in C# use cases

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.

Upvotes: 1

Views: 1227

Answers (1)

Charlieface
Charlieface

Reputation: 72436

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
        }
    }
}

Upvotes: 3

Related Questions