Database ADO.net

control SqlCommand to return a single row

Posted on by coder 
   
 

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

class SingleRowCommandBehavior {
    public static void Main() {
        SqlConnection mySqlConnection = new SqlConnection("server=localhost;database=Northwind;uid=sa;pwd=sa");

        SqlCommand mySqlCommand = mySqlConnection.CreateCommand();
        mySqlCommand.CommandText = "SELECT ProductID, ProductName, QuantityPerUnit, UnitPrice FROM Products";

        mySqlConnection.Open();
        SqlDataReader mySqlDataReader = mySqlCommand.ExecuteReader(CommandBehavior.SingleRow);

        while (mySqlDataReader.Read()) {
            Console.WriteLine("mySqlDataReader[" ProductID"] = " + mySqlDataReader["ProductID"]);
            Console.WriteLine("mySqlDataReader[" ProductName"] = " + mySqlDataReader["ProductName"]);
            Console.WriteLine("mySqlDataReader[" QuantityPerUnit"] = " + mySqlDataReader["QuantityPerUnit"]);
            Console.WriteLine("mySqlDataReader[" UnitPrice"] = " + mySqlDataReader["UnitPrice"]);
        }

        mySqlDataReader.Close();
        mySqlConnection.Close();
    }
}

Use ExecuteXmlReader() to run a SELECT statement that returns XML

Posted on by coder 
   
 

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

class ExecuteXmlReader {
    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 " +
          "FROM Products " +
          "ORDER BY ProductID " +
          "FOR XML AUTO";

        mySqlConnection.Open();
        XmlReader myXmlReader = mySqlCommand.ExecuteXmlReader();
        myXmlReader.Read();
        while (!myXmlReader.EOF) {
            Console.WriteLine(myXmlReader.ReadOuterXml());
        }

        myXmlReader.Close();
        mySqlConnection.Close();
    }
}

Populate a DataSet object using a SELECT statement

Posted on by coder 
   
 

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

class PopulateDataSetUsingSelect {
    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 " +
          "FROM Products " +
          "ORDER BY ProductID";
        SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter();
        mySqlDataAdapter.SelectCommand = mySqlCommand;
        DataSet myDataSet = new DataSet();
        mySqlConnection.Open();
        Console.WriteLine("Retrieving rows from the Products table");
        int numberOfRows = mySqlDataAdapter.Fill(myDataSet, "Products");
        Console.WriteLine("numberOfRows = " + numberOfRows);
        mySqlConnection.Close();
        DataTable myDataTable = myDataSet.Tables["Products"];
        foreach (DataRow myDataRow in myDataTable.Rows) {
            Console.WriteLine("ProductID = " + myDataRow["ProductID"]);
            Console.WriteLine("ProductName = " + myDataRow["ProductName"]);
            Console.WriteLine("UnitPrice = " + myDataRow["UnitPrice"]);
        }
    }
}

Async Command Object

Posted on by coder 
   
 

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;

using System.Runtime.Remoting.Messaging;
using System.Threading;

class Program {
    static void Main(string[] args) {
        SqlConnection cn = new SqlConnection();
        cn.ConnectionString = "uid=sa;pwd=;Initial Catalog=Cars;Asynchronous Processing=true;Data Source=(local)";
        cn.Open();

        string strSQL = "WaitFor Delay '00:00:02';Select * From Inventory";
        SqlCommand myCommand = new SqlCommand(strSQL, cn);

        IAsyncResult itfAsynch;
        itfAsynch = myCommand.BeginExecuteReader(CommandBehavior.CloseConnection);

        while (!itfAsynch.IsCompleted) {
            Console.WriteLine("Working on main thread...");
            Thread.Sleep(1000);
        }
        SqlDataReader myDataReader = myCommand.EndExecuteReader(itfAsynch);
        while (myDataReader.Read()) {
            Console.WriteLine("-> Make: {0}, PetName: {1}, Color: {2}.",
              myDataReader["Make"].ToString().Trim(),
              myDataReader["PetName"].ToString().Trim(),
              myDataReader["Color"].ToString().Trim());
        }
        myDataReader.Close();
    }
}

execute multiple SELECT statements(select) using a SqlCommand object and read the results using a SqlDataReader object

Posted on by coder 
   
 

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

class ExecuteSelect {
    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 " +
          "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]);
            }
        } while (mySqlDataReader.NextResult());

        mySqlDataReader.Close();
        mySqlConnection.Close();
    }
}

Execute multiple SQL statements(insert) using a SqlCommand object

Posted on by coder 


   

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

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

    SqlCommand mySqlCommand = mySqlConnection.CreateCommand();

    mySqlCommand.CommandText =
      "INSERT INTO Employee (ID, FirstName) " +
      "VALUES (11, 'Jason');" +
      "SELECT ID, FirstName " +
      "FROM Employee " +
      "WHERE ID = 11;" +
      "UPDATE Employee " +
      "SET FirstName = 'Jason 2' " +
      "WHERE ID = 11;" +
      "SELECT ID, FirstName " +
      "FROM Employee " +
      "WHERE ID = 11;" +
      "DELETE FROM Employee " +
      "WHERE ID = 11;";

    mySqlConnection.Open();

    SqlDataReader mySqlDataReader = mySqlCommand.ExecuteReader();

    do
    {
      while (mySqlDataReader.Read())
      {
        Console.WriteLine("mySqlDataReader[0] = " + mySqlDataReader[0]);
        Console.WriteLine("mySqlDataReader[1] = " + mySqlDataReader[1]);
      }
      Console.WriteLine(""); // visually split the results
    } while (mySqlDataReader.NextResult());

    mySqlDataReader.Close();
    mySqlConnection.Close();
  }
}

Get row count by 'ExecuteScalar'

Posted on by coder 
   

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

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

         SqlCommand thisCommand = new SqlCommand("SELECT COUNT(*) FROM Employee", thisConnection);

         try {
            thisConnection.Open();

            Console.WriteLine("Number of Employees is: {0}",
               thisCommand.ExecuteScalar());
         }
         catch (SqlException ex) 
         {
            Console.WriteLine(ex.ToString());
         }
         finally 
         {
            thisConnection.Close();
            Console.WriteLine("Connection Closed.");
         }
      }
   }