How to check for duplicate records so that your database will not be filled with same content over and over again??
In this tutorial, I am checking for duplicate record for a book so that when adding a book record, duplicated book record will NOT be added.
NOTE: In this tutorial, I used a shortcut to connect the connection string. You can still use your normal connection string. However, if you wish to use the shortcut, remember to replace your connectionStrings line in your Web.config
TO BE REPLACED IN WEB.CONFIG
<connectionStrings>
<add name="ApplicationServices"
connectionString="data source=.\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|\aspnetdb.mdf;User Instance=true"
providerName="System.Data.SqlClient" />
<add name="connString" connectionString="data source=_______________;database=_________________________;Integrated Security=True" providerName="System.Data.SqlClient"/>
</connectionStrings>
Firstly, in the .aspx file, add this code below to allow user to add a book name record.
<b><asp:Label ID="lblbookname" Text="Enter book name: " runat="server"></asp:Label></b><br />
<asp:TextBox ID="bookname" runat="server" Width="300px"></asp:TextBox><br />
<div id="bookdivMessage" runat="server"></div><br />
<b><asp:Button ID="addbookbtn2" Text="Add Book" runat="server" Width="100px"
onclick="addbookbtn_Click"></asp:Button></b><br />
Next, in the aspx.cs file (code behind file), add this part to allow access and usage of Sql and Class file which will be created later.
using System.Data;
using System.Data.SqlClient;
using ProjectName.App_Code;
protected void addbookbtn_Click(object sender, EventArgs e)
{
int numOfRecordsAffected = 0;
CBookManager BooksManager = new CBookManager();
string collectedBookName = "";
collectedBookName = bookname.Text;
Boolean isThereDuplicateRecordByBookName = false;
isThereDuplicateRecordByBookName = BooksManager.checkDuplicateRecordByBookName(collectedBookName);
if (isThereDuplicateRecordByBookName == true)
{
bookdivMessage.InnerHtml = "There is already a book with this name";
}
if (isThereDuplicateRecordByBookName == false)
{
numOfRecordsAffected = BooksManager.addOneBook(collectedBookName);
divMessage.InnerHtml = "<u>" + numOfRecordsAffected + "</u> record has been added";
}
else
{
numOfRecordsAffected = 0;
divMessage.InnerHtml = "<u>" + numOfRecordsAffected + "</u> record has been added";
}
}
After completing the code behind file, create a file named App_Code within your project and create a class file named CBookManager. After creating CBookManager, change the build action to COMPILE and add the following codes below.
using System.Data;
using System.Data.SqlClient;
using System.Configuration; -- To use Connection String
This code below is to check for duplicate record within the database. If there is duplicate record within database, record will not be added. However, if duplicate record == false, record will be added successfully.
public Boolean checkDuplicateRecordByBookName(String inBookName)
{
int result = 0;
SqlCommand cmd = new SqlCommand();
SqlConnection conn = new SqlConnection();
string connString = ConfigurationManager.ConnectionStrings["connString"].ConnectionString;
conn.ConnectionString = connString;
cmd.Connection = conn;
string SqlText = "SELECT COUNT(*) FROM BOOK WHERE BOOKNAME = @inBookName";
cmd.CommandText = SqlText;
cmd.Parameters.Add("@inBookName", SqlDbType.VarChar, 100);
cmd.Parameters["@inBookName"].Value = inBookName;
conn.Open();
result = Convert.ToInt32(cmd.ExecuteScalar());
conn.Close();
if (result >= 1)
{
return true;
}
else
{
return false;
}
Next, to activate the add book function, insert the following code:
public int addOneBook(String inBookName)
{
int numOfRecordsAffected = 0;
SqlCommand cmd = new SqlCommand();
SqlConnection conn = new SqlConnection();
//Setup of the connection information
string connString = ConfigurationManager.ConnectionStrings["connString"].ConnectionString;
conn.ConnectionString = connString;
cmd.Connection = conn;//Tell the Command object use the Connection object
//Prepare a INSERT SQL template
string sqlText = "INSERT INTO Book (BookName) ";
sqlText += " VALUES (@inBookName)";
//setup the SQL in the cmd object
cmd.CommandText = sqlText;
cmd.Parameters.Add("@inBookName", SqlDbType.VarChar, 100);
cmd.Parameters["@inBookName"].Value = inBookName;
conn.Open();
numOfRecordsAffected = cmd.ExecuteNonQuery();
conn.Close();
return numOfRecordsAffected;
}
Remember to create a Book database, which consists of BookName, type = varchar, 100 characters.
We have come to the end of this tutorial :D Thanks for your patience and have fun coding :)
