Introduction

The article, or rather code snippet, demonstrates a simple application to insert, update, and delete using a DataGridView. The application uses an asynchronous architecture for most of the calls to the database. This is to show that without a hanging UI, we can allow the user to continue with his tasks. The application has the following outline:

Get all SQL Server instances from the network.

Get all databases from the selected instance.

If the user provides an empty user name or password, or a wrong user name or password, the same list of SQL Server instances will be returned. The code is available here[^].

Get all tables from the selected database.

Get all records from selected table.

Add, edit, delete records from the DataGridView.

A paging feature with number of records per page is also provided.

Asynchronous architecture

The application uses an async calling mechanism to make database calls. As the SQLEnumerator does not support async calling, I have added delegates to call those methods asynchronously. In .NET 2.0, the SqlCommand object supports async calling to a database. I am using this feature to get all the tables from the selected database. Delegates are the best practices to design async architectures because most of the things are internally handled by the CLR, and we do not have to bother much about them.

Application structure

When we start reading the code from the beginning, we can see that there is an enum named CallFor. This is used to set a private variable called when making calls to a SQL Server list, databases, and tables. This is done because only one call back method handles all the callbacks from asyn calls. A switch case statement manages the behavior of different callbacks. I have designed this application using a SqlCommandBuilder as I have some queries regarding how to use SqlCommandBuilder. The builder will automatically generate the insert, update, and delete commands, provided that you select the primary key in your Select query. I have faced a very common problem of cross thread exceptions. But, in my previous project, we implemented the same architecture, and .NET 2.0 provides InvokeRequired and the Invoke() function to overcome this problem. We have to declare a delegate with a similar signature as the callback method and call the same method using the control's Invoke() method. This will push all the call stack to the parent thread from the executing thread, and put parent thread to work. You need to follow a sequence like:

Get all SQL Server instances.

Get all databases from the selected instance.

Get all tables from the selected database.

Load data from the selected table.

Use paging to navigate.

Add buttons like Add/Update, Commit, Delete to insert/update or delete. You can delete/update/insert multiple records.

Here are some code blocks I'll explain. This function sets the database objects required throughout the application. The sqlQuery is dynamically built.

The two functions below load the data and bind it to a DataGridView. I was trying to bind a temporary DataTable object to the DataGridView and then update the main table. But, I was not able to do so. I used the adapter's Fill() method which takes a start record and the number of records as input parameters with the DataSet. I created a temporary DataSet to get the total records. I dispose it immediately. Instead of directly binding the data source to a DataGridView, I prefer to add columns manually and then let the rows to bind to them. This allows sorting, and in case we do not want to show any columns, we can do it here.

Here is a sample method which gets SQL Server instances asynchronously. After BeginInvoke(), the user is free to perform any task. The callback function will catch the response. This is the way you can invoke a method asynchronously using a delegate.

Here is the callback method that will handle all the callbacks from the different methods. When you use an async architecture, the callback is always on a new thread other than the parent one. This new thread will not able to access the controls on the parent thread. Hence, we need to shift the call stack to the parent thread, and this can be done using control.InvokeRequired and control.Invoke(). The following method shows how to do that.

Conclusion

This way, we can easily manipulate a DataGridView. The only this is we have to use the data source that is directly bound to the DataGridView. Async calling will be very efficient while loading huge data. Please let me know if you have any queries.

Share

About the Author

Dear Friends,I'm from Pune and currently working with Symantec. I'm having 7+ yrs of software development experience and I'm working on .NET since 6+ years.I'm a Brainbench Certified Software Engineer in C#, ASP.NET, .NET Framework and ADO.NET.

Comments and Discussions

i am design a bill system and i want to show bill wise data in gridview but know i fetch data one by one my problem is that i need all bills data in a single gridview .know i enter 1 bill no and save the data to excel but i want to do that if i enter i bill no my data is shown on grid and when i add next bill my data save under the previous data and so on so i dont have to compile the excel workbook to get a hole day bill details

Nice article, but I have a question. Do u know any way to perform the paging from within the database? The only solution I've found (with postgresql) is to use the database syntax, but from exp I haven't seen such a command in sql server

I am a novice in Visual Basic.Net and trying to create a form for voucher entry. I want to use a DataGridView to add/update/delete row containing account code, sub account code, debit/credit and amount. I want to use the datagridview cells for these operations. Please help me out. Thanks in advance. Please let me know for clarifications, if any. My mail id is roopeshmandloi@gmail.com.

As long as I know, based on MSDN, etc, if we use xxxDataAdapter class, we don't need to open and close connection "explicitly". I am very sure about it. So, why would you still called Open() and Close() methods instead? Because I think that the DataAdapter can Open and Close connection to the database automatically. Please tell me if I'm wrong.

Here's my scenario, I want to display the information for a particular sql table in a datagridview. I want the user to select a table, and display certain columns that they select from a checkedlistbox.

ExampleUser selects table "CITY" then they select specific columns from the city table, then what i want to happen is to display the column info in a datagridfirst last name DOBjack daniels 4-4-24

For this you need to get all columns for the selected table from the database. Create check box dynamically as per the number of columns. Now you can either fetch all columns or only selected columns. When you fecth selected column, user might not select the primary key column which is required for edit and delete. Hence, you fetch all the columns and show only selected columns. This will keep you with primary key and other data from columns.--

i want the example program of grid view with edit update insert operations using datareader or datasets i was trying the same but i didnt get update problem is when i am clicking update button, field of that row gets in to textboxes,i dont know what name i should give to the (automatically generated textboxes in runtime )so that i can retrive the datam from the textboxes to the update queryhelp me please

Hi Mr.kulakarni, I read your article. it is very good.if data(More than 1000 record at a time,time may be in seonds) is inserted in sql server table, In the front end grid has to display that without doing an action in the front end.I have to solve this. please help me.

Hi Anjulla,As far as what I understood is that there will be some application that will insert data in sql server and you need to show it in datagridview. So you need to poll database after a fixed time interval and fetch the data from the table and as soon as new records found, add them to the datagridview.

for this i was searching the net for 2 weeks i given what a exact program needed to be explained thank u for ur job .:-OI am a fresher in .net presently i am in need of books for .net 2005 for attending interviews and to do practicals can u sugest any books and sites for me i am begineer in this please help me

Hi manikandan,There are lot of sites where you can get interview questions on all versions of .NET. Just search on google and you will thousands of sites..Please let me know if you need help on some specific topic.

Hello jdkulkarni..Your article is so good, but I have some problems for using datagridview.I have 1 datagrid and 4 buttons.They are add,edit,delete and load data.how can I add,edit and delete directly into the dataGrid?I hope you understand what I'm asking . Could u do some example for me?I think,it is too easy for you.

Hi KTS, Thanks for visiting my article. What you want is already there in the code. The application adds, edits and deletes directly from datagridview and updates in database in the backend. If something more you want then please let me know, I will try to help you.

Hello Jayant,Thanks for your reply.I think,my problem will be too simple for you,.I have to write 2 applications,they are server application(console application) and client application(windows application).The client application have 1 DataGridView and 4 buttons(load,add,edit and delete).The client will connect to server, and then the server will connect to the sqlserver or database.I think, it is need to use .net remoting.But,I can't think, how to program for these applications.If u have any free time,please write some code for me.My email is kyithar82@gmail.com.I am hoping your help. Thanks.Best regards,KTS

Hello Jayant,I have some questions.1.How can I Insert,Update,Delete data to sql server using .net remoting?2.How to program in server side object?3.How to program in host application?4.How to program in client application with dataGridView?Could you please give me some examples.

I'm working on something similar like this. It's a small purchasing and sales application. User will input/update the records of transaction in datagridview. The columns are : item, price, quantity and total. I wonder how to count all the total (in column Total of the datagridview) and put it in a textbox (let's name it : GrandTotal.text) I hope you understand what I'm asking

hi Daniel,Thanks for visiting and downloading my article. What I think is, you should create a datatable, datacolumns and datarows in the codebehind. So when you have created the total column, asisgn the default value as value of price column * value of quantity. And the you can put the value of total column in textbox.Hope this might help you.