Csharp/C Sharp/Database ADO.net/SqlDataReader

Deal with Multiple Results

<source lang="csharp"> 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();
        }
     }  
  }
          
      </source>

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

<source lang="csharp"> 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();
 }

}

      </source>

Get column index from SqlDataReader

<source lang="csharp">

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

      </source>

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

<source lang="csharp"> 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();
   }

}

</source>

Read data from SqlDataReader

<source lang="csharp">

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

      </source>

Reference data in SqlDataReader by column name

<source lang="csharp">

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

      </source>

SqlDataReader Ordinal Indexer

<source lang="csharp"> 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();
        }
     }
  }

      </source>

Use A Data Reader

<source lang="csharp"> /*

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

}

      </source>

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

<source lang="csharp"> 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();
   }

}

</source>

Use While loop to read query result data from SqlDataReader

<source lang="csharp"> 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();
        }
     }
  } 

      </source>