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