获得数据库中所有数据库的名字:select name From sysdatabases
获得某个数据库中所有表的名字:select name from sysobjects where type='U'
获得某个表中字段的名字:select name from syscolumns where id=object_id('表名')
use master if exists(SELECT * From sysdatabases where name='test3') drop database test3 create database test3 go use test3 go 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,2 insert into abc values('a',null,default)
接下来我给数据表作一个外键连接,
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 cascade insert 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)
这样的话如果你要删除或者更新主表,如果子表的外键具有主表的主键信息的话,操作失败,
同样如果想要插入从表一个在主表中不存在的主键所对应的外键信息的话,同样,无法操作
但是我在表中确增加了更新与删除级联关系,那样的话,如果我主表中删除记录,此时从表外键
所对应的该删除记录的主键的信息也将随即删除,更新同样如此
那这样的操作在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");
此时大家可以看一下运行结果,与在sql中进行的更新级联效果一样, 如果你在sql中没有添加级联关系, 在datatable中可以通过添加 custOrderFK.DeleteRule = Rule.Cascade;来 达到同样的效果, 所以结果也就是删除了主表的第一条记录,从表所对应的记录也予以删除 同样在j2ee的hibernate中也可以实现,以后详细说明