/// <summary> /// Adds the relation to the collection. /// </summary> public void Add(DataRelation relation) { long logScopeId = DataCommonEventSource.Log.EnterScope("<ds.DataRelationCollection.Add|API> {0}, relation={1}", ObjectID, (relation != null) ? relation.ObjectID : 0); try { if (_inTransition == relation) { return; } _inTransition = relation; try { OnCollectionChanging(new CollectionChangeEventArgs(CollectionChangeAction.Add, relation)); AddCore(relation); OnCollectionChanged(new CollectionChangeEventArgs(CollectionChangeAction.Add, relation)); } finally { _inTransition = null; } } finally { DataCommonEventSource.Log.ExitScope(logScopeId); } }
public void Add(DataRelation relation) { IntPtr ptr; Bid.ScopeEnter(out ptr, "<ds.DataRelationCollection.Add|API> %d#, relation=%d\n", this.ObjectID, (relation != null) ? relation.ObjectID : 0); try { if (this.inTransition != relation) { this.inTransition = relation; try { this.OnCollectionChanging(new CollectionChangeEventArgs(CollectionChangeAction.Add, relation)); this.AddCore(relation); this.OnCollectionChanged(new CollectionChangeEventArgs(CollectionChangeAction.Add, relation)); } finally { this.inTransition = null; } } } finally { Bid.ScopeLeave(ref ptr); } }
protected void Page_Load(object sender, System.EventArgs e) { // Create the Connection, DataAdapter, and DataSet. string connectionString = "Data Source=localhost;Initial Catalog=Northwind;" + "Integrated Security=SSPI"; SqlConnection con = new SqlConnection(connectionString); string sqlCat = "SELECT CategoryID, CategoryName FROM Categories"; string sqlProd = "SELECT ProductName, CategoryID FROM Products"; SqlDataAdapter da = new SqlDataAdapter(sqlCat, con); DataSet ds = new DataSet(); try { con.Open(); // Fill the DataSet with the Categories table. da.Fill(ds, "Categories"); // Change the command text and retrieve the Products table. // You could also use another DataAdapter object for this task. da.SelectCommand.CommandText = sqlProd; da.Fill(ds, "Products"); } finally { con.Close(); } // Define the relationship between Categories and Products. DataRelation relat = new DataRelation("CatProds", ds.Tables["Categories"].Columns["CategoryID"], ds.Tables["Products"].Columns["CategoryID"]); // Add the relationship to the DataSet. ds.Relations.Add(relat); // Loop through the category records and build the HTML string. StringBuilder htmlStr = new StringBuilder(""); foreach (DataRow row in ds.Tables["Categories"].Rows) { htmlStr.Append("<b>"); htmlStr.Append(row["CategoryName"].ToString()); htmlStr.Append("</b><ul>"); // Get the children (products) for this parent (category). DataRow[] childRows = row.GetChildRows(relat); // Loop through all the products in this category. foreach (DataRow childRow in childRows) { htmlStr.Append("<li>"); htmlStr.Append(childRow["ProductName"].ToString()); htmlStr.Append("</li>"); } htmlStr.Append("</ul>"); } // Show the generated HTML code. HtmlContent.Text = htmlStr.ToString(); }
private string ChildPropertyName(DataRelation relation, out bool usesAnnotations) { usesAnnotations = true; string str = (string) relation.ExtendedProperties["typedChildren"]; if (!StringUtil.Empty(str)) { return str; } string str2 = (string) relation.ChildTable.ExtendedProperties["typedPlural"]; if (StringUtil.Empty(str2)) { str2 = (string) relation.ChildTable.ExtendedProperties["typedName"]; if (StringUtil.Empty(str2)) { usesAnnotations = false; str = "Get" + relation.ChildTable.TableName + "Rows"; if (1 < TablesConnectedness(relation.ParentTable, relation.ChildTable)) { str = str + "By" + relation.RelationName; } return NameHandler.FixIdName(str); } str2 = str2 + "Rows"; } return ("Get" + str2); }
//Activate This Construntor to log All To Standard output //public TestClass():base(true){} //Activate this constructor to log Failures to a log file //public TestClass(System.IO.TextWriter tw):base(tw, false){} //Activate this constructor to log All to a log file //public TestClass(System.IO.TextWriter tw):base(tw, true){} //BY DEFAULT LOGGING IS DONE TO THE STANDARD OUTPUT ONLY FOR FAILURES public void run() { Exception exp = null; DataSet ds = new DataSet(); DataTable dtChild = GHTUtils.DataProvider.CreateChildDataTable(); DataTable dtParent = GHTUtils.DataProvider.CreateParentDataTable(); ds.Tables.Add(dtParent); ds.Tables.Add(dtChild); DataRelation dRel; dRel = new DataRelation(null,dtParent.Columns[0],dtChild.Columns[0]); ds.Relations.Add(dRel); try { BeginCase("Nested default "); Compare(dRel.Nested , false); } catch(Exception ex) {exp = ex;} finally {EndCase(exp); exp = null;} dRel.Nested = true; try { BeginCase("Nested get/set"); Compare(dRel.Nested ,true); } catch(Exception ex) {exp = ex;} finally {EndCase(exp); exp = null;} }
private void Form1_Load(object sender, EventArgs e) { DbADOTest = new DataSet("DbADOTest"); tblPeople = MakePeopleTable(); tblSale = MakeSaleTable(); DbADOTest.Tables.Add(tblPeople); DbADOTest.Tables.Add(tblSale); //* 관계 객체 생성 RelBuy = new DataRelation("Buy", DbADOTest.Tables["tblPeople"].Columns["Name"], DbADOTest.Tables["tblSale"].Columns["Customer"]); DbADOTest.Relations.Add(RelBuy); //*/ /* FK 직접 생성 ForeignKeyConstraint fk = new ForeignKeyConstraint("Buy", DbADOTest.Tables["tblPeople"].Columns["Name"], DbADOTest.Tables["tblSale"].Columns["Customer"]); tblSale.Constraints.Add(fk); //*/ /* 삭제 규칙 변경 ForeignKeyConstraint fk= (ForeignKeyConstraint)tblSale.Constraints["Buy"]; fk.DeleteRule = Rule.None; //*/ dataGridView1.DataSource = DbADOTest.Tables["tblPeople"]; dataGridView2.DataSource = DbADOTest.Tables["tblSale"]; }
private void Form1_Load(object sender, EventArgs e) { SqlConnection con = new SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database1.mdf;Integrated Security=True;User Instance=True"); con.Open(); SqlCommand comm = new SqlCommand("select * from UserData",con); DataTable master = new DataTable(); DataTable child = new DataTable(); // Fill Table 2 with Data SqlDataAdapter da = new SqlDataAdapter(comm); da.Fill(master); // Fill Table1 with data comm = new SqlCommand("select * from UserDetail",con); da.Fill(child); con.Close(); DataSet ds = new DataSet(); //Add two DataTables in Dataset ds.Tables.Add(master); ds.Tables.Add(child); // Create a Relation in Memory DataRelation relation = new DataRelation("",ds.Tables[0].Columns[0],ds.Tables[1].Columns[0],true); ds.Relations.Add(relation); dataGrid1.DataSource = ds.Tables[0]; }
protected void Button1_Click(object sender, EventArgs e) { SqlConnection con = new SqlConnection(WebConfigurationManager.ConnectionStrings["Nash"].ConnectionString); SqlCommand cmd = new SqlCommand("select * from Categories", con); SqlCommand cmd1 = new SqlCommand("select * from Products", con); SqlDataAdapter adapter = new SqlDataAdapter(); adapter.SelectCommand = cmd; DataSet dataSet = new DataSet(); con.Open(); adapter.Fill(dataSet, "Categories"); adapter.SelectCommand = cmd1; adapter.Fill(dataSet, "Products"); con.Close(); DataRelation relation = new DataRelation("primary", dataSet.Tables["Categories"].Columns["CategoryID"], dataSet.Tables["Products"].Columns["CategoryID"]); dataSet.Relations.Add(relation); DataColumn column1 = new DataColumn("no. of products", typeof(int), "Count(Child(primary).CategoryID)"); dataSet.Tables["Categories"].Columns.Add(column1); GridView1.DataSource = dataSet; GridView1.DataBind(); //DataView view = new DataView(dataSet.Tables["Categories"]); //view.RowFilter = "Count(Child(primary).CategoryID) > 1"; //GridView1.DataSource = view; //GridView1.DataBind(); //GridView1.DataSource = dataSet; //GridView1.DataMember = "Categories"; //GridView1.DataBind(); }
//Activate This Construntor to log All To Standard output //public TestClass():base(true){} //Activate this constructor to log Failures to a log file //public TestClass(System.IO.TextWriter tw):base(tw, false){} //Activate this constructor to log All to a log file //public TestClass(System.IO.TextWriter tw):base(tw, true){} //BY DEFAULT LOGGING IS DONE TO THE STANDARD OUTPUT ONLY FOR FAILURES public void run() { Exception exp = null; DataSet ds = new DataSet(); DataTable dtChild = GHTUtils.DataProvider.CreateChildDataTable(); DataTable dtParent = GHTUtils.DataProvider.CreateParentDataTable(); ds.Tables.Add(dtParent); ds.Tables.Add(dtChild); DataRelation dRel; dRel = new DataRelation("MyRelation",dtParent.Columns[0],dtChild.Columns[0]); ds.Relations.Add(dRel); PropertyCollection pc; pc = dRel.ExtendedProperties ; try { base.BeginCase("Checking ExtendedProperties default "); base.Compare(pc != null,true); } catch(Exception ex) {exp = ex;} finally {EndCase(exp); exp = null;} try { base.BeginCase("Checking ExtendedProperties count "); base.Compare(pc.Count ,0); } catch(Exception ex) {exp = ex;} finally {EndCase(exp); exp = null;} }
private void Form1_Load(object sender, EventArgs e) { connectionString = "Data Source=(local);Initial Catalog=Practice-TrackingSystem;Integrated Security=SSPI;"; dbConn = new SqlConnection(connectionString); projectsDA = new SqlDataAdapter("SELECT * FROM Project", dbConn); tasksDA = new SqlDataAdapter("SELECT * FROM Task", dbConn); tasksCB = new SqlCommandBuilder(tasksDA); dataSet = new DataSet(); projectsDA.Fill(dataSet, "Project"); tasksDA.Fill(dataSet, "Task"); dataRelation = new DataRelation("Project_Tasks", dataSet.Tables["Project"].Columns["ProjID"], dataSet.Tables["Task"].Columns["ProjID"]); dataSet.Relations.Add(dataRelation); projectsBS = new BindingSource(); tasksBS = new BindingSource(); projectsBS.DataSource = dataSet; projectsBS.DataMember = "Project"; tasksBS.DataSource = projectsBS; tasksBS.DataMember = "Project_Tasks"; projectsComboBox.DataSource = projectsBS; projectsComboBox.DisplayMember = "ProjName"; tasksDataGridView.DataSource = tasksBS; }
private void Init(InfoBindingSource aBindingSource, DataRelation Relation) { DataRelation R1; lbDetail.Items.Clear(); if (aBindingSource.DataSource.GetType().Equals(typeof(InfoDataSet))) { InfoDataSet set1 = (InfoDataSet)aBindingSource.DataSource; for (int I = 0; I < set1.RealDataSet.Tables[0].ChildRelations.Count; I++) { R1 = set1.RealDataSet.Tables[0].ChildRelations[I]; lbDetail.Items.Add(R1.ChildTable.TableName); FDetailList.AddObject(R1.ChildTable.TableName, R1); } } if (aBindingSource.DataSource.GetType().Equals(typeof(InfoBindingSource))) { while (!aBindingSource.DataSource.GetType().Equals(typeof(InfoDataSet))) { aBindingSource = (InfoBindingSource)aBindingSource.DataSource; } InfoDataSet set2 = (InfoDataSet)aBindingSource.DataSource; for (int num2 = 0; num2 < set2.RealDataSet.Tables.Count; num2++) { if (set2.RealDataSet.Tables[num2].TableName.Equals(Relation.ChildTable.TableName)) { for (int num3 = 0; num3 < set2.RealDataSet.Tables[num2].ChildRelations.Count; num3++) { R1 = set2.RealDataSet.Tables[num2].ChildRelations[num3]; lbDetail.Items.Add(R1.ChildTable.TableName); FDetailList.AddObject(R1.ChildTable.TableName, R1); } } } } }
/// <summary> /// Add a relation between two table /// </summary> /// <param name="relactionName"></param> /// <param name="masterTable"></param> /// <param name="masterColumn"></param> /// <param name="slaveTable"></param> /// <param name="slaveColumn"></param> /// <param name="createConstraint">todo: describe createConstraint parameter on AddRelation</param> /// <returns></returns> public DataRelation AddRelation( string relactionName, string masterTable, string masterColumn, string slaveTable, string slaveColumn, bool createConstraint ) { if (Dataset == null) { return(null); } System.Data.DataRelation drDB; var dc1 = new DataColumn(); var dc2 = new DataColumn(); // Get the parent and child columns of the two tables. dc1 = Dataset.Tables[masterTable].Columns[masterColumn]; dc2 = Dataset.Tables[slaveTable].Columns[slaveColumn]; drDB = new System.Data.DataRelation(relactionName, dc1, dc2, createConstraint); try { Dataset.Relations.Add(drDB); } catch (Exception e) { MessageBox.Show(e.Message); } Dataset.EnforceConstraints = createConstraint; AddSlaveBinding(relactionName); return(drDB); }
public void Add() { DataRelationCollection drcol = _dataset.Relations; DataColumn parentCol = _dataset.Tables["Customer"].Columns["custid"]; DataColumn childCol = _dataset.Tables["Order"].Columns["custid"]; DataRelation dr = new DataRelation("CustOrder",parentCol,childCol); drcol.Add(dr); AssertEquals("test#1","CustOrder",drcol[0].RelationName); drcol.Clear(); drcol.Add(parentCol,childCol); AssertEquals("test#2",1,drcol.Count); drcol.Clear(); drcol.Add("NewRelation",parentCol,childCol); AssertEquals("test#3","NewRelation",drcol[0].RelationName); drcol.Clear(); drcol.Add("NewRelation",parentCol,childCol,false); AssertEquals("test#4",1,drcol.Count); drcol.Clear(); drcol.Add("NewRelation",parentCol,childCol,true); AssertEquals("test#5",1,drcol.Count); drcol.Clear(); }
//Activate This Construntor to log All To Standard output //public TestClass():base(true){} //Activate this constructor to log Failures to a log file //public TestClass(System.IO.TextWriter tw):base(tw, false){} //Activate this constructor to log All to a log file //public TestClass(System.IO.TextWriter tw):base(tw, true){} //BY DEFAULT LOGGING IS DONE TO THE STANDARD OUTPUT ONLY FOR FAILURES public void run() { Exception exp = null; DataSet ds = new DataSet(); DataTable dtChild = GHTUtils.DataProvider.CreateChildDataTable(); DataTable dtParent = GHTUtils.DataProvider.CreateParentDataTable(); ds.Tables.Add(dtParent); ds.Tables.Add(dtChild); DataRelation dRel; dRel = new DataRelation("MyRelation",dtParent.Columns[0],dtChild.Columns[0]); ds.Relations.Add(dRel); try { BeginCase("ParentColumns 1"); Compare(dRel.ParentColumns.Length , 1 ); } catch(Exception ex) {exp = ex;} finally {EndCase(exp); exp = null;} try { BeginCase("ParentColumns 2"); Compare(dRel.ParentColumns[0] , dtParent.Columns[0] ); } catch(Exception ex) {exp = ex;} finally {EndCase(exp); exp = null;} }
//Activate This Construntor to log All To Standard output //public TestClass():base(true){} //Activate this constructor to log Failures to a log file //public TestClass(System.IO.TextWriter tw):base(tw, false){} //Activate this constructor to log All to a log file //public TestClass(System.IO.TextWriter tw):base(tw, true){} //BY DEFAULT LOGGING IS DONE TO THE STANDARD OUTPUT ONLY FOR FAILURES public void run() { Exception exp = null; DataTable dt1 = GHTUtils.DataProvider.CreateParentDataTable(); DataTable dt2 = GHTUtils.DataProvider.CreateChildDataTable(); dt1.PrimaryKey = new DataColumn[] {dt1.Columns[0]}; dt2.PrimaryKey = new DataColumn[] {dt2.Columns[0],dt2.Columns[1]}; DataRelation rel = new DataRelation("Rel",dt1.Columns["ParentId"],dt2.Columns["ParentId"]); DataSet ds = new DataSet(); ds.Tables.AddRange(new DataTable[] {dt1,dt2}); ds.Relations.Add(rel); ds.Reset(); try { BeginCase("Reset - Relations"); Compare(ds.Relations.Count ,0 ); } catch(Exception ex) {exp = ex;} finally {EndCase(exp); exp = null;} try { BeginCase("Reset - Tables"); Compare(ds.Tables.Count ,0 ); } catch(Exception ex) {exp = ex;} finally {EndCase(exp); exp = null;} }
protected void Page_Load(object sender, EventArgs e) { DataSet ds = new DataSet(); string connStr = @"Data Source=Talha-PC\SQLExpress;Initial Catalog=LocalTestDB;User Id=talha;Password=talha123;Trusted_Connection=True;"; using (SqlConnection conn = new SqlConnection(connStr)) { string sql = "Select MenuID, Text,Description, ParentID from Menu"; SqlDataAdapter da = new SqlDataAdapter(sql, conn); da.Fill(ds); da.Dispose(); } ds.DataSetName = "Menus"; ds.Tables[0].TableName = "Menu"; DataRelation relation = new DataRelation("ParentChild", ds.Tables["Menu"].Columns["MenuID"], ds.Tables["Menu"].Columns["ParentID"], true); relation.Nested = true; ds.Relations.Add(relation); XmlDataSource1.Data = ds.GetXml(); if (Request.Params["Sel"] != null) Page.Controls.Add(new System.Web.UI.LiteralControl("You selected " + Request.Params["Sel"])); }
public void ver_comprasCliente(DataGridView d1, DataGridView d2) { String m = ""; String fat = "select re.id_fact,cl.Id_cliente,re.costo_compra,re.cantidad,es.precio_unitario,lo.Tamaño_cm,p.Color from relacion re inner join esferas es on es.Id_esferas=re.id_esfera inner join lote lo on lo.Id_lote=es.Id_lote inner join pintura p on p.Id_pintura=lo.Id_pintura inner join factura fa on fa.Id_Fatura=re.id_fact inner join cliente cl on cl.Id_cliente=fa.Id_cliente"; String ven = "select * from cliente"; ms.consultasetporreferencia(ref setglobal, ms.conexion(ref m), fat, ref m, "detalle"); MessageBox.Show(m); ms.consultasetporreferencia(ref setglobal, ms.conexion(ref m), ven, ref m, "venta"); MessageBox.Show(m); //d1.DataSource = setglobal.Tables["detalle"]; System.Data.DataColumn mesclavo = setglobal.Tables["detalle"].Columns["Id_cliente"]; System.Data.DataColumn maestro = setglobal.Tables["venta"].Columns["Id_cliente"]; DataRelation muchos = new System.Data.DataRelation("factura_venta", maestro, mesclavo); setglobal.Relations.Add(muchos); BindingSource relacionfactura = new BindingSource(); relacionfactura.DataSource = setglobal; relacionfactura.DataMember = "venta"; BindingSource relacionventa = new BindingSource(); relacionventa.DataSource = relacionfactura; relacionventa.DataMember = "factura_venta"; d1.DataSource = relacionfactura; d2.DataSource = relacionventa; }
private void relacionCursoAsignatura() { bindingSourceCurso = new BindingSource(); bindingSourceAsignatura = new BindingSource(); bindingSourceRelacionCursoAsignatura = new BindingSource(); bindingSourceRelacionAsignaturaNota = new BindingSource(); bindingSourceCurso.DataSource = dataset1.Tables["Cursos"]; bindingSourceAsignatura.DataSource = dataset1.Tables["Asignaturas"]; ColCurso = dataset1.Tables["Cursos"].Columns["COD_CUR"]; ColAsignaturaCUR = dataset1.Tables["Asignaturas"].Columns["COD_CUR"]; ColAsignaturaASI = dataset1.Tables["Asignaturas"].Columns["COD_ASI"]; ColNota = dataset1.Tables["Notas"].Columns["COD_ASI"]; RelacionCursoAsignatura = new DataRelation("RelCursoAsignatura", ColCurso, ColAsignaturaCUR); RelacionAsignaturaNota = new DataRelation("RelAsignaturaNota", ColAsignaturaASI, ColNota); dataset1.Relations.Clear(); dataset1.Relations.Add(RelacionCursoAsignatura); dataset1.Relations.Add(RelacionAsignaturaNota); bindingSourceRelacionCursoAsignatura.DataSource = bindingSourceCurso; bindingSourceRelacionCursoAsignatura.DataMember = "RelCursoAsignatura"; bindingSourceRelacionAsignaturaNota.DataSource = bindingSourceRelacionCursoAsignatura; bindingSourceRelacionAsignaturaNota.DataMember = "RelAsignaturaNota"; }
protected void ListBox1_SelectedIndexChanged(object sender, EventArgs e) { ListBox2.Items.Clear(); string sqlnaredba = "SELECT id_lekara, ime_lekara, prezime_lekara FROM Lekar where id_lekara='" + ListBox1.SelectedItem.Value + "'"; SqlCommand komanda = new SqlCommand(sqlnaredba, konekcija); SqlDataAdapter adapter = new SqlDataAdapter(komanda); System.Data.DataSet dsLekar = new System.Data.DataSet(); konekcija.Open(); adapter.Fill(dsLekar, "Lekar"); komanda.CommandText = "SELECT id_lekara, br_zdr_knjizice, ime_pacijenta, prezime_pacijenta FROM Pacijent"; adapter.Fill(dsLekar, "Pacijent"); System.Data.DataRelation Lekar_Pacijent = new System.Data.DataRelation("Lekar_Pacijent", dsLekar.Tables["Lekar"].Columns["id_lekara"], dsLekar.Tables["Pacijent"].Columns["id_lekara"], false); dsLekar.Relations.Add(Lekar_Pacijent); foreach (DataRow rs in dsLekar.Tables["Lekar"].Rows) { foreach (DataRow rsu in rs.GetChildRows("Lekar_Pacijent")) { ListItem Pacijenti = new ListItem(); Pacijenti.Text = rsu["ime_pacijenta"].ToString() + " " + rsu["prezime_pacijenta"].ToString(); Pacijenti.Value = rsu["br_zdr_knjizice"].ToString(); ListBox2.Items.Add(Pacijenti);//popunice se pacijentima samo selektovanog lekara kao sto je i receno u naredbi } } konekcija.Close(); }
private void relacionCursoAlumnos() { //bindingSourceAsignatura = new BindingSource(); bindingSourceAlumno = new BindingSource(); bindingSourceRelacionCursoAlumno = new BindingSource(); bindingSourceRelacionAlumnoNota = new BindingSource(); bindingSourceAlumno.DataSource = dataset1.Tables["Alumnos"]; ColAlumnoCUR = dataset1.Tables["Alumnos"].Columns["COD_CUR"]; ColAlumnoALU = dataset1.Tables["Alumnos"].Columns["COD_ALU"]; ColNota = dataset1.Tables["Notas"].Columns["COD_ALU"]; RelacionCursoAlumno = new DataRelation("RelCursoAlumno", ColCurso, ColAlumnoCUR); RelacionAlumnoNota = new DataRelation("RelAlumnoNota", ColAlumnoALU, ColNota); dataset1.Relations.Add(RelacionCursoAlumno); dataset1.Relations.Add(RelacionAlumnoNota); bindingSourceRelacionCursoAlumno.DataSource = bindingSourceCurso; bindingSourceRelacionCursoAlumno.DataMember = "RelCursoAlumno"; bindingSourceRelacionAlumnoNota.DataSource = bindingSourceRelacionCursoAlumno; bindingSourceRelacionAlumnoNota.DataMember = "RelAlumnoNota"; }
public void Add () { DataRelationCollection drcol = _dataset.Relations; DataColumn parentCol = _dataset.Tables ["Customer"].Columns ["custid"]; DataColumn childCol = _dataset.Tables ["Order"].Columns ["custid"]; DataRelation dr = new DataRelation ("CustOrder", parentCol, childCol); drcol.Add (dr); Assert.That (drcol [0].RelationName, Is.EqualTo ("CustOrder"), "test#1"); drcol.Clear (); drcol.Add (parentCol, childCol); Assert.That (drcol.Count, Is.EqualTo (1), "test#2"); drcol.Clear (); drcol.Add ("NewRelation", parentCol, childCol); Assert.That (drcol [0].RelationName, Is.EqualTo ("NewRelation"), "test#3"); drcol.Clear (); drcol.Add ("NewRelation", parentCol, childCol, false); Assert.That (drcol.Count, Is.EqualTo (1), "test#4"); drcol.Clear (); drcol.Add ("NewRelation", parentCol, childCol, true); Assert.That (drcol.Count, Is.EqualTo (1), "test#5"); drcol.Clear (); }
private void Form1_Load(object sender, EventArgs e) { connectionString = "Data Source=(local);Initial Catalog=Exam-UserRuns;Integrated Security=SSPI;"; dbConn = new SqlConnection(connectionString); usersDA = new SqlDataAdapter("select * from Users", dbConn); runsDA = new SqlDataAdapter("select * from Runs", dbConn); runsCB = new SqlCommandBuilder(runsDA); dataSet = new DataSet(); usersDA.Fill(dataSet, "Users"); runsDA.Fill(dataSet, "Runs"); dataRelation = new DataRelation("User_Runs", dataSet.Tables["Users"].Columns["userID"], dataSet.Tables["Runs"].Columns["userID"]); dataSet.Relations.Add(dataRelation); usersBS = new BindingSource(); runsBS = new BindingSource(); usersBS.DataSource = dataSet; usersBS.DataMember = "Users"; runsBS.DataSource = usersBS; runsBS.DataMember = "User_Runs"; usersComboBox.DataSource = usersBS; usersComboBox.DisplayMember = "userName"; runsDataGridView.DataSource = runsBS; }
private void _DataMemberChanged(System.Object sender, System.EventArgs e) { if (string.IsNullOrEmpty(this.DataMember) | _dataSet == null) { _dataTable = null; } else { //check to see if the Data Member is the name of a table in the dataset if (_dataSet.Tables(this.DataMember) == null) { //it must be a relationship instead of a table System.Data.DataRelation rel = _dataSet.Relations(this.DataMember); if ((rel != null)) { _dataTable = rel.ChildTable; } else { throw new ApplicationException("Invalid Data Member"); } } else { _dataTable = _dataSet.Tables(this.DataMember); } } }
private void Load_catesearch() { var list = per.Load_danhmuc_search(1); if (list.Count > 0) { DataRelation relCat; DataTable tbl = DataUtil.LINQToDataTable(list); DataSet ds = new DataSet(); ds.Tables.Add(tbl); tbl.PrimaryKey = new DataColumn[] { tbl.Columns["CAT_ID"] }; relCat = new DataRelation("Category_parent", ds.Tables[0].Columns["CAT_ID"], ds.Tables[0].Columns["CAT_PARENT_ID"], false); ds.Relations.Add(relCat); DataSet dsCat = ds.Clone(); DataTable CatTable = ds.Tables[0]; DataUtil.TransformTableWithSpace(ref CatTable, dsCat.Tables[0], relCat, null); Drcate_search.DataSource = dsCat.Tables[0]; Drcate_search.DataTextField = "CAT_NAME"; Drcate_search.DataValueField = "CAT_ID"; Drcate_search.DataBind(); } ListItem l = new ListItem("Tất cả", "0"); l.Selected = true; Drcate_search.Items.Insert(0, l); }
internal static void WriteXmlSchema (XmlWriter writer, DataTable[] tables, DataRelation[] relations, string mainDataTable, string dataSetName, CultureInfo locale) { new XmlSchemaWriter (writer, tables, relations, mainDataTable, dataSetName, locale).WriteSchema (); }
public static DataTable getDifferentRecords(DataTable FirstDataTable, DataTable SecondDataTable) { FirstDataTable = FirstDataTable.Copy(); FirstDataTable.TableName += " First"; SecondDataTable = SecondDataTable.Copy(); SecondDataTable.TableName += " Second"; //Create Empty Table DataTable ResultDataTable = new DataTable("ResultDataTable"); //use a Dataset to make use of a DataRelation object using (DataSet ds = new DataSet()) { //Add tables ds.Tables.AddRange(new DataTable[] { FirstDataTable, SecondDataTable }); //Get Columns for DataRelation DataColumn[] firstColumns = FirstDataTable.Columns.Cast<DataColumn>().ToArray(); DataColumn[] secondColumns = SecondDataTable.Columns.Cast<DataColumn>().ToArray(); //Create DataRelation DataRelation r1 = new DataRelation(string.Empty, firstColumns, secondColumns, false); ds.Relations.Add(r1); //DataRelation r2 = new DataRelation(string.Empty, secondColumns, firstColumns, false); //ds.Relations.Add(r2); //Create columns for return table List<DataColumn> PK = new List<DataColumn>(); for (int i = 0; i < FirstDataTable.Columns.Count; i++) { DataColumn newdc = ResultDataTable.Columns.Add(FirstDataTable.Columns[i].ColumnName, FirstDataTable.Columns[i].DataType); if (FirstDataTable.PrimaryKey.Contains(FirstDataTable.Columns[i])) PK.Add(newdc); } ResultDataTable.PrimaryKey = PK.ToArray(); //If FirstDataTable Row not in SecondDataTable, Add to ResultDataTable. ResultDataTable.BeginLoadData(); foreach (DataRow parentrow in FirstDataTable.Rows) { DataRow[] childrows = parentrow.GetChildRows(r1); if (childrows == null || childrows.Length == 0) ResultDataTable.LoadDataRow(parentrow.ItemArray, true); } ////If SecondDataTable Row not in FirstDataTable, Add to ResultDataTable. //foreach (DataRow parentrow in SecondDataTable.Rows) //{ // DataRow[] childrows = parentrow.GetChildRows(r2); // if (childrows == null || childrows.Length == 0) // ResultDataTable.LoadDataRow(parentrow.ItemArray, true); //} ResultDataTable.EndLoadData(); } return ResultDataTable; }
protected void Page_Load(object sender, System.EventArgs e) { // Create the Connection, DataAdapter, and DataSet. string connectionString = "Data Source=localhost;Initial Catalog=Northwind;" + "Integrated Security=SSPI"; SqlConnection con = new SqlConnection(connectionString); string sqlCat = "SELECT CategoryID, CategoryName FROM Categories"; string sqlProd = "SELECT ProductName, CategoryID, UnitPrice FROM Products"; SqlDataAdapter da = new SqlDataAdapter(sqlCat, con); DataSet ds = new DataSet(); try { con.Open(); // Fill the DataSet with the Categories table. da.Fill(ds, "Categories"); // Change the command text and retrieve the Products table. // You could also use another DataAdapter object for this task. da.SelectCommand.CommandText = sqlProd; da.Fill(ds, "Products"); } finally { con.Close(); } // Define the relationship between Categories and Products. DataRelation relat = new DataRelation("CatProds", ds.Tables["Categories"].Columns["CategoryID"], ds.Tables["Products"].Columns["CategoryID"]); // Add the relationship to the DataSet. ds.Relations.Add(relat); // Create the calculated columns. DataColumn count = new DataColumn( "Products (#)", typeof(int), "COUNT(Child(CatProds).CategoryID)"); DataColumn max = new DataColumn( "Most Expensive Product", typeof(decimal), "MAX(Child(CatProds).UnitPrice)"); DataColumn min = new DataColumn( "Least Expensive Product", typeof(decimal), "MIN(Child(CatProds).UnitPrice)"); // Add the columns. ds.Tables["Categories"].Columns.Add(count); ds.Tables["Categories"].Columns.Add(max); ds.Tables["Categories"].Columns.Add(min); // Show the data. GridView1.DataSource = ds.Tables["Categories"]; GridView1.DataBind(); }
public DataRelationDetailDescription( DataRelation relation ) : this() { if( relation == null ) throw new ArgumentNullException( "relation" ); this.DataRelation = relation; m_userAssignedDataRelation = true; }
public void run() { Exception exp = null; //create a dataset with two tables, with a DataRelation between them DataTable dtParent = GHTUtils.DataProvider.CreateParentDataTable(); DataTable dtChild = GHTUtils.DataProvider.CreateChildDataTable(); DataSet ds = new DataSet(); ds.Tables.Add(dtParent); ds.Tables.Add(dtChild); DataRelation drel = new DataRelation("ParentChild",dtParent.Columns["ParentId"],dtChild.Columns["ParentId"]); ds.Relations.Add(drel); //DataView dvChild = null; DataView dvParent = new DataView(dtParent); DataView dvTmp1 = dvParent[0].CreateChildView(drel); DataView dvTmp2 = dvParent[3].CreateChildView(drel); try { BeginCase("ChildView != null"); Compare(dvTmp1!=null,true); } catch(Exception ex) {exp = ex;} finally {EndCase(exp); exp = null;} try { BeginCase("Child view table = ChildTable"); Compare(dvTmp1.Table ,dtChild ); } catch(Exception ex) {exp = ex;} finally {EndCase(exp); exp = null;} try { BeginCase("ChildView1.Table = ChildView2.Table"); Compare(dvTmp1.Table ,dvTmp2.Table); } catch(Exception ex) {exp = ex;} finally {EndCase(exp); exp = null;} //the child dataview are different try { BeginCase("Child DataViews different "); Compare(dvTmp1.Equals(dvTmp2),false); } catch(Exception ex) {exp = ex;} finally {EndCase(exp); exp = null;} }
internal override void Bind(DataTable table, List<DataColumn> list) { BindTable(table); if (table == null) throw ExprException.AggregateUnbound(this.ToString()); if (local) { relation = null; } else { DataRelationCollection relations; relations = table.ChildRelations; if (relationName == null) { // must have one and only one relation if (relations.Count > 1) { throw ExprException.UnresolvedRelation(table.TableName, this.ToString()); } if (relations.Count == 1) { relation = relations[0]; } else { throw ExprException.AggregateUnbound(this.ToString()); } } else { relation = relations[relationName]; } } childTable = (relation == null) ? table : relation.ChildTable; this.column = childTable.Columns[columnName]; if (column == null) throw ExprException.UnboundName(columnName); // add column to the dependency list, do not add duplicate columns Debug.Assert(column != null, "Failed to bind column " + columnName); int i; for (i = 0; i < list.Count; i++) { // walk the list, check if the current column already on the list DataColumn dataColumn = (DataColumn)list[i]; if (column == dataColumn) { break; } } if (i >= list.Count) { list.Add(column); } // SQLBU 383715: Staleness of computed values in expression column as the relationship end columns are not being added to the dependent column list. AggregateNode.Bind(relation, list); }
/// <summary> /// Fetches the page list. /// </summary> /// <returns></returns> public DataSet FetchPageList() { DataSet ds = new Query(Page.Schema).ORDER_BY(Page.Columns.SortOrder, ASC).ExecuteDataSet(); ds.DataSetName = MENUS; ds.Tables[0].TableName = MENU; DataRelation relation = new DataRelation(PARENT_CHILD, ds.Tables[MENU].Columns[PAGE_ID], ds.Tables[MENU].Columns[PARENT_ID], false); relation.Nested = true; ds.Relations.Add(relation); return ds; }
public override System.Data.DataSet GetVoteByIsShow() { System.Data.Common.DbCommand sqlStringCommand = this.database.GetSqlStringCommand("SELECT * FROM Hishop_Votes WHERE IsBackup = 1 SELECT * FROM Hishop_VoteItems WHERE voteId IN (SELECT voteId FROM Hishop_Votes WHERE IsBackup = 1)"); System.Data.DataSet dataSet = this.database.ExecuteDataSet(sqlStringCommand); System.Data.DataColumn parentColumn = dataSet.Tables[0].Columns["VoteId"]; System.Data.DataColumn childColumn = dataSet.Tables[1].Columns["VoteId"]; System.Data.DataRelation relation = new System.Data.DataRelation("Vote", parentColumn, childColumn); dataSet.Relations.Add(relation); return(dataSet); }
public override System.Data.DataSet GetHelps() { System.Data.Common.DbCommand sqlStringCommand = this.database.GetSqlStringCommand("SELECT * FROM Hishop_HelpCategories WHERE IsShowFooter = 1 ORDER BY DisplaySequence SELECT * FROM Hishop_Helps WHERE IsShowFooter = 1 AND CategoryId IN (SELECT CategoryId FROM Hishop_HelpCategories WHERE IsShowFooter = 1)"); System.Data.DataSet dataSet = this.database.ExecuteDataSet(sqlStringCommand); System.Data.DataColumn parentColumn = dataSet.Tables[0].Columns["CateGoryId"]; System.Data.DataColumn childColumn = dataSet.Tables[1].Columns["CateGoryId"]; System.Data.DataRelation relation = new System.Data.DataRelation("CateGory", parentColumn, childColumn); dataSet.Relations.Add(relation); return(dataSet); }
public static void createDataRelationMetadata(System.Data.DataSet ds) { // DataRelation requires: two DataColumns (parent and child) and a name. { System.Data.DataColumn parent = ds.Tables["DataStore"].Columns["Name"]; System.Data.DataColumn child = ds.Tables["DataStoreSnapshot"].Columns["Name"]; System.Data.DataRelation relation = new System.Data.DataRelation("parent2Child", parent, child); // ds.Tables[ "DataStoreSnapshot" ].ParentRelations.Add( relation ); } }
static void Main(string[] args) { DataSet ds = new DataSet(); DataTable tablDrivers = CreateTableDrivers(); DataTable tablCars = CreateTableCar(); ds.Tables.AddRange(new[] { tablDrivers, tablCars }); // создание отношения между таблицами Drivers и Cars DataRelation relation = new DataRelation("FK_DriversCars", // имя отношения tablDrivers.Columns["Id"], // поле родительской таблицы tablCars.Columns["IdDriver"], // поле дочерней таблицы false); // создавать/не создавать ограничения // после созания ограничения его нужно добавить в коллекцию Relations // объекта DataSet, в которой содержаться таблицы // без этого шага отношение не будет работать ds.Relations.Add(relation); Console.WriteLine("Вывод информации через дочерние строки "); foreach (DataRow driverRow in tablDrivers.Rows) { Console.WriteLine(" Владелец: {0}", driverRow["Name"]); Console.WriteLine(" Автомобили: "); // метод GetChaildRows получает дочерние строки в виде массива DataRow[] foreach (DataRow carsRow in driverRow.GetChildRows(relation)) { Console.WriteLine("{0} {1}", carsRow["Name"], carsRow["Price"]); } } Console.WriteLine("Вывод информации через родительские строки "); foreach (DataRow carsRow in tablCars.Rows) { Console.Write(" Атомобиль: {0} {1}", carsRow["Name"], carsRow["Price"]); Console.Write(" Владелец: "); // метод GetParrentRow возвращает родительские строки в виде массива DataRow[] foreach (DataRow driverRow in carsRow.GetParentRows(relation)) { Console.WriteLine("{0}", driverRow["Name"]); } } ds.WriteXmlSchema("Registration.xsd"); ds.WriteXml("Registration.xml"); Console.ReadKey(); }
public override System.Data.DataSet GetVoteByIsShow() { System.Data.Common.DbCommand sqlStringCommand = this.database.GetSqlStringCommand("SELECT * FROM distro_Votes WHERE IsBackup = 1 AND DistributorUserId=@DistributorUserId SELECT * FROM distro_VoteItems WHERE voteId IN (SELECT voteId FROM distro_Votes WHERE IsBackup = 1 AND DistributorUserId=@DistributorUserId)"); this.database.AddInParameter(sqlStringCommand, "DistributorUserId", System.Data.DbType.Int32, HiContext.Current.SiteSettings.UserId.Value); System.Data.DataSet dataSet = this.database.ExecuteDataSet(sqlStringCommand); System.Data.DataColumn parentColumn = dataSet.Tables[0].Columns["VoteId"]; System.Data.DataColumn childColumn = dataSet.Tables[1].Columns["VoteId"]; System.Data.DataRelation relation = new System.Data.DataRelation("Vote", parentColumn, childColumn); dataSet.Relations.Add(relation); return(dataSet); }
internal override void Bind(DataTable table, List<DataColumn> list) { BindTable(table); column = null; // clear for rebinding (if original binding was valid) relation = null; if (table == null) throw ExprException.ExpressionUnbound(this.ToString()); // First find parent table DataRelationCollection relations; relations = table.ParentRelations; if (relationName == null) { // must have one and only one relation if (relations.Count > 1) { throw ExprException.UnresolvedRelation(table.TableName, this.ToString()); } relation = relations[0]; } else { relation = relations[relationName]; } if (null == relation) { throw ExprException.BindFailure(relationName);// WebData 112162: this operation is not clne specific, throw generic exception } DataTable parentTable = relation.ParentTable; Debug.Assert(relation != null, "Invalid relation: no parent table."); Debug.Assert(columnName != null, "All Lookup expressions have columnName set."); this.column = parentTable.Columns[columnName]; if (column == null) throw ExprException.UnboundName(columnName); // add column to the dependency list int i; for (i = 0; i < list.Count; i++) { // walk the list, check if the current column already on the list DataColumn dataColumn = list[i]; if (column == dataColumn) { break; } } if (i >= list.Count) { list.Add(column); } // SQLBU 383715: Staleness of computed values in expression column as the relationship end columns are not being added to the dependent column list. AggregateNode.Bind(relation, list); }
public Form1() { InitializeComponent(); saveprompt = false; connString = @"Provider=Microsoft.ACE.OLEDB.12.0; Data Source=C:\Users\hudgi\source\repos\hudginsm\DataSkeptic-Projects\C#\C# Database Basics\Northwind.accdb; Persist Security Info=False;"; NW_Orders = new DataSet(); query1 = "SELECT * FROM [Orders]"; query2 = "SELECT * FROM [Order Details]"; orders_dAdapter = new OleDbDataAdapter(query1, connString); order_details_dAdapter = new OleDbDataAdapter(query2, connString); cBuilder = new OleDbCommandBuilder(orders_dAdapter); cBuilder.QuotePrefix = "["; cBuilder.QuoteSuffix = "]"; cBuilder1 = new OleDbCommandBuilder(order_details_dAdapter); cBuilder1.QuotePrefix = "["; cBuilder1.QuoteSuffix = "]"; orders_dAdapter.Fill(NW_Orders, "Orders"); order_details_dAdapter.Fill(NW_Orders, "Order Details"); DataColumn parentcolumn = NW_Orders.Tables["Orders"].Columns["Order ID"]; DataColumn childcolumn = NW_Orders.Tables["Order Details"].Columns["Order ID"]; DataRelation relation = new System.Data.DataRelation("OrderstoDetails", parentcolumn, childcolumn); NW_Orders.Relations.Add(relation); orders_bndSource = new BindingSource(); orders_bndSource.DataSource = NW_Orders.Tables["Orders"]; order_details_bndSource = new BindingSource(); order_details_bndSource.DataSource = orders_bndSource; order_details_bndSource.DataMember = "OrderstoDetails"; order_details_bndSource.CurrentItemChanged += new EventHandler(order_details_bndSource_CurrentItemChanged); order_details_bndSource.ListChanged += new ListChangedEventHandler(order_details_bndSource_ListChanged); this.textBox1.DataBindings.Add(new Binding("Text", orders_bndSource, "Order ID", true)); this.dateTimePicker1.DataBindings.Add(new Binding("Text", orders_bndSource, "Order Date", true)); this.dateTimePicker2.DataBindings.Add(new Binding("Text", orders_bndSource, "Shipped Date", true)); this.textBox4.DataBindings.Add(new Binding("Text", order_details_bndSource, "ID", true)); this.textBox5.DataBindings.Add(new Binding("Text", order_details_bndSource, "Order ID", true)); this.textBox6.DataBindings.Add(new Binding("Text", order_details_bndSource, "Quantity", true)); this.textBox7.DataBindings.Add(new Binding("Text", order_details_bndSource, "Unit Price", true)); this.textBox8.DataBindings.Add(new Binding("Text", order_details_bndSource, "Discount", true)); this.textBox2.Text = "" + (order_details_bndSource.Position + 1); this.textBox3.Text = order_details_bndSource.Count.ToString(); foreach (Control tx in this.Controls) { if (tx.DataBindings.Count > 0 && tx.Name != "textBox1" && tx.Name != "textBox3") { tx.Enter += new EventHandler(tx_Enter); } } }
public override System.Data.DataSet GetHelps() { System.Data.Common.DbCommand sqlStringCommand = this.database.GetSqlStringCommand("SELECT * FROM distro_HelpCategories WHERE IsShowFooter = 1 AND DistributorUserId=@DistributorUserId ORDER BY DisplaySequence SELECT * FROM distro_Helps WHERE IsShowFooter = 1 AND CategoryId IN (SELECT CategoryId FROM distro_HelpCategories WHERE IsShowFooter = 1 AND DistributorUserId=@DistributorUserId)"); this.database.AddInParameter(sqlStringCommand, "DistributorUserId", System.Data.DbType.Int32, HiContext.Current.SiteSettings.UserId.Value); System.Data.DataSet dataSet = this.database.ExecuteDataSet(sqlStringCommand); System.Data.DataColumn parentColumn = dataSet.Tables[0].Columns["CateGoryId"]; System.Data.DataColumn childColumn = dataSet.Tables[1].Columns["CateGoryId"]; System.Data.DataRelation relation = new System.Data.DataRelation("CateGory", parentColumn, childColumn); dataSet.Relations.Add(relation); return(dataSet); }
/// <summary> /// Performs verification on the table. /// </summary> /// <param name="relation">The relation to check.</param> protected override void AddCore (DataRelation relation) { if (relation.ChildTable.DataSet != dataSet || relation.ParentTable.DataSet != dataSet) throw new DataException (); base.AddCore (relation); relation.ParentTable.ChildRelations.Add (relation); relation.ChildTable.ParentRelations.Add (relation); relation.SetDataSet (dataSet); relation.UpdateConstraints (); }
private void InitClass() { this.DataSetName = "dsPublishers"; this.Prefix = ""; this.Namespace = "Deverest.Generated.DataSets"; this.Locale = new System.Globalization.CultureInfo("en-US"); this.CaseSensitive = false; this.EnforceConstraints = true; System.Data.ForeignKeyConstraint fkc; this.tablepublishers = new publishersDataTable(); this.Tables.Add(this.tablepublishers); this.tabletitles = new titlesDataTable(); this.Tables.Add(this.tabletitles); fkc = new System.Data.ForeignKeyConstraint("publisherstitles", new System.Data.DataColumn[] { this.tablepublishers.pub_idColumn, this.tablepublishers.pub_nameColumn }, new System.Data.DataColumn[] { this.tabletitles.titlepub_idColumn, this.tabletitles.titleColumn }); this.tabletitles.Constraints.Add(fkc); fkc.AcceptRejectRule = System.Data.AcceptRejectRule.None; fkc.DeleteRule = System.Data.Rule.Cascade; fkc.UpdateRule = System.Data.Rule.Cascade; this.relationpublisherstitles = new System.Data.DataRelation("publisherstitles", new System.Data.DataColumn[] { this.tablepublishers.pub_idColumn, this.tablepublishers.pub_nameColumn }, new System.Data.DataColumn[] { this.tabletitles.titlepub_idColumn, this.tabletitles.titleColumn }); this.relationpublisherstitles.Nested = true; this.Relations.Add(this.relationpublisherstitles); this.tabletitleauthors = new titleauthorsDataTable(); this.Tables.Add(this.tabletitleauthors); fkc = new System.Data.ForeignKeyConstraint("titlestitleauthors", new System.Data.DataColumn[] { this.tabletitles.title_idColumn }, new System.Data.DataColumn[] { this.tabletitleauthors.title_idColumn }); this.tabletitleauthors.Constraints.Add(fkc); fkc.AcceptRejectRule = System.Data.AcceptRejectRule.None; fkc.DeleteRule = System.Data.Rule.Cascade; fkc.UpdateRule = System.Data.Rule.Cascade; this.relationtitlestitleauthors = new System.Data.DataRelation("titlestitleauthors", new System.Data.DataColumn[] { this.tabletitles.title_idColumn }, new System.Data.DataColumn[] { this.tabletitleauthors.title_idColumn }); this.relationtitlestitleauthors.Nested = true; this.Relations.Add(this.relationtitlestitleauthors); }
public override System.Data.DataSet GetHelpTitleList() { string text = "SELECT * FROM Hishop_HelpCategories order by DisplaySequence"; text += " SELECT HelpId,Title,CategoryId FROM Hishop_Helps where CategoryId IN (SELECT CategoryId FROM Hishop_HelpCategories)"; System.Data.Common.DbCommand sqlStringCommand = this.database.GetSqlStringCommand(string.Format(text, new object[0])); System.Data.DataSet dataSet = this.database.ExecuteDataSet(sqlStringCommand); System.Data.DataColumn parentColumn = dataSet.Tables[0].Columns["CateGoryId"]; System.Data.DataColumn childColumn = dataSet.Tables[1].Columns["CateGoryId"]; System.Data.DataRelation relation = new System.Data.DataRelation("CateGory", parentColumn, childColumn); dataSet.Relations.Add(relation); return(dataSet); }
public System.Data.DataSet GetCategoryList() { string text = "select * from Hishop_Categories where ParentCategoryId=0 order by DisplaySequence asc,CategoryId asc ;"; text += "select * from Hishop_Categories where ParentCategoryId<>0 order by DisplaySequence asc,CategoryId asc;"; System.Data.Common.DbCommand sqlStringCommand = this.database.GetSqlStringCommand(text); System.Data.DataSet dataSet = this.database.ExecuteDataSet(sqlStringCommand); System.Data.DataColumn parentColumn = dataSet.Tables[0].Columns["CategoryId"]; System.Data.DataColumn childColumn = dataSet.Tables[1].Columns["ParentCategoryId"]; System.Data.DataRelation relation = new System.Data.DataRelation("SubCategories", parentColumn, childColumn); dataSet.Relations.Add(relation); return(dataSet); }
public override System.Data.DataSet GetHelpTitleList() { string text = "SELECT * FROM distro_HelpCategories where DistributorUserId=@DistributorUserId order by DisplaySequence"; text += " SELECT HelpId,Title,CategoryId FROM distro_Helps where DistributorUserId=@DistributorUserId AND CategoryId IN (SELECT CategoryId FROM distro_HelpCategories WHERE DistributorUserId=@DistributorUserId)"; System.Data.Common.DbCommand sqlStringCommand = this.database.GetSqlStringCommand(text); this.database.AddInParameter(sqlStringCommand, "DistributorUserId", System.Data.DbType.Int32, HiContext.Current.SiteSettings.UserId.Value); System.Data.DataSet dataSet = this.database.ExecuteDataSet(sqlStringCommand); System.Data.DataColumn parentColumn = dataSet.Tables[0].Columns["CateGoryId"]; System.Data.DataColumn childColumn = dataSet.Tables[1].Columns["CateGoryId"]; System.Data.DataRelation relation = new System.Data.DataRelation("CateGory", parentColumn, childColumn); dataSet.Relations.Add(relation); return(dataSet); }
internal void InitVars() { this.tablepublishers = ((publishersDataTable)(this.Tables["publishers"])); if ((this.tablepublishers != null)) { this.tablepublishers.InitVars(); } this.tabletitles = ((titlesDataTable)(this.Tables["titles"])); if ((this.tabletitles != null)) { this.tabletitles.InitVars(); } this.relationpublisherstitles = this.Relations["publisherstitles"]; this.tabletitleauthors = ((titleauthorsDataTable)(this.Tables["titleauthors"])); if ((this.tabletitleauthors != null)) { this.tabletitleauthors.InitVars(); } this.relationtitlestitleauthors = this.Relations["titlestitleauthors"]; }
public IEnumerable <Provider> CreateProviders(DataSet ds) { List <Provider> providers = new List <Provider>(); DataTable dt = ds.Tables["Table"]; dt.TableName = "Provider"; DataTable dt1 = ds.Tables["Table1"]; dt1.TableName = "Taxonomy"; DataTable dt2 = ds.Tables["Table2"]; dt2.TableName = "OthProvider"; // Setup Relations DataColumn parentColumn = ds.Tables["Provider"].Columns["NPI"]; DataColumn txchildColumn = ds.Tables["Taxonomy"].Columns["NPI"]; DataColumn othchildColumn = ds.Tables["OthProvider"].Columns["NPI"]; DataRelation txrelation = new System.Data.DataRelation("ProvidersTaxonomy", parentColumn, txchildColumn); DataRelation othrelation = new System.Data.DataRelation("ProvidersOth", parentColumn, othchildColumn); ds.Relations.Add(txrelation); ds.Relations.Add(othrelation); foreach (DataRow row in dt.Rows) { // Get Taxonomy DataRow[] txitems = row.GetChildRows(txrelation); // Get Other Related Providers DataRow[] othitems = row.GetChildRows(othrelation); // Create a Provider Provider prov = CreateProvider(row, txitems, othitems); // Add the Provider to a collection providers.Add(prov); } return(providers); }
public void Combine() { string constring = ConfigurationManager.ConnectionStrings["constr"].ConnectionString; OracleConnection connection = new OracleConnection(constring); DataSet dataset = new DataSet(); OracleDataAdapter adapter = new OracleDataAdapter("select * from Employees ", constring); adapter.Fill(dataset, "emp"); OracleDataAdapter adapter2 = new OracleDataAdapter("select * from Departments ", constring); adapter2.Fill(dataset, "dept"); System.Data.DataRelation dataRelation = new System.Data.DataRelation("combine", dataset.Tables[1].Columns[0], dataset.Tables[0].Columns[4]); dataset.Relations.Add(dataRelation); dataset.Relations[0].Nested = true; dg3.DataContext = dataset; }
void ConnectToData() { // Create the ConnectionString and create a SqlConnection. // Change the data source value to the name of your computer. string cString = "Persist Security Info=False;Integrated Security=SSPI;database=northwind;server=DH"; SqlConnection myConnection = new SqlConnection(cString); // Create a SqlDataAdapter. SqlDataAdapter myAdapter = new SqlDataAdapter(); myAdapter.TableMappings.Add("Table", "Suppliers"); myConnection.Open(); SqlCommand myCommand = new SqlCommand("SELECT * FROM Suppliers", myConnection); myCommand.CommandType = CommandType.Text; myAdapter.SelectCommand = myCommand; Console.WriteLine("The connection is open"); ds = new DataSet("Customers"); myAdapter.Fill(ds); // Create a second Adapter and Command. SqlDataAdapter adpProducts = new SqlDataAdapter(); adpProducts.TableMappings.Add("Table", "Products"); SqlCommand cmdProducts = new SqlCommand("SELECT * FROM Products", myConnection); adpProducts.SelectCommand = cmdProducts; adpProducts.Fill(ds); myConnection.Close(); Console.WriteLine("The connection is closed."); System.Data.DataRelation dr; System.Data.DataColumn dc1; System.Data.DataColumn dc2; // Get the parent and child columns of the two tables. dc1 = ds.Tables["Suppliers"].Columns["SupplierID"]; dc2 = ds.Tables["Products"].Columns["SupplierID"]; dr = new System.Data.DataRelation("suppliers2products", dc1, dc2); ds.Relations.Add(dr); }
public DataRelation AddRelation( string relactionName, IColumn[] masterColumn, IColumn[] slaveColumn, bool createConstraint ) { if (Dataset == null) { return(null); } System.Data.DataRelation drDB; System.Data.DataColumn[] dcM; System.Data.DataColumn[] dcS; Debug.Assert(masterColumn.Length == slaveColumn.Length, "AddRelation - disambiguous number column"); dcM = new DataColumn[masterColumn.Length]; dcS = new DataColumn[slaveColumn.Length]; for (int t = 0; t < masterColumn.Length; t++) { dcM[t] = Dataset.Tables[masterColumn[t].Tablename].Columns[masterColumn[t].Name]; dcS[t] = Dataset.Tables[slaveColumn[t].Tablename].Columns[slaveColumn[t].Name]; } drDB = new System.Data.DataRelation(relactionName, dcM, dcS, createConstraint); Dataset.Relations.Add(drDB); Dataset.EnforceConstraints = createConstraint; AddSlaveBinding(relactionName); return(drDB); }
public override void AddRelation(IPDFDataSetProviderCommand parentCommand, IPDFDataSetProviderCommand childCommand, System.Data.DataSet dataset, PDFDataContext context) { string childtablename = childCommand.GetDataTableName(dataset); if (string.IsNullOrEmpty(childtablename)) { throw new ArgumentNullException("owner.CommandName"); } string parenttablename = parentCommand.GetDataTableName(dataset); if (string.IsNullOrEmpty(parenttablename)) { throw new ArgumentNullException("this.Parent"); } int matchcount = this.MatchOn.Count; if (matchcount < 1) { throw new ArgumentOutOfRangeException("this.MatchOn"); } DataTable child = dataset.Tables[childtablename]; if (null == child) { context.TraceLog.Add(TraceLevel.Message, SqlProviderCommand.SqlCommandLog, "Relation not created between '" + childtablename + "' and '" + parenttablename + "' as one or more tables are not in data set"); return; } DataTable parent = dataset.Tables[parenttablename]; if (null == parent) { context.TraceLog.Add(TraceLevel.Message, SqlProviderCommand.SqlCommandLog, "Relation not created between '" + childtablename + "' and '" + parenttablename + "' as one or more tables are not in data set"); return; } DataColumn[] childmatches = new DataColumn[matchcount]; DataColumn[] parentmatches = new DataColumn[matchcount]; for (int i = 0; i < matchcount; i++) { DataRelationMatch match = this.MatchOn[i]; if (string.IsNullOrEmpty(match.ChildName)) { throw new NullReferenceException(string.Format(Errors.NullChildColumnForRelationToTable, childtablename, parentCommand.ID)); } DataColumn col = child.Columns[match.ChildName]; if (null == col) { throw new NullReferenceException(string.Format(Errors.TableDoesNotContainColumn, match.ChildName, parentCommand.ID, childtablename)); } childmatches[i] = col; if (string.IsNullOrEmpty(match.ParentName)) { throw new NullReferenceException(string.Format(Errors.NullParentColumnForRelationToTable, parenttablename, parentCommand.ID)); } col = parent.Columns[match.ParentName]; if (null == col) { throw new NullReferenceException(string.Format(Errors.TableDoesNotContainColumn, match.ParentName)); } parentmatches[i] = col; } System.Data.DataRelation rel = new System.Data.DataRelation(childtablename + "_2_" + parenttablename, parentmatches, childmatches); rel.Nested = true; dataset.Relations.Add(rel); }
public void EstablecerRelacion() { DataRelation cursoAlumno = new System.Data.DataRelation("CursoAlumno", this._dataSetAlumnos_Cursos.Tables["dbo.cursos"].Columns[0], this._dataSetAlumnos_Cursos.Tables["dbo.alumnos"].Columns[2]); this._dataSetAlumnos_Cursos.Relations.Add(cursoAlumno); }
private static void ConnectToData(string connectionString) { //Create a SqlConnection to the Northwind database. using (SqlConnection connection = new SqlConnection(connectionString)) { //Create a SqlDataAdapter for the Suppliers table. SqlDataAdapter adapter = new SqlDataAdapter(); // A table mapping names the DataTable. adapter.TableMappings.Add("Table", "Suppliers"); // Open the connection. connection.Open(); Console.WriteLine("The SqlConnection is open."); // Create a SqlCommand to retrieve Suppliers data. SqlCommand command = new SqlCommand( "SELECT SupplierID, CompanyName FROM dbo.Suppliers;", connection); command.CommandType = CommandType.Text; // Set the SqlDataAdapter's SelectCommand. adapter.SelectCommand = command; // Fill the DataSet. DataSet dataSet = new DataSet("Suppliers"); adapter.Fill(dataSet); // Create a second Adapter and Command to get // the Products table, a child table of Suppliers. SqlDataAdapter productsAdapter = new SqlDataAdapter(); productsAdapter.TableMappings.Add("Table", "Products"); SqlCommand productsCommand = new SqlCommand( "SELECT ProductID, SupplierID FROM dbo.Products;", connection); productsAdapter.SelectCommand = productsCommand; // Fill the DataSet. productsAdapter.Fill(dataSet); // Close the connection. connection.Close(); Console.WriteLine("The SqlConnection is closed."); // Create a DataRelation to link the two tables // based on the SupplierID. DataColumn parentColumn = dataSet.Tables["Suppliers"].Columns["SupplierID"]; DataColumn childColumn = dataSet.Tables["Products"].Columns["SupplierID"]; DataRelation relation = new System.Data.DataRelation("SuppliersProducts", parentColumn, childColumn); dataSet.Relations.Add(relation); Console.WriteLine( "The {0} DataRelation has been created.", relation.RelationName); } }
public MasterDetail() { // Create Components InitializeComponent(); // Setup DB-Connection ConnectionString = "data source=xeon;uid=sa;password=manager;database=northwind"; SqlConnection cn = new SqlConnection(ConnectionString); // Create the DataSet ds = new DataSet("CustOrders"); // Fill the Dataset with Customers, map Default Tablename // "Table" to "Customers". SqlDataAdapter da1 = new SqlDataAdapter("SELECT * FROM Customers", cn); da1.TableMappings.Add("Table", "Customers"); da1.Fill(ds); // Fill the Dataset with Orders, map Default Tablename // "Table" to "Orders". SqlDataAdapter da2 = new SqlDataAdapter("SELECT * FROM Orders", cn); da2.TableMappings.Add("Table", "Orders"); da2.Fill(ds); // Fill the Dataset with [Order Details], map Default Tablename // "Table" to "OrderDetails". SqlDataAdapter da3 = new SqlDataAdapter("SELECT * FROM [Order Details]", cn); da3.TableMappings.Add("Table", "OrderDetails"); da3.Fill(ds); // Show created Tablenames within the Dataset string myMessage = "Table Mappings: "; for (int i = 0; i < ds.Tables.Count; i++) { myMessage += i.ToString() + " " + ds.Tables[i].ToString() + " "; } // Establish the Relationship "RelCustOrd" // between Customers ---< Orders System.Data.DataRelation relCustOrd; System.Data.DataColumn colMaster1; System.Data.DataColumn colDetail1; colMaster1 = ds.Tables["Customers"].Columns["CustomerID"]; colDetail1 = ds.Tables["Orders"].Columns["CustomerID"]; relCustOrd = new System.Data.DataRelation("RelCustOrd", colMaster1, colDetail1); ds.Relations.Add(relCustOrd); // Establish the Relationship "RelOrdDet" // between Orders ---< [Order Details] System.Data.DataRelation relOrdDet; System.Data.DataColumn colMaster2; System.Data.DataColumn colDetail2; colMaster2 = ds.Tables["Orders"].Columns["OrderID"]; colDetail2 = ds.Tables["OrderDetails"].Columns["OrderID"]; relOrdDet = new System.Data.DataRelation("RelOrdDet", colMaster2, colDetail2); ds.Relations.Add(relOrdDet); // Show created Relations within the Dataset myMessage += "Relation Mappings: "; for (int i = 0; i < ds.Relations.Count; i++) { myMessage += i.ToString() + " " + ds.Relations[i].ToString() + " "; } txtMessage.Text = myMessage; // The DataViewManager returned by the DefaultViewManager // property allows you to create custom settings for each // DataTable in the DataSet. dsView = ds.DefaultViewManager; // Databinding for the Grid's grdOrders.DataSource = dsView; grdOrders.DataMember = "Customers.RelCustOrd"; grdOrderDetails.DataSource = dsView; grdOrderDetails.DataMember = "Customers.RelCustOrd.RelOrdDet"; // Databinding for the Combo Box // // If you have two controls bound to the same datasource, // and you do not want them to share the same position, // then you must make sure that the BindingContext member // of one control differs from the BindingContext member of // the other control. If they have the same BindingContext, // they will share the same position in the datasource. // // If you add a ComboBox and a DataGrid to a form, the default // behavior is for the BindingContext member of each of the // two controls to be set to the Form's BindingContext. Thus, // the default behavior is for the DataGrid and ComboBox to share // the same BindingContext, and hence the selection in the ComboBox // is synchronized with the current row of the DataGrid. If you // do not want this behavior, you should create a new BindingContext // member for at least one of the controls. // // IF YOU UNCOMMENT THE FOLLOWING LINE THE SYNC WILL NO MORE WORK // cbCust.BindingContext = new BindingContext(); cbCust.DataSource = dsView; cbCust.DisplayMember = "Customers.CompanyName"; cbCust.ValueMember = "Customers.CustomerID"; // Databinding for the Text Columns txtContact.DataBindings.Add("Text", dsView, "Customers.ContactName"); txtPhoneNo.DataBindings.Add("Text", dsView, "Customers.Phone"); txtFaxNo.DataBindings.Add("Text", dsView, "Customers.Fax"); }
public virtual int IndexOf(DataRelation relation) { return(List.IndexOf(relation)); }
protected virtual void RemoveCore(DataRelation relation) { // Remove from collection List.Remove(relation); }
private void AddRowToTable(XmlNode tableNode, DataColumn relationColumn, bool inferSchema, bool fillRows) { Hashtable rowValue = new Hashtable(); DataTable table; // Check if the table exists in the DataSet. If not create one. if (DSet.Tables.Contains(tableNode.LocalName)) { table = DSet.Tables[tableNode.LocalName]; } else if (inferSchema) { table = new DataTable(tableNode.LocalName); DSet.Tables.Add(table); } else { return; } // For elements that are inferred as tables and that contain text // but have no child elements, a new column named "TableName_Text" // is created for the text of each of the elements. // If an element is inferred as a table and has text, but also has child elements, // the text is ignored. // Note : if an element is inferred as a table and has text // and has no child elements, // but the repeated ements of this table have child elements, // then the text is ignored. if (!HaveChildElements(tableNode) && HaveText(tableNode) && !IsRepeatedHaveChildNodes(tableNode)) { string columnName = tableNode.Name + "_Text"; if (!table.Columns.Contains(columnName)) { table.Columns.Add(columnName); } rowValue.Add(columnName, tableNode.InnerText); } // Get the child nodes of the table. Any child can be one of the following tow: // 1. DataTable - if there was a relation with another table.. // 2. DataColumn - column of the current table. XmlNodeList childList = tableNode.ChildNodes; for (int i = 0; i < childList.Count; i++) { XmlNode childNode = childList[i]; // we are looping through elements only // Note : if an element is inferred as a table and has text, but also has child elements, // the text is ignored. if (childNode.NodeType != XmlNodeType.Element) { continue; } // Elements that have attributes are inferred as tables. // Elements that have child elements are inferred as tables. // Elements that repeat are inferred as a single table. if (IsInferredAsTable(childNode)) { // child node inferred as table if (inferSchema) { // We need to create new column for the relation between the current // table and the new table we found (the child table). string newRelationColumnName = table.TableName + "_Id"; if (!table.Columns.Contains(newRelationColumnName)) { DataColumn newRelationColumn = new DataColumn(newRelationColumnName, typeof(int)); newRelationColumn.AllowDBNull = false; newRelationColumn.AutoIncrement = true; // we do not want to serialize this column so MappingType is Hidden. newRelationColumn.ColumnMapping = MappingType.Hidden; table.Columns.Add(newRelationColumn); } // Add a row to the new table we found. AddRowToTable(childNode, table.Columns[newRelationColumnName], inferSchema, fillRows); } else { AddRowToTable(childNode, null, inferSchema, fillRows); } } else { // Elements that have no attributes or child elements, and do not repeat, // are inferred as columns. object val = null; if (childNode.FirstChild != null) { val = childNode.FirstChild.Value; } else { val = ""; } if (table.Columns.Contains(childNode.LocalName)) { rowValue.Add(childNode.LocalName, val); } else if (inferSchema) { table.Columns.Add(childNode.LocalName); rowValue.Add(childNode.LocalName, val); } } } // Column can be attribute of the table element. XmlAttributeCollection aCollection = tableNode.Attributes; for (int i = 0; i < aCollection.Count; i++) { XmlAttribute attr = aCollection[i]; //the atrribute can be the namespace. if (attr.Prefix.Equals("xmlns")) { table.Namespace = attr.Value; } else // the attribute is a column. { if (!table.Columns.Contains(attr.LocalName)) { DataColumn col = table.Columns.Add(attr.LocalName); col.ColumnMapping = MappingType.Attribute; } table.Columns[attr.LocalName].Namespace = table.Namespace; rowValue.Add(attr.LocalName, attr.Value); } } // If the current table is a child table we need to add a new column for the relation // and add a new relation to the DataSet. if (relationColumn != null) { if (!table.Columns.Contains(relationColumn.ColumnName)) { DataColumn dc = new DataColumn(relationColumn.ColumnName, typeof(int)); // we do not want to serialize this column so MappingType is Hidden. dc.ColumnMapping = MappingType.Hidden; table.Columns.Add(dc); // Convention of relation name is: ParentTableName_ChildTableName DataRelation dr = new DataRelation(relationColumn.Table.TableName + "_" + dc.Table.TableName, relationColumn, dc); dr.Nested = true; DSet.Relations.Add(dr); UniqueConstraint.SetAsPrimaryKey(dr.ParentTable.Constraints, dr.ParentKeyConstraint); } rowValue.Add(relationColumn.ColumnName, relationColumn.GetAutoIncrementValue()); } // Create new row and add all values to the row. // then add it to the table. DataRow row = table.NewRow(); IDictionaryEnumerator enumerator = rowValue.GetEnumerator(); while (enumerator.MoveNext()) { row [enumerator.Key.ToString()] = StringToObject(table.Columns[enumerator.Key.ToString()].DataType, enumerator.Value.ToString()); } if (fillRows) { table.Rows.Add(row); } }
private void ReadXml() { if (document.DocumentElement == null) { return; } dataset.Locale = new CultureInfo("en-US"); // default(!) // If the root element is not a data table, treat // this element as DataSet. // Read one element. It might be DataSet element. XmlElement el = document.DocumentElement; if (el.NamespaceURI == XmlSchema.Namespace) { throw new InvalidOperationException("DataSet is not designed to handle XML Schema as data content. Please use ReadXmlSchema method instead of InferXmlSchema method."); } if (IsDocumentElementTable()) { InferTopLevelTable(el); } else { string localName = XmlHelper.Decode(el.LocalName); dataset.DataSetName = localName; dataset.Namespace = el.NamespaceURI; dataset.Prefix = el.Prefix; foreach (XmlNode n in el.ChildNodes) { if (n.NamespaceURI == XmlSchema.Namespace) { continue; } if (n.NodeType == XmlNodeType.Element) { InferTopLevelTable(n as XmlElement); } } } int count = 0; foreach (TableMapping map in tables) { string baseName = map.PrimaryKey != null ? map.PrimaryKey.ColumnName : map.Table.TableName + "_Id"; // Make sure name of RK column is unique string rkName = baseName; if (map.ChildTables [map.Table.TableName] != null) { rkName = baseName + '_' + count; while (map.GetColumn(rkName) != null) { count++; rkName = baseName + '_' + count; } } foreach (TableMapping ct in map.ChildTables) { ct.ReferenceKey = GetMappedColumn(ct, rkName, map.Table.Prefix, map.Table.Namespace, MappingType.Hidden, map.PrimaryKey != null ? map.PrimaryKey.DataType : typeof(int)); } } foreach (TableMapping map in tables) { if (map.ExistsInDataSet) { continue; } if (map.PrimaryKey != null) { map.Table.Columns.Add(map.PrimaryKey); } foreach (DataColumn col in map.Elements) { map.Table.Columns.Add(col); } foreach (DataColumn col in map.Attributes) { map.Table.Columns.Add(col); } if (map.SimpleContent != null) { map.Table.Columns.Add(map.SimpleContent); } if (map.ReferenceKey != null) { map.Table.Columns.Add(map.ReferenceKey); } dataset.Tables.Add(map.Table); } foreach (RelationStructure rs in relations) { string relName = rs.ExplicitName != null ? rs.ExplicitName : rs.ParentTableName + "_" + rs.ChildTableName; DataTable pt = dataset.Tables [rs.ParentTableName]; DataTable ct = dataset.Tables [rs.ChildTableName]; DataColumn pc = pt.Columns [rs.ParentColumnName]; DataColumn cc = null; // If both parent and child tables have same name, it is quite // possible to have column names suffixed with some numbers. if (rs.ParentTableName == rs.ChildTableName) { cc = ct.Columns [rs.ChildColumnName + "_" + count]; } if (cc == null) { cc = ct.Columns [rs.ChildColumnName]; } if (pt == null) { throw new DataException("Parent table was not found : " + rs.ParentTableName); } else if (ct == null) { throw new DataException("Child table was not found : " + rs.ChildTableName); } else if (pc == null) { throw new DataException("Parent column was not found :" + rs.ParentColumnName); } else if (cc == null) { throw new DataException("Child column was not found :" + rs.ChildColumnName); } DataRelation rel = new DataRelation(relName, pc, cc, rs.CreateConstraint); if (rs.IsNested) { rel.Nested = true; rel.ParentTable.PrimaryKey = rel.ParentColumns; } dataset.Relations.Add(rel); } }
private static bool AdjustSchemaRelations(DataSet targetSet, DataSet sourceSet, MissingSchemaAction missingSchemaAction) { if (missingSchemaAction == MissingSchemaAction.Ignore) { return(true); } foreach (DataTable sourceTable in sourceSet.Tables) { DataTable targetTable = targetSet.Tables[sourceTable.TableName]; if (targetTable == null) { continue; } foreach (Constraint constraint in sourceTable.Constraints) { Constraint targetConstraint = null; string constraintName = constraint.ConstraintName; if (targetTable.Constraints.Contains(constraintName)) { constraintName = ""; } UniqueConstraint uc = constraint as UniqueConstraint; // PrimaryKey is already taken care of while merging the table // ForeignKey constraint takes care of Parent Unique Constraints if (uc != null) { if (uc.IsPrimaryKey || uc.ChildConstraint != null) { continue; } DataColumn[] columns = ResolveColumns(targetTable, uc.Columns); targetConstraint = new UniqueConstraint(constraintName, columns, false); } ForeignKeyConstraint fc = constraint as ForeignKeyConstraint; if (fc != null) { DataColumn[] columns = ResolveColumns(targetTable, fc.Columns); DataColumn[] relatedColumns = ResolveColumns(targetSet.Tables [fc.RelatedTable.TableName], fc.RelatedColumns); targetConstraint = new ForeignKeyConstraint(constraintName, relatedColumns, columns); } bool dupConstraintFound = false; foreach (Constraint cons in targetTable.Constraints) { if (!targetConstraint.Equals(cons)) { continue; } dupConstraintFound = true; break; } // If equivalent-constraint already exists, then just do nothing if (dupConstraintFound) { continue; } if (missingSchemaAction == MissingSchemaAction.Error) { throw new DataException("Target DataSet missing " + targetConstraint.GetType() + targetConstraint.ConstraintName); } else { targetTable.Constraints.Add(targetConstraint); } } } foreach (DataRelation relation in sourceSet.Relations) { DataRelation targetRelation = targetSet.Relations [relation.RelationName]; if (targetRelation == null) { if (missingSchemaAction == MissingSchemaAction.Error) { throw new ArgumentException("Target DataSet mising definition for " + relation.RelationName); } DataColumn[] parentColumns = ResolveColumns(targetSet.Tables [relation.ParentTable.TableName], relation.ParentColumns); DataColumn[] childColumns = ResolveColumns(targetSet.Tables [relation.ChildTable.TableName], relation.ChildColumns); targetRelation = targetSet.Relations.Add(relation.RelationName, parentColumns, childColumns, relation.createConstraints); targetRelation.Nested = relation.Nested; } else if (!CompareColumnArrays(relation.ParentColumns, targetRelation.ParentColumns) || !CompareColumnArrays(relation.ChildColumns, targetRelation.ChildColumns)) { RaiseMergeFailedEvent(null, "Relation " + relation.RelationName + " cannot be merged, because keys have mismatch columns."); } } return(true); }
/// <summary> /// Adds to the list /// </summary> /// <param name="relation">The relation to check.</param> protected virtual void AddCore(DataRelation relation) { if (relation == null) { //TODO: Issue a good exception message. throw new ArgumentNullException(); } if (List.IndexOf(relation) != -1) { //TODO: Issue a good exception message. throw new ArgumentException(); } // check if the collection has a relation with the same name. int tmp = IndexOf(relation.RelationName); // if we found a relation with same name we have to check // that it is the same case. // indexof can return a table with different case letters. if (tmp != -1 && relation.RelationName == this [tmp].RelationName) { throw new DuplicateNameException("A DataRelation named '" + relation.RelationName + "' already belongs to this DataSet."); } // check whether the relation exists between the columns already foreach (DataRelation rel in this) { // compare child columns bool differs = false; foreach (DataColumn current in relation.ChildColumns) { bool exists = false; foreach (DataColumn col in rel.ChildColumns) { if (col == current) { exists = true; break; } } if (!exists) { differs = true; break; } } if (!differs) { // compare parent columns differs = false; foreach (DataColumn current in relation.ParentColumns) { bool exists = false; foreach (DataColumn col in rel.ParentColumns) { if (col == current) { exists = true; break; } } if (!exists) { differs = true; break; } } if (!differs) { throw new ArgumentException("A relation already exists for these child columns"); } } } // Add to collection List.Add(relation); }
public Form1() { InitializeComponent(); //set DB connection connectionString = @"data source = SEANLENOVO\SQLEXPRESS; initial catalog = Northwind; Integrated Security = true"; // PR-COMMONS-10\\SQLEXPRESS SqlConnection cn = new SqlConnection(connectionString); //create dataset ds = new DataSet("CustOrders"); //fill the dataset with customers, map default Table "Table" to "Customers" SqlDataAdapter da1 = new SqlDataAdapter("SELECT * from Customers;", cn); da1.TableMappings.Add("Table", "Customers"); da1.Fill(ds); //fill dataset with Orders, map default Table "table" to "Orders" SqlDataAdapter da2 = new SqlDataAdapter("SELECT * from Orders;", cn); da2.TableMappings.Add("Table", "Orders"); da2.Fill(ds); //fill dataset with [Order Details], map default Table "Table" to "OrderDetails" SqlDataAdapter da3 = new SqlDataAdapter("SELECT * from [Order Details];", cn); da3.TableMappings.Add("Table", "OrderDetails"); da3.Fill(ds); //show created Table names within the Dataset string myMessage = "Table Mappings: "; for (int i = 0; i < ds.Tables.Count; i++) { myMessage += i.ToString() + " " + ds.Tables[i].ToString() + ""; } //establish the relationship "RelCustOrd" between Customers ----- < Orders System.Data.DataRelation relCustOrd; System.Data.DataColumn colMaster1; System.Data.DataColumn colDetail1; colMaster1 = ds.Tables["Customers"].Columns["CustomerID"]; colDetail1 = ds.Tables["Orders"].Columns["CustomerID"]; relCustOrd = new System.Data.DataRelation("RelCustOrd", colMaster1, colDetail1); ds.Relations.Add(relCustOrd); //establish the relationship "RelOrddet" between Orders ----- < [Order details] System.Data.DataRelation relOrdDet; System.Data.DataColumn colMaster2; System.Data.DataColumn colDetail2; colMaster2 = ds.Tables["Orders"].Columns["OrderID"]; colDetail2 = ds.Tables["OrderDetails"].Columns["OrderID"]; relOrdDet = new System.Data.DataRelation("RelOrdDet", colMaster2, colDetail2); ds.Relations.Add(relOrdDet); //show created relations within the dataset myMessage = "relation Mapping: "; for (int i = 0; i < ds.Relations.Count; i++) { myMessage += i.ToString() + " " + ds.Relations[i].ToString() + " "; } listMessages.Text = myMessage; //the dataGridView returned by the DefaultViewManager property allows you to create custom settings for each DataTable in the dataset dsView = ds.DefaultViewManager; //grid databinding GridOrder.DataSource = dsView; GridOrder.DataMember = "Customers.RelCustOrd"; //grid databinding gridOrderDetails.DataSource = dsView; gridOrderDetails.DataMember = "Customers.RelCustOrd.RelOrdDet"; //combobox databinding comboName.DataSource = dsView; comboName.DisplayMember = "Customers.CompanyName"; comboName.ValueMember = "Customers.CustomerID"; //text column's databinding txtContact.DataBindings.Add("Text", dsView, "Customers.ContactName"); txtPhone.DataBindings.Add("Text", dsView, "Customers.Phone"); txtFax.DataBindings.Add("Text", dsView, "Customers.Fax"); }