以下创建了一个包含3个DataTable对象的DataSet:
private
void
button1_Click(
object
sender, EventArgs e)
{
SqlConnection con = new SqlConnection();
con.ConnectionString = (@"Data source=localhost;Integrated Security=true;database=Northwind");
try
{
string SQL1 = "SELECT * FROM Orders";
string SQL2 = "SELECT * FROM Employees";
DataSet ds = new DataSet();
DataTable tb1;
DataColumn col;
ForeignKeyConstraint fk;
//创建客户表
tb1 = ds.Tables.Add("Customers");
col=tb1.Columns.Add("CustomerID",typeof(string));
col.MaxLength=5;
col=tb1.Columns.Add("CompanyName",typeof(string));
col.MaxLength=40;
col=tb1.Columns.Add("ContactName",typeof(string));
col.MaxLength=30;
col=tb1.Columns.Add("Phone",typeof(string));
col.MaxLength=24;
tb1.PrimaryKey=new DataColumn[]{tb1.Columns["CustomerID"]};
//创建订单表
tb1=ds.Tables.Add("Orders");
col=tb1.Columns.Add("OrderID",typeof(int));
col.AutoIncrement = true;
col.AutoIncrementSeed=-1;
col.AutoIncrementStep=-1;
col.ReadOnly = true;
col=tb1.Columns.Add("CustomerID",typeof(string));
col.AllowDBNull=false;
col.MaxLength=5;
tb1.Columns.Add("EmployeeID",typeof(int));
tb1.Columns.Add("OrderDate",typeof(DateTime));
tb1.PrimaryKey=new DataColumn[]{tb1.Columns["OrderID"]};
//创建订单详细信息表
tb1=ds.Tables.Add("Order Details");
tb1.Columns.Add("OrderID",typeof(int));
tb1.Columns.Add("ProductID",typeof(int));
col=tb1.Columns.Add("UnitPrice",typeof(Decimal));
col.AllowDBNull=false;
col=tb1.Columns.Add("Quantity",typeof(int));
col.AllowDBNull=false;
col.DefaultValue=1;
col=tb1.Columns.Add("Discount",typeof(Decimal));
col.DefaultValue=0;
tb1.Columns.Add("ItemTotal",typeof(Decimal),
"UnitPrice*Quantity*(1-Discount)");
tb1.PrimaryKey=new DataColumn[]{tb1.Columns["OrderID"],
tb1.Columns["ProductID"]};
//创建外鍵约束
fk=new ForeignKeyConstraint(ds.Tables["Customers"].Columns["CustomerID"],
ds.Tables["Orders"].Columns["CustomerID"]);
fk=new ForeignKeyConstraint(ds.Tables["Orders"].Columns["OrderID"],
ds.Tables["Order Details"].Columns["OrderID"]);
ds.Tables["Order Details"].Constraints.Add(fk);
}
catch (SqlException es)
{
MessageBox.Show(es.Message);
}
}
{
SqlConnection con = new SqlConnection();
con.ConnectionString = (@"Data source=localhost;Integrated Security=true;database=Northwind");
try
{
string SQL1 = "SELECT * FROM Orders";
string SQL2 = "SELECT * FROM Employees";
DataSet ds = new DataSet();
DataTable tb1;
DataColumn col;
ForeignKeyConstraint fk;
//创建客户表
tb1 = ds.Tables.Add("Customers");
col=tb1.Columns.Add("CustomerID",typeof(string));
col.MaxLength=5;
col=tb1.Columns.Add("CompanyName",typeof(string));
col.MaxLength=40;
col=tb1.Columns.Add("ContactName",typeof(string));
col.MaxLength=30;
col=tb1.Columns.Add("Phone",typeof(string));
col.MaxLength=24;
tb1.PrimaryKey=new DataColumn[]{tb1.Columns["CustomerID"]};
//创建订单表
tb1=ds.Tables.Add("Orders");
col=tb1.Columns.Add("OrderID",typeof(int));
col.AutoIncrement = true;
col.AutoIncrementSeed=-1;
col.AutoIncrementStep=-1;
col.ReadOnly = true;
col=tb1.Columns.Add("CustomerID",typeof(string));
col.AllowDBNull=false;
col.MaxLength=5;
tb1.Columns.Add("EmployeeID",typeof(int));
tb1.Columns.Add("OrderDate",typeof(DateTime));
tb1.PrimaryKey=new DataColumn[]{tb1.Columns["OrderID"]};
//创建订单详细信息表
tb1=ds.Tables.Add("Order Details");
tb1.Columns.Add("OrderID",typeof(int));
tb1.Columns.Add("ProductID",typeof(int));
col=tb1.Columns.Add("UnitPrice",typeof(Decimal));
col.AllowDBNull=false;
col=tb1.Columns.Add("Quantity",typeof(int));
col.AllowDBNull=false;
col.DefaultValue=1;
col=tb1.Columns.Add("Discount",typeof(Decimal));
col.DefaultValue=0;
tb1.Columns.Add("ItemTotal",typeof(Decimal),
"UnitPrice*Quantity*(1-Discount)");
tb1.PrimaryKey=new DataColumn[]{tb1.Columns["OrderID"],
tb1.Columns["ProductID"]};
//创建外鍵约束
fk=new ForeignKeyConstraint(ds.Tables["Customers"].Columns["CustomerID"],
ds.Tables["Orders"].Columns["CustomerID"]);
fk=new ForeignKeyConstraint(ds.Tables["Orders"].Columns["OrderID"],
ds.Tables["Order Details"].Columns["OrderID"]);
ds.Tables["Order Details"].Constraints.Add(fk);
}
catch (SqlException es)
{
MessageBox.Show(es.Message);
}
}