Insert ASP.Net Repeater checked Records if not exists in database using C# and VB.Net

Hi selverca,

Please refer below sample.

HTML

<asp:Repeater ID="rptDetails" runat="server">
    <HeaderTemplate>
        <table>
            <tr>
                <td></td>
                <td>CustomerId</td>
                <td>Name</td>
                <td>Country</td>
            </tr>
    </HeaderTemplate>
    <ItemTemplate>
        <tr>
            <td><asp:CheckBox ID="ChkBox" runat="server" /></td>
            <td><asp:Label ID="lblCustomerId" Text='<%#Eval("CustomerID") %>' runat="server" /></td>
            <td><asp:Label ID="lblName" Text='<%#Eval("Name") %>' runat="server" /></td>
            <td><asp:Label ID="lblCountry" Text='<%#Eval("Country") %>' runat="server" /></td>
        </tr>
    </ItemTemplate>
    <FooterTemplate>
        </table>.
    </FooterTemplate>
</asp:Repeater>
<asp:Button ID="btnSave" Text="Save" runat="server" OnClick="btnSave_Click" /><br />
<asp:Label ID="lblMessage" runat="server" />

Namespace

C#

using System.Data;
using System.Configuration;
using System.Data.SqlClient;

VB.Net

Imports System.Data
Imports System.Configuration
Imports System.Data.SqlClient

Code

C#

protected void Page_Load(object sender, EventArgs e)
{
    if (!this.IsPostBack)
    {
        this.DataTableBind();
    }
}
private void DataTableBind()
{
    string conString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    using (SqlConnection con = new SqlConnection(conString))
    {
        using (SqlCommand cmd = new SqlCommand("SELECT * FROM Customers", con))
        {
            using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
            {
                DataTable dt = new DataTable();
                sda.Fill(dt);
                rptDetails.DataSource = dt;
                rptDetails.DataBind();
            }
        }
    }
}    

protected void btnSave_Click(object sender, EventArgs e)
{
    foreach (RepeaterItem item in rptDetails.Items)
    {
        Label customerId = (item.FindControl("lblCustomerId") as Label);
        Label name = ((item.FindControl("lblName")) as Label);
        Label country = ((item.FindControl("lblCountry")) as Label);
        if ((item.FindControl("ChkBox") as CheckBox).Checked)
        {
            if (!IsDataExists(name.Text, country.Text))
            {
                InsertData(name.Text, country.Text);
                lblMessage.Text = "User Added Successfully";
            }
            else
            {
                lblMessage.Text = "User already Exists.";
            }
        }
    }
}

private void InsertData(string name, string country)
{
    string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    using (SqlConnection con = new SqlConnection(constr))
    {
        using (SqlCommand cmd = new SqlCommand("INSERT INTO Customers VALUES(@Name, @Country)", con))
        {
            cmd.Parameters.AddWithValue("@Name", name);
            cmd.Parameters.AddWithValue("@Country", country);
            con.Open();
            cmd.ExecuteNonQuery();
            con.Close();
        }
    }
}

private bool IsDataExists(string name, string country)
{
    bool isExists = false;
    string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    using (SqlConnection con = new SqlConnection(constr))
    {
        using (SqlCommand cmd = new SqlCommand("SELECT COUNT(*) FROM Customers WHERE Name = @Name AND Country = @Country", con))
        {
            cmd.Parameters.AddWithValue("@Name", name);
            cmd.Parameters.AddWithValue("@Country", country);
            con.Open();
            isExists = Convert.ToInt16(cmd.ExecuteScalar()) > 0 ? true : false;
            con.Close();
        }
    }

    return isExists;
}

VB.Net

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

Private Sub DatatableBind()
    Dim conString As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
    Using con As SqlConnection = New SqlConnection(conString)
        Using cmd As SqlCommand = New SqlCommand("SELECT * FROM Customers", con)
            Using sda As SqlDataAdapter = New SqlDataAdapter(cmd)
                Dim dt As DataTable = New DataTable()
                sda.Fill(dt)
                rptDetails.DataSource = dt
                rptDetails.DataBind()
            End Using
        End Using
    End Using
End Sub

Protected Sub btnSave_Click(ByVal sender As Object, ByVal e As EventArgs)
    For Each item As RepeaterItem In rptDetails.Items
        Dim customerId As Label = (TryCast(item.FindControl("lblCustomerId"), Label))
        Dim name As Label = (TryCast((item.FindControl("lblName")), Label))
        Dim country As Label = (TryCast((item.FindControl("lblCountry")), Label))
        If (TryCast(item.FindControl("ChkBox"), CheckBox)).Checked Then

            If Not IsDataExists(name.Text, country.Text) Then
                InsertData(name.Text, country.Text)
                lblMessage.Text = "User Added Successfully"
            Else
                lblMessage.Text = "User already Exists."
            End If
        End If
    Next
End Sub

Private Sub InsertData(ByVal name As String, ByVal country As String)
    Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
    Using con As SqlConnection = New SqlConnection(constr)
        Using cmd As SqlCommand = New SqlCommand("INSERT INTO Customers VALUES(@Name, @Country)", con)
            cmd.Parameters.AddWithValue("@Name", name)
            cmd.Parameters.AddWithValue("@Country", country)
            con.Open()
            cmd.ExecuteNonQuery()
            con.Close()
        End Using
    End Using
End Sub

Private Function IsDataExists(ByVal name As String, ByVal country As String) As Boolean
    Dim isExists As Boolean = False
    Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
    Using con As SqlConnection = New SqlConnection(constr)
        Using cmd As SqlCommand = New SqlCommand("SELECT COUNT(*) FROM Customers WHERE Name = @Name AND Country = @Country", con)
            cmd.Parameters.AddWithValue("@Name", name)
            cmd.Parameters.AddWithValue("@Country", country)
            con.Open()
            isExists = If(Convert.ToInt16(cmd.ExecuteScalar()) > 0, True, False)
            con.Close()
        End Using
    End Using

    Return isExists
End Function

Screenshot