Bind Data Source To A DataGridView In Windows Form Application With Sql Server

In This Article Ill Demonstrate You Guys To Integrate A SQL Server Data Source With Data Grid View In C# Using Step By Step. In This Article I Will Create A Windows Form Application To Integrate With SQL Server. First Of All Make Sure You Have Installed SQL Server.

So Lets Start Our Application By Opening The Visual Studio. 

Step 01 : Click New Project And Select Windows > Windows Forms Application And Give A Project Name To It. Just Have A Look On Below Image, It Shows How I Did. 

Now Press OK And Create The Project. Then Open The Toolbox. If Toolbox Not Visible Click View > Toolbox. Then Toolbox Window Will Open. Then Add A Data Grid View From Tool Box To The Form.

Now What We Need To Do Is We Should Connect The DataGridView With SQL Server Data Source. Follow The Below Steps Continue It.

Step 02 : Open Server Explorer.

 Step 03 : Add A New Data Connection By Right Clicking The Data Connections Tab.

Step 04 : Select The Data Source As "Microsoft SQL Server (SqlClient)" And Select The Server Name. According To SQL Server, Select The Logging Either Windows Authentication Or SQL Server Authentication. Then Enter The Database Name. In My Example I Wanted My Database Name As "Bind Data To A GridView". If You Already Have A Database You Can Simply Select Your Database From Here. But Here I Will Create A New Database. Then Press OK.

Step 05 : If You Enter A Database Which Is Not Existing, It Will Open A Popup Like Below. Press Yes To Create It.

Step 06 : Then You Will Get The Server Explorer As Below. Right Click Tables And Add A New Table If You Created A New Database.

Step 07 : I Have Created The Table Called Employee With The Following Fields. Then Click Update Button.

Step 08 : Go To SQL Server And Check The Database With The Table We Have Created.

Step 09 : Add Some Sample Data To The Table To Test The Data Source.

Step 10 : Add Data Source To The Data Grid View.

Step 11 : Select The Source Type As Database.

Step 12 : Choose The Database Model

Step 13 : Choose The Connection

Step 14 : Give A Name To The Connection

Step 15 : Select The Table And Columns You Want To Show.

Now You Will See The Data Grid As Below.

Now Run The Project And Check The Output.

Hope You Got The Basic Knowledge And Enjoyed.

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);
        }  
}

Import Excel File To A DataGridView in C#

In a windows form application sometimes you may want to import a excel file to a data grid view. In this blog post Ill demonstrate to you guys to do that. First of all open a new project and name it as you want. Design the form simple as you can with a Text Field, Button and a Data Grid View like shown in the below image. 

I've named my project as "ImportExcelFileToDataGridViewinCsharp". I've added the Text Field, Button and a Data Grid View. Just make sure you have renamed the properties as below to follow some coding standard which I followed in the code. 
     * TextField Name as 'txtFilePath'
     * Button Name as 'btnLoad' and Text as 'Load Excel'
     * DataGridView Name as 'dgvExcelData'

We need another item to do this task. That is OpenFileDialog, which uses to select the relevant file from windows explorer. You can find it in the toolbox.

Now lets do the coding part. Actually what you should happen is when we click the button we need to open the "Open File Dialog" window to select the file. To do that double click the button you will get the button click event. The final code will looks like below.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42

using System; using System.Windows.Forms; using System.Data; using System.Data.OleDb; namespace ImportExcelFileToDataGridViewinCsharp { public partial class Form1 : Form { public Form1() { InitializeComponent(); } private void btnLoad_Click(object sender, EventArgs e) { OpenFileDialog ofd = new OpenFileDialog(); System.Windows.Forms.DialogResult dr = ofd.ShowDialog(); if (dr == DialogResult.OK) { txtFilePath.Text = ofd.FileName; loadExcelToDataGrid(ofd.FileName); } } private void loadExcelToDataGrid(string strFilePath) { string sheet = "Sheet1"; String strConnectionString = @"Data Source=" + strFilePath + "; Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=Excel 12.0;"; OleDbConnection con = new OleDbConnection(strConnectionString); con.Open(); OleDbCommand cmdSelect = new OleDbCommand(@"SELECT * FROM [" + sheet + "$]", con); OleDbDataAdapter daCSV = new OleDbDataAdapter(); daCSV.SelectCommand = cmdSelect; DataSet ds = new DataSet(); daCSV.Fill(ds); dgvExcelData.DataSource = ds.Tables[0]; con.Close(); } } }

One main thing to be note in the above code. You can see in the private method "loadExcelToDataGridView" I have assigned the "Sheet1" to the string variable sheet. This is the name of the excel sheet which you are going to upload. Simply you can use this code and improve it to capture more sheets as well as giving a selection to the user to select the sheet. 

Sometimes if you run the project in a new version of visual studio you may get the below exception. That means you need to install the 2007 Office System Driver: Data Connectivity Components.

Download Source Code

Hope you got what you are looking for.