Csharp/CSharp Tutorial/ADO.Net/SqlDataReader

Accessing Data Values in a DataReader

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( )));
        }
    }

DataReader has records using HasRows property

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);
        }
    }

Data Reading with SqlDataReader

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();
    }
  }

Determining the Number of Records Returned in a DataReader

/*
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);
            }
        }
    }

Get data by data type using SqlDataReader

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();
    }   
  }
}

ID:2 ,Name:G

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

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]);
        }
    }

Handle Multiple Results

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();
            } 
        }
    }

how to read a table schema

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();
    }
}

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

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);
                }
            }
        }
    }

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

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);
                }
            }
        }
    }

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

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);
                }
            }
        }
    }

Ordinal Indexer

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();
            } 
        }
    }

Ordinal Indexer demo

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();
      }
   }
}

Output fields from DataReader row

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( );
        }
    }

Reference result set by column index in SqlDataReader

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();
      }
   }
}

Reference result set by column name in SqlDataReader

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();
      }
   }
}

ResultSet Info

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();
            } 
        }
    }

Retrieves data as native SQL Server types.

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();
        }
    }    
}

Retrieving a Result Set Stream Using a DataReader

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( );
        }
    }

Schema Table from SqlDataReader

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();
            } 
        }
    }

SqlDataReader

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();
  }
}

SqlDataReader.GetString, GetDateTime

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();
        }
    }
}

SqlDataReader: RecordsAffected

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();
    }
}

Typed Accessors: int, varchar and decimal

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();
      }
   }
}

Updating Data Using CommondBuilder

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);
  }
}

Use indexer to read data

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);
        }
    }
}

Use loop to read all data in SqlDataReader

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();
      }
   }
}

Use SqlDataReader to retrieve data in the resultset

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();
        }   
  }
}

ID:2 ,Name:G

Use SQL Server Data Provider

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();
      }
   }
}

Using a DataReader to read row by row

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();
  }
}