none
sqlParameterCollection coding RRS feed

  • Question

  • Hi,

     

    i am recently into C# from vb6. In my current module i have to insert some datas into the sql server database by firing a stored procedure using SqlParameterCollection.

     

    in VB6.0 we were using the code like this

     

     

    Public Function ExecSP(ByVal strName As String, ByRef oParameters As ParameterCollection) As ADODB.Parameters

        On Error GoTo ErrorHandler

        Dim oCommand As ADODB.Command
        Dim lParameterCount As Long
        Dim oParameter As ADODB.Parameter
       
        strInformation = strName
       
        Set oCommand = New ADODB.Command
        lParameterCount = oParameters.Count - 1
        For lParameter = 0 To lParameterCount
            Set oParameter = oParameters.GetItemFromIndex(lParameter)
            Call oCommand.Parameters.Append(oParameter)
            strInformation = strInformation & " " & oParameter.Name & "=" & oParameter.Value
        Next lParameter
       
        oCommand.CommandTimeout = m_tConfigSettings.lCommandTimeout
        oCommand.CommandType = adCmdStoredProc
        oCommand.CommandText = strName
        Set oCommand.ActiveConnection = m_oConnection
       
        Call oCommand.Execute(, , ADODB.ExecuteOptionEnum.adExecuteNoRecords)


        Set oCommand.ActiveConnection = Nothing
        
      
        Set ExecuteStoredProcedure = oCommand.Parameters
       
    End Function

     

     

     

    we call this function using,

     

    Dim oParameters As New ParameterCollection

    Dim oParameter As ADODB.Parameter

     

     

                    Call oParameters.AddByValue("PERCENTAGEID", strRuleID)
                    Call oParameters.AddByValue("COMMERCIALPRODUCTELEMENTID", strCommercialProductElementID)
                    Call oParameters.AddByValue("PRODUCTTYPE", strProductType)
                    Call oParameters.AddByValue("INVESTMENTPERCENTAGE", 0)
                    Call oParameters.AddByValue("DISINVESTMENTPERCENTAGE", 0)
                    Call oParameters.AddByValue("VALUESTRATEGY", 0)
                    Call oParameters.AddByValue("TARGETACCOUNT", 0)
                    Call oParameters.AddByValue("TARGETREMAININGPERCENTAGE", 0) 

                    Call oParameters.AddByValue("OBJECTSTATUS", lngObjectStatus)
                    Call m_oComponentServices.ExecSP("ABPercentage_PercentageElementCreate", oParameters)
                    Set oParameter = Nothing
                    Set oParameters = Nothing
                End If

    Could any one tell me what is the equivalent coding in C#.

     

     

     

     

     

     

    Monday, December 3, 2007 1:28 PM

Answers

  • If you're targeting SQL Server then the following code will get you going.  For other DBs the class names are about the only thing that change:

     

    Code Block

    using(SqlConnection conn = new SqlConnection(...))
    {
       SqlCommand cmd = new SqlCommand("...", conn);

       cmd.CommandType = CommandType.StoredProcedure;
       cmd.Parameters.AddWithValue(parmName, parmValue);

       ...

     

       conn.Open();

       cmd.ExecuteNonQuery();
    };

     

     

    Use ExecuteReader to get a data reader to process any SELECT statement results if you are use to streaming data from the DB:

     

    Code Block

    using(SqlConnection conn = ...)
    {
       ...

       using(SqlDataReader dr = cmd.ExecuteReader())
       {

          if (dr != null)
          {

             //Read a row
             while (dr.Read())

             {

                someType value = dr["someColumn"];
             };
          };
       };
    };

     

     

    Alternatively you can load all the data in at once (not recommended for large result sets) similar to a recordset in ADO using this code:

     

    Code Block

    using(SqlConnection conn = new ...)
    {

       ...

       DataAdapter da = new DataAdapter(cmd);

       DataSet ds = new DataSet();

       da.Fill(ds);
    };

     

     

    Finally, if your command returns a single value (such as an integer) then you can use ExecuteScalar to get the single result value back.  Refer to MSDN for examples of all these methods and guidelines for when you should use each one.

     

    Michael Taylor - 12/3/07

    http://p3net.mvps.org

     

    Monday, December 3, 2007 2:05 PM