Data access is the basis of any application. In this article, I will show you how to access SQL Server-based data using C# and ADO.NET, as well as how to display the data in a data-bound grid control. I use a simple C# application as an example.
ADO.NET allows you to work without needing to maintain a connection. Additionally, it allows you to switch from one data source to another with just a few lines of code.
The core objects in ADO.NET are Command, Connection, DataReader, and DataAdapter. They are the basis of all data operations in .NET.
Core ADO.NET namespaces
- System.Data: Serves as a basis for other namespaces and makes up objects such as DataTable, DataColumn, DataView, and Constraints.
- System.Data.Common: Defines generic objects shared by the different data providers, which 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 you can use 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 contains the drivers for 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: Takes advantage of the SQL Server APIs directly and offers a better performance than the more generic System.Data.OleDb namespace. It is a data provider namespace created specifically for SQL Server version 7.0 and up.
- System.Data.SqlTypes: Provides classes for data types specific to SQL Server. The namespace is designed specifically for SQL Server and offers better performance than other namespaces but only when dealing with the SQL Server backend.
- System.Data.Odbc: Works with all compliant ODBC drivers. This namespace is supported only in version 1.1 of the .NET Framework, so installing the new Framework is the way to get it.
Data grid example
The code defines two variables: strConn and strSQL. strConn is set to the required connection string for utilizing the JET database using OLEDB and pointing to a location of the Northwind.mdb database on the local machine. strSQL specifies the query I want to run on the Access database (Northwind.mdb).
Next, I define the OleDBDataAdapter object da and pass it the query statement (strSQL) and the connection string (strConn). Notice that I am not creating a Connection object in the example.
Then, I define the dataset ds, which is used to get the actual data from the Customers table onto the grid control. I specify the DataMember property of the data grid control dataGrid1 to point to the table where I am getting the data and set the control's DataSource property to the DataSetds. (The DataMember property gets/sets a table in the DataSource used to bind to the control, and the DataSource property gets/sets the data source used to populate the control.) When you run the code in Listing A, it looks like Figure 2.
I display the data from the C:\\DataAccess\\Northwind.mdb database, and see only the columns I chose in the select statement. If the number of rows or columns is larger than what can fit on the page, the grid control will automatically show the scrollbars.
Now you know the basics of using ADO.NET in a C# application and creating a data grid control to display the data returned by the query.
Irina Medvinskaya has been involved in technology since 1996. She has an MBA from Pace University and works as a Project Manager at Citigroup.