一步一步asp.net_删除模块设计

删除使我们经常性碰到的模块,无处不在删除,但是删除在业务中是非常重要的,因为一般删除涉及到数据库,图片,等等,往往关联多个数据库,甚至是分布式服务器,数据库,图片服务器图片的删除,所以保持事务的一致性,是非常重要的,比如下图:

在这个简单的业务中,涉及到删除到图片删除的模块:

大师(大师有一个logo主图片),

大师荣誉证书(n张),

企业(企业logo一张图片),

企业美景图(用于展示企业空间tab轮转切换)

企业荣誉证书(n张荣誉证书)

产品(主图片一张)

产品副图片(n张)

而图片的尺寸一般分为三种,大中小三种型号,用于缓解服务器鸭梨,很多地方都加在小图片

这时候,我们要做删除功能必须做数据库删除和图片删除两个,而且必须保证事务的一致性,也就是说,先删除数据库,如果数据库删除成功才去删除图片.

但是这又有一个问题:

          如果先删除数据库,图片信息已经删除了,怎么删除图片?

这时候,需要我们预先保存要删除的图片,然后,在执行数据库删除,如果数据库删除成功,在进行图片的删除.

但是又有一个问题,删除图片,涉及大量的删除,如果我们写死在一块,就会造成一个模块出现大量的代码,这就加大了复杂程度.

特别是类别删除,一个类别删除,首先要删除,大师类别,企业类别,如果删除大师/企业类别,又必须删除相应的大师和企业,如果删除相应的大师和企业,又必须删除,大师和企业对应的产品,如果删除产品又要删除产品对应的收藏夹,购物车等等,再加上图片删除,可以想象,一个删除功能最少上千行代码,我们就应该把删除功能抽取出来,实现软件复用.

我们采用三层构架,这时候作用非常明显了,我们的DAL层专门处理数据库删除,不去关心怎

么删除图片问题,图片的删除是业务逻辑层需要解决的问题,因此,我们可以实现,整个网站先

不做删除,最后一起做,这样,职责清晰明了.

这就是产品删除层次图:

这是数据库DAL层删除

但是如果是普通的删除方法,比如,删除类别,然后根据类别id去查找大师或者企业,然后根据大师或者企业类别去查找产品等等,这势必会导致指数级的sql语句,这时候我们需要做sql优化,优化的地方在哪里呢?

  我们根据类别查找出大师的企业,这时候,对应多个企业和大师,但是,都有很多产品,我们再根据产品去删除产品其他信息,尽量保证在O(n)这个级别的sql,尽量不要出现sql在循环中,那样势必造成无数个连接.

然后删除就是一个纯体力活,不断的写删除,不过主要是思维要清晰,要保证事务,是非常重要的.

crafttype删除:

     #region 删除
        #region 获得属于该类别的大师的删除语句
        /// <summary>
        /// 获得属于该类别的大师的删除语句
        /// </summary>
        /// <param name="Id"></param>
        /// <returns></returns>
        internal string GetDeleteMastersSql(string Id)
        {
            string sql = "select Masterid from master_type where  Typeid=" + Id;
            DataTable dt = SqlHelper.ExecuteDataTable(sql);
            StringBuilder sb = new StringBuilder();
            masterDAL dal = new masterDAL();
            foreach (DataRow dr in dt.Rows)
            {
                sb.Append(dal.GetMasterDeleteSql(Convert.ToInt32(dr["Masterid"])));
            }
            return sb.ToString();
        }
        #endregion
        #region 获得属于该类别的企业的删除语句
        /// <summary>
        /// 获得属于该类别的企业的删除语句
        /// </summary>
        /// <param name="Id"></param>
        /// <returns></returns>
        internal string GetDeleteCompanySql(string Id)
        {
            string sql = "select Companyid from company_type where  Typeid=" + Id;
            DataTable dt = SqlHelper.ExecuteDataTable(sql);
            StringBuilder sb = new StringBuilder();
            companyDAL dal = new companyDAL();
            foreach (DataRow dr in dt.Rows)
            {
                sb.Append(dal.GetCompanyDeleteSql(Convert.ToInt32(dr["Companyid"])));
            }
            return sb.ToString();
        }
        #endregion
        #region 获得属于多个类别的大师的删除语句
        /// <summary>
        /// 获得属于多个类别的大师的删除语句
        /// </summary>
        /// <param name="Id"></param>
        /// <returns></returns>
        internal string GetDeleteMoreMastersSql(string Ids)
        {
            string sql = "select Masterid from master_type where  Typeid in (" + Ids + ")";
            DataTable dt = SqlHelper.ExecuteDataTable(sql);
            StringBuilder sb = new StringBuilder();
            masterDAL dal = new masterDAL();
            foreach (DataRow dr in dt.Rows)
            {
                sb.Append(dal.GetMasterDeleteSql(Convert.ToInt32(dr["Masterid"])));
            }
            return sb.ToString();
        }
        #endregion
        #region 获得属于多个类别的企业的删除语句
        /// <summary>
        /// 获得属于多个类别的企业的删除语句
        /// </summary>
        /// <param name="Id"></param>
        /// <returns></returns>
        internal string GetDeleteMoreCompanySql(string Ids)
        {
            string sql = "select Companyid from company_type where  Typeid in (" + Ids + ")";
            DataTable dt = SqlHelper.ExecuteDataTable(sql);
            StringBuilder sb = new StringBuilder();
            companyDAL dal = new companyDAL();
            foreach (DataRow dr in dt.Rows)
            {
                sb.Append(dal.GetCompanyDeleteSql(Convert.ToInt32(dr["Companyid"])));
            }
            return sb.ToString();
        }
        #endregion
        #region 删除crafttype
        /// <summary>
        /// 删除crafttype
        /// </summary>
        /// <param name="id">id</param>
        /// <returns>执行状态</returns>
        public bool Delete(int id)
        {
            StringBuilder sb = new StringBuilder();
            // 获取删除企业的sql语句
            sb.Append(GetDeleteCompanySql(id.ToString ()));
            //获取删除大师的sql语句
            sb.Append(GetDeleteMastersSql(id.ToString ()));
            sb.Append("delete from craftknowledge where Typeid=" + id);
            sb.Append("delete from crafttype where id=" + id);
            bool status = false;
            SqlHelper.Open();
            SqlHelper.BeginTrans();
            if (SqlHelper.ExecuteNonQuery(sb.ToString()))
            {
                status = true;
                SqlHelper.CommitTrans();
            }
            else
            {
                status = false;
                SqlHelper.RollbackTrans();
            }

            return status;
        }
        #endregion
        #region 删除crafttype(多选)
        /// <summary>
        /// 删除crafttype(多选)
        /// </summary>
        /// <param name="strID">strID,记得多个用,隔开</param>
        /// <returns>执行状态</returns>
        public bool DeleteMoreID(string strID)
        {
            bool status = false;
            string[] IDs = strID.Split(',');
            StringBuilder sb = new StringBuilder();
            sb.Append(GetDeleteMoreMastersSql(strID));
            sb.Append(GetDeleteMoreCompanySql(strID));
            for (int i = 0; i < IDs.Length; i++)
            {
             sb.Append(";delete from craftknowledge where  Typeid=" + IDs[i]);
            }
            sb.Append(";delete from crafttype where id in (" + strID + ")");
            SqlHelper.Open();
            SqlHelper.BeginTrans();
            if (SqlHelper.ExecuteNonQuery(sb.ToString()))
            {
                status = true;
                SqlHelper.CommitTrans();
            }
            else
            {
                status = false;
                SqlHelper.RollbackTrans();
            }

            return status;

        }
        #endregion
        #endregion
    }

crafttype调用的master和company中构造删除的sql,master和company又迭代的构造删除product的sql,

这样就能节省很多工作,性能也有了较大的提高.

构造company删除:

        #region 删除
       /// <summary>
       /// 删除company的sql语句生成
       /// </summary>
       /// <param name="id"></param>
       /// <returns></returns>
        internal string GetCompanyDeleteSql(int id)
        {
            string sql = "select Id from product where  Companyid=" + id;
            DataTable dt = SqlHelper.ExecuteDataTable(sql);
            StringBuilder sb = new StringBuilder();
            productDAL dal = new productDAL();
            foreach (DataRow dr in dt.Rows)
            {
                sb.Append(dal.GetDeleteProductSqlString(dr["Id"].ToString()));
            }
            sb.Append(";delete from company_pic where Companyid=" + id);
            sb.Append(";delete from company_cert where  Companyid=" + id);
            sb.Append(";delete from company_type where  Companyid=" + id);
            sb.Append(";delete from company where Id="+id+";");
            return sb.ToString();
        }

构造大师删除sql:

        /// <summary>
        /// 删除master的sql语句生成
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        internal string GetMasterDeleteSql(int id)
        {
            string sql = "select Id from product where Belongstype='0' and Masterid=" + id;
            DataTable dt = SqlHelper.ExecuteDataTable(sql);
            StringBuilder sb = new StringBuilder();
            productDAL dal = new productDAL();
            foreach (DataRow dr in dt.Rows)
            {
                //获取删除产品所有信息的SQL(构造出sql)
                sb.Append(dal.GetDeleteProductSqlString(dr["Id"].ToString()));
            }
            sb.Append(";delete from master_cert where  Masterid=" + id);
            sb.Append(";delete from master_type where  Masterid=" + id);
            sb.Append(";delete from master where Id=" + id + ";");
            return sb.ToString();
        }

构造产品删除:

        #region 获取删除产品的前置sql语句
        /// <summary>
        /// 获取删除产品的前置sql语句
        /// </summary>
        /// <param name="ProductId"></param>
        /// <returns></returns>
        internal string GetDeleteProductSqlString(string ProductId)
        {
            string sql = string.Format("delete from MemberCollection where ProductId={0};delete from ShoppingCart where ProductId={0};delete from comment where Productid={0};delete from product_picturepath where Productid={0}; delete from product where id={0};", ProductId);
            return sql;
        }
        #endregion

这就是关于类别的删除,太复杂了,乱七八糟,需要思路清晰.

不过总体都是DAL层的删除工作,接下来就是图片删除:

图片删除,首先要构造一个删除类,专门负责删除图片(大中小),一个负责获取图片路径

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
namespace Common
{
    /// <summary>
    /// 潮州工艺品图片删除
    /// </summary>
   public  class CzcraftDeletePic
    {
       public enum DeletePicType
       {
           /// <summary>
           /// 大师图片
           /// </summary>
           GetMasterPic=0,
           /// <summary>
           /// 大师荣誉图片
           /// </summary>
           GetMasterCert=1,
           /// <summary>
           /// 企业荣誉图片
           /// </summary>
           GetCompanyCert=2,
           /// <summary>
           /// 企业主图片
           /// </summary>
           GetLogoCompanyPic=3,
           /// <summary>
           /// 企业美景图
           /// </summary>
           GetCompanyPic=4,
           /// <summary>
           /// 产品主图片
           /// </summary>
           GetMainProductPic=5,
           /// <summary>
           /// 产品副图片
           /// </summary>
           GetOtherProductPic=6
       }
       /// <summary>
        /// 潮州工艺品图片删除
       /// </summary>
        /// <param name="FileType">文件类别(GetMasterPic,GetMasterCert,GetCompanyCert,GetLogoCompanyPic,GetMainProductPic,GetOtherProductPic)</param>
       /// <param name="FileName">文件名</param>
       public static void FileDelete(DeletePicType FileType, string FileName)
       {
          string VirtualPath= GetPicPath(FileType, FileName);
           //删除文件
          FileOperate.FileDelete(VirtualPath, true);
       }
       /// <summary>
       /// 获取文件路径(根据文件类别)
       /// </summary>
       /// <param name="FileType">文件类别</param>
       /// <param name="FileName">文件名</param>
       /// <returns></returns>
       public static string GetPicPath(DeletePicType FileType, string FileName)
       {
           string PicServerPath = ConfigurationManager.AppSettings["PicServerPath"];
           string VirtualFilePath = PicServerPath;
           switch (FileType)
           {
               case DeletePicType.GetMasterPic:
                   VirtualFilePath += "Master/MainMasterPic/";
                   break;
               case DeletePicType.GetMasterCert:
                   VirtualFilePath += "Master/MasterCert/";
                   break;
               case DeletePicType.GetCompanyCert:
                   VirtualFilePath += "Company/CompanyCert/";
                   break;
               case DeletePicType.GetCompanyPic:
                   VirtualFilePath += "Company/MainCompanyPic/";
                   break;
               case DeletePicType.GetLogoCompanyPic:
                   VirtualFilePath += "Company/CompanyPic/";
                   break;
               case DeletePicType.GetMainProductPic:
                   VirtualFilePath += "Product/MainProductPic/";
                   break;
               case DeletePicType.GetOtherProductPic:
                   VirtualFilePath += "Product/OtherProductPic/"; ;
                   break;
               default:
                throw new Exception("文件类别输入有误!");



           }
           VirtualFilePath += FileName;
           return VirtualFilePath;
       }
    }
}

图片的路径写在webconfig中,接下来就是业务逻辑方面的删除,

我们要保证先从数据库中查出图片,然后删除数据库,最后删除图片.

这个业务逻辑层就复杂得多了.

同样是类别删除:

      #region 删除
       #region 删除crafttype
       /// <summary>
       /// 删除crafttype
       /// </summary>
       /// <param name="id">id</param>
       /// <returns>执行状态</returns>
       public bool Delete(int id)
       {
           #region 大师图片信息保存
           //获取大师主Id信息
           DataTable dtMasterIds = new masterDAL().GetAllMasterByCraftType(id.ToString());
           StringBuilder MasterIds = new StringBuilder();
           foreach (DataRow dr in dtMasterIds.Rows)
           {
               MasterIds.Append(dr["Masterid"].ToString());
           }
           MasterIds.Remove(MasterIds.Length - 1, 1);
           //大师多个id
           string strMasterIds = MasterIds.ToString();
           //获取大师主图片
           DataTable dtMasterPath = new masterDAL().GetMasterMainPic(strMasterIds);
           //获取大师荣誉图片
           DataTable dtMasterCert = new master_certDAL().GetMoreMasterCertPath(strMasterIds);
           //获取大师产品图片
           DataTable dtMasterProducts = new productDAL().GetProductsPicByMoreMasterIds(strMasterIds);
           //获取大师其他产品图片
           StringBuilder ProductIds = new StringBuilder();
           foreach (DataRow dr in dtMasterProducts.Rows)
           {
               ProductIds.Append(dr["Id"].ToString());
           }
           ProductIds.Remove(ProductIds.Length - 1, 1);
           DataTable dtProductOtherProducts = new product_picturepathDAL().GetMoreOtherProductPicPath(ProductIds.ToString());
           #endregion
           #region 企业图片信息保存
           //获取企业主Id信息
           DataTable dtCompanyIds = new companyDAL().GetAllCompanyByCraftType(id.ToString());
           StringBuilder CompanyIds = new StringBuilder();
           foreach (DataRow dr in dtCompanyIds.Rows)
           {
               CompanyIds.Append(dr["Masterid"].ToString());
           }
           CompanyIds.Remove(CompanyIds.Length - 1, 1);
           //企业多个id
           string strCompanyIds = CompanyIds.ToString();
           //获取企业主图片
           DataTable dtCompanyPath = new companyDAL().GetCompanyMainPic(strCompanyIds);
           //获取企业美景图
           DataTable dtCompanyPic = new company_picDAL().GetCompanyPicByMoreCompanyIds(strCompanyIds);
           //获取企业荣誉图片
           DataTable dtCompanyCert = new company_certDAL().GetMoreCompanyCertPath(strCompanyIds);
           //获取企业产品图片
           DataTable dtCompanyProducts = new productDAL().GetProductsPicByMoreCompanyIds(strCompanyIds);
           //获取企业其他产品图片
           StringBuilder CompanyProductIds = new StringBuilder();
           foreach (DataRow dr in dtCompanyProducts.Rows)
           {
               CompanyProductIds.Append(dr["Id"].ToString());
           }
           CompanyProductIds.Remove(CompanyProductIds.Length - 1, 1);
           DataTable dtCompanyOtherProducts = new product_picturepathDAL().GetMoreOtherProductPicPath(ProductIds.ToString());
           #endregion
           //删除
           crafttypeDAL dal = new crafttypeDAL();
           bool Status = dal.Delete(id);
           //如果执行成功,则删除图片
           masterBLL bll = new masterBLL();
           companyBLL companyBll = new companyBLL();
           if (Status)
           {
               #region 大师图片删除
               //删除大师主图片
               bll.DeleteMainMasterPath(dtMasterPath);

               //删除大师荣誉证书

               bll.DeleteMasterCertPic(dtMasterCert);
               //删除所有产品图片

               bll.DeleteProdoctsByMasterId(dtMasterProducts);
               //删除产品其他图片
               new product_picturepathBLL().DeleteMoreOtherProductPic(dtProductOtherProducts);
               #endregion
               #region 企业图片删除
               //删除企业主图片
               companyBll.DeleteMainCompanyPath(dtCompanyPath);
               //删除企业美景图
               companyBll.DeleteCompanyPic(dtCompanyPic);

               //删除多个企业荣誉图片

               companyBll.DeleteCompanyCertPic(dtCompanyCert);
               //删除所有产品图片

               companyBll.DeleteProdoctsByCompanyId(dtCompanyProducts);
               //删除产品其他图片
               new product_picturepathBLL().DeleteMoreOtherProductPic(dtCompanyOtherProducts);
               #endregion

           }
           return Status;

       }
       #endregion
       #region 删除crafttype
       /// <summary>
       /// 删除crafttype
       /// </summary>
       /// <param name="strID">strID,记得多个用,隔开</param>
       /// <returns>执行状态</returns>
       public bool DeleteMoreID(string strID)
       {
           #region 大师图片信息保存
           //获取大师主Id信息
           DataTable dtMasterIds = new masterDAL().GetAllMasterByCraftType(strID);
           StringBuilder MasterIds = new StringBuilder();
           foreach (DataRow dr in dtMasterIds.Rows)
           {
               MasterIds.Append(dr["Masterid"].ToString());
           }
           MasterIds.Remove(MasterIds.Length - 1, 1);
           //大师多个id
           string strMasterIds = MasterIds.ToString();
           //获取大师主图片
           DataTable dtMasterPath = new masterDAL().GetMasterMainPic(strMasterIds);
           //获取大师荣誉图片
           DataTable dtMasterCert = new master_certDAL().GetMoreMasterCertPath(strMasterIds);
           //获取大师产品图片
           DataTable dtMasterProducts = new productDAL().GetProductsPicByMoreMasterIds(strMasterIds);
           //获取大师其他产品图片
           StringBuilder ProductIds = new StringBuilder();
           foreach (DataRow dr in dtMasterProducts.Rows)
           {
               ProductIds.Append(dr["Id"].ToString());
           }
           ProductIds.Remove(ProductIds.Length - 1, 1);
           DataTable dtProductOtherProducts = new product_picturepathDAL().GetMoreOtherProductPicPath(ProductIds.ToString());
           #endregion
           #region 企业图片信息保存
           //获取企业主Id信息
           DataTable dtCompanyIds = new companyDAL().GetAllCompanyByCraftType(strID);
           StringBuilder CompanyIds = new StringBuilder();
           foreach (DataRow dr in dtCompanyIds.Rows)
           {
               CompanyIds.Append(dr["Masterid"].ToString());
           }
           CompanyIds.Remove(CompanyIds.Length - 1, 1);
           //企业多个id
           string strCompanyIds = CompanyIds.ToString();
           //获取企业主图片
           DataTable dtCompanyPath = new companyDAL().GetCompanyMainPic(strCompanyIds);
           //获取企业美景图
           DataTable dtCompanyPic = new company_picDAL().GetCompanyPicByMoreCompanyIds(strCompanyIds);
           //获取企业荣誉图片
           DataTable dtCompanyCert = new company_certDAL().GetMoreCompanyCertPath(strCompanyIds);
           //获取企业产品图片
           DataTable dtCompanyProducts = new productDAL().GetProductsPicByMoreCompanyIds(strCompanyIds);
           //获取企业其他产品图片
           StringBuilder CompanyProductIds = new StringBuilder();
           foreach (DataRow dr in dtCompanyProducts.Rows)
           {
               CompanyProductIds.Append(dr["Id"].ToString());
           }
           CompanyProductIds.Remove(CompanyProductIds.Length - 1, 1);
           DataTable dtCompanyOtherProducts = new product_picturepathDAL().GetMoreOtherProductPicPath(ProductIds.ToString());
           #endregion
           //删除
           crafttypeDAL dal = new crafttypeDAL();
           bool Status = dal.DeleteMoreID(strID);
           //如果执行成功,则删除图片
           masterBLL bll = new masterBLL();
           companyBLL companyBll = new companyBLL();
           if (Status)
           {
               #region 大师图片删除
               //删除大师主图片
               bll.DeleteMainMasterPath(dtMasterPath);

               //删除大师荣誉证书

               bll.DeleteMasterCertPic(dtMasterCert);
               //删除所有产品图片

               bll.DeleteProdoctsByMasterId(dtMasterProducts);
               //删除产品其他图片
               new product_picturepathBLL().DeleteMoreOtherProductPic(dtProductOtherProducts);
               #endregion
               #region 企业图片删除
               //删除企业主图片
               companyBll.DeleteMainCompanyPath(dtCompanyPath);
               //删除企业美景图
               companyBll.DeleteCompanyPic(dtCompanyPic);

               //删除多个企业荣誉图片

               companyBll.DeleteCompanyCertPic(dtCompanyCert);
               //删除所有产品图片

               companyBll.DeleteProdoctsByCompanyId(dtCompanyProducts);
               //删除产品其他图片
               new product_picturepathBLL().DeleteMoreOtherProductPic(dtCompanyOtherProducts);
               #endregion

           }
           return Status;
       }
       #endregion
        #endregion
    }

然后具体细节的删除就更多了.这里就不一一概述了....可以发现其实一个删除都挺复杂了......不过由于三层的因素简化了太多太多了,以前的一个类别删除,我也不知道要删除多少东西..........

总之,记住一句话,DAL负责数据库交互,BLL负责业务逻辑,这样才能让项目思路更清晰,更容易维护!

当然,我感觉现在这个逻辑也还是乱乱的,不过已经清晰太多了,至少,把DAL个部分职责分的非常清晰!