Access any Stored Procedure with or without parameter

Common Stored Procedure for insert,update delete

Hi! here i am writting a single method by which you can access any stored procedure with and without parameter.

SqlCommandBuilder automatically derive the parameters .
and list of parameters are supplied to stored procedure.

here is the code to execute the stored procedure and return the dataset.


class DataAcess
{
SqlConnection con;
//spName:name of stored procedure
//spParams:parameters required for stored procedure.
public DataSet ExecuteDataSet(string spName, object[] spParams)
{
try
{
SqlCommand sqlcom = GetSqlCommand(spName, spParams);
SqlDataAdapter sqlda = new SqlDataAdapter(sqlcom);
DataSet dsetResult = new DataSet();
sqlda.Fill(dsetResult);
return dsetResult;
}
finally
{
CloseConnection();
}
}

private SqlCommand GetSqlCommand(string spName, object[] spParam)
{
SqlCommand com = new SqlCommand(spName, GetConnection());
com.CommandType = CommandType.StoredProcedure;
//SqlCommandBuilder automatically derive the parameters
SqlCommandBuilder.DeriveParameters(com);
com.Parameters.RemoveAt(0);
if (spParam != null || spParam.Length != 0)
{
for (int index = 0; index < spParam.Length; index++)
{
//value will be set
if (spParam[index] != null)
com.Parameters[index].Value = spParam[index];
else
com.Parameters[index].Value = DBNull.Value;
}
}
return com;
}

//read the connection string from web.config(web) /app.config(window)
private SqlConnection GetConnection()
{
con = new SqlConnection(System.Configuration.ConfigurationSettings.AppSettings["MyConn"]);
con.Open();
return con;
}
private void CloseConnection()
{
con.Close();
}
}



//you have to just call the ExecuteDataSet from your business logic.

private void DisplayMachine(string machineName)
{
DataAcess dataAcess = new DataAcess();
//parameters required for procedure,if no parameter then pass Null
object[] spparam = { machineName };
DataSet ds = dataAcess.ExecuteDataSet("Usp_DisplayMachine",spparam);
}



Regards,
Shital Umare


Comments

No responses found. Be the first to comment...


  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name:
    Email: