Introduction
As with most things I do, my purpose is not obtaining the result but understanding the journey. What I want to do write a winform so that I can
· Investigate the properties of the DataGridView control in Visual Studio 2005.
· Configure the TableAdapter object so that the DataGridView control shows values from a database.
· Use the DataGridView control to navigate and update the database.
The actual database I use is not that important. I chose to write a simple database in Access that records my household budget. Access for me is a comfort zone, but I think the database could just as easily be in SQL Server 2005. I may later migrate it to SQL Server 2005, but right now my interest is in the DataGridView control and not in the database software.
It turns out you can get an application running by just dragging and dropping and configuring property screens. You don’t really have to write any code at all. It’s designed to be “quick and easy.” But here’s a quote from Pro ADO.NET 2.0 by Sahil Malik. “… your application is just as good as the amount of effort you put into it. You can’t expect drag-and-drop applications to help you create a well-architected enterprise-level application …” The drag-and drop method is good for prototyping, and you can learn a bunch by reading the auto-generated code.
The Database
Here is how my database looks initially. I may expand on it as I develop my application, adding entries to tables, adding new tables, setting relationships between the tables.
Building the Winform
Just make a winform application. And click on Add New Data Source …
Then, click Next>.
I already had a connection from a previous attempt at this, and so I chose it. Click Next>.
Click Next>. The connection string gets saved in app.config.
Select Tables. I chose not to select Views. I don’t know how easy it would be to add them later. Hopefully, one would not have to start from scratch.
Click Finish. You now see a Data Source window with the generated DataSet.
Select Checks and drag it onto the form You see a DataGridView. Notice the navigation controls at the top and the components in the IDE footer.
You can now build and run the winform, and this is what you see. Note that CategoryID, Location, and BankID appear as integers. This is not what I want. I’d like to have a lookup table in a dropdown list.
Back in Visual Studio, select the DataGridView and expand the Smart Tag. Then click Edit Columns …
Do the following for the three columns: CategoryID, Location, and BankID. Highlight the column in Selected Columns:. On the right, in Bound Column Properties,
· Go to Design à ColumnType. Drop down the list for its value and choose DataGridViewComboBoxColumn.
· Go to Data à DisplayMember. Drop down the list for its value and choose Name.
· Go to Data à DataSource. Drop down the value list. Expand Other Data Sources. Expand Project Data Sources. Expand ExpensesDataSet. Select the table corresponding to the column you originally selected. This is Category, Bank, or Location.
· Go to Appearance à HeaderText. Type in the value field what you want to appear in the header of the table.
For each column that you do this to, you get two more components in the IDE footer.
Do it for the Category column and you get categoryBindingSource and categoryTableAdapter.
Do it for the Location column and you get locationBindingSource and locationTableAdapter.
Do it for the Bank column and you get bankBindingSource and bankTableAdapter.
Now I want to add a textbox that also binds and moves with the TableAdapter navigator. In the Data Sources pane expand ExpensesDataSet and then expand Category. Drag CategoryID onto the form.
Then, select the textbox, right-click and choose Properties. In the Properties window, expand (DataBindings). Select Text. Drop down the value and select CategoryID under checksBindingSource. Notice that the CategoryID changes as you navigate through the table.
Also, note that Update works. Select a record. Double-click in a field. Edit the field. Then, click on the diskette symbol on the toolbar. Check that the record is updated either by looking in Access itself or by exiting the winform and reinvoking it.