Data insertion by datagridview

Question

I have a DataGridview filled with some data in approx 100 rows and in 5 columns. I also have a button on the form. i want that whenever i click on that button, all the data from that DataGrid view gets inserted in my database but all the rows data at once in the fastest way . Thanks .

Answer 1

Hi @Rajkumar Rao ,

Try using SqlTransaction Class to submit all insertion operations at once to reduce database I/O operations and improve performance.

using (SqlConnection conn = new SqlConnection(connectionString))
{
    conn.Open();

    using (SqlTransaction transaction = conn.BeginTransaction())
    {
        try
        {
            foreach (DataGridViewRow row in dataGridView1.Rows)
            {
                if (row.IsNewRow) continue; // 跳过新行

                string column1Value = row.Cells["Column1"].Value.ToString();
                string column2Value = row.Cells["Column2"].Value.ToString();
                string column3Value = row.Cells["Column3"].Value.ToString();
                string column4Value = row.Cells["Column4"].Value.ToString();

                string query = "INSERT INTO YourTableName (Column1, Column2, Column3, Column4) VALUES (@Column1, @Column2, @Column3, @Column4)";

                using (SqlCommand command = new SqlCommand(query, conn, transaction))
                {
                    command.Parameters.AddWithValue("@Column1", column1Value);
                    command.Parameters.AddWithValue("@Column2", column2Value);
                    command.Parameters.AddWithValue("@Column3", column3Value);
                    command.Parameters.AddWithValue("@Column4", column4Value);

                    command.ExecuteNonQuery();
                }
            }

            transaction.Commit();
        }
        catch (Exception ex)
        {
            transaction.Rollback();
        }
    }
}

Best Regards.

Jiachen Li

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Hello, I am sharing an example below. I hope it is helpful.

using System;
using System.Data;
using System.Data.SqlClient;
using System.Windows.Forms;

namespace DataGridViewToDatabase
{
    public partial class MainForm : Form
    {
        private DataGridView dataGridView;
        private Button saveButton;

        public MainForm()
        {
            InitializeComponent();
            InitializeDataGridView();
            InitializeButton();
        }

        private void InitializeDataGridView()
        {
            // Create and configure the DataGridView
            dataGridView = new DataGridView();
            dataGridView.Dock = DockStyle.Fill;
            Controls.Add(dataGridView);

            // Create a DataTable to hold data for the DataGridView
            DataTable dataTable = new DataTable();
            dataTable.Columns.Add("Column1");
            dataTable.Columns.Add("Column2");
            // You can add more columns if needed

            // Bind the DataTable to the DataGridView
            dataGridView.DataSource = dataTable;
        }

        private void InitializeButton()
        {
            // Create the "Save" button
            saveButton = new Button();
            saveButton.Text = "Save";
            saveButton.Click += SaveDataToDatabase;
            Controls.Add(saveButton);
        }

        private void SaveDataToDatabase(object sender, EventArgs e)
        {
            // Define the connection string for the SQL Server database
            string connectionString = "Data Source=YourServer;Initial Catalog=YourDatabase;Integrated Security=True";

            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();

                // Start a new SQL transaction
                SqlTransaction transaction = connection.BeginTransaction();

                // Get the DataTable bound to the DataGridView
                DataTable dataTable = (DataTable)dataGridView.DataSource;

                try
                {
                    foreach (DataRow row in dataTable.Rows)
                    {
                        string column1 = row["Column1"].ToString();
                        string column2 = row["Column2"].ToString();

                        // Create an INSERT query to add data to the SQL Server database
                        string insertQuery = "INSERT INTO YourTable (Column1, Column2) VALUES (@Column1, @Column2)";

                        using (SqlCommand cmd = new SqlCommand(insertQuery, connection, transaction))
                        {
                            cmd.Parameters.AddWithValue("@Column1", column1);
                            cmd.Parameters.AddWithValue("@Column2", column2);
                            cmd.ExecuteNonQuery();
                        }
                    }

                    // Commit the transaction if all inserts are successful
                    transaction.Commit();
                    MessageBox.Show("Data has been successfully saved.");
                }
                catch (Exception ex)
                {
                    // Roll back the transaction if there's an error
                    transaction.Rollback();
                    MessageBox.Show("An error occurred: " + ex.Message);
                }
            }
        }

    }
}

Answer 3

I like to offer another solution. First, when loading data, load if into a list. For example, the DataGridView is populated with a list of people. The nice thing about this is you know the data types are correct and the database table can handle nulls if need be.

internal class Person
{
    public int Id { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public DateOnly BirthDate { get; set; }
    public override string ToString() => Id.ToString();
}

In the form I load mocked data, one button for saving.

public partial class DataForm : Form
{
    private BindingList<Person> _personList;
    private BindingSource _bindingSource = new();
    public DataForm()
    {
        InitializeComponent();
        _personList = new BindingList<Person>(BogusOperations.People());
        _bindingSource.DataSource = _personList;
        dataGridView1.DataSource = _bindingSource;
    }

    private void SaveButton_Click(object sender, EventArgs e)
    {
        DataOperations.AddRange(_personList.ToList());
    }
}

I like to place SQL as follows.

public class SqlStatements
{
    public static string InsertPeople =>
        """
        INSERT INTO dbo.Person
        (
            FirstName,
            LastName,
            BirthDate
        )
        VALUES
        (@FirstName, @LastName, @BirthDate);
        SELECT CAST(scope_identity() AS int);
        """;

}

Data class

internal class DataOperations
{

    public static void AddRange(List<Person> list)
    {

        using SqlConnection cn = new(ConnectionString());
        using SqlCommand cmd = new() { Connection = cn, CommandText = SqlStatements.InsertPeople };

        cmd.Parameters.Add("@FirstName", SqlDbType.NVarChar);
        cmd.Parameters.Add("@LastName", SqlDbType.NVarChar);
        cmd.Parameters.Add("@BirthDate", SqlDbType.Date);
      
        cn.Open();

        for (int index = 0; index < list.Count; index++)
        {
            cmd.Parameters["@FirstName"].Value = list[index].FirstName;
            cmd.Parameters["@LastName"].Value = list[index].LastName;
            cmd.Parameters["@BirthDate"].Value = list[index].BirthDate;
            list[index].Id = Convert.ToInt32(cmd.ExecuteScalar());
        }
    }

}

• In the other replies they used AddWithValue which can have side affects as per the following post.
• A bonus for my code is if needed the new key is set for each insert.

And for completeness this is how for the above I mocked data using Bogus

internal class BogusOperations
{
    public static List<Models.Person> People(int count = 20) =>
        new Faker<Models.Person>()
            .RuleFor(c => c.FirstName, f => f.Person.FirstName)
            .RuleFor(c => c.LastName, f => f.Person.LastName)
            .RuleFor(c => c.BirthDate, f => 
                f.Date.BetweenDateOnly(new DateOnly(1999,1,1),
                    new DateOnly(2010, 1, 1))).Generate(count);
}