/// <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");
}