C# Data Grid View

At first  use the DataGridView control for displaying data from your SQL database. Display a table from the database on the DataGridView, using DataAdapter and data logic. Here review steps to set up a DataGridView, generate an SQL database, and then render it, using the C# programming language.

Make new Windows Forms application

In Visual Studio 2008, 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 your Window you will see the Visual Studio Toolbox. It contains the icons and controls you can add. Select View and then Toolbox to show it.

                        

Use databases

Here we need to generate a simple SDF file for the rest of the program 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.

Database creation. Select Database, and click Next. Click on New Connection, as we need to create an all-new connection and database. We are not reusing one that already exists.

Create new 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. You will get the connection string at this point. The one given to me is as follows:

Data Source=C:\Users\Sam\Documents\Data.sdf

Following steps. Next, you will see the "Save the Connection String to the Application Configure File" dialog. Select "Yes, save the connection as" DataConnectionString.

Create simple table

The database you are using must 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. Type in the table name. For my example, I use a table name of "Animals". Type this in the Name: text box.

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.

Add data to table in Visual Studio

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. This data is just for the example, but your program may have similar fields. We could be working on a table for a veterinarian's office. Repeat for other rows.

Weight: 10
Color: Brown

Weight: 15
Color: Black

Weight: 5
Color: Green

Weight: 20
Color: White

Set up Windows Forms program

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.

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;

  

Add data adapter code

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. You are likely not using this exact database provider, but the code is exactly the same for SQL Server 2005 and many others. Make sure your program has the using System.Data and using System.Data.SqlServerCe directives at the top. Now, add the following custom FillData method.

SqlClient Tutorial

Program that uses SqlCeConnection [C#]

using System.Data;
using System.Data.SqlServerCe;
using System.Windows.Forms;

namespace WindowsFormsApplication5
{
    public partial class Form1 : Form
    {
 public Form1()
 {
     InitializeComponent();
     FillData();
 }

 void FillData()
 {
     // 1
     // Open connection
     using (SqlCeConnection c = new SqlCeConnection(
  Properties.Settings.Default.DataConnectionString))
     {
  c.Open();
  // 2
  // Create new DataAdapter
  using (SqlCeDataAdapter a = new SqlCeDataAdapter(
      "SELECT * FROM Animals", c))
  {
      // 3
      // Use DataAdapter to fill DataTable
      DataTable t = new DataTable();
      a.Fill(t);
      // 4
      // Render data onto the screen
      dataGridView1.DataSource = t;
  }
     }
 }
    }
}

Description. It 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 created earlier in the tutorial. We use the Properties.Settings.Default.DataConnectionString string, which was autogenerated by Visual Studio.

Step 2. It uses a new DataAdapter, in this case SqlCeDataAdapter. What DataAdapters allow us to do is specify a command, similar to SqlCommand, but use it to directly fill a DataSet or DataTable.

Step 3. It assigns the DataSource in the DataGridView, which renders the contents of the database onto the screen. This is the result of the tutorial so far:

DataSet versus DataTable

The DataSet object available in System.Data has a slightly different purpose than DataTable, as it allows more information to be stored. However, for this tutorial, DataTable is adequate.

DataSet Examples

Understanding 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..."

Understanding DataTable. "The DataTable is a central object in the ADO.NET library. Other objects that use the DataTable include the DataSet and the DataView."

msdn.microsoft.commsdn.microsoft.com

Improve DataGridView

The default DataGridView appearance and functionality is not usually the best. Here we make some tweaks to the DataGridView to make it more pleasing.

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. 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 you can use: CellSelect, FullRowSelect, FullColumnSelect, RowHeaderSelect, and ColumnHeaderSelect. Change the one in this tutorial 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 being fully displayed from a DataAdapter.

Options

DataGridView has a myriad of options and this is both its biggest advantage, and its most frustrating difficulty. You can see many options in the DataGridView property article.

DataGridView Property Notes

Add columns

With the DataGridView, you can predefine columns for display. This doesn't actually display anything, but you create the rules by which the actual data will be displayed. This is ideal for when you want to have the first column have a width of 200px, for example. 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 the bottom left. Change the Unbound column. An Unbound column is one that is not directly linked to a DataSource. Note that sometimes it is easier to use the DataSource directly.

Next steps. Change the header text. The text you type into the Header text: 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. For the example, I will change the Weight cell header to "Weight (lbs.)". This ensures the application's users will know to use pounds, not kilograms. (Please see the DataPropertyName section below.)

Final column steps. Finally, change the properties. You need to change the properties of the DataGridViewColumn in the dialog box that appears. I set 110px as the width, and AutoSizeMode of None.

Change 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. For example, to specify that your DataGridViewColumn be used for the Weight column for your database, type "Weight" into the DataPropertyName box.

Column properties

When using DataGridView you will need to change column properties. The collection of properties available is in the DataGridView Property article.

DataGridView Property Notes

Alternating row 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. Change the BackColor to something your users will like to look at, and that will improve the program's usability. For my example, I chose aqua.

     

Finishing touches

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 repeated the instructions shown in the Columns topic to add another column template for the next column.

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. 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 Vista, the best appearance is None here. For completeness, the enums are Custom, Single, Raised, Sunken, and None. My experience is that none of them look really good on Vista. I changed the Font. I also modified some properties of the entire program, such as the form's Text.

Summary

In summary, here we saw how you can use a database and display its contents in a usable DataGridView. This is critical for many data-driven Windows Forms application. The example shown here could form the foundation of a data-driven application for veterinarian's office. When a dog-owner arrives and his dog is sick, the vet employee could record the species, weight, and color of the dog.