Update cascade in SQL and ado.net

Obtain the names of all databases in the database: Select name from sysdatabases

Obtain the names of all tables in a database: Select name from sysobjects where type = 'U'

Obtain the field name in a table: Select name from syscolumns where id = object_id ('table name ')

use masterif exists(SELECT * From sysdatabases where name='test3')drop database  test3create database test3go use test3go if exists (select * from sysobjects where type='U' and name='abc')drop table abc create table abc ( id int not null identity(20011001,1) primary key clustered, name varchar(6) not null ,class int null , time1 datetime default getdate())insert into abc  select 'a',1,1 union all select 'b',2,2insert into abc values('a',null,default)

Next, I create a foreign key connection for the data table,

create table a11 (id int not null  primary key clustered,name varchar(6) not null ,class int null)create table b11 (id int not null  primary key clustered,bbbname varchar(6) not null ,bbbid int null)alter   table   b11   add      constraint FK_id   foreign   key    (bbbid)   references   a11(id)  on   update   cascade   on delete cascadeinsert into a11 values(1,'11',111)insert into a11 values(2,'22',222)insert into a11 values(3,'33',333)insert into b11 values(1,'aa',1)insert into b11 values(2,'bb',2)insert into b11 values(3,'cc',3)

In this case, if you want to delete or update the primary table, if the foreign key of the sub-table has primary key information of the primary table, the operation fails,

Similarly, if you want to insert the foreign key information corresponding to a primary key that does not exist in the primary table from the slave table, you cannot perform the operation.

However, I have added a cascading relationship between update and deletion in the table. In this case, if you delete a record from the main table

The corresponding information about the primary key of the deleted record will be deleted immediately.

So how does this operation work under ado.net?

    {        string ConnectionString = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa ;Password=sa ;Initial Catalog=test1;Data Source=192.168.10.250";        System.Data.OleDb.OleDbConnection cn = new System.Data.OleDb.OleDbConnection(ConnectionString);        cn.Open();        System.Data.DataSet ds = new System.Data.DataSet();        System.Data.OleDb.OleDbDataAdapter da = new System.Data.OleDb.OleDbDataAdapter("SELECT * From a11", cn);        System.Data.OleDb.OleDbDataAdapter da1 = new System.Data.OleDb.OleDbDataAdapter("SELECT * From b11", cn);        System.Data.OleDb.OleDbCommandBuilder cb = new System.Data.OleDb.OleDbCommandBuilder(da);        System.Data.OleDb.OleDbCommandBuilder cb1 = new System.Data.OleDb.OleDbCommandBuilder(da1);                da.Fill(ds,"table");        da1.Fill(ds, "table1");        ds.Tables[0].PrimaryKey = new DataColumn[]{ds.Tables[0].Columns["id"]};        ds.Tables[1].PrimaryKey = new DataColumn[]{ds.Tables[1].Columns["id"]};        ForeignKeyConstraint custOrderFK = new ForeignKeyConstraint("CustOrderFK",ds.Tables["table"].Columns["id"],ds.Tables["table1"].Columns["bbbid"]);        custOrderFK.DeleteRule = Rule.Cascade;        ds.Tables["table1"].Constraints.Add(custOrderFK);        ds.Tables[0].Rows[0].Delete();        da.Update(ds,"table");        da1.Update(ds,"table1");

At this point, you can check the running result, which is the same as the update cascade effect in SQL. If you do not add cascade relationships in SQL,
In datatable, you can add custorderfk. deleterule = rule. cascade; to achieve the same effect,
Therefore, the result is that the first record of the master table is deleted from the corresponding record of the table.
It can also be implemented in hibernate of J2EE, which will be described in detail later.