Csharp/CSharp Tutorial/ADO.Net/SqlDataReader

Accessing Data Values in a DataReader

<source lang="csharp">using System; using System.Data; using System.Data.SqlClient;

   class Program
   {
       static void Main(string[] args)
       {
           string sqlConnectString = "Data Source=(local);" +
               "Integrated security=SSPI;Initial Catalog=AdventureWorks;";
           string sqlSelect = @"SELECT ContactID, NameStyle, Title,
               FirstName, MiddleName, LastName, Suffix, EmailAddress,
               EmailPromotion, Phone, PasswordHash, PasswordSalt
               FROM Person.Contact";
           SqlConnection connection = new SqlConnection(sqlConnectString);
           SqlCommand command = new SqlCommand(sqlSelect, connection);
           connection.Open( );
           SqlDataReader dr = command.ExecuteReader( );
           dr.Read( );
           Console.WriteLine("ContactID = {0}", dr[0]);
           Console.WriteLine("Title = {0}", dr["Title"]);
           Console.WriteLine("FirstName = {0}",dr.IsDBNull(3) ? "NULL" : dr.GetString(3));
           Console.WriteLine("MiddleName = {0}",dr.IsDBNull(4) ? "NULL" : dr.GetSqlString(4));
           Console.WriteLine("LastName = {0}",dr.IsDBNull(5) ? "NULL" : dr.GetSqlString(5).Value);
           Console.WriteLine("EmailAddress = {0}", dr.GetValue(7));
           Console.WriteLine("EmailPromotion = {0}",int.Parse(dr["EmailPromotion"].ToString( )));
       }
   }</source>

DataReader has records using HasRows property

<source lang="csharp">using System; using System.Data; using System.Data.SqlClient;

   class Program
   {
       static void Main(string[] args)
       {
           string sqlConnectString = "Data Source=(local);" +
               "Integrated security=SSPI;Initial Catalog=AdventureWorks;";
           string sqlSelect = "SELECT * FROM Person.Contact";
           string sqlSelectEmpty = "SELECT * FROM Person.Contact";
           SqlDataAdapter da = new SqlDataAdapter(sqlSelect, sqlConnectString);
           DataTable dt = new DataTable( );
           da.Fill(dt);
           Console.WriteLine("DataTable has records = {0}", dt.Rows.Count > 0);
           using (SqlConnection connection = new SqlConnection(sqlConnectString))
           {
               SqlCommand command = new SqlCommand(sqlSelect, connection);
               connection.Open( );
               SqlDataReader dr = command.ExecuteReader( );
               Console.WriteLine(dr.HasRows);
               Console.WriteLine(dr.Read( ));
               dr.Close( );
           }
           da = new SqlDataAdapter(sqlSelectEmpty, sqlConnectString);
           dt = new DataTable( );
           da.Fill(dt);
       }
   }</source>

Data Reading with SqlDataReader

<source lang="csharp">using System; using System.Data; using System.Data.SqlClient;

 class Program
 {
   static void Main(string[] args)
   {
     SqlConnection thisConnection = new SqlConnection(
               @"Data Source=.\SQLEXPRESS;" +
               @"AttachDbFilename="NORTHWND.MDF";" +
               @"Integrated Security=True;Connect Timeout=30;User Instance=true");
     thisConnection.Open();
     SqlCommand thisCommand = thisConnection.CreateCommand();
     thisCommand.rumandText = "SELECT CustomerID, CompanyName from Customers";
     SqlDataReader thisReader = thisCommand.ExecuteReader();
     while (thisReader.Read())
     {
       Console.WriteLine("\t{0}\t{1}",thisReader["CustomerID"], thisReader["CompanyName"]);
     }
     thisReader.Close();
     thisConnection.Close();
   }
 }</source>

Determining the Number of Records Returned in a DataReader

<source lang="csharp">/* CREATE PROCEDURE Person.GetContacts

   @RowCount int OUTPUT

AS

   SET NOCOUNT ON
   SELECT * FROM Person.Contact
   set @RowCount = @@ROWCOUNT
   RETURN @RowCount

• /

using System; using System.Data; using System.Data.SqlClient;

   class Program
   {
       static void Main(string[] args)
       {
           string sqlConnectString = "Data Source=(local);Integrated security=SSPI;Initial Catalog=AdventureWorks;";
           string sqlSelect ="SELECT COUNT(*) FROM Person.Contact; SELECT * FROM Person.Contact;";
           using (SqlConnection connection = new SqlConnection(sqlConnectString))
           {
               SqlCommand command = new SqlCommand(sqlSelect, connection);
               connection.Open( );
               SqlDataReader dr = command.ExecuteReader( );
               dr.Read( );
               Console.WriteLine(dr.GetInt32(0));
               dr.NextResult( );
               int count = 0;
               while (dr.Read( )){
                   count++;
               }
               Console.WriteLine(count);
               dr.Close( );
               command = new SqlCommand("Person.GetContacts", connection);
               command.rumandType = CommandType.StoredProcedure;
               command.Parameters.Add("@RowCount", SqlDbType.Int).Direction =ParameterDirection.Output;
               dr = command.ExecuteReader( );
               dr.Close( );
               Console.WriteLine("Record count, using @@ROWCOUNT = {0}",command.Parameters["@RowCount"].Value);
           }
       }
   }</source>

Get data by data type using SqlDataReader

<source lang="csharp">using System; using System.Data; using System.Data.SqlClient; class MainClass {

 [STAThread]
 static void Main(string[] args)
 {
   string SQL = "SELECT * FROM Employee";
   string ConnectionString ="server=(local)\\SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI;";
   SqlConnection conn = new SqlConnection(ConnectionString);
   SqlCommand cmd = new SqlCommand(SQL, conn);
   conn.Open();
   SqlDataReader reader = cmd.ExecuteReader();
   try 
   {
     while (reader.Read()) 
     {
       Console.Write("ID:"+reader.GetInt32(0).ToString() );
       Console.Write(" ,");
       Console.WriteLine("Name:" + reader.GetString(1).ToString() );
     }
   }
   finally 
   {
     reader.Close();
     conn.Close();
   }   
 }

}</source>

ID:2 ,Name:G

Get the column ordinal for the Phone attribute and use it to output the column

<source lang="csharp">using System; using System.Data; using System.Data.SqlClient;

   class Program
   {
       static void Main(string[] args)
       {
           string sqlConnectString = "Data Source=(local);" +
               "Integrated security=SSPI;Initial Catalog=AdventureWorks;";
           string sqlSelect = @"SELECT ContactID, NameStyle, Title,
               FirstName, MiddleName, LastName, Suffix, EmailAddress,
               EmailPromotion, Phone, PasswordHash, PasswordSalt
               FROM Person.Contact";
           SqlConnection connection = new SqlConnection(sqlConnectString);
           SqlCommand command = new SqlCommand(sqlSelect, connection);
           connection.Open( );
           SqlDataReader dr = command.ExecuteReader( );
           dr.Read( );
           int coPhone = dr.GetOrdinal("Phone");
           Console.WriteLine("Phone = {0}", dr[coPhone]);
       }
   }</source>

Handle Multiple Results

<source lang="csharp">using System; using System.Data; using System.Data.SqlClient;

   class MultipleResults
   {
       static void Main(string[] args)
       {
           string connString = @"server = .\sqlexpress;integrated security = true;database = northwind";
           string sql1 = @"select companyname,contactname from customers where companyname like "A%"";
           string sql2 = @"select firstname,lastname from employees";
           string sql = sql1 + sql2;
           SqlConnection conn = new SqlConnection(connString);
           try
           {
               conn.Open();
               SqlCommand cmd = new SqlCommand(sql, conn);
               SqlDataReader rdr = cmd.ExecuteReader();
               do{
                   while (rdr.Read()){
                       Console.WriteLine("{0} : {1}", rdr[0], rdr[1]);
                   }
                   Console.WriteLine("".PadLeft(60, "="));
               }
               while (rdr.NextResult());
               rdr.Close();
           }
           catch (Exception e)
           {
               Console.WriteLine("Error Occurred: " + e);
           }
           finally
           {
               conn.Close();
           } 
       }
   }</source>

how to read a table schema

<source lang="csharp">using System; using System.Data; using System.Data.SqlClient; class SchemaOnlyCommandBehavior {

   public static void Main() {
       SqlConnection mySqlConnection = new SqlConnection("server=localhost;database=Northwind;uid=sa;pwd=sa");
       SqlCommand mySqlCommand = mySqlConnection.CreateCommand();
       mySqlCommand.rumandText = "SELECT ProductID, ProductName, UnitPrice FROM Products WHERE ProductID = 1";
       mySqlConnection.Open();
       SqlDataReader productsSqlDataReader = mySqlCommand.ExecuteReader(CommandBehavior.SchemaOnly);
       DataTable myDataTable = productsSqlDataReader.GetSchemaTable();
       foreach (DataRow myDataRow in myDataTable.Rows) {
           foreach (DataColumn myDataColumn in myDataTable.Columns) {
               Console.WriteLine(myDataColumn + "= " + myDataRow[myDataColumn]);
               if (myDataColumn.ToString() == "ProviderType") {
                   Console.WriteLine(myDataColumn + "= " + ((System.Data.SqlDbType)myDataRow[myDataColumn]));
               }
           }
       }
       productsSqlDataReader.Close();
       mySqlConnection.Close();
   }

}</source>

Mapping .NET Data Provider Data Types to .NET Framework Data Types by calling the GetXXX method

<source lang="csharp">using System; using System.Data.SqlClient; using System.Data.SqlTypes;

   class Program
   {
       static void Main(string[] args)
       {
           string sqlConnectString = "Data Source=(local);Integrated security=SSPI;Initial Catalog=AdventureWorks;";
           string sqlSelect = "SELECT TOP 5 ContactID, FirstName, MiddleName, LastName FROM Person.Contact";
           SqlConnection connection = new SqlConnection(sqlConnectString);
           SqlCommand command = new SqlCommand(sqlSelect, connection);
           connection.Open();
           using (SqlDataReader dr = command.ExecuteReader())
           {
               while (dr.Read())
               {
                   int contactID = dr.GetInt32(0);
                   String firstName = dr.GetString(1);
                   String middleName = dr.IsDBNull(2) ? null : dr.GetString(2);
                   String lastName = dr.GetString(3);
                   Console.WriteLine("{0}\t{1}, {2} {3}",contactID, lastName, firstName, middleName);
               }
           }
       }
   }</source>

Mapping .NET Data Provider Data Types to .NET Framework Data Types by casting

<source lang="csharp">using System; using System.Data.SqlClient; using System.Data.SqlTypes;

   class Program
   {
       static void Main(string[] args)
       {
           string sqlConnectString = "Data Source=(local);Integrated security=SSPI;Initial Catalog=AdventureWorks;";
           string sqlSelect = "SELECT TOP 5 ContactID, FirstName, MiddleName, LastName FROM Person.Contact";
           SqlConnection connection = new SqlConnection(sqlConnectString);
           SqlCommand command = new SqlCommand(sqlSelect, connection);
           connection.Open();
           using (SqlDataReader dr = command.ExecuteReader())
           {
               while (dr.Read())
               {
                   int contactID = (int)dr.GetSqlInt32(0);
                   String firstName = (string)dr.GetSqlString(1);
                   String middleName = dr.IsDBNull(2) ? null :(string)dr.GetSqlString(2);
                   String lastName = (string)dr.GetSqlString(3);
                   Console.WriteLine("{0}\t{1}, {2} {3}",contactID, lastName, firstName, middleName);
               }
           }
       }
   }</source>

Mapping .NET Data Provider Data Types to .NET Framework Data Types by converting

<source lang="csharp">using System; using System.Data.SqlClient; using System.Data.SqlTypes;

   class Program
   {
       static void Main(string[] args)
       {
           string sqlConnectString = "Data Source=(local);Integrated security=SSPI;Initial Catalog=AdventureWorks;";
           string sqlSelect = "SELECT TOP 5 ContactID, FirstName, MiddleName, LastName FROM Person.Contact";
           SqlConnection connection = new SqlConnection(sqlConnectString);
           SqlCommand command = new SqlCommand(sqlSelect, connection);
           connection.Open();
           using (SqlDataReader dr = command.ExecuteReader())
           {
               while (dr.Read())
               {
                   int contactID = Convert.ToInt32(dr[0]);
                   String firstName = Convert.ToString(dr[1]);
                   String middleName = Convert.ToString(dr[2]);
                   String lastName = Convert.ToString(dr[3]);
                   Console.WriteLine("{0}\t{1}, {2} {3}",contactID, lastName, firstName, middleName);
               }
           }
       }
   }</source>

Ordinal Indexer

<source lang="csharp">using System; using System.Data; using System.Data.SqlClient;

   class OrdinalIndexer
   {
       static void Main(string[] args)
       {
           string connString = @"server = .\sqlexpress;integrated security = true;database = northwind";
           string sql = @"select companyname,contactname from customers where contactname like "M%"";
           SqlConnection conn = new SqlConnection(connString);
           try
           {
               conn.Open();
               SqlCommand cmd = new SqlCommand(sql, conn);
               SqlDataReader rdr = cmd.ExecuteReader();
               while (rdr.Read())
               {
                   Console.WriteLine(" {0} | {1}",
                      rdr[0].ToString().PadLeft(25),
                      rdr[1].ToString().PadLeft(20));
               }
               rdr.Close();
           }
           catch (Exception e)
           {
               Console.WriteLine("Error Occurred: " + e);
           }
           finally
           {
               conn.Close();
           } 
       }
   }</source>

Ordinal Indexer demo

<source lang="csharp">using System; using System.Data; using System.Data.SqlClient; class MainClass {

  static void Main(string[] args)
  {
     string connString = "server=(local)\\SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI;";
     string sql = @"select firstname,lastname from employee where firstname like "M%"";
     SqlConnection conn = new SqlConnection(connString); 
     try
     {
        conn.Open();
        SqlCommand cmd = new SqlCommand(sql, conn);
        SqlDataReader rdr = cmd.ExecuteReader();
        while (rdr.Read())
        {
           Console.WriteLine(" {0} | {1}",rdr[0].ToString().PadLeft(25),rdr[1].ToString().PadLeft(20));
        }
        rdr.Close();
     }
     catch(Exception e)
     {
        Console.WriteLine("Error Occurred: " + e);
     }
     finally
     {
        conn.Close();
     }
  }

}</source>

Output fields from DataReader row

<source lang="csharp">using System; using System.Data.SqlClient;

   class Program
   {
       static void Main(string[] args)
       {
           string sqlConnectString = "Data Source=(local);Integrated security=SSPI;Initial Catalog=AdventureWorks;";
           string sqlSelect = "SELECT ContactID, FirstName, LastName FROM Person.Contact ";
           SqlConnection connection = new SqlConnection(sqlConnectString);
           SqlCommand command = new SqlCommand(sqlSelect, connection);
           connection.Open( );

           using (SqlDataReader dr = command.ExecuteReader( ))
           {
               while (dr.Read( ))
               {
                   Console.WriteLine(dr["ContactID"]);
                   Console.WriteLine(dr["LastName"]);
                   Console.WriteLine(dr["FirstName"]);
               }
           }
           connection.Close( );
       }
   }</source>

Reference result set by column index in SqlDataReader

<source lang="csharp">using System; using System.Data; using System.Data.SqlClient; class MainClass {

  static void Main(string[] args)
  {
     string connString = "server=(local)\\SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI;";
     string sql = @"select * from employee";
     SqlConnection conn = null;
     SqlDataReader reader = null;
     try
     {
        conn = new SqlConnection(connString);
        conn.Open();
        SqlCommand cmd = new SqlCommand(sql, conn);
        reader = cmd.ExecuteReader();
        Console.WriteLine("Querying database {0} with query {1}\n", conn.Database, cmd.rumandText);
        while(reader.Read()) {
           Console.WriteLine("{0} | {1}", reader["FirstName"].ToString().PadLeft(10) , reader[1].ToString().PadLeft(10) );
        }
     }catch (Exception e)
     {
        Console.WriteLine("Error: " + e);
     }
     finally
     {
        reader.Close();
        conn.Close();
     }
  }

}</source>

Reference result set by column name in SqlDataReader

<source lang="csharp">using System; using System.Data; using System.Data.SqlClient; class MainClass {

  static void Main(string[] args)
  {
     string connString = "server=(local)\\SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI;";
     string sql = @"select * from employee";
     SqlConnection conn = null;
     SqlDataReader reader = null;
     try
     {
        conn = new SqlConnection(connString);
        conn.Open();
        SqlCommand cmd = new SqlCommand(sql, conn);
        reader = cmd.ExecuteReader();
        Console.WriteLine("Querying database {0} with query {1}\n", conn.Database, cmd.rumandText);
        while(reader.Read()) {
           Console.WriteLine("{0} | {1}", reader["FirstName"].ToString().PadLeft(10) , reader[1].ToString().PadLeft(10) );
        }
     }catch (Exception e)
     {
        Console.WriteLine("Error: " + e);
     }
     finally
     {
        reader.Close();
        conn.Close();
     }
  }

}</source>

ResultSet Info

<source lang="csharp">using System; using System.Data; using System.Data.SqlClient;

   class ResultSetInfo
   {
       static void Main(string[] args)
       {
           string connString = @"server = .\sqlexpress;integrated security = true;database = northwind";
           string sql = @"select contactname,contacttitle from customers where contactname like "M%"";
           SqlConnection conn = new SqlConnection(connString);
           try
           {
               conn.Open();
               SqlCommand cmd = new SqlCommand(sql, conn);
               SqlDataReader rdr = cmd.ExecuteReader();
               Console.WriteLine("Column Name:\t{0} {1}",rdr.GetName(0),rdr.GetName(1));
               Console.WriteLine("Data Type:\t{0} {1}",rdr.GetDataTypeName(0),rdr.GetDataTypeName(1));
               while (rdr.Read())
               {
                   Console.WriteLine("{0} {1}",rdr.GetString(0),rdr.GetString(1));
               }
               Console.WriteLine("Number of columns in a row: {0}",rdr.FieldCount);
               Console.WriteLine(rdr.GetName(0));
               Console.WriteLine(rdr.GetOrdinal("contactname"));
               Console.WriteLine(rdr.GetFieldType(0));
               Console.WriteLine(rdr.GetName(1));
               Console.WriteLine(rdr.GetOrdinal("contacttitle"));
               Console.WriteLine(rdr.GetFieldType(1));
               rdr.Close();
           }
           catch (Exception e)
           {
               Console.WriteLine("Error Occurred: " + e);
           }
           finally
           {
               conn.Close();
           } 
       }
   }</source>

Retrieves data as native SQL Server types.

<source lang="csharp">using System; using System.Data.SqlClient; using System.Data.SqlTypes; public class NativeSqlServer {

   public static void Main() 
   {
       string SQL = "SELECT OrderID, CustomerID, OrderDate, Freight FROM Orders";
       SqlConnection con = new SqlConnection("Data Source=localhost;Initial Catalog=Northwind;Integrated Security=SSPI");
       SqlCommand cmd = new SqlCommand(SQL, con);
       SqlDataReader r;
       con.Open();
       r = cmd.ExecuteReader();
       while (r.Read()) {
           SqlInt32 orderID = r.GetSqlInt32(0);
           SqlString customerID = r.GetSqlString(1);
           SqlDateTime orderDate = r.GetSqlDateTime(2);
           SqlMoney freight = r.GetSqlMoney(3);
           Console.Write(orderID.ToString() + ", ");
           Console.Write(customerID + ", ");
           Console.Write(orderDate.ToString() + ", ");
           Console.Write(freight.ToString() + ", ");
           Console.WriteLine();
       }
   }    

}</source>

Retrieving a Result Set Stream Using a DataReader

<source lang="csharp">using System; using System.Data.SqlClient;

   class Program
   {
       static void Main(string[] args)
       {
           string sqlConnectString = "Data Source=(local);Integrated security=SSPI;Initial Catalog=AdventureWorks;";
           string sqlSelect = "SELECT ContactID, FirstName, LastName FROM Person.Contact ";
           SqlConnection connection = new SqlConnection(sqlConnectString);
           SqlCommand command = new SqlCommand(sqlSelect, connection);
           connection.Open( );
           using (SqlDataReader dr = command.ExecuteReader( )){
               while (dr.Read( ))
               {
                   Console.WriteLine(dr["ContactID"]);
                   Console.WriteLine(dr["LastName"]);
                   Console.WriteLine(dr["FirstName"]);
               }
           }
           connection.Close( );
       }
   }</source>

Schema Table from SqlDataReader

<source lang="csharp">using System; using System.Data; using System.Data.SqlClient;

   class SchemaTable
   {
       static void Main(string[] args)
       {
           string connString = @"server = .\sqlexpress;integrated security = true;database = northwind";
           string sql = @"select * from employees";
           SqlConnection conn = new SqlConnection(connString);
           try
           {
               conn.Open();
               SqlCommand cmd = new SqlCommand(sql, conn);
               SqlDataReader rdr = cmd.ExecuteReader();
               DataTable schema = rdr.GetSchemaTable();
               foreach (DataRow row in schema.Rows)
               {
                   foreach (DataColumn col in schema.Columns)
                       Console.WriteLine(col.ColumnName + " = " + row[col]);
               }
               rdr.Close();
           }
           catch (Exception e)
           {
               Console.WriteLine("Error Occurred: " + e);
           }
           finally
           {
               conn.Close();
           } 
       }
   }</source>

SqlDataReader

<source lang="csharp">using System; using System.Data; using System.Data.SqlClient; class MainClass {

 [STAThread]
 static void Main(string[] args)
 {
   string ConnectionString ="server=(local)\\SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI;";
   string SQL = "SELECT * FROM Employee";
   SqlConnection conn = new SqlConnection(ConnectionString);
   SqlCommand cmd = new SqlCommand(SQL, conn);
       
   conn.Open();
   SqlDataReader reader = cmd.ExecuteReader();
   Console.WriteLine("ID, FirstName");
   while (reader.Read())
   {
     Console.Write(reader["ID"].ToString() + ", ");
     Console.Write(reader["FirstName"].ToString() + ", ");
   }
   reader.Close();
   conn.Close();
 }

}</source>

SqlDataReader.GetString, GetDateTime

<source lang="csharp">using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Data.SqlClient;

class Report {

   static void Main(string[] args) {
       SqlConnection dataConnection = new SqlConnection();
       try {
           dataConnection.ConnectionString = "Integrated Security=true;Initial Catalog=Northwind;Data Source=.\\SQLExpress";
           dataConnection.Open();
           SqlCommand dataCommand = new SqlCommand();
           dataCommand.Connection = dataConnection;
           dataCommand.rumandText =
               "SELECT OrderID, OrderDate, " +
               "ShippedDate, ShipName, ShipAddress, ShipCity, " +
               "ShipCountry FROM Orders WHERE CustomerID="0001"";
           Console.WriteLine("About to execute: {0}\n\n", dataCommand.rumandText);
           SqlDataReader dataReader = dataCommand.ExecuteReader();
           while (dataReader.Read()) {
               int orderId = dataReader.GetInt32(0);
               if (dataReader.IsDBNull(2)) {
                   Console.WriteLine("Order {0} not yet shipped\n\n", orderId);
               } else {
                   DateTime orderDate = dataReader.GetDateTime(1);
                   DateTime shipDate = dataReader.GetDateTime(2);
                   string shipName = dataReader.GetString(3);
                   string shipAddress = dataReader.GetString(4);
                   string shipCity = dataReader.GetString(5);
                   string shipCountry = dataReader.GetString(6);
                   Console.WriteLine(
                       "Order: {0}\nPlaced: {1}\nShipped: {2}\n" +
                       "To Address: {3}\n{4}\n{5}\n{6}\n\n", orderId, orderDate,
                       shipDate, shipName, shipAddress, shipCity, shipCountry);
               }
           }
           dataReader.Close();
       } catch (SqlException e) {
           Console.WriteLine("Error accessing the database: {0}", e.Message);
       } finally {
           dataConnection.Close();
       }
   }

}</source>

SqlDataReader: RecordsAffected

<source lang="csharp">using System; using System.Data; using System.Data.OleDb; using System.Data.SqlClient; class MainClass {

 static void Main(string[] args)
   {
       string ConnectionString ="server=(local)\\SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI;";
       string SQL = "SELECT * FROM Employee";
       SqlConnection conn = new SqlConnection(ConnectionString);
       SqlCommand cmd = new SqlCommand(SQL, conn);
       
       conn.Open();
       SqlDataReader reader = cmd.ExecuteReader();
       Console.WriteLine("ID, FirstName");
       while (reader.Read())
       {
           Console.Write(reader["ID"].ToString() + ", ");
           Console.Write(reader["FirstName"].ToString() + ", ");
       }
   Console.WriteLine("Total Number of records affected: "+ reader.RecordsAffected.ToString() );
       reader.Close();
       conn.Close();
   }

}</source>

Typed Accessors: int, varchar and decimal

<source lang="csharp">using System; using System.Data; using System.Data.SqlClient; class MainClass {

  static void Main(string[] args)
  {
     string connString = @"server = .\sqlexpress;integrated security = true;database = northwind";
     string sql = @"select productname,unitprice,unitsinstock,discontinued from products";
     SqlConnection conn = new SqlConnection(connString); 
     try
     {
        conn.Open();
        SqlCommand cmd = new SqlCommand(sql, conn);
        SqlDataReader rdr = cmd.ExecuteReader();
        while (rdr.Read())
        {
           Console.WriteLine(
              "{0}\t {1}\t\t {2}\t {3}",
              // nvarchar
              rdr.GetString(0).PadRight(30), 
              // money
              rdr.GetDecimal(1),
              // smallint
              rdr.GetInt16(2),
              // bit
              rdr.GetBoolean(3));
        }
        rdr.Close();
     }
     catch(Exception e)
     {
        Console.WriteLine("Error Occurred: " + e);
     }
     finally
     {
        conn.Close();
     }
  }

}</source>

Updating Data Using CommondBuilder

<source lang="csharp">using System; using System.Data; using System.Data.SqlClient; class MainClass {

 static void Main(string[] args)
 {
   SqlConnection MyConnection = new SqlConnection("server=(local)\\SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI;");
   SqlDataAdapter MyDataAdapter = new SqlDataAdapter("SELECT ID, FirstName FROM Employee", MyConnection);
   DataSet MyDataSet = new DataSet();
   MyDataAdapter.Fill(MyDataSet);
   MyDataSet.Tables[0].Rows[0][0] = 55;
   SqlCommandBuilder MyCmd = new SqlCommandBuilder(MyDataAdapter);
   MyDataAdapter.Update(MyDataSet);
 }

}</source>

Use indexer to read data

<source lang="csharp">using System; using System.Data.SqlClient; class FirstExample {

   public static void Main() {
       try {
           SqlConnection mySqlConnection = new SqlConnection("server=localhost;database=Northwind;uid=sa;pwd=sa");
           SqlCommand mySqlCommand = mySqlConnection.CreateCommand();
           mySqlCommand.rumandText =
             "SELECT CustomerID, CompanyName, ContactName, Address " +
             "FROM Customers " +
             "WHERE CustomerID = "ALFKI"";
           mySqlConnection.Open();
           SqlDataReader mySqlDataReader = mySqlCommand.ExecuteReader();
           mySqlDataReader.Read();
           Console.WriteLine("mySqlDataReader[\" CustomerID\"] = " + mySqlDataReader["CustomerID"]);
           Console.WriteLine("mySqlDataReader[\" CompanyName\"] = " + mySqlDataReader["CompanyName"]);
           Console.WriteLine("mySqlDataReader[\" ContactName\"] = " + mySqlDataReader["ContactName"]);
           Console.WriteLine("mySqlDataReader[\" Address\"] = " + mySqlDataReader["Address"]);
           mySqlDataReader.Close();
           mySqlConnection.Close();
       } catch (SqlException e) {
           Console.WriteLine("A SqlException was thrown");
           Console.WriteLine("Number = " + e.Number);
           Console.WriteLine("Message = " + e.Message);
           Console.WriteLine("StackTrace:\n" + e.StackTrace);
       }
   }

}</source>

Use loop to read all data in SqlDataReader

<source lang="csharp">using System; using System.Data; using System.Data.SqlClient; class MainClass {

  static void Main(string[] args)
  {
     string connString = "server=(local)\\SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI;";
     string sql = @"select firstName from Employee";
     SqlConnection conn = new SqlConnection(connString); 
     try
     {
        conn.Open();
        SqlCommand cmd = new SqlCommand(sql, conn);
        SqlDataReader rdr = cmd.ExecuteReader();
        while (rdr.Read())
        {
           Console.WriteLine("{0}", rdr[0]); 
        }
        rdr.Close();
     }
     catch(Exception e)
     {
        Console.WriteLine("Error Occurred: " + e);
     }
     finally
     {
        conn.Close();
     }
  }

}</source>

Use SqlDataReader to retrieve data in the resultset

<source lang="csharp">using System; using System.Data; using System.Data.OleDb; using System.Data.SqlClient; class MainClass {

 static void Main(string[] args)
 {
       string SQL = "SELECT * FROM Employee";
   string ConnectionString ="server=(local)\\SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI;";
   SqlConnection conn = new SqlConnection(ConnectionString);
       SqlCommand cmd = new SqlCommand(SQL, conn);
       conn.Open();
       SqlDataReader reader = cmd.ExecuteReader();
       try 
       {
           while (reader.Read()) 
           {
               Console.Write("ID:"+reader.GetInt32(0).ToString() );
               Console.Write(" ,");
               Console.WriteLine("Name:" + reader.GetString(1).ToString() );
           }
       }
       finally 
       {
           reader.Close();
           conn.Close();
       }   
 }

}</source>

ID:2 ,Name:G

Use SQL Server Data Provider

<source lang="csharp">using System; using System.Data; using System.Data.SqlClient; class MainClass {

  static void Main(string[] args)
  {
     string connString = "server=(local)\\SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI;";
     string sql = @"select * from employee";
     SqlConnection conn = null;
     SqlDataReader reader = null;
     try
     {
        conn = new SqlConnection(connString);
        conn.Open();
        SqlCommand cmd = new SqlCommand(sql, conn);
        reader = cmd.ExecuteReader();
        Console.WriteLine("Querying database {0} with query {1}\n", conn.Database, cmd.rumandText);
        while(reader.Read()) {
           Console.WriteLine("{0} | {1}", reader["FirstName"].ToString().PadLeft(10) , reader[1].ToString().PadLeft(10) );
        }
     }catch (Exception e)
     {
        Console.WriteLine("Error: " + e);
     }
     finally
     {
        reader.Close();
        conn.Close();
     }
  }

}</source>

Using a DataReader to read row by row

<source lang="csharp">using System; using System.Data; using System.Data.SqlClient; class MainClass {

 static void Main(string[] args)
 {
   SqlConnection MyConnection = new SqlConnection("server=(local)\\SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI;");
   MyConnection.Open();
   SqlCommand MyCommand = new SqlCommand("SELECT * FROM Employee", MyConnection);
   SqlDataReader MyDataReader = MyCommand.ExecuteReader(CommandBehavior.CloseConnection);
   while (MyDataReader.Read())
   {
     Console.WriteLine(MyDataReader[0] + " " + MyDataReader[1]);
   }
   MyConnection.Close();
 }

}</source>