The DataSet Class phần 2

Chia sẻ: Tuan Nghia | Ngày: | Loại File: PDF | Số trang:12

Thêm vào BST

Báo xấu

79
lượt xem 11
download

Download Vui lòng tải xuống để xem tài liệu đầy đủ

Note The Fill() method will actually open and close the Connection for you if you don't open it first, however, it is better to explicitly open and close the Connection because that way it is clearer what your program is doing.

Chủ đề:

Bình luận(0) Đăng nhập để gửi bình luận!

Lưu

Nội dung Text: The DataSet Class phần 2

Note The Fill() method will actually open and close the Connection for you if you don't open it first, however, it is better to explicitly open and close the Connection because that way it is clearer what your program is doing. Also, if you're calling the Fill() method repeatedly over a short span of code, you'll want to keep the database connection open and close it only when you're finished. The DataSet is now populated with a DataTable named Products. You can read the Products DataTable from myDataSet using the following example: DataTable myDataTable = myDataSet.Tables["Products"]; You can also read the Products DataTable using an int value: DataTable myDataTable = myDataSet.Tables[0]; You can display the column values for each row in myDataTable using the following foreach loop that iterates over the DataRow objects stored in myDataTable; notice the use of the myDataTable object's Rows property: foreach (DataRow myDataRow in myDataTable.Rows) { Console.WriteLine("ProductID = " + myDataRow["ProductID"]); Console.WriteLine("ProductName = " + myDataRow["ProductName"]); Console.WriteLine("UnitPrice = " + myDataRow["UnitPrice"]); } The Rows property returns a DataRowCollection object that allows you to access all the DataRow objects stored in myDataTable. You can read each column value in a DataRow using the name of the column; for example, to read the ProductID column value you use myDataRow["ProductID"]. You can also use the numeric position of the column; for example, myDataRow[0] returns the value for the first column. This is the ProductID column. You can also use the following code to iterate over all the DataTable, DataRow, and DataColumn objects stored in myDataSet: foreach (DataTable myDataTable in myDataSet.Tables) { foreach (DataRow myDataRow in myDataTable.Rows) { foreach (DataColumn myDataColumn in myDataTable.Columns) { Console.WriteLine(myDataColumn + "= " +
myDataRow[myDataColumn]); } } } Notice you don't need to know the names of the DataTable or DataColumn objects to display them. The call to the WriteLine() method displays myDataColumn, which returns the name of the column, and myDataRow[myDataColumn], which returns the column value for the current row. Note You'll see the details of the DataTable, DataRow, and DataColumn classes in Chapter 11. Listing 10.1 shows a program that uses the code examples shown in this section. Listing 10.1: POPULATEDATASETUSINGSELECT.CS /* PopulateDataSetUsingSelect.cs illustrates how to populate a DataSet object using a SELECT statement */ using System; using System.Data; using System.Data.SqlClient; class PopulateDataSetUsingSelect { public static void Main() { SqlConnection mySqlConnection = new SqlConnection( "server=localhost;database=Northwind;uid=sa;pwd=sa" ); // create a SqlCommand object and set its CommandText property // to a SELECT statement that retrieves the top 5 rows from // the Products table SqlCommand mySqlCommand = mySqlConnection.CreateCommand(); mySqlCommand.CommandText = "SELECT TOP 5 ProductID, ProductName, UnitPrice " + "FROM Products " + "ORDER BY ProductID";
// create a SqlDataAdapter object and set its SelectCommand // property to the SqlCommand object SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter(); mySqlDataAdapter.SelectCommand = mySqlCommand; // create a DataSet object DataSet myDataSet = new DataSet(); // open the database connection mySqlConnection.Open(); // use the Fill() method of the SqlDataAdapter object to // retrieve the rows from the table, storing the rows locally // in a DataTable of the DataSet object Console.WriteLine("Retrieving rows from the Products table"); int numberOfRows = mySqlDataAdapter.Fill(myDataSet, "Products"); Console.WriteLine("numberOfRows = " + numberOfRows); // close the database connection mySqlConnection.Close(); // get the DataTable object from the DataSet object DataTable myDataTable = myDataSet.Tables["Products"]; // display the column values for each row in the DataTable, // using a DataRow object to access each row in the DataTable foreach (DataRow myDataRow in myDataTable.Rows) { Console.WriteLine("ProductID = " + myDataRow["ProductID"]); Console.WriteLine("ProductName = " + myDataRow["ProductName"]); Console.WriteLine("UnitPrice = " + myDataRow["UnitPrice"]); } } } The output from this program is as follows: Retrieving rows from the Products table numberOfRows = 5 ProductID = 1 ProductName = Chai UnitPrice = 18 ProductID = 2
ProductName = Chang UnitPrice = 19 ProductID = 3 ProductName = Aniseed Syrup UnitPrice = 10 ProductID = 4 ProductName = Chef Anton's Cajun Seasoning UnitPrice = 22 ProductID = 5 ProductName = Chef Anton's Gumbo Mix UnitPrice = 21.35 Using a Range of Rows In this section, you'll learn how to populate a DataSet with a range of rows. Now, the Fill() method is overloaded and a partial list of Fill() methods is as follows: int Fill(DataSet myDataSet) int Fill(DataTable myDataTable) int Fill(DataSet myDataSet, string dataTableName) int Fill(DataSet myDataSet, int startRow, int numOfRows, string dataTableName) where dataTableName specifies a string containing the name of the DataTable to fill. startRow is an int that specifies the position of the row in the result set to read (starting at 0). NumOfRows is an int that specifies the number rows to read. The range of rows from startRow to startRow + numOfRows is then stored in the DataTable . The int returned by the Fill() method is the number of rows retrieved from the database. As you can see, the final Fill() method allows you to populate a DataSet with a range of rows. The following example shows the use of this Fill() method to store a range of rows. It retrieves the top five rows from the Products table, but stores only three rows in the Products DataTable, starting at position 1 (because rows are numbered starting at 0, position 1 corresponds to the second row in the result set returned by the SELECT statement):
SqlCommand mySqlCommand = mySqlConnection.CreateCommand(); mySqlCommand.CommandText = "SELECT TOP 5 ProductID, ProductName, UnitPrice " + "FROM Products " + "ORDER BY ProductID"; SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter(); mySqlDataAdapter.SelectCommand = mySqlCommand; DataSet myDataSet = new DataSet(); int numberOfRows = mySqlDataAdapter.Fill(myDataSet, 1, 3, "Products"); The numberOfRows variable is set to 3-the number of rows myDataSet was populated with. One thing to remember is the DataAdapter still retrieves all five rows from the Products table, but only three are actually used to populate the DataSet: the other two are thrown away. Listing 10.2 shows a program that uses the code examples shown in this section. Listing 10.2: POPULATEDATASETUSINGRANGE.CS /* PopulateDataSetUsingRange.cs illustrates how to 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=localhost;database=Northwind;uid=sa;pwd=sa" ); // create a SqlCommand object and set its CommandText property // to a SELECT statement that retrieves the top 5 rows from // the Products table SqlCommand mySqlCommand = mySqlConnection.CreateCommand(); mySqlCommand.CommandText = "SELECT TOP 5 ProductID, ProductName, UnitPrice " + "FROM Products " + "ORDER BY ProductID";
SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter(); mySqlDataAdapter.SelectCommand = mySqlCommand; DataSet myDataSet = new DataSet(); mySqlConnection.Open(); // use the Fill() method of the SqlDataAdapter object to // retrieve the rows from the table, storing a range of rows // in a DataTable of the DataSet object Console.WriteLine("Retrieving rows from the Products table"); int numberOfRows = mySqlDataAdapter.Fill(myDataSet, 1, 3, "Products"); Console.WriteLine("numberOfRows = " + numberOfRows); mySqlConnection.Close(); DataTable myDataTable = myDataSet.Tables["Products"]; foreach (DataRow myDataRow in myDataTable.Rows) { Console.WriteLine("ProductID = " + myDataRow["ProductID"]); Console.WriteLine("ProductName = " + myDataRow["ProductName"]); Console.WriteLine("UnitPrice = " + myDataRow["UnitPrice"]); } } } The output from this program is as follows: Retrieving rows from the Products table numberOfRows = 3 ProductID = 2 ProductName = Chang UnitPrice = 19 ProductID = 3 ProductName = Aniseed Syrup UnitPrice = 10 ProductID = 4 ProductName = Chef Anton's Cajun Seasoning UnitPrice = 22 Using a Stored Procedure
You can also populate a DataSet object using a stored procedure that returns a result set. For example, the SQL Server Northwind database contains a stored procedure called CustOrderHist() that returns the products and total number of the products ordered by a customer. The customer's CustomerID is passed as a parameter to CustOrderHist(). Listing 10.3 shows the definition of the CustOrderHist() stored procedure. Listing 10.3: CUSTORDERHIST() STORED PROCEDURE CREATE PROCEDURE CustOrderHist @CustomerID nchar(5) AS SELECT ProductName, Total=SUM(Quantity) FROM Products P, [Order Details] OD, Orders O, Customers C WHERE C.CustomerID = @CustomerID AND C.CustomerID = O.CustomerID AND O.OrderID = OD.OrderID AND OD.ProductID = P.ProductID GROUP BY ProductName Note You don't have to create the CustOrderHist() procedure yourself. It's already defined in the Northwind database. Calling CustOrderHist() and populating a DataSet with the returned result set is straightforward. For example, the following code creates a SqlCommand object, sets its CommandText object to an EXECUTE statement that calls CustOrderHist(), and sets the @CustomerID parameter to ALFKI (parameters are covered in Chapter 8, "Executing Database Commands"): SqlCommand mySqlCommand = mySqlConnection.CreateCommand(); mySqlCommand.CommandText = "EXECUTE CustOrderHist @CustomerID"; mySqlCommand.Parameters.Add( "@CustomerID", SqlDbType.NVarChar, 5).Value = "ALFKI"; You then use code similar to thatshown in the previous section to populate a DataSet with the result set returned by CustOrderHist(): SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter(); mySqlDataAdapter.SelectCommand = mySqlCommand; DataSet myDataSet = new DataSet(); mySqlConnection.Open(); int numberOfRows = mySqlDataAdapter.Fill(myDataSet, "CustOrderHist"); mySqlConnection.Close();
The CustOrderHist DataTable contained within myDataSet is populated with the result set returned by the CustOrderHist() procedure. Listing 10.4 shows a program that uses the code examples shown in this section. Listing 10.4: POPULATEDATASETUSINGPROCEDURE.CS /* PopulateDataSetUsingProcedure.cs illustrates how to populate a DataSet object using a stored procedure */ using System; using System.Data; using System.Data.SqlClient; class PopulateDataSetUsingProcedure { public static void Main() { SqlConnection mySqlConnection = new SqlConnection( "server=localhost;database=Northwind;uid=sa;pwd=sa" ); // create a SqlCommand object and set its CommandText property // to call the CustOrderHist() stored procedure 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"]); } } } The output from this program is as follows: Retrieving rows from the CustOrderHist() Procedure numberOfRows = 11 ProductName = Aniseed Syrup Total = 6 ProductName = Chartreuse verte Total = 21 ProductName = Escargots de Bourgogne Total = 40 ProductName = Flotemysost Total = 20 ProductName = Grandma's Boysenberry Spread Total = 16 ProductName = Lakkalikööri Total = 15 ProductName = Original Frankfurter grüne Soße Total = 2 ProductName = Raclette Courdavault Total = 15 ProductName = Rössle Sauerkraut Total = 17 ProductName = Spegesild Total = 2 ProductName = Vegie-spread Total = 20 Populating a DataSet with Multiple DataTable Objects You can populate a DataSet with multiple DataTable objects. You might want to do that when you need to access the information stored in multiple tables in the database. You may use any of the following techniques to populate a DataSet with multiple DataTable objects:
• Use multiple SELECT statements in the same SelectCommand. • Change the CommandText property of the SelectCommand before each call to the Fill() method. • Use multiple DataAdapter objects to populate the same DataSet. Let's take a look at each of these techniques. Using Multiple SELECT Statements in the Same SelectCommand The following example sets the CommandText property of a SqlCommand object to two separate SELECT statements: SqlCommand mySqlCommand = mySqlConnection.CreateCommand(); mySqlCommand.CommandText = "SELECT TOP 2 ProductID, ProductName, UnitPrice " + "FROM Products " + "ORDER BY ProductID;" + "SELECT CustomerID, CompanyName " + "FROM Customers " + "WHERE CustomerID = 'ALFKI';"; Notice that each SELECT statement is separated by a semicolon (;). When these SELECT statements are run, two result sets are returned: one containing the two rows from the Products table, the second containing the one row from the Customers table. These two result sets are stored in separate DataTable objects by the following code: SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter(); mySqlDataAdapter.SelectCommand = mySqlCommand; DataSet myDataSet = new DataSet(); mySqlConnection.Open(); int numberOfRows = mySqlDataAdapter.Fill(myDataSet); mySqlConnection.Close(); Notice the use of the Fill(myDataSet) method, which doesn't specify the name of the DataTable to be created. Instead, the names of the two DataTable objects used to store the result sets are automatically set to the default of Table and Table1. Table stores the result set from the Products table, and Table1 stores the result set from the Customers table. The name of a DataTable object is stored in its TableName property, which you can change. For example, the following code changes the name of the Table DataSet to Products and the Table1 DataSet to Customers:
myDataSet.Tables["Table"].TableName = "Products"; myDataSet.Tables["Table1"].TableName = "Customers"; Listing 10.5 shows a program that uses the code examples shown in this section. Listing 10.5: MULTIPLEDATATABLES.CS /* MutlipleDataTables.cs illustrates how to populate a DataSet with multiple DataTable objects using multiple SELECT statements */ using System; using System.Data; using System.Data.SqlClient; class MultipleDataTables { public static void Main() { SqlConnection mySqlConnection = new SqlConnection( "server=localhost;database=Northwind;uid=sa;pwd=sa" ); // create a SqlCommand object and set its CommandText property // to mutliple SELECT statements SqlCommand mySqlCommand = mySqlConnection.CreateCommand(); mySqlCommand.CommandText = "SELECT TOP 2 ProductID, ProductName, UnitPrice " + "FROM Products " + "ORDER BY ProductID;" + "SELECT CustomerID, CompanyName " + "FROM Customers " + "WHERE CustomerID = 'ALFKI';"; SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter(); mySqlDataAdapter.SelectCommand = mySqlCommand; DataSet myDataSet = new DataSet(); mySqlConnection.Open(); int numberOfRows = mySqlDataAdapter.Fill(myDataSet); Console.WriteLine("numberOfRows = " + numberOfRows); mySqlConnection.Close();
// change the TableName property of the DataTable objects myDataSet.Tables["Table"].TableName = "Products"; myDataSet.Tables["Table1"].TableName = "Customers"; foreach (DataTable myDataTable in myDataSet.Tables) { Console.WriteLine("\nReading from the " + myDataTable.TableName + "DataTable"); foreach (DataRow myDataRow in myDataTable.Rows) { foreach (DataColumn myDataColumn in myDataTable.Columns) { Console.WriteLine(myDataColumn + "= " + myDataRow[myDataColumn]); } } } } }