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.
Data from DataBase to/into dgv using DataTable
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
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.