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 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, andDataAdapter.
- 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.
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.
Form1_Load(object sender, System.EventArgs e)
string strConn, strSQLQuery;
strConn = "Provider=Microsoft.JET.OLEDB.4.0; data source=C:\\ Northwind.mdb";
strSQLQuery = "SELECT CompanyName, ContactName, ContactTitle,Address, City, Country
OleDbDataAdapter da = new OleDbDataAdapter(strSQLQuery, strConn);
DataSet ds = new DataSet();
dataGrid1.DataMember = "Customers";