224

SQL Server Stored Procedure with C#.Net

Share on TwitterShare on TumblrSubmit to StumbleUponSave on DeliciousDigg This

 

I will show you about how to insert the data into data table from user interface page using Stored Procedures and view the data in GridView.

UI page is like this:


 

Designing code (.aspx) and Code-behind page (.aspx.cs) are available in Download Link:

After inserting all the fields click on the “Save” button. The GridView will show all the records.

 

Now I will tell you the process. After click in “Save” button, then the Button Click Event Fires.

 

  protected void Button1_Click(object sender, EventArgs e)

    {

        if (Session[“update”].ToString() == ViewState[“update”].ToString())

        {

            InsertingtheData();

            Clearall();

Session[“update”] = Server.UrlEncode(System.DateTime.Now.ToString());

        }

        first(1);

    }

    public void InsertingtheData()

    {

//creating a variable and insert the field values into it.

        int employee_id= Convert.ToInt32(txtEmployeeId.Text);

        ————-

        ————

//Sql Stuff

SqlConnection sqlconn;

SqlCommand sqlcomm;

 

sqlconn = new SqlConnection(ConfigurationManager.AppSettings[“Sql_server”]);

sqlconn.Open();

sqlcomm = new SqlCommand();

sqlcomm.Connection = sqlconn;

 

//Here I am definied command type is Stored Procedure.

sqlcomm.CommandType = CommandType.StoredProcedure;

 

//Here I mentioned the Stored Procedure Name.

sqlcomm.CommandText = “EMP_MASTER_TESTING_I”;

 

 

//Here I fix the variable values to Stored Procedure Parameters. You can easily understand if you can see the Stored Procedure Code.

sqlcomm.Parameters.Add(new SqlParameter(“@emp_id”, SqlDbType.Int)).Value = employee_id;

sqlcomm.Parameters.Add(new SqlParameter(“@emp_desg_id”, SqlDbType.Int)).Value = employee_designation_id;

sqlcomm.Parameters.Add(new SqlParameter(“@emp_dob”,SqlDbType.DateTime)).Value = employee_dob;

                              

sqlcomm.ExecuteNonQuery();

sqlcomm.Dispose();

sqlconn.Close();

    }

 

//Clear all fields values like this.

public void Clearall()

    {

        txtBankaccNo.Text = “”;

        txtBankname.Text = “”;

    }

———————-

//This is the Stored Procedure Code.AvailableàDownLoad Link

 

ALTER PROCEDURE dbo.EMP_MASTER_TESTING_I

 

      (

            @emp_id         int,

            @emp_desg_id      int,

            @emp_dob        Datetime,

            @emp_doj        Datetime,

            @emp_fname      varchar(100),

            @emp_lname      varchar(100),

            @emp_gender     varchar(1),

            @emp_p_street   varchar(50),

            @emp_p_city     varchar(50),

            @emp_p_state      varchar(50),

            @emp_p_country  varchar(50),

            @emp_p_pin      Varchar(50),

            @emp_phone      varchar(50),

            @emp_loc_id     int,

            @emp_bank_name  varchar(20),

            @emp_bank_acno  varchar(20),

            @pan_no             varchar(20),

            @emp_off_email  varchar(50),       

            @emp_dor        Datetime,

            @created_on         Datetime,

            @created_by     int

            @status           bit,

            @updated_on     int

      )

 

AS

      Begin

            Insert into

                              EMP_MASTER_TESTING

                              (

                                    EMP_ID,

                                    EMP_DESG_ID,

                                    EMP_DOB,

                                    EMP_DOJ,

                                    EMP_FNAME,

                                    EMP_LNAME,

                                    EMP_GENDER,

                                –EMP_P_STREET,

                                    EMP_P_CITY,

                                    EMP_P_STATE,

                                    EMP_P_COUNTRY,

                                    EMP_P_PIN,

                                    EMP_Phone,

                                    EMP_LOC_ID,

                                    EMP_BANK_NAME,

                                    EMP_BANK_ACNO,

                                    PAN_NO,

                                    EMP_OFF_EMAIL,

                                    EMP_DOR,   

                                    CREATED_ON,                  

                                    CREATED_BY

                              )

                        Values

                              (

                                    @emp_id,

                                    @emp_desg_id,

                                    @emp_dob,

                                    @emp_doj,

                                    @emp_fname,

                                    @emp_lname,

                                    @emp_gender,

                                @emp_p_street,

                                    @emp_p_city,

                                    @emp_p_state,

                                    @emp_p_country,

                                    @emp_p_pin,

                                    @emp_phone,

                                    @emp_loc_id,

                                    @emp_bank_name,

                                    @emp_bank_acno,

                                    @pan_no,

                                    @emp_off_email,

                                    @emp_dor,  

                                    convert(Datetime, getdate(), 103),                         

                                    @created_by

                              )

      End

      RETURN
 

After that first () method calls. GridView will show all the columns.

 

   public void first(int a)

    {

        if (a == 0)

        {

            tblInsertPage.Visible = true;

            tblgv.Visible = false;

        }

        if (a == 1)

        {

            tblgv.Visible = true;

            tblInsertPage.Visible = false;

        }

    }

 

When page Refresh occurs the values are again inserted into the database. So to avoid that I wrote these lines in Page load event, Button Click event and PreRender event.

 

//In Page_Load Event

if (Session[“update”]==null)

{

Session[“update”] = Server.UrlEncode(System.DateTime.Now.ToString());

}

//In Button Click Event

if (Session[“update”].ToString() == ViewState[“update”].ToString())

{

InsertingtheData();

Clearall();

Session[“update”] = Server.UrlEncode(System.DateTime.Now.ToString());

}

 //In PreRender Event

    private void Page_PreRender(object sender, EventArgs e)

    {

            ViewState[“update”] = Session[“update”];

    }


 

Before you write a Stored Procedure you have the Data table that you are using in the SP. So I wrote an insert command. Insert command values are Stored Procedure input parameters. And Run the Stored Procedure.

 

I wrote a single command in stored procedure, but you can write as many commands as you like.  We can write conditional statements like If, while etc and looping statements like for-loop and switch case etc.

 

We can see GridView like this.

 


 

prajapat