Insert DataGridView Values In C# To A SqlServer Database Table

This Program Helps You To Insert Data Grid View Values To A Database.

In My Example I'm Considering A Data Grid View With Three Columns Named As ItemNo , ItemName, Qty.In My Example I Wanted To Insert All The DataGridView Rows To A Table Called Items Which Has ItemNo, ItemName, Qty Fields.Insertion Must Happened After Clicking The Save Button.

Make Sure Data Types In The Data Grid View Are Similar To The Data Types In The Database Table.

First Create A New Project and Add A DataGridView and A Button To It.Make Sure Your Data Grid View Id As MyDataGridView and Button Id As Save.Then Add Three Columns To That.You Can Add Columns To The DataGridView From Either Properties Window or from Code.
Before Doing The Codes Make Sure You Have The Sql Server Class As Follows.
(if you have any problem in SQL Connection refer to following post
http://easycodestuff.blogspot.com/2014/01/secure-best-sql-server-connection-for-c.html)

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data;

namespace DataGridViewExample
{
    class DBConnection
    {
        string strconnection = "Server=localhost;Uid=root;Pwd=;Database=yourdatabase;";
        SqlConnection sqlcon = new SqlConnection();
        SqlCommand sqlcmd = new SqlCommand();
        SqlDataAdapter sqlda = new SqlDataAdapter();
        DataTable dt = new DataTable();

        public void connect()
        {
            sqlcon = new SqlConnection(strconnection);
            sqlcon.Open();
        }

        public void disconnect()
        {
            if (sqlcon.State == ConnectionState.Open)
            {
                sqlcon.Close();
                sqlcon.Dispose();
            }
        }

        public DataTable ReadData(string query)
        {
            try
            {
                connect();
                sqlcmd = new SqlCommand(query, sqlcon);
                sqlda = new SqlDataAdapter(sqlcmd);
                dt = new DataTable();
                sqlda.Fill(dt);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                disconnect();
            }
            return dt;
        }

        public void QryCommand(string query)
        {
            try
            {
                connect();
                sqlcmd = new SqlCommand(query, sqlcon);
                sqlcmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                disconnect();
            }
        }

    }
}


Make Sure Your Form Class Start With Following Code Lines.

namespace DataGridViewExample
{
    class test     {
          private string query = "";
          DBConnection connection = new DBConnection();

          DataTable dt = new DataTable();

Finally You Have To Do Is Reading All The Rows Of The Data Grid View Thorough A Loop and Calling The Insert Query Inside The Same Loop.Following Code Unit Will Help You That.


private void BtnSave_Click(object sender, EventArgs e)//Save Button
{
for (int i = 0; i < MyDataGridView.RowCount; i++) // Run Until End Of MyDataGridView
        {
query = "INSERT INTO Items(ItemNo,ItemName,Qty) VALUES('" +
           MyDataGridView.Rows[i].Cells[0].Value.ToString() + "','" +  
           MyDataGridView.Rows[i].Cells[1].Value.ToString() + "','" + 
           MyDataGridView.Rows[i].Cells[2].Value.ToString() + "')";
           connection.connect();
           connection.QryCommand(query);
        }  
}