Data from DataBase to/into dgv using DataTable

Mitja Bonca 0 Tallied Votes 694 Views Share

Code shows how to bind the data from a database`s table to dataTable and populate dataGridView with it.
And then how to pass the modified (or not) data from dataGridView back to dataBase`s table.

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Configuration;

namespace Feb02DataBase_DataTable
{
    public partial class Form1 : Form
    {       
        public Form1()
        {
            InitializeComponent();
            PopulatingDGV();
        }

        private void PopulatingDGV()
        {
            dataGridView1.AllowUserToAddRows = true;
            dataGridView1.RowHeadersVisible = false;

            BindingSource bs = new BindingSource();
            bs.DataSource = DAL.GetDataFromDB();
            dataGridView1.DataSource = bs;
            dataGridView1.AutoResizeColumns();
            dataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill;
        }

        private void buttonSaveDB_Click(object sender, EventArgs e)
        {
            BindingSource bs = (BindingSource)dataGridView1.DataSource;
            DataTable dt1 = (DataTable)bs.DataSource;
            DAL.UpdateDataInDB(dt1);
        }
    }

    public class DAL
    {
        private static string connString = ConfigurationManager.ConnectionStrings["Feb01TestConnectionString"].ConnectionString;

        public static DataTable GetDataFromDB()
        {
            DataTable table = new DataTable("customers");
            using (SqlConnection sqlConn = new SqlConnection(connString))
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    cmd.CommandText = "select_GetCustomers";//SELECT * FROM Customers
                    cmd.CommandType = CommandType.StoredProcedure;  //if you use CommandType.Text;
                    cmd.Connection = sqlConn;
                    using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                        da.Fill(table);
                }
            }
            return table;
        }

        public static void UpdateDataInDB(DataTable table)
        {
            using (SqlConnection sqlConn = new SqlConnection(connString))
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    cmd.CommandText = "select_GetCustomers";
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Connection = sqlConn;
                    sqlConn.Open();
                    using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                    {
                        using (SqlCommandBuilder cb = new SqlCommandBuilder(da))
                        {
                            da.UpdateCommand = cb.GetUpdateCommand();
                            da.Update(table);
                        }
                    }
                }
            }
        }
    }
}

Hope it helps,
Mitja
manugm_1987 0 Junior Poster in Training

make use of insert query .... insert into tablename values(1,2,3) ..... pass the value of datagridview column in the query and use da.update(dataset,tablename)

j111 0 Newbie Poster

if using cmd.CommandText = "select_GetCustomers" in method UpdateDataInDB,
at the line below,
da.UpdateCommand = cb.GetUpdateCommand();
you will get In InvalieOperationException and get message below,
"Dynamic SQL generation for the UpdateCommand is not supported against a SelectCommand that does not return any key column information."

So codes in UpdateDataInDB won't work! Can you give further help?

tomason 0 Newbie Poster

I suggest to use a C# data export component Spire.DataExport, I use it for a long time, quit good. Now it provides a free version. It supports importing/exporting from database to XLS, PDF, Word and so on. May help to you. More information:
http://www.e-iceblue.com/Download/download-dataexport-for-net-now.html

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.