StoredProcedure

//SP  with out parameter
//to create SP
CREATE PROCEDURE SP_TestNoParam

AS
BEGIN   
    SELECT * FROM TBL_Details
END   
   
//to drop SP  
 drop procedure SP_TestNoParam
 //to view SP
 sp_helptext SP_TestNoParam
 //to execute SP
 SP_TestNoParam

-------------------
//SP with parameters

CREATE PROCEDURE SP_Test(
                        @P_ID INT                       
                        )
    AS
    BEGIN
        SELECT * FROM TBL_Details WHERE ID=@P_ID
    END 

 SP_Test 2


-----------------------
//insert SP
CREATE PROCEDURE SP_TestInsert(                           
                            @P_Name VARCHAR(100),
                            @P_Age INT
                              )
AS
BEGIN
    DECLARE @P_ID INT
    SET @P_ID=(SELECT ISNULL(MAX(ID),0) FROM TBL_Details) + 1
    INSERT INTO TBL_Details VALUES(@P_ID,@P_Name,@P_Age)
END

//caling SP
SP_TestInsert 'Test123', 33
-----------------------------------
    public void DataConnectionSP(string query,string name,int age)
        {
            SqlConnection con = new SqlConnection();
            con.ConnectionString = "Data Source=.\\SQLEXPRESS;Initial Catalog=TestDB;Integrated Security=True;Pooling=False";
            con.Open();
            SqlCommand com = new SqlCommand();
            com.Connection = con;
            com.CommandText = query;
            com.Parameters.Add(new SqlParameter("@P_Name",name));
            com.Parameters.Add(new SqlParameter("@P_Age",age));
            com.CommandType = CommandType.StoredProcedure;

            com.ExecuteNonQuery();
        }

 -------------------------------

//In grid rowCommand put

string query = "SP_TestInsert";
oGridDL.DataConnectionSP(query,txtName.Text.ToString(),Convert.ToInt32(txtAge.Text));

Grid EmptyRow Fill

<Gridview>
<column>
</column>

 <EmptyDataTemplate>
            <table>
            <tr>
          
            <td><asp:Label ID="Label1" runat="server" Text="Name"></asp:Label></td>
            <td><asp:Label ID="Label2" runat="server" Text="Age"></asp:Label></td>
            <td><asp:Label ID="Label3" runat="server" Text="Place"></asp:Label></td>
            <td><asp:Label ID="Label4" runat="server" Text="Dob"></asp:Label></td>
             <td><asp:Label ID="Label6" runat="server" Text="Add"></asp:Label></td>
            </tr>
            <tr>
            <td><asp:TextBox ID="txtNameE" runat="server"></asp:TextBox></td>
            <td><asp:TextBox ID="txtAgeE" runat="server"></asp:TextBox></td>
            <td><asp:TextBox ID="txtPlaceE" runat="server"></asp:TextBox></td>
            <td><asp:TextBox ID="txtDOBE" runat="server" ></asp:TextBox></td>
            <td><asp:ImageButton runat="server" ID="imgAddE" ImageUrl="Images/add.gif" CommandName="InsertEmpty"/></td>
            </tr>
            </table>
            </EmptyDataTemplate>

</Gridview>

-----------------------------------

aspx.cs


 protected void gvwtest_RowCommand(object sender, GridViewCommandEventArgs e)
        {
            if (e.CommandName == "Insert")
            {
                TextBox txtName = gvwtest.FooterRow.FindControl("txtName") as TextBox;
                TextBox txtAge = gvwtest.FooterRow.FindControl("txtAge") as TextBox;
                TextBox txtPlace = gvwtest.FooterRow.FindControl("txtPlace") as TextBox;
                TextBox txtDOB = gvwtest.FooterRow.FindControl("txtDOB") as TextBox;
              

                string query = "INSERT INTO TBL_UserDetails VALUES((SELECT ISNULL(MAX(Id),0) as maxid FROM TBL_UserDetails)+1,'" + txtName.Text + "','" + txtAge.Text + "','" + txtPlace.Text + "','" + txtDOB.Text + "')";
                DataConnection(query, 0);
                FillUserDetails();
            }
            else if (e.CommandName == "InsertEmpty")
            {
                TextBox txtName = gvwtest.Controls[0].Controls[0].FindControl("txtNameE") as TextBox;
                TextBox txtAge = gvwtest.Controls[0].Controls[0].FindControl("txtAgeE") as TextBox;
                TextBox txtPlace = gvwtest.Controls[0].Controls[0].FindControl("txtPlaceE") as TextBox;
                TextBox txtDOB = gvwtest.Controls[0].Controls[0].FindControl("txtDOBE") as TextBox;

                string query = "INSERT INTO TBL_UserDetails VALUES((SELECT ISNULL(MAX(Id),0) as maxid FROM TBL_UserDetails)+1,'" + txtName.Text + "','" + txtAge.Text + "','" + txtPlace.Text + "','" + txtDOB.Text + "')";
                DataConnection(query, 0);
                FillUserDetails();
            }
        }

DisplayMessage Function in aspx

private void DisplayMessage(string strMsg)
  {
      string strScript = "<script langauge=javascript type=text/javascript> alert('" + strMsg + "')</script>";
      ClientScript.RegisterClientScriptBlock(this.GetType(), "", strScript);
  }