DataGridView - Insert, Update, Delete and Retrieve records using stored procedure

Article is about DataGridView control that shows some basics data operations like inserts, updates, and deletes using stored procedure.

Introduction

DataGridView control is the most popular amongst Windows Forms developers. Control helps to display data in a tabular format. The DataGridView control allows viewing, inserting, updating, or deleting data in a db table. Here am trying to show a simple example, how to retrieve, add/update or delete records using DataGridView. 

Getting Started

Before getting started this sample uses Northwind database and Visual Studio 2008. 

Download Northwind database: (http://blogs.msdn.com/b/smartclientdata/archive/2005/11/02/488258.aspx)

Following namespaces used:

using System.Data;

using System.Data.SqlClient;

using System.Configuration;

This sample does not using any table from Northwind, but created a simple table named (dbo.UserMaster). It's a simple User Master Table using for saving First Name, Last Name, City and Phone Number. Also, contains one identity column (SNo). Stored Procedure using [dbo].[UserDetailsSummary].

 

Now create a windows application project, drag and drop DataGridView control to Form window. Set true for "Enable Adding", "Enable Deleting" and "Enable Editing" from DataGridView designer. This will enable control for editing, adding, deleting records. Add a checkbox control into DataGridView which uses as a selection for deletion of records.

The Code

Form contains three buttons: 

Retrieve, Add/Update (Enabled False) and Delete (Enabled False).

btnRetrieve_Click: Retrieving records from db table which uses stored procedure by passing input parameters (method used: AddParameteres) which check for records exists. If found returning all the rows from User Master Table or if not returning a dummy row. If more than one row found, buttons Add/Update and Delete property changing to Enabled True.

dgvUsers_DataBindingComplete: Once DataGridView data binding completes, this events call to set column (SNo) property readonly to true. This will helps to disable editing of identity column.

btnAddEdit_Click: Event used for adding or editing records by looping through the DataGridView control. SqlTransaction used for successful commit or rollback if any error occurs.

btnDelete_Click: Event used for deleting records depending upon checkbox selection of DataGridView control.

Summary

Code samples attached also with sql scripts. Test the code and post your comments. There will be different better ways of doing these DataGridView operations. If anyone interested in making an article, please come forward and share the knowledge.