Csharp/C Sharp/Database ADO.net/SqlDataReader

Deal with Multiple Results

using System;
using System.Data;
using System.Data.SqlClient;
   class MultipleResults {
      static void Main(string[] args) {
         string connString = "server=(local)\\SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI";
         string sql1 = @"select FirstName from Employee;";
         string sql2 = @"select FirstName, LastName from Employee;";
         string sql = sql1 + sql2;
         SqlConnection conn = new SqlConnection(connString);
         try
         {
            conn.Open();
            SqlCommand cmd = new SqlCommand(sql, conn);
            SqlDataReader reader = cmd.ExecuteReader();
            do{
                while(reader.Read()) {
                   Console.WriteLine("{0} : {1}", reader[0], reader[0]);
                }
                Console.WriteLine("".PadLeft(60, "="));
            } while(reader.NextResult());
            reader.Close();
         } catch(Exception e) {
            Console.WriteLine("Error Occurred: " + e);
         } finally {
            conn.Close();
         }
      }  
   }

Execute multiple SELECT statements using a SqlCommand object and read the results using a SqlDataReader object

using System;
using System.Data;
using System.Data.SqlClient;
class ExecuteSelect
{
  public static void Main()
  {
    SqlConnection mySqlConnection =new SqlConnection("server=(local)\\SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI;");
    SqlCommand mySqlCommand = mySqlConnection.CreateCommand();
    mySqlCommand.rumandText =
      "SELECT TOP 5 ProductID, ProductName " +
      "FROM Products " +
      "ORDER BY ProductID;" +
      "SELECT TOP 3 CustomerID, CompanyName " +
      "FROM Customers " +
      "ORDER BY CustomerID;" +
      "SELECT TOP 6 OrderID, CustomerID " +
      "FROM Orders " +
      "ORDER BY OrderID;";
    mySqlConnection.Open();
    SqlDataReader mySqlDataReader = mySqlCommand.ExecuteReader();
    do
    {
      while (mySqlDataReader.Read())
      {
        Console.WriteLine("mySqlDataReader[0] = " + mySqlDataReader[0]);
        Console.WriteLine("mySqlDataReader[1] = " + mySqlDataReader[1]);
      }
      Console.WriteLine("");
    } while (mySqlDataReader.NextResult());
    mySqlDataReader.Close();
    mySqlConnection.Close();
  }
}

Get column index from SqlDataReader

    using System;
    using System.Data.SqlClient;
  
  class ConnectToSqlConnection {
    static void Main(string[] args)  {
      String sConn = "server=(local)\\SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI";
        String sSQL = "select id, firstname, lastname from Employee";
      SqlConnection oConn = new SqlConnection(sConn);
      oConn.Open();
      SqlCommand oCmd = new SqlCommand(sSQL, oConn);
      SqlDataReader oReader = oCmd.ExecuteReader();
      int idxID = oReader.GetOrdinal("id");
      int idxFirstName = oReader.GetOrdinal("firstname");
      int idxLastName = oReader.GetOrdinal("lastname");
      while(oReader.Read()) {
        Console.WriteLine("{0} {1} {2}",
          oReader.GetValue(idxID),
          oReader.GetValue(idxFirstName),
          oReader.GetValue(idxLastName));
      }
    }
  }

how to read column values as C# types using the Get* methods

 
using System;
using System.Data;
using System.Data.SqlClient;
class StronglyTypedColumnValues {
    public static void Main() {
        SqlConnection mySqlConnection = new SqlConnection("server=localhost;database=Northwind;uid=sa;pwd=sa");
        SqlCommand mySqlCommand = mySqlConnection.CreateCommand();
        mySqlCommand.rumandText =
          "SELECT TOP 5 ProductID, ProductName, UnitPrice, " +
          "UnitsInStock, Discontinued " +
          "FROM Products " +
          "ORDER BY ProductID";
        mySqlConnection.Open();
        SqlDataReader productsSqlDataReader =
          mySqlCommand.ExecuteReader();
        int productIDColPos = productsSqlDataReader.GetOrdinal("ProductID");
        int productNameColPos = productsSqlDataReader.GetOrdinal("ProductName");
        int unitPriceColPos = productsSqlDataReader.GetOrdinal("UnitPrice");
        int unitsInStockColPos = productsSqlDataReader.GetOrdinal("UnitsInStock");
        int discontinuedColPos = productsSqlDataReader.GetOrdinal("Discontinued");
        Console.WriteLine("ProductID .NET type = " + productsSqlDataReader.GetFieldType(productIDColPos));
        Console.WriteLine("ProductName .NET type = " + productsSqlDataReader.GetFieldType(productNameColPos));
        Console.WriteLine("UnitPrice .NET type = " + productsSqlDataReader.GetFieldType(unitPriceColPos));
        Console.WriteLine("UnitsInStock .NET type = " + productsSqlDataReader.GetFieldType(unitsInStockColPos));
        Console.WriteLine("Discontinued .NET type = " + productsSqlDataReader.GetFieldType(discontinuedColPos));
        Console.WriteLine("ProductID database type = " + productsSqlDataReader.GetDataTypeName(productIDColPos));
        Console.WriteLine("ProductName database type = " + productsSqlDataReader.GetDataTypeName(productNameColPos));
        Console.WriteLine("UnitPrice database type = " + productsSqlDataReader.GetDataTypeName(unitPriceColPos));
        Console.WriteLine("UnitsInStock database type = " + productsSqlDataReader.GetDataTypeName(unitsInStockColPos));
        Console.WriteLine("Discontinued database type = " + productsSqlDataReader.GetDataTypeName(discontinuedColPos));
        while (productsSqlDataReader.Read()) {
            int productID = productsSqlDataReader.GetInt32(productIDColPos);
            Console.WriteLine("productID = " + productID);
            string productName = productsSqlDataReader.GetString(productNameColPos);
            Console.WriteLine("productName = " + productName);
            decimal unitPrice = productsSqlDataReader.GetDecimal(unitPriceColPos);
            Console.WriteLine("unitPrice = " + unitPrice);
            short unitsInStock = productsSqlDataReader.GetInt16(unitsInStockColPos);
            Console.WriteLine("unitsInStock = " + unitsInStock);
            bool discontinued = productsSqlDataReader.GetBoolean(discontinuedColPos);
            Console.WriteLine("discontinued = " + discontinued);
        }
        productsSqlDataReader.Close();
        mySqlConnection.Close();
    }
}

Read data from SqlDataReader

    using System;
    using System.Data.SqlClient;
  
  class ConnectToSqlConnection {
    static void Main(string[] args)  {
      String sConn = "server=(local)\\SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI";
        String sSQL = "select id, firstname, lastname from Employee";
      SqlConnection oConn = new SqlConnection(sConn);
      oConn.Open();
      SqlCommand oCmd = new SqlCommand(sSQL, oConn);
      SqlDataReader oReader = oCmd.ExecuteReader();
      int idxID = oReader.GetOrdinal("id");
      int idxFirstName = oReader.GetOrdinal("firstname");
      int idxLastName = oReader.GetOrdinal("lastname");
      while(oReader.Read()) {
        Console.WriteLine("{0} {1} {2}",
          oReader.GetValue(idxID),
          oReader.GetValue(idxFirstName),
          oReader.GetValue(idxLastName));
      }
    }
  }

Reference data in SqlDataReader by column name

    using System;
    using System.Data.SqlClient;
  class pubsdemo
  {
    static void Main(string[] args)
    {
      String sConn = "server=(local)\\SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI";
        String sSQL = "select id, firstname, lastname from Employee";
      SqlConnection oConn = new SqlConnection(sConn);
      oConn.Open();
      SqlCommand oCmd = new SqlCommand(sSQL, oConn);
      SqlDataReader oReader = oCmd.ExecuteReader();
      while(oReader.Read()) {
        Console.WriteLine("{0} {1} {2}",
          oReader["id"],
          oReader["firstname"],
          oReader["lastname"]);
      }
    }
  }

SqlDataReader Ordinal Indexer

using System;
using System.Data; 
using System.Data.SqlClient; 
   class OrdinalIndexer
   {
      static void Main(string[] args)
      {
         string connString = "server=(local)\\SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI";
         string sql = @"select FirstName, LastName from Employee";
         SqlConnection conn = new SqlConnection(connString);
         try
         {
            conn.Open();
            SqlCommand cmd = new SqlCommand(sql, conn);
            SqlDataReader reader = cmd.ExecuteReader();
            Console.WriteLine("\t{0}   {1}", "First Name".PadRight(25),"Last Name".PadRight(20));
            Console.WriteLine("\t{0}   {1}",  "============".PadRight(25), "============".PadRight(20));
            while(reader.Read()) {
               Console.WriteLine(" {0} | {1}", 
                  reader[0].ToString().PadLeft(25),
                  reader[1].ToString().PadLeft(20));
            }
            reader.Close();
         } catch(Exception e) {
            Console.WriteLine("Error Occurred: " + e);
         } finally {
            conn.Close();
         }
      }
   }

Use A Data Reader

/*
 * C# Programmers Pocket Consultant
 * Author: Gregory S. MacBeth
 * Email: gmacbeth@comporium.net
 * Create Date: June 27, 2003
 * Last Modified Date:
 * Version: 1
 */
using System;
using System.Data;
using System.Data.SqlClient;
namespace Client.Chapter_13___ADO.NET
{
    public class UsingADataReader
    {
        static void Main(string[] args)
        {
            SqlConnection MyConnection = new SqlConnection(@"Data Source=(local); Initial Catalog = CaseManager; Integrated Security=true");
            MyConnection.Open();
            SqlCommand MyCommand = new SqlCommand("SELECT * FROM CaseInfo", MyConnection);
            SqlDataReader MyDataReader = MyCommand.ExecuteReader(CommandBehavior.CloseConnection);
            while (MyDataReader.Read())
            {
                Console.WriteLine(MyDataReader[0] + " " + MyDataReader[1]);
            }
            MyConnection.Close();
        }
    }
}

use the GetOrdinal() from DataReader to get the numeric positions of a column

 
using System;
using System.Data;
using System.Data.SqlClient;
class UsingColumnOrdinals {
    public static void Main() {
        SqlConnection mySqlConnection = new SqlConnection("server=localhost;database=Northwind;uid=sa;pwd=sa");
        SqlCommand mySqlCommand = mySqlConnection.CreateCommand();
        mySqlCommand.rumandText =
          "SELECT TOP 5 ProductID, ProductName, UnitPrice, " +
          "UnitsInStock, Discontinued " +
          "FROM Products " +
          "ORDER BY ProductID";
        mySqlConnection.Open();
        SqlDataReader productsSqlDataReader = mySqlCommand.ExecuteReader();
        int productIDColPos = productsSqlDataReader.GetOrdinal("ProductID");
        int productNameColPos = productsSqlDataReader.GetOrdinal("ProductName");
        int unitPriceColPos = productsSqlDataReader.GetOrdinal("UnitPrice");
        int unitsInStockColPos = productsSqlDataReader.GetOrdinal("UnitsInStock");
        int discontinuedColPos = productsSqlDataReader.GetOrdinal("Discontinued");
        while (productsSqlDataReader.Read()) {
            Console.WriteLine("ProductID = " + productsSqlDataReader[productIDColPos]);
            Console.WriteLine("ProductName = " + productsSqlDataReader[productNameColPos]);
            Console.WriteLine("UnitPrice = " + productsSqlDataReader[unitPriceColPos]);
            Console.WriteLine("UnitsInStock = " + productsSqlDataReader[unitsInStockColPos]);
            Console.WriteLine("Discontinued = " + productsSqlDataReader[discontinuedColPos]);
        }
        productsSqlDataReader.Close();
        mySqlConnection.Close();
    }
}

Use While loop to read query result data from SqlDataReader

using System;
using System.Data; 
using System.Data.SqlClient; 
   class DataLooper{
      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 reader = cmd.ExecuteReader();
            while(reader.Read()) {
               Console.WriteLine("{0}", reader[0]);
            }
            reader.Close();
         }
         catch(Exception e)
         {
            Console.WriteLine("Error Occurred: " + e);
         }
         finally
         {
            conn.Close();
         }
      }
   }