ADO.NET 4 快速上手（3）——DataReader读数据

三、DataReader读数据

1.   执行SQL读表

string connectionString = "Data Source=(local);Initial Catalog=Northwind;Integrated Security=true";// Provide the query string with a parameter placeholder.string queryString ="SELECT ProductID, UnitPrice, ProductName from dbo.products WHERE UnitPrice > @pricePoint "    + "ORDER BY UnitPrice DESC;";// Specify the parameter value.int paramValue = 5;// Create and open the connection in a using block. This// ensures that all resources will be closed and disposed// when the code exits.using (SqlConnection connection = new SqlConnection(connectionString)){    // Create the Command and Parameter objects.    SqlCommand command = new SqlCommand(queryString, connection);    command.Parameters.AddWithValue("@pricePoint", paramValue);    // Open the connection in a try/catch block.    // Create and execute the DataReader, writing the result    // set to the console window.    try    {        connection.Open();        SqlDataReader reader = command.ExecuteReader();        while (reader.Read())        {            Console.WriteLine("\t{0}\t{1}\t{2}",            reader[0], reader[1], reader[2]);        }        reader.Close();    }    catch (Exception ex)    {        Console.WriteLine(ex.Message);    }    Console.ReadLine();}

2.调有参存储过程，返回表

using (SqlConnection connection = new SqlConnection(connectionString)){    // Create the command and set its properties.    SqlCommand command = new SqlCommand();    command.Connection = connection;    command.CommandText = "SalesByCategory";    command.CommandType = CommandType.StoredProcedure;    // Add the input parameter and set its properties.    SqlParameter parameter = new SqlParameter();    parameter.ParameterName = "@CategoryName";    parameter.SqlDbType = SqlDbType.NVarChar;    parameter.Direction = ParameterDirection.Input;    parameter.Value = categoryName;    // Add the parameter to the Parameters collection.    command.Parameters.Add(parameter);    // Open the connection and execute the reader.    connection.Open();    SqlDataReader reader = command.ExecuteReader();    if (reader.HasRows)    {        while (reader.Read())        {            Console.WriteLine("{0}: {1:C}", reader[0], reader[1]);        }    }    else    {        Console.WriteLine("No rows found.");    }    reader.Close();}