1using System;
  2using System.Collections.Generic;
  3
  4using System.Data;
  5using System.Data.OleDb;
  6using MyShop.IDAL;
  7using MyShop.Model;
  8
  9namespace MyShop.AccessDAL
 10...{
 11    public class Category:ICategory
 12    ...{
 13        private ConfigInfo configInfo = new ConfigInfo();
 14        private string tableName = "Ljh_ProductCategory";
 15
 16        public Category()
 17        ...{
 18            if (!string.IsNullOrEmpty(configInfo.TablePrefix.Trim()))
 19                tableName = configInfo.TablePrefix + "ProductCategory";
 20        }
 21        ICategory member#region ICategory member
 22
 23        /**//// <summary>
 24        /// 
 25        /// </summary>
 26        /// <param name="model"></param>
 27        /// <returns>返回ID, 如果发生错误则返回-1</returns>
 28        public int Add(CategoryInfo model)
 29        ...{
 30            if (model == null)
 31            ...{
 32                return 0;
 33            }
 34            string commandText = "insert into " + this.tableName + "(categoryName,productKindId,description,IsDisabled,OrderId) values(@categoryName,@productKindId,@description,@IsDisabled,@OrderId) ; SELECT @@IDENTITY  ";
 35            OleDbParameter[] commandParameters = ...{
 36                Database.MakeInParam("@CategoryName", OleDbType.VarWChar,50,model.CategoryName),
 37                Database.MakeInParam("@ProductKindId", OleDbType.Integer,4,model.ProductKindId),
 38                Database.MakeInParam("@Description", OleDbType.VarWChar,255,model.Description),
 39                Database.MakeInParam("@IsDisabled", OleDbType.UnsignedTinyInt,1,model.IsDisabled),
 40                Database.MakeInParam("@OrderId", OleDbType.Integer,4,model.OrderId)
 41            };
 42            int intIdentity = -1;
 43            try
 44            ...{
 45                Database.ExecuteNonQuery(CommandType.Text, out intIdentity, commandText, commandParameters);
 46            }
 47            catch (Exception exception)
 48            ...{
 49                throw exception;
 50            }
 51            return intIdentity;
 52        }
 53
 54 
 55
 56 
 57
 58        /**//// <summary>
 59        /// 删除购物车项目 
 60        /// </summary>
 61        /// <param name="filter">where后面的条件语句，不加where</param>
 62        /// <returns>返回影响行数</returns>
 63        public int Delete(string filter)
 64        ...{
 65            int count = -1;
 66            string sql = @"delete from " + tableName;
 67            if (!string.IsNullOrEmpty(filter.Trim()))
 68            ...{
 69                sql = sql + "  where  " + filter;
 70            }
 71            try
 72            ...{
 73                
 74                count = Database.ExecuteNonQuery(sql);
 75            }
 76            catch (Exception ex)
 77            ...{
 78                throw ex;
 79            }
 80            return count;
 81        }
 82
 83        /**//// <summary>
 84        /// 判断购物车项目是否存在
 85        /// </summary>
 86        /// <param name="filter"></param>
 87        /// <returns></returns>
 88        public bool Exist(string filter)
 89        ...{
 90            bool result = false;
 91            string sql = @"select * from " + tableName;
 92            if (!string.IsNullOrEmpty(filter.Trim()))
 93            ...{
 94                sql = sql + "  where  " + filter;
 95            }
 96            try
 97            ...{
 98                
 99
100                DataSet dataset = new DataSet();
101                dataset = Database.ExecuteDataSet(sql);
102                if (dataset.Tables[0].Rows.Count > 0)
103                ...{
104                    result = true;
105                }
106            }
107            catch (Exception ex)
108            ...{
109                throw ex;
110            }
111            return result;
112        }
113
114        /**//// <summary>
115        /// 返回所有购物车项目
116        /// </summary>
117        /// <returns>返回所有购物车项目</returns>
118        public DataSet GetDataSet()
119        ...{
120            string sql = "select * from " + tableName;
121            DataSet dataset = new DataSet();
122            try
123            ...{
124                
125                dataset = Database.ExecuteDataSet(sql);
126
127            }
128            catch (Exception ex)
129            ...{
130                throw ex;
131            }
132            return dataset;
133        }
134
135
136        /**//// <summary>
137        /// 
138        /// </summary>
139        /// <param name="filter"></param>
140        /// <returns></returns>
141        public DataSet GetDataSet(string filter)
142        ...{
143            if (string.IsNullOrEmpty(filter))
144                return null;
145
146            string sql = "select * from " + tableName + " where " + filter;
147            DataSet dataset = new DataSet();
148            try
149            ...{
150                
151                dataset = Database.ExecuteDataSet(sql);
152            }
153            catch (Exception ex)
154            ...{
155                throw ex;
156            }
157            return dataset;
158        }
159
160
161        /**//// <summary>
162        /// 
163        /// </summary>
164        /// <param name="dr"></param>
165        /// <returns></returns>
166        public CategoryInfo GetModel(DataRow dr)
167        ...{
168            if (dr == null)
169                return null;
170            CategoryInfo model = new CategoryInfo();
171
172            if (dr["categoryId"].ToString() != "")
173                model.CategoryId = Convert.ToInt32(dr["categoryId"]);
174            if (dr["categoryName"].ToString() != "")
175                model.CategoryName =dr["categoryName"].ToString();
176            if (dr["ProductKindId"].ToString() != "")
177                model.ProductKindId = Convert.ToInt32( dr["ProductKindId"].ToString());
178            if (dr["description"].ToString() != "")
179                model.Description =  dr["description"].ToString();
180            if (dr["OrderID"].ToString() != "") model.OrderId = Convert.ToInt32(dr["OrderID"]);
181            if (dr["IsDisabled"].ToString() != "") model.IsDisabled = Convert.ToInt32(dr["IsDisabled"]);
182
183            return model;
184        }
185
186        /**//// <summary>
187        /// 
188        /// </summary>
189        /// <param name="sql"></param>
190        /// <returns></returns>
191        public DataSet Query(string sql)
192        ...{
193            if (string.IsNullOrEmpty(sql))
194                return null;
195
196            DataSet dataset = new DataSet();
197            try
198            ...{
199                
200                dataset = Database.ExecuteDataSet(sql);
201            }
202            catch (Exception ex)
203            ...{
204                throw ex;
205            }
206            return dataset;
207        }
208
209
210        /**//// <summary>
211        ///
212        /// </summary>
213        /// <param name="model"></param>
214        /// <param name="filter"></param>
215        /// <returns></returns>
216        public int Update(CategoryInfo model, string filter)
217        ...{
218            int result;
219            if (string.IsNullOrEmpty(filter))
220            ...{
221                throw new Exception("The 'filter' can not be null!");
222            }
223            string sql = @"update " + tableName + " set categoryName=@categoryName,ProductKindId=@ProductKindId,description=@description ,IsDisabled=@IsDisabled,OrderId=@OrderId" + " where " + filter;
224            OleDbParameter[] prams = ...{
225                Database.MakeInParam("@CategoryName", OleDbType.VarWChar,50,model.CategoryName),
226                Database.MakeInParam("@ProductKindId", OleDbType.Integer,4,model.ProductKindId),
227                Database.MakeInParam("@Description", OleDbType.VarWChar,255,model.Description),
228                Database.MakeInParam("@IsDisabled", OleDbType.UnsignedTinyInt,1,model.IsDisabled),
229                Database.MakeInParam("@OrderId", OleDbType.Integer,4,model.OrderId)
230            };
231            try
232            ...{
233                
234                result = Database.ExecuteNonQuery(sql, prams);
235            }
236            catch (Exception ex)
237            ...{
238                throw ex;
239            }
240            return result;
241        }
242
243        #endregion
244
245        /**//// <summary>
246        /// 高级查询
247        /// </summary>
248        /// <param name="field"></param>
249        /// <param name="keywords"></param>
250        /// <returns></returns>
251        public DataSet KeywordsSearch(string field, string keywords)
252        ...{
253            string filter = "";
254            if (field.ToLower() == "Productkindname")
255            ...{
256                filter = "  ProductKindId in ( select ProductKindId from "  + configInfo.TablePrefix + "ProductKinds  where ProductKindName like '%" + keywords + "%' )  " ;
257            }
258
259            else
260            ...{
261                filter = " " + field + " like '%" + keywords + "%'";
262            }
263            
264            return GetDataSet(filter);
265        }
266
267
268    }
269}
270