/* Mastering Visual C# .NET by Jason Price, Mike Gunderloy Publisher: Sybex; ISBN: 0782129110 */ /* Example23_7.cs illustrates how to call a SQL Server stored procedure */ using System; using System.Data; using System.Data.SqlClient; public class Example23_7 { public static void Main() { // formulate a string containing the details of the // database connection string connectionString = "server=localhost;database=Northwind;uid=sa;pwd=sa"; // create a SqlConnection object to connect to the // database, passing the connection string to the constructor SqlConnection mySqlConnection = new SqlConnection(connectionString); // formulate a string containing the name of the // stored procedure string procedureString = "Ten Most Expensive Products"; // create a SqlCommand object to hold the SQL statement SqlCommand mySqlCommand = mySqlConnection.CreateCommand(); // set the CommandText property of the SqlCommand object to // procedureString mySqlCommand.CommandText = procedureString; // set the CommandType property of the SqlCommand object // to CommandType.StoredProcedure mySqlCommand.CommandType = CommandType.StoredProcedure; // open the database connection using the // Open() method of the SqlConnection object mySqlConnection.Open(); // run the stored procedure mySqlCommand.ExecuteNonQuery(); // create a SqlDataAdapter object SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter(); // set the SelectCommand property of the SqlAdapter object // to the SqlCommand object mySqlDataAdapter.SelectCommand = mySqlCommand; // create a DataSet object to store the results of // the stored procedure call DataSet myDataSet = new DataSet(); // use the Fill() method of the SqlDataAdapter object to // retrieve the rows from the stored procedure call, // storing the rows in a DataTable named Products mySqlDataAdapter.Fill(myDataSet, "Products"); // display the rows in the Products DataTable Console.WriteLine("The ten most expensive products are:"); DataTable products = myDataSet.Tables["Products"]; foreach (DataRow product in products.Rows) { Console.WriteLine("Product name = " + product["TenMostExpensiveProducts"]); Console.WriteLine("Unit price = " + product["UnitPrice"]); } // close the database connection using the Close() method // of the SqlConnection object mySqlConnection.Close(); } }