Friday, March 6, 2009

For each & every database driven web application development based on asp.net, no one can avoid the asp.net powerful control GridView because of its set of builtin capabilities & a long list of extensions & improvements. The GridView control provides us the infrastructure for in-place Editing & Deleting. But for Insertion its left for developers(strange). Here i am showing how you can Insert/Update/Delete data using GridView with minimum coding. You can implement Update/Delete without writing a single line of code thats why at first i am showing Update & delete operations & in the later section i will describe how to Insert.

Atfirst we need to create a table in SQL Server database. For this example i use the below table named tblSupplier:

Now open a new asp.net application. To connect to the database we need to make a connectionstring first. So open the web.config file and add your connectionstring. The connectionstring looks like:

You can change the order of the column by rearranging the BoundField from page source code. Now our page is ready to run. Run the page. Now you can Edit & Delete the data from GridView. Since we don't implement Insert operation yet so insert few data into the Database table then run the application otherwise you can't do the edit/delete operation. The page now looks like:

Now you can Edit & Delete data from your page which rquires just few clicks.

Now Inserting. It requires few tricks to implement. First we need to modify our GridView code like:

Where each column implemented under Template Column. It gives us another opportunity to rearrange the GridView like Header Column, Column Ordering, Prompt user before Deleting a row of the GridView by confirm() method of javascript etc. Since we supplied Edit, Update, Cancel, & Delete command CommandName property to Edit,Update, Cancel, & Delete respectively into the GridView declaration to cooperate with SqlDataSource to enable users to Update and Delete data without any further implementation. Now look at the FooterTemplate of each column. This is because we are using FooterTemplate for our Insert operation. Add FotterTemplate for each column by server side control based on datatype. Since tblSupplier contains each column of datatype string so i need four TextBox here. In the last Template column headed by Action I added a Button with CommandName property=New. Now when you run this application what you see?

Look at the FooterTemplate of the GridView. Now we need to bind four TextBox data with SqlDataSource after clicking on the Add Supplier button. So first we need catch the click event of Add Supplier button. Gridview gives us an event to resolve such type of problem. The event name is RowCommand. Now we need to implement the RowCommand. But questions are how we can identify which button user pressed to fire this event? Answer is CommandName property. So the GridView1_RowCommand() method code is:

The CommandName "EmptyNew" i will discuss later in this post. To add SqlParameter into the SqlDataSource i need to use four SqlParameter(or you can use List collection) variable. Declare the below four variables within page scope.

When i catch the event for Add Supplier button then populate those parameters & fire Inserting event for the dsSupplier SqlDataSource object. Keep in mind that we must override the Inserting handler to assign our data into the SqlDataSource. So add below code into your page:

Now everything is completed but we can do nothing if the table has no row in database. Gridview show nothing. The solution is EmptyDataTemplate. See the above GridView HTML code. Here i also added another Button which CommandName is EmptyNew. Hope now you can understand why i implement the GridView1_RowCommand in this way.