The DataSet Class

The DataSet Class

You use an object of the DataSet class to represent a local copy of the information stored in the database. You can make changes to that local copy in your DataSet and then later synchronize those changes with the database through a DataAdapter. A DataSet can represent database structures such as tables, rows, and columns. You can even add constraints to your locally stored tables to enforce unique and foreign key constraints.

Figure 10.1 shows the DataSet and its relationship to some of the objects you can store within it. As you can see from this figure, you can store multiple DataTable objects in a DataSet, and so on.

click to expand
Figure 10.1: Some of the DataSet objects

Table 10.4 shows some of the DataSet properties.

Table 10.4: DataSet PROPERTIES

PROPERTY

TYPE

DESCRIPTION

CaseSensitive

bool

Gets or sets a bool value that indicates whether string comparisons within DataTable objects are case-sensitive.

DataSetName

string

Gets or sets the name of the current DataSet object.

DefaultViewManager

DataViewManager

Gets a custom view of the data stored in the DataSet object. You use a view to filter, search, and navigate the DataSet.

EnforceConstraints

bool

Gets or sets a bool value that indicates whether constraint rules are followed when updating information in the DataSet object.

ExtendedProperties

PropertyCollection

Gets a collection (PropertyCollection) of user information. You can use the PropertyCollection to store strings with any additional information you want. You use the Add() method through ExtendedProperties to add a string.

HasErrors

bool

Gets a bool value that indicates whether there are errors in any of the rows in the tables of the DataSet object.

Locale

CultureInfo

Gets or sets a CultureInfo object for the DataSet. A CultureInfo object contains information about a specific culture including its name, writing system, and calendar.

Namespace

string

Gets or sets the namespace for the DataSet object. The namespace is a string that is used when reading and writing an XML document using the ReadXml(), WriteXml(), ReadXmlSchema(), and WriteXmlSchema() methods. The namespace is used to scope the XML attributes and elements.

Prefix

string

Gets or sets the XML prefix for the DataSet namespace. The prefix is used in an XML document to identify the elements that belong to the DataSet object's namespace.

Relations

DataRelationCollection

Gets the collection of relations (DataRelationCollection) that allows navigation from a parent table to a child table. A DataRelationCollection consists of DataRelation objects.

Tables

DataTableCollection

Gets the collection of tables (DataTableCollection) that contains the DataTable objects stored in the DataSet.

Table 10.5 shows some of the DataSet methods.

Table 10.5: DataSet METHODS

METHOD

RETURN TYPE

DESCRIPTION

AcceptChanges()

void

Commits all the changes made to the DataSet object since it was loaded or since the last time the AcceptChanges() method was called.

BeginInit()

void

Used by the Visual Studio .NET designer to initialize a DataSet used in a form or component.

Clear()

void

Removes all rows from all tables in the DataSet object.

Clone()

DataSet

Clones the structure of the DataSet object and returns that clone. The clone contains all the schemas, relations, and constraints.

Copy()

DataSet

Copies the structure and data of the DataSet object and returns that copy. The copy contains all the schemas, relations, constraints, and data.

EndInit()

void

Used by the Visual Studio .NET designer to end initialization of a DataSet used in a form or component.

GetChanges()

DataSet

Overloaded. Gets a copy of all the changes made to the DataSet object since it was last loaded or since the last time the AcceptChanges() method was called.

GetXml()

string

Returns the XML representation of the data stored in the DataSet object.

GetXmlSchema()

string

Returns the XML representation of the schema for the DataSet object.

HasChanges()

bool

Overloaded. Returns a bool value that indicates whether the DataSet object has changes that haven't been committed.

Merge()

void

Overloaded. Merges this DataSet with another specified DataSet object.

ReadXml()

XmlReadMode

Overloaded. Loads the data from an XML file into the DataSet object.

ReadXmlSchema()

void

Overloaded. Loads a schema from an XML file into the DataSet object.

RejectChanges()

void

Undoes all the changes made to the DataSet object since it was created or since the last time the AcceptChanges() method was called.

Reset()

void

Resets the DataSet object to its original state.

WriteXml()

void

Overloaded. Writes out the data from the DataSet object to an XML file.

WriteXmlSchema()

void

Overloaded. Writes out the schema of the DataSet object to an XML file.

Table 10.6 shows one of the DataSet events.

Table 10.6: DataSet EVENT

EVENT

EVENT HANDLER

DESCRIPTION

MergeFailed

MergeFailedEventHandler

Fires when an attempt is made add a DataRow to a DataSet when a DataRow with the same primary key value already exists in that DataSet.

In the next section, you'll learn how to create a DataSet object.

Creating a DataSet Object

You create a DataSet object using one of the following DataSet constructors:

 DataSet() DataSet(string dataSetNameString) 

where dataSetNameString is the string assigned to the DataSetName property of your DataSet object. The setting of the DataSetName property is optional.

The following example uses the DataSet() constructor to create a DataSet object:

 DataSet myDataSet = new DataSet(); 

The next example uses the DataSet(string dataSetNameString) constructor to create a DataSet object:

 DataSet myDataSet = new DataSet("myDataSet"); 

Populating a DataSet Object

In this section, you'll learn how to populate a DataSet using the Fill() method of a DataAdapter. Specifically, you'll see how to populate a DataSet using

  • A SELECT statement

  • A range of rows

  • A stored procedure

Using a SELECT Statement

Before you populate a DataSet you first need a Connection, a Command, and a DataAdapter:

 SqlConnection mySqlConnection =   new SqlConnection(     "server=localhost;database=Northwind;uid=sa;pwd=sa"   ); 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(); 

Notice the mySqlCommand object contains a SELECT statement that retrieves the ProductID, ProductName, and UnitPrice columns of the top five rows from the Products table.

start sidebar
RETRIEVING FROM MULTIPLE TABLES

Of course, you're not limited to a SELECT statement that retrieves from a single table. You can use a SELECT statement that retrieves from multiple tables using a join, however, you should typically avoid doing that because a DataTable is meant to be used to store rows from a single database table.

end sidebar

Next, to populate myDataSet with the rows from the Products table, you call the Fill() method of mySqlDataAdapter. For example:

 int numberOfRows = mySqlDataAdapter.Fill(myDataSet, "Products"); 

The int returned by the Fill() method is the number of rows synchronized between the DataSet and the database via the DataAdapter. In the previous example, the int is the number of rows copied from the Products table to myDataSet and is set to 5-the number of rows retrieved by the SELECT statement shown earlier.

The first parameter to the Fill() method is your DataSet, and the second parameter is a string containing the name you want to assign to the DataTable created in your DataSet.

Note 

The name you assign to your DataTable doesn't have to be the same as the name of the database table. You can use any string of text, though typically you should still use the same name, since it will help you keep track of what database table was used to populate the DataTable.

When you call the Fill() method for the first time, the following steps are performed by ADO.NET:

  1. The SELECT statement in your SqlCommand is executed.

  2. A new DataTable object is created in your DataSet.

  3. Your DataTable is populated with the result set returned by the SELECT statement.

If you're finished with the database after calling the Fill() method, you should close your Connection object using the Close() method:

 mySqlConnection.Close(); 

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

start example
 /*   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"]);     }   } } 
end example

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

start example
 /*   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"]);     }   } } 
end example

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

start example
 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 
end example

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

start example
 /*   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"]);     }   } } 
end example

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

start example
 /*   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]);         }       }     }   } } 
end example

The output from this program is as follows:

 numberOfRows = 3 Reading from the Products DataTable ProductID = 1 ProductName = Chai UnitPrice = 18 ProductID = 2 ProductName = Chang UnitPrice = 19 Reading from the Customers DataTable CustomerID = ALFKI CompanyName = Alfreds Futterkiste 

Changing the CommandText Property of the SelectCommand

You can also populate a DataSet with multiple DataTable objects by changing the CommandText property of the SelectCommand for your DataAdapter object before each call to the Fill() method. First, the following code populates a DataSet with a DataTable containing two rows from the Products table:

 SqlCommand mySqlCommand = mySqlConnection.CreateCommand(); mySqlCommand.CommandText =   "SELECT TOP 2 ProductID, ProductName, UnitPrice " +   "FROM Products " +   "ORDER BY ProductID"; SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter(); mySqlDataAdapter.SelectCommand = mySqlCommand; DataSet myDataSet = new DataSet(); mySqlConnection.Open(); int numberOfRows = mySqlDataAdapter.Fill(myDataSet, "Products"); 

The myDataSet object now contains a DataTable named Products.

Next, the CommandText property for the SelectCommand of mySqlDataAdapter is changed to a SELECT statement that retrieves rows from the Customers table, and the Fill() method is called again:

 mySqlDataAdapter.SelectCommand.CommandText =   "SELECT CustomerID, CompanyName " +   "FROM Customers " +   "WHERE CustomerID = 'ALFKI'"; numberOfRows = mySqlDataAdapter.Fill(myDataSet, "Customers"); mySqlConnection.Close(); 

The myDataSet object now contains an additional DataTable named Customers.

Listing 10.6 shows a program that uses the code examples shown in this section.

Listing 10.6: MULTIPLEDATATABLES2.CS

start example
 /*   MutlipleDataTables2.cs illustrates how to populate a DataSet   object with multiple DataTable objects by changing the   CommandText property of a DataAdapter object's SelectCommand */ using System; using System.Data; using System.Data.SqlClient; class MultipleDataTables2 {   public static void Main()   {     SqlConnection mySqlConnection =       new SqlConnection(         "server=localhost;database=Northwind;uid=sa;pwd=sa"       );     SqlCommand mySqlCommand = mySqlConnection.CreateCommand();     mySqlCommand.CommandText =       "SELECT TOP 2 ProductID, ProductName, UnitPrice " +       "FROM Products " +       "ORDER BY ProductID";     SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter();     mySqlDataAdapter.SelectCommand = mySqlCommand;     DataSet myDataSet = new DataSet();     mySqlConnection.Open();     int numberOfRows = mySqlDataAdapter.Fill(myDataSet, "Products");     Console.WriteLine("numberOfRows = " + numberOfRows);     // change the CommandText property of the SelectCommand     mySqlDataAdapter.SelectCommand.CommandText =       "SELECT CustomerID, CompanyName " +       "FROM Customers " +       "WHERE CustomerID = 'ALFKI'";     numberOfRows = mySqlDataAdapter.Fill(myDataSet, "Customers");     Console.WriteLine("numberOfRows = " + numberOfRows);     mySqlConnection.Close();     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]);         }       }     }   } } 
end example

The output from this program is as follows:

 numberOfRows = 2 numberOfRows = 1 Reading from the Products DataTable ProductID = 1 ProductName = Chai UnitPrice = 18 ProductID = 2 ProductName = Chang UnitPrice = 19 Reading from the Customers DataTable CustomerID = ALFKI CompanyName = Alfreds Futterkiste 

Using Multiple DataAdapter Objects to Populate the Same DataSet Object

You can also populate the same DataSet with multiple DataTable objects using different DataAdapter objects. For example, assume you already have a DataSet named myDataSet that was populated using a SqlDataAdapter named mySqlDataAdapter, and that myDataSet currently contains a DataTable named Products. The following example creates another SqlDataAdapter and uses it to populate myDataSet with another DataTable named Customers:

 SqlDataAdapter mySqlDataAdapter2 = new SqlDataAdapter(); mySqlDataAdapter2.SelectCommand = mySqlCommand; mySqlDataAdapter2.SelectCommand.CommandText =   "SELECT CustomerID, CompanyName " +   "FROM Customers " +   "WHERE CustomerID = 'ALFKI'"; numberOfRows = mySqlDataAdapter2.Fill(myDataSet, "Customers"); 

Listing 10.7 shows a program that uses the code examples shown in this section.

Listing 10.7: MULTIPLEDATATABLES3.CS

start example
 /*   MutlipleDataTables3.cs illustrates how to populate a DataSet   object with multiple DataTable objects using multiple   DataAdapter objects to populate the same DataSet object */ using System; using System.Data; using System.Data.SqlClient; class MultipleDataTables3 {   public static void Main()   {     SqlConnection mySqlConnection =       new SqlConnection(         "server=localhost;database=Northwind;uid=sa;pwd=sa"       );     SqlCommand mySqlCommand = mySqlConnection.CreateCommand();     mySqlCommand.CommandText =       "SELECT TOP 2 ProductID, ProductName, UnitPrice " +       "FROM Products " +       "ORDER BY ProductID";     SqlDataAdapter mySqlDataAdapter1 = new SqlDataAdapter();     mySqlDataAdapter1.SelectCommand = mySqlCommand;     DataSet myDataSet = new DataSet();     mySqlConnection.Open();     int numberOfRows = mySqlDataAdapter1.Fill(myDataSet, "Products");     Console.WriteLine("numberOfRows = " + numberOfRows);     // create another DataAdapter object     SqlDataAdapter mySqlDataAdapter2 = new SqlDataAdapter();     mySqlDataAdapter2.SelectCommand = mySqlCommand;     mySqlDataAdapter2.SelectCommand.CommandText =       "SELECT CustomerID, CompanyName " +       "FROM Customers " +       "WHERE CustomerID = 'ALFKI'";     numberOfRows = mySqlDataAdapter2.Fill(myDataSet, "Customers");     Console.WriteLine("numberOfRows = " + numberOfRows);     mySqlConnection.Close();     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]);         }       }     }   } } 
end example

The output from this program is as follows:

 numberOfRows = 2 numberOfRows = 1 Reading from the Products DataTable ProductID = 1 ProductName = Chai UnitPrice = 18 ProductID = 2 ProductName = Chang UnitPrice = 19 Reading from the Customers DataTable CustomerID = ALFKI CompanyName = Alfreds Futterkiste 

Merging DataRow, DataSet, and DataTable Objects into Another DataSet

In this section, you'll learn how to use the Merge() method to merge DataRow, DataSet, and DataTable objects into another DataSet. You might want to do this when you have multiple sources of data; for example, you might get data from many regional offices that is sent to headquarters, and you need to merge all that data into one DataSet.

The Merge() method is overloaded as follows:

 void Merge(DataRow[] myDataRows) void Merge(DataSet myDataSet) void Merge(DataTable myDataTable) void Merge(DataSet myDataSet, bool preserveChanges) void Merge(DataRow[] myDataRows, bool preserveChanges,  MissingSchemaAction myMissingSchemaAction) void Merge(DataSet myDataSet, bool preserveChanges,  MissingSchemaAction myMissingSchemaAction) void Merge(DataTable myDataTable, bool preserveChanges,  MissingSchemaAction myMissingSchemaAction) 

where

  • PreserveChanges specifies whether changes in the current DataSet (the DataSet with the Merge() method that is called) are to be kept.

  • MyMissingSchemaAction specifies the action to take when the current DataSet doesn't have the same tables or columns as the DataRow, DataSet, or DataTable being merged into that DataSet.

You set myMissingSchemaAction to one of the constants defined in the System.Data.MissingSchemaAction enumeration. Table 10.7 shows the constants defined in the MissingSchemaAction enumeration.

Table 10.7: MissingSchemaAction ENUMERATION MEMBERS

CONSTANT

DESCRIPTION

Add

The column or table is added to the current DataSet. Add is the default.

AddWithKey

The column and primary key information is added to the current DataSet.

Error

A SystemException is thrown.

Ignore

The column or table is ignored and not read.

Listing 10.8 illustrates the use of the Merge() method.

Listing 10.8: MERGE.CS

start example
 /*   Merge.cs illustrates how to use the Merge() method */ using System; using System.Data; using System.Data.SqlClient; class Merge {   public static void Main()   {     SqlConnection mySqlConnection =       new SqlConnection(         "server=localhost;database=Northwind;uid=sa;pwd=sa"       );     SqlCommand mySqlCommand = mySqlConnection.CreateCommand();     // populate myDataSet with three rows from the Customers table     mySqlCommand.CommandText =       "SELECT CustomerID, CompanyName, ContactName, Address " +       "FROM Customers " +       "WHERE CustomerID IN ('ALFKI', 'ANATR', 'ANTON')";     SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter();     mySqlDataAdapter.SelectCommand = mySqlCommand;     DataSet myDataSet = new DataSet();     mySqlConnection.Open();     mySqlDataAdapter.Fill(myDataSet, "Customers");     // populate myDataSet2 with two rows from the Customers table     mySqlCommand.CommandText =       "SELECT CustomerID, CompanyName, ContactName, Address " +       "FROM Customers " +       "WHERE CustomerID IN ('AROUT', 'BERGS')";     DataSet myDataSet2 = new DataSet();     mySqlDataAdapter.Fill(myDataSet2, "Customers2");     // populate myDataSet3 with five rows from the Products table     mySqlCommand.CommandText =       "SELECT TOP 5 ProductID, ProductName, UnitPrice " +       "FROM Products " +       "ORDER BY ProductID";     DataSet myDataSet3 = new DataSet();     mySqlDataAdapter.Fill(myDataSet3, "Products");     mySqlConnection.Close();     // merge myDataSet2 into myDataSet     myDataSet.Merge(myDataSet2);     // merge myDataSet3 into myDataSet     myDataSet.Merge(myDataSet3, true, MissingSchemaAction.Add);     // display the rows in myDataSet     foreach (DataTable myDataTable in myDataSet.Tables)     {       Console.WriteLine("\nReading from the " + myDataTable + "DataTable");       foreach (DataRow myDataRow in myDataTable.Rows)       {         foreach (DataColumn myDataColumn in myDataTable.Columns)         {           Console.WriteLine(myDataColumn + "= " +             myDataRow[myDataColumn]);         }       }     }   } } 
end example

The output from this program is as follows:

 Reading from the Customers DataTable CustomerID = ALFKI CompanyName = Alfreds Futterkiste ContactName = Maria Anders Address = Obere Str. 57 CustomerID = ANATR CompanyName = Ana Trujillo3 Emparedados y helados ContactName = Ana Trujillo Address = Avda. de la Constitución 2222 CustomerID = ANTON CompanyName = Antonio Moreno Taquería ContactName = Antonio Moreno Address = Mataderos 2312 Reading from the Customers2 DataTable CustomerID = AROUT CompanyName = Around the Horn ContactName = Thomas Hardy Address = 120 Hanover Sq. CustomerID = BERGS CompanyName = Berglunds snabbköp ContactName = Christina Berglund Address = Berguvsvägen 8 Reading from the Products DataTable 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 




Mastering C# Database Programming
Mastering the SAP Business Information Warehouse: Leveraging the Business Intelligence Capabilities of SAP NetWeaver
ISBN: 0764596373
EAN: 2147483647
Year: 2003
Pages: 181

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net