Insert Data Using Stored Procedure in ASP.NET

Introduction

This tip is based on the article "Insert data into SQL Server database using stored procedure and data access layer", written by CodeProject user Rajendra Patel. Here I have class file, stored procedure and web form. Using these, we can insert data into database table.

Using the Code

Create a one class file which interacts between stored procedure and web form. Here form data are passed to InsertData function through Object which is created in web form code behind file.

//Code Behind file code of web form 
protected void btnSave_Click(object sender, EventArgs e)
    {
        try
        {
            Class1 cs = new Class1();
            object[] o = new object[4];
            o[0] = txtName.Text.Trim();
            o[1] = txtAdd.Text.Trim();
            o[2] = Convert.ToDateTime(txtbdt.Text.Trim());
            o[3] = txtsal.Text.Trim();
            cs.InsertData(o, "InsertInformation");
            Response.Write("<script>alert('Data Inserted Successfully!')</script>");
        }
        catch (Exception ex)
        {
            Response.Write(ex);
        }
    } 
 
// Class File code which interacts both web form and stored Procedure
using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
/// <summary>
/// Summary description for Dal
/// </summary>
public class Dal
{
    SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["ConString"].ConnectionString.ToString());
    SqlDataAdapter da;
    SqlCommand cmd;
    DataSet ds;

    public Dal()
    {
        //
        // TODO: Add constructor logic here
        //
    }public void InsertData(object[] o,string str)
    {
        try
        {
            // Get Length of Object pass from View Page
            int a = o.Length;

            // Create Object to get store procedure parameters
            object[] abc = new object[a];

            // Check Connection Open or Close
            if (con.State == ConnectionState.Closed)
            {
                con.Open();
            }

            // Start Code to get SQL parameter from Stored Procedure

            SqlCommand myCommand = new SqlCommand();
            myCommand.Connection = con;
            myCommand.CommandText = str;
            myCommand.CommandType = CommandType.StoredProcedure;


            SqlCommandBuilder.DeriveParameters(myCommand);

            for (int i = 0; i < myCommand.Parameters.Count - 1; i++)
            {
                abc[i] = myCommand.Parameters[i + 1].ParameterName.ToString();
            }

            // End code to get SQL parameter from Stored Procedure

            // Start Code to Insert data into table using Stored Procedure
            cmd = new SqlCommand(str, con);

            for (int i = 0; i < o.Length; i++)
            {
                SqlParameter sp = new SqlParameter();
                sp.ParameterName = abc[i].ToString();
                sp.Value = o[i];
                cmd.Parameters.Add(sp);
            }

            cmd.CommandType = CommandType.StoredProcedure;
            cmd.ExecuteNonQuery();

            //End Code to Insert data intot table using stored procedure 
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }
} 
 
//Stored Procedure by which we can insert data into database
 ALTER PROCEDURE dbo.InsertInformation
    @Name nvarchar(50),
    @Address nvarchar(max),
    @Bdt date,
    @sal nvarchar(50)
AS
    insert into Information (IName,IAddress,Birthdate,Salary) values(@Name,@Address,@Bdt,@sal)
    RETURN