1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37
|
public bool DeleteSimpleValue(string table, int id)
{
using (SqlCommand cmd1 = Cnx.CreateCommand(), cmd2 = Cnx.CreateCommand())
{
List<KeyValuePair<string, string>> list = new List<KeyValuePair<string, string>>();
SqlParameter pId = cmd2.Parameters.Add("id", SqlDbType.Int);
pId.Value = id;
cmd1.CommandText = "SELECT FK.TABLE_NAME [table], concat('select count(*) from [', FK.TABLE_NAME, '] where (', CU.COLUMN_NAME, ') in (select ', PT.COLUMN_NAME, ' from [', PK.TABLE_NAME, '] where id = @id)') query FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME INNER JOIN (SELECT i1.TABLE_NAME, i2.COLUMN_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1 INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2 ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY') PT ON PT.TABLE_NAME = PK.TABLE_NAME where PK.TABLE_NAME = @name";
SqlParameter pName = cmd1.Parameters.Add("name", SqlDbType.VarChar, 50);
pName.Value = table;
cmd1.Prepare();
SqlDataReader dr = cmd1.ExecuteReader(CommandBehavior.SequentialAccess | CommandBehavior.SingleResult);
while (dr.Read())
{
list.Add(new KeyValuePair<string, string>(dr.GetString(0), dr.GetString(1)));
}
dr.Close();
foreach (KeyValuePair<string, string> kv in list)
{
cmd2.CommandText = kv.Value;
cmd2.Prepare();
int nb = (int)cmd2.ExecuteScalar();
if (nb > 0)
{
MessageBox.Show(string.Format("This {0} is referenced in another table : {1}", table, kv.Key));
return false;
}
}
cmd2.CommandText = string.Format("delete {0} where id = @id", table);
cmd2.Prepare();
cmd2.ExecuteNonQuery();
return true;
}
} |
Partager