How to avoid insert duplicate record in sqlserver using asp.net C#

 

Description:-

                    in this example we simply explain that how to check the Record is Exist in Database or not by using Procedure or Restrict the duplicate insertion of records to the database using Store Procedure.

There are several ways to avoid duplicate record insertion in the sqlserver database using various programming languages. But, it doesnt have to be from programs always we can avoid duplicate insertion of records from the database too.

Here I am  explain to avoid duplicate record insertion in the SQL Server database using store procedure, incase the database table does not contains any unique field.

In SQL Server Database we can check the record before insert to the database table.

Here I am using the [hk_procreglogin ] as Table name. [unm] and [pwd] are the two columns in the table and through this store procedure we can insert data to the table after checking if the record exists or not in the hk_procreglogin table.

If the record is already exists then it ignores the insert operation and display proper message user or record is already exists.if record does not exist then it inserts the record in the database and commits the process.

What is Procedure:-

                        Procedure is a set of SQL Statement and that is predefined by the user and it is Compiled only once time when the application is run. Procedure is Define once time and used in multiple time.

This Example is very useful when there is a large number of record and in which we have to Find the particular record so using this we can save your time.

In this example we create a Procedure for searching a Record and Check that the Record is Exist or not in the database.

That is useful when user Login into a Application at that time First we have to check that this person is Registered in our Website then and then further proceed otherwise Display message please Fill the registration Detail First to Login this Site.

Here we Define one procedure that procedure will return user is Exist or not 

ModalPopup Example for Insert,Update,Delete ModalPopup For CRUD Operation

Export Gridview Data to PDF File Transfer Gridview row to PDF



 procedure

 ALTER PROCEDURE dbo.procins1
  
    (
    @name varchar(50)=null,
    @add varchar(50)=null,
    @state varchar(50)=null,
    @city varchar(50)=null,
    @email varchar(50)=null,
    @mobileno varchar(50)=null,
    @gender varchar(50)=null,
    @hobby varchar(50)=null,
    @photo varchar(200)=null,
    @unm varchar(50)=null,
    @pwd varchar(50)=null,
    @cpwd varchar(50)=null,
    @status varchar(50)=null,
    @id int=0 OUTPUT,
    @rid int=0
  
  
    )
  
AS
begin
if(@status ='register')
    begin
  
    insert into hk_procreglogin values(@name,@add,@state,@city,@email,@mobileno,@gender,@hobby,@photo,@unm,@pwd,@cpwd)
    end
  
    else if(@status ='login')
        begin
        (select @rid=id  from hk_procreglogin where unm= @unm and pwd=@pwd)
        set @id=@rid;
            return @id
                
        end
      
  else if(@status='select')
     begin
        (select * from hk_procreglogin where id= @unm)     end
 end

 code.aspx.cs

protected void btnlogin_Click(object sender, EventArgs e)

    {

        SqlConnection conn = new SqlConnection(connStr);

        conn.Open();

        SqlCommand dCmd = new SqlCommand("dbo.procins1", conn);

        dCmd.CommandType = CommandType.StoredProcedure;

        SqlParameter id = dCmd.Parameters.Add("@id",SqlDbType.Int);

        id.Direction = ParameterDirection.ReturnValue;

        dCmd.Parameters.AddWithValue("@unm",txtunm.Text);

        dCmd.Parameters.AddWithValue("@pwd",txtpwd.Text);

        dCmd.Parameters.AddWithValue("@status","login");

        dCmd.ExecuteNonQuery();

        if(Convert.ToInt32(id.Value)>0)

       {

           Session["id"] = id.Value;

            Response.Redirect("profile.aspx");

        }

        else

        {

            Response.Write("error");

        }

        conn.Close();

       

    }