Wednesday 12.07.11

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
//call the method that creates the tables and the relations
DataSet ds = CreateDataSet();


//set the data source for the grid to the first table
BugsGridView.DataSource = ds.Tables["Bugs"];
BugsGridView.DataBind();

BugConstraintsGridView.DataSource = ds.Tables["Bugs"].Constraints;
BugConstraintsGridView.DataBind();
}
}

private DataSet CreateDataSet()
{
//create a new dataset object for tables and relations
DataSet dataSet = new DataSet();

//create the Bugs table and add it to the DataSet
DataTable tblBugs = CreateBugsTable();
dataSet.Tables.Add(tblBugs);


//create the Product Table and add it to the dataset
DataTable tblProduct = CreateProductTable();
dataSet.Tables.Add(tblProduct);

//create the people table and add it to the Dataset
DataTable tblPeople = CreatePeopleTable();
dataSet.Tables.Add(tblPeople);

//create the foreign key constraint
//Peopple.PersonID = Bugs.ReportID
CreateForeignKeyAndDataRelation(dataSet, "BugToPerson", tblPeople, "PersonID", tblBugs, "ReporterID");

//create the Foreign Key Constrain
//Product.ProductID = Bugs.ProductID
CreateForeignKeyAndDataRelation(dataSet, "BugToProduct", tblProduct, "ProductID", tblBugs, "ProductID");

return dataSet;
}

private DataTable CreateBugsTable()
{
DataTable tblBugs = new DataTable("Bugs");

//add columns
AddNewPrimaryKeyColumn(tblBugs, "BugID");
AddNewColumn(tblBugs, "System.Int32", "ProductID", false, 1);
AddNewColumn(tblBugs, "System.String", "Version", false, "0.1", 50);
AddNewColumn(tblBugs, "System.String", "Description", false, "", 8000);
AddNewColumn(tblBugs, "System.Int32", "ReporterID", false);

//add some rows to the table
AddNewBug(tblBugs, 1, "0.1", "Crashes on load", 5);
AddNewBug(tblBugs, 1, "0.1", "Does not report correct owner of bug", 5);
AddNewBug(tblBugs, 1, "0.1", "Does not show history of previous action", 6);
AddNewBug(tblBugs, 1, "0.1", "Fails to reload properly", 5);
AddNewBug(tblBugs, 2, "0.1", "Loses data overnight", 5);
AddNewBug(tblBugs, 2, "0.1", "HTML is not shown properly", 6);
return tblBugs;
}

private DataTable CreateProductTable()
{
DataTable tblProduct = new DataTable("lkProduct");

//add columns
AddNewPrimaryKeyColumn(tblProduct, "ProductID");
AddNewColumn(tblProduct, "System.String", "ProductDescription", false, "", 8000);

//add rows to the Product table
AddNewProduct(tblProduct, "BugX Bug Tracking");
AddNewProduct(tblProduct, "PIM - My Personal Information Manager");
return tblProduct;
}

private void AddNewProduct(DataTable productTable, string description)
{
DataRow newRow = productTable.NewRow();
newRow["ProductDescription"] = description;
productTable.Rows.Add(newRow);
}

private DataTable CreatePeopleTable()
{
DataTable tblPeople = new DataTable("People");

//add column
AddNewPrimaryKeyColumn(tblPeople, "PersonID");
AddNewColumn(tblPeople, "System.String", "FullName", false, "", 8000);
AddNewColumn(tblPeople, "System.String", "Email", false, "", 100);
AddNewColumn(tblPeople, "System.String", "Phone", false, "", 20);
AddNewColumn(tblPeople, "System.Int32", "Role", false, 0);

//add new people
AddNewPerson(tblPeople, "Dan Maharry", "danm@hmobius.com", "212-255-0285", 1);
AddNewPerson(tblPeople, "Jesse Liberty", "jliberty@libertyassociates.com", "617-555-7301", 1);
AddNewPerson(tblPeople, "Dan Hurwitz", "dhurwitz@stersol.com", "781-555-3375", 1);
AddNewPerson(tblPeople, "John Galt", "jGalt@franconia.com", "617-444-8732", 1);
AddNewPerson(tblPeople, "John Osborn", "jOsborn@oreilly.com", "617-554-3212", 3);
AddNewPerson(tblPeople, "Ron Petrusha", "ron@oreilly.com", "707-555-0515", 2);
AddNewPerson(tblPeople, "Tatiana Apandi", "tatiana@oreilly.com", "617-555-1234", 2);

return tblPeople;
}

private void AddNewPerson(DataTable table, string name, string email, string phone, int role)
{
DataRow newRow = table.NewRow();
newRow["FullName"] = name;
newRow["Email"] = email;
newRow["Phone"] = phone;
newRow["Role"] = role;
table.Rows.Add(newRow);
}


//AllowDBNull sets whether a value in that column can ever be null or not
//DefaultValue sets the default value for that column
//MaxLength sets the maximum length fo that column if it is of type string

private void AddNewColumn(DataTable table, string ColumnType, string ColumnName, bool AllowNulls, object DefaultValue, int MaxLength)
{
DataColumn newColumn = table.Columns.Add(ColumnName, Type.GetType(ColumnType));
newColumn.AllowDBNull = AllowNulls;
newColumn.MaxLength = MaxLength;
newColumn.DefaultValue = DefaultValue;
}

private void AddNewColumn(DataTable table, string ColumnType, string ColumnName, bool AllowNulls, object DefaultValue)
{
AddNewColumn(table, ColumnType, ColumnName, AllowNulls, DefaultValue, -1);
}

private void AddNewColumn(DataTable table, string ColumnType, string ColumnName, bool AllowNulls)
{
AddNewColumn(table, ColumnType, ColumnName, AllowNulls, null, -1);
}

//further properties must be set to identify a column as a primary key for the DataTable
//creates a reference to the new column and calls it PkColumn
private void AddNewPrimaryKeyColumn(DataTable table, string ColumnName)
{
AddNewColumn(table, "System.Int32", ColumnName, false);
DataColumn PkColumn = table.Columns[ColumnName];
//because thsi is to be an identity column, you'll want to set its AutoIncrement property to true and its AutoIncrementSeed and AutoIncrementStep proeprties to set
//the seed and step values to 1

//set column as auto-increment field
PkColumn.AutoIncrement = true;
PkColumn.AutoIncrementSeed = 1;
PkColumn.AutoIncrementStep = 1;

//make sure all values are unique
string constraintName = String.Format("Unique_{0}", ColumnName);
UniqueConstraint constraint = new UniqueConstraint(constraintName, PkColumn);
table.Constraints.Add(constraint);

//finally the DataColumn must be added to the DataTable's PrimaryKey property

//set column as priamry key for table
DataColumn[] columnArray = new DataColumn[] { PkColumn };
table.PrimaryKey = columnArray;
}

//add rows by calling the DataTable objects NewRow() method, which returns an empty DataRow object
//with the right structure for the DataTable to which it belongs

private void AddNewBug(DataTable bugTable, int product, string version, string description, int reporter)
{
DataRow newRow = bugTable.NewRow();
newRow["ProductID"] = product;
newRow["Version"] = version;
newRow["Description"] = description;
newRow["ReporterID"] = reporter;
bugTable.Rows.Add(newRow);
}

//you can encapsulate the relationship between tables in a DataRelation object
//the DeleteRule property of a ForeignKeyConstraint object determines the action that will occur
//when a row is deleted from the parent table.
private void CreateForeignKeyAndDataRelation(DataSet dataSet, string relationName, DataTable parentTable, string primaryKeyColumnName, DataTable childTable, string foreignKeyColumnName)
{
DataColumn primaryKeyColumn = parentTable.Columns[primaryKeyColumnName];
DataColumn foreignKeyColumn = childTable.Columns[foreignKeyColumnName];
string foreignKeyConstraintName = String.Format("FL_{0}", relationName);

//create the foreign key constraint
ForeignKeyConstraint fk = new ForeignKeyConstraint(foreignKeyConstraintName, primaryKeyColumn, foreignKeyColumn);
fk.DeleteRule = Rule.Cascade;
fk.UpdateRule = Rule.Cascade;
childTable.Constraints.Add(fk);

//add a datarelation representing the FKConstraint to the DataSet
DataRelation relation = new DataRelation(relationName, primaryKeyColumn, foreignKeyColumn);
dataSet.Relations.Add(relation);
}
}

---