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.Equal("CustOrder", drcol[0].RelationName);
drcol.Clear();
drcol.Add(parentCol, childCol);
Assert.Equal(1, drcol.Count);
drcol.Clear();
drcol.Add("NewRelation", parentCol, childCol);
Assert.Equal("NewRelation", drcol[0].RelationName);
drcol.Clear();
drcol.Add("NewRelation", parentCol, childCol, false);
Assert.Equal(1, drcol.Count);
drcol.Clear();
drcol.Add("NewRelation", parentCol, childCol, true);
Assert.Equal(1, drcol.Count);
drcol.Clear();
}
public void ChildTable()
{
var ds = new DataSet();
DataTable dtChild = DataProvider.CreateChildDataTable();
DataTable dtParent = 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);
// ChildTable
Assert.Equal(dtChild, dRel.ChildTable);
}
public void AddRange()
{
DataSet ds = getDataSet();
DataRelation[] relArray = new DataRelation[2];
relArray[0] = new DataRelation("rel1", ds.Tables[0].Columns["ParentId"], ds.Tables[1].Columns["ParentId"]);
relArray[1] = new DataRelation("rel2", ds.Tables[0].Columns["String1"], ds.Tables[1].Columns["String1"]);
ds.Relations.AddRange(relArray);
Assert.Equal(2, ds.Relations.Count);
Assert.Equal("rel1", ds.Relations[0].RelationName);
Assert.Equal("rel2", ds.Relations[1].RelationName);
ds.Relations.AddRange(null);
}
public void CopyTo()
{
DataSet ds = getDataSet();
DataRelation[] dataRelArray = new DataRelation[2];
ds.Relations.Add(new DataRelation("rel1", ds.Tables[0].Columns["ParentId"], ds.Tables[1].Columns["ParentId"]));
ds.Relations.CopyTo(dataRelArray, 1);
Assert.Equal("rel1", dataRelArray[1].RelationName);
ds.Relations.CopyTo(dataRelArray, 0);
Assert.Equal("rel1", dataRelArray[0].RelationName);
}
public void relationName()
{
var ds = new DataSet();
DataTable dtChild = DataProvider.CreateChildDataTable();
DataTable dtParent = DataProvider.CreateParentDataTable();
ds.Tables.Add(dtParent);
ds.Tables.Add(dtChild);
DataRelation dRel;
dRel = new DataRelation(null, dtParent.Columns[0], dtChild.Columns[0]);
// RelationName default 1
Assert.Equal(string.Empty, dRel.RelationName);
ds.Relations.Add(dRel);
// RelationName default 2
Assert.Equal("Relation1", dRel.RelationName);
dRel.RelationName = "myRelation";
// RelationName get/set
Assert.Equal("myRelation", dRel.RelationName);
}
public void ctor_ByNameDataColumnsArrays()
{
var ds = new DataSet();
DataTable dtChild = DataProvider.CreateChildDataTable();
DataTable dtParent = DataProvider.CreateParentDataTable();
ds.Tables.Add(dtParent);
ds.Tables.Add(dtChild);
DataRelation dRel;
//check some exception
// DataRelation - CTor ArgumentException, two columns child
Assert.Throws<ArgumentException>(() =>
{
dRel = new DataRelation("MyRelation", new DataColumn[] { dtParent.Columns[0] }, new DataColumn[] { dtChild.Columns[0], dtChild.Columns[2] });
});
dRel = new DataRelation("MyRelation", new DataColumn[] { dtParent.Columns[0], dtParent.Columns[1] }, new DataColumn[] { dtChild.Columns[0], dtChild.Columns[2] });
// DataRelation - Add Relation ArgumentException, fail on creating child Constraints
Assert.Throws<ArgumentException>(() =>
{
ds.Relations.Add(dRel);
});
// DataRelation ArgumentException - parent Constraints
Assert.Equal(1, dtParent.Constraints.Count);
// DataRelation ArgumentException - child Constraints
Assert.Equal(0, dtChild.Constraints.Count);
// DataRelation ArgumentException - DataSet.Relation count
Assert.Equal(1, ds.Relations.Count);
//begin to check the relation ctor
dtParent.Constraints.Clear();
dtChild.Constraints.Clear();
ds.Relations.Clear();
dRel = new DataRelation("MyRelation", new DataColumn[] { dtParent.Columns[0] }, new DataColumn[] { dtChild.Columns[0] });
ds.Relations.Add(dRel);
// DataSet DataRelation count
Assert.Equal(1, ds.Relations.Count);
// DataRelation - CTor
Assert.Equal(false, dRel == null);
// DataRelation - parent Constraints
Assert.Equal(1, dtParent.Constraints.Count);
// DataRelation - child Constraints
Assert.Equal(1, dtChild.Constraints.Count);
// DataRelation - child relations
Assert.Equal(dRel, dtParent.ChildRelations[0]);
// DataRelation - parent relations
Assert.Equal(dRel, dtChild.ParentRelations[0]);
// DataRelation - name
Assert.Equal("MyRelation", dRel.RelationName);
}
public void ContainsSchema()
{
var ds = new DataSet();
DataTable dt1 = new DataTable();
ds.Tables.Add(dt1);
DataColumn dc1 = new DataColumn("Col1");
dt1.Columns.Add(dc1);
dt1.Rows.Add(new string[] { "aaa" });
DataTable dt2 = new DataTable();
ds.Tables.Add(dt2);
DataColumn dc2 = new DataColumn("Col2");
dt2.Columns.Add(dc2);
dt2.Rows.Add(new string[] { "bbb" });
DataRelation rel = new DataRelation("Rel1", dc1, dc2, false);
ds.Relations.Add(rel);
StringWriter sw = new StringWriter();
ds.WriteXml(sw, XmlWriteMode.WriteSchema);
ds = new DataSet();
ds.ReadXml(new StringReader(sw.ToString()));
sw = new StringWriter();
ds.WriteXml(sw);
XmlDocument doc = new XmlDocument();
doc.LoadXml(sw.ToString());
Assert.Equal(2, doc.DocumentElement.ChildNodes.Count);
}
public void CreateChildView_ByDataRelation()
{
//create a dataset with two tables, with a DataRelation between them
DataTable dtParent = DataProvider.CreateParentDataTable();
DataTable dtChild = DataProvider.CreateChildDataTable();
var 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);
// ChildView != null
Assert.Equal(true, dvTmp1 != null);
// Child view table = ChildTable
Assert.Equal(dtChild, dvTmp1.Table);
// ChildView1.Table = ChildView2.Table
Assert.Equal(dvTmp2.Table, dvTmp1.Table);
//the child dataview are different
// Child DataViews different
Assert.Equal(false, dvTmp1.Equals(dvTmp2));
}
public void XmlTest16()
{
DataSet ds = new DataSet();
DataTable parent = new DataTable("Parent");
parent.Columns.Add(new DataColumn("col1", typeof(int)));
parent.Columns.Add(new DataColumn("col2", typeof(string)));
parent.Columns[0].Unique = true;
DataTable child1 = new DataTable("Child1");
child1.Columns.Add(new DataColumn("col3", typeof(int)));
child1.Columns.Add(new DataColumn("col4", typeof(string)));
child1.Columns.Add(new DataColumn("col5", typeof(int)));
child1.Columns[2].Unique = true;
DataTable child2 = new DataTable("Child2");
child2.Columns.Add(new DataColumn("col6", typeof(int)));
child2.Columns.Add(new DataColumn("col7"));
parent.Rows.Add(new object[] { 1, "P_" });
parent.Rows.Add(new object[] { 2, "P_" });
child1.Rows.Add(new object[] { 1, "C1_", 3 });
child1.Rows.Add(new object[] { 1, "C1_", 4 });
child1.Rows.Add(new object[] { 2, "C1_", 5 });
child1.Rows.Add(new object[] { 2, "C1_", 6 });
child2.Rows.Add(new object[] { 3, "C2_" });
child2.Rows.Add(new object[] { 3, "C2_" });
child2.Rows.Add(new object[] { 4, "C2_" });
child2.Rows.Add(new object[] { 4, "C2_" });
child2.Rows.Add(new object[] { 5, "C2_" });
child2.Rows.Add(new object[] { 5, "C2_" });
child2.Rows.Add(new object[] { 6, "C2_" });
child2.Rows.Add(new object[] { 6, "C2_" });
ds.Tables.Add(parent);
ds.Tables.Add(child1);
ds.Tables.Add(child2);
DataRelation relation = new DataRelation("Relation1", parent.Columns[0], child1.Columns[0]);
parent.ChildRelations.Add(relation);
relation = new DataRelation("Relation2", child1.Columns[2], child2.Columns[0]);
child1.ChildRelations.Add(relation);
using (FileStream stream = new FileStream(_tempFile, FileMode.Create))
{
WriteXmlSerializable(stream, parent);
}
DataTable table = new DataTable();
ReadXmlSerializable(_tempFile, table);
Assert.Equal("Parent", table.TableName);
Assert.Equal(2, table.Columns.Count);
Assert.Equal(2, table.Rows.Count);
Assert.Equal(typeof(int), table.Columns[0].DataType);
Assert.Equal(typeof(string), table.Columns[1].DataType);
Assert.Equal(1, table.Constraints.Count);
Assert.Equal(typeof(UniqueConstraint), table.Constraints[0].GetType());
}
// build the FROM clause
string BuildFromClause()
{
// build list of tables in query
var tables = new List <DataTable>();
foreach (QueryField field in QueryFields)
{
string tableName = field.Table;
DataTable table = _schema.Tables[tableName];
if (table != null && !tables.Contains(table))
{
tables.Add(table);
}
}
// save table count so caller can check this
_tableCount = tables.Count;
// build list of joined tables so each table is related to the next one
var qTables = new List <DataTable>();
bool done = false;
while (qTables.Count < tables.Count && !done)
{
done = true;
foreach (DataTable dt in tables)
{
bool inserted = InsertRelatedTable(dt, qTables);
if (inserted)
{
done = false;
}
}
}
// build join list
var qJoins = new List <string>();
for (int index = 0; index < qTables.Count - 1; index++)
{
// get relation
DataTable dt1 = (DataTable)qTables[index];
DataTable dt2 = (DataTable)qTables[index + 1];
DataRelation dr = GetRelation(dt1, dt2);
// build join statement
qJoins.Add(string.Format("{0}.{1} = {2}.{3}",
OleDbSchema.GetFullTableName(dr.ParentTable),
dr.ParentColumns[0].ColumnName,
OleDbSchema.GetFullTableName(dr.ChildTable),
dr.ChildColumns[0].ColumnName));
}
// build from statement
StringBuilder sb = new StringBuilder();
for (int i = 0; i < qTables.Count - 1; i++)
{
DataTable dt = qTables[i] as DataTable;
if (sb.Length > 0)
{
sb.Append("\r\n\t");
}
sb.AppendFormat("({0} INNER JOIN", OleDbSchema.GetFullTableName(dt));
}
sb.AppendFormat(" {0}", OleDbSchema.GetFullTableName(qTables[qTables.Count - 1]));
for (int i = qJoins.Count - 1; i >= 0; i--)
{
string join = qJoins[i] as string;
sb.AppendFormat("\r\n\tON {0})", join);
}
// not all tables joined? probably not what the user wants...
_missingJoins = qTables.Count < tables.Count;
// add tables that couldn't be joined
if (_missingJoins)
{
foreach (DataTable dt in tables)
{
if (!qTables.Contains(dt))
{
sb.AppendFormat(", {0}", OleDbSchema.GetFullTableName(dt));
qTables.Add(dt);
}
}
}
// done
return(sb.ToString());
}
private void btnRun_Click(object sender, EventArgs e)
{
DataSet Ds = new DataSet();
if (RptType == "SALREGDEF")
{
int tPay = 0;
if (!int.TryParse(txtPayPeriod.Text.Trim(), out tPay))
{
return;
}
else
{
if (tPay <= 0)
{
return;
}
}
this.Cursor = Cursors.WaitCursor;
string tContCode = txtContCode.Text.ToString().Trim();
if (txtContCode.Text.ToString().Trim() != "")
{
tContCode = txtContCode.Text.Trim();
}
if (chkBreak.Checked == true)
{
var HeaderTBL = new Reports.DS_rptMthlySalDT.sp_Cont_MthlySalTPARegisterDataTable();
var DetailTBL = new Reports.DS_rptMthlySalDT.sp_Cont_MthlySalDTDataTable();
var HeaderTa = new Reports.DS_rptMthlySalDTTableAdapters.sp_Cont_MthlySalTPARegisterTableAdapter();
sp_Cont_MthlySalDTTableAdapter DetailTa = new Reports.DS_rptMthlySalDTTableAdapters.sp_Cont_MthlySalDTTableAdapter();
HeaderTa.Connection.ConnectionString = Utils.Helper.constr;
HeaderTa.ClearBeforeFill = true;
DetailTa.Connection.ConnectionString = Utils.Helper.constr;
DetailTa.ClearBeforeFill = true;
DetailTa.SelectCommandTimeout = 0;
//HeaderTBL.Constraints.Clear();
HeaderTBL = HeaderTa.GetData(tPay, tContCode);
DetailTBL = DetailTa.GetData(tPay, tContCode);
Ds.Tables.Add(HeaderTBL);
Ds.Tables.Add(DetailTBL);
DataRelation newRelation = new DataRelation("sp_Cont_MthlySalTPARegister_sp_Cont_MthlySalDT",
new DataColumn[] { HeaderTBL.Columns["PayPeriod"], HeaderTBL.Columns["EmpUnqID"] },
new DataColumn[] { DetailTBL.Columns["PayPeriod"], DetailTBL.Columns["EmpUnqID"] }
);
Ds.Relations.Add(newRelation);
}
else
{
var HeaderTBL = new Reports.DS_rptMthlySalReg.sp_Cont_MthlySalTPARegisterDataTable();
var HeaderTa = new Reports.DS_rptMthlySalRegTableAdapters.sp_Cont_MthlySalTPARegisterTableAdapter();
HeaderTa.Connection.ConnectionString = Utils.Helper.constr;
HeaderTBL = HeaderTa.GetData(tPay, tContCode);
Ds.Tables.Add(HeaderTBL);
}
bool hasRows = Ds.Tables.Cast <DataTable>().Any(table => table.Rows.Count != 0);
if (hasRows)
{
if (chkBreak.Checked == true)
{
DevExpress.XtraReports.UI.XtraReport report = new Reports.rptMthlySAL();
report.DataSource = Ds;
this.Cursor = Cursors.Default;
report.ShowPreviewDialog();
}
else
{
DevExpress.XtraReports.UI.XtraReport report = new Reports.rptMthlySalRegDt();
report.DataSource = Ds;
this.Cursor = Cursors.Default;
report.ShowPreviewDialog();
}
}
}
else if (RptType == "TPAREGDEF")
{
int tPay = 0;
if (!int.TryParse(txtPayPeriod.Text.Trim(), out tPay))
{
return;
}
else
{
if (tPay <= 0)
{
this.Cursor = Cursors.Default;
return;
}
}
string tContCode = " ";
if (txtContCode.Text.Trim() != "")
{
tContCode = txtContCode.Text.Trim();
}
if (chkBreak.Checked == true)
{
var HeaderTBL = new Reports.DS_rptMthlyTPADT.sp_Cont_MthlySalTPARegisterDataTable();
var DetailTBL = new Reports.DS_rptMthlyTPADT.sp_Cont_MthlyTPADTDataTable();
var HeaderTa = new Reports.DS_rptMthlyTPADTTableAdapters.sp_Cont_MthlySalTPARegisterTableAdapter();
var DetailTa = new Reports.DS_rptMthlyTPADTTableAdapters.sp_Cont_MthlyTPADTTableAdapter();
HeaderTa.Connection.ConnectionString = Utils.Helper.constr;
HeaderTa.ClearBeforeFill = true;
DetailTa.Connection.ConnectionString = Utils.Helper.constr;
DetailTa.ClearBeforeFill = true;
DetailTa.SelectCommandTimeout = 0;
//HeaderTBL.Constraints.Clear();
HeaderTBL = HeaderTa.GetData(tPay, tContCode);
DetailTBL = DetailTa.GetData(tPay, tContCode);
Ds.Tables.Add(HeaderTBL);
Ds.Tables.Add(DetailTBL);
DataRelation newRelation = new DataRelation("sp_Cont_MthlySalTPARegister_sp_Cont_MthlyTPADT",
new DataColumn[] { HeaderTBL.Columns["PayPeriod"], HeaderTBL.Columns["EmpUnqID"] },
new DataColumn[] { DetailTBL.Columns["PayPeriod"], DetailTBL.Columns["EmpUnqID"] }
);
Ds.Relations.Add(newRelation);
}
else
{
var HeaderTBL = new Reports.DS_rptMthlySalReg.sp_Cont_MthlySalTPARegisterDataTable();
var HeaderTa = new Reports.DS_rptMthlySalRegTableAdapters.sp_Cont_MthlySalTPARegisterTableAdapter();
HeaderTa.Connection.ConnectionString = Utils.Helper.constr;
HeaderTBL = HeaderTa.GetData(tPay, tContCode);
Ds.Tables.Add(HeaderTBL);
}
bool hasRows = Ds.Tables.Cast <DataTable>().Any(table => table.Rows.Count != 0);
if (hasRows)
{
if (chkBreak.Checked == true)
{
DevExpress.XtraReports.UI.XtraReport report = new Reports.rptMthlyTPADt();
report.DataSource = Ds;
this.Cursor = Cursors.Default;
report.ShowPreviewDialog();
}
else
{
DevExpress.XtraReports.UI.XtraReport report = new Reports.rptMthlyTPAReg();
report.DataSource = Ds;
this.Cursor = Cursors.Default;
report.ShowPreviewDialog();
}
}
}
this.Cursor = Cursors.Default;
}
public void ToGraph()
{
// Empty data set
var dataSet = new DataSet();
DataSetGraph graph = dataSet.ToGraph();
AssertEmptyGraph(graph);
Assert.AreSame(dataSet, graph.DataSet);
// Only tables data set
dataSet = new DataSet();
var ships = new DataTable("Ships");
var shipIdCol = new DataColumn("ShipID", typeof(int))
{
Unique = true
};
ships.Columns.Add(shipIdCol);
dataSet.Tables.Add(ships);
var modules = new DataTable("Modules");
var moduleIdCol = new DataColumn("ModuleID", typeof(int))
{
Unique = true
};
modules.Columns.Add(moduleIdCol);
dataSet.Tables.Add(modules);
graph = dataSet.ToGraph();
AssertHasVertices(graph, new[] { ships, modules });
AssertNoEdge(graph);
Assert.AreSame(dataSet, graph.DataSet);
// Table with relations
dataSet = new DataSet();
var computers = new DataTable("Computers");
var computerIdCol = new DataColumn("ComputerID", typeof(int))
{
Unique = true
};
var computerBrandCol = new DataColumn("Brand", typeof(string));
var computerWinVerCol = new DataColumn("WindowsVersion", typeof(string));
computers.Columns.Add(computerIdCol);
computers.Columns.Add(computerBrandCol);
computers.Columns.Add(computerWinVerCol);
dataSet.Tables.Add(computers);
var users = new DataTable("Users");
var userIdCol = new DataColumn("UserID", typeof(int))
{
Unique = true
};
var userNameCol = new DataColumn("Name", typeof(string));
users.Columns.Add(userIdCol);
users.Columns.Add(userNameCol);
dataSet.Tables.Add(users);
var printers = new DataTable("Printers");
var printerIdCol = new DataColumn("PrinterID", typeof(int))
{
Unique = true
};
var printerBrandCol = new DataColumn("Brand", typeof(string));
printers.Columns.Add(printerIdCol);
printers.Columns.Add(printerBrandCol);
dataSet.Tables.Add(printers);
var phones = new DataTable("Phones");
var phoneIdCol = new DataColumn("PhoneID", typeof(int))
{
Unique = true
};
var phoneMacAddrCol = new DataColumn("MacAddress", typeof(string))
{
Unique = true
};
var phoneNumberCol = new DataColumn("Number", typeof(string));
phones.Columns.Add(phoneIdCol);
phones.Columns.Add(phoneMacAddrCol);
phones.Columns.Add(phoneNumberCol);
dataSet.Tables.Add(phones);
var use = new DataRelation("Use", userIdCol, computerIdCol);
dataSet.Relations.Add(use);
var connectedTo = new DataRelation("ConnectedTo", computerIdCol, printerIdCol);
dataSet.Relations.Add(connectedTo);
var phoneWith = new DataRelation("phoneWith", userIdCol, phoneIdCol);
dataSet.Relations.Add(phoneWith);
graph = dataSet.ToGraph();
AssertHasVertices(graph, new[] { computers, users, printers, phones });
AssertHasRelations(
graph,
new[]
{
new DataRelationEdge(use),
new DataRelationEdge(connectedTo),
new DataRelationEdge(phoneWith)
});
Assert.AreSame(dataSet, graph.DataSet);
}
private static void CreateCustomersAndOrdersTables(DataSet ds)
{
DataTable customers = new DataTable("Customers");
customers.Columns.Add("CustomerID", typeof(string));
customers.Columns.Add("CompanyName", typeof(string));
customers.Columns.Add("Address", typeof(string));
customers.Columns.Add("City", typeof(string));
customers.Columns.Add("Region", typeof(string));
customers.Columns.Add("PostalCode", typeof(string));
customers.Columns.Add("Country", typeof(string));
customers.Columns.Add("Phone", typeof(string));
customers.Columns.Add("Fax", typeof(string));
ds.Tables.Add(customers);
DataTable orders = new DataTable("Orders");
orders.Columns.Add("OrderID", typeof(int));
orders.Columns.Add("CustomerID", typeof(string));
orders.Columns.Add("OrderDate", typeof(DateTime));
orders.Columns.Add("Total", typeof(decimal));
ds.Tables.Add(orders);
DataRelation co = new DataRelation("CustomersOrders", customers.Columns["CustomerID"], orders.Columns["CustomerID"], true);
ds.Relations.Add(co);
var customerList = (
from e in XDocument.Load("customers.xml").
Root.Elements("customer")
select new Customer
{
CustomerID = (string)e.Element("id"),
CompanyName = (string)e.Element("name"),
Address = (string)e.Element("address"),
City = (string)e.Element("city"),
Region = (string)e.Element("region"),
PostalCode = (string)e.Element("postalcode"),
Country = (string)e.Element("country"),
Phone = (string)e.Element("phone"),
Fax = (string)e.Element("fax"),
Orders = (
from o in e.Elements("orders").Elements("order")
select new Order
{
OrderID = (int)o.Element("id"),
OrderDate = (DateTime)o.Element("orderdate"),
Total = (decimal)o.Element("total")
})
.ToArray()
}
).ToList();
foreach (Customer cust in customerList)
{
customers.Rows.Add(new object[] { cust.CustomerID, cust.CompanyName, cust.Address, cust.City, cust.Region,
cust.PostalCode, cust.Country, cust.Phone, cust.Fax });
foreach (Order order in cust.Orders)
{
orders.Rows.Add(new object[] { order.OrderID, cust.CustomerID, order.OrderDate, order.Total });
}
}
}
/// <summary>
/// Gets a SyncSchema object created from the existing DataSet defined by the user
/// </summary>
/// <param name="tableNames">the names of the tables to be included in the schema</param>
/// <param name="missingTables">the names of the missing tables not found in dataset</param>
/// <returns>the SyncSchema object</returns>
protected virtual SyncSchema GetSchemaFromSchemaDataset(Collection <string> tableNames, out Collection <string> missingTables)
{
SyncSchema schema = new SyncSchema();
missingTables = new Collection <string>();
foreach (string tableName in tableNames)
{
if (!Schema.Tables.Contains(tableName))
{
missingTables.Add(tableName);
}
}
foreach (DataTable table in Schema.SchemaDataSet.Tables)
{
if (tableNames.Contains(table.TableName))
{
DataTable table2 = table.Copy();
schema.SchemaDataSet.Tables.Add(table2);
}
}
foreach (DataRelation relation in Schema.SchemaDataSet.Relations)
{
if (tableNames.Contains(relation.ParentTable.TableName) &&
tableNames.Contains(relation.ChildTable.TableName))
{
Collection <DataColumn> parentCollection = new Collection <DataColumn>();
foreach (DataColumn c in relation.ParentColumns)
{
parentCollection.Add(schema.SchemaDataSet.Tables[relation.ParentTable.TableName].Columns[c.ColumnName]);
}
Collection <DataColumn> childCollection = new Collection <DataColumn>();
foreach (DataColumn c in relation.ParentColumns)
{
childCollection.Add(schema.SchemaDataSet.Tables[relation.ChildTable.TableName].Columns[c.ColumnName]);
}
DataColumn[] parentArray = new DataColumn[parentCollection.Count];
DataColumn[] childArray = new DataColumn[childCollection.Count];
parentCollection.CopyTo(parentArray, 0);
childCollection.CopyTo(childArray, 0);
DataRelation relation2 = new DataRelation(relation.RelationName, parentArray, childArray);
schema.SchemaDataSet.Relations.Add(relation2);
}
}
if (missingTables.Count == 0)
{
missingTables = null;
}
//CODE FIX TO AVOID THE DREADED NOTORIOUS "TESTINTRANET" BUG
Connection.Open();
Connection.Close();
//END OF CODE FIX
return(schema);
}
/// <summary>
/// Initializes a new instance of the <see cref="DataRelationEdge"/> class.
/// </summary>
/// <param name="relation">Data relation.</param>
public DataRelationEdge([NotNull] DataRelation relation)
{
Relation = relation ?? throw new ArgumentNullException(nameof(relation));
}
public void Foreign()
{
DataRelation Relation = new DataRelation("Rel", Mom.Columns [1], Child.Columns [0]);
Set.Relations.Add(Relation);
DataRow Row = Mom.NewRow();
Row [0] = "Teresa";
Row [1] = "Jack";
Mom.Rows.Add(Row);
Row = Mom.NewRow();
Row [0] = "Teresa";
Row [1] = "Dick";
Mom.Rows.Add(Row);
Row = Mom.NewRow();
Row [0] = "Mary";
Row [1] = "Harry";
Row = Child.NewRow();
Row [0] = "Jack";
Row [1] = 16;
Child.Rows.Add(Row);
Row = Child.NewRow();
Row [0] = "Dick";
Row [1] = 56;
Child.Rows.Add(Row);
Assert.That(Child.Rows.Count, Is.EqualTo(2), "test#01");
Row = Mom.Rows [0];
Row.Delete();
Assert.That(Child.Rows.Count, Is.EqualTo(1), "test#02");
Row = Mom.NewRow();
Row [0] = "Teresa";
Row [1] = "Dick";
try {
Mom.Rows.Add(Row);
Assert.Fail("test#03");
} catch (Exception e) {
Assert.That(e, Is.TypeOf(typeof(ConstraintException)), "test#04");
// Never premise English.
//Assert.That (e.Message, Is.EqualTo("Column 'ChildName' is constrained to be unique. Value 'Dick' is already present."), "test#05");
}
Row = Mom.NewRow();
Row [0] = "Teresa";
Row [1] = "Mich";
Mom.Rows.Add(Row);
Assert.That(Child.Rows.Count, Is.EqualTo(1), "test#06");
Row = Child.NewRow();
Row [0] = "Jack";
Row [1] = 16;
try {
Child.Rows.Add(Row);
Assert.Fail("test#07");
} catch (Exception e) {
Assert.That(e, Is.TypeOf(typeof(InvalidConstraintException)), "test#08");
}
}
public void Item()
{
DataSet ds = getDataSet();
DataRelation rel1 = new DataRelation("rel1", ds.Tables[0].Columns["ParentId"], ds.Tables[1].Columns["ParentId"]);
DataRelation rel2 = new DataRelation("rel2", ds.Tables[0].Columns["String1"], ds.Tables[1].Columns["String1"]);
ds.Relations.Add(rel1);
ds.Relations.Add(rel2);
Assert.Equal("rel1", ds.Relations["rel1"].RelationName);
Assert.Equal("rel2", ds.Relations["rel2"].RelationName);
Assert.Equal("rel1", ds.Relations[0].RelationName);
Assert.Equal("rel2", ds.Relations[1].RelationName);
}
private void Form1_Load(object sender, EventArgs e)
{
string strConn = "Host=localhost; port=5433; Username=postgres; Password=8270; Database=gis";
conn = new NpgsqlConnection(strConn);
cmd = new NpgsqlCommand();
cmd.Connection = conn;
adapter = new NpgsqlDataAdapter();
adapter.SelectCommand = cmd;
dataSet = new DataSet();
cmd.CommandText = "select sid, sgg_nm from seoul_gu";
adapter.Fill(dataSet, "Gu");
tblGu = dataSet.Tables["Gu"];
cmd.CommandText = "select id, emd_nm, sid, area from seoul_dong";
adapter.Fill(dataSet, "Dong");
tblDong = dataSet.Tables["Dong"];
cmd.CommandText = "select id, name, address, capa, dong_id from dong_bicycle";
adapter.Fill(dataSet, "Bicycle");
tblBicycle = dataSet.Tables["Bicycle"];
// 관계 생성 파트
// 일 대 다 : 다(외래키)
DataRelation gu_dong = new DataRelation("gu_dong", tblGu.Columns["sid"], tblDong.Columns["sid"]);
dataSet.Relations.Add(gu_dong);
DataRelation dong_bicycle = new DataRelation("dong_bicycle", tblDong.Columns["id"], tblBicycle.Columns["dong_id"]);
dataSet.Relations.Add(dong_bicycle);
// Expression 컬럼 추가 파트
// 관계를 기반으로 집계함수 적용
DataColumn dongCount = new DataColumn();
dongCount.ColumnName = "count_dong";
dongCount.DataType = typeof(int);
dongCount.Expression = "Count(Child(gu_dong).id)";
tblGu.Columns.Add(dongCount);
DataColumn guArea = new DataColumn();
guArea.ColumnName = "area_gu";
guArea.DataType = typeof(double);
guArea.Expression = "Sum(Child(gu_dong).area)";
tblGu.Columns.Add(guArea);
DataColumn bicycleCount = new DataColumn();
bicycleCount.ColumnName = "count_bicycle";
bicycleCount.DataType = typeof(int);
bicycleCount.Expression = "Count(Child(dong_bicycle).id)";
tblDong.Columns.Add(bicycleCount);
DataColumn bicycleCapa = new DataColumn();
bicycleCapa.ColumnName = "sum_capa";
bicycleCapa.DataType = typeof(double);
bicycleCapa.Expression = "Sum(Child(dong_bicycle).capa)";
tblDong.Columns.Add(bicycleCapa);
// BindingSource 설정 (연속된 one-to-many)
bsGu = new BindingSource();
bsGu.DataSource = tblGu;
bsDong = new BindingSource();
bsDong.DataSource = bsGu;
bsDong.DataMember = "gu_dong";
bsBicycle = new BindingSource();
bsBicycle.DataSource = bsDong;
bsBicycle.DataMember = "dong_bicycle";
// 컨트롤과의 데이터 바인딩
// 콤보박스
cbGu.DataSource = bsGu;
cbGu.DisplayMember = "sgg_nm";
// 데이터그리드뷰
dgvDong.DataSource = bsDong;
dgvBicycle.DataSource = bsBicycle;
// 텍스트박스
tbCountDong.DataBindings.Add("Text", bsGu, "count_dong");
tbSumArea.DataBindings.Add("Text", bsGu, "area_gu");
tbCountBicycle.DataBindings.Add("Text", bsDong, "count_bicycle");
tbSumCapa.DataBindings.Add("Text", bsDong, "sum_capa");
}
public void Remove_String()
{
DataSet ds = getDataSet();
DataSet ds1 = getDataSet();
DataRelation rel1 = new DataRelation("rel1", ds.Tables[0].Columns["ParentId"], ds.Tables[1].Columns["ParentId"]);
DataRelation rel2 = new DataRelation("rel2", ds.Tables[0].Columns["String1"], ds.Tables[1].Columns["String1"]);
ds.Relations.Add(rel1);
ds.Relations.Add(rel2);
Assert.Equal(ds.Relations.Count, 2);
ds.Relations.CollectionChanged += new CollectionChangeEventHandler(Relations_CollectionChanged);
//Perform remove
ds.Relations.Remove("rel1");
Assert.Equal(1, ds.Relations.Count);
Assert.Equal("rel2", ds.Relations[0].RelationName);
ds.Relations.Remove("rel2");
Assert.Equal(0, ds.Relations.Count);
Assert.Equal(2, _changesCounter);
Assert.Throws<ArgumentException>(() =>
{
ds.Relations.Remove((string)null);
});
Assert.Throws<ArgumentException>(() =>
{
ds.Relations.Remove("rel3");
});
}
public void TestCreateElementAndRow()
{
DataSet ds = new DataSet("set");
DataTable dt = new DataTable("tab1");
dt.Columns.Add("col1");
dt.Columns.Add("col2");
ds.Tables.Add(dt);
DataTable dt2 = new DataTable("child");
dt2.Columns.Add("ref");
dt2.Columns.Add("val");
ds.Tables.Add(dt2);
DataRelation rel = new DataRelation("rel",
dt.Columns [0], dt2.Columns [0]);
rel.Nested = true;
ds.Relations.Add(rel);
XmlDataDocument doc = new XmlDataDocument(ds);
doc.LoadXml("<set><tab1><col1>1</col1><col2/><child><ref>1</ref><val>aaa</val></child></tab1></set>");
AssertEquals(1, ds.Tables [0].Rows.Count);
AssertEquals(1, ds.Tables [1].Rows.Count);
// document element - no mapped row
XmlElement el = doc.DocumentElement;
AssertNull(doc.GetRowFromElement(el));
// tab1 element - has mapped row
el = el.FirstChild as XmlElement;
DataRow row = doc.GetRowFromElement(el);
AssertNotNull(row);
AssertEquals(DataRowState.Added, row.RowState);
// col1 - it is column. no mapped row
el = el.FirstChild as XmlElement;
row = doc.GetRowFromElement(el);
AssertNull(row);
// col2 - it is column. np mapped row
el = el.NextSibling as XmlElement;
row = doc.GetRowFromElement(el);
AssertNull(row);
// child - has mapped row
el = el.NextSibling as XmlElement;
row = doc.GetRowFromElement(el);
AssertNotNull(row);
AssertEquals(DataRowState.Added, row.RowState);
// created (detached) table 1 element (used later)
el = doc.CreateElement("tab1");
row = doc.GetRowFromElement(el);
AssertEquals(DataRowState.Detached, row.RowState);
AssertEquals(1, dt.Rows.Count); // not added yet
// adding a node before setting EnforceConstraints
// raises an error
try {
doc.DocumentElement.AppendChild(el);
Fail("Invalid Operation should occur; EnforceConstraints prevents addition.");
} catch (InvalidOperationException) {
}
// try again...
ds.EnforceConstraints = false;
AssertEquals(1, dt.Rows.Count); // not added yet
doc.DocumentElement.AppendChild(el);
AssertEquals(2, dt.Rows.Count); // added
row = doc.GetRowFromElement(el);
AssertEquals(DataRowState.Added, row.RowState); // changed
// Irrelevant element
XmlElement el2 = doc.CreateElement("hoge");
row = doc.GetRowFromElement(el2);
AssertNull(row);
// created table 2 element (used later)
el = doc.CreateElement("child");
row = doc.GetRowFromElement(el);
AssertEquals(DataRowState.Detached, row.RowState);
// Adding it to irrelevant element performs no row state change.
AssertEquals(1, dt2.Rows.Count); // not added yet
el2.AppendChild(el);
AssertEquals(1, dt2.Rows.Count); // still not added
row = doc.GetRowFromElement(el);
AssertEquals(DataRowState.Detached, row.RowState); // still detached here
}
private void InitClass()
{
DataSetName = "myTypedDataSet";
Prefix = "";
Namespace = "http://www.tempuri.org/myTypedDataSet.xsd";
Locale = new CultureInfo("en-US");
CaseSensitive = false;
EnforceConstraints = true;
_tableOrder_Details = new Order_DetailsDataTable();
Tables.Add(_tableOrder_Details);
_tableOrders = new OrdersDataTable();
Tables.Add(_tableOrders);
ForeignKeyConstraint fkc;
fkc = new ForeignKeyConstraint("OrdersOrder_x0020_Details", new DataColumn[] {
_tableOrders.OrderIDColumn}, new DataColumn[] {
_tableOrder_Details.OrderIDColumn});
_tableOrder_Details.Constraints.Add(fkc);
fkc.AcceptRejectRule = AcceptRejectRule.None;
fkc.DeleteRule = Rule.Cascade;
fkc.UpdateRule = Rule.Cascade;
_relationOrdersOrder_x0020_Details = new DataRelation("OrdersOrder_x0020_Details", new DataColumn[] {
_tableOrders.OrderIDColumn}, new DataColumn[] {
_tableOrder_Details.OrderIDColumn}, false);
Relations.Add(_relationOrdersOrder_x0020_Details);
}
// Create a DataSet with two tables and populate it.
private void MakeDataSet()
{
// Create a DataSet.
ds = new DataSet("myDataSet");
// Create two DataTables.
DataTable tCust = new DataTable("Customers");
DataTable tOrders = new DataTable("Orders");
// Create two columns, and add them to the first table.
DataColumn cCustID = new DataColumn("CustID", typeof(int));
DataColumn cCustName = new DataColumn("CustName");
tCust.Columns.Add(cCustID);
tCust.Columns.Add(cCustName);
// Create three columns, and add them to the second table.
DataColumn cID =
new DataColumn("CustID", typeof(int));
DataColumn cOrderDate =
new DataColumn("orderDate", typeof(DateTime));
DataColumn cOrderAmount =
new DataColumn("OrderAmount", typeof(decimal));
tOrders.Columns.Add(cOrderAmount);
tOrders.Columns.Add(cID);
tOrders.Columns.Add(cOrderDate);
// Add the tables to the DataSet.
ds.Tables.Add(tCust);
ds.Tables.Add(tOrders);
// Create a DataRelation, and add it to the DataSet.
DataRelation dr = new DataRelation
("custToOrders", cCustID, cID);
ds.Relations.Add(dr);
/* Populate the tables. For each customer and order,
* create two DataRow variables. */
DataRow newRow1;
DataRow newRow2;
// Create three customers in the Customers Table.
for (int i = 1; i < 4; i++)
{
newRow1 = tCust.NewRow();
newRow1["custID"] = i;
// Add the row to the Customers table.
tCust.Rows.Add(newRow1);
}
// Give each customer a distinct name.
tCust.Rows[0]["custName"] = "Alpha";
tCust.Rows[1]["custName"] = "Beta";
tCust.Rows[2]["custName"] = "Omega";
// For each customer, create five rows in the Orders table.
for (int i = 1; i < 4; i++)
{
for (int j = 1; j < 6; j++)
{
newRow2 = tOrders.NewRow();
newRow2["CustID"] = i;
newRow2["orderDate"] = new DateTime(2001, i, j * 2);
newRow2["OrderAmount"] = i * 10 + j * .1;
// Add the row to the Orders table.
tOrders.Rows.Add(newRow2);
}
}
}
public void CloneCopy()
{
DataTable table = new DataTable("pTable");
DataTable table1 = new DataTable("cTable");
DataSet set = new DataSet();
set.Tables.Add(table);
set.Tables.Add(table1);
DataColumn col = new DataColumn();
col.ColumnName = "Id";
col.DataType = Type.GetType("System.Int32");
table.Columns.Add(col);
UniqueConstraint uc = new UniqueConstraint("UK1", table.Columns[0]);
table.Constraints.Add(uc);
col = new DataColumn();
col.ColumnName = "Name";
col.DataType = Type.GetType("System.String");
table.Columns.Add(col);
col = new DataColumn();
col.ColumnName = "Id";
col.DataType = Type.GetType("System.Int32");
table1.Columns.Add(col);
col = new DataColumn();
col.ColumnName = "Name";
col.DataType = Type.GetType("System.String");
table1.Columns.Add(col);
ForeignKeyConstraint fc = new ForeignKeyConstraint("FK1", table.Columns[0], table1.Columns[0]);
table1.Constraints.Add(fc);
DataRow row = table.NewRow();
row["Id"] = 147;
row["name"] = "Row1";
row.RowError = "Error#1";
table.Rows.Add(row);
// Set column to RO as commonly used by auto-increment fields.
// ds.Copy() has to omit the RO check when cloning DataRows
table.Columns["Id"].ReadOnly = true;
row = table1.NewRow();
row["Id"] = 147;
row["Name"] = "Row1";
table1.Rows.Add(row);
//Setting properties of DataSet
set.CaseSensitive = true;
set.DataSetName = "My DataSet";
set.EnforceConstraints = false;
set.Namespace = "Namespace#1";
set.Prefix = "Prefix:1";
DataRelation dr = new DataRelation("DR", table.Columns[0], table1.Columns[0]);
set.Relations.Add(dr);
set.ExtendedProperties.Add("TimeStamp", DateTime.Now);
CultureInfo cultureInfo = new CultureInfo("ar-SA");
set.Locale = cultureInfo;
//Testing Copy ()
DataSet copySet = set.Copy();
Assert.Equal(set.CaseSensitive, copySet.CaseSensitive);
Assert.Equal(set.DataSetName, copySet.DataSetName);
Assert.Equal(set.EnforceConstraints, copySet.EnforceConstraints);
Assert.Equal(set.HasErrors, copySet.HasErrors);
Assert.Equal(set.Namespace, copySet.Namespace);
Assert.Equal(set.Prefix, copySet.Prefix);
Assert.Equal(set.Relations.Count, copySet.Relations.Count);
Assert.Equal(set.Tables.Count, copySet.Tables.Count);
Assert.Equal(set.ExtendedProperties["TimeStamp"], copySet.ExtendedProperties["TimeStamp"]);
for (int i = 0; i < copySet.Tables.Count; i++)
{
Assert.Equal(set.Tables[i].Rows.Count, copySet.Tables[i].Rows.Count);
Assert.Equal(set.Tables[i].Columns.Count, copySet.Tables[i].Columns.Count);
}
//Testing Clone ()
copySet = set.Clone();
Assert.Equal(set.CaseSensitive, copySet.CaseSensitive);
Assert.Equal(set.DataSetName, copySet.DataSetName);
Assert.Equal(set.EnforceConstraints, copySet.EnforceConstraints);
Assert.False(copySet.HasErrors);
Assert.Equal(set.Namespace, copySet.Namespace);
Assert.Equal(set.Prefix, copySet.Prefix);
Assert.Equal(set.Relations.Count, copySet.Relations.Count);
Assert.Equal(set.Tables.Count, copySet.Tables.Count);
Assert.Equal(set.ExtendedProperties["TimeStamp"], copySet.ExtendedProperties["TimeStamp"]);
for (int i = 0; i < copySet.Tables.Count; i++)
{
Assert.Equal(0, copySet.Tables[i].Rows.Count);
Assert.Equal(set.Tables[i].Columns.Count, copySet.Tables[i].Columns.Count);
}
}
private void btnCreate_Click(object sender, EventArgs e)
{
try
{
// tabela Kupci
//dodavanje kolone KupacID
DataColumn KupacID = new DataColumn("KupacID");
KupacID.DataType = typeof(int);
KupacID.AutoIncrement = true;
KupacID.AutoIncrementSeed = 1;
KupacID.AutoIncrementStep = 1;
KupacID.AllowDBNull = false;
//PK
kupci.PrimaryKey = new DataColumn[] { kupci.Columns["KupacID"] };
kupci.Columns.Add(KupacID);
//dodavanje kolone NazivKupca
DataColumn NazivKupca = new DataColumn("NazivKupca");
NazivKupca.DataType = typeof(string);
NazivKupca.MaxLength = 50;
NazivKupca.AllowDBNull = false;
kupci.Columns.Add(NazivKupca);
//dodavanje kolone Adresa
DataColumn Adresa = new DataColumn("Adresa");
Adresa.DataType = typeof(string);
Adresa.MaxLength = 200;
Adresa.AllowDBNull = true;
kupci.Columns.Add(Adresa);
// tabela Fakture
//dodavanje kolone FakturaID
DataColumn FakturaID = new DataColumn("FakturaID");
FakturaID.DataType = typeof(int);
FakturaID.AutoIncrement = true;
FakturaID.AutoIncrementSeed = 1;
FakturaID.AutoIncrementStep = 1;
FakturaID.AllowDBNull = false;
//PK
fakture.PrimaryKey = new DataColumn[] { fakture.Columns["FakturaID"] };
fakture.Columns.Add(FakturaID);
//dodavanje kolone KupacID - spoljni kljuc
DataColumn KupacID_FK = new DataColumn("KupacID");
KupacID_FK.DataType = typeof(int);
KupacID_FK.AllowDBNull = false;
fakture.Columns.Add(KupacID_FK);
//dodavanje kolone Datum
DataColumn Datum = new DataColumn("Datum");
Datum.DataType = typeof(DateTime);
Datum.AllowDBNull = false;
Datum.DefaultValue = DateTime.Now;
fakture.Columns.Add(Datum);
//tabela FaktureStavke
//dodavanje kolone FakturaID - spoljni kljuc
DataColumn FakturaID_FK = new DataColumn("FakturaID");
FakturaID_FK.DataType = typeof(int);
FakturaID_FK.AllowDBNull = false;
faktureStavke.Columns.Add(FakturaID_FK);
//dodavanje kolone NazivStavke
DataColumn NazivStavke = new DataColumn("NazivStavke");
NazivStavke.DataType = typeof(string);
NazivStavke.MaxLength = 40;
NazivStavke.AllowDBNull = false;
faktureStavke.Columns.Add(NazivStavke);
//dodavanje kolone CenaStavke
DataColumn CenaStavke = new DataColumn("CenaStavke");
CenaStavke.DataType = typeof(decimal);
CenaStavke.AllowDBNull = false;
faktureStavke.Columns.Add(CenaStavke);
//PK
faktureStavke.PrimaryKey = new DataColumn[] { faktureStavke.Columns["FakturaID"], faktureStavke.Columns["NazivStavke"] };
// stavljanje tabela u DataSet
Fakturisanje = new DataSet("Fakturisanje");
Fakturisanje.Tables.Add(kupci);
Fakturisanje.Tables.Add(fakture);
Fakturisanje.Tables.Add(faktureStavke);
//pravljenje relacije kupciFakture
DataRelation kupciFakture = new DataRelation("RelacijaKupciFakture", kupci.Columns["KupacID"], fakture.Columns["KupacID"], true);
//pravljenje relacije FaktureStavke
DataRelation FaktureStavke = new DataRelation("RelacijaFaktureStavke", fakture.Columns["FakturaID"], faktureStavke.Columns["FakturaID"], true);
//dodavanje relacija
Fakturisanje.Relations.Add(kupciFakture);
Fakturisanje.Relations.Add(FaktureStavke);
ForeignKeyConstraint fk = (ForeignKeyConstraint)fakture.Constraints["RelacijaKupciFakture"];
fk.DeleteRule = Rule.None;
fk.UpdateRule = Rule.None;
ForeignKeyConstraint fk2 = (ForeignKeyConstraint)faktureStavke.Constraints["RelacijaFaktureStavke"];
fk2.DeleteRule = Rule.None;
fk2.UpdateRule = Rule.None;
////dodavanje redova u tabele
DataRow kupac1 = kupci.NewRow();
kupac1["NazivKupca"] = "Radovan Vukovic";
kupac1["Adresa"] = "Makedonska 6";
kupci.Rows.Add(kupac1);
DataRow kupac2 = kupci.NewRow();
kupac2["NazivKupca"] = "Mihajlo Pantic";
kupac2["Adresa"] = "Bele bartoka 13";
kupci.Rows.Add(kupac2);
DataRow kupac3 = kupci.NewRow();
kupac3["NazivKupca"] = "Ivan Milutinovic";
kupac3["Adresa"] = "Francuska 23";
kupci.Rows.Add(kupac3);
DataRow faktura1 = fakture.NewRow();
faktura1["KupacID"] = 1;
faktura1["Datum"] = "09/10/2020";
fakture.Rows.Add(faktura1);
DataRow faktura2 = fakture.NewRow();
faktura2["KupacID"] = 2;
faktura2["Datum"] = "23/12/2020";
fakture.Rows.Add(faktura2);
DataRow faktura3 = fakture.NewRow();
faktura3["KupacID"] = 3;
faktura3["Datum"] = "15/7/2020";
fakture.Rows.Add(faktura3);
DataRow stavka1 = faktureStavke.NewRow();
stavka1["FakturaID"] = 1;
stavka1["NazivStavke"] = "Laptop";
stavka1["CenaStavke"] = 60000;
faktureStavke.Rows.Add(stavka1);
DataRow stavka2 = faktureStavke.NewRow();
stavka2["FakturaID"] = 1;
stavka2["NazivStavke"] = "Tastatura";
stavka2["CenaStavke"] = 4000;
faktureStavke.Rows.Add(stavka2);
DataRow stavka3 = faktureStavke.NewRow();
stavka3["FakturaID"] = 2;
stavka3["NazivStavke"] = "Mis";
stavka3["CenaStavke"] = 3000;
faktureStavke.Rows.Add(stavka3);
DataRow stavka4 = faktureStavke.NewRow();
stavka4["FakturaID"] = 2;
stavka4["NazivStavke"] = "Monitor";
stavka4["CenaStavke"] = 20000;
faktureStavke.Rows.Add(stavka4);
DataRow stavka5 = faktureStavke.NewRow();
stavka5["FakturaID"] = 3;
stavka5["NazivStavke"] = "Telefon";
stavka5["CenaStavke"] = 35000;
faktureStavke.Rows.Add(stavka5);
DataRow stavka6 = faktureStavke.NewRow();
stavka6["FakturaID"] = 3;
stavka6["NazivStavke"] = "Slusalice";
stavka6["CenaStavke"] = 8000;
faktureStavke.Rows.Add(stavka6);
dgKupci.DataSource = kupci;
dgFakture.DataSource = fakture;
dgFaktureStavke.DataSource = faktureStavke;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
public void CanRemove()
{
DataTable Table = new DataTable("test_table");
DataTable Table2 = new DataTable("test_table_2");
DataColumnCollection Cols = Table.Columns;
DataColumn C = new DataColumn("test1");
Cols.Add();
// LAMESPEC: MSDN says that if C doesn't belong to Cols
// Exception is thrown.
Assert.False(Cols.CanRemove(C));
Cols.Add(C);
Assert.True(Cols.CanRemove(C));
C = new DataColumn();
C.Expression = "test1 + 2";
Cols.Add(C);
C = Cols["test2"];
Assert.False(Cols.CanRemove(C));
C = new DataColumn("t");
Table2.Columns.Add(C);
DataColumnCollection Cols2 = Table2.Columns;
Assert.True(Cols2.CanRemove(C));
DataSet Set = new DataSet();
Set.Tables.Add(Table);
Set.Tables.Add(Table2);
DataRelation Rel = new DataRelation("Rel", Table.Columns[0], Table2.Columns[0]);
Set.Relations.Add(Rel);
Assert.False(Cols2.CanRemove(C));
Assert.False(Cols.CanRemove(null));
}
public void ExpressionAggregates()
{
DataTable T = new DataTable("test");
DataTable T2 = new DataTable("test2");
DataColumn C = new DataColumn("name");
T.Columns.Add(C);
C = new DataColumn("age");
C.DataType = typeof(int);
T.Columns.Add(C);
C = new DataColumn("childname");
T.Columns.Add(C);
C = new DataColumn("expression");
T.Columns.Add(C);
DataSet Set = new DataSet("TestSet");
Set.Tables.Add(T);
Set.Tables.Add(T2);
DataRow Row = null;
for (int i = 0; i < 100; i++)
{
Row = T.NewRow();
Row[0] = "human" + i;
Row[1] = i;
Row[2] = "child" + i;
T.Rows.Add(Row);
}
Row = T.NewRow();
Row[0] = "h*an";
Row[1] = DBNull.Value;
T.Rows.Add(Row);
C = new DataColumn("name");
T2.Columns.Add(C);
C = new DataColumn("age");
C.DataType = typeof(int);
T2.Columns.Add(C);
for (int i = 0; i < 100; i++)
{
Row = T2.NewRow();
Row[0] = "child" + i;
Row[1] = i;
T2.Rows.Add(Row);
Row = T2.NewRow();
Row[0] = "child" + i;
Row[1] = i - 2;
T2.Rows.Add(Row);
}
DataRelation Rel = new DataRelation("Rel", T.Columns[2], T2.Columns[0]);
Set.Relations.Add(Rel);
C = T.Columns[3];
C.Expression = "Sum (Child.age)";
Assert.Equal("-2", T.Rows[0][3]);
Assert.Equal("98", T.Rows[50][3]);
C.Expression = "Count (Child.age)";
Assert.Equal("2", T.Rows[0][3]);
Assert.Equal("2", T.Rows[60][3]);
C.Expression = "Avg (Child.age)";
Assert.Equal("-1", T.Rows[0][3]);
Assert.Equal("59", T.Rows[60][3]);
C.Expression = "Min (Child.age)";
Assert.Equal("-2", T.Rows[0][3]);
Assert.Equal("58", T.Rows[60][3]);
C.Expression = "Max (Child.age)";
Assert.Equal("0", T.Rows[0][3]);
Assert.Equal("60", T.Rows[60][3]);
C.Expression = "stdev (Child.age)";
Assert.Equal((1.4142135623730951).ToString(T.Locale), T.Rows[0][3]);
Assert.Equal((1.4142135623730951).ToString(T.Locale), T.Rows[60][3]);
C.Expression = "var (Child.age)";
Assert.Equal("2", T.Rows[0][3]);
Assert.Equal("2", T.Rows[60][3]);
}
public new void ToString()
{
var ds = new DataSet();
DataTable dtChild = DataProvider.CreateChildDataTable();
DataTable dtParent = DataProvider.CreateParentDataTable();
ds.Tables.Add(dtParent);
ds.Tables.Add(dtChild);
DataRelation dRel;
dRel = new DataRelation(null, dtParent.Columns[0], dtChild.Columns[0]);
// ToString 1
Assert.Equal(string.Empty, dRel.ToString());
ds.Relations.Add(dRel);
// ToString 2
Assert.Equal("Relation1", dRel.ToString());
dRel.RelationName = "myRelation";
// ToString 3
Assert.Equal("myRelation", dRel.ToString());
}
public void Aggregation_TestForSyntaxErrors()
{
string error = "Aggregation functions cannot be called on Singular(Parent) Columns";
DataSet ds = new DataSet();
DataTable table1 = new DataTable();
DataTable table2 = new DataTable();
DataTable table3 = new DataTable();
table1.Columns.Add("test", typeof(int));
table2.Columns.Add("test", typeof(int));
table3.Columns.Add("test", typeof(int));
ds.Tables.Add(table1);
ds.Tables.Add(table2);
ds.Tables.Add(table3);
DataRelation rel1 = new DataRelation("rel1", table1.Columns[0], table2.Columns[0]);
DataRelation rel2 = new DataRelation("rel2", table2.Columns[0], table3.Columns[0]);
ds.Relations.Add(rel1);
ds.Relations.Add(rel2);
error = "Aggregation Functions cannot be called on Columns Returning Single Row (Parent Column)";
try
{
table2.Columns.Add("result", typeof(int), "count(parent.test)");
Assert.False(true);
}
catch (SyntaxErrorException)
{
}
error = "Numerical or Functions cannot be called on Columns Returning Multiple Rows (Child Column)";
// Check arithematic operator
try
{
table2.Columns.Add("result", typeof(int), "10*(child.test)");
Assert.False(true);
}
catch (SyntaxErrorException)
{
}
// Check rel operator
try
{
table2.Columns.Add("result", typeof(int), "(child.test) > 10");
Assert.False(true);
}
catch (SyntaxErrorException)
{
}
// Check predicates
try
{
table2.Columns.Add("result", typeof(int), "(child.test) IN (1,2,3)");
Assert.False(true);
}
catch (SyntaxErrorException)
{
}
try
{
table2.Columns.Add("result", typeof(int), "(child.test) LIKE 1");
Assert.False(true);
}
catch (SyntaxErrorException)
{
}
try
{
table2.Columns.Add("result", typeof(int), "(child.test) IS null");
Assert.False(true);
}
catch (SyntaxErrorException)
{
}
// Check Calc Functions
try
{
table2.Columns.Add("result", typeof(int), "isnull(child.test,10)");
Assert.False(true);
}
catch (SyntaxErrorException)
{
}
}
public void extendedProperties()
{
var ds = new DataSet();
DataTable dtChild = DataProvider.CreateChildDataTable();
DataTable dtParent = 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;
// Checking ExtendedProperties default
Assert.Equal(true, pc != null);
// Checking ExtendedProperties count
Assert.Equal(0, pc.Count);
}
// This method is modelled after the DataSet's WriteXml functionality.
internal static void WriteTables(XmlWriter writer,
XmlWriteMode mode,
List <DataTable> tables,
List <DataRelation> relations,
string mainDataTable,
string dataSetName)
{
if (mode == XmlWriteMode.DiffGram)
{
foreach (DataTable table in tables)
{
table.SetRowsID();
}
DataSet.WriteDiffGramElement(writer);
}
bool shouldOutputContent = (mode != XmlWriteMode.DiffGram);
for (int n = 0; n < tables.Count && !shouldOutputContent; n++)
{
shouldOutputContent = tables[n].Rows.Count > 0;
}
if (shouldOutputContent)
{
// We assume that tables[0] is the main table being written.
// We happen to know that the code above us does things that way.
DataSet.WriteStartElement(writer, mode, tables[0].Namespace, tables[0].Prefix, XmlHelper.Encode(dataSetName));
if (mode == XmlWriteMode.WriteSchema)
{
DataTable [] _tables = new DataTable[tables.Count];
tables.CopyTo(_tables);
DataRelation[] _relations = new DataRelation[relations.Count];
relations.CopyTo(_relations);
DataTable dt = _tables [0];
new XmlSchemaWriter(writer,
_tables,
_relations,
mainDataTable,
dataSetName,
dt.LocaleSpecified ? dt.Locale : null
).WriteSchema();
}
WriteTableList(writer, mode, tables, DataRowVersion.Default);
writer.WriteEndElement();
}
if (mode == XmlWriteMode.DiffGram)
{
List <DataTable> changedTables = new List <DataTable>();
foreach (DataTable table in tables)
{
DataTable changed = table.GetChanges(DataRowState.Modified | DataRowState.Deleted);
if (changed != null && changed.Rows.Count > 0)
{
changedTables.Add(changed);
}
}
if (changedTables.Count > 0)
{
DataSet.WriteStartElement(writer, XmlWriteMode.DiffGram, XmlConstants.DiffgrNamespace, XmlConstants.DiffgrPrefix, "before");
WriteTableList(writer, mode, changedTables, DataRowVersion.Original);
writer.WriteEndElement();
}
writer.WriteEndElement(); // diffgr:diffgram
}
writer.Flush();
}
public void RelationFromSchema()
{
DataSet Set = new DataSet();
Set.ReadXmlSchema("Test/System.Data/store.xsd");
DataTable Table = Set.Tables [0];
Assert.That(Table.CaseSensitive, Is.False, "test#01");
Assert.That(Table.ChildRelations.Count, Is.EqualTo(1), "test#02");
Assert.That(Table.ParentRelations.Count, Is.EqualTo(0), "test#03");
Assert.That(Table.Constraints.Count, Is.EqualTo(1), "test#04");
Assert.That(Table.PrimaryKey.Length, Is.EqualTo(1), "test#05");
Assert.That(Table.Rows.Count, Is.EqualTo(0), "test#06");
Assert.That(Table.TableName, Is.EqualTo("bookstore"), "test#07");
Assert.That(Table.Columns.Count, Is.EqualTo(1), "test#08");
DataRelation Relation = Table.ChildRelations [0];
Assert.That(Relation.ChildColumns.Length, Is.EqualTo(1), "test#09");
Assert.That(Relation.ChildKeyConstraint.ConstraintName, Is.EqualTo("bookstore_book"), "test#10");
Assert.That(Relation.ChildKeyConstraint.Columns.Length, Is.EqualTo(1), "test#11");
Assert.That(Relation.ChildTable.TableName, Is.EqualTo("book"), "test#12");
Assert.That(Relation.DataSet.DataSetName, Is.EqualTo("NewDataSet"), "test#13");
Assert.That(Relation.ExtendedProperties.Count, Is.EqualTo(0), "test#14");
Assert.That(Relation.Nested, Is.True, "test#15");
Assert.That(Relation.ParentColumns.Length, Is.EqualTo(1), "test#16");
Assert.That(Relation.ParentKeyConstraint.ConstraintName, Is.EqualTo("Constraint1"), "test#17");
Assert.That(Relation.ParentTable.TableName, Is.EqualTo("bookstore"), "test#18");
Assert.That(Relation.RelationName, Is.EqualTo("bookstore_book"), "test#19");
Table = Set.Tables [1];
Assert.That(Table.CaseSensitive, Is.False, "test#20");
Assert.That(Table.ChildRelations.Count, Is.EqualTo(1), "test#21");
Assert.That(Table.ParentRelations.Count, Is.EqualTo(1), "test#22");
Assert.That(Table.Constraints.Count, Is.EqualTo(2), "test#23");
Assert.That(Table.PrimaryKey.Length, Is.EqualTo(1), "test#24");
Assert.That(Table.Rows.Count, Is.EqualTo(0), "test#25");
Assert.That(Table.TableName, Is.EqualTo("book"), "test#26");
Assert.That(Table.Columns.Count, Is.EqualTo(5), "test#27");
Relation = Table.ChildRelations [0];
Assert.That(Relation.ChildColumns.Length, Is.EqualTo(1), "test#28");
Assert.That(Relation.ChildKeyConstraint.ConstraintName, Is.EqualTo("book_author"), "test#29");
Assert.That(Relation.ChildKeyConstraint.Columns.Length, Is.EqualTo(1), "test#30");
Assert.That(Relation.ChildTable.TableName, Is.EqualTo("author"), "test#31");
Assert.That(Relation.DataSet.DataSetName, Is.EqualTo("NewDataSet"), "test#32");
Assert.That(Relation.ExtendedProperties.Count, Is.EqualTo(0), "test#33");
Assert.That(Relation.Nested, Is.True, "test#34");
Assert.That(Relation.ParentColumns.Length, Is.EqualTo(1), "test#35");
Assert.That(Relation.ParentKeyConstraint.ConstraintName, Is.EqualTo("Constraint1"), "test#36");
Assert.That(Relation.ParentTable.TableName, Is.EqualTo("book"), "test#37");
Assert.That(Relation.RelationName, Is.EqualTo("book_author"), "test#38");
Table = Set.Tables [2];
Assert.That(Table.CaseSensitive, Is.False, "test#39");
Assert.That(Table.ChildRelations.Count, Is.EqualTo(0), "test#40");
Assert.That(Table.ParentRelations.Count, Is.EqualTo(1), "test#41");
Assert.That(Table.Constraints.Count, Is.EqualTo(1), "test#42");
Assert.That(Table.PrimaryKey.Length, Is.EqualTo(0), "test#43");
Assert.That(Table.Rows.Count, Is.EqualTo(0), "test#44");
Assert.That(Table.TableName, Is.EqualTo("author"), "test#45");
Assert.That(Table.Columns.Count, Is.EqualTo(3), "test#46");
}
private void button1_Click(object sender, EventArgs e)
{
string cnStr = "Server=" + textBox1.Text + ";database=" + textBox2.Text + ";Integrated Security=True;"; //SYASHIN\\SQLEXPRESS 兩條斜線
if (textBox1.Text == "" | textBox2.Text == "")
{
MessageBox.Show("請輸入您要連結的資料庫名稱", "目前狀態", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
else
{
try
{
cn.ConnectionString = cnStr;
cn.Open();
if (cn.State == ConnectionState.Open)
{
MessageBox.Show("「" + cn.Database + "」已連接", "目前狀態", MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
button1.Text = "已連接"; //cn.Database;
//button1.Font = new Font(FontFamily.GenericSansSerif,6F, FontStyle.Bold); 字型設定
button1.Enabled = false;
button2.Enabled = true;
button3.Enabled = true;
button4.Enabled = true;
button5.Enabled = true;
button6.Enabled = true;
button7.Enabled = true;
textBox1.Enabled = false;
textBox2.Enabled = false;
textBox3.Enabled = true;
button8.Enabled = true;
button9.Enabled = true;
checkBox1.Enabled = true;
checkBox2.Enabled = true;
radioButton1.Enabled = true;
radioButton2.Enabled = true;
//-----------------------------------------各式資料表查看----------------------------------------------------//
SqlDataAdapter da1 = new SqlDataAdapter
("SELECT 樓層.名稱 AS 所在樓層,位置,衛工裝置.名稱,規格容量W "
+ "FROM 衛工裝置,樓層 "
+ "WHERE 衛工裝置.樓層=樓層.ID "
+ "ORDER BY 位置 ASC", cn);
da1.Fill(ds, "飲水機(衛工裝置)");
SqlDataAdapter da3 = new SqlDataAdapter
("SELECT 樓層.名稱 AS 所在樓層,位置,燈具.名稱,規格,規格容量W "
+ "FROM 燈具,樓層 "
+ "WHERE 燈具.樓層=樓層.ID "
+ "ORDER BY 位置 ASC", cn);
da3.Fill(ds, "燈具");
SqlDataAdapter da4 = new SqlDataAdapter
("SELECT 樓層.名稱 AS 所在樓層,位置,機械設備.名稱,規格容量W "
+ "FROM 機械設備,樓層 "
+ "WHERE 機械設備.樓層=樓層.ID "
+ "ORDER BY 位置 ASC", cn);
da4.Fill(ds, "機械設備");
SqlDataAdapter da6 = new SqlDataAdapter
("SELECT 樓層.名稱 AS 所在樓層,位置,資料裝置.名稱,規格容量W "
+ "FROM 資料裝置,樓層 "
+ "WHERE 資料裝置.樓層=樓層.ID "
+ "ORDER BY 位置 ASC", cn);
da6.Fill(ds, "資料裝置");
SqlDataAdapter da7 = new SqlDataAdapter
("SELECT 樓層.名稱 AS 所在樓層,位置,家具.名稱,規格容量W "
+ "FROM 家具,樓層 "
+ "WHERE 家具.樓層=樓層.ID "
+ "ORDER BY 位置 ASC", cn);
da7.Fill(ds, "家具");
SqlDataAdapter da8 = new SqlDataAdapter
("SELECT 樓層.名稱 AS 所在樓層,編號,房間.名稱,部門 AS 房間類型,ROUND(面積,3) AS 房間面積,年使用週數,週使用時數,空調使用週數 "
+ "FROM 房間,樓層 "
+ "WHERE 房間.樓層=樓層.ID "
+ "ORDER BY 編號 ASC", cn);
da8.Fill(ds, "房間");
comboBox1.Text = "請選擇資料表";
for (int i = 0; i < ds.Tables.Count; i++)
{
comboBox1.Items.Add(ds.Tables[i].TableName);
}
//-----------------------------------------各式資料表查看(END)----------------------------------------------------//
SqlDataAdapter da10 = new SqlDataAdapter(string1, cn);
da10.Fill(ds1, "房間各別統計");
SqlDataAdapter da12 = new SqlDataAdapter
("SELECT "
+ "convert(Decimal(38,3),ROUND((SUM((ISNULL(燈具類型資料.燈具總電容,0)+ISNULL(資料裝置類型資料.資料裝置總電容,0))*週使用時數*年使用週數+ISNULL(機械設備類型資料.機械設備總電容,0)*週使用時數*ISNULL(空調使用週數,0)+ISNULL(衛工裝置類型資料.衛工裝置總耗電,0)+ISNULL(家具類型資料.家具總耗電,0) )/1000.0),3)) AS 總耗電量度KW_H" + ","
+ "ROUND(SUM(房間資料表.面積),3) AS 總樓地板面積" + ","
+ "convert(Decimal(38,3),ROUND((SUM((ISNULL(燈具類型資料.燈具總電容,0)+ISNULL(資料裝置類型資料.資料裝置總電容,0))*週使用時數*年使用週數+ISNULL(機械設備類型資料.機械設備總電容,0)*週使用時數*ISNULL(空調使用週數,0)+ISNULL(衛工裝置類型資料.衛工裝置總耗電,0)+ISNULL(家具類型資料.家具總耗電,0) )/1000.0/SUM (房間資料表.面積)),3)) AS 總體EUI "
+ "FROM" + ROOMstr_s + "," + LIGHT_s + "," + PLUMBING_s + "," + MACHINE_s + "," + DATA_s + "," + FURNITURE_s + " " + WHERE_s, cn);
da12.Fill(ds1, "總耗電量統計");
string string4 = "HAVING SUM(A2.用電容計算之耗電量)/A3.耗電量*100<=100 ORDER BY A1.用電容計算之耗電量 DESC"; //HAVING子句可以消除
SqlDataAdapter da13 = new SqlDataAdapter(string3 + " " + string4, cn);
da13.Fill(ds1, "耗電量與EUI統計");
SqlDataAdapter daCATEGORY = new SqlDataAdapter
("SELECT DISTINCT 部門 FROM 房間", cn);
daCATEGORY.Fill(ds2, "空間類型");
SqlDataAdapter daROOM = new SqlDataAdapter
("SELECT 編號,名稱,部門,年使用週數,週使用時數,空調使用週數 FROM 房間", cn);
daROOM.Fill(ds2, "房間列表");
SqlDataAdapter daFacility = new SqlDataAdapter
("SELECT 位置,名稱,規格容量W,耗電量 FROM(SELECT 位置,燈具.名稱,規格容量W,規格容量W*年使用週數*週使用時數 AS 耗電量 FROM 燈具,房間 WHERE 燈具.位置=房間.編號 UNION ALL SELECT 位置,資料裝置.名稱,規格容量W,規格容量W*年使用週數*週使用時數 AS 耗電量 FROM 資料裝置,房間 WHERE 資料裝置.位置=房間.編號 UNION ALL SELECT 位置,機械設備.名稱,規格容量W,規格容量W*週使用時數*空調使用週數 AS 耗電量 FROM 機械設備,房間 WHERE 機械設備.位置=房間.編號 UNION ALL SELECT 位置,名稱,規格容量W,規格容量W*年使用時數hr AS 耗電量 FROM 衛工裝置 UNION ALL SELECT 位置,家具.名稱,規格容量W,規格容量W*年使用時數hr AS 耗電量 FROM 家具)AS A1", cn);
daFacility.Fill(ds2, "設備列表");
DataRelation datarelation1 = new DataRelation("FK_房間列表_空間類型", ds2.Tables["空間類型"].Columns["部門"], ds2.Tables["房間列表"].Columns["部門"]);
DataRelation datarelation2 = new DataRelation("FK_設備列表_房間列表", ds2.Tables["房間列表"].Columns["編號"], ds2.Tables["設備列表"].Columns["位置"]);
ds2.Relations.AddRange(new DataRelation[] { datarelation1, datarelation2 });
dataGridView4.DataSource = ds2;
dataGridView4.DataMember = "空間類型";
dataGridView4.Dock = DockStyle.Fill;
}
}
catch
{
if (cn.State == ConnectionState.Open)
{
MessageBox.Show("資料庫沒有對應的資料表，請檢查您的伺服器與資料庫名稱是否輸入正確", "目前狀態", MessageBoxButtons.OK, MessageBoxIcon.Question);
}
else
{
MessageBox.Show("沒有這個資料庫，請檢查您的伺服器與資料庫名稱是否輸入正確", "目前狀態", MessageBoxButtons.OK, MessageBoxIcon.Question);
}
}
}
}
public void IndexOf_ByDataRelationName()
{
DataSet ds = getDataSet();
DataSet ds1 = getDataSet();
DataRelation rel1 = new DataRelation("rel1", ds.Tables[0].Columns["ParentId"], ds.Tables[1].Columns["ParentId"]);
DataRelation rel2 = new DataRelation("rel2", ds.Tables[0].Columns["String1"], ds.Tables[1].Columns["String1"]);
DataRelation rel3 = new DataRelation("rel3", ds1.Tables[0].Columns["ParentId"], ds1.Tables[1].Columns["ParentId"]);
ds.Relations.Add(rel1);
ds.Relations.Add(rel2);
Assert.Equal(1, ds.Relations.IndexOf("rel2"));
Assert.Equal(0, ds.Relations.IndexOf("rel1"));
Assert.Equal(-1, ds.Relations.IndexOf((string)null));
Assert.Equal(-1, ds.Relations.IndexOf("rel3"));
}
public static DataSet RetornarDataSet <TSource, TKey>(
this IEnumerable <TSource> source,
Expression <Func <TSource, TKey> > keySelectorPai,
Expression <Func <TSource, TKey> > keySelectorFilho)
{
Dictionary <string, string> dictionary = new Dictionary <string, string>();
Func <int, Expression, MemberExpression> funcMemberExpression =
(tipo, propriedade) =>
{
MemberExpression memberExpression = null;
UnaryExpression unaryExpression = propriedade as UnaryExpression;
if (unaryExpression == null)
{
memberExpression = propriedade as MemberExpression;
}
else
{
memberExpression = unaryExpression.Operand as MemberExpression;
}
dictionary.Add(String.Concat(tipo, '_', memberExpression.Member.Name), memberExpression.Member.Name);
return(memberExpression);
};
NewExpression newExpressionPai = keySelectorPai.Body as NewExpression;
NewExpression newExpressionFilho = keySelectorFilho.Body as NewExpression;
if (newExpressionPai == null)
{
funcMemberExpression(1, keySelectorPai);
funcMemberExpression(2, keySelectorFilho);
}
else
{
newExpressionPai.Arguments
.ToList()
.ForEach(f => funcMemberExpression(1, f));
newExpressionFilho.Arguments
.ToList()
.ForEach(f => funcMemberExpression(2, f));
}
Func <int, Dictionary <string, string> > FuncFiltro = p => dictionary
.Where(s => Convert.ToInt32(s.Key.Split('_').FirstOrDefault()) == p)
.ToDictionary(d => d.Key, d => d.Value);
var x2 = FuncFiltro(1).Intersect(FuncFiltro(2));
Console.WriteLine(x2.Count());
XmlSerializer xmlSerializer = new XmlSerializer(typeof(TSource[]));
StringWriter stringWriter = new StringWriter();
xmlSerializer.Serialize(stringWriter, source.ToArray());
TextReader txtReader = new StringReader(stringWriter.ToString());
XmlReader xmlReader = new XmlTextReader(txtReader);
DataSet dataSet = new DataSet();
dataSet.ReadXml(xmlReader);
dataSet.Relations.Clear();
DataRelation CustOrderRel = new DataRelation("Tabela1", dataSet.Tables["cFrmDragDrop"].Columns["ID"], dataSet.Tables["cFrmDragDrop2"].Columns["ID"]);
DataColumn[] parentCols1 = new DataColumn[] { dataSet.Tables["cFrmDragDrop"].Columns["ID"] };
DataColumn[] childCols1 = new DataColumn[] { dataSet.Tables["cFrmDragDrop2XXXXXX"].Columns["ID2"] };
DataRelation CustOrderRel1 = new DataRelation("Tabela2", parentCols1, childCols1);
dataSet.Relations.Add(CustOrderRel);
dataSet.Relations.Add(CustOrderRel1);
return(dataSet);
}
public void Add_DataColumn1()
{
DataSet ds = DataProvider.CreateForigenConstraint();
int originalRelationsCount = ds.Relations.Count;
DataRelation rel = new DataRelation("rel1", ds.Tables[0].Columns["ParentId"]
, ds.Tables[1].Columns["ParentId"]);
ds.Relations.Add(rel);
Assert.Equal(originalRelationsCount + 1, ds.Relations.Count);
Assert.Equal(1, ds.Tables[0].ChildRelations.Count);
Assert.Equal(1, ds.Tables[1].ParentRelations.Count);
Assert.Equal(typeof(UniqueConstraint), ds.Tables[0].Constraints[0].GetType());
Assert.Equal(typeof(ForeignKeyConstraint), ds.Tables[1].Constraints[0].GetType());
Assert.Throws<ArgumentException>(() =>
{
ds.Relations.Add(rel);
});
ds.Relations.Add(null);
}
public DatabaseFile(string databasePath, string schemaPath)
{
this._loadErrors = new List <string[]>();
base.DataSetName = "database";
using (DatabaseBinaryReader reader = new DatabaseBinaryReader(EndianBitConverter.Little, File.Open(databasePath, FileMode.Open, FileAccess.Read, FileShare.Read)))
{
int num;
Exception exception;
Dictionary <DataColumn, string[]> dR = new Dictionary <DataColumn, string[]>();
this._loadErrors = new List <string[]>();
XmlDocument SXML = new XmlDocument();
SXML.Load(File.Open(schemaPath, FileMode.Open, FileAccess.Read, FileShare.Read));
int itemNum = 0;
int nodeNum = 0;
base.DataSetName = reader.ReadUInt32().ToString();
uint num4 = 0;
if (SXML.DocumentElement.ChildNodes[nodeNum].Name == "schemaVersion")
{
num4 = reader.ReadUInt32();
base.DataSetName = base.DataSetName + ";" + num4.ToString();
nodeNum++;
}
int offset = 12;
if (num4 == 0x3e8)
{
num = 1;
while (num < SXML.DocumentElement.ChildNodes.Count)
{
reader.Seek(offset, SeekOrigin.Begin);
int num6 = reader.ReadInt32();
offset += ((num6 * (SXML.DocumentElement.ChildNodes[num].ChildNodes.Count + 1)) * 4) + 8;
num++;
}
offset += 4;
reader.Seek(8, SeekOrigin.Begin);
}
while (reader.BaseStream.Position < reader.BaseStream.Length)
{
string innerText;
if (nodeNum >= SXML.DocumentElement.ChildNodes.Count)
{
break;
}
DataTable table = new DataTable(SXML.DocumentElement.ChildNodes[nodeNum].Attributes["name"].InnerText);
reader.Seek(4, SeekOrigin.Current);
itemNum = reader.ReadInt32();
foreach (XmlElement element in SXML.DocumentElement.ChildNodes[nodeNum])
{
DataColumn column = new DataColumn();
table.Columns.Add(column);
column.ColumnName = element.Attributes["name"].InnerText;
if (element.HasAttribute("key"))
{
if (element.Attributes["key"].InnerText == "primary")
{
table.PrimaryKey = new DataColumn[] { column };
}
else
{
string[] strArray = element.Attributes["key"].InnerText.Split(new char[] { '.' });
dR.Add(column, strArray);
}
}
innerText = element.Attributes["type"].InnerText;
if (innerText == null)
{
goto Label_03E8;
}
if (!(innerText == "float"))
{
if (innerText == "int")
{
goto Label_0346;
}
if (innerText == "string")
{
goto Label_036B;
}
if (innerText == "bool")
{
goto Label_03C6;
}
goto Label_03E8;
}
column.DataType = typeof(float);
column.DefaultValue = 0f;
continue;
Label_0346:
column.DataType = typeof(int);
column.DefaultValue = 0;
continue;
Label_036B:
column.DataType = typeof(string);
if (element.HasAttribute("size"))
{
column.MaxLength = Convert.ToInt32(element.Attributes["size"].InnerText);
}
column.DefaultValue = string.Empty;
continue;
Label_03C6:
column.DataType = typeof(bool);
column.DefaultValue = false;
continue;
Label_03E8:
column.DataType = typeof(int);
column.DefaultValue = 0;
}
for (num = 0; num < itemNum; num++)
{
DataRow row = table.NewRow();
List <object> list = new List <object>();
reader.Seek(4, SeekOrigin.Current);
foreach (XmlElement element in SXML.DocumentElement.ChildNodes[nodeNum])
{
innerText = element.Attributes["type"].InnerText;
if (innerText == null)
{
goto Label_05C0;
}
if (!(innerText == "float"))
{
if (innerText == "int")
{
goto Label_0502;
}
if (innerText == "string")
{
goto Label_051A;
}
if (innerText == "bool")
{
goto Label_05A3;
}
goto Label_05C0;
}
list.Add(reader.ReadSingle());
continue;
Label_0502:
list.Add(reader.ReadInt32());
continue;
Label_051A:
if (num4 == 0x3e8)
{
int num7 = ((int)reader.BaseStream.Position) + 4;
reader.Seek(offset + reader.ReadInt32(), SeekOrigin.Begin);
list.Add(reader.ReadTerminatedString(0));
reader.Seek(num7, SeekOrigin.Begin);
}
else
{
list.Add(reader.ReadDatabaseString(table.Columns[element.Attributes["name"].InnerText].MaxLength));
}
continue;
Label_05A3:
list.Add(reader.ReadBoolean());
reader.ReadBytes(3);
continue;
Label_05C0:
list.Add(reader.ReadInt32());
}
row.ItemArray = list.ToArray();
try
{
table.Rows.Add(row);
}
catch (Exception exception2)
{
float num8;
int num9;
bool flag;
exception = exception2;
if (exception is ConstraintException)
{
try
{
this._loadErrors.Add(new string[] { table.TableName, exception.Message, string.Empty });
table.PrimaryKey = null;
table.Rows.Add(row);
this._loadErrors[this._loadErrors.Count - 1][2] = "The rows were kept but it is recommended that you fix the problem.";
}
catch (Exception exception3)
{
(this._loadErrors[this._loadErrors.Count - 1])[1] += " " + exception3.Message;
(this._loadErrors[this._loadErrors.Count - 1])[1] += " The row was removed. Below are its contents separated by \" | \". Make sure to fix the problem if you are going to add it again.";
foreach (object value in list)
{
if (value is float)
{
num8 = (float)value;
(this._loadErrors[this._loadErrors.Count - 1])[2] += num8.ToString() + "|";
}
else if (value is int)
{
num9 = (int)value;
(this._loadErrors[this._loadErrors.Count - 1])[2] += num9.ToString() + "|";
}
else if (value is string)
{
(this._loadErrors[this._loadErrors.Count - 1])[2] += ((string)value) + "|";
}
else if (value is bool)
{
flag = (bool)value;
(this._loadErrors[this._loadErrors.Count - 1])[2] += flag.ToString() + "|";
}
}
}
}
else
{
this._loadErrors.Add(new string[] { table.TableName, exception.Message, string.Empty });
(this._loadErrors[this._loadErrors.Count - 1])[1] += " The row was removed. Below are its contents separated by \" | \". Make sure to fix the problem if you are going to add it again.";
foreach (object value in list)
{
if (value is float)
{
num8 = (float)value;
(this._loadErrors[this._loadErrors.Count - 1])[2] += num8.ToString() + "|";
}
else if (value is int)
{
num9 = (int)value;
(this._loadErrors[this._loadErrors.Count - 1])[2] += num9.ToString() + "|";
}
else if (value is string)
{
(this._loadErrors[this._loadErrors.Count - 1])[2] += ((string)value) + "|";
}
else if (value is bool)
{
flag = (bool)value;
(this._loadErrors[this._loadErrors.Count - 1])[2] += flag.ToString() + "|";
}
}
}
}
}
base.Tables.Add(table);
nodeNum++;
}
foreach (KeyValuePair <DataColumn, string[]> pair in dR)
{
DataRelation relation = new DataRelation(pair.Key.Table.TableName + "." + pair.Key.ColumnName, base.Tables[pair.Value[0]].Columns[pair.Value[1]], pair.Key);
pair.Key.Table.ExtendedProperties.Add(pair.Key.ColumnName, pair.Value[0]);
try
{
base.Relations.Add(relation);
}
catch (Exception exception4)
{
exception = exception4;
if ((exception is ConstraintException) && (exception.Message.Contains("to exist in the parent table.") && !exception.Message.Contains("values (0)")))
{
this._loadErrors.Add(new string[] { pair.Key.Table.TableName, exception.Message, string.Empty });
this._loadErrors[this._loadErrors.Count - 1][2] = "It is recommended that you fix the problem. To help you find the row, search for the value() from the above line in the " + pair.Key + " column.";
}
}
}
}
base.Namespace = Path.GetFileName(schemaPath);
base.AcceptChanges();
}
private void RemoveAt()
{
DataSet ds = getDataSet();
DataSet ds1 = getDataSet();
DataRelation rel1 = new DataRelation("rel1", ds.Tables[0].Columns["ParentId"], ds.Tables[1].Columns["ParentId"]);
DataRelation rel2 = new DataRelation("rel2", ds.Tables[0].Columns["String1"], ds.Tables[1].Columns["String1"]);
ds.Relations.Add(rel1);
ds.Relations.Add(rel2);
Assert.Equal(2, ds.Relations.Count);
ds.Relations.CollectionChanged += new CollectionChangeEventHandler(Relations_CollectionChanged);
//Perform remove
ds.Relations.RemoveAt(0);
Assert.Equal(1, ds.Relations.Count);
Assert.Equal("rel2", ds.Relations[0].RelationName);
ds.Relations.RemoveAt(0);
Assert.Equal(0, ds.Relations.Count);
Assert.Equal(2, _changesCounter);
Assert.Throws<IndexOutOfRangeException>(() =>
{
ds.Relations.RemoveAt(-1);
});
}
static void Main(string[] args)
{
DataSet dataSet = new DataSet("Books");
dataSet.ExtendedProperties.Add("Owner", "ITstep"); //метаданные о наборе
#region books columns
DataTable booksTable = new DataTable("books");
booksTable.Columns.Add(new DataColumn
{
AllowDBNull = false,
AutoIncrement = true,
AutoIncrementStep = 1,
AutoIncrementSeed = 1,
ColumnName = "Id",
DataType = typeof(int),
Unique = true,
});
booksTable.Columns.Add("name", typeof(string));
booksTable.Columns.Add("authorId", typeof(int));
booksTable.Columns.Add("price", typeof(int));
#endregion
#region books rows
booksTable.Rows.Add("Fairytale", 1000, 1);
DataRow row = booksTable.NewRow();
row.BeginEdit();
row.ItemArray = new object[] { "Fairytale p.2", 1200, 1 };
row.SetAdded();
row.EndEdit();
booksTable.Rows.Add(booksTable.NewRow());
#endregion
dataSet.Tables.Add(booksTable);
#region authors columns
DataTable authorTable = new DataTable("authors");
booksTable.Columns.Add(new DataColumn
{
AllowDBNull = false,
AutoIncrement = true,
AutoIncrementStep = 1,
AutoIncrementSeed = 1,
ColumnName = "Id",
DataType = typeof(int),
Unique = true,
});
booksTable.Columns.Add("name", typeof(string));
#endregion
#region authors rows
authorTable.Rows.Add("Pushkin A.S");
authorTable.Rows.Add(booksTable.NewRow());
#endregion
dataSet.Tables.Add(authorTable);
DataRelation dataRelation = new DataRelation("author_books_fk", "authors", "books", new string[] { "id" }, new string[] { "authorId" }, false);
dataSet.Relations.Add(dataRelation);
}
internal void InitVars()
{
_tableOrder_Details = ((Order_DetailsDataTable)(Tables["Order Details"]));
if ((_tableOrder_Details != null))
{
_tableOrder_Details.InitVars();
}
_tableOrders = ((OrdersDataTable)(Tables["Orders"]));
if ((_tableOrders != null))
{
_tableOrders.InitVars();
}
_relationOrdersOrder_x0020_Details = Relations["OrdersOrder_x0020_Details"];
}
//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;
DataRelation dRel;
DataTable dtChild = GHTUtils.DataProvider.CreateChildDataTable();
DataTable dtParent = GHTUtils.DataProvider.CreateParentDataTable();
DataSet ds = new DataSet();
ds.Tables.Add(dtParent);
ds.Tables.Add(dtChild);
//parameter createConstraints = true
bool createConstraints = true;
for (int i = 0; i <= 1; i++)
{
if (i == 0)
{
createConstraints = false;
}
else
{
createConstraints = true;
}
ds.Relations.Clear();
dtParent.Constraints.Clear();
dtChild.Constraints.Clear();
//add duplicated row
dtParent.Rows.Add(dtParent.Rows[0].ItemArray);
dRel = new DataRelation("MyRelation", new DataColumn[] { dtParent.Columns[0] }, new DataColumn[] { dtChild.Columns[0] }, createConstraints);
try
{
BeginCase("Add relation which will create invalid constraint,createConstraints=" + createConstraints.ToString());
exp = new Exception();
try
{
ds.Relations.Add(dRel);
}
catch (ArgumentException ex) { exp = ex; }
if (createConstraints == true)
{
Compare(exp.GetType().FullName, typeof(ArgumentException).FullName);
}
else
{
Compare(exp.GetType().FullName, typeof(Exception).FullName);
}
exp = null;
}
catch (Exception ex) { exp = ex; }
finally { EndCase(exp); exp = null; }
ds.Relations.Clear();
dtParent.Constraints.Clear();
dtChild.Constraints.Clear();
dtParent.Rows.Remove(dtParent.Rows[dtParent.Rows.Count - 1]);
dRel = new DataRelation("MyRelation", new DataColumn[] { dtParent.Columns[0] }, new DataColumn[] { dtChild.Columns[0] }, createConstraints);
ds.Relations.Add(dRel);
try
{
BeginCase("DataRelation - CTor,createConstraints=" + createConstraints.ToString());
Compare(dRel == null, false);
}
catch (Exception ex) { exp = ex; }
finally { EndCase(exp); exp = null; }
try
{
BeginCase("DataRelation - parent Constraints,createConstraints=" + createConstraints.ToString());
Compare(dtParent.Constraints.Count, i);
}
catch (Exception ex) { exp = ex; }
finally { EndCase(exp); exp = null; }
try
{
BeginCase("DataRelation - child Constraints,createConstraints=" + createConstraints.ToString());
Compare(dtChild.Constraints.Count, i);
}
catch (Exception ex) { exp = ex; }
finally { EndCase(exp); exp = null; }
try
{
BeginCase("DataRelation - child relations,createConstraints=" + createConstraints.ToString());
Compare(dtParent.ChildRelations[0], dRel);
}
catch (Exception ex) { exp = ex; }
finally { EndCase(exp); exp = null; }
try
{
BeginCase("DataRelation - parent relations,createConstraints=" + createConstraints.ToString());
Compare(dtChild.ParentRelations[0], dRel);
}
catch (Exception ex) { exp = ex; }
finally { EndCase(exp); exp = null; }
try
{
BeginCase("DataRelation - name");
Compare(dRel.RelationName, "MyRelation");
}
catch (Exception ex) { exp = ex; }
finally { EndCase(exp); exp = null; }
}
}
public void AssertDataRelation(string label, DataRelation rel, string name, bool nested,
string[] parentColNames, string[] childColNames,
bool existsUK, bool existsFK)
{
Assert.Equal(name, rel.RelationName);
Assert.Equal(nested, rel.Nested);
for (int i = 0; i < parentColNames.Length; i++)
Assert.Equal(parentColNames[i], rel.ParentColumns[i].ColumnName);
Assert.Equal(parentColNames.Length, rel.ParentColumns.Length);
for (int i = 0; i < childColNames.Length; i++)
Assert.Equal(childColNames[i], rel.ChildColumns[i].ColumnName);
Assert.Equal(childColNames.Length, rel.ChildColumns.Length);
if (existsUK)
Assert.NotNull(rel.ParentKeyConstraint);
else
Assert.Null(rel.ParentKeyConstraint);
if (existsFK)
Assert.NotNull(rel.ChildKeyConstraint);
else
Assert.Null(rel.ChildKeyConstraint);
}
//Compare two DataTables and return a DataTable with DifferentRecords
public static DataTable CompareDataTables(DataTable FirstDataTable, DataTable SecondDataTable)
{
//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.Copy(), SecondDataTable.Copy() });
//Get Columns for DataRelation
DataColumn[] firstColumns = new DataColumn[ds.Tables[0].Columns.Count];
for (int i = 0; i < firstColumns.Length; i++)
{
firstColumns[i] = ds.Tables[0].Columns[i];
}
DataColumn[] secondColumns = new DataColumn[ds.Tables[1].Columns.Count];
for (int i = 0; i < secondColumns.Length; i++)
{
secondColumns[i] = ds.Tables[1].Columns[i];
}
//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
for (int i = 0; i < FirstDataTable.Columns.Count; i++)
{
ResultDataTable.Columns.Add(FirstDataTable.Columns[i].ColumnName, FirstDataTable.Columns[i].DataType);
}
//If FirstDataTable Row not in SecondDataTable, Add to ResultDataTable.
ResultDataTable.BeginLoadData();
foreach (DataRow parentrow in ds.Tables[0].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 ds.Tables[1].Rows)
{
DataRow[] childrows = parentrow.GetChildRows(r2);
if (childrows == null || childrows.Length == 0)
{
ResultDataTable.LoadDataRow(parentrow.ItemArray, true);
}
}
ResultDataTable.EndLoadData();
}
return(ResultDataTable);
}
private void MakeDataRelation(DataTable dt)
{
DataColumn parentColumn = dt.Columns["id"];
DataColumn childColumn = _dataSet.Tables["ChildTable"].Columns["ParentID"];
DataRelation relation = new DataRelation("ParentChild_Relation1", parentColumn, childColumn);
_dataSet.Tables["ChildTable"].ParentRelations.Add(relation);
DataColumn[] parentColumn1 = new DataColumn[2];
DataColumn[] childColumn1 = new DataColumn[2];
parentColumn1[0] = dt.Columns["id"];
parentColumn1[1] = dt.Columns["DepartmentID"];
childColumn1[0] = _dataSet.Tables["SecondChildTable"].Columns["ParentID"];
childColumn1[1] = _dataSet.Tables["SecondChildTable"].Columns["DepartmentID"];
DataRelation secondRelation = new DataRelation("ParentChild_Relation2", parentColumn1, childColumn1);
_dataSet.Tables["SecondChildTable"].ParentRelations.Add(secondRelation);
}
public DataRelationEdge(DataRelation relation)
{
Contract.Requires(relation != null);
this.relation = relation;
}
public void WriteNestedTableXml()
{
string xml = @"<NewDataSet>
<tab1>
<ident>1</ident>
<name>hoge</name>
<tab2>
<timestamp>2004-05-05</timestamp>
</tab2>
</tab1>
<tab1>
<ident>2</ident>
<name>fuga</name>
<tab2>
<timestamp>2004-05-06</timestamp>
</tab2>
</tab1>
</NewDataSet>";
var ds = new DataSet();
DataTable dt = new DataTable("tab1");
dt.Columns.Add("ident");
dt.Columns.Add("name");
dt.Rows.Add(new object[] { "1", "hoge" });
dt.Rows.Add(new object[] { "2", "fuga" });
DataTable dt2 = new DataTable("tab2");
dt2.Columns.Add("idref");
dt2.Columns[0].ColumnMapping = MappingType.Hidden;
dt2.Columns.Add("timestamp");
dt2.Rows.Add(new object[] { "1", "2004-05-05" });
dt2.Rows.Add(new object[] { "2", "2004-05-06" });
ds.Tables.Add(dt);
ds.Tables.Add(dt2);
DataRelation rel = new DataRelation("rel", dt.Columns[0], dt2.Columns[0]);
rel.Nested = true;
ds.Relations.Add(rel);
StringWriter sw = new StringWriter();
ds.WriteXml(sw);
Assert.Equal(sw.ToString().Replace("\r\n", "\n"), xml.Replace("\r\n", "\n"));
}
// <Snippet1>
private void PrintName(DataRelation relation)
{
// Print the name of the DataRelation.
Console.WriteLine(relation.ToString());
}
private void AddRelation(DataColumn parentChapterColumn, DataColumn chapterColumn)
{
if (null != _dataSet)
{
string name = /*parentChapterColumn.ColumnName + "_" +*/ chapterColumn.ColumnName;
DataRelation relation = new DataRelation(name, new DataColumn[] { parentChapterColumn }, new DataColumn[] { chapterColumn }, false);
int index = 1;
string tmp = name;
DataRelationCollection relations = _dataSet.Relations;
while (-1 != relations.IndexOf(tmp))
{
tmp = name + index;
index++;
}
relation.RelationName = tmp;
relations.Add(relation);
}
}
private void Getdata()
{
string sql = "select a.*, b.UserName from sysHistory a inner join sysuser b on a.sysUserID=b.sysUserID where a.sysTableID=" + _Data.DrTableMaster["sysTableID"].ToString() + " and pkValue='" + _Data.DrCurrentMaster[_Data.PkMaster.FieldName] + "' order by a.hDateTime ";
DataTable tb = _dbData.GetDataTable(sql);
if (tb != null)
{
dbSet.Tables.Add(tb);
}
else
{
return;
}
tb.PrimaryKey = new DataColumn[] { tb.Columns["sysHistoryID"] };
sql = "select a.*, c.FieldName,c.LabelName from sysHistoryDt a inner join sysHistory b on a.sysHistoryID =b.sysHistoryID inner join sysField c on a.sysFieldID=c.sysFieldID where b.sysTableID=" + _Data.DrTableMaster["sysTableID"].ToString() + " and pkValue='" + _Data.DrCurrentMaster[_Data.PkMaster.FieldName] + "' order by b.hDateTime ";
DataTable tb1 = _dbData.GetDataTable(sql);
if (tb1 != null)
{
dbSet.Tables.Add(tb1);
}
tb.TableName = "Master";
tb1.TableName = "Detail";
tb1.PrimaryKey = new DataColumn[] { tb1.Columns["sysHistoryDtID"] };
DataRelation re = new DataRelation("Re", tb.Columns["sysHistoryID"], tb1.Columns["sysHistoryID"]);
dbSet.Relations.Add(re);
BindingSource bs = new BindingSource();
bs.DataSource = dbSet;
bs.DataMember = tb.TableName;
gridControl1.DataSource = bs;
gridControl2.DataSource = bs;
gridControl2.DataMember = "Re";
string idlist = "(";
foreach (DataRow drDt in _Data.LstDrCurrentDetails)
{
string pkDt = _Data.DrTable["pk"].ToString();
idlist += "'" + drDt[pkDt].ToString() + "',";
}
idlist = idlist.Substring(0, idlist.Length - 1) + ")";
sql = "select a.*, b.UserName from sysHistory a inner join sysuser b on a.sysUserID=b.sysUserID where a.sysTableID=" + _Data.DrTable["sysTableID"].ToString() + " and pkValue in " + idlist + " order by a.hDatetime";
DataTable tbDt = _dbData.GetDataTable(sql);
DataSet dbSetDt = new DataSet();
if (tbDt != null)
{
dbSetDt.Tables.Add(tbDt);
}
else
{
return;
}
tbDt.PrimaryKey = new DataColumn[] { tbDt.Columns["sysHistoryID"] };
sql = "select a.*, c.FieldName,c.LabelName from sysHistoryDt a inner join sysHistory b on a.sysHistoryID =b.sysHistoryID inner join sysField c on a.sysFieldID=c.sysFieldID where ";
sql += " a.sysHistoryID in (select sysHistoryID from sysHistory where sysTableID=" + _Data.DrTable["sysTableID"].ToString() + " and pkValue in " + idlist + ") order by b.hDatetime";
DataTable tbDt1 = _dbData.GetDataTable(sql);
if (tbDt1 != null)
{
dbSetDt.Tables.Add(tbDt1);
}
tbDt.TableName = "Master";
tbDt1.TableName = "Detail";
tbDt1.PrimaryKey = new DataColumn[] { tbDt1.Columns["sysHistoryDtID"] };
DataRelation reDt = new DataRelation("ReDt", tbDt.Columns["sysHistoryID"], tbDt1.Columns["sysHistoryID"]);
dbSetDt.Relations.Add(reDt);
BindingSource bsDt = new BindingSource();
bsDt.DataSource = dbSetDt;
bsDt.DataMember = tbDt.TableName;
gridControl3.DataSource = bsDt;
gridControl4.DataSource = bsDt;
gridControl4.DataMember = "ReDt";
}
public void Clear()
{
DataTable Table = new DataTable("test_table");
DataTable Table2 = new DataTable("test_table2");
DataSet Set = new DataSet();
Set.Tables.Add(Table);
Set.Tables.Add(Table2);
DataColumnCollection Cols = Table.Columns;
DataColumnCollection Cols2 = Table2.Columns;
Cols.Add();
Cols.Add("testi");
Cols.Clear();
Assert.Equal(0, Cols.Count);
Cols.Add();
Cols.Add("testi");
Cols2.Add();
Cols2.Add();
DataRelation Rel = new DataRelation("Rel", Cols[0], Cols2[0]);
Set.Relations.Add(Rel);
try
{
Cols.Clear();
Assert.False(true);
}
catch (Exception e)
{
Assert.Equal(typeof(ArgumentException), e.GetType());
// Never premise English.
//Assert.Equal ("Cannot remove this column, because it is part of the parent key for relationship Rel.", e.Message);
}
}
public Form1()
{
InitializeComponent();
this.DS = new DataSet();
this.cn = new OleDbConnection();
this.cn.ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\CountriesCities.accdb";
OleDbCommand cmdSel = (OleDbCommand)this.cn.CreateCommand();
this.DA = new OleDbDataAdapter(cmdSel);
foreach (String table in Tables)
{
cmdSel.CommandText = "SELECT * FROM " + table;
this.DA.Fill(this.DS, table);
}
this.cmdDelCountry = this.cn.CreateCommand();
this.cmdDelCountry.CommandText = "DELETE FROM Countries WHERE id = @id";
DbParameter P1 = this.cmdDelCountry.CreateParameter();
P1.DbType = DbType.Int32;
P1.ParameterName = "@id";
P1.SourceColumn = "id";
this.cmdDelCountry.Parameters.Add(P1);
this.cmdDelCity = this.cn.CreateCommand();
this.cmdDelCity.CommandText = "DELETE FROM Cities WHERE id = @id";
P1 = this.cmdDelCity.CreateParameter();
P1.DbType = DbType.Int32;
P1.ParameterName = "@id";
P1.SourceColumn = "id";
this.cmdDelCity.Parameters.Add(P1);
this.cmdInsCountry = this.cn.CreateCommand();
this.cmdInsCountry.CommandText = "INSERT INTO Countries (name) VALUES (@name)";
P1 = this.cmdInsCountry.CreateParameter();
P1.DbType = DbType.String;
P1.ParameterName = "@name";
P1.SourceColumn = "name";
this.cmdInsCountry.Parameters.Add(P1);
this.cmdUpdCountry = this.cn.CreateCommand();
this.cmdUpdCountry.CommandText = "UPDATE Countries SET name = @name WHERE id = @id";
P1 = this.cmdUpdCountry.CreateParameter();
P1.DbType = DbType.String;
P1.ParameterName = "@name";
P1.SourceColumn = "name";
this.cmdUpdCountry.Parameters.Add(P1);
P1 = this.cmdUpdCountry.CreateParameter();
P1.DbType = DbType.Int32;
P1.ParameterName = "@id";
P1.SourceColumn = "id";
this.cmdUpdCountry.Parameters.Add(P1);
this.cmdInsCity = this.cn.CreateCommand();
this.cmdInsCity.CommandText = "INSERT INTO Cities (name, id_country) VALUES (@name, @id_country)";
P1 = this.cmdInsCity.CreateParameter();
P1.DbType = DbType.String;
P1.ParameterName = "@name";
P1.SourceColumn = "name";
this.cmdInsCity.Parameters.Add(P1);
P1 = this.cmdInsCity.CreateParameter();
P1.DbType = DbType.Int32;
P1.ParameterName = "@id_country";
P1.SourceColumn = "id_country";
this.cmdInsCity.Parameters.Add(P1);
this.cmdUpdCity = this.cn.CreateCommand();
this.cmdUpdCity.CommandText = "UPDATE Cities SET name = @name, id_country = @id_country WHERE id = @id";
P1 = this.cmdUpdCity.CreateParameter();
P1.DbType = DbType.String;
P1.ParameterName = "@name";
P1.SourceColumn = "name";
this.cmdUpdCity.Parameters.Add(P1);
P1 = this.cmdUpdCity.CreateParameter();
P1.DbType = DbType.Int32;
P1.ParameterName = "@id_country";
P1.SourceColumn = "id_country";
this.cmdUpdCity.Parameters.Add(P1);
P1 = this.cmdUpdCity.CreateParameter();
P1.DbType = DbType.Int32;
P1.ParameterName = "@id";
P1.SourceColumn = "id";
this.cmdUpdCity.Parameters.Add(P1);
DataRelation DR1 = new DataRelation("CityCountryRelation", this.DS.Tables["Countries"].Columns["id"], this.DS.Tables["Cities"].Columns["id_country"]);
this.DS.Relations.Add(DR1);
ForeignKeyConstraint FKC = DR1.ChildKeyConstraint;
FKC.DeleteRule = Rule.None;
FKC.UpdateRule = Rule.None;
cbcol = new DataGridViewComboBoxColumn();
cbcol.Name = "Country";
cbcol.FlatStyle = FlatStyle.Flat;
cbcol.DataSource = this.DS.Tables["Countries"];
cbcol.ValueMember = "id";
cbcol.DisplayMember = "name";
cbcol.DataPropertyName = "id_country";
this.DS.Tables["Countries"].TableNewRow += Form1_TableNewRow;
this.toolStripComboBox1.SelectedIndex = 0;
}
public void ParentColumns()
{
var ds = new DataSet();
DataTable dtChild = DataProvider.CreateChildDataTable();
DataTable dtParent = 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);
// ParentColumns 1
Assert.Equal(1, dRel.ParentColumns.Length);
// ParentColumns 2
Assert.Equal(dtParent.Columns[0], dRel.ParentColumns[0]);
}
// Load data to DataGrids and set Master-Detail
private void loadData()
{
try {
// Create DataSet
DataSet data = new DataSet();
data.Locale = System.Globalization.CultureInfo.InvariantCulture;
// Add data
string cmdBuildsString;
string cmdComponentsString;
SqlCommand cmdMaster;
SqlCommand cmdDetails;
if (LoginData.havePermission("SHOW_BUILDS", LoginData.AccessControl.YES))
{
cmdBuildsString = "SELECT * FROM vBuilds";
cmdComponentsString = "SELECT * FROM vBuildsComponents";
cmdMaster = new SqlCommand(cmdBuildsString, SqlConn.Connection);
cmdDetails = new SqlCommand(cmdComponentsString, SqlConn.Connection);
}
else
{
//("SHOW_BUILDS", LoginData.AccessControl.ONLY_OWN)
cmdBuildsString = "SELECT * FROM vBuilds WHERE bldCreatorID = @pID";
cmdComponentsString = "SELECT * FROM vBuildsComponents WHERE bldUserID = @pID";
cmdMaster = new SqlCommand(cmdBuildsString, SqlConn.Connection);
{
cmdMaster.Parameters.Add("@pID", SqlDbType.Int);
cmdMaster.Parameters["@pID"].Value = LoginData.GetUserID();
}
cmdDetails = new SqlCommand(cmdComponentsString, SqlConn.Connection);
{
cmdDetails.Parameters.Add("@pID", SqlDbType.Int);
cmdDetails.Parameters["@pID"].Value = LoginData.GetUserID();
}
}
// Fill master - dgvBuilds
SqlDataAdapter masterDataAdapter = new SqlDataAdapter();
masterDataAdapter.SelectCommand = cmdMaster;
masterDataAdapter.Fill(data, "v_builds");
// Fill details - dgvComponents
SqlDataAdapter detailsDataAdapter = new SqlDataAdapter();
detailsDataAdapter.SelectCommand = cmdDetails;
detailsDataAdapter.Fill(data, "v_components");
// Establish a relationship between the two tables.
DataRelation relation = new DataRelation("build_components",
data.Tables["v_builds"].Columns["bldID"],
data.Tables["v_components"].Columns["blcBuildID"]);
data.Relations.Add(relation);
// Bind the master data connector to the Customers table.
bnsBuilds.DataSource = data;
bnsBuilds.DataMember = "v_builds";
// Bind the details data connector to the master data connector,
// using the DataRelation name to filter the information in the
// details table based on the current row in the master table.
bnsComponents.DataSource = bnsBuilds;
bnsComponents.DataMember = "build_components";
}
catch (SqlException ex) {
MessageBox.Show("Sql error: " + ex.ToString());
}
}
public void ctor_ByNameDataColumns()
{
var ds = new DataSet();
DataTable dtChild = DataProvider.CreateChildDataTable();
DataTable dtParent = 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);
// DataRelation - CTor
Assert.Equal(false, dRel == null);
// DataRelation - parent Constraints
Assert.Equal(1, dtParent.Constraints.Count);
// DataRelation - child Constraints
Assert.Equal(1, dtChild.Constraints.Count);
// DataRelation - child relations
Assert.Equal(dRel, dtParent.ChildRelations[0]);
// DataRelation - parent relations
Assert.Equal(dRel, dtChild.ParentRelations[0]);
// DataRelation - name
Assert.Equal("MyRelation", dRel.RelationName);
// DataRelation - parent UniqueConstraint
Assert.Equal(typeof(UniqueConstraint), dtParent.Constraints[0].GetType());
// DataRelation - Child ForeignKeyConstraint
Assert.Equal(typeof(ForeignKeyConstraint), dtChild.Constraints[0].GetType());
ds.Relations.Clear();
// Remove DataRelation - Parent Constraints
Assert.Equal(1, dtParent.Constraints.Count);
// Remove DataRelation - Child Constraints
Assert.Equal(1, dtChild.Constraints.Count);
// Remove DataRelation - child relations
Assert.Equal(0, dtParent.ChildRelations.Count);
// Remove DataRelation - parent relations
Assert.Equal(0, dtChild.ParentRelations.Count);
//add relation which will create invalid constraint
dtChild.Constraints.Clear();
dtParent.Constraints.Clear();
//add duplicated row
dtParent.Rows.Add(dtParent.Rows[0].ItemArray);
dRel = new DataRelation("MyRelation", dtParent.Columns[0], dtChild.Columns[0]);
// Add relation which will create invalid constraint
Assert.Throws<ArgumentException>(() =>
{
ds.Relations.Add(dRel);
});
}
// Create a 'DataSet' with two tables and populate it.
private void MakeDataSet()
{
// Create a 'DataSet'.
myDataSet = new DataSet("myDataSet");
// Create two 'DataTables'.
DataTable tCust = new DataTable("Customers");
DataTable tOrders = new DataTable("Orders");
// Create two columns, and add them to the first table.
DataColumn cCustID = new DataColumn("CustID", typeof(int));
DataColumn cCustName = new DataColumn("CustName");
DataColumn cCurrent = new DataColumn("Current", typeof(bool));
tCust.Columns.Add(cCustID);
tCust.Columns.Add(cCustName);
tCust.Columns.Add(cCurrent);
// Map 'myDataGridTableStyle' to 'Customers' table.
myDataGridTableStyle.MappingName = "Customers";
// Add the DataGridTableStyle objects to the collection.
myDataGrid.TableStyles.Add(myDataGridTableStyle);
// Create three columns and add them to the second table.
DataColumn cID =
new DataColumn("CustID", typeof(int));
DataColumn cOrderDate =
new DataColumn("OrderDate", typeof(DateTime));
DataColumn cOrderAmount =
new DataColumn("OrderAmount", typeof(string));
tOrders.Columns.Add(cID);
tOrders.Columns.Add(cOrderAmount);
tOrders.Columns.Add(cOrderDate);
// Add the tables to the 'DataSet'.
myDataSet.Tables.Add(tCust);
myDataSet.Tables.Add(tOrders);
// Create a 'DataRelation' and add it to the 'DataSet'.
DataRelation dr = new DataRelation
("custToOrders", cCustID, cID);
myDataSet.Relations.Add(dr);
// Populate the tables.
// Create two 'DataRow' variables for customer and order.
DataRow newRow1;
DataRow newRow2;
// Create three customers in the 'Customers Table'.
for (int i = 1; i < 4; i++)
{
newRow1 = tCust.NewRow();
newRow1["custID"] = i;
// Add the row to the 'Customers Table'.
tCust.Rows.Add(newRow1);
}
// Give each customer a distinct name.
tCust.Rows[0]["custName"] = "Customer1";
tCust.Rows[1]["custName"] = "Customer2";
tCust.Rows[2]["custName"] = "Customer3";
// Give the current column a value.
tCust.Rows[0]["Current"] = true;
tCust.Rows[1]["Current"] = true;
tCust.Rows[2]["Current"] = false;
// For each customer, create five rows in the orders table.
double myNumber = 0;
string myString;
for (int i = 1; i < 4; i++)
{
for (int j = 1; j < 6; j++)
{
newRow2 = tOrders.NewRow();
newRow2["CustID"] = i;
newRow2["orderDate"] = new DateTime(2001, i, j * 2);
myNumber = i * 10 + j * .1;
myString = "$ ";
myString += myNumber.ToString();
newRow2["OrderAmount"] = myString;
// Add the row to the orders table.
tOrders.Rows.Add(newRow2);
}
}
}
public void ctor_ByNameDataColumnsArraysCreateConstraints()
{
DataRelation dRel;
DataTable dtChild = DataProvider.CreateChildDataTable();
DataTable dtParent = DataProvider.CreateParentDataTable();
var ds = new DataSet();
ds.Tables.Add(dtParent);
ds.Tables.Add(dtChild);
//parameter createConstraints = true
bool createConstraints = true;
for (int i = 0; i <= 1; i++)
{
if (i == 0)
createConstraints = false;
else
createConstraints = true;
ds.Relations.Clear();
dtParent.Constraints.Clear();
dtChild.Constraints.Clear();
//add duplicated row
dtParent.Rows.Add(dtParent.Rows[0].ItemArray);
dRel = new DataRelation("MyRelation", new DataColumn[] { dtParent.Columns[0] }, new DataColumn[] { dtChild.Columns[0] }, createConstraints);
// Add relation which will create invalid constraint
if (createConstraints == true)
{
Assert.Throws<ArgumentException>(() => ds.Relations.Add(dRel));
}
else
ds.Relations.Add(dRel);
ds.Relations.Clear();
dtParent.Constraints.Clear();
dtChild.Constraints.Clear();
dtParent.Rows.Remove(dtParent.Rows[dtParent.Rows.Count - 1]);
dRel = new DataRelation("MyRelation", new DataColumn[] { dtParent.Columns[0] }, new DataColumn[] { dtChild.Columns[0] }, createConstraints);
ds.Relations.Add(dRel);
// DataRelation - CTor,createConstraints=
Assert.Equal(false, dRel == null);
// DataRelation - parent Constraints,createConstraints=
Assert.Equal(i, dtParent.Constraints.Count);
// DataRelation - child Constraints,createConstraints=
Assert.Equal(i, dtChild.Constraints.Count);
// DataRelation - child relations,createConstraints=
Assert.Equal(dRel, dtParent.ChildRelations[0]);
// DataRelation - parent relations,createConstraints=
Assert.Equal(dRel, dtChild.ParentRelations[0]);
// DataRelation - name
Assert.Equal("MyRelation", dRel.RelationName);
}
}
public void InvalidConstraintException()
{
// Parent Columns and Child Columns don't have type-matching columns.
DataRelation Relation = new DataRelation("Rel", Mom.Columns [1], Child.Columns [1], true);
}
public void nested()
{
var ds = new DataSet();
DataTable dtChild = DataProvider.CreateChildDataTable();
DataTable dtParent = 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);
// Nested default
Assert.Equal(false, dRel.Nested);
dRel.Nested = true;
// Nested get/set
Assert.Equal(true, dRel.Nested);
}
public void CollectionChanged()
{
DataSet ds = getDataSet();
ds.Relations.CollectionChanged += new CollectionChangeEventHandler(Relations_CollectionChanged);
DataRelation rel = new DataRelation("rel1", ds.Tables[0].Columns["ParentId"]
, ds.Tables[1].Columns["ParentId"]);
ds.Relations.Add(rel);
ds.Relations.Remove(rel);
Assert.Equal(2, _changesCounter);
}
private void InitTables()
{
#region Gender
DataColumn genderColumn = new DataColumn("GenderID");
genderColumn.ReadOnly = true;
genderColumn.AllowDBNull = false;
genderColumn.DataType = typeof(System.Int32);
genderColumn.Caption = "№";
GenderDataTable.Columns.Add(genderColumn);
GenderDataTable.PrimaryKey = new DataColumn[1] {
genderColumn
};
genderColumn = new DataColumn("Name");
genderColumn.AllowDBNull = false;
genderColumn.DataType = typeof(System.String);
genderColumn.MaxLength = 1;
genderColumn.Caption = "Пол";
GenderDataTable.Columns.Add(genderColumn);
DispancerDataSet.Tables.Add(GenderDataTable);
#endregion Gender
#region APPP Date Table
DataColumn apppColumn = new DataColumn("APPPTPRID");
apppColumn.ReadOnly = true;
apppColumn.AllowDBNull = false;
apppColumn.AutoIncrement = true;
apppColumn.AutoIncrementSeed = -1;
apppColumn.AutoIncrementStep = -1;
apppColumn.Caption = "№";
apppColumn.DataType = typeof(System.Int32);
ApppDataTable.Columns.Add(apppColumn);
ApppDataTable.PrimaryKey = new[] { apppColumn };
apppColumn = new DataColumn("Name");
apppColumn.ReadOnly = false;
apppColumn.AllowDBNull = false;
apppColumn.DataType = typeof(string);
apppColumn.MaxLength = 5;
apppColumn.Caption = "АППП/ТПР";
ApppDataTable.Columns.Add(apppColumn);
DispancerDataSet.Tables.Add(ApppDataTable);
#endregion APPP Date Table
#region CustomerTable
DataColumn customerID = new DataColumn("CustomerID");
customerID.AllowDBNull = false;
customerID.AutoIncrement = true;
customerID.AutoIncrementSeed = -1;
customerID.AutoIncrementStep = -1;
customerID.Unique = true;
customerID.ReadOnly = false;
customerID.DataType = typeof(System.Int32);
customerID.Caption = "№ пациента";
CustomerDataTable.Columns.Add(customerID);
DataColumn medCard = new DataColumn("MedCard");
medCard.AllowDBNull = true;
medCard.ReadOnly = false;
medCard.DataType = typeof(System.Int32);
medCard.Caption = "№ медкарты";
CustomerDataTable.Columns.Add(medCard);
DataColumn cardCustomer = new DataColumn("CodeCustomer");
cardCustomer.AllowDBNull = true;
cardCustomer.ReadOnly = false;
cardCustomer.DataType = typeof(Int32);
cardCustomer.Caption = "Код пациента";
CustomerDataTable.Columns.Add(cardCustomer);
DataColumn lastName = new DataColumn("LastName");
lastName.AllowDBNull = false;
lastName.ReadOnly = false;
lastName.DataType = typeof(String);
lastName.MaxLength = 100;
lastName.Caption = "Фамилия";
CustomerDataTable.Columns.Add(lastName);
DataColumn firstName = new DataColumn("FirstName");
firstName.AllowDBNull = false;
firstName.ReadOnly = false;
firstName.DataType = typeof(string);
firstName.MaxLength = 100;
firstName.Caption = "Имя";
CustomerDataTable.Columns.Add(firstName);
DataColumn middleName = new DataColumn("MiddleName");
middleName.AllowDBNull = true;
middleName.ReadOnly = false;
middleName.DataType = typeof(string);
middleName.MaxLength = 100;
middleName.Caption = "Отчество";
CustomerDataTable.Columns.Add(middleName);
DataColumn birthDay = new DataColumn("Birthday");
birthDay.AllowDBNull = true;
birthDay.ReadOnly = false;
birthDay.DataType = typeof(DateTime);
birthDay.Caption = "День рождения";
CustomerDataTable.Columns.Add(birthDay);
DataColumn age = new DataColumn("Arch");
age.AllowDBNull = false;
age.ReadOnly = false;
age.DataType = typeof(bool);
age.DefaultValue = false;
age.Caption = "Архив";
CustomerDataTable.Columns.Add(age);
DataColumn apppTprID = new DataColumn("APPPTPRID");
apppTprID.ReadOnly = false;
apppTprID.AllowDBNull = true;
apppTprID.DataType = typeof(System.Int32);
apppTprID.Caption = "АППП/ТПР";
CustomerDataTable.Columns.Add(apppTprID);
apppTprID = new DataColumn("GenderID");
apppTprID.ReadOnly = false;
apppTprID.AllowDBNull = true;
apppTprID.DataType = typeof(System.Int32);
apppTprID.Caption = "Пол";
CustomerDataTable.Columns.Add(apppTprID);
DataColumn notaBene = new DataColumn("NotaBene");
notaBene.AllowDBNull = true;
notaBene.DataType = typeof(System.String);
notaBene.Caption = "Доп. сведения";
CustomerDataTable.Columns.Add(notaBene);
CustomerDataTable.PrimaryKey = new[] { customerID };
DispancerDataSet.Tables.Add(_customerDataTable);
ForeignKeyConstraint fkc = new ForeignKeyConstraint("FK_Customer_APPPTPR_APPPTPRID",
ApppDataTable.Columns[0],
CustomerDataTable.Columns["APPPTPRID"]);
CustomerDataTable.Constraints.Add(fkc);
fkc.AcceptRejectRule = AcceptRejectRule.None;
fkc.DeleteRule = Rule.SetNull;
fkc.UpdateRule = Rule.Cascade;
DataRelation relation = new DataRelation("FK_Customer_APPPTPR_APPPTPRID", ApppDataTable.Columns[0],
CustomerDataTable.Columns["APPPTPRID"], true);
DispancerDataSet.Relations.Add(relation);
relation.ChildKeyConstraint.AcceptRejectRule = AcceptRejectRule.None;
relation.ChildKeyConstraint.DeleteRule = Rule.SetNull;
relation.ChildKeyConstraint.UpdateRule = Rule.Cascade;
fkc = new ForeignKeyConstraint("FK_Customer_Gender_GenderID", GenderDataTable.Columns[0],
CustomerDataTable.Columns["GenderID"]);
CustomerDataTable.Constraints.Add(fkc);
fkc.AcceptRejectRule = AcceptRejectRule.None;
fkc.DeleteRule = Rule.SetDefault;
fkc.UpdateRule = Rule.Cascade;
relation = new DataRelation("FK_Customer_Gender_GenderID", GenderDataTable.Columns[0],
CustomerDataTable.Columns["GenderID"], true);
DispancerDataSet.Relations.Add(relation);
relation.ChildKeyConstraint.AcceptRejectRule = AcceptRejectRule.None;
relation.ChildKeyConstraint.DeleteRule = Rule.SetDefault;
relation.ChildKeyConstraint.UpdateRule = Rule.Cascade;
DataColumn errorID = new DataColumn("CustomerID");
customerID.AllowDBNull = false;
//customerID.AutoIncrement = true;
//customerID.AutoIncrementSeed = -1;
//customerID.AutoIncrementStep = -1;
errorID.Unique = false;
errorID.ReadOnly = false;
errorID.DataType = typeof(System.Int32);
errorID.Caption = "CustomerID";
ErrorDataTable.Columns.Add(errorID);
DataColumn flmColumn = new DataColumn("FML");
flmColumn.AllowDBNull = true;
flmColumn.DataType = typeof(System.String);
flmColumn.Caption = "ФИО";
ErrorDataTable.Columns.Add(flmColumn);
DataColumn errorColumn = new DataColumn("Error");
errorColumn.AllowDBNull = true;
errorColumn.DataType = typeof(System.String);
errorColumn.Caption = "Ошибка";
ErrorDataTable.Columns.Add(errorColumn);
DispancerDataSet.Tables.Add(ErrorDataTable);
fkc = new ForeignKeyConstraint("FK_Error_Customer_CustomerID", CustomerDataTable.Columns[0],
ErrorDataTable.Columns["CustomerID"]);
ErrorDataTable.Constraints.Add(fkc);
fkc.AcceptRejectRule = AcceptRejectRule.None;
fkc.DeleteRule = Rule.Cascade;
fkc.UpdateRule = Rule.Cascade;
relation = new DataRelation("FK_Error_Customer_CustomerID", CustomerDataTable.Columns[0],
ErrorDataTable.Columns["CustomerID"], true);
DispancerDataSet.Relations.Add(relation);
relation.ChildKeyConstraint.AcceptRejectRule = AcceptRejectRule.None;
relation.ChildKeyConstraint.DeleteRule = Rule.Cascade;
relation.ChildKeyConstraint.UpdateRule = Rule.Cascade;
#endregion CustomerTable
#region AdminDivision Table
DataColumn adminDivisionColumn = new DataColumn("AdminDivisionID");
adminDivisionColumn.ReadOnly = true;
adminDivisionColumn.AllowDBNull = false;
adminDivisionColumn.AutoIncrementSeed = -1;
adminDivisionColumn.AutoIncrementStep = -1;
adminDivisionColumn.AutoIncrement = true;
adminDivisionColumn.DataType = typeof(Int32);
adminDivisionColumn.Caption = "№";
AdminDivisionDataTable.Columns.Add(adminDivisionColumn);
AdminDivisionDataTable.PrimaryKey = new[] { adminDivisionColumn };
adminDivisionColumn = new DataColumn("Name");
adminDivisionColumn.ReadOnly = false;
adminDivisionColumn.AllowDBNull = false;
adminDivisionColumn.DataType = typeof(string);
adminDivisionColumn.MaxLength = 30;
adminDivisionColumn.Caption = "Полное наименование";
AdminDivisionDataTable.Columns.Add(adminDivisionColumn);
adminDivisionColumn = new DataColumn("SocrName");
adminDivisionColumn.ReadOnly = false;
adminDivisionColumn.AllowDBNull = false;
adminDivisionColumn.DataType = typeof(string);
adminDivisionColumn.MaxLength = 10;
adminDivisionColumn.Caption = "Сокращенное наименование";
AdminDivisionDataTable.Columns.Add(adminDivisionColumn);
DispancerDataSet.Tables.Add(AdminDivisionDataTable);
#endregion AdminDivision Table
#region TypeStreet Table
DataColumn typeStreetColumn = new DataColumn("TypeStreetID");
typeStreetColumn.ReadOnly = true;
typeStreetColumn.AllowDBNull = false;
typeStreetColumn.AutoIncrementSeed = -1;
typeStreetColumn.AutoIncrementStep = -1;
typeStreetColumn.AutoIncrement = true;
typeStreetColumn.DataType = typeof(Int32);
TypeStreetDataTable.Columns.Add(typeStreetColumn);
TypeStreetDataTable.PrimaryKey = new[] { typeStreetColumn };
typeStreetColumn = new DataColumn("Name");
typeStreetColumn.ReadOnly = false;
typeStreetColumn.AllowDBNull = false;
typeStreetColumn.DataType = typeof(string);
typeStreetColumn.MaxLength = 30;
typeStreetColumn.Caption = "Наименование";
TypeStreetDataTable.Columns.Add(typeStreetColumn);
typeStreetColumn = new DataColumn("SocrName");
typeStreetColumn.ReadOnly = false;
typeStreetColumn.AllowDBNull = false;
typeStreetColumn.DataType = typeof(string);
typeStreetColumn.MaxLength = 10;
typeStreetColumn.Caption = "Сокращенное наименование";
TypeStreetDataTable.Columns.Add(typeStreetColumn);
DispancerDataSet.Tables.Add(TypeStreetDataTable);
#endregion TypeStreet Table
#region AddressTable
DataColumn addressColumn = new DataColumn("AddressID");
addressColumn.ReadOnly = false;
addressColumn.AllowDBNull = false;
addressColumn.AutoIncrement = true;
addressColumn.AutoIncrementSeed = -1;
addressColumn.AutoIncrementStep = -1;
addressColumn.DataType = typeof(Int32);
AddressDataTable.Columns.Add(addressColumn);
AddressDataTable.PrimaryKey = new DataColumn[1] {
addressColumn
};
addressColumn = new DataColumn("Region");
addressColumn.ReadOnly = false;
addressColumn.AllowDBNull = true;
addressColumn.DataType = typeof(string);
addressColumn.MaxLength = 50;
addressColumn.Caption = "Регион";
AddressDataTable.Columns.Add(addressColumn);
addressColumn = new DataColumn("Contry");
addressColumn.ReadOnly = false;
addressColumn.AllowDBNull = true;
addressColumn.DataType = typeof(string);
addressColumn.MaxLength = 50;
addressColumn.Caption = "Страна";
AddressDataTable.Columns.Add(addressColumn);
addressColumn = new DataColumn("City");
addressColumn.ReadOnly = false;
addressColumn.AllowDBNull = true;
addressColumn.DataType = typeof(string);
addressColumn.MaxLength = 100;
addressColumn.Caption = "Населённый пункт";
AddressDataTable.Columns.Add(addressColumn);
addressColumn = new DataColumn("AdminDivisionID");
addressColumn.ReadOnly = false;
addressColumn.AllowDBNull = false;
addressColumn.DataType = typeof(Int32);
addressColumn.Caption = "Тип НП";
AddressDataTable.Columns.Add(addressColumn);
addressColumn = new DataColumn("TypeStreetID");
addressColumn.ReadOnly = false;
addressColumn.AllowDBNull = true;
addressColumn.DataType = typeof(Int32);
addressColumn.Caption = "Тип улицы";
AddressDataTable.Columns.Add(addressColumn);
addressColumn = new DataColumn("NameStreet");
addressColumn.ReadOnly = false;
addressColumn.AllowDBNull = true;
addressColumn.DataType = typeof(string);
addressColumn.MaxLength = 100;
addressColumn.Caption = "Название улицы";
AddressDataTable.Columns.Add(addressColumn);
addressColumn = new DataColumn("NumberHouse");
addressColumn.ReadOnly = false;
addressColumn.AllowDBNull = true;
addressColumn.DataType = typeof(string);
addressColumn.MaxLength = 10;
addressColumn.Caption = "№ Дома";
AddressDataTable.Columns.Add(addressColumn);
addressColumn = new DataColumn("NumberApartment");
addressColumn.ReadOnly = false;
addressColumn.AllowDBNull = true;
addressColumn.DataType = typeof(string);
addressColumn.MaxLength = 10;
addressColumn.Caption = "№ квартиры";
AddressDataTable.Columns.Add(addressColumn);
addressColumn = new DataColumn("CustomerID");
addressColumn.ReadOnly = false;
addressColumn.AllowDBNull = false;
addressColumn.DataType = typeof(Int32);
AddressDataTable.Columns.Add(addressColumn);
this.DispancerDataSet.Tables.Add(AddressDataTable);
fkc = new ForeignKeyConstraint("FK_Address_Customer_CustomerID",
CustomerDataTable.Columns[0], AddressDataTable.Columns["CustomerID"]);
AddressDataTable.Constraints.Add(fkc);
fkc.AcceptRejectRule = AcceptRejectRule.None;
fkc.DeleteRule = Rule.Cascade;
fkc.UpdateRule = Rule.Cascade;
relation = new DataRelation("FK_Address_Customer_CustomerID", CustomerDataTable.Columns[0],
AddressDataTable.Columns["CustomerID"], true);
this.DispancerDataSet.Relations.Add(relation);
relation.ChildKeyConstraint.AcceptRejectRule = AcceptRejectRule.None;
relation.ChildKeyConstraint.DeleteRule = Rule.Cascade;
relation.ChildKeyConstraint.UpdateRule = Rule.Cascade;
fkc = new ForeignKeyConstraint("FK_Address_AdminDivision_AdminDivisionID",
AdminDivisionDataTable.Columns[0], AddressDataTable.Columns["AdminDivisionID"]);
AddressDataTable.Constraints.Add(fkc);
fkc.AcceptRejectRule = AcceptRejectRule.None;
fkc.DeleteRule = Rule.Cascade;
fkc.UpdateRule = Rule.Cascade;
relation = new DataRelation("FK_Address_AdminDivision_AdminDivisionID",
AdminDivisionDataTable.Columns[0], AddressDataTable.Columns["AdminDivisionID"], true);
this.DispancerDataSet.Relations.Add(relation);
relation.ChildKeyConstraint.AcceptRejectRule = AcceptRejectRule.None;
relation.ChildKeyConstraint.DeleteRule = Rule.Cascade;
relation.ChildKeyConstraint.UpdateRule = Rule.Cascade;
fkc = new ForeignKeyConstraint("FK_Address_TypeStreet_TypeStreetID",
TypeStreetDataTable.Columns[0], AddressDataTable.Columns["TypeStreetID"]);
AddressDataTable.Constraints.Add(fkc);
fkc.AcceptRejectRule = AcceptRejectRule.None;
fkc.DeleteRule = Rule.SetNull;
fkc.UpdateRule = Rule.Cascade;
relation = new DataRelation("FK_Address_TypeStreet_TypeStreetID",
TypeStreetDataTable.Columns[0], AddressDataTable.Columns["TypeStreetID"], true);
this.DispancerDataSet.Relations.Add(relation);
relation.ChildKeyConstraint.AcceptRejectRule = AcceptRejectRule.None;
relation.ChildKeyConstraint.DeleteRule = Rule.SetNull;
relation.ChildKeyConstraint.UpdateRule = Rule.Cascade;
#endregion AddressTable
#region Register Tables
#region RegisterTable
DataColumn regColumn = new DataColumn("RegisterID");
regColumn.ReadOnly = false;
regColumn.AllowDBNull = false;
regColumn.AutoIncrement = true;
regColumn.AutoIncrementSeed = -1;
regColumn.AutoIncrementStep = -1;
regColumn.DataType = typeof(Int32);
regColumn.Caption = "№";
RegisterDataTable.Columns.Add(regColumn);
RegisterDataTable.PrimaryKey = new DataColumn[1] {
regColumn
};
regColumn = new DataColumn("FirstRegister");
regColumn.ReadOnly = false;
regColumn.AllowDBNull = true;
regColumn.DataType = typeof(DateTime);
regColumn.Caption = "1-й раз взят на регистрацию";
RegisterDataTable.Columns.Add(regColumn);
regColumn = new DataColumn("FirstDeregister");
regColumn.ReadOnly = false;
regColumn.AllowDBNull = true;
regColumn.DataType = typeof(DateTime);
regColumn.Caption = "1-й раз снят с регистрации";
RegisterDataTable.Columns.Add(regColumn);
regColumn = new DataColumn("SecondRegister");
regColumn.ReadOnly = false;
regColumn.AllowDBNull = true;
regColumn.DataType = typeof(DateTime);
regColumn.Caption = "Повторно взят на регистрацию";
RegisterDataTable.Columns.Add(regColumn);
regColumn = new DataColumn("SecondDeRegister");
regColumn.ReadOnly = false;
regColumn.AllowDBNull = true;
regColumn.DataType = typeof(DateTime);
regColumn.Caption = "Повторно снят с регистрации";
RegisterDataTable.Columns.Add(regColumn);
regColumn = new DataColumn("Diagnosis");
regColumn.ReadOnly = false;
regColumn.AllowDBNull = true;
regColumn.DataType = typeof(string);
regColumn.MaxLength = 10;
regColumn.Caption = "Диагноз";
RegisterDataTable.Columns.Add(regColumn);
regColumn = new DataColumn("DataDiagnosis");
regColumn.ReadOnly = false;
regColumn.AllowDBNull = true;
regColumn.DataType = typeof(DateTime);
regColumn.Caption = "Дата диагноза";
RegisterDataTable.Columns.Add(regColumn);
regColumn = new DataColumn("RegisterTypeID");
regColumn.ReadOnly = false;
regColumn.AllowDBNull = true;
regColumn.DataType = typeof(Int32);
regColumn.Caption = "Причина взятия на учёт";
RegisterDataTable.Columns.Add(regColumn);
regColumn = new DataColumn("CustomerID");
regColumn.ReadOnly = false;
regColumn.AllowDBNull = false;
regColumn.DataType = typeof(Int32);
RegisterDataTable.Columns.Add(regColumn);
regColumn = new DataColumn("WhyDeRegisterID");
regColumn.ReadOnly = false;
regColumn.AllowDBNull = true;
regColumn.DataType = typeof(Int32);
regColumn.Caption = "Причина снятия с учёта";
RegisterDataTable.Columns.Add(regColumn);
regColumn = new DataColumn("WhySecondDeRegisterID");
regColumn.ReadOnly = false;
regColumn.AllowDBNull = true;
regColumn.DataType = typeof(Int32);
regColumn.Caption = "Причина повторного снятия с учёт";
RegisterDataTable.Columns.Add(regColumn);
regColumn = new DataColumn("SecondRegisterTypeID");
regColumn.ReadOnly = false;
regColumn.AllowDBNull = true;
regColumn.DataType = typeof(Int32);
regColumn.Caption = "Причина повторного взятия на учёт";
RegisterDataTable.Columns.Add(regColumn);
regColumn = new DataColumn("LandID");
regColumn.ReadOnly = false;
regColumn.AllowDBNull = false;
regColumn.Caption = "№ участка";
regColumn.DataType = typeof(Int32);
RegisterDataTable.Columns.Add(regColumn);
regColumn = new DataColumn("NotaBene");
regColumn.ReadOnly = false;
regColumn.AllowDBNull = true;
regColumn.DataType = typeof(string);
regColumn.MaxLength = 2000;
regColumn.Caption = "Дополнительно";
RegisterDataTable.Columns.Add(regColumn);
DispancerDataSet.Tables.Add(RegisterDataTable);
fkc = new ForeignKeyConstraint("FK_Register_Customer_CustomerID",
CustomerDataTable.Columns[0], RegisterDataTable.Columns["CustomerID"]);
RegisterDataTable.Constraints.Add(fkc);
fkc.AcceptRejectRule = AcceptRejectRule.None;
fkc.DeleteRule = Rule.Cascade;
fkc.UpdateRule = Rule.Cascade;
relation = new DataRelation("FK_Register_Customer_CustomerID", CustomerDataTable.Columns[0],
RegisterDataTable.Columns["CustomerID"], false);
this.DispancerDataSet.Relations.Add(relation);
#endregion RegisterTable
#region Land Table
DataColumn landColumn = new DataColumn("LandID");
landColumn.ReadOnly = true;
landColumn.AllowDBNull = false;
landColumn.AutoIncrementSeed = -1;
landColumn.AutoIncrementStep = -1;
landColumn.AutoIncrement = true;
landColumn.DataType = typeof(Int32);
LandDataTable.Columns.Add(landColumn);
LandDataTable.PrimaryKey = new[] { landColumn };
landColumn = new DataColumn("Name");
landColumn.ReadOnly = false;
landColumn.AllowDBNull = false;
landColumn.DataType = typeof(string);
landColumn.Caption = "№ участка";
landColumn.MaxLength = 50;
LandDataTable.Columns.Add(landColumn);
this.DispancerDataSet.Tables.Add(_landDataTable);
fkc = new ForeignKeyConstraint("FK_Register_Land_LandID", LandDataTable.Columns[0],
RegisterDataTable.Columns["LandID"]);
RegisterDataTable.Constraints.Add(fkc);
fkc.AcceptRejectRule = AcceptRejectRule.None;
fkc.DeleteRule = Rule.SetDefault;
fkc.UpdateRule = Rule.Cascade;
relation = new DataRelation("FK_Register_Land_LandID", LandDataTable.Columns[0],
RegisterDataTable.Columns["LandID"], false);
DispancerDataSet.Relations.Add(relation);
#endregion Land Table
#region RegisterType Table
DataColumn regTypeColumn = new DataColumn("RegisterTypeID");
regTypeColumn.ReadOnly = false;
regTypeColumn.AllowDBNull = false;
regTypeColumn.AutoIncrementSeed = -1;
regTypeColumn.AutoIncrementStep = -1;
regTypeColumn.AutoIncrement = true;
regTypeColumn.DataType = typeof(Int32);
RegisterTypeDataTable.Columns.Add(regTypeColumn);
RegisterTypeDataTable.PrimaryKey = new[] { regTypeColumn };
regTypeColumn = new DataColumn("Name");
regTypeColumn.ReadOnly = false;
regTypeColumn.AllowDBNull = false;
regTypeColumn.DataType = typeof(string);
regTypeColumn.MaxLength = 50;
regTypeColumn.Caption = "Тип регистации";
RegisterTypeDataTable.Columns.Add(regTypeColumn);
regTypeColumn = new DataColumn("NotaBene");
regTypeColumn.ReadOnly = false;
regTypeColumn.AllowDBNull = true;
regTypeColumn.DataType = typeof(string);
regTypeColumn.MaxLength = 2000;
RegisterTypeDataTable.Columns.Add(regTypeColumn);
this.DispancerDataSet.Tables.Add(RegisterTypeDataTable);
fkc = new ForeignKeyConstraint("FK_Register_RegisterType_RegisterTypeID", RegisterTypeDataTable.Columns[0],
RegisterDataTable.Columns["RegisterTypeID"]);
RegisterDataTable.Constraints.Add(fkc);
fkc.AcceptRejectRule = AcceptRejectRule.Cascade;
fkc.DeleteRule = Rule.SetNull;
fkc.UpdateRule = Rule.Cascade;
relation = new DataRelation("FK_Register_RegisterType_RegisterTypeID", RegisterTypeDataTable.Columns[0],
RegisterDataTable.Columns["RegisterTypeID"], false);
DispancerDataSet.Relations.Add(relation);
//fkc = new ForeignKeyConstraint("FK_Register_RegisterType_SecondRegisterTypeID", RegisterTypeDataTable.Columns[0],
// RegisterDataTable.Columns["SecondRegisterTypeID"]);
//RegisterDataTable.Constraints.Add(fkc);
//fkc.AcceptRejectRule = AcceptRejectRule.None;
//fkc.DeleteRule = Rule.None;
//fkc.UpdateRule = Rule.None;
relation = new DataRelation("FK_Register_RegisterType_SecondRegisterTypeID", RegisterTypeDataTable.Columns[0],
RegisterDataTable.Columns["SecondRegisterTypeID"], true);
DispancerDataSet.Relations.Add(relation);
#endregion RegisterType Table
#region WhyDeRegister Table
DataColumn whyDeRegColumn = new DataColumn("WhyDeRegisterID");
whyDeRegColumn.ReadOnly = true;
whyDeRegColumn.AllowDBNull = false;
whyDeRegColumn.AutoIncrementSeed = -1;
whyDeRegColumn.AutoIncrementStep = -1;
whyDeRegColumn.AutoIncrement = true;
whyDeRegColumn.DataType = typeof(Int32);
WhyDeRegisterDataTable.Columns.Add(whyDeRegColumn);
WhyDeRegisterDataTable.PrimaryKey = new[] { whyDeRegColumn };
whyDeRegColumn = new DataColumn("Name");
whyDeRegColumn.ReadOnly = false;
whyDeRegColumn.AllowDBNull = false;
whyDeRegColumn.DataType = typeof(string);
whyDeRegColumn.MaxLength = 50;
whyDeRegColumn.Caption = "Причина снятия с учёта";
WhyDeRegisterDataTable.Columns.Add(whyDeRegColumn);
whyDeRegColumn = new DataColumn("NotaBene");
whyDeRegColumn.ReadOnly = false;
whyDeRegColumn.AllowDBNull = true;
whyDeRegColumn.DataType = typeof(string);
whyDeRegColumn.MaxLength = 2000;
WhyDeRegisterDataTable.Columns.Add(whyDeRegColumn);
this.DispancerDataSet.Tables.Add(WhyDeRegisterDataTable);
fkc = new ForeignKeyConstraint("FK_Register_WhyDeRegister_WhyDeRegisterID",
WhyDeRegisterDataTable.Columns[0], RegisterDataTable.Columns["WhyDeRegisterID"]);
RegisterDataTable.Constraints.Add(fkc);
fkc.AcceptRejectRule = AcceptRejectRule.Cascade;
fkc.DeleteRule = Rule.SetNull;
fkc.UpdateRule = Rule.Cascade;
relation = new DataRelation("FK_Register_WhyDeRegister_WhyDeRegisterID", WhyDeRegisterDataTable.Columns[0],
RegisterDataTable.Columns["WhyDeRegisterID"], false);
DispancerDataSet.Relations.Add(relation);
//fkc = new ForeignKeyConstraint("FK_Register_WhyDeRegister_WhySecondDeRegisterID",
// WhyDeRegisterDataTable.Columns[0], RegisterDataTable.Columns["WhySecondDeRegisterID"]);
//RegisterDataTable.Constraints.Add(fkc);
//fkc.AcceptRejectRule = AcceptRejectRule.None;
//fkc.DeleteRule = Rule.None;
//fkc.UpdateRule = Rule.None;
relation = new DataRelation("FK_Register_WhyDeRegister_WhySecondDeRegisterID",
WhyDeRegisterDataTable.Columns[0], RegisterDataTable.Columns["WhySecondDeRegisterID"], true);
DispancerDataSet.Relations.Add(relation);
#endregion WhyDeRegister Table
#endregion Register Tables
#region Invalid Tables
#region ChiperRecept Table
DataColumn chiperColumn = new DataColumn("ChiperReceptID");
chiperColumn.ReadOnly = false;
chiperColumn.AllowDBNull = false;
chiperColumn.AutoIncrementSeed = -1;
chiperColumn.AutoIncrementStep = -1;
chiperColumn.AutoIncrement = true;
chiperColumn.DataType = typeof(Int32);
ChiperReceptDataTable.Columns.Add(chiperColumn);
ChiperReceptDataTable.PrimaryKey = new[] { chiperColumn };
chiperColumn = new DataColumn("Name");
chiperColumn.ReadOnly = false;
chiperColumn.AllowDBNull = false;
chiperColumn.DataType = typeof(string);
chiperColumn.MaxLength = 50;
chiperColumn.Caption = "Шифр рецепта";
ChiperReceptDataTable.Columns.Add(chiperColumn);
chiperColumn = new DataColumn("NotaBene");
chiperColumn.ReadOnly = false;
chiperColumn.AllowDBNull = true;
chiperColumn.DataType = typeof(string);
chiperColumn.MaxLength = 150;
chiperColumn.Caption = "Доп сведения";
ChiperReceptDataTable.Columns.Add(chiperColumn);
DispancerDataSet.Tables.Add(ChiperReceptDataTable);
#endregion ChiperRecept Table
#region DisabilityGroup Table
DataColumn disabilityColumn = new DataColumn("DisabilityGroupID");
disabilityColumn.ReadOnly = false;
disabilityColumn.AllowDBNull = false;
disabilityColumn.AutoIncrementSeed = -1;
disabilityColumn.AutoIncrementStep = -1;
disabilityColumn.AutoIncrement = true;
disabilityColumn.DataType = typeof(Int32);
DisabilityGroupDataTable.Columns.Add(disabilityColumn);
DisabilityGroupDataTable.PrimaryKey = new[] { disabilityColumn };
disabilityColumn = new DataColumn("Name");
disabilityColumn.ReadOnly = false;
disabilityColumn.AllowDBNull = false;
disabilityColumn.DataType = typeof(string);
disabilityColumn.MaxLength = 100;
disabilityColumn.Caption = "Название Группы инвалидности";
DisabilityGroupDataTable.Columns.Add(disabilityColumn);
disabilityColumn = new DataColumn("NotaBene");
disabilityColumn.ReadOnly = false;
disabilityColumn.AllowDBNull = true;
disabilityColumn.DataType = typeof(string);
disabilityColumn.MaxLength = 100;
chiperColumn.Caption = "Доп сведения";
DisabilityGroupDataTable.Columns.Add(disabilityColumn);
DispancerDataSet.Tables.Add(DisabilityGroupDataTable);
#endregion DisabilityGroup Table
#region Benefits Table
DataColumn benefitsColumn = new DataColumn("BenefitsCategoryID");
benefitsColumn.ReadOnly = true;
benefitsColumn.AllowDBNull = false;
benefitsColumn.AutoIncrementSeed = -1;
benefitsColumn.AutoIncrementStep = -1;
benefitsColumn.AutoIncrement = true;
benefitsColumn.DataType = typeof(Int32);
BenefitsDataTable.Columns.Add(benefitsColumn);
BenefitsDataTable.PrimaryKey = new[] { benefitsColumn };
benefitsColumn = new DataColumn("Name");
benefitsColumn.ReadOnly = false;
benefitsColumn.AllowDBNull = false;
benefitsColumn.DataType = typeof(string);
benefitsColumn.MaxLength = 100;
benefitsColumn.Caption = "Название льготной категории";
BenefitsDataTable.Columns.Add(benefitsColumn);
benefitsColumn = new DataColumn("NotaBene");
benefitsColumn.ReadOnly = false;
benefitsColumn.AllowDBNull = true;
benefitsColumn.DataType = typeof(string);
benefitsColumn.MaxLength = 100;
chiperColumn.Caption = "Доп сведения";
BenefitsDataTable.Columns.Add(benefitsColumn);
DispancerDataSet.Tables.Add(BenefitsDataTable);
#endregion Benefits Table
#region InvalidTable
DataColumn invalidColumn = new DataColumn("InvalidID");
invalidColumn.ReadOnly = false;
invalidColumn.AllowDBNull = false;
invalidColumn.AutoIncrement = true;
invalidColumn.AutoIncrementSeed = -1;
invalidColumn.AutoIncrementStep = -1;
invalidColumn.DataType = typeof(Int32);
InvalidDataTable.Columns.Add(invalidColumn);
InvalidDataTable.PrimaryKey = new DataColumn[1] {
invalidColumn
};
invalidColumn = new DataColumn("DisabilityGroupID");
invalidColumn.ReadOnly = false;
invalidColumn.AllowDBNull = true;
invalidColumn.DataType = typeof(Int32);
invalidColumn.Caption = "ГШруппа инвалидности";
InvalidDataTable.Columns.Add(invalidColumn);
invalidColumn = new DataColumn("DataInvalidity");
invalidColumn.ReadOnly = false;
invalidColumn.AllowDBNull = true;
invalidColumn.DataType = typeof(DateTime);
invalidColumn.Caption = "Дата инвалидности";
InvalidDataTable.Columns.Add(invalidColumn);
invalidColumn = new DataColumn("PeriodInvalidity");
invalidColumn.ReadOnly = false;
invalidColumn.AllowDBNull = true;
invalidColumn.DataType = typeof(DateTime);
invalidColumn.Caption = "Период инвалидности";
InvalidDataTable.Columns.Add(invalidColumn);
invalidColumn = new DataColumn("ChiperReceptID");
invalidColumn.ReadOnly = false;
invalidColumn.AllowDBNull = true;
invalidColumn.DataType = typeof(Int32);
invalidColumn.Caption = "Шифр рецепта";
InvalidDataTable.Columns.Add(invalidColumn);
invalidColumn = new DataColumn("Incapable");
invalidColumn.ReadOnly = false;
invalidColumn.AllowDBNull = false;
invalidColumn.DataType = typeof(bool);
invalidColumn.Caption = "Недееспособен";
InvalidDataTable.Columns.Add(invalidColumn);
invalidColumn = new DataColumn("DateIncapable");
invalidColumn.ReadOnly = false;
invalidColumn.AllowDBNull = true;
invalidColumn.DataType = typeof(DateTime);
invalidColumn.Caption = "Дата недееспособности";
InvalidDataTable.Columns.Add(invalidColumn);
invalidColumn = new DataColumn("CustomerID");
invalidColumn.ReadOnly = false;
invalidColumn.AllowDBNull = false;
invalidColumn.DataType = typeof(Int32);
InvalidDataTable.Columns.Add(invalidColumn);
this.DispancerDataSet.Tables.Add(InvalidDataTable);
fkc = new ForeignKeyConstraint("FK_Invalid_Customer_CustomerID",
CustomerDataTable.Columns[0], InvalidDataTable.Columns["CustomerID"]);
InvalidDataTable.Constraints.Add(fkc);
fkc.AcceptRejectRule = AcceptRejectRule.None;
fkc.DeleteRule = Rule.Cascade;
fkc.UpdateRule = Rule.Cascade;
relation = new DataRelation("FK_Invalid_Customer_CustomerID", CustomerDataTable.Columns[0],
InvalidDataTable.Columns["CustomerID"], false);
this.DispancerDataSet.Relations.Add(relation);
fkc = new ForeignKeyConstraint("FK_Invalid_ChiperRecept_ChiperReceptID",
ChiperReceptDataTable.Columns[0], InvalidDataTable.Columns["ChiperReceptID"]);
InvalidDataTable.Constraints.Add(fkc);
fkc.AcceptRejectRule = AcceptRejectRule.None;
fkc.DeleteRule = Rule.SetNull;
fkc.UpdateRule = Rule.Cascade;
relation = new DataRelation("FK_Invalid_ChiperRecept_ChiperReceptID",
ChiperReceptDataTable.Columns[0], InvalidDataTable.Columns["ChiperReceptID"], false);
this.DispancerDataSet.Relations.Add(relation);
fkc = new ForeignKeyConstraint("FK_Invalid_DisabilityGroup_DisabilityGroupID",
DisabilityGroupDataTable.Columns[0], InvalidDataTable.Columns["DisabilityGroupID"]);
InvalidDataTable.Constraints.Add(fkc);
fkc.AcceptRejectRule = AcceptRejectRule.None;
fkc.DeleteRule = Rule.SetNull;
fkc.UpdateRule = Rule.Cascade;
relation = new DataRelation("FK_Invalid_DisabilityGroup_DisabilityGroupID",
DisabilityGroupDataTable.Columns[0], InvalidDataTable.Columns["DisabilityGroupID"], false);
this.DispancerDataSet.Relations.Add(relation);
#endregion InvalidTable
#region InvalidBenefits Table
DataColumn column = new DataColumn("InvID");
column.AllowDBNull = false;
column.DataType = typeof(Int32);
InvalidBenefitsDataTable.Columns.Add(column);
column = new DataColumn("BenefitsID");
column.DataType = typeof(Int32);
column.AllowDBNull = false;
InvalidBenefitsDataTable.Columns.Add(column);
InvalidBenefitsDataTable.PrimaryKey = new DataColumn[]
{ InvalidBenefitsDataTable.Columns[0], InvalidBenefitsDataTable.Columns[1] };
DispancerDataSet.Tables.Add(InvalidBenefitsDataTable);
fkc = new ForeignKeyConstraint("FK_InvalidBenefitsCategory_BenefitsCategory_BenefitsCategoryID",
BenefitsDataTable.Columns[0], InvalidBenefitsDataTable.Columns["BenefitsID"]);
InvalidBenefitsDataTable.Constraints.Add(fkc);
fkc.AcceptRejectRule = AcceptRejectRule.None;
fkc.DeleteRule = Rule.Cascade;
fkc.UpdateRule = Rule.Cascade;
//relation = new DataRelation("FK_InvalidBenefitsCategory_BenefitsCategory_BenefitsCategoryID",
// BenefitsDataTable.Columns[0], InvalidBenefitsDataTable.Columns["BenefitsID"], false);
//this.DispancerDataSet.Relations.Add(relation);
fkc = new ForeignKeyConstraint("FK_InvalidBenefitsCategory_Invalid_InvID",
InvalidDataTable.Columns[0], InvalidBenefitsDataTable.Columns["InvID"]);
InvalidBenefitsDataTable.Constraints.Add(fkc);
fkc.AcceptRejectRule = AcceptRejectRule.None;
fkc.DeleteRule = Rule.Cascade;
fkc.UpdateRule = Rule.Cascade;
//relation = new DataRelation("FK_InvalidBenefitsCategory_Invalid_InvID",
// InvalidDataTable.Columns[0], InvalidBenefitsDataTable.Columns["InvID"], false);
//this.DispancerDataSet.Relations.Add(relation);
#endregion InvalidBenefits Table
#endregion Invalid Tables
CustomerDataTable.ExtendedProperties.Add("Customer", "CustomerData");
RegisterDataTable.ExtendedProperties.Add("Customer", "CustomerData");
InvalidDataTable.ExtendedProperties.Add("Customer", "CustomerData");
AddressDataTable.ExtendedProperties.Add("Customer", "CustomerData");
}
public void Creation2()
{
DataSet Set = new DataSet();
DataTable Mom2 = new DataTable("Mom");
DataTable Child2 = new DataTable("Child");
DataTable Hubby = new DataTable("Hubby");
Set.Tables.Add(Mom2);
Set.Tables.Add(Child2);
Set.Tables.Add(Hubby);
DataColumn Col = new DataColumn("Name");
DataColumn Col2 = new DataColumn("ChildName");
DataColumn Col3 = new DataColumn("hubby");
Mom2.Columns.Add(Col);
Mom2.Columns.Add(Col2);
Mom2.Columns.Add(Col3);
DataColumn Col4 = new DataColumn("Name");
DataColumn Col5 = new DataColumn("Age");
DataColumn Col6 = new DataColumn("father");
Child2.Columns.Add(Col4);
Child2.Columns.Add(Col5);
Child2.Columns.Add(Col6);
DataColumn Col7 = new DataColumn("Name");
DataColumn Col8 = new DataColumn("Age");
Hubby.Columns.Add(Col7);
Hubby.Columns.Add(Col8);
DataColumn [] Parents = new DataColumn [2];
Parents [0] = Col2;
Parents [1] = Col3;
DataColumn [] Childs = new DataColumn [2];
Childs [0] = Col4;
Childs [1] = Col7;
DataRelation Relation = null;
try {
Relation = new DataRelation("Rel", Parents, Childs);
Assert.Fail("test#01");
} catch (InvalidConstraintException e) {
// Assert.That (e.GetType (), Is.EqualTo(typeof (InvalidConstraintException)), "test#02");
// Assert.That (e.Message, Is.EqualTo("Cannot create a Key from Columns that belong to different tables."), "test#03");
}
Childs [1] = Col6;
Relation = new DataRelation("Rel", Parents, Childs);
Set.Relations.Add(Relation);
DataRelation Test = null;
Assert.That(Mom2.ChildRelations.Count, Is.EqualTo(1), "test#01");
Assert.That(Child2.ChildRelations.Count, Is.EqualTo(0), "test#02");
Assert.That(Mom2.ParentRelations.Count, Is.EqualTo(0), "test#03");
Assert.That(Child2.ParentRelations.Count, Is.EqualTo(1), "test#04");
Test = Child2.ParentRelations [0];
Assert.That(Test.ToString(), Is.EqualTo("Rel"), "test#05");
Assert.That(Test.RelationName, Is.EqualTo("Rel"), "test#06");
Assert.That(Test.ParentTable.TableName, Is.EqualTo("Mom"), "test#07");
Assert.That(Test.ParentKeyConstraint.Columns.Length, Is.EqualTo(2), "test#08");
Assert.That(Test.ParentKeyConstraint.IsPrimaryKey, Is.False, "test#09");
Assert.That(Test.ParentColumns.Length, Is.EqualTo(2), "test#10");
Assert.That(Test.Nested, Is.False, "test#11");
Assert.That(Test.ExtendedProperties.Count, Is.EqualTo(0), "test#12");
Assert.That(Test.ChildTable.TableName, Is.EqualTo("Child"), "test#13");
Assert.That(Test.ChildKeyConstraint.ConstraintName, Is.EqualTo("Rel"), "test#14");
Assert.That(Test.ChildColumns.Length, Is.EqualTo(2), "test#15");
Assert.That(Mom2.Constraints.Count, Is.EqualTo(1), "test#16");
Assert.That(Mom2.Constraints [0].ToString(), Is.EqualTo("Constraint1"), "test#17");
Assert.That(Child2.Constraints.Count, Is.EqualTo(1), "test#18");
Assert.That(Hubby.Constraints.Count, Is.EqualTo(0), "test#19");
}
