Author: coder

Use While loop to read query result data from SqlDataReader

Posted on by coder 
   

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

Use A Data Reader

Posted on by coder 
   


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

Reference data in SqlDataReader by column name

Posted on by coder 
   


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

Read data from SqlDataReader

Posted on by coder 
   


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

read column values as Sql* types using the GetSql* methods

Posted on by coder 
   


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

class StronglyTypedColumnValuesSql
{
  public static void Main()
  {
    SqlConnection mySqlConnection =new SqlConnection("server=(local)SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI;");

    SqlCommand mySqlCommand = mySqlConnection.CreateCommand();

    mySqlCommand.CommandText =
       "SELECT TOP 5 ID, Name, UnitPrice, " +
       "UnitsInStock, Discontinued " +
       "FROM Products " +
       "ORDER BY ProductID";

    mySqlConnection.Open();

    SqlDataReader productsSqlDataReader = mySqlCommand.ExecuteReader();

    int productIDColPos = productsSqlDataReader.GetOrdinal("ID");
    int productNameColPos = productsSqlDataReader.GetOrdinal("Name");
    int unitPriceColPos = productsSqlDataReader.GetOrdinal("UnitPrice");
    int unitsInStockColPos = productsSqlDataReader.GetOrdinal("UnitsInStock");
    int discontinuedColPos = productsSqlDataReader.GetOrdinal("Discontinued");

    while (productsSqlDataReader.Read())
    {
      SqlInt32 productID = productsSqlDataReader.GetSqlInt32(productIDColPos);
      Console.WriteLine("productID = " + productID);

      SqlString productName = productsSqlDataReader.GetSqlString(productNameColPos);
      Console.WriteLine("productName = " + productName);

      SqlMoney unitPrice = productsSqlDataReader.GetSqlMoney(unitPriceColPos);
      Console.WriteLine("unitPrice = " + unitPrice);

      SqlInt16 unitsInStock = productsSqlDataReader.GetSqlInt16(unitsInStockColPos);
      Console.WriteLine("unitsInStock = " + unitsInStock);

      SqlBoolean discontinued = productsSqlDataReader.GetSqlBoolean(discontinuedColPos);
      Console.WriteLine("discontinued = " + discontinued);
    }

    productsSqlDataReader.Close();
    mySqlConnection.Close();
  }
}

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

Posted on by coder 
   
 

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.CommandText =
          "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();
    }
}

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

Posted on by coder 
   
 

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.CommandText =
          "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();
    }
}