Actually I recently made a web application. I have around 20 store procedures [for insert, update and delete] , around 12 store procedures are having different numbers of procedures.
I made different functions for every single operation, like see the code below.
#region Update Comment
public int Update_Comment(string Comment, string Leave_ID)
{
SqlCommand com = new SqlCommand("Update_Comments", con);
com.CommandType =CommandType.StoredProcedure;
com.Parameters.Add("@Comments",SqlDbType.NVarChar,1000).Value =Comment;
com.Parameters.Add("@Leave_ID", SqlDbType.NVarChar, 50).Value = Leave_ID;
con.Open();
int row = com.ExecuteNonQuery();
con.Dispose();
con.Close();
return row;
}
#endregion
#region Updating Leave after approval
public int Updating_Leave(string User_Id, string Leave_Type, int Leave_Applied)
{
SqlCommand com = new SqlCommand("Update_Leave", con);
com.CommandType =CommandType.StoredProcedure;
com.Parameters.Add("@User_Id",SqlDbType.NVarChar,50).Value = User_Id;
com.Parameters.Add("@Leave_Type", SqlDbType.NVarChar, 50).Value = Leave_Type;
com.Parameters.Add("@Leave_Applied", SqlDbType.Int).Value = Leave_Applied;
con.Open();
int row = com.ExecuteNonQuery();
con.Dispose();
con.Close();
return row;
}
#endregion
There are two function first function is having 2 parametrs and the second one is having 3, but rest of the code is SAME.
So can it be possible, with the help of enum and param we can reduce the no of functions I made. all 12 functions are having different numbers of parameters.
for that I did like below
enum Operation_Type
{
Insert,
Update,
Delete,
Select
}
public void Do_Insert_Update_Delete(string Proc_Name, Operation_Type Type ,int No_Of_Args , params object[] args)
{
if(Operation_Type.Insert ==Type)
{
SqlCommand com =new SqlCommand(Proc_Name,con);
com.CommandType = CommandType.StoredProcedure;
for(int i=1; i== No_Of_Args; i++)
{
com.Parameters.AddWithValue("sss",args);
}
con.Open();
com.ExecuteNonQuery();
con.Close();
con.Dispose();
}
}
But I am not getting that how to pass Proc_Name and parameters in this code. and how to operate in businesslogic layer and in UI ?
please let me know.
