.NET Array Dictionary List String 2D Async DataTable Dates DateTime Enum File For Foreach Format IEnumerable If IndexOf Lambda Parse Path Process Property Regex Replace Row Sort Split Static StringBuilder Substring Switch Tuple WindowDataGridView displays data from SQL databases. This tutorial takes a specific table from a database and display it on a DataGridView. This is done with a DataAdapter and data logic. A visual representation of data is the end result.
Start. In Visual Studio, select the File menu and then New Project, and select a Windows Forms application. Next, in the designer, drag the DataGridView icon into the window. On the right part of the window, we see the Visual Studio Toolbox.Visual Studio
And: The Toolbox contains the icons and controls you can add. Select View and then Toolbox to show it.
Databases. Here we need to generate an SDF file to interact with. We will use Visual Studio for this part. First, go to Data and then Add New Data Source. This is the Visual Studio wizard that you can use to add a new database.
Then: Select Database, and click Next. Click on New Connection, as we need to create an all-new connection and database.
Create database. Type in a database name, and then click Create. Note that we are using SQL Server CE, which is the compact edition of SQL Server 2005. You will use different dialogs to configure SQL Server 2005.
Note: You will get the connection string at this point. The one given to me is as follows.
Next: You will see the "Save the Connection String to the Application Configure File" dialog. Save it as DataConnectionString.
Create table. The database should have some tables containing row data. To keep this document as simple as possible, I create an example table. First, open Server Explorer. The Server Explorer is a pane in the right side of Visual Studio normally.
Select your SDF database, and right click on the Tables folder. And then type in the table name. For my example, I use a table name of "Animals". Type this in the Name text box in Visual Studio.
Next steps. Click in "Column Name" cell. Click in the empty cell under Column Name and type your column's name. I create a column named "Weight" with a data type of Numeric. To complete this step in the tutorial, click OK.
Data. Next, we need to have actual data in the table we created. Return to the Server Explorer, and right-click on the table, such as the Animals table. Type in 10 for Weight, and brown for Color.
Note: This data is for the example, but your program may have similar fields. We could be working on a table for a veterinarian's office.
Color: WhiteWindows Forms. At this point you have a special database as part of your C# Windows Forms program. It has one table and four rows, and you want to use a DataAdapter with your DataGridView. We must work on integrating it further.
Adding directive. Before you add logic, add the SqlCeServer directive at the top of your Windows Forms program's code. When you use different database engines, you will need different directives.
Using directive: C#
using System.Data.SqlServerCe;Data adapter. In C# code, you must open a connection to your database first, and then create a DataAdapter. There are different adapter implementations, but they all work similarly. For this tutorial, we will use the SqlCeDataAdapter.
Note: You are likely not using this exact database provider, but the code is the same for SQL Server 2005 and many others.SqlClient Tutorial: SqlConnection, SqlCommand
Tip: Make sure your program has the using System.Data and using System.Data.SqlServerCe directives at the top.
C# program that uses SqlCeConnection
public partial class Form1 : Form
// Open connection
using (SqlCeConnection c = new SqlCeConnection(
// Create new DataAdapter
using (SqlCeDataAdapter a = new SqlCeDataAdapter(
"SELECT * FROM Animals", c))
// Use DataAdapter to fill DataTable
DataTable t = new DataTable();
// Render data onto the screen
dataGridView1.DataSource = t;
}The example calls FillData after InitializeComponent, which is autogenerated by Visual Studio. Look at the FillData method, which is where we put the database contents into the DataGridView.
Step 1: It opens a connection to the database. We use Properties.Settings.Default.DataConnectionString, which was autogenerated.
Step 2: It uses a new DataAdapter: SqlCeDataAdapter. A DataAdapter specifies a command that directly fills a DataSet or DataTable.
Step 3: It assigns the DataSource in the DataGridView, which renders the contents of the database onto the screen.
DataSet, DataTable. The DataSet object available in System.Data has a slightly different purpose than DataTable, as it allows more information to be stored. For this tutorial, however, DataTable is adequate.DataSet
The DataSet consists of a collection of DataTable objects that you can relate to each other with DataRelation objects. You can also enforce data integrity in the DataSet...DataSet Class: MSDN
The DataTable is a central object in the ADO.NET library. Other objects that use the DataTable include the DataSet and the DataView.DataTable Class: MSDN
Improvements. The default DataGridView appearance and functionality is not usually the best. Here we make some tweaks to the DataGridView to make it more pleasing. Appearances are important—let's improve them.
First improvement steps. Expand and anchor the DataGridView. Use the Anchor property on the DataGridView in the designer view to "pin" it to each edge of your window. Go to Properties, and then Layout, and then Anchor.
Appearance steps. Change the background. Usually, developers need to change the backgrounds of the DataGridView. Make it white by changing the Appearance and then BackgroundColor from AppWorkspace to Window.
Also: Hide the row headers. Go to Appearance and then RowHeadersVisible and change it to False.
Altering the selection mode option. Change the SelectionMode property. There are several SelectionMode enums: CellSelect, FullRowSelect, FullColumnSelect, RowHeaderSelect and ColumnHeaderSelect. Change this to FullRowSelect.
Tutorial progress: What we have so far is a single window with a DataGridView that expands, rows that are selected the entire way across.
And: The data from our database is now being fully displayed from a DataAdapter.
Add columns. With the DataGridView, you can predefine columns for display. This doesn't display anything. You create the rules by which the actual data will be displayed. This allows you to have the first column have a width of 200px, for example.
Note: For the tutorial, we want the Animal table's Weight column to be 110px wide.
First column steps. Go to Properties and then locate Columns. This shows the Edit Columns dialog box. Click on the "Add" button. Change the Unbound column. An Unbound column is one that is not directly linked to a DataSource.
Tip: Sometimes it is easier to directly use the DataSource property on the DataGridView.
Next, change the header text. The text you type into the Header text box will ensure that the text is always shown. This means you can display a different form of the column's name than is in the actual DataTable and database.
Note: For the example, I change the Weight cell header to "Weight". This ensures users will know to use pounds, not kilograms.
Finally: Change the properties of the DataGridViewColumn in the dialog box. I set 110px as the width and AutoSizeMode of None.
DataPropertyName. You need to specify that a certain column in your database be inserted into the Column you just added in the Columns dialog box. To do this, you must assign the DataPropertyName of the column to the column name from your database.
So: To specify that your DataGridViewColumn be used for the Weight column for your database, type "Weight" into the DataPropertyName box.
Alternating colors. For usability, you want to have alternating row colors on your DataGridView. Fortunately, newer versions of the .NET Framework have the AlternatingRowsDefaultCellStyle attribute. Click to change that property.
Tip: Change the BackColor to something your users will enjoy, and that will improve the program's usability. I chose aqua.DataGridView Row Colors: Alternating
Discussion. Here I just apply some more tricks to improve the appearance and usability. At the top you can see the final result from this tutorial. First, I added another column property. I added another column template for the next column.DataGridView Columns
Next finishing touches. I changed AllowUserToAddRows. This eliminates the blank row on the bottom of the DataGridView. I changed the BorderStyle. I choose to use the best style for Vista here and chose Fixed3D.
Tip: The other border, FixedSingle, looks better in Windows XP. The None option might be good for some programs.
Column header styles and fonts. I changed the ColumnHeaderBorderStyle. For Windows Vista, we use None for a good appearance. For completeness, the enums are Custom, Single, Raised, Sunken and None.
Note: My experience is that none of them look really good on Vista. I changed the Font.
Also: I modified some properties of the entire program, such as the form's Text.
DataGrid. WPF is a newer framework for building Windows programs with C# code. Instead of the DataGridView control, it provides a DataGrid control. The DataGrid is used in a slightly different way, but many principles are the same.DataGrid
Summary. We used a database and displayed its contents in a usable DataGridView. This is critical for many data-driven Windows Forms application. The example here could form the foundation of a data-driven program for a vet's office.
Therefore: When a dog-owner arrives and his dog is sick, the vet employee could record the species, weight, and color of the dog.