for RuBoard |
An XML schema can be used to generate a dataset that is "typed." Instead of using the index property of a collection to access an element of the dataset, you can use the name of a column. Here is a fragment from the TypedDataSet example:
AirlineBroker.AirlinesRow UAL = a.FindByName("United"); Console.WriteLine("{0}({1}) ReservationNumber:{2} WebSite:{3}", UAL.Name.Trim(), UAL.Abbreviation.Trim(), UAL.ReservationNumber.Trim(), UAL.WebSite.Trim());
You can assign a meaningful name to rows as well as use strong typing to make sure you are working with the data element you want to. If you try to set the field UAL.ReservationNumber to an integer, the compiler will detect the mistake.
A typed DataSet inherits from the DataSet class, so that everything that is available in a DataSet is available in a typed DataSet . If the schema of the database changes, however, the typed dataset class must be regenerated.
The XML Schema Definition Tool (Xsd.exe) is used to transform an XML schema (XSD) to a typed data set. The syntax for doing this is:
Xsd.exe /d /l:C# filename.xsd
The /d switch indicates that a DataSet should be generated. The /l switch indicates that a C# class should be generated.
The XSD Tool directory has a batch file that can be used to take the revised AirlineBroker XSD and generate a typed dataset AirlineBroker.cs.
The TypedDataSet example shows how to use a typed dataset to access the Airline Brokers database. You define your SqlConnection as usual and create an SqlDataAdapter instance for each table you want to use. You create whatever SqlCommands you need to work with the data. A typed DataSet is independent of a database, just like the untyped DataSet , so it needs SqlDataAdapter to handle the database operations.
SqlConnection conn = new SqlConnection(connectString); SqlDataAdapter airlinesAdapter = new SqlDataAdapter(); SqlDataAdapter flightsAdapter = new SqlDataAdapter(); SqlDataAdapter planetypeAdapter = new SqlDataAdapter(); SqlDataAdapter customersAdapter = new SqlDataAdapter(); SqlDataAdapter reservationsAdapter = new SqlDataAdapter(); AirlineBroker airlineBrokerDataset = new AirlineBroker();
Next the select commands are defined to fetch the data, just as for use with a regular DataSet . For illustrative purposes, constraint checking is enabled even though it is on by default.
airlinesAdapter.SelectCommand = new SqlCommand( "select * from Airlines", conn); airlinesAdapter.InsertCommand = new SqlCommand( "insert Airlines(Name, Abbreviation, WebSite, ReservationNumber) values(@Name, @Abbrev, @Web, @Reserve)", conn); airlinesAdapter.InsertCommand.CommandType = CommandType.Text; SqlParameter param = new SqlParameter("@Name", SqlDbType.NChar, 40); airlinesAdapter.InsertCommand.Parameters.Add(param); airlinesAdapter.InsertCommand.Parameters["@Name"]. SourceColumn = "Name"; ... airlineBrokerDataset.EnforceConstraints = true; ...
Now you can fetch the data. The order is which you do this is important. If Flights data are fetched before PlaneType data, a constraint violation exception will occur, because the PlaneType field in the Flights table does not exist.
airlinesAdapter.Fill(airlineBrokerDataset, "Airlines"); planetypeAdapter.Fill(airlineBrokerDataset, "PlaneType"); flightsAdapter.Fill(airlineBrokerDataset, "Flights"); customersAdapter.Fill(airlineBrokerDataset, "Customers"); reservationsAdapter.Fill(airlineBrokerDataset, "Reservations");
The strong typing makes it straightforward to display the data:
AirlineBroker.AirlinesDataTable a = airlineBrokerDataset.Airlines; Console.WriteLine(a.TableName); Console.WriteLine(" {0, -18} {1, -20} {2, -20} {3, -15}", "Name", "Abbreviation", "Web Site", "Reservation Numbers"); for (int i = 0; i < a.Count; i++) Console.WriteLine(" {0, -18} {1, -20} {2, -20} {3, -15}", a[i].Name.Trim(), a[i].Abbreviation.Trim(), a[i].WebSite.Trim(), a[i].ReservationNumber.Trim()); ...
It is easy to locate the data:
AirlineBroker.AirlinesRow UAL = a.FindByName("United"); Console.WriteLine("{0}({1}) ReservationNumber:{2} WebSite:{3}", UAL.Name.Trim(), UAL.Abbreviation.Trim(), UAL.ReservationNumber.Trim(), UAL.WebSite.Trim()); ...
You modify and update the database with a typed dataset just like a regular dataset. Make sure the correct table is specified in the Update method.
airlineBrokerDataset.Airlines.AddAirlinesRow("Southwest", "S", "1-800-111-222", "www.southwest.com"); NumberRows = airlinesAdapter.Update(airlineBrokerDataset, "Airlines"); if (NumberRows == 1) Console.WriteLine("Southwest added."); else Console.WriteLine("Southwest not added");
for RuBoard |