Tuesday, August 18, 2009

Populate a DataSet object using a store procedure in csharp

SqlConnection mySqlConnection =new SqlConnection("server=(local)\\SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI;");

SqlCommand mySqlCommand = mySqlConnection.CreateCommand();
mySqlCommand.CommandText = "EXECUTE CustOrderHist @CustomerID";
mySqlCommand.Parameters.Add("@CustomerID", SqlDbType.NVarChar, 5).Value = "ALFKI";

SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter();
mySqlDataAdapter.SelectCommand = mySqlCommand;
DataSet myDataSet = new DataSet();
mySqlConnection.Open();
Console.WriteLine("Retrieving rows from the CustOrderHist() Procedure");
int numberOfRows = mySqlDataAdapter.Fill(myDataSet, "CustOrderHist");
Console.WriteLine("numberOfRows = " + numberOfRows);
mySqlConnection.Close();

DataTable myDataTable = myDataSet.Tables["CustOrderHist"];
foreach (DataRow myDataRow in myDataTable.Rows)
{
Console.WriteLine("ProductName = " + myDataRow["ProductName"]);
Console.WriteLine("Total = " + myDataRow["Total"]);
}

No comments:

Post a Comment