C# Help Home » Articles » Data Access » Accessing Data From SQL Server

Accessing Data From SQL Server

26. Feb, 2006 by admin 0 Comments

Among the many things that are changing with .NET is data access. Underthe .NET Framework, data access is handled by a set of classes calledADO.NET which are essentially an augmentation of the existing ActiveXData Objects (ADO).

The ADO.NET components have been designed to factor data access fromdata manipulation. There are two central components of ADO.NET thataccomplish this: the DataSet, and the .NET data provider, which is aset of components including the Connection, Command, DataReader, andDataAdapter objects. Under ADO.NET, the functionality of the Recordsethas been split into three groups. The DataReader object allows you toperform a single pass through a set of records as efficiently aspossible. In ADO, this was achieved using a forward-only, server-sidecursor. The DataSet and Command objects allow you to create aclient-side cache of one or more related Recordsets and process it in adisconnected fashion.

The DataSet is the core component of the disconnectedarchitecture of ADO.NET. The DataSet is explicitly designed for dataaccess independent of any data source.

The other core element of the ADO.NET architecture is the .NETdata provider, whose components are explicitly designed for datamanipulation and fast, forward-only, read-only access to data. TheConnection object provides connectivity to a data source. The Commandobject enables access to database commands to return data, modify data,run stored procedures, and sends or retrieves parameter information.The DataReader provides a high-performance stream of data from the datasource. Finally, the DataAdapter provides the bridge between theDataSet object and the data source. The DataAdapter uses Commandobjects to execute SQL commands at the data source to both load theDataSet with data, and reconcile changes made to the data in theDataSet back to the data source.You can write .NET data providers for any data source. The .NETFramework ships with two .NET data providers: the SQL Server .NET DataProvider and the OLE DB .NET Data Provider.

pic1 Accessing Data From SQL Server

The Microsoft .NET Framework SDK (including ADO.NET) issupported on Microsoft� Windows� 2000, Microsoft� Windows NT� 4 withService Pack 6a, Microsoft� Windows� Millennium Edition, Microsoft�Windows� 98, Microsoft� Windows� SE, and Microsoft� Windows� 95. Use ofthe SQL Server .NET Data Provider or OLE DB .NET Data Provider requiresthe installation of Microsoft Data Access Components 2.6 or later.

To use ADO.NET, you will want to include the System.Data namespace in your applications.

Using ADO.NET, to connect to Microsoft SQL Server 7.0 or later,use the SqlConnection object of the SQL Server .NET Data Provider. SQLServer .NET Data Provider classes are located in theSystem.Data.SqlClient namespace. To connect to an OLE DB data source,or to Microsoft SQL Server 6.x or earlier using the OLE DB Provider forSQL Server (SQLOLEDB), use the OleDbConnection object of the OLE DB.NET Data Provider.

To establish the connection with SQL Server, IntegratedSecurity is applied through SSIP. Security Support Provider Interface(SSPI) provides a common interface between transport-level applicationssuch as remote procedure call (RPC), and security support providers(SSPs) such as Windows Distributed Security. SSPI allows an applicationto use various security models available on a computer or networkwithout changing the interface to the security system.

After establishing a connection to a data source, you canexecute commands and return results from the data source using aSqlCommand object. A SqlCommand object can be created using theSqlCommand constructor. When creating a SqlCommand object using theSqlCommand constructor, specify an SQL statement to execute at the datasource, and a SqlConnection object.

The Command object exposes several Execute methods you can use toperform the intended action. When returning results as a stream ofdata, use ExecuteReader to return a DataReader object.

ADO.NET DataReader to retrieve a read-only, forward-only streamof data from a database. Using the SqlDataReader can increaseapplication performance and reduce system overhead because only one rowat a time is ever in memory.

By calling ExecuteReader method of SQLCommand object rows are retrievedfrom a data source. The Read method of the SqlDataReader object toobtain a row from the results of the query.

In ADO.NET the SqlDataReader provides a series of methods that allowyou to access column values in their native data types (GetDateTime,GetDouble, GetString, GetInt32, etc.).

The SqlDataReader provides a non-buffered stream of data that allowsprocedural logic to efficiently process results from a data sourcesequentially. The SqlDataReader is a good choice when retrieving largeamounts of data because the data is not cached in memory.

using System;
using System.Data.SqlClient;

public class Test
{
public static void Main()
{
Test t=new Test();
t.Run();
}
public void Run()
{
SqlConnection conn = new SqlConnection("Data Source=localhost; Integrated Security=SSPI; Initial Catalog=pubs");
SqlCommand cmd = new SqlCommand("SELECT * FROM emp_test", conn);
try
{
nwindConn.Open();

SqlDataReader myReader = cmd.ExecuteReader();
Console.WriteLine("Code \t Emp. Name \t Emp. Phone");
Console.WriteLine("—————————————–");
while (myReader.Read())
{
Console.WriteLine("{0}\t{1}\t\t{2}", myReader.GetInt32(0), myReader.GetString(1), myReader.GetString(2));
}

myReader.Close();
nwindConn.Close();
}
catch(Exception e)
{
Console.WriteLine("Exception Occured –>> {0}",e);
}
}
}

� At first you have to create a table 'emp_test' in SQL Server. The code will be:

create table emp_test
(
ecode int primary key,
ename varchar(20),
ephone char(7)
)

� Then create a User DSN called 'test'.

� Save the program in your disk as "savedata.cs" and compile the program as:

csc /r:System.Data.dll selectdata.cs

� No need to create an ODBC connection to execute the program.