Accessing Data From SQL Server
By Subhagata Swarnakar

Among the many things that are changing with .NET is data access. Under the .NET Framework, data access is handled by a set of classes called ADO.NET which are essentially an augmentation of the existing ActiveX Data Objects (ADO). The ADO.NET components have been designed to factor data access from data manipulation. There are two central components of ADO.NET that accomplish this: the DataSet, and the .NET data provider, which is a set of components including the Connection, Command, DataReader, and DataAdapter objects. Under ADO.NET, the functionality of the Recordset has been split into three groups. The DataReader object allows you to perform a single pass through a set of records as efficiently as possible. In ADO, this was achieved using a forward-only, server-side cursor. The DataSet and Command objects allow you to create a client-side cache of one or more related Recordsets and process it in a disconnected fashion. The DataSet is the core component of the disconnected architecture of ADO.NET. The DataSet is explicitly designed for data access independent of any data source. The other core element of the ADO.NET architecture is the .NET data provider, whose components are explicitly designed for data manipulation and fast, forward-only, read-only access to data. The Connection object provides connectivity to a data source. The Command object 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 data source. Finally, the DataAdapter provides the bridge between the DataSet object and the data source. The DataAdapter uses Command objects to execute SQL commands at the data source to both load the DataSet with data, and reconcile changes made to the data in the DataSet back to the data source. You can write .NET data providers for any data source. The .NET Framework ships with two .NET data providers: the SQL Server .NET Data Provider and the OLE DB .NET Data Provider. The Microsoft .NET Framework SDK (including ADO.NET) is supported on Microsoft® Windows® 2000, Microsoft® Windows NT® 4 with Service Pack 6a, Microsoft® Windows® Millennium Edition, Microsoft® Windows® 98, Microsoft® Windows® SE, and Microsoft® Windows® 95. Use of the SQL Server .NET Data Provider or OLE DB .NET Data Provider requires the 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. SQL Server .NET Data Provider classes are located in the System.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 for SQL Server (SQLOLEDB), use the OleDbConnection object of the OLE DB .NET Data Provider. To establish the connection with SQL Server, Integrated Security is applied through SSIP. Security Support Provider Interface (SSPI) provides a common interface between transport-level applications such as remote procedure call (RPC), and security support providers (SSPs) such as Windows Distributed Security. SSPI allows an application to use various security models available on a computer or network without changing the interface to the security system. After establishing a connection to a data source, you can execute commands and return results from the data source using a SqlCommand object. A SqlCommand object can be created using the SqlCommand constructor. When creating a SqlCommand object using the SqlCommand constructor, specify an SQL statement to execute at the data source, and a SqlConnection object. The Command object exposes several Execute methods you can use to perform the intended action. When returning results as a stream of data, use ExecuteReader to return a DataReader object. ADO.NET DataReader to retrieve a read-only, forward-only stream of data from a database. Using the SqlDataReader can increase application performance and reduce system overhead because only one row at a time is ever in memory. By calling ExecuteReader method of SQLCommand object rows are retrieved from a data source. The Read method of the SqlDataReader object to obtain a row from the results of the query. In ADO.NET the SqlDataReader provides a series of methods that allow you to access column values in their native data types (GetDateTime, GetDouble, GetString, GetInt32, etc.). The SqlDataReader provides a non-buffered stream of data that allows procedural logic to efficiently process results from a data source sequentially. The SqlDataReader is a good choice when retrieving large amounts 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);
}    	
}
}

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