Home
C#.WinForms
DataGridView and SQL Tutorial
This page was last reviewed on Dec 22, 2023.
Dot Net Perls
DataGridView. This control displays data from SQL databases. This tutorial takes a specific table from a database (as a DataTable) and displays it on a DataGridView.
DataGridView
Shows a datagridviewShows a datagridviewShows a datagridviewShows a datagridviewShows a datagridview
Getting started. In Visual Studio, select the File menu and then New Project, and select a Windows Forms application. Next drag the DataGridView icon into the window.
Make data source. Here we need to generate an SDF file to interact with. We will use Visual Studio for this part. Go to Data and then Add New Data Source.
Then Select Database, and click Next. Click on New Connection, as we need to create an all-new connection and database.
Detail Type in a database name, and then click Create. We are using SQL Server CE, which is the compact edition of SQL Server 2005.
Shows a datagridview
Data Source=C:\Users\Sam\Documents\Data.sdf
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.
Shows a datagridview
Weight: 10 Color: Brown
Weight: 15 Color: Black
Weight: 5 Color: Green
Weight: 20 Color: White
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 in similar ways.
Detail 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 generated.
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.
Shows a datagridview
using System.Data; using System.Data.SqlServerCe; using System.Windows.Forms; namespace WindowsFormsApplication5 { public partial class Form1 : Form { public Form1() { InitializeComponent(); FillData(); } void FillData() { // Step 1: Open connection. using (SqlCeConnection c = new SqlCeConnection( Properties.Settings.Default.DataConnectionString)) { c.Open(); // Step 2: Create new DataAdapter. using (SqlCeDataAdapter a = new SqlCeDataAdapter("SELECT * FROM Animals", c)) { // Step 3: Use DataAdapter to fill DataTable. DataTable t = new DataTable(); a.Fill(t); // Step 4: Render data onto the screen. dataGridView1.DataSource = t; } } } } }
Alternating colors. For usability, you want to have alternating row colors on your DataGridView. The .NET Framework provides the AlternatingRowsDefaultCellStyle attribute.
Tip Change the BackColor to something your users will enjoy, and that will improve the program's usability. I chose aqua.
Shows a datagridview
DataSet. The DataSet object available in System.Data has a slightly different purpose from DataTable, as it allows more information to be stored.Shows a datagridview
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.
Note Use the Anchor property on the DataGridView in the designer view to "pin" it to each edge of your window.
Note 2 Make the DataGridView white by changing the Appearance and then BackgroundColor from AppWorkspace to Window.
Note 3 Hide the row headers. Go to Appearance and then RowHeadersVisible and change it to False.
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.
Step 1 Go to Properties and then locate Columns. This shows the Edit Columns dialog box—click on the "Add" button.
Step 2 Enter Header text. The text you type into the Header text box will ensure that the text is always shown.
Step 3 Change the properties of the DataGridViewColumn in the dialog box. I set 110 pixels 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.
Tip 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.
A summary. We used a database and displayed its contents in a usable DataGridView. This is critical for many data-driven Windows Forms application.
Dot Net Perls is a collection of tested code examples. Pages are continually updated to stay current, with code correctness a top priority.
Sam Allen is passionate about computer languages. In the past, his work has been recommended by Apple and Microsoft and he has studied computers at a selective university in the United States.
This page was last updated on Dec 22, 2023 (edit).
Home
Changes
© 2007-2024 Sam Allen.