0

How can I pass parameters to a stored procedure dynamically using C#?

For example I have a table called Person which has columns Name, City, Zipcode etc.

So the code is:

cmd.Parameters.Add("@Name", SqlDbType.varchar);
cmd.Parameters.Add("@City", SqlDbType.varchar);
cmd.Parameters.Add("@Zipcode", SqlDbType.varchar);

Now I am adding two more columns, let's say State and Country.

So code will now be:

cmd.Parameters.Add("@Name", SqlDbType.varchar);
cmd.Parameters.Add("@City", SqlDbType.varchar);
cmd.Parameters.Add("@Zipcode", SqlDbType.varchar);
cmd.Parameters.Add("@State", SqlDbType.varchar);
cmd.Parameters.Add("@Country", SqlDbType.varchar);

Is there any better way to do things?

Edited:

after searching online i come up with following solution

behind aspx.cs file:

Dictionary dicPersonHash = new Dictionary();

    dicPersonHash .Add("@Name", txtName.Text);
    dicPersonHash .Add("@City", txtCity.Text);
    dicPersonHash .Add("@Zipcode", txtZipcode.Text);
    dicPersonHash .Add("@State", txtState.Text);
    dicPersonHash .Add("@Country", txtName.Text);
    dicPersonHash.Add("@returnID", null);
    dicPersonHash .Add("mode", "Insert");
    dicPersonHash .Add("spName", "ManagePerson");

and have class called clsDataAccess.cs and has method called ManageData

public int ManageData(Dictionary<string, object> dicDataHash)
    {
        int i = 0;
        string[] excludeKeyHash = { "mode", "spName" };
        try
        {
            cn.Close();
            cn.Open();
            cmd = new SqlCommand(dicDataHash["spName"].ToString(), cn);
            cmd.CommandType = CommandType.StoredProcedure;
            foreach (KeyValuePair<string, object> kvp in dicDataHash)
            {
                if (!excludeKeyHash.Contains(kvp.Key))
                {
                    if (kvp.Key == "@returnID")
                    {
                        cmd.Parameters.Add(kvp.Key, SqlDbType.Int).Direction = ParameterDirection.Output;
                    }
                    else
                    {
                        cmd.Parameters.AddWithValue(kvp.Key, kvp.Value);
                    }
                }
            }
            i = cmd.ExecuteNonQuery();
            if (dicDataHash["mode"] == "Insert")
            {
                i = Convert.ToInt32(cmd.Parameters["@returnID"].Value.ToString());
            }
        }
        catch (Exception ex)
        {
            throw ex;
            i = -1;
        }
        finally
        {
            cn.Close();
        }
        return i;
    }

what about these? is any better way to do these? please give me comment/suggestion on these

user9993
  • 4,711
  • 11
  • 43
  • 103
  • 1
    maybe this could help http://stackoverflow.com/questions/11102358/how-to-pass-an-array-into-a-sql-server-stored-procedure – fubo Sep 07 '15 at 08:56
  • how to dynamic get the parameters is explaind here http://stackoverflow.com/questions/3038364/get-stored-procedure-parameters-by-either-c-sharp-or-sql – Thorarins Sep 07 '15 at 08:56
  • 1
    Do you have to use stored proc's? Maybe take a look at ORM's NHibernate and EF – Mark Sep 07 '15 at 09:28
  • i search online and come up with solution – user2273024 Sep 08 '15 at 07:02

0 Answers0