当前文件路径：ClothesShop/OleDbDAL/product.cs 
1
using System;
2using System.Collections.Generic;
3using System.Text;
4using System.Data;
5using System.Data.OleDb;
6using ClothesShop.DBUtility;
7//该源码下载(５１ａｓpｘ．ｃｏｍ)
8
9namespace ClothesShop.OleDbDAL
10
...{
11
public class product : ClothesShop.IDAL.Iproduct
12
...{
13 /**//// <summary>
14 /// 创建一个商品
15 /// </summary>
16 /// <param name="productname">商品名称</param>
17 /// <param name="typeid_1">商品一级分类</param>
18 /// <param name="typeid_2">商品二级分类</param>
19 /// <param name="recommended">是否推荐</param>
20 /// <param name="specials">是否特价</param>
21 /// <param name="price">商品原价</param>
22 /// <param name="userprice">会员价格</param>
23 /// <param name="specialsprice">推荐价格</param>
24 /// <param name="pointcount">点击数量</param>
25 /// <param name="imagepath">图片路径</param>
26 /// <param name="count">商品库存数量</param>
27 /// <param name="count">销售数量</param>
28 /// <param name="description">商品描述</param>
29
/// <returns></returns>
30 public int createproduct(string productname, int typeid_1, int typeid_2, bool recommended, bool specials, double price,
31 double userprice, double specialsprice, int pointcount, string imagepath, int count, int sellcount, string description)
32 ...{
33 StringBuilder sb = new StringBuilder();
34 sb.Append("insert into products (productname,typeid_1,typeid_2,recommended,specials,price,");
35 sb.Append("userprice,specialsprice,pointcount,imagepath,[count],[sellcount],[description]) values (");
36 sb.Append("@productname,@typeid_1,@typeid_2,@recommended,@specials,@price,@userprice,");
37 sb.Append("@specialsprice,@pointcount,@imagepath,@count,@sellcount,@description)");
38 OleDbParameter[] param =
39 ...{
40 OleDbHelper.GetParameter("@productname",OleDbType.Char,50,"productname",productname),
41 OleDbHelper.GetParameter("@typeid_1",OleDbType.Integer,4,"typeid_1",typeid_1),
42 OleDbHelper.GetParameter("@typeid_2",OleDbType.Integer,4,"typeid_2",typeid_2),
43 OleDbHelper.GetParameter("@recommended",OleDbType.Boolean,recommended),
44 OleDbHelper.GetParameter("@specials",OleDbType.Boolean,specials),
45 OleDbHelper.GetParameter("@price",OleDbType.Double,8,"price",price),
46 OleDbHelper.GetParameter("@userprice",OleDbType.Double,8,"userprice",userprice),
47 OleDbHelper.GetParameter("@specialsprice",OleDbType.Double,8,"specialsprice",specialsprice),
48 OleDbHelper.GetParameter("@pointcount",OleDbType.Integer,4,"pointcount",pointcount),
49 OleDbHelper.GetParameter("@imagepath",OleDbType.Char,100,"imagepath",imagepath),
50 OleDbHelper.GetParameter("@count",OleDbType.Integer,4,"[count]",count),
51 OleDbHelper.GetParameter("@sellcount",OleDbType.Integer,4,"[sellcount]",sellcount),
52 OleDbHelper.GetParameter("@description",OleDbType.Char,description)
53 };
54 return OleDbHelper.ExecuteSql(sb.ToString(), param);
55 }
56
57 /**//// <summary>
58 /// 删除一个商品
59 /// </summary>
60 /// <param name="productid"></param>
61 /// <returns></returns>
62 public int deleteproduct(int productid)
63 ...{
64 StringBuilder sb = new StringBuilder();
65 sb.Append("delete from products where productid=@productid");
66 OleDbParameter[] param = ...{ OleDbHelper.GetParameter("@productid", OleDbType.Integer, 4, "productid", productid) };
67 return OleDbHelper.ExecuteSql(sb.ToString(), param);
68 }
69
70 /**//// <summary>
71 /// 更新一个商品
72 /// </summary>
73 /// <param name="productid">商品编号</param>
74 /// <param name="productname">商品名称</param>
75 /// <param name="typeid_1">商品一级分类</param>
76 /// <param name="typeid_2">商品二级分类</param>
77 /// <param name="recommended">是否推荐</param>
78 /// <param name="specials">是否特价</param>
79 /// <param name="price">商品原价</param>
80 /// <param name="userprice">会员价格</param>
81 /// <param name="specialsprice">推荐价格</param>
82 /// <param name="pointcount">点击数量</param>
83 /// <param name="imagepath">图片路径</param>
84 /// <param name="count">商品库存数量</param>
85 /// <param name="count">销售数量</param>
86 /// <param name="description">商品描述</param>
87 /// <returns></returns>
88 public int updateproduct(int productid, string productname, int typeid_1, int typeid_2, bool recommended, bool specials, Double price,
89 Double userprice, Double specialsprice, int pointcount, string imagepath, int count, int sellcount, string description)
90 ...{
91 StringBuilder sb = new StringBuilder();
92 sb.Append("update products set productname=@productname,typeid_1=@typeid_1,typeid_2=@typeid_2,recommended=@recommended,");
93 sb.Append("specials=@specials,price=@price,userprice=@userprice,specialsprice=@specialsprice,pointcount=@pointcount,");
94 sb.Append("imagepath=@imagepath,[count]=@count,sellcount=@sellcount,description=@description where productid=@productid");
95 OleDbParameter[] param =
96 ...{
97 OleDbHelper.GetParameter("@productname",OleDbType.Char,50,"productname",productname),
98 OleDbHelper.GetParameter("@typeid_1",OleDbType.Integer,4,"typeid_1",typeid_1),
99 OleDbHelper.GetParameter("@typeid_2",OleDbType.Integer,4,"typeid_2",typeid_2),
100 OleDbHelper.GetParameter("@recommended",OleDbType.Boolean,1,"recommended",recommended),
101 OleDbHelper.GetParameter("@specials",OleDbType.Boolean,1,"specials",specials),
102 OleDbHelper.GetParameter("@price",OleDbType.Double,8,"price",price),
103 OleDbHelper.GetParameter("@userprice",OleDbType.Double,8,"userprice",userprice),
104 OleDbHelper.GetParameter("@specialsprice",OleDbType.Double,8,"specialsprice",specialsprice),
105 OleDbHelper.GetParameter("@pointcount",OleDbType.Integer,4,"pointcount",pointcount),
106 OleDbHelper.GetParameter("@imagepath",OleDbType.Char,100,"imagepath",imagepath),
107 OleDbHelper.GetParameter("@count",OleDbType.Integer,4,"[count]",count),
108 OleDbHelper.GetParameter("@sellcount",OleDbType.Integer,4,"sellcount",sellcount),
109 OleDbHelper.GetParameter("@description",OleDbType.Char,description),
110 OleDbHelper.GetParameter("@productid", OleDbType.Integer, 4, "productid", productid)
111 };
112 return OleDbHelper.ExecuteSql(sb.ToString(), param);
113 }
114
115 /**//// <summary>
116 /// 根据参数查询商品
117 /// </summary>
118 /// <param name="typeid_1"></param>
119 /// <param name="typeid_2"></param>
120 /// <param name="productname"></param>
121 /// <returns></returns>
122 public DataTable productlist(int typeid_1, int typeid_2, string productname)
123 ...{
124 StringBuilder sb = new StringBuilder();
125 List<OleDbParameter> list = new List<OleDbParameter>();
126 sb.Append("select productid,productname,typeid_1,typeid_2,recommended,specials,price,");
127 sb.Append("userprice,specialsprice,pointcount,imagepath,[count],sellcount,description from products where 1=@index");
128 list.Add(OleDbHelper.GetParameter("@index",OleDbType.Integer,1));
129 if (typeid_1 != 0)
130 ...{
131 sb.Append(" and typeid_1=@typeid_1 ");
132 list.Add(OleDbHelper.GetParameter("@typeid_1", OleDbType.Integer, 4, "typeid_1", typeid_1));
133 }
134 if (typeid_2 != 0)
135 ...{
136 sb.Append(" and typeid_2=@typeid_2");
137 list.Add(OleDbHelper.GetParameter("@typeid_2", OleDbType.Integer, 4, "typeid_2", typeid_2));
138 }
139 if (productname != "")
140 ...{
141 sb.Append(" and productname like '%' + @productname + '%'");
142 list.Add(OleDbHelper.GetParameter("@productname", OleDbType.Char, 50, "productname", productname));
143 }
144
145 return OleDbHelper.ExecuteDt(sb.ToString(), list.ToArray());
146 }
147
148 public DataTable selectproduct(int typeid_1, int typeid_2, string productname, int recommended, int specials)
149 ...{
150 StringBuilder sb = new StringBuilder();
151 List<OleDbParameter> list = new List<OleDbParameter>();
152 sb.Append("SELECT products.*, type_2.typename AS typename_2, type_1.typename AS typename_1 ");
153 sb.Append("FROM products, type_2, type_1 ");
154 sb.Append("WHERE products.typeid_2=type_2.typeid_2 and type_2.typeid_1=type_1.typeid_1");
155 if (typeid_1 != 0)
156 ...{
157 sb.Append(" and type_2.typeid_1=@typeid_1 ");
158 list.Add(OleDbHelper.GetParameter("@typeid_1", OleDbType.Integer, 4, "type_2.typeid_1", typeid_1));
159 }
160 if (typeid_2 != 0)
161 ...{
162 sb.Append(" and type_2.typeid_2=@typeid_2");
163 list.Add(OleDbHelper.GetParameter("@typeid_2", OleDbType.Integer, 4, "type_2.typeid_2", typeid_2));
164 }
165 if (productname != "")
166 ...{
167 sb.Append(" and products.productname like '%' + @productname + '%'");
168 list.Add(OleDbHelper.GetParameter("@productname", OleDbType.Char, 50, "products.productname", productname));
169 }
170 if (recommended == 1)
171 ...{
172 sb.Append(" and products.recommended=@recommended");
173 list.Add(OleDbHelper.GetParameter("@recommended", OleDbType.Boolean, 1, "products.recommended", true));
174 }
175 if (recommended == 0)
176 ...{
177 sb.Append(" and products.recommended=@recommended");
178 list.Add(OleDbHelper.GetParameter("@recommended", OleDbType.Boolean, 1, "products.recommended", false));
179 }
180 if (specials == 1)
181 ...{
182 sb.Append(" and products.specials=@specials");
183 list.Add(OleDbHelper.GetParameter("@specials", OleDbType.Boolean, 1, "products.specials", true));
184 }
185 if (specials == 0)
186 ...{
187 sb.Append(" and products.specials=@specials");
188 list.Add(OleDbHelper.GetParameter("@specials", OleDbType.Boolean, 1, "products.specials", false));
189 }
190
191 return OleDbHelper.ExecuteDt(sb.ToString(), list.ToArray());
192 }
193
194 /**//// <summary>
195 ///获得一条商品纪录
196 /// </summary>
197 /// <param name="productid"></param>
198 /// <returns></returns>
199 public DataRow getoneproduct(int productid)
200 ...{
201 StringBuilder sb = new StringBuilder();
202 sb.Append("select productid,productname,typeid_1,typeid_2,recommended,specials,price,");
203 sb.Append("userprice,specialsprice,pointcount,imagepath,[count],sellcount,description from products ");
204 sb.Append("where productid=@productid");
205 OleDbParameter[] param = ...{ OleDbHelper.GetParameter("@productid", OleDbType.Integer, 4, "productid", productid) };
206 DataTable table = OleDbHelper.ExecuteDt(sb.ToString(), param);
207 return table.Rows[0];
208 }
209
210 /**//// <summary>
211 /// 获得若干数量的新商品
212 /// </summary>
213 /// <returns></returns>
214 public DataTable getnewproductlist(int num)
215 ...{
216 StringBuilder sb = new StringBuilder();
217 sb.Append("select top " + num + " productid,productname,typeid_1,typeid_2,recommended,specials,price,");
218 sb.Append("userprice,specialsprice,pointcount,imagepath,[count],sellcount,description from products order by productid desc");
219 return OleDbHelper.ExecuteDt(sb.ToString());
220 }
221
222 /**//// <summary>
223 /// 获得若干数量的特价商品
224 /// </summary>
225 /// <param name="num"></param>
226 /// <returns></returns>
227 public DataTable getspecialproductlist(int num)
228 ...{
229 StringBuilder sb = new StringBuilder();
230 sb.Append("select top " + num + " productid,productname,typeid_1,typeid_2,recommended,specials,price,");
231 sb.Append("userprice,specialsprice,pointcount,imagepath,[count],sellcount,description from products where specials=true order by productid desc");
232 return OleDbHelper.ExecuteDt(sb.ToString());
233 }
234
235 /**//// <summary>
236 /// 获得若干数量的推荐商品
237 /// </summary>
238 /// <param name="num"></param>
239 /// <returns></returns>
240 public DataTable getrecommendedproductlist(int num)
241 ...{
242 StringBuilder sb = new StringBuilder();
243 sb.Append("select top " + num + " productid,productname,typeid_1,typeid_2,recommended,specials,price,");
244 sb.Append("userprice,specialsprice,pointcount,imagepath,[count],sellcount,description from products where recommended=true order by productid desc");
245 return OleDbHelper.ExecuteDt(sb.ToString());
246 }
247
248
249
