Populating Datagrid from the database using minimum means with Visual Studio 2008

The object model of .Net can be overwhelming at times – so overwhelming that quite trivial tasks often take hours of research. In this case, the task at hand was to populate the datagrid from SQL Server database in Visual Studio 2008 Windows application.

Google search of course returns code samples; so does Visual Studio Help; however I did not find a single sample that wouldn’t create some objects in code. This got me curious: during the design, Visual Studio GUI creates several objects facilitating this data extraction, so how come we need to create even more in code? Let’s try to do it without any.

For this example, we create a Windows form, and place a textbox and DatagridView control on it. We will need to populate it from SQL Server database using stored procedure with one parameter, which value will be taken from the textbox - very typical task. For our sample (based on real life shipping application), we need to retrieve serial numbers of the units assigned to a given packlist. The user will enter packlist number, press the button, and receive the list of the serial numbers of the units he needs pull from the shelf and ship to the customer. The stored procedure in SQL Server is

Create procedure packlist_sn(@packlistid int) as 
                      select sn from serialno where packlistid=@packlistid
                      go
                      

Select all Open in new window

Of course you can simply use one of your own procedures in this example. Or, if you want to follow along, then to create the table for the Stored Procedure with some sample data you can use the following code :

create table serialno (packlistid int, sn varchar(20))
                      insert serialno values (1,'sn1')
                      insert serialno values (1,'sn3')
                      insert serialno values (1,'sn5')
                      insert serialno values (2,'sn2')
                      insert serialno values (2,'sn8')
                      

Select all Open in new window

Here’s the initial view of our form, with the datagrid occupying the lower part.


The next step is to specify our stored procedure as the data source of the datagrid. We achieve it by specifying the property “Datasource” of the grid. We select “Add new data source” and follow the wizard to specify our SQL Server database, and within it, our stored procedure packlist_sn . Once we are finished, we find our Datagrid complete with the column sn that will be returned by the stored procedure, plus, there are now 3 new components added to the form – dataset, binding source, and table adapter.


After having completed the wizard, in the Datasource of the datagrid we find PacklissnBindingSource, which is one of the generated components. In turn, PacklistsnBindingSource has its own DataSource which is another generated component  - DbDataset. The 3rd generated component, Packlist_snTableAdapter, is in fact a public component of DbDataset. It serves as a bridge between this data model and the database, and in its properties we will find SQL Server connection string as well as SQL commands for data manipulation – select, update, delete, and insert. All they are available through the constructor:







Note that at the above picture, only SelectCommand is specified, while InsertCommand, UpdateCommand, and DeleteCommand are not. This is correct and desired, since we indeed want only to retrieve the data, and we are not going to edit them in the database.

So, our datagrid is based on the dataset dbDataset, which Visual Studio has automatically craeated and added to the project (dbDataSet.xsd in the project tree above), and the dataset will be populated from the SQL Server database.

At this point, it’s good idea to verify that everything is working correctly: click on the small right arrow button in the upper right corner of the datagrid, select “Preview Data”, and upon manually entering our parameter of the stored procedure, we can see what data is about to be returned to our application.


In the application, the dataset, and consequently the grid, won’t be populated automatically by the adapter; we need to provide the code, which among other things will provide the value of the parameter. This is where the many ways to achieve that mentioned in the beginning of the article come into play. Arguably the shortest of them all, and not involving any new objects besides those already created, is the following code:

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
                         Packlist_snTableAdapter.Fill(DbDataSet.packlist_sn, TextBox1.Text)
                      End Sub
                      

Select all Open in new window

In fact, this one line of the code has been the essence of this article. And it works!


This solution also will work in compact application, without any changes (we tested it in Windows Mobile 6), so long as you are connected to your network. The only nuance will be the SQL Server connection string, that is, what needs to be specified as server\instance for the compact application to connect. Instead of NAME\INSTANCE, we will need to specify IP address (depending on the network connection of the mobile device); also, we need to explicitly specify SQL Server's listening port number, which is 1433 by default. This is not necessary in desktop Windows, but without the port number, Windows Mobile 6 will return "Invalid Connection".

For example, when testing the compact device, connected by USB connection, the connection string that worked was :

Data Source=169.254.2.2,1433; (other parameters as usual)


With this device in hand, our shipping department employee will have much less chance to forget the serial numbers on his way from the application screen to the shelf.