DataRelations in Visual C#.NET

Printer Code 128 Code Set B in Visual C#.NET DataRelations

In addition to the Tables collection, the DataSet has a Relations property, which returns a DataRelationCollection consisting of DataRelation objects. Each DataRelation represents a relationship between two tables through DataColumn objects. For example, in the Northwind database, the Customers table is in a relationship with the Orders table through the CustomerID column. The nature of this relationship is one-to-many, or parent-to-child. For any given order, there will be exactly one customer, but any given customer might be represented in any number of orders.

The Rows collection of the DataTable returns a set of rows for that table. You use this collection to examine the results of queries against the database, iterating through the rows to examine each record in turn, typically with a foreach loop. You ll see this in the example in this chapter.

The DataSet is an abstraction of a relational database. ADO.NET uses a DataAdapter as a bridge between the DataSet and the data source, which is the underlying database. DataAdapter provides the Fill( ) method to retrieve data from the database and populate the DataSet. Instead of tying the DataSet object too closely to your database architecture, ADO.NET uses a DataAdapter object to mediate between the DataSet object and the database. This decouples the DataSet from the database and allows a single DataSet to represent more than one database or other data source.

The DbConnection object represents a connection to a data source. This connection can be shared among different command objects. The DbCommand object allows you to send a command (typically, a SQL statement or a stored procedure) to the database. Often, these objects are implicitly created when you create a DataAdapter, but you can explicitly access these objects; for example, you can declare a connection string as follows:

string connectionString = "server=.\\sqlexpress;" + "Trusted_Connection=yes; database=Northwind";

You can then use this connection string to create a connection object or to create a DataAdapter object.

An alternative to creating a DataSet (and a DataAdapter) is to create a DataReader. The DataReader provides connected, forward-only, read-only access to a collection of tables by executing either a SQL statement or stored procedures. DataReaders are lightweight objects that are ideally suited for filling controls with data and then breaking the connection to the backend database.

Enough theory! Let s write some code and see how this works. Working with ADO.NET can be complex, but for many queries, the model is surprisingly simple. In this example, you ll create a console application and list out bits of information from the Customers table in the Northwind database. Create a new console application (we ll go back to console applications to keep things simple here). When the application opens, add the following two using statements to the top:

using System.Data; using System.Data.SqlClient;

|

The first thing you re going to need in the program itself is a way to identify the location of the SQL Server instance to your program. This is commonly called the connection string. It s a simple enough string format, and once you ve defined it, you can use the same string anytime you want to access Northwind. If you re using SQL Server Express, as installed with C# Express in 1, the access path is simple: .\sqlexpress. However, because you re defining a string, you need to escape the slash character, as we discussed in 15 (or you could also use a literal string). Create the connection string like this (all on one line):

string connectionString = "server=.\\sqlexpress; Trusted_Connection=yes;database=Northwind";

The next thing you need is a string to hold the SQL command itself. SQL Server can t understand C# directly, so you can t treat the entries in the database as though they were C# objects. (It d be nice if you could, though, and that s why LINQ was created, as you ll see in the next chapter.) So, you need to create a string object to hold the SQL statement that will retrieve the data you want. This is called the command string. In this case, you want to retrieve (Select) the company name and the contact name columns from the Customers table. To do that, you ll use this simple SQL statement in the commandString variable (again, on one line):

string commandString = "Select CompanyName, ContactName from Customers";

Now that you have the connection string and the command string, you need to contact the database, and for that, you need a DataAdapter object, as we mentioned earlier. There are several kinds of DataAdapter objects, each for a different kind of database. In this case, you re using a SQL Server database, so you need a SqlDataAdapter object. The constructor takes two parameters, not surprisingly, the command string and the connection string. So, now create the DataAdapter (inside Main( )), like this:

SqlDataAdapter myDataAdapter = new SqlDataAdapter(commandString, connectionString);

You have the DataAdapter in hand now, but you need a DataSet object before you can do anything with the data. So, create a new DataSet object:

DataSet myDataSet = new DataSet( );

Then you call the Fill( ) method of the myDataAdapter, passing in your new DataSet. This fills the DataSet with the data that you obtain from the SQL select statement:

myDataAdapter.Fill(myDataSet);

That s it. You now have a DataSet, and you can query, manipulate, and otherwise manage the data. To display the data you retrieved, you ll need a DataTable object. The DataSet object has a collection of tables, but your select statement retrieved only a single table, so you need to access only the first one, like this:

DataTable myDataTable = myDataSet.Tables[0];