程式師世界 >> 編程語言 >> .NET網頁編程 >> .NET實例教程 >> 在DataSet中建立外鍵約束

在DataSet中建立外鍵約束

編輯：.NET實例教程

程序可以直接拿來使用，運行通過。

----------------------------------------------------
testconstraint.ASPx
----------------------------------------------------

<%@ Page Language="c#" debug="true" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClIEnt" %>

<Html>
<head>
<script language="c#" runat="server">
public DataSet ds;
void Page_Load(Object sender,EventArgs e)
{
    if(!IsPostBack)
    {
        SqlConnection myConnection = new SqlConnection("server=(local);database=northwind;Trusted_Connection=yes");
        SqlDataAdapter myDataAdapter1=new SqlDataAdapter("select * from supplIErs",myConnection);
        SqlDataAdapter myDataAdapter2=new SqlDataAdapter("select * from products",myConnection);

        ds=new DataSet();
        myDataAdapter1.Fill(ds,"supplIErs");
        myDataAdapter2.Fill(ds,"products");

        MyDataGrid.DataSource=ds.Tables["suppliers"].DefaultVIEw;
        MyDataGrid.DataBind();
        MyDataGrid2.DataSource=ds.Tables["products"].DefaultVIEw;
        MyDataGrid2.DataBind();
    }
}

void MyDataGrid_Delete(Object sender,DataGridCommandEventArgs e)
{
    String conn="server=(local);database=northwind;Trusted_Connection=yes";
    String selectCommandText1="select * from supplIErs";
    String selectCommandText2="select * from products";

     SqlDataAdapter myDataAdapter1=new SqlDataAdapter();
    myDataAdapter1.SelectCommand=new SqlCommand();
    myDataAdapter1.SelectCommand.CommandText=selectCommandText1;
    myDataAdapter1.SelectCommand.Connection=new SqlConnection(conn);

    SqlDataAdapter myDataAdapter2=new SqlDataAdapter();
    myDataAdapter2.SelectCommand=new SqlCommand();
    myDataAdapter2.SelectCommand.CommandText=selectCommandText2;
    myDataAdapter2.SelectCommand.Connection=new SqlConnection(conn);

    ds=new DataSet();
    myDataAdapter1.Fill(ds,"supplIErs");
    myDataAdapter2.Fill(ds,"products");

CreateConstraint();

    int index=(int)e.Item.ItemIndex;
    ds.Tables["supplIErs"].Rows[index].Delete();
    ds.Tables["supplIErs"].AcceptChanges();

  myDataAdapter1.Update(ds,"supplIErs");

    MyDataGrid.DataSource=ds.Tables["suppliers"].DefaultVIEw;
    MyDataGrid.DataBind();

MyDataGrid2.DataSource=ds.Tables["products"].DefaultVIEw;
MyDataGrid2.DataBind();
}

void CreateConstraint()
{
DataColumn parentColumn,childColumn;
ForeignKeyConstraint myForeignKeyConstraint;

    parentColumn = ds.Tables["suppliers"].Columns["supplIErID"];
    childColumn = ds.Tables["products"].Columns["supplIErID"];
    myForeignKeyConstraint = new ForeignKeyConstraint("SupplIErForeignKeyConstraint", parentColumn, childColumn);

    myForeignKeyConstraint.DeleteRule = Rule.Cascade ;
    myForeignKeyConstraint.UpdateRule = Rule.Cascade ;
    myForeignKeyConstraint.AcceptRejectRule = AcceptRejectRule.Cascade ;

ds.Tables["products"].Constraints.Add(myForeignKeyConstraint) ;
ds.EnforceConstraints =true ;
}

</script>
</head>
<body>
<form runat="server">
<ASP:DataGrid id="MyDataGrid" runat="server"
      Width="800"
      BackColor="#ccccff"
      BorderColor="black"
      ShowFooter="false"
      CellPadding=3
      CellSpacing="0"
      Font-Name="Verdana"
      Font-Size="8pt"
      HeaderStyle-BackColor="#aaaadd"
      OnDeleteCommand="MyDataGrid_Delete"
      DataKeyField="supplIErid"
    >