Foreign key conflict detection before deleting data in. net

In writing a database system, the easiest and safest way to ensure data consistency in the system is to establish a foreign key constraint in the DBMS. However, if the foreign key constraint is violated when deleting the primary key data, although the DBMS will give an error message, for example, the prompt message "% 1! Statement and % 2! % 3! Constraint '% 4! 'Conflicted. This conflict occurred in database '% 6! ', Table' % 8! '% 10! % 11! % 13 !.", However, these prompts are unfriendly to end users, so they write a class to detect foreign key conflicts when deleting records. The Code is as follows:

Using system;
Using system. Data;
Using system. Data. sqlclient;
Using Microsoft. applicationblocks. Data;

Namespace dataaccess. sqlserverdal
{
///
/// Summary of check.
///
Public class check
{
///
/// DBMS
///
Const string default_systables = "systables ";

# Region ckeckfkbegindelete

///
/// Check whether a foreign key conflict exists before deleting the record.
///
/// Object
/// Name of the table to be deleted
/// Primary key value of the record to be deleted
/// Error message returned
/// True-no conflict, false-conflict exists
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; // the return value of the SQL query.
String description; // meaning of the foreign key table

Int count; // number of records that reference the primary key in the foreign key table

String [] tablenames = {"sysforeignkeys "};

Dataset DS = builddatatables ();

// Retrieve all the foreign key tables of 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 );

// Traverse all foreign key tables
Foreach (datarow DR in DS. Tables ["sysforeignkeys"]. Rows)
{
// Query the 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 the 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 ();

// Query whether the foreign key table references the primary key to be deleted.
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 a conflicting table and send a friendly prompt to the user
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 this data, delete it first" +
Description + ", otherwise you cannot delete this record! ";

Return false;
}
}

Return true;
}

# Endregion

# Region builddatatables

///
/// Create a foreign key able
///
/// 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
}
}

When using this class, you need to create a system table in the DBMS and maintain the data in the table. This table is used to record the general meaning of each user table in the system and tell users where a conflict has occurred:

Create Table tables Ables
(
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 at http://msdn.microsoft.com/library/en-us/dnbda/html/daab-rm.asp. Currently, this class is only applicable to SQL Server databases.