1using System;
  2using System.Collections.Generic;
  3using System.Data;
  4using System.Data.OleDb;
  5using MyShop.IDAL;
  6using MyShop.Model;
  7
  8namespace MyShop.AccessDAL
  9...{
 10     public class City:ICity
 11    ...{
 12        private ConfigInfo configInfo = new ConfigInfo();
 13        private string tableName = "Ljh_City";
 14
 15        public City()
 16        ...{
 17            if (!string.IsNullOrEmpty(configInfo.TablePrefix.Trim()))
 18                tableName = configInfo.TablePrefix + "City";
 19        }
 20        ICity member#region ICity member
 21
 22        /**//// <summary>
 23        /// 添加
 24        /// </summary>
 25        /// <param name="model"></param>
 26        /// <returns>返回ID, 如果发生错误则返回-1</returns>
 27        public int Add(CityInfo model)
 28        ...{
 29            if (model == null)
 30            ...{
 31                return -1;
 32            }
 33            string commandText = "insert into " + this.tableName + "(country,province,city,area,postCode,areaCode) values(@country,@province,@city,@area,@postCode,@areaCode)   ";
 34            OleDbParameter[] commandParameters = ...{
 35                Database.MakeInParam("@Country", OleDbType.VarWChar,20,model.Country),
 36                Database.MakeInParam("@Province", OleDbType.VarWChar,25,model.Province),
 37                Database.MakeInParam("@City", OleDbType.VarWChar,30,model.City),
 38                Database.MakeInParam("@Area", OleDbType.VarWChar,25,model.Area),
 39                Database.MakeInParam("@PostCode", OleDbType.VarWChar,15,model.PostCode),
 40                Database.MakeInParam("@AreaCode", OleDbType.VarWChar,8,model.AreaCode)
 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        /**//// <summary>
 58        /// 删除
 59        /// </summary>
 60        /// <param name="filter">where后面的条件语句，不加where</param>
 61        /// <returns>返回影响行数</returns>
 62        public int Delete(string filter)
 63        ...{
 64            int count = -1;
 65            string sql = @"delete from " + tableName;
 66            if (!string.IsNullOrEmpty(filter.Trim()))
 67            ...{
 68                sql = sql + "  where  " + filter;
 69            }
 70            try
 71            ...{
 72                
 73                count = Database.ExecuteNonQuery(sql);
 74            }
 75            catch (Exception ex)
 76            ...{
 77                throw ex;
 78            }
 79            return count;
 80        }
 81
 82        /**//// <summary>
 83        /// 判断是否存在
 84        /// </summary>
 85        /// <param name="filter">UserName=condition|UserName and password</param>
 86        /// <returns></returns>
 87        public bool Exist(string filter)
 88        ...{
 89            bool result = false;
 90            string sql = @"select * from " + tableName;
 91            if (!string.IsNullOrEmpty(filter.Trim()))
 92            ...{
 93                sql = sql + "  where  " + filter;
 94            }
 95            try
 96            ...{
 97                
 98
 99                DataSet dataset = new DataSet();
100                dataset = Database.ExecuteDataSet(sql);
101                if (dataset.Tables[0].Rows.Count > 0)
102                ...{
103                    result = true;
104                }
105            }
106            catch (Exception ex)
107            ...{
108                throw ex;
109            }
110            return result;
111        }
112
113        /**//// <summary>
114        /// 返回所有
115        /// </summary>
116        /// <returns>返回所有</returns>
117        public DataSet GetDataSet()
118        ...{
119            string sql = "select * from " + tableName;
120            DataSet dataset = new DataSet();
121            try
122            ...{
123                
124                dataset = Database.ExecuteDataSet(sql);
125
126            }
127            catch (Exception ex)
128            ...{
129                throw ex;
130            }
131            return dataset;
132        }
133
134        /**//// <summary>
135        /// 
136        /// </summary>
137        /// <param name="filter"></param>
138        /// <returns></returns>
139        public DataSet GetDataSet(string filter)
140        ...{
141            if (string.IsNullOrEmpty(filter))
142                return null;
143
144            string sql = "select * from " + tableName + " where " + filter;
145            DataSet dataset = new DataSet();
146            try
147            ...{
148                
149                dataset = Database.ExecuteDataSet(sql);
150            }
151            catch (Exception ex)
152            ...{
153                throw ex;
154            }
155            return dataset;
156        }
157
158        /**//// <summary>
159        /// /
160        /// </summary>
161        /// <param name="dr"></param>
162        /// <returns></returns>
163        public CityInfo GetModel(DataRow dr)
164        ...{
165            if (dr == null)
166                return null;
167            CityInfo model = new CityInfo();
168
169            if (dr["AreaId"].ToString() != "") model.AreaId = Convert.ToInt32( dr["AreaId"].ToString());
170            if (dr["Country"].ToString() != "") model.Country = dr["Country"].ToString();
171            if (dr["Province"].ToString() != "") model.Province = dr["Province"].ToString();
172            if (dr["City"].ToString() != "") model.City = dr["City"].ToString();
173            if (dr["Area"].ToString() != "") model.Area = dr["Area"].ToString();
174            if (dr["PostCode"].ToString() != "") model.PostCode = dr["PostCode"].ToString();
175            if (dr["AreaCode"].ToString() != "") model.AreaCode = dr["AreaCode"].ToString();
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        /**//// <summary>
203        /// 
204        /// </summary>
205        /// <param name="model"></param>
206        /// <param name="filter"></param>
207        /// <returns></returns>
208        public int Update(CityInfo model, string filter)
209        ...{
210            int result;
211            if (string.IsNullOrEmpty(filter))
212            ...{
213                throw new Exception("The 'filter' can not be null!");
214            }
215            string sql = @"update " + tableName + " set  country=@country,province    =@province,city    =@city,area    =@area,postCode    =@postCode,areaCode    =@areaCode " + " where " + filter;
216            OleDbParameter[] prams = ...{
217                Database.MakeInParam("@Country", OleDbType.VarWChar,20,model.Country),
218                Database.MakeInParam("@Province", OleDbType.VarWChar,25,model.Province),
219                Database.MakeInParam("@City", OleDbType.VarWChar,30,model.City),
220                Database.MakeInParam("@Area", OleDbType.VarWChar,25,model.Area),
221                Database.MakeInParam("@PostCode", OleDbType.VarWChar,15,model.PostCode),
222                Database.MakeInParam("@AreaCode", OleDbType.VarWChar,8,model.AreaCode)
223                                                           };
224
225            try
226            ...{
227                
228                result = Database.ExecuteNonQuery(sql, prams);
229            }
230            catch (Exception ex)
231            ...{
232                throw ex;
233            }
234            return result;
235        }
236
237        #endregion
238
239         public DataSet GetProvinceList()
240         ...{
241             return Database.ExecuteDataSet(string.Format("Select province From {0}   group by province", this.tableName));
242         }
243
244 
245
246 
247
248    }
249}
250