Creating a Data-Bound Grid in C# with ADO.NET

Introduction

Data access is a core of most applications and an ability to efficiently access and modify a database is required for developers on a regular basis. In this article, you will look at accessing SQL-based data utilizing C# and ADO.NET and displaying the data in a data-bound grid control.

ADO.NET Architecture

ADO.NET is a framework of classes that allows you to access data and get the necessary data for .NET-based applications. ADO.NET is similar to its predecessor, ADO; however, there are some very important differences in its architecture. ADO.NET is based on XML, is more flexible than ADO, and allows working without maintaining a connection and switching between data sources with little code.

The core objects of ADO.NET are: Command, Connection, DataReader, and DataAdapter.

  • Connection: A starting point to data access; determines how you connect to the data store; requires setting up properties, like ConnectionString, to establish communications to the data store.
  • Command: Used with stored procedures and running SQL statements
  • DataReader: Provides a forward-only, read-only stream of data from a given data source.
  • DataAdapter: Provides a bridge between the source data and the DataSet object to allow retrieving and updating data.

Data Access Basics

Working with ADO.NET in.NET framework requires using one of the two System.Data namespaces: System.Data.SQLClient or System.Data.OleDB. The choice of the namespace you use will depend on the database you are trying to access. When working with SQL server, System.Data.SQLClient namespace is the best choice. For other database types, you have to use the System.Data.OleDB namespace.

Core ADO.NET Namespaces

  • System.Data: Serves as a basis for others and makes up objects such as DataTable, DataColumn, DataView, and Constraints.
  • System.Data.Common: Defines generic objects shared by the different data providers that include DataAdapter, DataColumnMapping, and DataTableMapping. It is used by the data providers and contains the collections that are useful for accessing data sources.
  • System.Data.OleDb: Defines objects that can be used to connect to the data sources and to modify the data in the various data sources. It is written as the generic data provider, and the implementation provided by the .NET Framework in contains the drivers for Microsoft SQL Server, the Microsoft OLE DB Provider for Oracle, and Microsoft Provider for Jet 4.0. The namespace is useful when you need to connect to many different data sources and you want to achieve a better performance than a provider.
  • System.Data.SqlClient: A data provider namespace created specifically for Microsoft SQL Server version 7.0 and up. The namespace takes advantage of the Microsoft SQL Server APIs directly and offers a better performance than the more generic System.Data.OleDb namespace.
  • System.Data.SqlTypes: Provides classes for data types specific to Microsoft SQL Server. The namespace is designed specifically for SQL Server and offers better performance.
  • System.Data.Odbc: Is intended to work with all compliant ODBC drivers. It is available for download from the Microsoft's web site.

Start Coding

To create an example accessing the data and displaying it in a grid control, first add a data grid control to the form, dataGrid1.Add the following namespaces to your code.

using System.Data;
using System.Data.OleDb;

<code>
private void Form1_Load(object sender, System.EventArgs e)
{

   string strConn, strSQL;
   strConn = "Provider=Microsoft.JET.OLEDB.4.0;" +
             "data source=" + "C:\\DataAccess\\Northwind.mdb";

   strSQL = "SELECT CompanyName, ContactName, ContactTitle, " ;
   strSQL = strSQL + "Address, City, Country FROM Customers";

   OleDbDataAdapter da = new OleDbDataAdapter(strSQL, strConn);
   DataSet ds = new DataSet();
   da.Fill(ds, "Customers");

   dataGrid1.DataMember = "Customers";
   dataGrid1.DataSource=ds;

}

The Result

Running the code above will produce the following result:

In this example, you display the data from the C:\\DataAccess\\Northwind.mdb database. You will see only the columns you listed in the select statement. The grid control will provide scrollbars as necessary.

How It Works

The code example above provides a simple demonstration of getting the data from an Access database onto the data grid using C# and ASP.NET.

You define two string variables, strConn (a connection string variable ) and strSQL(a query statement variable). You proceed defining an OleDBDataAdapter object da and passing to it both the query statement (strSQL) and the connection string (strConn). Notice that you are not creating a Connection object in your example because ADO.NET doesn't force you to create one. Then, a dataset ds is defined; it is used to get the actual data from the Customers table onto the form's Data Grid control. You specify dataset's DataMember Property point to the table from which you are getting the data and set the Data Grid control's DataSource property to the DataSet ds. The result is displayed in the Data Grid control on the form.

Summary

In this example, you looked at the basics of using ADO.NET in C# applications and utilized a Data Grid control to display the data returned by the query.

About the Author

Irina Medvinskaya has been involved in technology since 1996. Throughout her career, she as developed many client/server and web applications mainly for financial services companies. She works as a Development Manager at Citigroup.



Comments

  • Thank

    Posted by My on 08/05/2014 03:08am

    Many thanks for the article! Althought I have experiences in coding asp net but this make good for me a lot!

    Reply
  • how to connect sql server database by using dll in asp.net

    Posted by manikandan_ck on 11/12/2008 10:01am

    how to connect sql server database by using dll in asp.net

    Reply
  • Information Systems Officer

    Posted by DAidoo on 07/19/2007 06:57am

    This example is simple self explanatory and precise. Thanks a lot

    Reply
Leave a Comment
  • Your email address will not be published. All fields are required.

Top White Papers and Webcasts

  • On-demand Event Event Date: September 10, 2014 Modern mobile applications connect systems-of-engagement (mobile apps) with systems-of-record (traditional IT) to deliver new and innovative business value. But the lifecycle for development of mobile apps is also new and different. Emerging trends in mobile development call for faster delivery of incremental features, coupled with feedback from the users of the app "in the wild." This loop of continuous delivery and continuous feedback is how the best mobile …

  • The explosion in mobile devices and applications has generated a great deal of interest in APIs. Today's businesses are under increased pressure to make it easy to build apps, supply tools to help developers work more quickly, and deploy operational analytics so they can track users, developers, application performance, and more. Apigee Edge provides comprehensive API delivery tools and both operational and business-level analytics in an integrated platform. It is available as on-premise software or through …

Most Popular Programming Stories

More for Developers

Latest Developer Headlines

RSS Feeds