Csharp/CSharp Tutorial/ADO.Net/DataRelation — различия между версиями
Admin (обсуждение | вклад) м (1 версия) |
(нет различий)
|
Текущая версия на 15:19, 26 мая 2010
DataRelation Example
<source lang="csharp">using System; using System.Data; using System.Data.SqlClient; using System.Collections.Generic; using System.Text;
class Program { static void Main(string[] args) { SqlConnection thisConnection = new SqlConnection( @"Data Source=.\SQLEXPRESS;" + @"AttachDbFilename="NORTHWND.MDF";" + @"Integrated Security=True;Connect Timeout=30;User Instance=true"); SqlDataAdapter thisAdapter = new SqlDataAdapter("SELECT CustomerID, CompanyName FROM Customers", thisConnection); SqlCommandBuilder thisBuilder = new SqlCommandBuilder(thisAdapter); DataSet thisDataSet = new DataSet(); SqlDataAdapter custAdapter = new SqlDataAdapter("SELECT * FROM Customers", thisConnection); SqlDataAdapter orderAdapter = new SqlDataAdapter("SELECT * FROM Orders", thisConnection); custAdapter.Fill(thisDataSet, "Customers"); orderAdapter.Fill(thisDataSet, "Orders"); DataRelation custOrderRel = thisDataSet.Relations.Add("CustOrders", thisDataSet.Tables["Customers"].Columns["CustomerID"], thisDataSet.Tables["Orders"].Columns["CustomerID"]); foreach (DataRow custRow in thisDataSet.Tables["Customers"].Rows) { Console.WriteLine("Customer ID: " + custRow["CustomerID"] + " Name: " + custRow["CompanyName"]); foreach (DataRow orderRow in custRow.GetChildRows(custOrderRel)) { Console.WriteLine(" Order ID: " + orderRow["OrderID"]); } } custOrderRel.Nested = true; thisDataSet.WriteXml("nwinddata.xml"); thisConnection.Close(); } }</source>
Define DataRelation
<source lang="csharp">using System; using System.Data; using System.Data.SqlClient; public class XmlRelations {
private static string connectionString = "Data Source=localhost;Initial Catalog=Northwind;Integrated Security=SSPI"; private static string categorySQL = "SELECT CategoryID, CategoryName,Description FROM Categories"; private static string productSQL = "SELECT ProductID, ProductName, CategoryID FROM Products"; public static void Main() { SqlConnection con = new SqlConnection(connectionString); SqlCommand com = new SqlCommand(categorySQL, con); SqlDataAdapter adapter = new SqlDataAdapter(com); DataSet ds = new DataSet("Nortwind"); con.Open(); adapter.FillSchema(ds, SchemaType.Mapped, "Categories"); adapter.Fill(ds, "Categories"); adapter.SelectCommand.rumandText = productSQL; adapter.FillSchema(ds, SchemaType.Mapped, "Products"); adapter.Fill(ds, "Products"); con.Close(); DataColumn parentCol = ds.Tables["Categories"].Columns["CategoryID"]; DataColumn childCol = ds.Tables["Products"].Columns["CategoryID"]; DataRelation relation = new DataRelation("Cat_Prod", parentCol, childCol); ds.Relations.Add(relation); relation.Nested = true; ds.WriteXml("mydata.xml"); ds.WriteXml(Console.Out); }
}</source>
<source lang="csharp">using System; using System.Data; using System.Data.SqlClient; public class AddCustomer {
public static void Main() { string connectionString = "Data Source=localhost;Initial Catalog=pubs;Integrated Security=SSPI"; string SQL = "SELECT au_lname, au_fname, au_id FROM Authors"; SqlConnection con = new SqlConnection(connectionString); SqlCommand cmd = new SqlCommand(SQL, con); SqlDataAdapter adapter = new SqlDataAdapter(cmd); DataSet ds = new DataSet(); con.Open(); adapter.Fill(ds, "Authors"); cmd.rumandText = "SELECT au_id, title_id FROM TitleAuthor"; adapter.Fill(ds, "TitleAuthor"); cmd.rumandText = "SELECT title_id, title FROM Titles"; adapter.Fill(ds, "Titles"); con.Close(); DataRelation titles_titleAuthor = new DataRelation("", ds.Tables["Titles"].Columns["title_id"], ds.Tables["TitleAuthor"].Columns["title_id"]); DataRelation authors_titleAuthor = new DataRelation("", ds.Tables["Authors"].Columns["au_id"], ds.Tables["TitleAuthor"].Columns["au_id"]); ds.Relations.Add(titles_titleAuthor); ds.Relations.Add(authors_titleAuthor); foreach (DataRow rowAuthor in ds.Tables["Authors"].Rows) { Console.WriteLine(rowAuthor["au_fname"]); Console.WriteLine(rowAuthor["au_lname"]); foreach (DataRow rowTitleAuthor in rowAuthor.GetChildRows(authors_titleAuthor)) { foreach (DataRow rowTitle in rowTitleAuthor.GetParentRows(titles_titleAuthor)) { Console.WriteLine(rowTitle["title"]); } } } }
}</source>