Friday 21 June 2013

How we can avoid duplicate insertion of record in database in ASP.NET


How we can avoid duplicate insertion of record in database in ASP.NET



Introduction- In this article, i will discuss that how we can avoid duplicate insertion of records in database. This is very helpful article for any .Net developer.

Implementation- create a website , add page named register.aspx. place five textboxes named name_txt, address_txt , contact_txt,username_txt,password_txt and a button named submit_button and a label named lbl_msg.




Database Script- create a table in database named tb_register. Here I am giving you to complete database script of the table tb_register. See below:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tb_register]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[tb_register](
[id] [bigint] IDENTITY(1,1) NOT NULL,
[ename] [varchar](50) NULL,
[eaddress] [varchar](200) NULL,
[econtact] [varchar](50) NULL,
[eusername] [varchar](50) NULL,
[epassword] [varchar](50) NULL
) ON [PRIMARY]
END


Code fo register.aspx.cs Page-

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

public partial class detail : System.Web.UI.Page
{
SqlConnection con = new SqlConnection();
SqlCommand cmd = new SqlCommand();
SqlDataAdapter adp = new SqlDataAdapter();

protected void Page_Load(object sender, EventArgs e)
{

}
protected void submit_button_Click(object sender, EventArgs e)
{


con.ConnectionString = ConfigurationManager.ConnectionStrings["cnn"].ConnectionString;
con.Open();
// here i am fetching name from the database and compare this name with the name that will be
// entered by the user in name_txt textbox
adp = new SqlDataAdapter(@"select ename from tb_register where ename=@ename", con);
cmd.Connection = con;
adp.SelectCommand.Parameters.AddWithValue("@ename", name_txt.Text);
DataSet ds = new DataSet();
adp.Fill(ds);
//here i am comapre name which is stored in the database with the name that will be entered by the user
// if the name that will be enteres by the user and the name which is store in the database are same
//then it goes in the else statement & label print the message the name enter by you is alreay available
//in the database. "please enter any other name"

if (ds.Tables[0].Rows.Count == 0)
{
String compare;
// here i am using try catch statement because if the compared name not available in database
// then there will be no any value in the string vaiable compare and it craetes an error
//there in no any row in the position of o. so i am using try catch statement
try
{
compare = (ds.Tables[0].Rows[0]["ename"]).ToString();
//It will avoid to save the duplicate name using Comparing string function.
// here i am using String.Compare function because suppose there is name "BHARAT" already stored
//in database.
// if u enter "bharat" it will accept. to avoid this drawback i am
//using string.compare method. it compare the strings
// and avoid to save dulpicate record

if (String.Compare(compare.ToUpper(), name_txt.Text.ToUpper()) == 0)
{
cmd.CommandText = @"INSERT INTO tb_register(ename,eaddress,econtact,eusername,epassword)
VALUES(@ename,@eaddress,@econtact,@eusername,@epassword)";
cmd.Connection = con;
cmd.Parameters.AddWithValue("@ename", name_txt.Text);
cmd.Parameters.AddWithValue("@eaddress", address_txt.Text);
cmd.Parameters.AddWithValue("@econtact", contact_txt.Text);
cmd.Parameters.AddWithValue("@eusername", username_txt.Text);
cmd.Parameters.AddWithValue("@epassword", password_txt.Text);
cmd.ExecuteNonQuery();
cmd.Dispose();
con.Close();
clr_rec();
lbl_msg.Text = "Your record sumitted successfully";
}
else
{
lbl_msg.Text = name_txt.Text + " " + " " + "already exists In the database";
clr_rec();
return;
}
}

catch
{
cmd.CommandText = @"INSERT INTO tb_register(ename,eaddress,econtact,eusername,epassword)
VALUES(@ename,@eaddress,@econtact,@eusername,@epassword)";
cmd.Connection = con;
cmd.Parameters.AddWithValue("@ename", name_txt.Text);
cmd.Parameters.AddWithValue("@eaddress", address_txt.Text);
cmd.Parameters.AddWithValue("@econtact", contact_txt.Text);
cmd.Parameters.AddWithValue("@eusername", username_txt.Text);
cmd.Parameters.AddWithValue("@epassword", password_txt.Text);
cmd.ExecuteNonQuery();
cmd.Dispose();
con.Close();
clr_rec();
lbl_msg.Text = "Your record sumitted successfully";
}

}
else
{
lbl_msg.Text = name_txt.Text + " " + " " + "already exists in the database";
clr_rec();
return;
}

}
private void clr_rec()
{
name_txt.Text = "";
address_txt.Text = "";
contact_txt.Text = "";
username_txt.Text = "";
password_txt.Text = "";
}



}

No comments:

Post a Comment