实现删除SQL关联表中的内容

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  

不好意思    由于是下班时间发的  可能里面有很多错误没能及时修改   请见谅、、