In writing a database system, the easiest way to ensure that the data in the system is the easiest and secure, but if the primary key data is deleted, if the primary key data is deleted, although DBMS will give an error message, such as SQL Server's prompt information "% 1! Statement and% 2!% 3! Constrained '% 4!' Conflict. This conflict occurs in database '% 6!', Table '% 8!'% 10!% 11!% 13! ", But these prompt information is unfriendly, so I wrote a class to delete the record, the code is as follows:
Using system; using system.data.sqlclient; using microsoft.ApplicationBlocks.Data;
Namespace DataAccess.sqlServerdal {////// Check Summary Description. /// public class check {/// /// DBMS Save system table // const string default_systables = "SSTABLES";
#Region Ckeckfkbegindelete
// // Prior to deleting a record /// /////// // / / ////bit of the record of the deletion operation to execute /// back to the record error message /// true - no conflict, false - there is a conflict public bool CkeckFKBeginDelete (SqlTransaction trans, string tableName, string id, ref string errText) {string selectString; // SQL query string fkTableName; // foreign key table name string FkColumnName; // Foreign key list name object obj; // execute SQL query return value string description; // foreign key table meaning
INT count; // The number of records of the primary key is referenced in the foreign key table.
String [] TABLENAMES = {"sysForeignKeys"};
DataSet DS = buildDataTables ();
// Retrieve all the foreign key table 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.FILDATASET (Trans, CommandType.Text, SelectString, DS, Tablenames, Name);
// Foreign key table idsqlparameter id = new sqlparameter ("@ id", sqldbtype.int); // Foreign key column idsqlparameter 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 ["sysForeIgnys"]. 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 (); // Query the primary key of the reference to delete in the external key table SelectString = "SELECT Count (*) from" fktablename "where" fkcolumnname "= @keyid" Keyid.Value = ID; count = convert.toint32 (SQLHELPER.EXECUTESCALARARAR (TRANS, CommandType.Text, SelectString, KeyID);
if (count> 0) {// query table meaning of conflict, which issued a friendly prompt selectString = "SELECT description FROM callCenterTables WHERE tableName = @tableName" to the user; SqlParameter TableName = new SqlParameter ( "@ tableName", SqlDbType .Varchar); TableName.Value = fktablename;
Obj = sqlhelper.executescalar (Trans, CommandType.Text, SelectString, TableName);
IF (Obj! = null) description = obj.tostring (); elsedescription = fktablename
Errtext = "The data you want to delete is used in" Description ". To delete the data, remove the related data in" Description "first, otherwise you will not be able to delete this record!"; Return False }}
Return True;}
#ndregion
#REGON BUILDDATABLES / / / / / / DATASET Instance Private Dataset DS = New Dataset (); DataSet DS = New DataSet (); DataTable Table; DatacolumnCollection Column
Table = New DataTable ("sysForeignKeys"); columns = table.columns;
Column.Add ("fkeyid", typeof (system.int32)); columns.add ("fkey", typeof (system.int32)); ds.tables.add (table);
Return DS;
#ndregion}}}
When using this class, you need to build a system table in DBMS, and maintain the data in the table, which is used to record the probably implicit meaning of each user table in the system.
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}}}}}
The code used to the MS's SQLHELPER class, you can download at http://msdn.microsoft.com/library/en-us/dnbda/html/daab-rm.asp. This class is only available for SQL Server databases
