在編寫(xiě)數(shù)據(jù)庫(kù)系統(tǒng)中為了保證系統(tǒng)中數(shù)據(jù)的一致性最簡(jiǎn)便且安全的方法就是在dbms中建立外鍵約束，但刪除主鍵數(shù)據(jù)時(shí)如果違反了外鍵約束，盡管dbms會(huì)給出錯(cuò)誤提示，如sql server的提示信息“%1! 語(yǔ)句與 %2! %3! 約束 '%4!' 沖突。該沖突發(fā)生于數(shù)據(jù)庫(kù) '%6!'，表 '%8!'%10!%11!%13!。”，但這些提示信息對(duì)最終用戶(hù)來(lái)說(shuō)，是不友好的，于是就自己寫(xiě)了個(gè)類(lèi)，用來(lái)刪除記錄時(shí)的進(jìn)行外鍵沖突檢測(cè)，代碼如下：
using system;
using system.data;
using system.data.sqlclient;
using microsoft.applicationblocks.data;
namespace dataaccess.sqlserverdal
{
///
/// check 的摘要說(shuō)明。
///
public class check
{
///
/// dbms中保存系統(tǒng)表的
///
const string default_systables = "systables";
#region ckeckfkbegindelete
///
/// 在刪除記錄之前先檢測(cè)有無(wú)外鍵沖突
///
/// 事物對(duì)象
/// 要執(zhí)行刪除操作的表名
/// 要?jiǎng)h除的記錄的主鍵值
/// 返回錯(cuò)誤信息
/// true - 無(wú)沖突，false - 有沖突
public bool ckeckfkbegindelete(sqltransaction trans, string tablename, string id, ref string errtext)
{
string selectstring; //sql查詢(xún)語(yǔ)句
string fktablename; //外鍵表名稱(chēng)
string fkcolumnname; //外鍵列名稱(chēng)
object obj; //執(zhí)行sql查詢(xún)返回值
string description; //外鍵表含義
int count; //外鍵表中引用了主鍵的記錄數(shù)
string[] tablenames = {"sysforeignkeys"};
dataset ds = builddatatables();
//檢索所有此表的外鍵表
selectstring = "select fkeyid, fkey from sysforeignkeys a, sysobjects b where a.rkeyid = b.id and b.name = @name";
sqlparameter name = new sqlparameter("@name", sqldbtype.varchar);
name.value = tablename;
sqlhelper.filldataset(trans, commandtype.text, selectstring, ds, tablenames, name);
//外鍵表id
sqlparameter id = new sqlparameter("@id", sqldbtype.int);
//外鍵列id
sqlparameter colid = new sqlparameter("@colid", sqldbtype.int);
//主鍵值
sqlparameter keyid = new sqlparameter("@keyid", sqldbtype.int);
//遍歷所有的外鍵表
foreach (datarow dr in ds.tables["sysforeignkeys"].rows)
{
//查詢(xún)外鍵表名稱(chēng)
selectstring = "select name from sysobjects where id = @id";
id.value = dr["fkeyid"];
fktablename = sqlhelper.executescalar(trans, commandtype.text, selectstring, id).tostring();
//查詢(xún)外鍵列名稱(chēng)
selectstring = "select name from syscolumns where id = @id and colid = @colid";
id.value = dr["fkeyid"];
colid.value = dr["fkey"];
fkcolumnname = sqlhelper.executescalar(trans, commandtype.text, selectstring, id, colid).tostring();
//查詢(xún)外鍵表中有沒(méi)有引用要?jiǎng)h除的主鍵
selectstring = "select count(*) from " + fktablename + " where " + fkcolumnname + " = @keyid";
keyid.value = id;
count = convert.toint32(sqlhelper.executescalar(trans, commandtype.text, selectstring, keyid));
if (count > 0)
{
//查詢(xún)發(fā)生沖突的表的含義，從而給用戶(hù)發(fā)出友好的提示
selectstring = "select description from callcentertables where tablename = @tablename";
sqlparameter tablename = new sqlparameter("@tablename", sqldbtype.varchar);
tablename.value = fktablename;
obj = sqlhelper.executescalar(trans, commandtype.text, selectstring, tablename);
if (obj != null)
description = obj.tostring();
else
description = fktablename;
errtext = "您要?jiǎng)h除的數(shù)據(jù)已在" + description + "中使用，要?jiǎng)h除該條數(shù)據(jù)，請(qǐng)先刪除" +
description + "中的相關(guān)數(shù)據(jù)，否則您將無(wú)法刪除此條記錄！";
return false;
}
}
return true;
}
#endregion
#region builddatatables
///
/// 創(chuàng)建外鍵datatable
///
/// dataset實(shí)例
private dataset builddatatables()
{
dataset ds = new dataset();
datatable table;
datacolumncollection columns;
table = new datatable("sysforeignkeys");
columns = table.columns;
columns.add("fkeyid", typeof(system.int32));
columns.add("fkey", typeof(system.int32));
ds.tables.add(table);
return ds;
}
#endregion
}
}
使用該類(lèi)時(shí)需要在dbms中建一張系統(tǒng)表，并維護(hù)表中的數(shù)據(jù)，該表用來(lái)記錄系統(tǒng)中各用戶(hù)表的大概含義，用來(lái)告訴用戶(hù)是什么地方發(fā)生了沖突：
create table systables
(
id int not null identity(1,1) primary key clustered, /*id*/
tablename varchar(255), /*用戶(hù)表名稱(chēng)*/
description varchar(255) /*用戶(hù)表描述*/
)
調(diào)用示例：
public bool test()
{
//數(shù)據(jù)庫(kù)連接字符串
string connectionstring = "";
using (sqlconnection conn = new sqlconnection(connectionstring))
{
conn.open();
using (sqltransaction trans = conn.begintransaction())
{
try
{
string execsqlstring = "delete from test where id = 1";
string errtext = "";
if (!new check().ckeckfkbegindelete(trans, "test", 1, ref errtext))
{
trans.rollback();
return false;
}
sqlhelper.executenonquery(trans, commandtype.text, execsqlstring);
trans.commit();
return true;
}
catch
{
trans.rollback();
throw;
}
}
}
}
代碼中用到ms的sqlhelper類(lèi)，可以到http://msdn.microsoft.com/library/en-us/dnbda/html/daab-rm.asp下載。目前該類(lèi)僅適用于sql server數(shù)據(jù)庫(kù)
