.Net中删除数据前进行外键冲突检测

langmafeng

在编写数据库系统中为了保证系统中数据的一致性最简便且安全的方法就是在DBMS中建立外键约束，但删除主键数据时如果违反了外键约束，尽管DBMS会给出错误提示，如SQL Server的提示信息“%1! 语句与 %2! %3! 约束 ´%4!´ 冲突。该冲突发生于数据库 ´%6!´，表 ´%8!´%10!%11!%13!。”，但这些提示信息对最终用户来说，是不友好的，于是就自己写了个类，用来删除记录时的进行外键冲突检测，代码如下：

using System;
using System.Data;
using System.Data.SqlClient;
using Microsoft.ApplicationBlocks.Data;

namespace DataAccess.SQLServerDAL
{
///

/// Check 的摘要说明。
///

public class Check
{
///

/// DBMS中保存系统表的
///

const string DEFAULT_SYSTABLES = "systables";

#region CkeckFKBeginDelete

///

/// 在删除记录之前先检测有无外键冲突
///

  /// 事物对象
  /// 要执行删除操作的表名
  /// 要删除的记录的主键值
  /// 返回错误信息
  /// true - 无冲突，false - 有冲突
  public bool CkeckFKBeginDelete(SqlTransaction trans, string tableName, string id, ref string errText)
  {
   string selectString; //SQL查询语句
   string fkTableName;  //外键表名称
   string fkColumnName; //外键列名称
   object obj;    //执行SQL查询返回值
   string description;  //外键表含义

int count; //外键表中引用了主键的记录数

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)
   {
    //查询外键表名称
    selectString = "SELECT name FROM sysobjects WHERE id = @id";
    Id.Value = dr["fkeyid"];
    fkTableName = SqlHelper.ExecuteScalar(trans, CommandType.Text, selectString, Id).ToString();

    //查询外键列名称
    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();

    //查询外键表中有没有引用要删除的主键
    selectString = "SELECT COUNT(*) FROM " + fkTableName + " WHERE " + fkColumnName + " = @keyid";
    keyid.Value = id;
    count = Convert.ToInt32(SqlHelper.ExecuteScalar(trans, CommandType.Text, selectString, keyid));

    if (count > 0)
    {
     //查询发生冲突的表的含义，从而给用户发出友好的提示
     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 = "您要删除的数据已在" + description + "中使用，要删除该条数据，请先删除" +
description + "中的相关数据，否则您将无法删除此条记录！";

     return false;
    }
   }

return true;
}

#endregion

#region BuildDataTables

///

/// 创建外键DataTable
///

  /// DataSet实例
  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
}
}

使用该类时需要在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数据库