Tuesday 2 April 2013

useful SQL Server Stored Procedure with C#.Net


SQL Server Stored Procedure with C#.Net



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:
UIpage.bmp
 
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(Datetimegetdate(), 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.

Gridv.gif


No comments:

Post a Comment