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 Announce:IAnnounce
 12    ...{
 13        private ConfigInfo configInfo = new ConfigInfo();
 14        private string tableName = "Ljh_Announce";
 15
 16        public Announce()
 17        ...{
 18            if (!string.IsNullOrEmpty(configInfo.TablePrefix.Trim()))
 19                tableName = configInfo.TablePrefix + "Announce";
 20        }
 21        IAnnounce member#region IAnnounce member
 22
 23
 24        /**//// <summary>
 25        /// add 
 26        /// </summary>
 27        /// <param name="model"></param>
 28        /// <returns>返回ID, 如果发生错误则返回-1</returns>
 29        public int Add(AnnounceInfo model)
 30        ...{
 31
 32            if (model == null)
 33            ...{
 34                return 0;
 35            }
 36            string commandText = "insert into " + this.tableName + "(title,content,author,addTime,isSelected,outTime) values(@title,@content,@author,@addTime,@isSelected,@outTime) ";
 37            OleDbParameter[] commandParameters =     
 38                ...{
 39                    Database.MakeInParam("@Title", OleDbType.VarWChar,255,model.Title),
 40                    Database.MakeInParam("@Content", OleDbType.VarWChar,0,model.Content),
 41                    Database.MakeInParam("@Author", OleDbType.VarWChar,50,model.Author),
 42                    Database.MakeInParam("@AddTime", OleDbType.Date,8,model.AddTime),
 43                    Database.MakeInParam("@IsSelected", OleDbType.UnsignedTinyInt,1,model.IsSelected),
 44                    Database.MakeInParam("@OutTime", OleDbType.Integer,4,model.OutTime)
 45
 46                                                            };
 47
 48            int intIdentity = -1;
 49            try
 50            ...{
 51                Database.ExecuteNonQuery(CommandType.Text, out intIdentity, commandText, commandParameters);
 52            }
 53            catch (Exception exception)
 54            ...{
 55                throw exception;
 56            }
 57            return intIdentity;
 58
 59        }
 60
 61        /**//// <summary>
 62        /// 删除 
 63        /// </summary>
 64        /// <param name="filter">where后面的条件语句，不加where</param>
 65        /// <returns>返回影响行数</returns>
 66        public int Delete(string filter)
 67        ...{
 68            int count = -1;
 69            string sql = @"delete from " + tableName;
 70            if (!string.IsNullOrEmpty(filter.Trim()))
 71            ...{
 72                sql = sql + "  where  " + filter;
 73            }
 74            try
 75            ...{
 76                
 77                count = Database.ExecuteNonQuery(sql);
 78            }
 79            catch (Exception ex)
 80            ...{
 81                throw ex;
 82            }
 83            return count;
 84        }
 85
 86        /**//// <summary>
 87        /// 
 88        /// </summary>
 89        /// <param name="filter"></param>
 90        /// <returns></returns>
 91        public bool Exist(string filter)
 92        ...{
 93            bool result = false;
 94            string sql = @"select * from " + tableName;
 95            if (!string.IsNullOrEmpty(filter))
 96            ...{
 97                sql = sql + "  where  " + filter;
 98            }
 99            try
100            ...{
101                
102
103                DataSet dataset = new DataSet();
104                dataset = Database.ExecuteDataSet(sql);
105                if (dataset.Tables[0].Rows.Count > 0)
106                ...{
107                    result = true;
108                }
109            }
110            catch (Exception ex)
111            ...{
112                throw ex;
113            }
114            return result;
115        }
116
117        /**//// <summary>
118        /// 返回所有
119        /// </summary>
120        /// <returns>返回所有</returns>
121        public DataSet GetDataSet()
122        ...{
123            string sql = "select * from " + tableName;
124            DataSet dataset = new DataSet();
125            try
126            ...{
127                
128                dataset = Database.ExecuteDataSet(sql);
129
130            }
131            catch (Exception ex)
132            ...{
133                throw ex;
134            }
135            return dataset;
136        }
137
138        public DataSet GetDataSet(string filter)
139        ...{
140            if (string.IsNullOrEmpty(filter))
141                return null;
142
143            string sql = "select * from " + tableName + " where " + filter;
144            DataSet dataset = new DataSet();
145            try
146            ...{
147                
148                dataset = Database.ExecuteDataSet(sql);
149            }
150            catch (Exception ex)
151            ...{
152                throw ex;
153            }
154            return dataset;
155        }
156
157        /**//// <summary>
158        ///
159        /// </summary>
160        /// <param name="dr"></param>
161        /// <returns></returns>
162        public AnnounceInfo GetModel(DataRow dr)
163        ...{
164            if (dr == null)
165                return null;
166            AnnounceInfo model = new AnnounceInfo();
167
168            if (dr["id"].ToString() != "") model.Id =　Convert.ToInt32( dr["id"].ToString());
169            if (dr["title"].ToString() != "") model.Title = dr["title"].ToString();
170            if (dr["content"].ToString() != "") model.Content = dr["content"].ToString();
171            if (dr["author"].ToString() != "") model.Author = dr["author"].ToString();
172            if (dr["addTime"].ToString() != "") model.AddTime = dr["addTime"].ToString();
173            if (dr["isSelected"].ToString() != "") model.IsSelected =　Convert.ToInt32( dr["isSelected"]);
174            if (dr["outTime"].ToString() != "") model.OutTime = 　Convert.ToInt32(dr["outTime"].ToString());
175
176            return model;
177        }
178
179        /**//// <summary>
180        /// 
181        /// </summary>
182        /// <param name="sql"></param>
183        /// <returns></returns>
184        public DataSet Query(string sql)
185        ...{
186            if (string.IsNullOrEmpty(sql))
187                return null;
188
189            DataSet dataset = new DataSet();
190            try
191            ...{
192                
193                dataset = Database.ExecuteDataSet(sql);
194            }
195            catch (Exception ex)
196            ...{
197                throw ex;
198            }
199            return dataset;
200        }
201
202
203        /**//// <summary>
204        /// update  
205        /// </summary>
206        /// <param name="model"></param>
207        /// <param name="filter"></param>
208        /// <returns></returns>
209        public int Update(AnnounceInfo model, string filter)
210        ...{
211            int result;
212            if (string.IsNullOrEmpty(filter))
213            ...{
214                throw new Exception("The 'filter' can not be null!");
215            }
216            string sql = @"update " + tableName + " set title=@title,content=@content,author=@author,addTime=@addTime,isSelected=@isSelected,outTime=@outTime " + " where " + filter;
217
218            OleDbParameter[] prams = ...{
219                    Database.MakeInParam("@Title", OleDbType.VarWChar,255,model.Title),
220                    Database.MakeInParam("@Content", OleDbType.VarWChar,0,model.Content),
221                    Database.MakeInParam("@Author", OleDbType.VarWChar,50,model.Author),
222                    Database.MakeInParam("@AddTime", OleDbType.Date,8,model.AddTime),
223                    Database.MakeInParam("@IsSelected", OleDbType.UnsignedTinyInt,1,model.IsSelected),
224                    Database.MakeInParam("@OutTime", OleDbType.Integer,4,model.OutTime)
225
226                                                            };
227
228            try
229            ...{
230                
231                result = Database.ExecuteNonQuery(sql, prams);
232            }
233            catch (Exception ex)
234            ...{
235                throw ex;
236            }
237            return result;
238        }
239
240        #endregion
241
242        /**//// <summary>
243        /// 快速搜索
244        /// </summary>
245        /// <param name="searchType"></param>
246        /// <returns></returns>
247        public DataSet QuickSearch(int searchType)
248        ...{
249            string str;
250            switch (searchType)
251            ...{
252                case 1:
253                    str = " [IsSelected] = 1 order by Id Desc";
254                    break;
255
256                case 2:
257                    str = " DateDiff('d',addTime,now()) = 0  order by Id Desc ";
258                    break;
259
260                case 3:
261                    str = " DateDiff('ww',addTime,now()) = 0  order by Id Desc ";
262                    break;
263
264                case 4:
265                    str = "  DateDiff('m',addTime,now()) = 0 order by Id Desc ";
266                    break;
267
268                case 5:
269                    str = "  DateDiff('m',addTime,now()) <= 3 order by Id Desc ";
270                    break;
271
272                default:
273                    str = "  DateDiff('m',addTime,now()) > 3  order by Id Desc";
274                    break;
275            }
276            return this.GetDataSet(str);
277        }
278
279 
280
281        /**//// <summary>
282        /// 高级查询
283        /// </summary>
284        /// <param name="field"></param>
285        /// <param name="keywords"></param>
286        /// <returns></returns>
287        public DataSet KeywordsSearch(string field, string keywords)
288        ...{
289