In this article I will explain how to insert (save) multiple rows (records) from DataGridView to database in Windows Forms (WinForms) Application using C# and VB.Net.
Database
I have made use of the following table Customers with the schema as follows.
![Insert (Save) Multiple rows from DataGridView to Database in Windows Forms (WinForms) Application using C# and VB.Net]()
Note: You can download the database table SQL by clicking the download link below.
Form Controls
The below Form consists of a DataGridView control and a Button.
![Insert (Save) Multiple rows from DataGridView to Database in Windows Forms (WinForms) Application using C# and VB.Net]()
Namespaces
You will need to import the following namespaces.
C#
using System.Data;
using System.Data.SqlClient;
VB.Net
Imports System.Data
Imports System.Data.SqlClient
Populating DataGridView
Inside the Form Load event, the DataGridView is populated with a dynamic DataTable with some dummy records.
C#
private void Form1_Load(object sender, EventArgs e)
{
this.BindDataGridView();
}
private void BindDataGridView()
{
DataTable dt = new DataTable();
dt.Columns.AddRange(new DataColumn[3] { new DataColumn("Id", typeof(int)),
new DataColumn("Name", typeof(string)),
new DataColumn("Country",typeof(string)) });
dt.Rows.Add(1, "John Hammond", "United States");
dt.Rows.Add(2, "Mudassar Khan", "India");
dt.Rows.Add(3, "Suzanne Mathews", "France");
dt.Rows.Add(4, "Robert Schidner", "Russia");
this.dataGridView1.DataSource = dt;
}
VB.Net
Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
BindDataGridView()
End Sub
Private Sub BindDataGridView()
Dim dt As New DataTable()
dt.Columns.AddRange(New DataColumn(2) {New DataColumn("Id", GetType(Integer)), New DataColumn("Name", GetType(String)), New DataColumn("Country", GetType(String))})
dt.Rows.Add(1, "John Hammond", "United States")
dt.Rows.Add(2, "Mudassar Khan", "India")
dt.Rows.Add(3, "Suzanne Mathews", "France")
dt.Rows.Add(4, "Robert Schidner", "Russia")
Me.dataGridView1.DataSource = dt
End Sub
![Insert (Save) Multiple rows from DataGridView to Database in Windows Forms (WinForms) Application using C# and VB.Net]()
Insert (Save) Multiple rows from DataGridView to Database
When the Insert Button is clicked, a loop is executed over the DataGridView rows. Inside the loop, the values from each column (cell) of the DataGridView
are fetched and the record is inserted into the database table.
C#
private void btnInsert_Click(object sender, EventArgs e)
{
foreach (DataGridViewRow row in dataGridView1.Rows)
{
string constring = @"Data Source=.\SQL2008R2;Initial Catalog=AjaxSamples;Integrated Security=true";
using (SqlConnection con = new SqlConnection(constring))
{
using (SqlCommand cmd = new SqlCommand("INSERT INTO Customers VALUES(@CustomerId, @Name, @Country)", con))
{
cmd.Parameters.AddWithValue("@CustomerId", row.Cells["Id"].Value);
cmd.Parameters.AddWithValue("@Name", row.Cells["Name"].Value);
cmd.Parameters.AddWithValue("@Country", row.Cells["Country"].Value);
con.Open();
cmd.ExecuteNonQuery();
con.Close();
}
}
}
MessageBox.Show("Records inserted.");
}
VB.Net
Private Sub btnInsert_Click(sender As System.Object, e As System.EventArgs) Handles btnInsert.Click
For Each row As DataGridViewRow In dataGridView1.Rows
Dim constring As String = "Data Source=.\SQL2008R2;Initial Catalog=AjaxSamples;Integrated Security=true"
Using con As New SqlConnection(constring)
Using cmd As New SqlCommand("INSERT INTO Customers VALUES(@CustomerId, @Name, @Country)", con)
cmd.Parameters.AddWithValue("@CustomerId", row.Cells("Id").Value)
cmd.Parameters.AddWithValue("@Name", row.Cells("Name").Value)
cmd.Parameters.AddWithValue("@Country", row.Cells("Country").Value)
con.Open()
cmd.ExecuteNonQuery()
con.Close()
End Using
End Using
Next
MessageBox.Show("Records inserted.")
End Sub
![Insert (Save) Multiple rows from DataGridView to Database in Windows Forms (WinForms) Application using C# and VB.Net]()
Downloads
