title: How to call stored procedure dynamically with 3-tier architecture?
body: <p>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.</p>
  
  <p>I made different functions for every single operation, like see the code below.</p>
  
  <pre><code>#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
  </code></pre>
  
  <p>There are two function first function is having 2 parametrs and the second one is having 3, but rest of the code is SAME.</p>
  
  <p>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.</p>
  
  <p>for that I did like below</p>
  
  <pre><code>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();
  
  }
  
  }
  </code></pre>
  
  <p>But I am not getting that how to pass <code>Proc_Name</code> and parameters in this code. and how to operate in businesslogic layer and in UI ?</p>
  
  <p>please let me know.</p>