Jesoba.com is launched, Click below image to access it

Jesoba.com is launched, Click below image to access it
Technology and Entertainment Blog

How to Create Store Procedure in SQL Server for Insert, Update, Delete and Search Data in Databse using Perameter.


Title : How to Create Store Procedure in SQL Server for Insert, Update, Delete and Search
          Data in Databse using Perameter.

Article ID : SS0003

Description : This Query will help you to Understand  Create Procedure
                     in SQL Server for Insert, Update, Delete and Search Data
                     using perameter.

                     First Create Table in Database
---------------------------------------------------------
create table emp
(
 Idemp BIGINT Constraint pk Primary Key ,
 EmpName VARCHAR(8),

 EmpContact VARCHAR(10)
)
---------------------------------------------------------
---------------------------------------------------------

USE EMPLOYEE --Database Name in which Procedure Execute. 

CREATE PROCEDURE usp_save_Employee
              @Mode  AS INT = NULL,
              @IdEmp AS BIGINT = NULL,
              @EmpName AS VARCHAR(80) = NULL,
              @EmpContact AS VARCHAR(80) = NULL
AS
If @Mode = 1--For Saving       
   BEGIN
          INSERT INTO CompanyH
                      (
                      IdEmp,   EmpName,  EmpContact
                     
                      )
                      Values
                      (
                      @IdEmp,  @EmpName, @EmpContact
                      )             
   END
IF @Mode = 2--For Updation
   BEGIN
         UPDATE emp SET
                    EmpName = @EmpName, EmpContact = @EmpContact
         Where IdEmp = @IdEmp

   END
IF @Mode = 3--For Deletion
   BEGIN
         Delete From emp Where IdEmp = @IdEmp
   END
IF @Mode = 4--For Searching or Display
   BEGIN
        SELECT EmpName,  EmpContact     
        From emp where IdEmp = @IdEmp
   END   
------------------------------------------------------------------------------------

Call procedure and pass perameter using following code :

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Collections;
using System.Data;
using System.Data.SqlClient;

public partial class Default3 : System.Web.UI.Page
{
    string  sSqlStr;
    SqlConnection con;
    protected void Page_Load(object sender, EventArgs e)
    {
       if (!IsPostBack)
        {

        }
    }
   protected void btnSave_Click(object sender, EventArgs e)
    {
        con = new SqlConnection(@"Data Source=.\SQL2005; Initial Catalog=VSPLHR2011; User ID = sa; 

                                                      Password = n@123");
        con.Open();
        sSqlStr = "usp_save_Employee @Mode = 1,   @IdEmp = " + Convert.ToInt16(txtid.Text)
                                                                       + ", @EmpName     = " + txtempname.Text
                                                                       + ", @EmpContac     = " + txtcontact.Text
        SqlCommand cmd = new SqlCommand(sSqlStr , con);
        cmd.ExecuteNonQuery();
        con.Close();
   }
   protected void btnUpdate_Click(object sender, EventArgs e)
    {
        con = new SqlConnection(@"Data Source=.\SQL2005; Initial Catalog=VSPLHR2011; User ID = sa; 

                                                       Password = n@123");
        con.Open();
        sSqlStr = "usp_save_Employee   @Mode = 2,   @IdEmp = '" + ddl.SelectedValue
                                                                         + "',@EmpName     = " + txtempname.Text
                                                                         + ", @EmpContac     = " + txtcontact.Text
        SqlCommand cmd = new SqlCommand(sSqlStr , con);
        cmd.ExecuteNonQuery();
        con.Close();
   }
protected void btnDelete_Click(object sender, EventArgs e)
    {
        con = new SqlConnection(@"Data Source=.\SQL2005; Initial Catalog=VSPLHR2011; User ID = sa; 

                                                      Password = n@123");
        con.Open();
        sSqlStr = "usp_save_Employee   @Mode = 3,   @IdEmp = '" + ddl.SelectedValue + "' ":;
        SqlCommand cmd = new SqlCommand(sSqlStr , con);
        cmd.ExecuteNonQuery();
        con.Close();
   }
private Void Search()
  {
        con = new SqlConnection(@"Data Source=.\SQL2005; Initial Catalog=VSPLHR2011; User ID = sa; 

                                                      Password =    n@123");
        con.Open();
        sSqlStr = "usp_save_Employee   @Mode = 4, @IdEmp = '" + ddl.SelectedValue + "' ";
        ds = FillDataSetERP(sSqlStr);
        if (ds.Tables[0].Rows.Count > 0)
        {
         
        }
      
  }
protected void ddl_SelectedIndexChanged(object sender, EventArgs e)
    {
       Search();
    }
private DataSet FillDataSetERP(string strQuery)
    {

        DataSet dsFill = new DataSet();
        //sqlcon.CreateCommand.CommandTimeout = 200000;
        SqlDataAdapter daFill = new SqlDataAdapter(strQuery, constr);
        daFill.Fill(dsFill);
        return dsFill;
    }
}

Image/Video :  Select Employee Id and you will get related EmpName and Contact
                         in TextBoxes.
                         Click on Image to see clear pic as slid show :








                          


                                            

Keyword : Store Procedure for insert, update, delete and search data, SQL Common query,
                  procedure with different Mode.

Reference : N/A

No comments:

Google+ Badge

About Author

My photo

I am Senior Server Expert and Professional Blogger.