How to use Multiple More Than One DataKeyNames to Delete GridView Checked Rows in Asp.Net C# VB.Net

In this article i will show you how one can use more than one datakeynames in a gridview as well as in GridView editing time or in GridView manipulation time how one can read more than one datakeynames that you have assigned in design time or in runtime. The real example is let you have a product table. Which contains productid, brandid, category id as well. So when you show a list of products then you have to pick a product with productid, BrandID & CategoryID for GridView records deletion or modification. Here i will describe how.

DataKeyNames is the property to define Read-only primary key or composite primary key like fields in a GridView control. We can also add some more other database fields to this property separated by comma operator.

Objective/Outcome:
How to use Multiple More Than One DataKeyNames of a GridView in Asp.Net Csharp VB.Net

CLICK HERE to Read Check or UnCheck all Checkboxes of a GridView using Javascript.

To do the Example First Create 2 Database Tables:

CREATE TABLE [dbo].[Brand](
	[ID] [bigint] NOT NULL,
	[Name] [varchar](max) NOT NULL,
 CONSTRAINT [PK_Brand] 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]

CREATE TABLE [dbo].[Product](
	[ID] [bigint] NOT NULL,
	[Name] [varchar](max) NULL,
	[Description] [varchar](max) NULL,
	[BrandID] [bigint] NULL,
 CONSTRAINT [PK_Product_2] 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]

Now Insert Some Data:

INSERT INTO Brand Values(1,'Kohinoor')
INSERT INTO Brand Values(2,'Proctor & Gamble')
INSERT INTO Brand Values(3,'Telenor')
INSERT INTO Brand Values(4,'Unilever')

INSERT INTO PRODUCT VALUES(1,'Lux Beauty Soap','Product of Unilever',4)
INSERT INTO PRODUCT VALUES(2,'Tibbet Pomed','Winter Product',1)
INSERT INTO PRODUCT VALUES(3,'Data SIM','Subscriber Identity Module',3)
INSERT INTO PRODUCT VALUES(4,'Nippon','Color Television',1)

Now modify the Web.Config File to Connect to Database:

<configuration>
  <connectionStrings>
    <add name="DBConnection" connectionString="Data Source=.\SQLEXPRESS;Initial Catalog=TESTDB;Trusted_Connection=yes;" providerName="System.Data.SqlClient"/>
    <!--<add name="BONConnection" connectionString="Data Source=XXX.com;Initial Catalog=DBNAME;User Id=UserName;Password=YourPassword;" providerName="System.Data.SqlClient" />-->
  </connectionStrings>
...................
...................

Note: If you use windows authentication then use the first key. Otherwise use second key line. Update server name, Database name, User name, Password as per your settings.

How to assign more than one or multiple Datakeynames in a Gridview:

<asp:GridView runat="server" ID="GridView1" DataKeyNames="ID,BrandID" AutoGenerateColumns="false" HeaderStyle-BackColor="CornflowerBlue" HeaderStyle-Font-Bold="true" HeaderStyle-ForeColor="White" CellPadding="5">

<Columns>
    <asp:TemplateField HeaderText="Select">
    <ItemTemplate>
    <asp:CheckBox runat="server" ID="chk"/>
    </ItemTemplate>
    </asp:TemplateField>

     <asp:BoundField DataField="Name" HeaderText="Name"/>
     <asp:BoundField DataField="Description" HeaderText="Description" />
     <asp:BoundField DataField="Brand" HeaderText="Brand" />
</Columns>
</asp:GridView>
<br />
<asp:Button ID="Delete" runat="server" Text="Delete" onclick="Delete_Click" />

Bind GridView with Sample Data:
C# Code:

    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
            Bind_GridView();
    }

    private void Bind_GridView()
    {
        DataTable dt;
        String SQL = "SELECT P.ID ID, P.Name Name, P.Description Description, B.ID BrandID,B.Name Brand" +
                " FROM Product P, Brand B" +
                " WHERE P.BrandID=B.ID";

        using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["DBConnection"].ConnectionString))
        {
            conn.Open();
            using (SqlDataAdapter da = new SqlDataAdapter(new SqlCommand(SQL, conn)))
            {
                dt = new DataTable("tbl");
                da.Fill(dt);
            }
        }

        GridView1.DataSource = dt;
        GridView1.DataBind();
    }

VB.Net Code:

    Protected Sub Page_Load(sender As Object, e As System.EventArgs) Handles Me.Load
        If Not IsPostBack Then
            Bind_GridView()
        End If
    End Sub

    Private Sub Bind_GridView()
        Dim dt As DataTable
        Dim SQL As [String] = "SELECT P.ID ID, P.Name Name, P.Description Description, B.ID BrandID,B.Name Brand" & " FROM Product P, Brand B" & " WHERE P.BrandID=B.ID"

        Using conn As New SqlConnection(ConfigurationManager.ConnectionStrings("DBConnection").ConnectionString)
            conn.Open()
            Using da As New SqlDataAdapter(New SqlCommand(SQL, conn))
                dt = New DataTable("tbl")
                da.Fill(dt)
            End Using
        End Using

        GridView1.DataSource = dt
        GridView1.DataBind()
    End Sub

How to use Multiple or More Than One DataKeyNames:
C# Code

    protected void Delete_Click(object sender, EventArgs e)
    {
        string sClause = "";
        foreach (GridViewRow oItem in GridView1.Rows)
        {
            if (((CheckBox)oItem.FindControl("chk")).Checked)
            {
                for (int i = 0; i < GridView1.DataKeyNames.Length; i++)
                    sClause = sClause + " AND " + GridView1.DataKeyNames.GetValue(i) + "=" + GridView1.DataKeys[oItem.DataItemIndex][i].ToString();

                string sSQL = "DELETE FROM Product WHERE 1=1 "+sClause;
                // The above sql will generate like the below query
                // DELETE FROM product WHERE 1=1 AND ID=4 AND BrandID=2
                using (SqlConnection Conn = new SqlConnection(ConfigurationManager.ConnectionStrings["DBConnection"].ConnectionString))
                {
                    Conn.Open();
                    using (SqlCommand comm = new SqlCommand(sSQL, Conn))
                    {
                        comm.CommandTimeout = 0;
                        comm.ExecuteNonQuery();
                    }
                }
            }
            sClause = "";
        }
        Bind_GridView();
    }

VB.Net Code:

Protected Sub Delete_Click(sender As Object, e As System.EventArgs) Handles Delete.Click
        Dim sClause As String = ""
        For Each oItem As GridViewRow In GridView1.Rows
            If DirectCast(oItem.FindControl("chk"), CheckBox).Checked Then
                For i As Integer = 0 To GridView1.DataKeyNames.Length - 1
                    sClause = sClause & " AND " & GridView1.DataKeyNames.GetValue(i) & "=" & GridView1.DataKeys(oItem.DataItemIndex)(i).ToString()
                Next

                Dim sSQL As String = "DELETE FROM Product WHERE 1=1 " & sClause
                ' The above sql will generate like the below query
                ' DELETE FROM product WHERE 1=1 AND ID=4 AND BrandID=2
                Using Conn As New SqlConnection(ConfigurationManager.ConnectionStrings("DBConnection").ConnectionString)
                    Conn.Open()
                    Using comm As New SqlCommand(sSQL, Conn)
                        comm.CommandTimeout = 0
                        comm.ExecuteNonQuery()
                    End Using
                End Using
            End If
            sClause = ""
        Next
        Bind_GridView()

    End Sub

The example is completed now. Check it from your end & let me know if any issue.

Download Code Example C#        Download Code Example VB.Net

Leave a Reply

Your email address will not be published. Required fields are marked *

AlphaOmega Captcha Classica  –  Enter Security Code