Thursday, 31 March 2011

Avoid insertion of duplicate record using Asp.Net Using Classes


Introduction
In this article, i will discuss about avoid duplicate Records insertion with GridView in ASP.NET.Most of times, when we are play with data and gridview in ASP.NET, we may needs to insert records into the database,when users enter in grdiview.so this time user may enter duplicate records twice times.so this article help you all how to avoid duplicate records insert in to the database from presentation layer to database.
Implementation
Firsy design page like following, 

HTML Code
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Duplicate.aspx.cs" Inherits="WebApplication1.Duplicate" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <table style="width: 32%; height: 51px;">
        <tr>
            <td>
                Enter Class
            </td>
            <td>
                <asp:TextBox ID="txt_class" runat="server"></asp:TextBox>
            </td>
            <td>
                <asp:RequiredFieldValidator ID="RequiredFieldValidator1" runat="server" ControlToValidate="txt_class"
                    ErrorMessage="Required"></asp:RequiredFieldValidator>
            </td>
        </tr>
        <tr>
            <td>
                &nbsp;
            </td>
            <td>
                <asp:Button ID="btn_submit" runat="server" Text="Submit" OnClick="btn_submit_Click" />
            </td>
            <td>
                &nbsp;
            </td>
        </tr>
        <tr>
            <td colspan="3">
                <asp:Label ID="lbl_msg" runat="server" ForeColor="Red"></asp:Label>
            </td>
        </tr>
    </table>
    <br />
    <asp:GridView ID="GridView1" runat="server" AllowPaging="True" AutoGenerateColumns="False"
        CellPadding="4" ForeColor="#333333" GridLines="None" Width="554px" OnPageIndexChanging="GridView1_PageIndexChanging"
        OnRowCancelingEdit="GridView1_RowCancelingEdit" OnRowDeleting="GridView1_RowDeleting"
        OnRowEditing="GridView1_RowEditing" OnRowUpdating="GridView1_RowUpdating" OnRowDataBound="GridView1_RowDataBound">
        <FooterStyle BackColor="#1C5E55" Font-Bold="True" ForeColor="White" />
        <RowStyle BackColor="#E3EAEB" />
        <Columns>
            <asp:TemplateField HeaderText="Click On Class to Add Terms">
                <EditItemTemplate>
                    <asp:RequiredFieldValidator ID="RequiredFieldValidator2" runat="server" ErrorMessage="Required"
                        ControlToValidate="txt_eclass" ValidationGroup="a"></asp:RequiredFieldValidator>
                    <asp:Label ID="lbl_edit" runat="server" Text='<%# Eval("id") %>' Visible="False"></asp:Label>
                    <asp:TextBox ID="txt_eclass" runat="server" Text='<%# Eval("class") %>'></asp:TextBox>
                </EditItemTemplate>
                <ItemTemplate>
                    <asp:Label ID="lbl_id" runat="server" Text='<%# Eval("id") %>' Visible="False"></asp:Label>
                    <a href='AdminTerms.aspx?clsid=<%#Eval("id") %>'>
                        <asp:Label ID="Label1" runat="server" Text='<%# Eval("class") %>'></asp:Label></a>
                </ItemTemplate>
            </asp:TemplateField>
            <asp:TemplateField HeaderText="Edit">
                <EditItemTemplate>
                    <asp:LinkButton ID="LinkButton2" runat="server" CommandName="update" ValidationGroup="a">Update</asp:LinkButton>
                    &nbsp;<asp:LinkButton ID="LinkButton3" runat="server" CausesValidation="False" CommandName="cancel">Cancel</asp:LinkButton>
                </EditItemTemplate>
                <ItemTemplate>
                    <asp:LinkButton ID="LinkButton1" runat="server" CommandName="edit" CausesValidation="False">Edit</asp:LinkButton>
                </ItemTemplate>
            </asp:TemplateField>
            <asp:TemplateField HeaderText="Delete">
                <ItemTemplate>
                    <asp:LinkButton ID="LinkButton4" runat="server" CommandName="delete" CausesValidation="False"
                        OnClientClick="return confirm('Correspond to this Class all Sub-Records will be deleted')">Delete</asp:LinkButton>
                </ItemTemplate>
            </asp:TemplateField>
        </Columns>
        <PagerStyle BackColor="#666666" ForeColor="White" HorizontalAlign="Center" />
        <SelectedRowStyle BackColor="#C5BBAF" Font-Bold="True" ForeColor="#333333" />
        <HeaderStyle BackColor="#1C5E55" Font-Bold="True" ForeColor="White" HorizontalAlign="Left" />
        <EditRowStyle BackColor="#7C6F57" />
        <AlternatingRowStyle BackColor="White" />
    </asp:GridView>
    </form>
</body>
</html>
In above GUI is very simple and just allow to gridview add/ edit data with gridview directly, then save into the database.
Prepare Stored Procedures
SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tbClasses_AdminGrdData]') AND type in (N'P', N'PC'))

BEGIN

EXEC dbo.sp_executesql @statement = N'

-- It will display the data from tbClasses in Admin area.

CREATE PROCEDURE [dbo].[tbClasses_AdminGrdData]
   

AS

      Select * from tbClasses order by class desc

      RETURN
'

END

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tbClasses_AdminClsCHK]') AND type in (N'P', N'PC'))

BEGIN

EXEC dbo.sp_executesql @statement = N'


-- It will avoid to save the duplicate class

CREATE PROCEDURE [dbo].[tbClasses_AdminClsCHK]

      @class varchar(20)

AS

      select class from tbClasses where  class=@class

      RETURN

'

END

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tbClasses]') AND type in (N'U'))

BEGIN

CREATE TABLE [dbo].[tbClasses](

      [id] [bigint] IDENTITY(1,1) NOT NULL,

      [class] [varchar](50) NOT NULL,

 CONSTRAINT [PK_tbClasses_1] PRIMARY KEY CLUSTERED

(

      [id] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY]

END
In above sql script  is used for this sample project only.when you are use this technique in your project, then you have created own procedures for your project database.
Now write server side code to perform data access operation.
using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Xml.Linq;
using System.Data.SqlClient;

namespace Admin_Class_Subjects
{
    // Main Class for MAIN_CONNECTION

    public class Main_con
    {
        protected SqlConnection con = new SqlConnection();
        public SqlCommand cmd;
        public SqlDataAdapter adp = new SqlDataAdapter();
        public DataSet ds = new DataSet();
        public Main_con()
        {
            try
            {
                con.ConnectionString = ConfigurationManager.ConnectionStrings["cnn"].ConnectionString;
                con.Open();
            }
            catch
            {
                if (con.State == ConnectionState.Closed)
                {
                    con.Open();
                }
            }
        }
    }

    // Common Method for all Web Form.

    public class common : Main_con
    {
        public DataSet getdataset(SqlCommand cmd)
        {
            if (con.State == ConnectionState.Closed)
            {
                con.Open();
            }
            cmd.CommandType = CommandType.Text;
            cmd.Connection = con;
            adp.SelectCommand = cmd;
            adp.SelectCommand.CommandType = CommandType.StoredProcedure;
            DataSet ds = new DataSet();
            try
            {
                adp.Fill(ds);
            }
            finally
            {
                adp.Dispose();
                con.Close();

            }


            return ds;
        }
        public DataTable getdatable(SqlCommand cmd)
        {
            if (con.State == ConnectionState.Closed)
            {
                con.Open();
            }
            cmd.CommandType = CommandType.Text;
            cmd.Connection = con;
            adp.SelectCommand = cmd;
            adp.SelectCommand.CommandType = CommandType.StoredProcedure;
            DataTable dt = new DataTable();
            try
            {
                adp.Fill(dt);
            }
            finally
            {
                adp.Dispose();
                con.Close();
            }
            return (dt);
        }

        public DataSet search(String query, String from, String to)
        {
            if (con.State == ConnectionState.Closed)
            {
                con.Open();
            }

            adp = new SqlDataAdapter(query, con);
            adp.SelectCommand.Parameters.Add("@from", SqlDbType.DateTime).Value = from;
            adp.SelectCommand.Parameters.Add("@to", SqlDbType.DateTime).Value = to;
            DataSet ds = new DataSet();
            ds.Clear();
            try
            {
                adp.Fill(ds);
                adp.Dispose();
            }
            catch { }
            finally
            {
                con.Close();
            }
            return ds;

        }



        public SqlDataReader dd_list(String query)
        {

            if (con.State == ConnectionState.Closed)
            {

                con.Open();

            }
            cmd = new SqlCommand();
            cmd.CommandText = query;
            cmd.Connection = con;
            try
            {
                SqlDataReader a = cmd.ExecuteReader();
                return a;
            }
            finally
            {
                cmd.Dispose();
                con.Close();
            }

        }



        public SqlDataReader dd_listWithParameters(SqlCommand cmd)
        {
            if (con.State == ConnectionState.Closed)
            {
                con.Open();
            }
            cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandText = cmd.ToString();
            cmd.CommandType = CommandType.Text;
            cmd.CommandType = CommandType.StoredProcedure;
            try
            {
                SqlDataReader a = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                return a;

            }
            finally
            {
                cmd.Dispose();

            }
        }
    }

    //||***********INTERFACES***********||\\

    //Inteface of table Classes
    public interface Int_tbclasses
    {
        Int32 p_id
        { get; set; }
        String p_class
        { get; set; }
    }

    //||***********PROPERTIES***********||\\
    //Properties of table Classes

    public class P_tbClasses : Int_tbclasses
    {
        private Int32 id;
        private String classes;
        #region Int_tbclasses Members
        public int p_id
        {
            get
            {
                return id;
            }
            set
            {
                id = value;
            }
        }

        public string p_class
        {
            get
            {
                return classes;
            }

            set
            {

                classes = value;

            }
        }
        #endregion
    }

    //classes of table Classes
    public class cls_tbClasses : Main_con
    {
        public void save(P_tbClasses p)
        {
            if (con.State == ConnectionState.Closed)
            {
                con.Open();
            }
            cmd = new SqlCommand("insert into tbClasses values(@class)", con);
            cmd.Parameters.Add("@class", SqlDbType.VarChar, 50).Value = p.p_class;
            cmd.ExecuteNonQuery();
            cmd.Dispose();
            con.Close();
        }
        public void update(P_tbClasses p)
        {
            if (con.State == ConnectionState.Closed)
            {
                con.Open();
            }
            cmd = new SqlCommand("update tbClasses set class=@class where id=@id", con);
            cmd.Parameters.Add("@class", SqlDbType.VarChar, 50).Value = p.p_class;
            cmd.Parameters.Add("@id", SqlDbType.VarChar, 50).Value = p.p_id;
            cmd.ExecuteNonQuery();
            cmd.Dispose();
            con.Close();
        }

        public void delete(P_tbClasses p)
        {
            if (con.State == ConnectionState.Closed)
            {

                con.Open();
            }
            cmd = new SqlCommand("delete from tbClasses where id=@id", con);
            cmd.Parameters.Add("@id", SqlDbType.VarChar, 50).Value = p.p_id;
            cmd.ExecuteNonQuery();
            cmd.Dispose();
            con.Close();
        }
    }
}
Note:Above data access layer code, i'm not implement proper excption handling and proper data access object dispose.becuase my concern is prevent duplicarte insert into the database.so please do the proper data access code in your project.
Now let me write c# code for page to access gridview and pass data from page to dataaccess layer to insert to the database.
using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;

public partial class Admin_AdminClass : System.Web.UI.Page
{
    String Query;
    SqlCommand cmd;
    DataTable dt;

    Admin_Class_Subjects.cls_tbClasses tbclasses = new Admin_Class_Subjects.cls_tbClasses();
    Admin_Class_Subjects.P_tbClasses prp = new Admin_Class_Subjects.P_tbClasses();
    Admin_Class_Subjects.common qry = new Admin_Class_Subjects.common();

    protected void Page_Load(object sender, EventArgs e)
    {
        if (IsPostBack == false)
        {
            grdview();
        }

    }

    protected void btn_submit_Click(object sender, EventArgs e)
    {

        //It will avoid to save the duplicate class

        //Query = "select class from tbClasses where class=@class";

        Query = "tbClasses_AdminClsCHK";
        cmd = new SqlCommand(Query);
        cmd.Parameters.AddWithValue("@class", txt_class.Text);
        dt = qry.getdatable(cmd);
        String a;
        if (dt.Rows.Count == 0)
        {
            try
            {
                a = (dt.Rows[0]["class"]).ToString();
                //It will avoid to save the duplicate class using Comparing string
                if (String.Compare(a.ToUpper(), txt_class.Text.ToUpper()) == 0)
                {
                    lbl_msg.Text = "";
                    prp.p_class = txt_class.Text.Trim();
                    tbclasses.save(prp);
                    grdview();
                    txt_class.Text = "";
                }
            }
            catch
            {

                lbl_msg.Text = "";
                prp.p_class = txt_class.Text.Trim();
                tbclasses.save(prp);
                grdview();
                txt_class.Text = "";
            }
        }
        else
        {

            lbl_msg.Text = "Class already exists";
            txt_class.Text = "";
            return;
        }
    }
    private void grdview()
    {

        //It will display the data from tbClasses in Admin area.
        //Query = "     Select * from tbClasses";
        Query = "tbClasses_AdminGrdData";
        cmd = new SqlCommand(Query);
        dt = qry.getdatable(cmd);
        if (dt.Rows.Count == 0)
        {
            GridView1.DataSource = dt;
            GridView1.DataBind();
        }
        else
        {
            GridView1.DataSource = dt;
            GridView1.DataBind();
        }
    }

    protected void GridView1_PageIndexChanging(object sender, GridViewPageEventArgs e)
    {
        GridView1.PageIndex = e.NewPageIndex;
        grdview();
    }

    protected void GridView1_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
    {
        GridView1.EditIndex = -1;
        grdview();
    }
    protected void GridView1_RowEditing(object sender, GridViewEditEventArgs e)
    {
        GridView1.EditIndex = e.NewEditIndex;
        grdview();
    }

    protected void GridView1_RowDeleting(object sender, GridViewDeleteEventArgs e)
    {
        Int32 id;
        id = Convert.ToInt32(((Label)(GridView1.Rows[e.RowIndex].FindControl("lbl_id"))).Text);
        prp.p_id = id;
        tbclasses.delete(prp);
        GridView1.EditIndex = -1;
        grdview();
    }
    protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e)
    {
        Int32 ID;
        String cls;
        cls = ((TextBox)(GridView1.Rows[e.RowIndex].FindControl("txt_eclass"))).Text;
        ID = Convert.ToInt32(((Label)(GridView1.Rows[e.RowIndex].FindControl("lbl_edit"))).Text);
        prp.p_id = ID;
        prp.p_class = cls.Trim();
        tbclasses.update(prp);
        GridView1.EditIndex = -1;
        grdview();
    }
}

 Connectionstring
<add name="cnn" connectionString="Data source=comp4;database=db_fantasy;uid=sa;pwd=1234;Min Pool Size=100;Max Pool Size=1000;Connect Timeout=10"/>

That's all. through this article, i discussed,how to prevent the duplicate records insert into the database.this is very striaght forward and easy to understand.
Conclusion
In this article, i discussed about avoid duplicate Records insertion with GridView in ASP.NET.if you have any comments, please post ,try to improve my next article.

No comments:

Post a Comment