. Foreign key conflict detection before deleting data in net

The easiest and safest way to ensure the consistency of data in a database system is to establish a foreign key constraint in the DBMS, but if the foreign key constraint is violated when the primary key data is deleted, the DBMS will give you the error prompt, such as SQL Server's prompt message "%1! Statement and%2! %3! Constrain the '%4! ' conflict. The conflict occurred in the database '%6! ', table '%8! ' %10!%11!%13!. , but these tips are unfriendly to end users, so they write a class that deletes the foreign key conflict detection when the record is deleted, and the code reads as follows:

Using System;
Using System.Data;
Using System.Data.SqlClient;
Using Microsoft.ApplicationBlocks.Data;

Namespace Dataaccess.sqlserverdal
{
///
A summary description of the Check.
///
public class Check
{
///
In the DBMS that holds the system tables.
///
Const string default_systables = "Systables";

#region Ckeckfkbegindelete

///
Detect no foreign key conflicts before deleting records
///
Thing Object
The name of the table to perform the delete operation
The primary key value of the record to be deleted
return error message
True-no conflicts, false-conflicts
public bool Ckeckfkbegindelete (SqlTransaction trans, string tablename, string ID, ref string errtext)
{
String selectstring; SQL query Statement
String Fktablename; Foreign key table name
String Fkcolumnname; Foreign key Column name
Object obj; Execute SQL query return value
string description; Foreign key table meaning

int count; Number of records in the foreign key table that reference the primary key

String[] Tablenames = {"Sysforeignkeys"};

DataSet ds = Builddatatables ();

Retrieves the foreign key table for all this table
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);

FOREIGN key table ID
SqlParameter Id = new SqlParameter ("@id", SqlDbType.Int);
FOREIGN key Column ID
SqlParameter colid = new SqlParameter ("@colid", SqlDbType.Int);
Primary key value
SqlParameter keyID = new SqlParameter ("@keyid", SqlDbType.Int);

Iterate through all the foreign key tables
foreach (DataRow Dr in DS. tables["Sysforeignkeys"]. Rows)
{
Query foreign key table name
selectstring = "SELECT name from sysobjects WHERE id = @id";
Id.value = dr["Fkeyid"];
Fktablename = Sqlhelper.executescalar (trans, CommandType.Text, SelectString, Id). ToString ();

Query foreign key Column name
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 ();

Is there any reference to the primary key to delete in the Query foreign key table?
selectstring = "Select COUNT (*) from" + Fktablename + "WHERE" + fkcolumnname + "= @keyid";
keyID. Value = ID;
Count = Convert.ToInt32 (Sqlhelper.executescalar (trans, CommandType.Text, SelectString, keyID));

if (Count > 0)
{
Query the meaning of conflicting tables to give users friendly hints
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 = "The data you want to delete is already used in" + Description + ", to delete the data, delete the" +
Description + ", otherwise you will not be able to delete this record!" ";

return false;
}
}

return true;
}

#endregion

#region Builddatatables

///
Create a foreign key DataTable
///
DataSet instance
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
}
}



Using this class, you need to build a system table in the DBMS and maintain the data in the table, which is used to record the approximate meaning of each user table in the system to tell the user where the conflict occurred:

CREATE TABLE Systables
(
ID int NOT NULL IDENTITY (1,1) PRIMARY KEY CLUSTERED,/*id*/
TableName varchar (255),/* User table name * *
Description varchar (255)/* User Table Description * *
)



Call Example:

public bool Test ()
{
Database connection string
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
}
}
}
}



The SqlHelper class of MS is used in the code and can be downloaded to http://msdn.microsoft.com/library/en-us/dnbda/html/daab-rm.asp. This class is currently available only for SQL Server databases