在編寫數據庫系統中為了保證系統中數據的一致性最簡便且安全的方法就是在DBMS中建立外鍵約束，但刪除主鍵數據時如果違反了外鍵約束，盡管DBMS會給出錯誤提示，如SQL Server的提示信息“%1! 語句與 %2! %3! 約束 '%4!' 沖突。該沖突發生于數據庫 '%6!'，表 '%8!'%10!%11!%13!。”，但這些提示信息對最終用戶來說，是不友好的，于是就自己寫了個類，用來刪除記錄時的進行外鍵沖突檢測，代碼如下：

??1using?System;
??2using?System.Data;
??3using?System.Data.SqlClient;
??4using?Microsoft.ApplicationBlocks.Data;
??5
??6namespace?DataAccess.SQLServerDAL
??7{?
??8????/**////??
??9????///?Check?的摘要說明。?
?10????///?
?11????///??
?12????public?class?Check?
?13????{?
?14????????/**////???
?15????????///?DBMS中保存系統表的??
?16????????///???
?17????????const?string?DEFAULT_SYSTABLES?=?"systables";
?18????????CkeckFKBeginDelete#region?CkeckFKBeginDelete
?19????????/**////???
?20????????///?在刪除記錄之前先檢測有無外鍵沖突??
?21????????///
?22????????///?事物對象??
?23????????///?
?24????????///?要執行刪除操作的表名??
?25????????///?
?26????????///?要刪除的記錄的主鍵值??
?27????????///?
?28????????///?返回錯誤信息?
?29????????///?
?30????????///?true?-?無沖突，false?-?有沖突??
?31????????public?bool?CkeckFKBeginDelete(SqlTransaction?trans,?string?tableName,?string?id,?ref?string?errText)??
?32????????{???
?33????????????string?selectString;?
?34????????????//SQL查詢語句???
?35????????????string?fkTableName;??
?36????????????//外鍵表名稱???
?37????????????string?fkColumnName;?
?38????????????//外鍵列名稱???
?39????????????object?obj;????
?40????????????//執行SQL查詢返回值???
?41????????????string?description;??
?42????????????//外鍵表含義
?43????????????int?count;????//外鍵表中引用了主鍵的記錄數
?44????????????string[]?tableNames?=?{"sysforeignkeys"};
?45????????????DataSet?ds?=?BuildDataTables();
?46????????????//檢索所有此表的外鍵表???
?47????????????selectString?=?"SELECT?fkeyid,?fkey?FROM?sysforeignkeys?a,?sysobjects?b?WHERE?a.rkeyid?=?b.id?AND?b.name?=?@name";
?48????????????SqlParameter?name?=?new?SqlParameter("@name",?SqlDbType.VarChar);???name.Value?=?tableName;
?49????????????SqlHelper.FillDataset(trans,?CommandType.Text,?selectString,?ds,?tableNames,?name);
?50????????????//外鍵表Id???
?51????????????SqlParameter?Id?=?new?SqlParameter("@id",?SqlDbType.Int);???//外鍵列Id???
?52????????????SqlParameter?colid?=?new?SqlParameter("@colid",?SqlDbType.Int);???//主鍵值???
?53????????????SqlParameter?keyid?=?new?SqlParameter("@keyid",?SqlDbType.Int);??????//遍歷所有的外鍵表???
?54????????????foreach?(DataRow?dr?in?ds.Tables["sysforeignkeys"].Rows)???
?55????????????{????//查詢外鍵表名稱????
?56????????????????selectString?=?"SELECT?name?FROM?sysobjects?WHERE?id?=?@id";????
?57????????????????Id.Value?=?dr["fkeyid"];????
?58????????????????fkTableName?=?SqlHelper.ExecuteScalar(trans,?CommandType.Text,?selectString,?Id).ToString();???????
?59????????????????//查詢外鍵列名稱???
?60????????????????selectString?=?"SELECT?name?FROM?syscolumns?WHERE?id?=?@id?AND?colid?=?@colid";????
?61????????????????Id.Value?=?dr["fkeyid"];????
?62????????????????colid.Value?=?dr["fkey"];????
?63????????????????fkColumnName?=?SqlHelper.ExecuteScalar(trans,?CommandType.Text,?selectString,?Id,?colid).ToString();
?64????????????????//查詢外鍵表中有沒有引用要刪除的主鍵????selectString?=?"SELECT?COUNT(*)?FROM?"?+?fkTableName?+?"?WHERE?"?+?fkColumnName?+?"?=?@keyid";????
?65????????????????keyid.Value?=?id;????
?66????????????????count?=?Convert.ToInt32(SqlHelper.ExecuteScalar(trans,?CommandType.Text,?selectString,?keyid));
?67????
?68????????????????if?(count?>?0)????
?69????????????????{????
?70????????????????????//查詢發生沖突的表的含義，從而給用戶發出友好的提示?????
?71????????????????????selectString?=?"SELECT?description?FROM?callCenterTables?WHERE?tableName?=?@tableName";?????
?72????????????????????SqlParameter?TableName?=?new?SqlParameter("@tableName",?SqlDbType.VarChar);?????
?73????????????????????TableName.Value?=?fkTableName;
?74????????????????????obj?=?SqlHelper.ExecuteScalar(trans,?CommandType.Text,?selectString,?TableName);
?75????????????????????if?(obj?!=?null)??????
?76????????????????????????description?=?obj.ToString();?????
?77????????????????????else??????
?78????????????????????????description?=?fkTableName;
?79????????????????????errText?=?"您要刪除的數據已在"?+?description?+?"中使用，要刪除該條數據，請先刪除"?+??????description?+?"中的相關數據，否則您將無法刪除此條記錄！";
?80????????????????????return?false;????}???
?81????????????}
?82????????????return?true;??}
?83????????#endregion
?84????????BuildDataTables#region?BuildDataTables???
?85????????/**////???
?86????????///?創建外鍵DataTable??
?87????????///?
?88????????///?DataSet實例?
?89????????private?DataSet?BuildDataTables()??
?90????????{
?91????????????????DataSet?ds?=?new?DataSet();??????
?92????????????DataTable?table;???
?93????????????DataColumnCollection?columns;
?94????????????table???=?new?DataTable("sysforeignkeys");???
?95????????????columns?=?table.Columns;
?96????????????columns.Add("fkeyid",?typeof(System.Int32));???
?97????????????columns.Add("fkey",?typeof(System.Int32));???
?98????????????ds.Tables.Add(table);
?99????????????return?ds;??}
100????????#endregion?
101????}
102}
103<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

使用該類時需要在DBMS中建一張系統表，并維護表中的數據，該表用來記錄系統中各用戶表的大概含義，用來告訴用戶是什么地方發生了沖突：

create table sysTables(id????int not null IDENTITY(1,1)
PRIMARY KEY CLUSTERED,?/*ID*/
tableName??varchar(255),??????????/*用戶表名稱*/
description??varchar(255)??????????/*用戶表描述*/)

調用示例：

public?bool?test()??
????????{???
????????????//數據庫連接字符串???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類，可以到http://msdn.microsoft.com/library/en-us/dnbda/html/daab-rm.asp下載。目前該類僅適用于SQL Server數據庫

轉載于:https://www.cnblogs.com/Hedonister/archive/2005/06/07/169280.html

總結

以上是生活随笔為你收集整理的.Net中删除数据前进行外键冲突检测的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。