|
The main objective of this article was to describe the DataGridViewComboBoxColumn of Visual Studio 2005. This component allows you to display a dropdown list (combo box) as a column in a DataGridView. Before I get started with the DataGridViewComboBoxColumn, I'll first take you through the initial bits which is getting a DataGridView to display data from your database.
If you are comfortable with the DataGridView component, read the next article
Using the DataGridViewComboBoxColumn to display a dropdown list in C# (Visual Studio 2005)
Downloads
Visual Studio 2005 (Beta) project (Excludes the database) - 21KB
MS Access database - 8KB
Download and unzip the project file into your Visual Studio projects directory. Then unzip the MS Access file into the bin\debug directory of your new project. Open the project in Visual Studio and run it.
MS Access Database
I will be using an MS Access database as datasource. For simplicity's sake, I place the Access database in the BIN\DEBUG directory of my project - My Documents\Visual Studio\Projects\DataGridView-Sample\DataGridView-Sample\bin\Debug, where "DataGridView-Sample" is the name of my Visual Studio C# project.
You may download the database from the Downloads section above or you can recreate it with the following 2 tables.
| People | |
|---|---|
| PersonID | AutoNumber |
| PTitle | Text(5) |
| PName | Text(50) |
| Titles | |
| Title | Text(5) |
Creating the application
Create a new C# Windows.Forms project. In this article we will only be working in Form1, which is automatically created when you create the project.
Open the toolbar and, from the "Data" component set, click on DataGridView.
Click somewhere on your form, which will fill your whole form with the new DataGridView named dataGridView1.
To resize the DataGridView control, click on the control and set its "Dock" property to "none" and drag the control to your preferred size. Now go to the "Anchor" property and select all four options - top, bottom, left, right. This will cause your DataGridView control to size with your window, if that is what you want.
See an image of the Form Design view of your form at this stage.
Create the database components
You can add all the components required to connect your DataGridView control to your database to the form visually via Visual Studio. However, I want the flexibility to connect to various databases, including MySQL, so I prefer to do this through code.
Next we will go to the form’s code to add the database connection and everything else. We're going to need a FormLoad event, so, double-click somewhere on your form so that Visual Studio creates the Form1_Load() method for you and links it to the Load event of the form. We'll get back to this method later.
For now scroll to the top of the code.
We're going to use OleDb to connect to an MS Access database, so first we'll add a using directive for OleDb.
Click on the + sign next to "Using Directives" at the top of your code and add the following line to the bottom of the list of using directives:
using System.Data.OleDb;
This using directive simply makes available the goodies we need to talk to the database through OleDb. So Let's declare the goodies as variables (components) within our class. Directly after your class declaration, add a declaration for an OleDbConnection, OleDbDataAdapter and DataSet
OleDbCommand
You could also declare an OleDbCommand up here, but I prefer doing that in a method so that I can override the method if I wish to reuse this form through inheritance later.
This method returns an OleDbCommand, so we will use this method as the select command for the OleDbDataAdapter name dbDA. Note that I have declared the getSelectCMD method as "protected virtual", which means that if you were to inherit from this form, its descendents could override this method. For this example you could have just made it "private". The OleDbCommand simply defines the SQL statement for a particular action, in this case selecting the data from our database.
The rest of the code is all within the Form1_Load method. In the following block is all the code required for the Form1_Load method. If you replace the empty Form1_Load method in your project with this code, you should be able to run your application and see the data from the Access database.
The connection String
First we declare a string, strConn, which contains the connection string to the Access database. Because my MDB file is kept in the BIN\DEBUG directory of my project, I do not have to qualify a full path in the "Source=" section. This will only work while you are debugging through Visual Studio. If you compile and run the application standalone, you will have to move the database to the same directory as your executable.
Making the connection to the database - the first try-catch block
Here we create an instance of dbConn, our OleDbConnection object and we tell it to use strConn as it's connection string. Then we open the connection. I know my exception handling is not very extensive, but we're not dealing with exception handling in this article. At least the Messagebox will give you some idea what's wrong while debugging.
The OleDbDataAdapter Class
In previous languages I have worked with, I was used to a database connection and some alternative to a dataset.
The OleDbConnection is your connection to the database and is the road by which all communication with your database travels.
The dataset is a copy of the actual data within your application. In other words, if you select 10 rows from a table in your database, all the data you selected will be available within the dataset. I know this is a very simplistic view, but the fact is that the dataset contains the actual data.
In a language such as Classic ASP (before Dot Net), you only worked with the connection and a recordset (which could be compared to a dataset - or at least a table within a dataset).
The DataAdapter then, in my simplistic mind, is a middle layer between the connection and the dataset. Or, put differently, the DataAdapter is a wrapper around the database connection. It contains the connection object and a bunch of methods for communicating with the connection.
Having said all that, let's look at how we use the OleDbDataAdapter to get the data into the DataGridView.
Getting the data - the second try-catch block
In the second try-catch block we instantiate the OleDbDataAdapter (named dbDA) and tell it to use the result of the getSelectCMD() method. The OleDbCommand, which is returned from the method, contains the connection to the database.
Also within this block we tell the DataAdapter to fill our dataset with data returned from our select command.
dbDA.Fill(ds1, "results");
The second parameter, "results", is simply a name for the DataTable within the dataset where our data will be kept. A DataSet can hold multiple DataTables, but for our example we're only concerned with one.
Displaying the results
We have now connected to our database and retrieved the data that we require.
This data is available within the ds1 DataSet, so now we simply have to tell our DataGridView control to use this data. This is achieved in the following 2 lines of code.
The final line of code is optional. Because the PersonID column in the database is an "autonumber" column, there is no need to display it to the user, so we simply hide it by setting its Visible property to false. This column, however, is the primary key for our table, so we will need it to update the database if the user makes any changes.
Close up shop
There is one final step outside the Form1_Load method. Simply add the following destructor to close your database connection.
You should now be able to run your application and see the data from your MS Access database.
Conclusion
This article was only an introduction to the DataGridView in Visual Studio. It shows one simplistic method of getting a DataGridView to display data from a database. In the next article, we will build on this example to display a dropdown list (Combo box) in the title column by using a DataGridViewComboBoxColumn.
About the author
Dirk Wessels is a software developer and owner of www.getquotes-it.com. You may redistribute this article, but please keep this “About the author” section within the article together with an active link to www.getquotes-it.com