public static void AddForeignKey(this ITransformationProvider provider, string foreignTable, string primaryTable,
ForeignKeyConstraint constraint = ForeignKeyConstraint.NoAction)
{
provider.AddForeignKey(foreignTable.ForeignKeyNameTo(primaryTable),
foreignTable,
primaryTable.ForeignId(),
primaryTable,
"Id",
constraint);
}
public void RelatedColumns()
{
var ds = new DataSet();
DataTable dtParent = DataProvider.CreateParentDataTable();
DataTable dtChild = DataProvider.CreateChildDataTable();
ds.Tables.Add(dtParent);
ds.Tables.Add(dtChild);
ForeignKeyConstraint fc = null;
fc = new ForeignKeyConstraint(dtParent.Columns[0], dtChild.Columns[0]);
// RelatedColumns
Assert.Equal(new DataColumn[] { dtParent.Columns[0] }, fc.RelatedColumns);
}
public string SqlForConstraint(ForeignKeyConstraint constraint) {
switch (constraint) {
case ForeignKeyConstraint.Cascade:
return "CASCADE";
case ForeignKeyConstraint.Restrict:
return "RESTRICT";
case ForeignKeyConstraint.SetDefault:
return "SET DEFAULT";
case ForeignKeyConstraint.SetNull:
return "SET NULL";
default:
return "NO ACTION";
}
}
public void Columns()
{
//int RowCount;
var ds = new DataSet();
DataTable dtParent = DataProvider.CreateParentDataTable();
DataTable dtChild = DataProvider.CreateChildDataTable();
ds.Tables.Add(dtParent);
ds.Tables.Add(dtChild);
ForeignKeyConstraint fc = null;
fc = new ForeignKeyConstraint(dtParent.Columns[0], dtChild.Columns[0]);
// Columns
Assert.Equal(dtChild.Columns[0], fc.Columns[0]);
// Columns count
Assert.Equal(1, fc.Columns.Length);
}
public void KeyBetweenColumns()
{
DataTable Table = _ds.Tables[0];
Assert.Equal(0, Table.Constraints.Count);
Table = _ds.Tables[1];
Assert.Equal(0, Table.Constraints.Count);
ForeignKeyConstraint Constraint = new ForeignKeyConstraint("Test", _ds.Tables[0].Columns[0], _ds.Tables[0].Columns[2]);
Table = _ds.Tables[0];
Table.Constraints.Add(Constraint);
Assert.Equal(2, Table.Constraints.Count);
Assert.Equal("Constraint1", Table.Constraints[0].ConstraintName);
Assert.Equal(typeof(UniqueConstraint), Table.Constraints[0].GetType());
Assert.Equal("Test", Table.Constraints[1].ConstraintName);
Assert.Equal(typeof(ForeignKeyConstraint), Table.Constraints[1].GetType());
}
public void KeyBetweenColumns()
{
DataTable Table = _ds.Tables [0];
AssertEquals("test#01", 0, Table.Constraints.Count);
Table = _ds.Tables [1];
AssertEquals("test#02", 0, Table.Constraints.Count);
ForeignKeyConstraint Constraint = new ForeignKeyConstraint("Test", _ds.Tables [0].Columns [0], _ds.Tables [0].Columns [2]);
Table = _ds.Tables [0];
Table.Constraints.Add(Constraint);
AssertEquals("test#03", 2, Table.Constraints.Count);
AssertEquals("test#04", "Constraint1", Table.Constraints [0].ConstraintName);
AssertEquals("test#05", typeof(UniqueConstraint), Table.Constraints [0].GetType());
AssertEquals("test#04", "Test", Table.Constraints [1].ConstraintName);
AssertEquals("test#05", typeof(ForeignKeyConstraint), Table.Constraints [1].GetType());
}
public string SqlForConstraint(ForeignKeyConstraint constraint)
{
switch (constraint)
{
case ForeignKeyConstraint.Cascade:
return("CASCADE");
case ForeignKeyConstraint.Restrict:
return("RESTRICT");
case ForeignKeyConstraint.SetDefault:
return("SET DEFAULT");
case ForeignKeyConstraint.SetNull:
return("SET NULL");
default:
return("NO ACTION");
}
}
// <Snippet1>
private void CreateConstraint(DataSet dataSet)
{
// Create the ForignKeyConstraint with two DataColumn objects.
DataColumn parentCol = dataSet.Tables["Customers"].Columns["id"];
DataColumn childCol = dataSet.Tables["Orders"].Columns["OrderID"];
ForeignKeyConstraint fkeyConstraint =
new ForeignKeyConstraint("fkConstraint", parentCol, childCol);
// Test against existing members using the Equals method.
foreach (ForeignKeyConstraint testConstraint in
dataSet.Tables["Orders"].Constraints)
{
if (fkeyConstraint.Equals(testConstraint))
{
Console.WriteLine("Identical ForeignKeyConstraint!");
// Insert code to delete the duplicate object,
// or stop the procedure.
}
}
}
public static void AssertForeignKeyConstraint(string label,
ForeignKeyConstraint fk, string name,
AcceptRejectRule acceptRejectRule, Rule delRule, Rule updateRule,
string[] colNames, string[] relColNames)
{
Assert.Equal(name, fk.ConstraintName);
Assert.Equal(acceptRejectRule, fk.AcceptRejectRule);
Assert.Equal(delRule, fk.DeleteRule);
Assert.Equal(updateRule, fk.UpdateRule);
for (int i = 0; i < colNames.Length; i++)
{
Assert.Equal(colNames[i], fk.Columns[i].ColumnName);
}
Assert.Equal(colNames.Length, fk.Columns.Length);
for (int i = 0; i < relColNames.Length; i++)
{
Assert.Equal(relColNames[i], fk.RelatedColumns[i].ColumnName);
}
Assert.Equal(relColNames.Length, fk.RelatedColumns.Length);
}
private static string GetFieldName(SqlException e, DataTable dt)
{
//The UPDATE statement conflicted with the REFERENCE constraint \"fk_OPS_AC11_Acp21_AC\". The conflict occurred in database \"C:\\A1-DOCS\\C_NET\\KLONS1\\DB\\KLONS.MDF\", table \"dbo.OPS\", column 'AC11'.\r\nThe statement has been terminated. dv.Row.RejectChanges();
string s = "";
int k1, k2;
if (e.Message.IndexOf(_sqlExceoptionStr1) >= 0)
{
k1 = _sqlExceoptionStr1.Length + 2;
k2 = e.Message.IndexOf(". The conflict occurred in database");
s = e.Message.Substring(k1, k2 - k1 - 1);
}
if (!dt.Constraints.Contains(s))
{
return("");
}
ForeignKeyConstraint fk = dt.Constraints[s] as ForeignKeyConstraint;
return(fk.Columns[0].Caption);
}
public void UpdateRule2()
{
Assert.Throws <ConstraintException>(() =>
{
DataSet ds = GetNewDataSet();
ds.Tables[0].PrimaryKey = null;
ForeignKeyConstraint fc = new ForeignKeyConstraint(ds.Tables[0].Columns[0], ds.Tables[1].Columns[0]);
fc.UpdateRule = Rule.None;
ds.Tables[1].Constraints.Add(fc);
//Changing parent row
ds.Tables[0].Rows[0]["ParentId"] = 5;
/*ds.Tables[0].AcceptChanges();
* ds.Tables[1].AcceptChanges();
* //Checking the table
* Compare(ds.Tables[1].Select("ParentId=8").Length ,0);*/
});
}
public void Columns()
{
//int RowCount;
var ds = new DataSet();
DataTable dtParent = DataProvider.CreateParentDataTable();
DataTable dtChild = DataProvider.CreateChildDataTable();
ds.Tables.Add(dtParent);
ds.Tables.Add(dtChild);
ForeignKeyConstraint fc = null;
fc = new ForeignKeyConstraint(dtParent.Columns[0], dtChild.Columns[0]);
// Columns
Assert.Equal(dtChild.Columns[0], fc.Columns[0]);
// Columns count
Assert.Equal(1, fc.Columns.Length);
}
public void AddRange()
{
_constraint1.ConstraintName = "UK1";
_constraint2.ConstraintName = "UK12";
var _constraint3 = new ForeignKeyConstraint("FK2", _table.Columns[0], _table2.Columns[0]);
var _constraint4 = new UniqueConstraint("UK2", _table2.Columns[1]);
// Add the constraints.
Constraint[] constraints = { _constraint1, _constraint2 };
_table.Constraints.AddRange(constraints);
Constraint[] constraints1 = { _constraint3, _constraint4 };
_table2.Constraints.AddRange(constraints1);
Assert.Equal("UK1", _table.Constraints[0].ConstraintName);
Assert.Equal("UK12", _table.Constraints[1].ConstraintName);
Assert.Equal("FK2", _table2.Constraints[0].ConstraintName);
Assert.Equal("UK2", _table2.Constraints[1].ConstraintName);
}
private void Form1_Load(object sender, EventArgs e)
{
string connectionString = @"Data Source=.\SQLEXPRESS; Initial Catalog=ShopDB; Integrated Security=True;"; // создание строки подключения
DataSet ds = new DataSet();
DataTable customers = new DataTable("Customers");
DataTable orders = new DataTable("Orders");
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
SqlCommand customersCmd = new SqlCommand("SELECT CustomerNo, LName, FName, Address1, Phone FROM Customers", connection);
SqlCommand ordersCmd = new SqlCommand("SELECT OrderID, CustomerNo, OrderDate FROM Orders", connection);
SqlDataReader ordersReader = ordersCmd.ExecuteReader(); // получение DataReader для таблицы OrderDetails
// метод LoadWithSchema позволяет на основе объекта DataReader создать объект DataTable
//с ограничениями для столбцов как в базе данных и заполнить эту таблицу данными
orders.LoadWithSchema(ordersReader);
ordersReader.Close();
SqlDataReader customersReader = customersCmd.ExecuteReader();
customers.LoadWithSchema(customersReader);
customersReader.Close();
}
// объект DataReader не имеет информации об ограничениях объектов DataTable, таких как
// UniqueConstraint, ForeignKeyConstraint и PrimaryKey, поэтому прийдется их создать вручную
customers.PrimaryKey = new DataColumn[] { customers.Columns[0] };
ds.Tables.AddRange(new DataTable[] { customers, orders });
// создание ограничения ForeignKeyConstraint для таблицы OrderDetails
var FK_CustomersOrders = new ForeignKeyConstraint(customers.Columns["CustomerNo"], orders.Columns["CustomerNo"]);
orders.Constraints.Add(FK_CustomersOrders);
parentGridView.DataSource = customers; // связывание элемента управления parentGridView с таблицей Products
childDataGridView.DataSource = orders; // Связывание элемента управления childDataGridView c таблицей OrderDetails
}
static void Main(string[] args)
{
DataSet shopDB = new DataSet("ShopDB");
DataTable orders = new DataTable("Orders");
DataTable customers = new DataTable("Customers");
string conStr = @"Data Source=(localdb)\MSSQLLocalDB; Initial Catalog=ShopDB; Integrated Security=True;"; // создание строки подключения
using (SqlConnection connection = new SqlConnection(conStr))
{
connection.Open();
SqlCommand customersCmd = new SqlCommand("SELECT CustomerNo, LName, FName, Address1, Phone FROM Customers", connection);
SqlCommand ordersCmd = new SqlCommand("SELECT OrderID, CustomerNo, OrderDate FROM Orders", connection);
SqlDataReader ordersReader = ordersCmd.ExecuteReader(); // получение DataReader для таблицы OrderDetails
// метод LoadWithSchema позволяет на основе объекта DataReader создать объект DataTable
//с ограничениями для столбцов как в базе данных и заполнить эту таблицу данными
orders.LoadWithSchema(ordersReader);
ordersReader.Close();
SqlDataReader customersReader = customersCmd.ExecuteReader();
customers.LoadWithSchema(customersReader);
customersReader.Close();
}
customers.PrimaryKey = new DataColumn[] { customers.Columns[0] };
shopDB.Tables.AddRange(new DataTable[] { customers, orders });
// создание ограничения ForeignKeyConstraint для таблицы OrderDetails
var FK_CustomersOrders = new ForeignKeyConstraint(customers.Columns["CustomerNo"], orders.Columns["CustomerNo"]);
orders.Constraints.Add(FK_CustomersOrders);
Console.ReadKey();
}
private void RelateAndBind()
{
ForeignKeyConstraint cs = new ForeignKeyConstraint("StudExam", ds.Tables[0].Columns[0], ds.Tables[1].Columns[1]);
cs.DeleteRule = Rule.Cascade;
cs.UpdateRule = Rule.Cascade;
ds.Tables[1].Constraints.Add(cs);
ds.EnforceConstraints = true;
ds.Relations.Add(new DataRelation("ArtistAlbum", ds.Tables[0].Columns[0], ds.Tables[1].Columns[1]));
ds.Relations.Add(new DataRelation("AlbumTrack", ds.Tables[1].Columns[0], ds.Tables[2].Columns[1]));
bsArtist = new BindingSource(ds, ds.Tables[0].TableName);
bsAlbum = new BindingSource(bsArtist, ds.Relations[0].RelationName);
bsTrack = new BindingSource(bsAlbum, ds.Relations[1].RelationName);
gridArtist.DataSource = bsArtist;
gridAlbum.DataSource = bsAlbum;
gridTracklist.DataSource = bsTrack;
bn.BindingSource = bsArtist;
}
internal static SchemaException EstablishingRelationError(ForeignKeyConstraint fk, string source, string helpLink)
{
SchemaException schemaException = new SchemaException(SyncResource.FormatString("ErrorEstablishingRelation", new object[1]
{
(object)fk.ConstraintName
}));
schemaException.SyncSource = source;
schemaException.SyncStage = SyncStage.CreatingSchema;
schemaException.ErrorNumber = SyncErrorNumber.InvalidArguments;
schemaException.HelpLink = helpLink;
if (fk.RelatedTable != null)
{
schemaException.TableName = fk.RelatedTable.TableName;
}
SyncTracer.Warning("{0}", new object[1]
{
(object)schemaException
});
return(schemaException);
}
public void constraintName()
{
var ds = new DataSet();
DataTable dtParent = DataProvider.CreateParentDataTable();
DataTable dtChild = DataProvider.CreateChildDataTable();
ds.Tables.Add(dtParent);
ds.Tables.Add(dtChild);
ForeignKeyConstraint fc = null;
fc = new ForeignKeyConstraint(dtParent.Columns[0], dtChild.Columns[0]);
// default
Assert.Equal(string.Empty, fc.ConstraintName);
fc.ConstraintName = "myConstraint";
// set/get
Assert.Equal("myConstraint", fc.ConstraintName);
}
//Activate This Construntor to log All To Standard output
//public TestClass():base(true){}
//Activate this constructor to log Failures to a log file
//public TestClass(System.IO.TextWriter tw):base(tw, false){}
//Activate this constructor to log All to a log file
//public TestClass(System.IO.TextWriter tw):base(tw, true){}
//BY DEFAULT LOGGING IS DONE TO THE STANDARD OUTPUT ONLY FOR FAILURES
public void run()
{
Exception exp = null;
DataSet ds = new DataSet();
DataTable dtParent = GHTUtils.DataProvider.CreateParentDataTable();
DataTable dtChild = GHTUtils.DataProvider.CreateChildDataTable();
ds.Tables.Add(dtParent);
ds.Tables.Add(dtChild);
ForeignKeyConstraint fc = null;
fc = new ForeignKeyConstraint(dtParent.Columns[0], dtChild.Columns[0]);
try
{
BeginCase("Table");
Compare(fc.Table, dtChild);
}
catch (Exception ex) { exp = ex; }
finally { EndCase(exp); exp = null; }
}
private void InitClass()
{
base.DataSetName = "VoterAnswersData";
base.Prefix = "";
base.Namespace = "http://tempuri.org/VoterAnswersData.xsd";
base.Locale = new CultureInfo("en-US");
base.CaseSensitive = false;
base.EnforceConstraints = true;
this.tableVoters = new VotersDataTable();
base.Tables.Add(this.tableVoters);
this.tableVotersAnswers = new VotersAnswersDataTable();
base.Tables.Add(this.tableVotersAnswers);
ForeignKeyConstraint constraint = new ForeignKeyConstraint("VoterAnswersRelation", new DataColumn[] { this.tableVoters.VoterIdColumn }, new DataColumn[] { this.tableVotersAnswers.VoterIdColumn });
this.tableVotersAnswers.Constraints.Add(constraint);
constraint.AcceptRejectRule = AcceptRejectRule.None;
constraint.DeleteRule = Rule.Cascade;
constraint.UpdateRule = Rule.Cascade;
this.relationVoterAnswersRelation = new DataRelation("VoterAnswersRelation", new DataColumn[] { this.tableVoters.VoterIdColumn }, new DataColumn[] { this.tableVotersAnswers.VoterIdColumn }, false);
base.Relations.Add(this.relationVoterAnswersRelation);
}
protected override void OnInsert(int index, object value)
{
base.ValidateType(value);
DesignRelation relation = (DesignRelation)value;
if ((this.dataSource == null) || (relation.Owner != this.dataSource))
{
if ((this.dataSource != null) && (relation.Owner != null))
{
throw new InternalException("This relation belongs to another DataSource already", 0x4e2a);
}
if ((relation.Name == null) || (relation.Name.Length == 0))
{
relation.Name = this.CreateUniqueName(relation);
}
this.ValidateName(relation);
System.Data.DataSet dataSet = this.DataSet;
if (dataSet != null)
{
if (relation.ForeignKeyConstraint != null)
{
ForeignKeyConstraint foreignKeyConstraint = relation.ForeignKeyConstraint;
if (foreignKeyConstraint.Columns.Length > 0)
{
DataTable table = foreignKeyConstraint.Columns[0].Table;
if ((table != null) && !table.Constraints.Contains(foreignKeyConstraint.ConstraintName))
{
table.Constraints.Add(foreignKeyConstraint);
}
}
}
if ((relation.DataRelation != null) && !dataSet.Relations.Contains(relation.DataRelation.RelationName))
{
dataSet.Relations.Add(relation.DataRelation);
}
}
base.OnInsert(index, value);
relation.Owner = this.dataSource;
}
}
/// <summary>
/// UC2:AAbility to create table
/// </summary>
public AddressBookService()
{
AddressBook = new DataTable("AddressBook");
PersonType = new DataTable("ContactType");
Type = new DataTable("Type");
AddressBook.Columns.Add("FirstName", typeof(string));
AddressBook.Columns.Add("LastName", typeof(string));
AddressBook.Columns.Add("Address", typeof(string));
AddressBook.Columns.Add("City", typeof(string));
AddressBook.Columns.Add("State", typeof(string));
AddressBook.Columns.Add("Zip", typeof(string));
AddressBook.Columns.Add("PhoneNumber", typeof(string));
AddressBook.Columns.Add("Email", typeof(string));
AddressBook.Columns.Add("Name", typeof(string));
AddressBook.PrimaryKey = new DataColumn[] { AddressBook.Columns["Name"] };
AddressBookDB.Tables.Add(AddressBook);
DataColumn rd = Type.Columns.Add("TypeID", typeof(int));
rd.AutoIncrement = true;
rd.AutoIncrementSeed = 1;
rd.AutoIncrementStep = 1;
Type.Columns.Add("Type", typeof(string));
Type.Rows.Add(null, "Family");
Type.Rows.Add(null, "Friends");
Type.Rows.Add(null, "Profession");
PersonType.Columns.Add("TypeID", typeof(int));
PersonType.Columns.Add("Name", typeof(string));
AddressBookDB.Tables.Add(Type);
AddressBookDB.Tables.Add(PersonType);
ForeignKeyConstraint foreignKeyOnContactTypeTypeID = new ForeignKeyConstraint(
"foreignKeyOnContactType_TypeID", Type.Columns["TypeID"], PersonType.Columns["TypeID"]);
ForeignKeyConstraint foreignKeyOnContactTypeName = new ForeignKeyConstraint(
"foreignKeyOnContactType_Name", AddressBook.Columns["Name"], PersonType.Columns["Name"]);
PersonType.Constraints.Add(foreignKeyOnContactTypeTypeID);
PersonType.Constraints.Add(foreignKeyOnContactTypeName);
}
public SessionGame(DataSet dataset) : base(dataset)
{
DataColumn dc;
Columns.Add(new DataColumn(NameColumn, typeof(String)));
Columns.Add(dc = new DataColumn("game_part_number", typeof(String)));
dc.MaxLength = 8;
Columns.Add(new DataColumn("ball_timer", typeof(int)));
Columns.Add(new DataColumn("progressive", typeof(bool)));
Columns.Add(new DataColumn("bonanza", typeof(bool)));
Columns.Add(new DataColumn("wild", typeof(bool)));
Columns.Add(new DataColumn("double_wild", typeof(bool)));
Columns.Add(new DataColumn("blind", typeof(bool)));
Columns.Add(new DataColumn("poker", typeof(bool)));
Columns.Add(new DataColumn("special", typeof(bool)));
Columns.Add(new DataColumn("single_hotball", typeof(bool)));
Columns.Add(new DataColumn("callers_choice", typeof(bool)));
Columns.Add(new DataColumn(ColorInfoTable.PrimaryKey, XDataTable.DefaultAutoKeyType));
if (dataset != null)
{
DataTable child;
dataset.Relations.Add(SessionGame.color_name = MySQLDataTable.StripPlural(MySQLDataTable.StripInfo(SessionGame.TableName))
+ "_is_"
+ MySQLDataTable.StripPlural(MySQLDataTable.StripInfo(ColorInfoTable.TableName))
, dataset.Tables[ColorInfoTable.TableName].Columns[ColorInfoTable.PrimaryKey]
, (child = dataset.Tables[SessionGame.TableName]).Columns[ColorInfoTable.PrimaryKey]
);
ForeignKeyConstraint fkc = child.Constraints[color_name] as ForeignKeyConstraint;
if (fkc != null)
{
fkc.DeleteRule = Rule.SetNull;
}
}
number_column = NumberColumn;
// these are event catches to make sure numbers track correctly.
CloneRow += new MySQLRelationTable.OnCloneRow(SessionGame_CloneRow);
AddingRow += new MySQLRelationTable.OnNewRow(initrow);
ColumnChanged += new DataColumnChangeEventHandler(SessionGame_ColumnChanged);
}
public void Ctor1()
{
DataTable Table = _ds.Tables[0];
Assert.Equal(0, Table.Constraints.Count);
Table = _ds.Tables[1];
Assert.Equal(0, Table.Constraints.Count);
// ctor (string, DataColumn, DataColumn
ForeignKeyConstraint Constraint = new ForeignKeyConstraint("test", _ds.Tables[0].Columns[2], _ds.Tables[1].Columns[0]);
Table = _ds.Tables[1];
Table.Constraints.Add(Constraint);
Assert.Equal(1, Table.Constraints.Count);
Assert.Equal("test", Table.Constraints[0].ConstraintName);
Assert.Equal(typeof(ForeignKeyConstraint), Table.Constraints[0].GetType());
Table = _ds.Tables[0];
Assert.Equal(1, Table.Constraints.Count);
Assert.Equal("Constraint1", Table.Constraints[0].ConstraintName);
Assert.Equal(typeof(UniqueConstraint), Table.Constraints[0].GetType());
}
static void Main(string[] args)
{
string connectionString = "Server=DESKTOP-RM1NBDJ;Database=shop;Trusted_Connection=True;";
DataSet shop = new DataSet();
Order order = new Order();
Customer customer = new Customer();
Employee employee = new Employee();
Product product = new Product();
OrderDetail orderDetail = new OrderDetail();
ForeignKeyConstraint OrderInOrderDetails = new ForeignKeyConstraint(orderDetail.OrderDetails.Columns["id"], order.Orders.Columns["orderDetailsId"]);
ForeignKeyConstraint OrderDetailsInProduct = new ForeignKeyConstraint(product.Products.Columns["id"], orderDetail.OrderDetails.Columns["productId"]);
ForeignKeyConstraint OrderDetailsInCustomers = new ForeignKeyConstraint(customer.Customers.Columns["id"], orderDetail.OrderDetails.Columns["customersId"]);
ForeignKeyConstraint OrderDetailsInEmployees = new ForeignKeyConstraint(employee.Employees.Columns["id"], orderDetail.OrderDetails.Columns["employeesId"]);
shop.Tables.Add(order.Orders);
shop.Tables.Add(customer.Customers);
shop.Tables.Add(employee.Employees);
shop.Tables.Add(product.Products);
shop.Tables.Add(orderDetail.OrderDetails);
}
public void EnforceConstraint()
{
int id = 100;
// Setup stuff
DataSet ds = new DataSet();
DataTable parent = ds.Tables.Add("parent");
parent.Columns.Add("id", typeof(int));
DataTable child = ds.Tables.Add("child");
child.Columns.Add("idref", typeof(int));
Constraint uniqueId = null;
parent.Constraints.Add(uniqueId = new UniqueConstraint("uniqueId",
new DataColumn[] { parent.Columns["id"] }, true));
ForeignKeyConstraint fkc = new ForeignKeyConstraint("ParentChildConstraint", new DataColumn[] { parent.Columns["id"] },
new DataColumn[] { child.Columns["idref"] });
child.Constraints.Add(fkc);
DataRelation relateParentChild = new DataRelation("relateParentChild",
new DataColumn[] { parent.Columns["id"] },
new DataColumn[] { child.Columns["idref"] },
false);
ds.Relations.Add(relateParentChild);
ds.EnforceConstraints = false;
DataRow parentRow = parent.Rows.Add(new object[] { id });
DataRow childRow = child.Rows.Add(new object[] { id });
if (parentRow == childRow.GetParentRow(relateParentChild))
{
foreach (DataColumn dc in parent.Columns)
{
AssertEquals(100, parentRow[dc]);
}
}
}
public void Equals()
{
var ds = new DataSet();
DataTable dtParent = DataProvider.CreateParentDataTable();
DataTable dtChild = DataProvider.CreateChildDataTable();
ds.Tables.Add(dtParent);
ds.Tables.Add(dtChild);
dtParent.PrimaryKey = new DataColumn[] { dtParent.Columns[0] };
ds.EnforceConstraints = true;
ForeignKeyConstraint fc1, fc2;
fc1 = new ForeignKeyConstraint(dtParent.Columns[0], dtChild.Columns[0]);
fc2 = new ForeignKeyConstraint(dtParent.Columns[0], dtChild.Columns[1]);
// different columnn
Assert.Equal(false, fc1.Equals(fc2));
//Two System.Data.ForeignKeyConstraint are equal if they constrain the same columns.
// same column
fc2 = new ForeignKeyConstraint(dtParent.Columns[0], dtChild.Columns[0]);
Assert.Equal(true, fc1.Equals(fc2));
}
public void CtorExceptions()
{
ForeignKeyConstraint fkc;
DataTable localTable = new DataTable();
localTable.Columns.Add("Col1", typeof(int));
localTable.Columns.Add("Col2", typeof(bool));
//Null
Assert.Throws <NullReferenceException>(() =>
{
fkc = new ForeignKeyConstraint(null, (DataColumn)null);
});
//zero length collection
AssertExtensions.Throws <ArgumentException>(null, () =>
{
fkc = new ForeignKeyConstraint(new DataColumn[] { }, new DataColumn[] { });
});
//different datasets
Assert.Throws <InvalidOperationException>(() =>
{
fkc = new ForeignKeyConstraint(_ds.Tables[0].Columns[0], localTable.Columns[0]);
});
Assert.Throws <InvalidOperationException>(() =>
{
fkc = new ForeignKeyConstraint(_ds.Tables[0].Columns[0], localTable.Columns[1]);
});
// Cannot create a Key from Columns that belong to
// different tables.
Assert.Throws <InvalidConstraintException>(() =>
{
fkc = new ForeignKeyConstraint(new DataColumn[] { _ds.Tables[0].Columns[0], _ds.Tables[0].Columns[1] }, new DataColumn[] { localTable.Columns[1], _ds.Tables[1].Columns[0] });
});
}
private void Form1_Load(object sender, EventArgs e)
{
const string conStr = @"data source =(local)\SqlExpress; integrated security = true; initial catalog = shopdb;";
var dataSet = new DataSet();
var customers = new DataTable("Customers");
var orders = new DataTable("Orders");
dataSet.Tables.AddRange(new DataTable[] { customers, orders });
using (var connection = new SqlConnection(conStr))
{
connection.Open();
var customerCommand = new SqlCommand("select * from customers", connection);
var orderCommand = new SqlCommand("select * from orders", connection);
using (var customerReader = customerCommand.ExecuteReader())
{
customers.LoadWithSchema(customerReader);
}
using (var orderReader = orderCommand.ExecuteReader())
{
orders.LoadWithSchema(orderReader);
}
}
//DataReader doesn't know if there are constraints in tables => you must add them manually
//customers.Constraints.Add(new UniqueConstraint(customers.Columns[0], true));
customers.PrimaryKey = new DataColumn[] { customers.Columns[0] };
var fk_CustomersOrders = new ForeignKeyConstraint(customers.Columns["CustomerNo"], orders.Columns["CustomerNo"]);
orders.Constraints.Add(fk_CustomersOrders);
dataGridView1.DataSource = customers;
dataGridView2.DataSource = orders;
}
private void OK_btn_Click(object sender, EventArgs e)
{
if (PK_comboBox.SelectedItem.ToString() != "" && PKT_Columns_Box.SelectedItem.ToString() != "" && FK_Columns_Box.SelectedItem.ToString() != "")
{
ForeignKeyConstraint custOrderFK = new ForeignKeyConstraint(
Relationship_txt.Text,
Global.dataSet.Tables[Global.Curtable].Columns[FK_Columns_Box.SelectedItem.ToString()],
Global.dataSet.Tables[PK_comboBox.SelectedItem.ToString()].Columns[PKT_Columns_Box.SelectedItem.ToString()]
);
if (Rule_Box.SelectedIndex == 0)
{
custOrderFK.DeleteRule = Rule.Cascade;
}
else if (Rule_Box.SelectedIndex == 1)
{
custOrderFK.DeleteRule = Rule.SetNull;
}
else if (Rule_Box.SelectedIndex == 2)
{
custOrderFK.DeleteRule = Rule.SetDefault;
}
else if (Rule_Box.SelectedIndex == 3)
{
custOrderFK.DeleteRule = Rule.None;
}
// Cannot delete a customer value that has associated existing orders.
if (Global.dataSet.Tables[PK_comboBox.SelectedItem.ToString()].Constraints.Contains(Relationship_txt.Text))
{
MessageBox.Show("already exist !");
return;
}
Global.dataSet.Tables[PK_comboBox.SelectedItem.ToString()].Constraints.Add(custOrderFK);
MessageBox.Show("Done .");
}
}
static void Main(string[] args)
{
//1 создания базы данных
DataSet studentsDb = new DataSet("StudentsDb");
//2 создание таблиц
DataTable students = new DataTable("Students");
DataTable gender = new DataTable("Gender");
//3 создание полей
InitStudent(ref students);
InitGender(ref gender);
ForeignKeyConstraint FK_Gender = new ForeignKeyConstraint(gender.Columns["Id"], students.Columns["GenderId"]);
//{
// DeleteRule = Rule.Cascade,
// UpdateRule = Rule.Cascade
//};
students.Constraints.Add(FK_Gender);
studentsDb.Tables.AddRange(new DataTable[] { gender, students });
DataRow newRow = gender.NewRow();
newRow["Name"] = "Male";
gender.Rows.Add(newRow);
newRow = gender.NewRow();
newRow["Name"] = "Female";
gender.Rows.Add(newRow);
gender.WriteXml("gender.xml");
InsertStudent(ref students);
students.WriteXml("students.xml");
Console.ReadLine();
}
public static DataSet EstruturaBuscaAux()
{
DataSet ds = new DataSet(DATASET_FAQ);
DataTable tFaqTipo = new DataTable(FAQ_TIPO);
DataTable tFaq = new DataTable(FAQ);
tFaqTipo.Columns.Add(ID, typeof(int)).DefaultValue = 0;
tFaqTipo.Columns.Add(TIPO, typeof(string)).DefaultValue = "-";
tFaqTipo.Columns.Add(EDITAVEL, typeof(bool)).DefaultValue = false;
tFaq.Columns.Add(ID, typeof(int));
tFaq.Columns.Add(FAQ_TIPO_ID, typeof(int)).DefaultValue = 0;
tFaq.Columns.Add(PERGUNTA, typeof(string)).DefaultValue = "-";
tFaq.Columns.Add(TAGS, typeof(string)).DefaultValue = "-";
tFaq.Columns.Add(RESPOSTA, typeof(string)).DefaultValue = "-";
tFaq.Columns.Add(EDITAVEL, typeof(bool)).DefaultValue = true;
tFaq.Columns.Add(CLASSIFICACAO, typeof(int)).DefaultValue = 0;
ds.Tables.Add(tFaqTipo);
ds.Tables.Add(tFaq);
//Grid com a InfoPacote
DataColumn colFaq = tFaq.Columns[FAQ_TIPO_ID];
DataColumn colFaqTipo = tFaqTipo.Columns[ID];
DataRelation dr2 = new DataRelation("FaqTipoXFaq", colFaqTipo, colFaq, true);
ForeignKeyConstraint idKeyRestraint2 = new ForeignKeyConstraint(colFaqTipo, colFaq);
idKeyRestraint2.DeleteRule = Rule.Cascade;
tFaq.Constraints.Add(idKeyRestraint2);
ds.EnforceConstraints = true;
ds.Relations.Add(dr2);
return(ds);
}
public void ParentChildSameColumn()
{
DataTable dataTable = new DataTable("Menu");
DataColumn colID = dataTable.Columns.Add("ID", typeof(int));
DataColumn colCulture = dataTable.Columns.Add("Culture", typeof(string));
dataTable.Columns.Add("Name", typeof(string));
DataColumn colParentID = dataTable.Columns.Add("ParentID", typeof(int));
// table PK (ID, Culture)
dataTable.Constraints.Add(new UniqueConstraint(
"MenuPK",
new DataColumn[] { colID, colCulture },
true));
// add a FK referencing the same table: (ID, Culture) <- (ParentID, Culture)
ForeignKeyConstraint fkc = new ForeignKeyConstraint(
"MenuParentFK",
new DataColumn[] { colID, colCulture },
new DataColumn[] { colParentID, colCulture });
dataTable.Constraints.Add(fkc);
}
public void Ctor2()
{
DataTable Table = _ds.Tables [0];
AssertEquals("test#01", 0, Table.Constraints.Count);
Table = _ds.Tables [1];
AssertEquals("test#02", 0, Table.Constraints.Count);
// ctor (string, DataColumn, DataColumn
ForeignKeyConstraint Constraint = new ForeignKeyConstraint(_ds.Tables [0].Columns [2], _ds.Tables [1].Columns [0]);
Table = _ds.Tables [1];
Table.Constraints.Add(Constraint);
AssertEquals("test#03", 1, Table.Constraints.Count);
AssertEquals("test#04", "Constraint1", Table.Constraints [0].ConstraintName);
AssertEquals("test#05", typeof(ForeignKeyConstraint), Table.Constraints [0].GetType());
Table = _ds.Tables [0];
AssertEquals("test#06", 1, Table.Constraints.Count);
AssertEquals("test#07", "Constraint1", Table.Constraints [0].ConstraintName);
AssertEquals("test#08", typeof(UniqueConstraint), Table.Constraints [0].GetType());
}
public void Ctor2()
{
DataTable Table = _ds.Tables[0];
Assert.Equal(0, Table.Constraints.Count);
Table = _ds.Tables[1];
Assert.Equal(0, Table.Constraints.Count);
// ctor (string, DataColumn, DataColumn
ForeignKeyConstraint Constraint = new ForeignKeyConstraint(_ds.Tables[0].Columns[2], _ds.Tables[1].Columns[0]);
Table = _ds.Tables[1];
Table.Constraints.Add(Constraint);
Assert.Equal(1, Table.Constraints.Count);
Assert.Equal("Constraint1", Table.Constraints[0].ConstraintName);
Assert.Equal(typeof(ForeignKeyConstraint), Table.Constraints[0].GetType());
Table = _ds.Tables[0];
Assert.Equal(1, Table.Constraints.Count);
Assert.Equal("Constraint1", Table.Constraints[0].ConstraintName);
Assert.Equal(typeof(UniqueConstraint), Table.Constraints[0].GetType());
}
/// <summary>
/// Adds the foreign key.
/// </summary>
/// <param name="primaryTableSchemaName">Name of the primary table schema.</param>
/// <param name="primaryTableName">Name of the primary table.</param>
/// <param name="primaryColumn">The primary column.</param>
/// <param name="refTableSchemaName">Name of the reference table schema.</param>
/// <param name="refTabelName">Name of the reference tabel.</param>
/// <param name="refColumn">The reference column.</param>
/// <param name="constraint">The constraint.</param>
public void AddForeignKey(
string primaryTableSchemaName,
string primaryTableName,
string primaryColumn,
string refTableSchemaName,
string refTabelName,
string refColumn,
ForeignKeyConstraint constraint)
=>
AddForeignKey(
primaryTableSchemaName,
primaryTableName,
new[]
{
primaryColumn
},
refTableSchemaName,
refTabelName,
new[]
{
refColumn
},
constraint);
public void AssertForeignKeyConstraint(string label,
ForeignKeyConstraint fk, string name,
AcceptRejectRule acceptRejectRule, Rule delRule, Rule updateRule,
string[] colNames, string[] relColNames)
{
Assert.Equal(name, fk.ConstraintName);
Assert.Equal(acceptRejectRule, fk.AcceptRejectRule);
Assert.Equal(delRule, fk.DeleteRule);
Assert.Equal(updateRule, fk.UpdateRule);
for (int i = 0; i < colNames.Length; i++)
Assert.Equal(colNames[i], fk.Columns[i].ColumnName);
Assert.Equal(colNames.Length, fk.Columns.Length);
for (int i = 0; i < relColNames.Length; i++)
Assert.Equal(relColNames[i], fk.RelatedColumns[i].ColumnName);
Assert.Equal(relColNames.Length, fk.RelatedColumns.Length);
}
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);
}
public void CloneCopy_TestForeignKeyConstraints()
{
DataTable dirTable = new DataTable("Directories");
DataColumn dir_UID = new DataColumn("UID", typeof(int));
dir_UID.Unique = true;
dir_UID.AllowDBNull = false;
dirTable.Columns.Add(dir_UID);
// Build a simple Files table
DataTable fileTable = new DataTable("Files");
DataColumn file_DirID = new DataColumn("DirectoryID", typeof(int));
file_DirID.Unique = false;
file_DirID.AllowDBNull = false;
fileTable.Columns.Add(file_DirID);
// Build the DataSet
DataSet ds = new DataSet("TestDataset");
ds.Tables.Add(dirTable);
ds.Tables.Add(fileTable);
// Add a foreign key constraint
DataColumn[] parentColumns = new DataColumn[1];
parentColumns[0] = ds.Tables["Directories"].Columns["UID"];
DataColumn[] childColumns = new DataColumn[1];
childColumns[0] = ds.Tables["Files"].Columns["DirectoryID"];
ForeignKeyConstraint fk = new ForeignKeyConstraint("FK_Test", parentColumns, childColumns);
ds.Tables["Files"].Constraints.Add(fk);
ds.EnforceConstraints = true;
Assert.Equal(1, ds.Tables["Directories"].Constraints.Count);
Assert.Equal(1, ds.Tables["Files"].Constraints.Count);
// check clone works fine
DataSet cloned_ds = ds.Clone();
Assert.Equal(1, cloned_ds.Tables["Directories"].Constraints.Count);
Assert.Equal(1, cloned_ds.Tables["Files"].Constraints.Count);
ForeignKeyConstraint clonedFk = (ForeignKeyConstraint)cloned_ds.Tables["Files"].Constraints[0];
Assert.Equal("FK_Test", clonedFk.ConstraintName);
Assert.Equal(1, clonedFk.Columns.Length);
Assert.Equal("DirectoryID", clonedFk.Columns[0].ColumnName);
UniqueConstraint clonedUc = (UniqueConstraint)cloned_ds.Tables["Directories"].Constraints[0];
UniqueConstraint origUc = (UniqueConstraint)ds.Tables["Directories"].Constraints[0];
Assert.Equal(origUc.ConstraintName, clonedUc.ConstraintName);
Assert.Equal(1, clonedUc.Columns.Length);
Assert.Equal("UID", clonedUc.Columns[0].ColumnName);
// check copy works fine
DataSet copy_ds = ds.Copy();
Assert.Equal(1, copy_ds.Tables["Directories"].Constraints.Count);
Assert.Equal(1, copy_ds.Tables["Files"].Constraints.Count);
ForeignKeyConstraint copyFk = (ForeignKeyConstraint)copy_ds.Tables["Files"].Constraints[0];
Assert.Equal("FK_Test", copyFk.ConstraintName);
Assert.Equal(1, copyFk.Columns.Length);
Assert.Equal("DirectoryID", copyFk.Columns[0].ColumnName);
UniqueConstraint copyUc = (UniqueConstraint)copy_ds.Tables["Directories"].Constraints[0];
origUc = (UniqueConstraint)ds.Tables["Directories"].Constraints[0];
Assert.Equal(origUc.ConstraintName, copyUc.ConstraintName);
Assert.Equal(1, copyUc.Columns.Length);
Assert.Equal("UID", copyUc.Columns[0].ColumnName);
}
/// <summary>
/// SQL-92, page 274<br/>
/// Note that item 8 on page 276 specifies that if ON UPDATE clause is omitted, DB should act as if "NO ACTION" is implicitly stated<br/>
/// Also, Oracle violates SQL-92 by not supporting explicit ON UPDATE clauses
/// </summary>
private string _UpdateRule(ForeignKeyConstraint.ReferentialAction onUpdate)
{
if(onUpdate == ForeignKeyConstraint.ReferentialAction.NoAction)
{
return "";
}
return string.Format(
"ON UPDATE {0}",
_ReferentialAction(onUpdate)
);
}
/// <summary>
/// SQL-92, page 274
/// </summary>
private string _ReferentialTriggeredAction(ForeignKeyConstraint constraint)
{
return string.Format(
"{0} {1}",
_UpdateRule(constraint.onUpdate),
_DeleteRule(constraint.onDelete)
);
}
/// <summary>
/// SQL-92, page 274
/// </summary>
private string _ReferentialConstraintDefinition(ForeignKeyConstraint constraint)
{
return string.Format(
"foreign key({0}) references {1} {2}",
nameEscaper(constraint.column),
nameEscaper(constraint.referencedTable),
_ReferentialTriggeredAction(constraint)
);
}
public void ctor_NameParentColsChildCols()
{
DataTable dtParent = DataProvider.CreateParentDataTable();
DataTable dtChild = DataProvider.CreateChildDataTable();
ForeignKeyConstraint fc = null;
fc = new ForeignKeyConstraint("myForeignKey", new DataColumn[] { dtParent.Columns[0] }, new DataColumn[] { dtChild.Columns[0] });
// Ctor
Assert.Equal(false, fc == null);
// Ctor - name
Assert.Equal("myForeignKey", fc.ConstraintName);
}
public void ParentChildSameColumn()
{
DataTable dataTable = new DataTable("Menu");
DataColumn colID = dataTable.Columns.Add("ID", typeof(int));
DataColumn colCulture = dataTable.Columns.Add("Culture", typeof(string));
dataTable.Columns.Add("Name", typeof(string));
DataColumn colParentID = dataTable.Columns.Add("ParentID", typeof(int));
// table PK (ID, Culture)
dataTable.Constraints.Add(new UniqueConstraint(
"MenuPK",
new DataColumn[] { colID, colCulture },
true));
// add a FK referencing the same table: (ID, Culture) <- (ParentID, Culture)
ForeignKeyConstraint fkc = new ForeignKeyConstraint(
"MenuParentFK",
new DataColumn[] { colID, colCulture },
new DataColumn[] { colParentID, colCulture });
dataTable.Constraints.Add(fkc);
}
public void UpdateRule4()
{
DataSet ds = GetNewDataSet();
ForeignKeyConstraint fc = new ForeignKeyConstraint(ds.Tables[0].Columns[0], ds.Tables[1].Columns[0]);
fc.UpdateRule = Rule.SetNull;
ds.Tables[1].Constraints.Add(fc);
//Changing parent row
ds.Tables[0].Rows.Find(1)["ParentId"] = 8;
ds.Tables[0].AcceptChanges();
ds.Tables[1].AcceptChanges();
//Checking the table
Assert.True(ds.Tables[1].Select("ParentId is null").Length > 0);
}
public void UpdateRule3()
{
DataSet ds = GetNewDataSet();
ForeignKeyConstraint fc = new ForeignKeyConstraint(ds.Tables[0].Columns[0], ds.Tables[1].Columns[1]);
fc.UpdateRule = Rule.SetDefault;
ds.Tables[1].Constraints.Add(fc);
//Changing parent row
ds.Tables[1].Columns[1].DefaultValue = "777";
//Add new row --> in order to apply the forigen key rules
DataRow dr = ds.Tables[0].NewRow();
dr["ParentId"] = 777;
ds.Tables[0].Rows.Add(dr);
ds.Tables[0].Rows.Find(1)["ParentId"] = 8;
ds.Tables[0].AcceptChanges();
ds.Tables[1].AcceptChanges();
//Checking the table
Assert.True(ds.Tables[1].Select("ChildId=777").Length > 0);
}
public new void ToString()
{
DataTable dtParent = DataProvider.CreateParentDataTable();
DataTable dtChild = DataProvider.CreateChildDataTable();
ForeignKeyConstraint fc = null;
fc = new ForeignKeyConstraint(dtParent.Columns[0], dtChild.Columns[0]);
// ToString - default
Assert.Equal(string.Empty, fc.ToString());
fc = new ForeignKeyConstraint("myConstraint", dtParent.Columns[0], dtChild.Columns[0]);
// Tostring - Constraint name
Assert.Equal("myConstraint", fc.ToString());
}
/// <summary>
/// SQL-92, page 274
/// SQL:1999 added a support for ON DELETE RESTRICT, and both Postgres and Oracle support it in their current versions
/// </summary>
private static string _ReferentialAction(ForeignKeyConstraint.ReferentialAction action)
{
switch(action)
{
case ForeignKeyConstraint.ReferentialAction.NoAction:
return "NO ACTION";
case ForeignKeyConstraint.ReferentialAction.Cascade:
return "CASCADE";
case ForeignKeyConstraint.ReferentialAction.SetNull:
return "SET NULL";
case ForeignKeyConstraint.ReferentialAction.SetDefault:
return "SET DEFAULT";
case ForeignKeyConstraint.ReferentialAction.Restrict:
return "RESTRICT";
default:
throw new ApplicationException(string.Format("Unknown action {0}", action));
}
}
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);
}
}
internal void AddForeignKeyConstraint(ForeignKeyConstraint constraint)
{
_foreignKeyConstraints[constraint.Name] = constraint;
constraint.UniqueConstraint.Table._foreignKeyDependencies.Add(constraint);
}
public void ctor_DclmDclm1()
{
Assert.Throws<NullReferenceException>(() =>
{
ForeignKeyConstraint fc = new ForeignKeyConstraint(null, (DataColumn)null);
});
}
public void ctor_DclmDclm()
{
DataTable dtParent = DataProvider.CreateParentDataTable();
DataTable dtChild = DataProvider.CreateChildDataTable();
var ds = new DataSet();
ds.Tables.Add(dtChild);
ds.Tables.Add(dtParent);
ForeignKeyConstraint fc = null;
fc = new ForeignKeyConstraint(dtParent.Columns[0], dtChild.Columns[0]);
Assert.False(fc == null);
Assert.Equal(0, dtChild.Constraints.Count);
Assert.Equal(0, dtParent.Constraints.Count);
Assert.Equal(0, ds.Relations.Count);
dtChild.Constraints.Add(fc);
Assert.Equal(1, dtChild.Constraints.Count);
Assert.Equal(1, dtParent.Constraints.Count);
Assert.Equal(0, ds.Relations.Count);
Assert.Equal(typeof(UniqueConstraint), dtParent.Constraints[0].GetType());
Assert.Equal(typeof(ForeignKeyConstraint), dtChild.Constraints[0].GetType());
Assert.Equal(0, dtParent.PrimaryKey.Length);
dtChild.Constraints.Clear();
dtParent.Constraints.Clear();
ds.Relations.Add(new DataRelation("myRelation", dtParent.Columns[0], dtChild.Columns[0]));
Assert.Equal(1, dtChild.Constraints.Count);
Assert.Equal(1, dtParent.Constraints.Count);
Assert.Equal(typeof(UniqueConstraint), dtParent.Constraints[0].GetType());
Assert.Equal(typeof(ForeignKeyConstraint), dtChild.Constraints[0].GetType());
Assert.Equal(0, dtParent.PrimaryKey.Length);
}
public void ctor_ParentColChildCol()
{
DataTable dtParent = DataProvider.CreateParentDataTable();
DataTable dtChild = DataProvider.CreateChildDataTable();
var ds = new DataSet();
ds.Tables.Add(dtChild);
ds.Tables.Add(dtParent);
ForeignKeyConstraint fc = null;
// Ctor ArgumentException
Assert.Throws<ArgumentException>(() =>
{
fc = new ForeignKeyConstraint(new DataColumn[] { dtParent.Columns[0] }, new DataColumn[] { dtChild.Columns[0], dtChild.Columns[1] });
});
fc = new ForeignKeyConstraint(new DataColumn[] { dtParent.Columns[0], dtParent.Columns[1] }, new DataColumn[] { dtChild.Columns[0], dtChild.Columns[2] });
// Add constraint to table - ArgumentException
Assert.Throws<ArgumentException>(() =>
{
dtChild.Constraints.Add(fc);
});
// Child Table Constraints Count - two columnns
Assert.Equal(0, dtChild.Constraints.Count);
// Parent Table Constraints Count - two columnns
Assert.Equal(1, dtParent.Constraints.Count);
// DataSet relations Count
Assert.Equal(0, ds.Relations.Count);
dtParent.Constraints.Clear();
dtChild.Constraints.Clear();
fc = new ForeignKeyConstraint(new DataColumn[] { dtParent.Columns[0] }, new DataColumn[] { dtChild.Columns[0] });
// Ctor
Assert.Equal(false, fc == null);
// Child Table Constraints Count
Assert.Equal(0, dtChild.Constraints.Count);
// Parent Table Constraints Count
Assert.Equal(0, dtParent.Constraints.Count);
// DataSet relations Count
Assert.Equal(0, ds.Relations.Count);
dtChild.Constraints.Add(fc);
// Child Table Constraints Count, Add
Assert.Equal(1, dtChild.Constraints.Count);
// Parent Table Constraints Count, Add
Assert.Equal(1, dtParent.Constraints.Count);
// DataSet relations Count, Add
Assert.Equal(0, ds.Relations.Count);
// Parent Table Constraints type
Assert.Equal(typeof(UniqueConstraint), dtParent.Constraints[0].GetType());
// Parent Table Constraints type
Assert.Equal(typeof(ForeignKeyConstraint), dtChild.Constraints[0].GetType());
// Parent Table Primary key
Assert.Equal(0, dtParent.PrimaryKey.Length);
dtChild.Constraints.Clear();
dtParent.Constraints.Clear();
ds.Relations.Add(new DataRelation("myRelation", dtParent.Columns[0], dtChild.Columns[0]));
// Relation - Child Table Constraints Count
Assert.Equal(1, dtChild.Constraints.Count);
// Relation - Parent Table Constraints Count
Assert.Equal(1, dtParent.Constraints.Count);
// Relation - Parent Table Constraints type
Assert.Equal(typeof(UniqueConstraint), dtParent.Constraints[0].GetType());
// Relation - Parent Table Constraints type
Assert.Equal(typeof(ForeignKeyConstraint), dtChild.Constraints[0].GetType());
// Relation - Parent Table Primary key
Assert.Equal(0, dtParent.PrimaryKey.Length);
}
public void deleteRule()
{
var ds = new DataSet();
DataTable dtParent = DataProvider.CreateParentDataTable();
DataTable dtChild = DataProvider.CreateChildDataTable();
ds.Tables.Add(dtParent);
ds.Tables.Add(dtChild);
dtParent.PrimaryKey = new DataColumn[] { dtParent.Columns[0] };
ds.EnforceConstraints = true;
ForeignKeyConstraint fc = null;
fc = new ForeignKeyConstraint(dtParent.Columns[0], dtChild.Columns[0]);
//checking default
// Default
Assert.Equal(Rule.Cascade, fc.DeleteRule);
//checking set/get
foreach (Rule rule in Enum.GetValues(typeof(Rule)))
{
// Set/Get - rule
fc.DeleteRule = rule;
Assert.Equal(rule, fc.DeleteRule);
}
dtChild.Constraints.Add(fc);
//checking delete rule
// Rule = None, Delete Exception
fc.DeleteRule = Rule.None;
//Exception = "Cannot delete this row because constraints are enforced on relation Constraint1, and deleting this row will strand child rows."
Assert.Throws<InvalidConstraintException>(() =>
{
dtParent.Rows.Find(1).Delete();
});
// Rule = None, Delete succeed
fc.DeleteRule = Rule.None;
foreach (DataRow dr in dtChild.Select("ParentId = 1"))
dr.Delete();
dtParent.Rows.Find(1).Delete();
Assert.Equal(0, dtParent.Select("ParentId=1").Length);
// Rule = Cascade
fc.DeleteRule = Rule.Cascade;
dtParent.Rows.Find(2).Delete();
Assert.Equal(0, dtChild.Select("ParentId=2").Length);
// Rule = SetNull
DataSet ds1 = new DataSet();
ds1.Tables.Add(DataProvider.CreateParentDataTable());
ds1.Tables.Add(DataProvider.CreateChildDataTable());
ForeignKeyConstraint fc1 = new ForeignKeyConstraint(ds1.Tables[0].Columns[0], ds1.Tables[1].Columns[1]);
fc1.DeleteRule = Rule.SetNull;
ds1.Tables[1].Constraints.Add(fc1);
Assert.Equal(0, ds1.Tables[1].Select("ChildId is null").Length);
ds1.Tables[0].PrimaryKey = new DataColumn[] { ds1.Tables[0].Columns[0] };
ds1.Tables[0].Rows.Find(3).Delete();
ds1.Tables[0].AcceptChanges();
ds1.Tables[1].AcceptChanges();
DataRow[] arr = ds1.Tables[1].Select("ChildId is null");
/*foreach (DataRow dr in arr)
{
Assert.Equal(null, dr["ChildId"]);
}*/
Assert.Equal(4, arr.Length);
// Rule = SetDefault
//fc.DeleteRule = Rule.SetDefault;
ds1 = new DataSet();
ds1.Tables.Add(DataProvider.CreateParentDataTable());
ds1.Tables.Add(DataProvider.CreateChildDataTable());
fc1 = new ForeignKeyConstraint(ds1.Tables[0].Columns[0], ds1.Tables[1].Columns[1]);
fc1.DeleteRule = Rule.SetDefault;
ds1.Tables[1].Constraints.Add(fc1);
ds1.Tables[1].Columns[1].DefaultValue = "777";
//Add new row --> in order to apply the forigen key rules
DataRow dr2 = ds1.Tables[0].NewRow();
dr2["ParentId"] = 777;
ds1.Tables[0].Rows.Add(dr2);
ds1.Tables[0].PrimaryKey = new DataColumn[] { ds1.Tables[0].Columns[0] };
ds1.Tables[0].Rows.Find(3).Delete();
Assert.Equal(4, ds1.Tables[1].Select("ChildId=777").Length);
}
public void constraintName()
{
var ds = new DataSet();
DataTable dtParent = DataProvider.CreateParentDataTable();
DataTable dtChild = DataProvider.CreateChildDataTable();
ds.Tables.Add(dtParent);
ds.Tables.Add(dtChild);
ForeignKeyConstraint fc = null;
fc = new ForeignKeyConstraint(dtParent.Columns[0], dtChild.Columns[0]);
// default
Assert.Equal(string.Empty, fc.ConstraintName);
fc.ConstraintName = "myConstraint";
// set/get
Assert.Equal("myConstraint", fc.ConstraintName);
}
public void acceptRejectRule()
{
DataSet ds = getNewDataSet();
ForeignKeyConstraint fc = new ForeignKeyConstraint(ds.Tables[0].Columns[0], ds.Tables[1].Columns[0]);
fc.AcceptRejectRule = AcceptRejectRule.Cascade;
ds.Tables[1].Constraints.Add(fc);
//Update the parent
ds.Tables[0].Rows[0]["ParentId"] = 777;
Assert.Equal(true, ds.Tables[1].Select("ParentId=777").Length > 0);
ds.Tables[0].RejectChanges();
Assert.Equal(0, ds.Tables[1].Select("ParentId=777").Length);
}
public void ctor_DclmDclm3()
{
Assert.Throws<ArgumentException>(() =>
{
var ds = new DataSet();
ds.Tables.Add(DataProvider.CreateParentDataTable());
ds.Tables.Add(DataProvider.CreateChildDataTable());
ds.Tables["Child"].Columns["ParentId"].Expression = "2";
ForeignKeyConstraint fc = new ForeignKeyConstraint(ds.Tables[0].Columns[0], ds.Tables[1].Columns[0]);
});
}
public void extendedProperties()
{
DataTable dtParent = DataProvider.CreateParentDataTable();
DataTable dtChild = DataProvider.CreateParentDataTable();
ForeignKeyConstraint fc = null;
fc = new ForeignKeyConstraint(dtParent.Columns[0], dtChild.Columns[0]);
PropertyCollection pc = fc.ExtendedProperties;
// Checking ExtendedProperties default
Assert.Equal(true, fc != null);
// Checking ExtendedProperties count
Assert.Equal(0, pc.Count);
}
/// <summary>
/// Create a new ForeignKeyConstraint object.
/// </summary>
/// <param name="id">Initial value of Id.</param>
/// <param name="name">Initial value of Name.</param>
/// <param name="isDeferrable">Initial value of IsDeferrable.</param>
/// <param name="isInitiallyDeferred">Initial value of IsInitiallyDeferred.</param>
/// <param name="updateRule">Initial value of UpdateRule.</param>
/// <param name="deleteRule">Initial value of DeleteRule.</param>
public static ForeignKeyConstraint CreateForeignKeyConstraint(string id, string name, bool isDeferrable, bool isInitiallyDeferred, string updateRule, string deleteRule)
{
ForeignKeyConstraint foreignKeyConstraint = new ForeignKeyConstraint();
foreignKeyConstraint.Id = id;
foreignKeyConstraint.Name = name;
foreignKeyConstraint.IsDeferrable = isDeferrable;
foreignKeyConstraint.IsInitiallyDeferred = isInitiallyDeferred;
foreignKeyConstraint.UpdateRule = updateRule;
foreignKeyConstraint.DeleteRule = deleteRule;
return foreignKeyConstraint;
}
public void UpdateRule2()
{
Assert.Throws<ConstraintException>(() =>
{
DataSet ds = GetNewDataSet();
ds.Tables[0].PrimaryKey = null;
ForeignKeyConstraint fc = new ForeignKeyConstraint(ds.Tables[0].Columns[0], ds.Tables[1].Columns[0]);
fc.UpdateRule = Rule.None;
ds.Tables[1].Constraints.Add(fc);
//Changing parent row
ds.Tables[0].Rows[0]["ParentId"] = 5;
/*ds.Tables[0].AcceptChanges();
ds.Tables[1].AcceptChanges();
//Checking the table
Compare(ds.Tables[1].Select("ParentId=8").Length ,0);*/
});
}
