Web Design that taps into the haromny and vision of your dreams.

Consuming Data in Windows Forms C# Applications

How to access data in a Windows Forms Application

Written By on in C#

1,130 words, estimated reading time 6 minutes.

Using the supplied data-aware controls in .Net, Windows Forms applications can easily access, display and update data without a great deal of code.

Active Data Objects (ADO.Net) Series
  1. What are Active Data Objects?
  2. Connecting to a SQL Server Database with C#
  3. Consuming Data in Windows Forms C# Applications
  4. Using ADO.Net Controls in ASP.Net Pages
  5. Modifying Data and Updating Databases with C#
  6. Connecting to MySql Databases with C# / ASP.Net
  7. Reading Excel Spreadsheets with C# ADO.Net
  8. Creating SQL Server Stored Procedures in C#
  9. Calling SQL Server Stored Procedures from C#
  10. Importing and Exporting XML from a DataSet

This article relates to an old version of the .Net Framework. While the contents are still relevant, future versions of the framework may change, or improve upon, the contents of this article.

In fact, you can create a form with a dataGrid and display records in a table without any code at all! The data grid component is specific to the .Net framework 2 and above.

The first thing you need to do is to drag a dataGridView onto a form. When it's on the form, Visual Studio will automatically expand the DataGridView Tasks window where you can assign a data source to it. Drop down the "Select Data Source" combo box and select "Add Project Data Source". Select Database from the list provided and creates a new connection. In the next dialogue box you can browse to the Northwind Traders sample files and select the database. You can test the connection or simply press OK to create the connection. If you have a database on a SQL Server you would prefer to use instead you can browse to that.

The Data Source dialog box will now show the connection with the connection string, in my case it is automatically set to

Data Source=.SQLEXPRESS;AttachDbFilename="C:\SQL Server 2000\Sample Databases\NORTHWND.MDF";Integrated Security=True;Connect Timeout=30;User Instance=True"

On clicking next the Wizard will ask if you want to copy the files to your project directory if you have selected a static database file. You can copy the files or leave them where they are; I can see no difference functionally with this option.

The next stage of the Wizard is to configure the Data Set that will be displayed in the Data Grid View. You can select tables and fields to be shown. In this example I will use the Customers table, so expand the table's item and select Customers from the list. If you only want a selection of fields you can expand the table and select only the fields required. You can then finish the Wizard.

The Data Grid now contains the entire column headings selected of the Customers table, but no data. You may also that there are a number of non-visual components assigned to the form, including a DataSet, and a TableAdaptor. The Data Grid is filled at runtime with a line of code that has been inserted into the Form_Load event:

private void Form1_Load(object sender, EventArgs e)
{
  this.customersTableAdapter.Fill(this.nORTHWNDDataSet.Customers);
}

Go ahead and run the application to see how this method call fills all the records of the DataSet into the DataGrid. You can navigate the records, modify them, add new and delete items. Change some of the values in the grid and then exit the program. Rerun the application and notice that the changes have not taken effect. This is because we are working in disconnected mode with a DataSet. Before any updates are committed to the database file (or server) we must call the update method of the TableAdaptor. You can add a button to the form with the following code to update the database. This will permanently update the database, so make sure you are certain, or that you are working on a test database. After calling the Update method, we also call the DataSet AcceptChanges method to commit the changes.

Using Data in Windows Forms Applications
Using Data in Windows Forms Applications
customersTableAdapter.Update(nORTHWNDDataSet);
nORTHWNDDataSet.AcceptChanges();

The database will only be updated when the button is clicked.

Enquiry Programs

It takes a little more code to write an interactive enquiry program. This little sample will query the sample SQL database and return contact and company details matching a customer number.

We create a form with a text box and label for each field in the table. We set all the text boxes apart from the search box to read-only. When the search button is pressed the code executes a query on the server to retrieve the customer details and we create a SqlDataAdaptor and DataTable to hold the results. The rest of the code binds the text boxes to the fields of the table using the DataBindings property. Almost any control can be bound to data and when adding a binding you can specify which property the data should be bound to. In this example we bind the "Text" property of the TextBox to the DataTable and specifying the data field in the past property. The whole code segment is wrapped in a try catch block as basic exception handling.

Interactive SQL Enquiry
Interactive SQL Enquiry
try
{
  string connectionString = @"Data Source=.SQLEXPRESS;AttachDbFilename=""C:\SQL Server 2000\Sample  Databases\NORTHWND.MDF"";Integrated Security=True;Connect Timeout=30;User Instance=True";
  string commandString = "SELECT * FROM Customers WHERE CustomerID = '" + txtCustomerID.Text + "'";
 
  SqlDataAdapter da = new SqlDataAdapter(commandString, connectionString);
  DataTable table = new DataTable();
  da.Fill(table);
 
  txtCompanyName.DataBindings.Add("Text", table, "CompanyName");
  txtContactName.DataBindings.Add("Text", table, "ContactName");
  txtContactRole.DataBindings.Add("Text", table, "ContactTitle");
  txtContactPhone.DataBindings.Add("Text", table, "Phone");
  txtCompanyAddress.DataBindings.Add("Text", table, "Address");
  txtCompanyCity.DataBindings.Add("Text", table, "City");
  txtCompanyRegion.DataBindings.Add("Text", table, "Region");
  txtCompanyPostCode.DataBindings.Add("Text", table, "PostalCode");
  txtCompanyCountry.DataBindings.Add("Text", table, "Country");
  txtFaxNumber.DataBindings.Add("Text", table, "Fax");
}
catch (SqlException ex)
{
  MessageBox.Show(ex.Message);
}

These properties can also be set from within the Visual Studio Editor component properties screen under DataBindings.

Sorting and Filtering a Grid View

We have already coded a SELECT statement to filter the results show, but we can also change the RowFilter property of the Data Grid View to filter the results. You will need the entire dataset for this to be most effective or you risk not showing some records. You can add a text box to the form and a button and add this code to the button click event.

DataView dv = new DataView(nORTHWNDDataSet.Tables["Customers"]);
dv.RowFilter = "City = '"+textBox1.Text+"'";
dataGridView1.DataSource = dv;

This code will create a DataView object with the data from the Customers table. We then set the RowFilter property to show all records where the City is equal to the value of the textBox text. We then set the dataGridView objects DataSource property to the new DataView data source.

The RowFilter property can also be used to show deleted records, updated records, records that have been added and so on using the DataViewRowState enumeration.

DataView dv = new DataView(nORTHWNDDataSet.Tables["Customers"]);
dv.RowFilter = DataViewRowState.ModifiedCurrent;
dataGridView1.DataSource = dv;

Last updated on: Friday 23rd June 2017

 

Comments
Syed Asim

Syed Asim

I appreciate ur work.u've done very well .tnx keep it up.

Reply to Syed Asim
lucrate

lucrate

IT is a very helpful website, i think u should add some some more code examples explaining what they do like what u did. This is how we get to learn the language

Reply to lucrate

 

Leave a Reply

Your email address will not be published.





If you find something abusive or that does not comply with our terms or guidelines please flag it as inappropriate.

Copyright © 2001-2018 Tim Trott, all rights reserved. Web Design by Azulia Designs

This web page is licensed for your personal, private, non-commercial use only.

Disclaimer, Privacy & LegalSitemapContact Me