Csharp/C Sharp/Database ADO.net/Select

Материал из .Net Framework эксперт
Перейти к: навигация, поиск

How to control the command behavior to return a single row

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

}

      </source>


how to execute a SELECT statement using a SqlCommand object

<source lang="csharp">

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

}

</source>


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

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

}

      </source>