One function to populate sql command parameters and run stored procedure

Question

In my application I have to execute a lot of stored procedures.

I have to write a lot of code for doing this.

For example :

_oconn.ConnectionString = _connectionString;
_oconn.Open();

SqlCommand ocmd = new SqlCommand();
ocmd.Connection = _oconn;
ocmd.CommandText = "procname";
ocmd.CommandType = CommandType.StoredProcedure;

ocmd.Parameters.Add("@Name", SqlDbType.NVarChar).Value = 'ABS';
ocmd.Parameters.Add("@ID", SqlDbType.Int).Value = 123;

var da = new SqlDataAdapter(ocmd);
da.Fill(Obj);

_oconn.Close();

I write this code for any procedure with different parameters,

Is there any way to write a function which do it for all my procedures?

I have about 30 peocedures with different parameters, I want some generic function to fit all cases.so I wont have to duplicate my code so many times — BrMe
– BrMe, Commented Jul 6, 2015 at 6:50
This is easy to provide, but please try something on your own, then let the suggestions flow in, that will help you much better — Mrinal Kamboj
– Mrinal Kamboj, Commented Jul 6, 2015 at 6:56

sangram parmar · Accepted Answer · 2015-07-10 09:32:15Z

1

You can't write common one function for different Stored Procedure

But I think the best practice is

// this is common function for all datatable
        public DataTable ExecuteDataTable(SqlParameterCollection ObjParams, string StoredProcedureName)
        {
            DataTable Obj = new DataTable();

            try
            {
                SqlConnection _oconn = new SqlConnection();

                _oconn.ConnectionString = _connectionString;
                _oconn.Open();

                SqlCommand ocmd = new SqlCommand();
                ocmd.Connection = _oconn;
                ocmd.CommandText = StoredProcedureName;
                ocmd.CommandType = CommandType.StoredProcedure;

                foreach (SqlParameter item in ObjParams)
                    ocmd.Parameters.Add(item);


                var da = new SqlDataAdapter(ocmd);
                da.Fill(Obj);

                _oconn.Close();
            }
            catch (Exception)
            {
                throw;
            }

            return Obj;
        }


        // You can write every diffrenet Procedure with different functions 
        public DataTable GetStudents()
        {
            SqlParameterCollection objParams = new SqlParameterCollection();
            objParams.Add(new SqlParameter("@Name", "ABS"));
            objParams.Add(new SqlParameter("@ID", 123));
            var StudentsTable = ExecuteDataTable(objParams, "procname");
        }

edited Jul 10, 2015 at 9:32

sangram parmar

8,7862 gold badges26 silver badges48 bronze badges

answered Jul 6, 2015 at 6:56

Anant Dabhi

11.2k3 gold badges33 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Mrinal Kamboj Over a year ago

Why not create a generic code to create parameter collection using Reflection or XML config, that would make things much simpler

marc_s · Accepted Answer · 2016-03-09 06:56:38Z

0

You can create as a method, example:

private DataSet ExecuteSQLSPDS(string strConnectString, string strSchema, string strStoredProcedureName, ArrayList arlParamName, ArrayList arlParamValue, ref string strErrMsg)
{
    SqlConnection cnResult = new SqlConnection(strConnectString);
    SqlDataAdapter daResult = new SqlDataAdapter();
    DataSet ds = new DataSet();

    try
    {
        daResult.SelectCommand = new SqlCommand();
        daResult.SelectCommand.CommandType = CommandType.StoredProcedure;
        daResult.SelectCommand.CommandText = strSchema + "." + strStoredProcedureName;
        daResult.SelectCommand.Connection = cnResult;
        daResult.SelectCommand.CommandTimeout = 0;

        //Form Param
        for (int i = 0; i < arlParamName.Count; i++)
        {
            daResult.SelectCommand.Parameters.Add(new SqlParameter(arlParamName[i].ToString(), arlParamValue[i].ToString()));
        }

        cnResult.Open();
        daResult.Fill(ds);
    }
    catch (Exception e)
    {
        strErrMsg = e.Message;
    }
    finally
    {
        if (cnResult.State.ToString() == System.Data.ConnectionState.Open.ToString())
            cnResult.Close();

        daResult.Dispose();
    }

    return ds;
}

Then call the method:

DataSet ds = ExecuteSQLSPDS(ConnectString, Schema, StoredProcedureName, arlParamName, arlParamValue, ref ErrMsg);

edited Mar 9, 2016 at 6:56

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Jul 6, 2015 at 6:50

Soon Khai

6827 silver badges13 bronze badges

1 Comment

Mrinal Kamboj Over a year ago

This would still lead to lot of repetitive code, there are ways to refactor to minimize all sort of repetition. Especially for creating collection of Parameters, and still using ArrayList :) start using List<T>

Collectives™ on Stack Overflow

One function to populate sql command parameters and run stored procedure

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related