删除使我们经常性碰到的模块,无处不在删除,但是删除在业务中是非常重要的,因为一般删除涉及到数据库,图片,等等,往往关联多个数据库,甚至是分布式服务器,数据库,图片服务器图片的删除,所以保持事务的一致性,是非常重要的,比如下图:
在这个简单的业务中,涉及到删除到图片删除的模块:
大师(大师有一个logo主图片),
大师荣誉证书(n张),
企业(企业logo一张图片),
企业美景图(用于展示企业空间tab轮转切换)
企业荣誉证书(n张荣誉证书)
产品(主图片一张)
产品副图片(n张)
而图片的尺寸一般分为三种,大中小三种型号,用于缓解服务器鸭梨,很多地方都加在小图片
这时候,我们要做删除功能必须做数据库删除和图片删除两个,而且必须保证事务的一致性,也就是说,先删除数据库,如果数据库删除成功才去删除图片.
但是这又有一个问题:
如果先删除数据库,图片信息已经删除了,怎么删除图片?
这时候,需要我们预先保存要删除的图片,然后,在执行数据库删除,如果数据库删除成功,在进行图片的删除.
但是又有一个问题,删除图片,涉及大量的删除,如果我们写死在一块,就会造成一个模块出现大量的代码,这就加大了复杂程度.
特别是类别删除,一个类别删除,首先要删除,大师类别,企业类别,如果删除大师/企业类别,又必须删除相应的大师和企业,如果删除相应的大师和企业,又必须删除,大师和企业对应的产品,如果删除产品又要删除产品对应的收藏夹,购物车等等,再加上图片删除,可以想象,一个删除功能最少上千行代码,我们就应该把删除功能抽取出来,实现软件复用.
我们采用三层构架,这时候作用非常明显了,我们的DAL层专门处理数据库删除,不去关心怎
么删除图片问题,图片的删除是业务逻辑层需要解决的问题,因此,我们可以实现,整个网站先
不做删除,最后一起做,这样,职责清晰明了.
这就是产品删除层次图:
这是数据库DAL层删除
但是如果是普通的删除方法,比如,删除类别,然后根据类别id去查找大师或者企业,然后根据大师或者企业类别去查找产品等等,这势必会导致指数级的sql语句,这时候我们需要做sql优化,优化的地方在哪里呢?
我们根据类别查找出大师的企业,这时候,对应多个企业和大师,但是,都有很多产品,我们再根据产品去删除产品其他信息,尽量保证在O(n)这个级别的sql,尽量不要出现sql在循环中,那样势必造成无数个连接.
然后删除就是一个纯体力活,不断的写删除,不过主要是思维要清晰,要保证事务,是非常重要的.
crafttype删除:
1: #region 删除
2: #region 获得属于该类别的大师的删除语句
3: /// <summary>
4: /// 获得属于该类别的大师的删除语句
5: /// </summary>
6: /// <param name="Id"></param>
7: /// <returns></returns>
8: internal string GetDeleteMastersSql(string Id)
9: {
10: string sql = "select Masterid from master_type where Typeid=" + Id;
11: DataTable dt = SqlHelper.ExecuteDataTable(sql);
12: StringBuilder sb = new StringBuilder();
13: masterDAL dal = new masterDAL();
14: foreach (DataRow dr in dt.Rows)
15: {
16: sb.Append(dal.GetMasterDeleteSql(Convert.ToInt32(dr["Masterid"])));
17: }
18: return sb.ToString();
19: }
20: #endregion
21: #region 获得属于该类别的企业的删除语句
22: /// <summary>
23: /// 获得属于该类别的企业的删除语句
24: /// </summary>
25: /// <param name="Id"></param>
26: /// <returns></returns>
27: internal string GetDeleteCompanySql(string Id)
28: {
29: string sql = "select Companyid from company_type where Typeid=" + Id;
30: DataTable dt = SqlHelper.ExecuteDataTable(sql);
31: StringBuilder sb = new StringBuilder();
32: companyDAL dal = new companyDAL();
33: foreach (DataRow dr in dt.Rows)
34: {
35: sb.Append(dal.GetCompanyDeleteSql(Convert.ToInt32(dr["Companyid"])));
36: }
37: return sb.ToString();
38: }
39: #endregion
40: #region 获得属于多个类别的大师的删除语句
41: /// <summary>
42: /// 获得属于多个类别的大师的删除语句
43: /// </summary>
44: /// <param name="Id"></param>
45: /// <returns></returns>
46: internal string GetDeleteMoreMastersSql(string Ids)
47: {
48: string sql = "select Masterid from master_type where Typeid in (" + Ids + ")";
49: DataTable dt = SqlHelper.ExecuteDataTable(sql);
50: StringBuilder sb = new StringBuilder();
51: masterDAL dal = new masterDAL();
52: foreach (DataRow dr in dt.Rows)
53: {
54: sb.Append(dal.GetMasterDeleteSql(Convert.ToInt32(dr["Masterid"])));
55: }
56: return sb.ToString();
57: }
58: #endregion
59: #region 获得属于多个类别的企业的删除语句
60: /// <summary>
61: /// 获得属于多个类别的企业的删除语句
62: /// </summary>
63: /// <param name="Id"></param>
64: /// <returns></returns>
65: internal string GetDeleteMoreCompanySql(string Ids)
66: {
67: string sql = "select Companyid from company_type where Typeid in (" + Ids + ")";
68: DataTable dt = SqlHelper.ExecuteDataTable(sql);
69: StringBuilder sb = new StringBuilder();
70: companyDAL dal = new companyDAL();
71: foreach (DataRow dr in dt.Rows)
72: {
73: sb.Append(dal.GetCompanyDeleteSql(Convert.ToInt32(dr["Companyid"])));
74: }
75: return sb.ToString();
76: }
77: #endregion
78: #region 删除crafttype
79: /// <summary>
80: /// 删除crafttype
81: /// </summary>
82: /// <param name="id">id</param>
83: /// <returns>执行状态</returns>
84: public bool Delete(int id)
85: {
86: StringBuilder sb = new StringBuilder();
87: // 获取删除企业的sql语句
88: sb.Append(GetDeleteCompanySql(id.ToString ()));
89: //获取删除大师的sql语句
90: sb.Append(GetDeleteMastersSql(id.ToString ()));
91: sb.Append("delete from craftknowledge where Typeid=" + id);
92: sb.Append("delete from crafttype where id=" + id);
93: bool status = false;
94: SqlHelper.Open();
95: SqlHelper.BeginTrans();
96: if (SqlHelper.ExecuteNonQuery(sb.ToString()))
97: {
98: status = true;
99: SqlHelper.CommitTrans();
100: }
101: else
102: {
103: status = false;
104: SqlHelper.RollbackTrans();
105: }
106:
107: return status;
108: }
109: #endregion
110: #region 删除crafttype(多选)
111: /// <summary>
112: /// 删除crafttype(多选)
113: /// </summary>
114: /// <param name="strID">strID,记得多个用,隔开</param>
115: /// <returns>执行状态</returns>
116: public bool DeleteMoreID(string strID)
117: {
118: bool status = false;
119: string[] IDs = strID.Split(',');
120: StringBuilder sb = new StringBuilder();
121: sb.Append(GetDeleteMoreMastersSql(strID));
122: sb.Append(GetDeleteMoreCompanySql(strID));
123: for (int i = 0; i < IDs.Length; i++)
124: {
125: sb.Append(";delete from craftknowledge where Typeid=" + IDs[i]);
126: }
127: sb.Append(";delete from crafttype where id in (" + strID + ")");
128: SqlHelper.Open();
129: SqlHelper.BeginTrans();
130: if (SqlHelper.ExecuteNonQuery(sb.ToString()))
131: {
132: status = true;
133: SqlHelper.CommitTrans();
134: }
135: else
136: {
137: status = false;
138: SqlHelper.RollbackTrans();
139: }
140:
141: return status;
142:
143: }
144: #endregion
145: #endregion
146: }
crafttype调用的master和company中构造删除的sql,master和company又迭代的构造删除product的sql,
这样就能节省很多工作,性能也有了较大的提高.
构造company删除:
1: #region 删除
2: /// <summary>
3: /// 删除company的sql语句生成
4: /// </summary>
5: /// <param name="id"></param>
6: /// <returns></returns>
7: internal string GetCompanyDeleteSql(int id)
8: {
9: string sql = "select Id from product where Companyid=" + id;
10: DataTable dt = SqlHelper.ExecuteDataTable(sql);
11: StringBuilder sb = new StringBuilder();
12: productDAL dal = new productDAL();
13: foreach (DataRow dr in dt.Rows)
14: {
15: sb.Append(dal.GetDeleteProductSqlString(dr["Id"].ToString()));
16: }
17: sb.Append(";delete from company_pic where Companyid=" + id);
18: sb.Append(";delete from company_cert where Companyid=" + id);
19: sb.Append(";delete from company_type where Companyid=" + id);
20: sb.Append(";delete from company where Id="+id+";");
21: return sb.ToString();
22: }
构造大师删除sql:
1:
2: /// <summary>
3: /// 删除master的sql语句生成
4: /// </summary>
5: /// <param name="id"></param>
6: /// <returns></returns>
7: internal string GetMasterDeleteSql(int id)
8: {
9: string sql = "select Id from product where Belongstype='0' and Masterid=" + id;
10: DataTable dt = SqlHelper.ExecuteDataTable(sql);
11: StringBuilder sb = new StringBuilder();
12: productDAL dal = new productDAL();
13: foreach (DataRow dr in dt.Rows)
14: {
15: //获取删除产品所有信息的SQL(构造出sql)
16: sb.Append(dal.GetDeleteProductSqlString(dr["Id"].ToString()));
17: }
18: sb.Append(";delete from master_cert where Masterid=" + id);
19: sb.Append(";delete from master_type where Masterid=" + id);
20: sb.Append(";delete from master where Id=" + id + ";");
21: return sb.ToString();
22: }
构造产品删除:
1: #region 获取删除产品的前置sql语句
2: /// <summary>
3: /// 获取删除产品的前置sql语句
4: /// </summary>
5: /// <param name="ProductId"></param>
6: /// <returns></returns>
7: internal string GetDeleteProductSqlString(string ProductId)
8: {
9: 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);
10: return sql;
11: }
12: #endregion
这就是关于类别的删除,太复杂了,乱七八糟,需要思路清晰.
不过总体都是DAL层的删除工作,接下来就是图片删除:
图片删除,首先要构造一个删除类,专门负责删除图片(大中小),一个负责获取图片路径
1: using System;
2: using System.Collections.Generic;
3: using System.Linq;
4: using System.Text;
5: using System.Configuration;
6: namespace Common
7: {
8: /// <summary>
9: /// 潮州工艺品图片删除
10: /// </summary>
11: public class CzcraftDeletePic
12: {
13: public enum DeletePicType
14: {
15: /// <summary>
16: /// 大师图片
17: /// </summary>
18: GetMasterPic=0,
19: /// <summary>
20: /// 大师荣誉图片
21: /// </summary>
22: GetMasterCert=1,
23: /// <summary>
24: /// 企业荣誉图片
25: /// </summary>
26: GetCompanyCert=2,
27: /// <summary>
28: /// 企业主图片
29: /// </summary>
30: GetLogoCompanyPic=3,
31: /// <summary>
32: /// 企业美景图
33: /// </summary>
34: GetCompanyPic=4,
35: /// <summary>
36: /// 产品主图片
37: /// </summary>
38: GetMainProductPic=5,
39: /// <summary>
40: /// 产品副图片
41: /// </summary>
42: GetOtherProductPic=6
43: }
44: /// <summary>
45: /// 潮州工艺品图片删除
46: /// </summary>
47: /// <param name="FileType">文件类别(GetMasterPic,GetMasterCert,GetCompanyCert,GetLogoCompanyPic,GetMainProductPic,GetOtherProductPic)</param>
48: /// <param name="FileName">文件名</param>
49: public static void FileDelete(DeletePicType FileType, string FileName)
50: {
51: string VirtualPath= GetPicPath(FileType, FileName);
52: //删除文件
53: FileOperate.FileDelete(VirtualPath, true);
54: }
55: /// <summary>
56: /// 获取文件路径(根据文件类别)
57: /// </summary>
58: /// <param name="FileType">文件类别</param>
59: /// <param name="FileName">文件名</param>
60: /// <returns></returns>
61: public static string GetPicPath(DeletePicType FileType, string FileName)
62: {
63: string PicServerPath = ConfigurationManager.AppSettings["PicServerPath"];
64: string VirtualFilePath = PicServerPath;
65: switch (FileType)
66: {
67: case DeletePicType.GetMasterPic:
68: VirtualFilePath += "Master/MainMasterPic/";
69: break;
70: case DeletePicType.GetMasterCert:
71: VirtualFilePath += "Master/MasterCert/";
72: break;
73: case DeletePicType.GetCompanyCert:
74: VirtualFilePath += "Company/CompanyCert/";
75: break;
76: case DeletePicType.GetCompanyPic:
77: VirtualFilePath += "Company/MainCompanyPic/";
78: break;
79: case DeletePicType.GetLogoCompanyPic:
80: VirtualFilePath += "Company/CompanyPic/";
81: break;
82: case DeletePicType.GetMainProductPic:
83: VirtualFilePath += "Product/MainProductPic/";
84: break;
85: case DeletePicType.GetOtherProductPic:
86: VirtualFilePath += "Product/OtherProductPic/"; ;
87: break;
88: default:
89: throw new Exception("文件类别输入有误!");
90:
91:
92:
93: }
94: VirtualFilePath += FileName;
95: return VirtualFilePath;
96: }
97: }
98: }
图片的路径写在webconfig中,接下来就是业务逻辑方面的删除,
我们要保证先从数据库中查出图片,然后删除数据库,最后删除图片.
这个业务逻辑层就复杂得多了.
同样是类别删除:
1: #region 删除
2: #region 删除crafttype
3: /// <summary>
4: /// 删除crafttype
5: /// </summary>
6: /// <param name="id">id</param>
7: /// <returns>执行状态</returns>
8: public bool Delete(int id)
9: {
10: #region 大师图片信息保存
11: //获取大师主Id信息
12: DataTable dtMasterIds = new masterDAL().GetAllMasterByCraftType(id.ToString());
13: StringBuilder MasterIds = new StringBuilder();
14: foreach (DataRow dr in dtMasterIds.Rows)
15: {
16: MasterIds.Append(dr["Masterid"].ToString());
17: }
18: MasterIds.Remove(MasterIds.Length - 1, 1);
19: //大师多个id
20: string strMasterIds = MasterIds.ToString();
21: //获取大师主图片
22: DataTable dtMasterPath = new masterDAL().GetMasterMainPic(strMasterIds);
23: //获取大师荣誉图片
24: DataTable dtMasterCert = new master_certDAL().GetMoreMasterCertPath(strMasterIds);
25: //获取大师产品图片
26: DataTable dtMasterProducts = new productDAL().GetProductsPicByMoreMasterIds(strMasterIds);
27: //获取大师其他产品图片
28: StringBuilder ProductIds = new StringBuilder();
29: foreach (DataRow dr in dtMasterProducts.Rows)
30: {
31: ProductIds.Append(dr["Id"].ToString());
32: }
33: ProductIds.Remove(ProductIds.Length - 1, 1);
34: DataTable dtProductOtherProducts = new product_picturepathDAL().GetMoreOtherProductPicPath(ProductIds.ToString());
35: #endregion
36: #region 企业图片信息保存
37: //获取企业主Id信息
38: DataTable dtCompanyIds = new companyDAL().GetAllCompanyByCraftType(id.ToString());
39: StringBuilder CompanyIds = new StringBuilder();
40: foreach (DataRow dr in dtCompanyIds.Rows)
41: {
42: CompanyIds.Append(dr["Masterid"].ToString());
43: }
44: CompanyIds.Remove(CompanyIds.Length - 1, 1);
45: //企业多个id
46: string strCompanyIds = CompanyIds.ToString();
47: //获取企业主图片
48: DataTable dtCompanyPath = new companyDAL().GetCompanyMainPic(strCompanyIds);
49: //获取企业美景图
50: DataTable dtCompanyPic = new company_picDAL().GetCompanyPicByMoreCompanyIds(strCompanyIds);
51: //获取企业荣誉图片
52: DataTable dtCompanyCert = new company_certDAL().GetMoreCompanyCertPath(strCompanyIds);
53: //获取企业产品图片
54: DataTable dtCompanyProducts = new productDAL().GetProductsPicByMoreCompanyIds(strCompanyIds);
55: //获取企业其他产品图片
56: StringBuilder CompanyProductIds = new StringBuilder();
57: foreach (DataRow dr in dtCompanyProducts.Rows)
58: {
59: CompanyProductIds.Append(dr["Id"].ToString());
60: }
61: CompanyProductIds.Remove(CompanyProductIds.Length - 1, 1);
62: DataTable dtCompanyOtherProducts = new product_picturepathDAL().GetMoreOtherProductPicPath(ProductIds.ToString());
63: #endregion
64: //删除
65: crafttypeDAL dal = new crafttypeDAL();
66: bool Status = dal.Delete(id);
67: //如果执行成功,则删除图片
68: masterBLL bll = new masterBLL();
69: companyBLL companyBll = new companyBLL();
70: if (Status)
71: {
72: #region 大师图片删除
73: //删除大师主图片
74: bll.DeleteMainMasterPath(dtMasterPath);
75:
76: //删除大师荣誉证书
77:
78: bll.DeleteMasterCertPic(dtMasterCert);
79: //删除所有产品图片
80:
81: bll.DeleteProdoctsByMasterId(dtMasterProducts);
82: //删除产品其他图片
83: new product_picturepathBLL().DeleteMoreOtherProductPic(dtProductOtherProducts);
84: #endregion
85: #region 企业图片删除
86: //删除企业主图片
87: companyBll.DeleteMainCompanyPath(dtCompanyPath);
88: //删除企业美景图
89: companyBll.DeleteCompanyPic(dtCompanyPic);
90:
91: //删除多个企业荣誉图片
92:
93: companyBll.DeleteCompanyCertPic(dtCompanyCert);
94: //删除所有产品图片
95:
96: companyBll.DeleteProdoctsByCompanyId(dtCompanyProducts);
97: //删除产品其他图片
98: new product_picturepathBLL().DeleteMoreOtherProductPic(dtCompanyOtherProducts);
99: #endregion
100:
101: }
102: return Status;
103:
104: }
105: #endregion
106: #region 删除crafttype
107: /// <summary>
108: /// 删除crafttype
109: /// </summary>
110: /// <param name="strID">strID,记得多个用,隔开</param>
111: /// <returns>执行状态</returns>
112: public bool DeleteMoreID(string strID)
113: {
114: #region 大师图片信息保存
115: //获取大师主Id信息
116: DataTable dtMasterIds = new masterDAL().GetAllMasterByCraftType(strID);
117: StringBuilder MasterIds = new StringBuilder();
118: foreach (DataRow dr in dtMasterIds.Rows)
119: {
120: MasterIds.Append(dr["Masterid"].ToString());
121: }
122: MasterIds.Remove(MasterIds.Length - 1, 1);
123: //大师多个id
124: string strMasterIds = MasterIds.ToString();
125: //获取大师主图片
126: DataTable dtMasterPath = new masterDAL().GetMasterMainPic(strMasterIds);
127: //获取大师荣誉图片
128: DataTable dtMasterCert = new master_certDAL().GetMoreMasterCertPath(strMasterIds);
129: //获取大师产品图片
130: DataTable dtMasterProducts = new productDAL().GetProductsPicByMoreMasterIds(strMasterIds);
131: //获取大师其他产品图片
132: StringBuilder ProductIds = new StringBuilder();
133: foreach (DataRow dr in dtMasterProducts.Rows)
134: {
135: ProductIds.Append(dr["Id"].ToString());
136: }
137: ProductIds.Remove(ProductIds.Length - 1, 1);
138: DataTable dtProductOtherProducts = new product_picturepathDAL().GetMoreOtherProductPicPath(ProductIds.ToString());
139: #endregion
140: #region 企业图片信息保存
141: //获取企业主Id信息
142: DataTable dtCompanyIds = new companyDAL().GetAllCompanyByCraftType(strID);
143: StringBuilder CompanyIds = new StringBuilder();
144: foreach (DataRow dr in dtCompanyIds.Rows)
145: {
146: CompanyIds.Append(dr["Masterid"].ToString());
147: }
148: CompanyIds.Remove(CompanyIds.Length - 1, 1);
149: //企业多个id
150: string strCompanyIds = CompanyIds.ToString();
151: //获取企业主图片
152: DataTable dtCompanyPath = new companyDAL().GetCompanyMainPic(strCompanyIds);
153: //获取企业美景图
154: DataTable dtCompanyPic = new company_picDAL().GetCompanyPicByMoreCompanyIds(strCompanyIds);
155: //获取企业荣誉图片
156: DataTable dtCompanyCert = new company_certDAL().GetMoreCompanyCertPath(strCompanyIds);
157: //获取企业产品图片
158: DataTable dtCompanyProducts = new productDAL().GetProductsPicByMoreCompanyIds(strCompanyIds);
159: //获取企业其他产品图片
160: StringBuilder CompanyProductIds = new StringBuilder();
161: foreach (DataRow dr in dtCompanyProducts.Rows)
162: {
163: CompanyProductIds.Append(dr["Id"].ToString());
164: }
165: CompanyProductIds.Remove(CompanyProductIds.Length - 1, 1);
166: DataTable dtCompanyOtherProducts = new product_picturepathDAL().GetMoreOtherProductPicPath(ProductIds.ToString());
167: #endregion
168: //删除
169: crafttypeDAL dal = new crafttypeDAL();
170: bool Status = dal.DeleteMoreID(strID);
171: //如果执行成功,则删除图片
172: masterBLL bll = new masterBLL();
173: companyBLL companyBll = new companyBLL();
174: if (Status)
175: {
176: #region 大师图片删除
177: //删除大师主图片
178: bll.DeleteMainMasterPath(dtMasterPath);
179:
180: //删除大师荣誉证书
181:
182: bll.DeleteMasterCertPic(dtMasterCert);
183: //删除所有产品图片
184:
185: bll.DeleteProdoctsByMasterId(dtMasterProducts);
186: //删除产品其他图片
187: new product_picturepathBLL().DeleteMoreOtherProductPic(dtProductOtherProducts);
188: #endregion
189: #region 企业图片删除
190: //删除企业主图片
191: companyBll.DeleteMainCompanyPath(dtCompanyPath);
192: //删除企业美景图
193: companyBll.DeleteCompanyPic(dtCompanyPic);
194:
195: //删除多个企业荣誉图片
196:
197: companyBll.DeleteCompanyCertPic(dtCompanyCert);
198: //删除所有产品图片
199:
200: companyBll.DeleteProdoctsByCompanyId(dtCompanyProducts);
201: //删除产品其他图片
202: new product_picturepathBLL().DeleteMoreOtherProductPic(dtCompanyOtherProducts);
203: #endregion
204:
205: }
206: return Status;
207: }
208: #endregion
209: #endregion
210: }
然后具体细节的删除就更多了.这里就不一一概述了....可以发现其实一个删除都挺复杂了......不过由于三层的因素简化了太多太多了,以前的一个类别删除,我也不知道要删除多少东西..........
总之,记住一句话,DAL负责数据库交互,BLL负责业务逻辑,这样才能让项目思路更清晰,更容易维护!
当然,我感觉现在这个逻辑也还是乱乱的,不过已经清晰太多了,至少,把DAL个部分职责分的非常清晰!