在DataSet中建立外键约束

2020-02-03 13:39:42

字体：大中小

来源：转载

供稿：网友

程序可以直接拿来使用，运行通过。

----------------------------------------------------
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"
    >