Saturday, June 30, 2012

Adding Foriegn Key to DataTbale Programmatically in Asp.net

Here i will show how to assign foreign key to Data table dynamically  using C#.For this we will the data of two table(Orders,Customers) from sqlserver.Then the data is bind to data set.The FK is assigned to OrderId in Customers table using ForeignKeyConstraint class.The Rules(delete,edit) of key also set dynamically in below peace of code

SqlConnection cn=New SqlConnection("server=./sqlexpress;user id=sa;password=ds;database=test");
SqlDataSet ds=New SqlDataSet();
SqlDataAdapter daOrder = new SqlDataAdapter("select *from Orders", cn);
SqlDataAdapter daCustomers = new SqlDataAdapter("select *from Customers", cn);
dadept.Fill(ds, "Orders");
daemp.Fill(ds, "Customers");
ForeignKeyConstraint fkc = null;
fkc = new ForeignKeyConstraint(ds.tables("Orders").Columns("OrderId"), ds.Tables("Customers").Columns("OrderId"));
fkc.DeleteRule = Rule.Cascade;
fkc.DeleteRule = Rule.SetNull;
fkc.EditRUle = Rule.Cascade;
ds.Tables("Customers").Constraint.ADD(fk);
gvOrders = ds.Tables("Orders");
gvCustomers = ds.Tables("Customers");

No comments:

Bel