0 Replies - 4008 Views - Last Post: 07 July 2016 - 01:03 PM

#1 andrewsw  Icon User is offline

  • lashings of ginger beer
  • member icon

Reputation: 6340
  • View blog
  • Posts: 25,565
  • Joined: 12-December 12

Basic Binding of a Database Table to DataGridView and Updating

Posted 07 July 2016 - 01:03 PM

This snippet demonstrates displaying database data in a DataGridView and then updating changes back to the database.

A DataAdapter is used to Fill a DataTable with data from a SQL Server database table. (Any other database would work in an almost identical way.) The DataTable is then bound (using .DataSource) to a DataGridView. A button then uses the adapter to Update changes back to the database.

This snippet provides an overview of the process as it is a common question here @DIC. A fuller tutorial (using a BindingSource) is found here:

How to: Bind Data to the Windows Forms DataGridView Control

I stress that I am providing an introduction to this subject, not a robust code-model to follow explicitly. Much more should be added to abstract a data model and provide robust error checking and handling.

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient; // need this, or another client
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace BasicBinding {
    public partial class frmBasicBinding : Form {
        private string _sConn = @"Server=.\SQLExpress;Database=StaffDB;Trusted_Connection=Yes";
        private SqlConnection _conn;
        private DataTable _staffTable;
        private SqlDataAdapter _adapter;

        public frmBasicBinding() {
            InitializeComponent();
        }

        private void frmBasicBinding_Load(object sender, EventArgs e) {
            try {
                // define the connection using the connection string
                _conn = new SqlConnection(_sConn);
                // configure the adapter
                _adapter = new SqlDataAdapter("SELECT * FROM Staff", _conn);
                // use CommandBuilder to generate INSERT, UPDATE and DELETE commands
                SqlCommandBuilder builder = new SqlCommandBuilder(_adapter);
                // create a DataTable..
                _staffTable = new DataTable();
                // use the adapter to Fill the DataTable
                _adapter.Fill(_staffTable);
                // configure the DataGridView
                dgvStaff.AutoResizeColumns(DataGridViewAutoSizeColumnsMode.AllCells);
                // bind the DataTable to the DataGridView
                dgvStaff.DataSource = _staffTable;
            } catch (SqlException ex) {
                MessageBox.Show(ex.Message);
                // do something with the exception!
            }
        }

        private void btnUpdate_Click(object sender, EventArgs e) {
            // update changes to the database
            _adapter.Update((DataTable)dgvStaff.DataSource);
        }
    }
}

Note that for a simple table with a primary key (as I have here) it is only necessary to instantiate a CommandBuilder in order for the necessary INSERT, UPDATE and DELETE commands to be created for us, allowing .Update to reconcile changes back to the database.

Is This A Good Question/Topic? 1
  • +

Page 1 of 1