Csharp/C Sharp/Database ADO.net/Select

How to control the command behavior to return a single row

using System;
using System.Data;
using System.Data.SqlClient;
class SingleRowCommandBehavior
{
  public static void Main()
  {
    SqlConnection mySqlConnection =new SqlConnection("server=(local)\\SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI;");
    SqlCommand mySqlCommand = mySqlConnection.CreateCommand();
    mySqlCommand.rumandText ="SELECT ID, FirstName, LastName FROM Employee";
    mySqlConnection.Open();
    SqlDataReader mySqlDataReader = mySqlCommand.ExecuteReader(CommandBehavior.SingleRow);
    while (mySqlDataReader.Read()){
      Console.WriteLine("mySqlDataReader[\" ID\"] = " +
        mySqlDataReader["ID"]);
      Console.WriteLine("mySqlDataReader[\" FirstName\"] = " +
        mySqlDataReader["FirstName"]);
      Console.WriteLine("mySqlDataReader[\" LastName\"] = " +
        mySqlDataReader["LastName"]);
    }
    mySqlDataReader.Close();
    mySqlConnection.Close();
  }
}

how to execute a SELECT statement using a SqlCommand object

 

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.rumandText =
          "SELECT TOP 5 CustomerID, CompanyName, ContactName, Address " +
          "FROM Customers " +
          "ORDER BY CustomerID";
        mySqlConnection.Open();
        SqlDataReader mySqlDataReader = mySqlCommand.ExecuteReader();
        while (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();
    }
}

Populate a DataSet object with a range of rows from a SELECT statement

using System;
using System.Data;
using System.Data.SqlClient;
class PopulateDataSetUsingRange
{
  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 ID, FirstName, LastName " +
      "FROM Employee " +
      "ORDER BY ID";
    SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter();
    mySqlDataAdapter.SelectCommand = mySqlCommand;
    DataSet myDataSet = new DataSet();
    mySqlConnection.Open();
    Console.WriteLine("Retrieving rows from the Employee table");
    int numberOfRows = mySqlDataAdapter.Fill(myDataSet, 1, 3, "Employee");
    Console.WriteLine("numberOfRows = " + numberOfRows);
    mySqlConnection.Close();
    DataTable myDataTable = myDataSet.Tables["Employee"];
    foreach (DataRow myDataRow in myDataTable.Rows)
    {
      Console.WriteLine("ID = " + myDataRow["ID"]);
      Console.WriteLine("FirstName = " + myDataRow["FirstName"]);
      Console.WriteLine("LastName = " + myDataRow["LastName"]);
    }
  }
}