namespace DeleteTableInfo{ public class Program { public static void Main(string[] args) { Console.WriteLine("表名:"); string talbe = Console.ReadLine(); Console.WriteLine("列名:"); string ID = Console.ReadLine(); Console.WriteLine("列值:"); string value = Console.ReadLine(); DeletePK_Table(talbe, ID, value); } #region 删除带关联的表信息 /// <summary> /// 删除带关联的表信息 /// </summary> /// <param name="table">表名</param> /// <param name="param">列</param> /// <param name="value">值</param> public static void DeletePK_Table(string table, string param, object value) { // DB.Connection.Open(); using (SqlConnection conn = new SqlConnection(@"server=.;database=DB;uid=sa;pwd=sa")) { conn.Open(); string delsql = delInfo(table, param, value.ToString(), 0, conn); //用事务提交 SqlTransaction tran = conn.BeginTransaction(); try { SqlCommand cmd = new SqlCommand(delsql, conn); cmd.Transaction = tran; cmd.ExecuteNonQuery(); tran.Commit(); } catch (Exception e) { tran.Rollback(); throw new Exception(e.Message); } } } /// <summary> /// 返回删除关联表的SQL /// </summary> /// <param name="Ftable">外键表</param> /// <param name="FColumn">外键列</param> /// <param name="where">条件</param> /// <param name="index">层【刚调用时写0】</param> /// <param name="conn"></param> /// <returns></returns> public static string delInfo(string Ftable, string FColumn, string where, int index, SqlConnection conn) { String sql = "sp_fkeys"; SqlCommand cmd = new SqlCommand(sql, conn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add(new SqlParameter("@PKTABLE_NAME", Ftable)); SqlDataReader sda = cmd.ExecuteReader(); List<tableInfo> list = new List<tableInfo>(); tableInfo table;//= new tableInfo(); while (sda.Read())//取到所有的子表 ................................ { table = new tableInfo(); table.PKTABLE_NAME = sda["PKTABLE_NAME"].ToString(); table.PKCOLUMN_NAME = sda["PKCOLUMN_NAME"].ToString(); table.FKTABLE_NAME = sda["FKTABLE_NAME"].ToString(); table.FKCOLUMN_NAME = sda["FKCOLUMN_NAME"].ToString(); if (index == 0)//如果是第一次 { table.tempSql = "Left Join [" + table.PKTABLE_NAME + "] as [t0] on [t0].[" + table.PKCOLUMN_NAME + "]= [t1].[" + table.FKCOLUMN_NAME + "] where [t0].[" + FColumn + "]= " + where; } else { table.tempSql = "Left Join [" + table.PKTABLE_NAME + "] as [t" + (index) + "] on [t" + (index) + "].[" + table.PKCOLUMN_NAME + "]= [t" + (index + 1) + "].[" + table.FKCOLUMN_NAME + "] " + where; } // Console.WriteLine("删除表格的条件:" + table.tempSql); list.Add(table); } sda.Close(); StringBuilder resSql = new StringBuilder(); ; foreach (var item in list) { if (item.FKTABLE_NAME == Ftable) { SqlCommand cmd2 = new SqlCommand("SELECT count(*) from [" + item.FKTABLE_NAME + "] AS [t" + (index + 1) + "] " + item.tempSql + " and [t" + (index + 1) + "].[" + item.PKCOLUMN_NAME + "] != [t" + (index + 1) + "].[" + item.FKCOLUMN_NAME + "] AND [t" + (index + 1) + "].[" + item.FKCOLUMN_NAME + "] IS NOT NULL", conn); if (Convert.ToUInt32(cmd2.ExecuteScalar()) == 0) { //resSql.Append(delInfo(item.FKTABLE_NAME, item.FKCOLUMN_NAME, item.tempSql, index + 1, conn)); } else { //如呆是内联表........ } } else//从这里递归、 先生成删除子表的SQL resSql.Append(delInfo(item.FKTABLE_NAME, item.FKCOLUMN_NAME, item.tempSql, index + 1, conn)); } return resSql.Append("DELETE [t" + index + "] FROM [" + Ftable + "] as [t" + index + "] " + (index == 0 ? ("Where [t" + index + "].[" + FColumn + "] = " + where) : where) + " ; ").ToString(); } class tableInfo { /// <summary> /// 主键表 /// </summary> public string PKTABLE_NAME { get; set; } /// <summary> /// 主键列 /// </summary> public string PKCOLUMN_NAME { get; set; } /// <summary> /// 外键表 /// </summary> public string FKTABLE_NAME { get; set; } /// <summary> /// 外键列 /// </summary> public string FKCOLUMN_NAME { get; set; } /// <summary> /// 约束条件 /// </summary> public string tempSql { get; set; } } #endregion }}?
1 楼 wodexxh 2011-03-31
不好意思 由于是下班时间发的 可能里面有很多错误没能及时修改 请见谅、、