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


This article is about DataGridView control that shows some basics data operations like inserts, updates, deletes 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].

1

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.

2

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.


Attachments

  • DataGridView (41605-14138-DataGridView.zip)
  • Comments

    Guest Author: Mike02 May 2012

    This article is real good and awsmeoe. I dont know how to work with LINQ. Your article has made my learning easy and its damm good. Thanks for your beautiful and explanatory article. need a small info like, in your explanation you have explained about getting or inserting values, At times we will need to get values after lot of filtering and conditions that will be handled in StoredProcedures.How to work with Stored procedures here. Should we write the whole code that we write in stored procedures in the Data Access Layer?Also can you provide me with a link that explains about the syntax to be used while coding using LINQ.

    Author: Suthish Nair05 May 2012 Member Level: Bronze   Points : 0

    Am no more supporting dotnetspider. You can reach me at www.c-sharpcorner.com for articles and forum support. Soon going to delete my profile from dotnetspider.

    Author: vijay24 Jul 2012 Member Level: Bronze   Points : 0

    Thanks it helped me.
    can you please contact me then only I could contact you. I have few more questions for you. Since i'm registered just now, I couldn't do that.
    Hope to hear from you soon.



  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name:
    Email: