C# DataGridView Tutorial

Display an SQL database table with a DataGridView control and a DataTable. Use Windows Forms.

DataGridView displays data from SQL databases. This tutorial takes a specific table from a database (as a DataTable) and displays it on a DataGridView. This is done with a DataAdapter and data logic.DataTable

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.

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.

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

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.

Animal 1 Weight: 10 Color: Brown Animal 2 Weight: 15 Color: Black Animal 3 Weight: 5 Color: Green Animal 4 Weight: 20 Color: White

Windows 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.SqlClient

FillData: The example calls FillData after InitializeComponent. In FillData 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.

C# program that uses SqlCeConnection 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; } } } } }

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

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 Colors

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 DataGridView Add Rows

Finishing touches. I changed AllowUserToAddRows. This eliminates the blank row on the bottom of the DataGridView. I changed the BorderStyle—Fixed3D works well on newer versions of Microsoft Windows.

FixedSingle: This border looks better in Windows XP. The None option might be good for some programs.

Column header, styles and fonts. I changed the ColumnHeaderBorderStyle. For more recent versions of Windows, we use None for a good appearance. The enums are Custom, Single, Raised, Sunken and None.

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.

The Dev Codes

.Net

.NET Array Dictionary List String 2D Async DataTable Dates DateTime Enum File For Foreach Format IEnumerable If IndexOf Lambda LINQ Parse Path Process Property Regex Replace Sort Split Static StringBuilder Substring Switch Tuple

Java

Core Array ArrayList HashMap String 2D Cast Character Console Deque Duplicates File For Format HashSet If IndexOf Lambda Math ParseInt Process Random Regex Replace Sort Split StringBuilder Substring Switch Vector While

TheDeveloperBlog.com

C# DataGridView Tutorial

Related Links:

.Net

Java

Related Links