Creating DataSet Relationships from SQL Server Relationships

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

Thêm vào BST

Báo xấu

129
lượt xem 8
download

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

[ Team LiB ] Recipe 10.11 Creating DataSet Relationships from SQL Server Relationships Problem You need to create relationships between DataTable objects within your DataSet at runtime based on the relationships that are defined in your SQL Server database.

Chủ đề:

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

Lưu

Nội dung Text: Creating DataSet Relationships from SQL Server Relationships

[ Team LiB ] Recipe 10.11 Creating DataSet Relationships from SQL Server Relationships Problem You need to create relationships between DataTable objects within your DataSet at runtime based on the relationships that are defined in your SQL Server database. Solution Use INFORMATION_SCHEMA views and system tables to create relationships automatically at runtime. The schema of table TBL1011a used in this solution is shown in Table 10-5. Table 10-5. TBL1011a schema Column name Data type Length Allow nulls? a int 4 No b int 4 No c int 4 No The schema of table TBL1011b used in this solution is shown in Table 10-6. Table 10-6. TBL1011b schema Column name Data type Length Allow nulls? d int 4 No e int 4 No a2 int 4 No b2 int 4 No The sample code creates a DataSet containing the Orders table and Order Details table from the Northwind sample database. The tables TBL1011a and TBL1011b—related through a multicolumn key—are also added to the DataSet. Next, the result set of a query of the INFORMATION_SCHEMA views are examined to determine the relationships specified in the data source between the tables in the DataSet. DataRelation objects are created in the DataSet for the identified relationships.
The C# code is shown in Example 10-11. Example 10-11. File: AutoDataRelationForm.cs // Namespaces, variables, and constants using System; using System.Configuration; using System.Collections; using System.Text; using System.Data; using System.Data.SqlClient; // . . . DataSet ds = new DataSet( ); SqlDataAdapter da; // Add the Orders and Order Details tables to the DataSet. da = new SqlDataAdapter("SELECT * FROM Orders", ConfigurationSettings.AppSettings["Sql_ConnectString"]); da.Fill(ds, ORDERS_TABLE); da = new SqlDataAdapter("SELECT * FROM [Order Details]", ConfigurationSettings.AppSettings["Sql_ConnectString"]); da.Fill(ds, ORDERDETAILS_TABLE); // Add the TBL1011a and TBL1101b tables to the DataSet. da = new SqlDataAdapter("SELECT * FROM TBL1011a", ConfigurationSettings.AppSettings["Sql_ConnectString"]); da.Fill(ds, PARENTMULTICOLKEYTABLE); da = new SqlDataAdapter("SELECT * FROM TBL1011b", ConfigurationSettings.AppSettings["Sql_ConnectString"]); da.Fill(ds, CHILDMULTICOLKEYTABLE); StringBuilder result = new StringBuilder( ); String sqlText = "SELECT rc.CONSTRAINT_NAME, rc.UPDATE_RULE, " + "rc.DELETE_RULE, " + "kcuP.TABLE_NAME ParentTable, kcuC.TABLE_NAME ChildTable, " + "kcuP.COLUMN_NAME ParentColumn, kcuC.COLUMN_NAME ChildColumn " + "FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc " + "LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcuP ON " + "rc.UNIQUE_CONSTRAINT_NAME = kcuP.CONSTRAINT_NAME " +
"LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcuC ON " + "rc.CONSTRAINT_NAME = kcuC.CONSTRAINT_NAME AND " + "kcuP.ORDINAL_POSITION = kcuC.ORDINAL_POSITION " + "ORDER BY rc.CONSTRAINT_NAME, kcuP.ORDINAL_POSITION"; // Create the connection and command to retrieve constraint information. SqlConnection conn = new SqlConnection( ConfigurationSettings.AppSettings["Sql_ConnectString"]); SqlCommand cmd = new SqlCommand(sqlText, conn); // Fill the DataReader with constraint information. conn.Open( ); SqlDataReader reader = cmd.ExecuteReader( ); String prevConstraintName = ""; String constraintName = ""; String parentTableName = ""; String childTableName = ""; bool updateCascade = false; bool deleteCascade = false; String relationName = ""; // Arrays to store related columns from constraints in DataReader ArrayList parentColsAL = new ArrayList( ); ArrayList childColsAL = new ArrayList( ); DataColumn[] parentCols; DataColumn[] childCols; DataRelation dr; bool isRecord = false; // Iterate over the constraint collection for the database. do { // Read the next record from the DataReader. isRecord = reader.Read( ); // Store the current constraint as the previous constraint name // to handle multicolumn-based relations. prevConstraintName = constraintName; // Get the current constraint name. constraintName = isRecord ? reader["CONSTRAINT_NAME"].ToString( ) : "";
// If the constraint name has changed and both tables exist, // create a relation based on the previous constraint column(s). if (prevConstraintName != "" && constraintName != prevConstraintName && ds.Tables.Contains(parentTableName) && ds.Tables.Contains(childTableName)) { // Create the parent and child column arrays. parentCols = new DataColumn[parentColsAL.Count]; parentColsAL.CopyTo(parentCols); childCols = new DataColumn[childColsAL.Count]; childColsAL.CopyTo(childCols); // Create the relation name based on the constraint name. relationName = prevConstraintName.Replace("FK_","RELATION_"); // Create the relation and add it to the DataSet. dr = new DataRelation(relationName, parentCols, childCols, true); ds.Relations.Add(dr); // Set the cascade update and delete rules. dr.ChildKeyConstraint.UpdateRule = updateCascade ? Rule.Cascade : Rule.None; dr.ChildKeyConstraint.DeleteRule = deleteCascade ? Rule.Cascade : Rule.None; // Clear the parent and child column arrays for the previous // constraint. parentColsAL.Clear( ); childColsAL.Clear( ); result.Append("Added relationship " + relationName + " to DataSet." + Environment.NewLine); } if (isRecord) { // Store the current parent and child table names. parentTableName = reader["ParentTable"].ToString( ); childTableName = reader["ChildTable"].ToString( ); // Store the cascade update and delete for the current // constraint.
updateCascade = (reader["UPDATE_RULE"].ToString( ) == "CASCADE"); deleteCascade = (reader["DELETE_RULE"].ToString( ) == "CASCADE"); // Add the parent and child column for the current constraint // to the ArrayLists, if both parent and child are in DataSet. if (ds.Tables.Contains(parentTableName) && ds.Tables.Contains(childTableName)) { parentColsAL.Add(ds.Tables[parentTableName].Columns[ reader["ParentColumn"].ToString( )]); childColsAL.Add(ds.Tables[childTableName].Columns[ reader["ChildColumn"].ToString( )]); } } } while(isRecord); // Close the DataReader and connection. reader.Close( ); conn.Close( ); resultTextBox.Text = result.ToString( ); Discussion There is no ADO.NET data provider method that automatically returns information about table relationships that are defined in a database. To get the relation information, information views in SQL Server must be queried. The information required to reconstruct relationships between tables requires a query that pulls together information from two different information views, INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS and INFORMATION_SCHEMA.KEY_COLUMN_USAGE, and requires two joins into the latter table to obtain required information for both unique and foreign key constraints. The REFERENTIAL_CONSTRAINTS table contains a row for each foreign key constraint in the database. The KEY_COLUMN_USAGE table contains one row for each row constrained as a key in the database. The solution starts by loading a DataSet with two sets of tables. These tables are the Orders and Order Details tables from Northwind and a pair of sample tables—TBL0011a and TBL0011b—which demonstrate retrieving relation information for tables related on
more than one column. Next, the query to retrieve the data relationship information is constructed. The SQL statement used is: SELECT rc.CONSTRAINT_NAME, rc.UPDATE_RULE, rc.DELETE_RULE, kcuP.TABLE_NAME ParentTable, kcuC.TABLE_NAME ChildTable, kcuP.COLUMN_NAME ParentColumn, kcuC.COLUMN_NAME ChildColumn FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcuP ON rc.UNIQUE_CONSTRAINT_NAME = kcuP.CONSTRAINT_NAME LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcuC ON rc.CONSTRAINT_NAME = kcuC.CONSTRAINT_NAME AND kcuP.ORDINAL_POSITION = kcuC.ORDINAL_POSITION ORDER BY rc.CONSTRAINT_NAME, kcuP.ORDINAL_POSITION This statement retrieves the constraint information needed to create the relations in the DataSet based on the schema information in the database. Specifically, the columns returned are shown in Table 10-7. Table 10-7. Relation query columns Column Description CONSTRAINT_NAME Name of the constraint UPDATE_RULE NO ACTION or CASCADE DELETE_RULE NO ACTION or CASCADE ParentTable Name of the parent table in the relationship ChildTable Name of the child table in the relationship ParentColumn Name of the column in the parent table ChildColumn Name of the column in the child table For relationships that are based on more than one column, there will be more than one row in the result set that must be combined to create the DataRelation object in the DataSet. Notice that the statement groups the results that are returned by the constraint name, grouping all records related to a single relation. The result set is ordered by the ORDINAL_POSITION field that defines the order of the columns in the relation. When iterating over the query results, if both the parent and child names are contained in the result set, a relationship has been identified and processing continues. For those relationships, the column names for the parent and child tables are loaded into arrays
allowing relations based on multiple columns to be created. Once all of the columns for a relation have been loaded (this is determined by a change in the constraint name and the names of the parent and child tables), the DataRelation is created in the DataSet based on the parent and child column names in the arrays. The update and delete cascade rules are set for the relation. Although not necessary, the sample names the relation based on the name of the constraint without the FK_ prefix. Once the DataRelation is created, processing of the result set resumes to determine the remaining relations. [ Team LiB ]

CÓ THỂ BẠN MUỐN DOWNLOAD

THÔNG TIN

TRỢ GIÚP

HỖ TRỢ KHÁCH HÀNG

Theo dõi chúng tôi

Chịu trách nhiệm nội dung:

Nguyễn Công Hà - Giám đốc Công ty TNHH TÀI LIỆU TRỰC TUYẾN VI NA

LIÊN HỆ

Địa chỉ: P402, 54A Nơ Trang Long, Phường 14, Q.Bình Thạnh, TP.HCM

Hotline: 093 303 0098

Email: support@tailieu.vn