[ Team LiB ] Recipe 2.2 Building a DataSet Programmatically Problem You want to build a DataSet programmatically—including adding tables, columns, primary keys, and relations—from a schema that you have designed.
AMBIENT/
Chủ đề:
Nội dung Text: Building a DataSet Programmatically
- [ Team LiB ]
Recipe 2.2 Building a DataSet Programmatically
Problem
You want to build a DataSet programmatically—including adding tables, columns,
primary keys, and relations—from a schema that you have designed.
Solution
The following example shows how to build a complex DataSet programmatically,
including how to build and add tables, columns, primary key constraints, relations, and
column mappings. Use this as a template for building your own DataSet.
The sample code creates a DataSet. A DataTable object is created representing the Orders
table in Northwind. Columns are added, including the auto-increment primary key, to the
table. The table is added to the DataSet. The process is repeated for a DataTable
representing the Order Details table in Northwind. A DataRelation is created relating the
two tables. Finally, the tables are filled with data from Northwind.
The C# code is shown in Example 2-2.
Example 2-2. File: BuildDataSetProgramaticallyForm.cs
// Namespaces, variables, and constants
using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
// . . .
// Create the DataSet.
DataSet ds = new DataSet("MyDataSet");
// Build the Orders (parent) table.
DataTable parentTable = new DataTable("Orders");
DataColumnCollection cols = parentTable.Columns;
// Add the identity field.
DataColumn column = cols.Add("OrderID", typeof(System.Int32));
column.AutoIncrement = true;
- column.AutoIncrementSeed = -1;
column.AutoIncrementStep = -1;
// Add the other fields.
cols.Add("CustomerID", typeof(System.String)).MaxLength = 5;
cols.Add("EmployeeID", typeof(System.Int32));
cols.Add("OrderDate", typeof(System.DateTime));
cols.Add("RequiredDate", typeof(System.DateTime));
cols.Add("ShippedDate", typeof(System.DateTime));
cols.Add("ShipVia", typeof(System.Int32));
cols.Add("Freight", typeof(System.Decimal));
cols.Add("ShipName", typeof(System.String)).MaxLength = 40;
cols.Add("ShipAddress", typeof(System.String)).MaxLength = 60;
cols.Add("ShipCity", typeof(System.String)).MaxLength = 15;
cols.Add("ShipRegion", typeof(System.String)).MaxLength = 15;
cols.Add("ShipPostalCode", typeof(System.String)).MaxLength = 10;
cols.Add("ShipCountry", typeof(System.String)).MaxLength = 15;
// Set the primary key.
parentTable.PrimaryKey = new DataColumn[] {cols["OrderID"]};
// Add the Orders table to the DataSet.
ds.Tables.Add(parentTable);
// Build the Order Details (child) table.
DataTable childTable = new DataTable("Order Details");
cols = childTable.Columns;
// Add the PK fields.
cols.Add("OrderID", typeof(System.Int32)).AllowDBNull = false;
cols.Add("ProductID", typeof(System.Int32)).AllowDBNull = false;
// Add the other fields.
cols.Add("UnitPrice", typeof(System.Decimal)).AllowDBNull = false;
cols.Add("Quantity", typeof(System.Int16)).AllowDBNull = false;
cols.Add("Discount", typeof(System.Single)).AllowDBNull = false;
// Set the primary key.
childTable.PrimaryKey = new DataColumn[]
{
cols["OrderID"],
cols["ProductID"]
};
// Add the Order Details table to the DataSet.
ds.Tables.Add(childTable);
// Add the relationship between parent and child tables.
ds.Relations.Add("Order_OrderDetails_Relation",
- parentTable.Columns["OrderID"], childTable.Columns["OrderID"], true);
// Fill the tables from the data source.
SqlDataAdapter da;
String sqlText;
sqlText = "SELECT OrderID, CustomerID, EmployeeID, OrderDate, " +
"RequiredDate, ShippedDate, ShipVia, Freight, ShipName, " +
"ShipAddress, ShipCity, ShipRegion, ShipPostalCode, ShipCountry " +
"FROM Orders";
da = new SqlDataAdapter(sqlText,
ConfigurationSettings.AppSettings["Sql_ConnectString"]);
da.Fill(parentTable);
sqlText = "SELECT OrderID, ProductID, UnitPrice, Quantity, Discount " +
"FROM [Order Details]";
da = new SqlDataAdapter(sqlText,
ConfigurationSettings.AppSettings["Sql_ConnectString"]);
da.Fill(childTable);
Discussion
The steps to build a complex DataSet programmatically, as shown in the code for the
solution, are:
1. Design the DataSet identifying the tables, columns, indexes, constraints, and data
relations that need to be created.
2. Create a new DataSet, naming it in the constructor.
3. Create a new DataTable, naming it in the constructor.
4. Add a column to the ColumnCollection of the table using the Add( ) method
exposed by the Columns property of the DataTable specifying the name and data
type of the column. If the column is a character-type column, define its maximum
length. If the column is an auto-increment column, set the AutoIncrement property
to true and set both the AutoIncrementSeed and AutoIncrementStep properties of
the column to -1. (For more information about using auto-increment columns, see
Recipe 4.1). Repeat step 4 for each column in the table.
5. Define the primary key for the table by setting the PrimaryKey property of the
DataTable to the array of primary key DataColumn objects.
6. Add the new table to the DataSet using the Add( ) method of the
DataTableCollection exposed by the Tables property of the DataSet.
7. Repeat steps 3-6 for each table in the DataSet.
8. Create a data relationship between two related tables in the DataSet by using the
Add( ) method of the DataRelationCollection exposed by the Relations property of
- the DataSet. Specify the relationship name, the related columns, and whether
constraints are to be created when calling the Add( ) method. Repeat step 8 for
each data relationship in the DataSet.
The steps continue, demonstrating how to fill the new DataSet:
9. To fill the DataSet with data from the data source, create a DataAdapter defining
the SQL select statement and the connection string in the constructor.
10. Use the Fill( ) method of the DataSet to fill the table. Specify the table name to be
filled in the second argument of the Fill( ) method.
11. Repeat steps 9 and 10 for each table to be filled. See Recipe 2.1 for information
about how to fill related tables from the data source without raising constraint
violation errors.
[ Team LiB ]
Thêm vào BST
Báo xấu
Download
Vui lòng tải xuống để xem tài liệu đầy đủ
