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 User : IUser
 12    ...{
 13        private ConfigInfo configInfo = new ConfigInfo();
 14        private string tableName = "Ljh_Users";
 15        public User()
 16        ...{
 17            if (!string.IsNullOrEmpty(configInfo.TablePrefix.Trim()))
 18                tableName = configInfo.TablePrefix + "Users";
 19        }
 20
 21        IUser 成员#region IUser 成员
 22
 23        /**//// <summary>
 24        /// 添加会员 
 25        /// </summary>
 26        /// <param name="model"></param>
 27        /// <returns>返回用户ID, 如果发生错误则返回-1</returns>
 28        public int Add(UserInfo model)
 29        ...{
 30            if (model == null)
 31            ...{
 32                return 0;
 33            }
 34            string commandText = "insert into " + this.tableName + "(username,nickname,userpassword,realname,groupid,gender,question,answer,email,regtime,address,icq,qq,yahoo,msn,islocked,balance,userExp) values(@username,@nickname,@userpassword,@realname,@groupid,@gender,@question,@answer,@email,@regtime,@address,@icq,@qq,@yahoo,@msn,@islocked,@balance,@userExp) ";
 35            OleDbParameter[] commandParameters = ...{
 36                Database.MakeInParam("@UserName",OleDbType.VarWChar,20,model.UserName),
 37                Database.MakeInParam("@NickName",OleDbType.VarWChar,20,model.NickName),
 38                Database.MakeInParam("@UserPassword",OleDbType.VarWChar,32,model.UserPassword),
 39                Database.MakeInParam("@RealName",OleDbType.VarWChar,20,model.RealName),
 40                Database.MakeInParam("@GroupId",OleDbType.UnsignedTinyInt,2,model.GroupId),
 41                Database.MakeInParam("@Gender",OleDbType.UnsignedTinyInt,1,model.Gender),
 42                Database.MakeInParam("@Question",OleDbType.VarWChar,50,model.Question),
 43                Database.MakeInParam("@Answer",OleDbType.VarWChar,50,model.Answer),
 44                Database.MakeInParam("@Email",OleDbType.VarWChar,50,model.Email),
 45                Database.MakeInParam("@Regtime",OleDbType.Date,8,model.RegTime),
 46                Database.MakeInParam("@Address",OleDbType.VarWChar,255,model.Address),
 47                Database.MakeInParam("@Icq",OleDbType.VarWChar,50,model.Icq),
 48                Database.MakeInParam("@Qq",OleDbType.VarWChar,50,model.Qq),
 49                Database.MakeInParam("@Yahoo",OleDbType.VarWChar,50,model.Yahoo),
 50                Database.MakeInParam("@Msn",OleDbType.VarWChar,50,model.Msn),
 51                Database.MakeInParam("@Islocked",OleDbType.UnsignedTinyInt,1,model.IsLocked),
 52                Database.MakeInParam("@Balance",OleDbType.Currency,8,model.Balance),
 53                Database.MakeInParam("@UserExp",OleDbType.Integer,4,model.UserExp),
 54                Database.MakeInParam("@LastLogInIP",OleDbType.VarWChar,15,model.LastLogInIP),
 55                Database.MakeInParam("@LogInTimes",OleDbType.Integer,4,model.LogInTimes),
 56                Database.MakeInParam("@LastLogInTime",OleDbType.Date,8,DateTime.Now)
 57                                                           };
 58            int intIdentity = -1;
 59            try
 60            ...{
 61                Database.ExecuteNonQuery(CommandType.Text, out intIdentity, commandText, commandParameters);
 62            }
 63            catch (Exception exception)
 64            ...{
 65                throw exception;
 66            }
 67            return intIdentity;
 68        }
 69
 70
 71
 72
 73        /**//// <summary>
 74        /// 删除会员 
 75        /// </summary>
 76        /// <param name="filter">where后面的条件语句，不加where</param>
 77        /// <returns>返回影响行数</returns>
 78        public int Delete(string filter)
 79        ...{
 80            int count = -1;
 81            string sql = @"delete from " + tableName;
 82            if (!string.IsNullOrEmpty(filter.Trim()))
 83            ...{
 84                sql = sql + "  where  " + filter;
 85            }
 86            try
 87            ...{
 88                
 89                count = Database.ExecuteNonQuery(sql);
 90            }
 91            catch (Exception ex)
 92            ...{
 93                throw ex;
 94            }
 95            return count;
 96        }
 97
 98        /**//// <summary>
 99        /// 判断用户是否存在
100        /// </summary>
101        /// <param name="filter">UserName=condition|UserName and password</param>
102        /// <returns></returns>
103        public bool Exist(string filter)
104        ...{
105            string sql = @"SELECT COUNT(userId) FROM " + tableName;
106            if (!string.IsNullOrEmpty(filter.Trim()))
107            ...{
108                sql = sql + "  where  " + filter;
109            }
110            try
111            ...{
112                return Convert.ToInt32( Database.ExecuteScalar(sql) ) >= 1; 
113            }
114            catch (Exception ex)
115            ...{
116                throw ex;
117            }
118        }
119
120        /**//// <summary>
121        /// 返回所有用户
122        /// </summary>
123        /// <returns>返回所有用户</returns>
124        public DataSet GetDataSet()
125        ...{
126            string sql = "select * from " + tableName;
127            DataSet dataset = new DataSet();
128            try
129            ...{
130                
131                dataset = Database.ExecuteDataSet(sql);
132
133            }
134            catch (Exception ex)
135            ...{
136                throw ex;
137            }
138            return dataset;
139        }
140
141        /**//// <summary>
142        /// 
143        /// </summary>
144        /// <param name="filter"></param>
145        /// <returns></returns>
146        public DataSet GetDataSet(string filter)
147        ...{
148            if (string.IsNullOrEmpty(filter))
149                return null;
150
151            string sql = "select * from " + tableName + " where " + filter;
152            DataSet dataset = new DataSet();
153            try
154            ...{
155                
156                dataset = Database.ExecuteDataSet(sql);
157            }
158            catch (Exception ex)
159            ...{
160                throw ex;
161            }
162            return dataset;
163        }
164
165        /**//// <summary>
166        /// /
167        /// </summary>
168        /// <param name="dr"></param>
169        /// <returns></returns>
170        public UserInfo GetModel(DataRow dr)
171        ...{
172            if (dr == null)
173                return null;
174            UserInfo model = new UserInfo();
175
176            if (dr["userid"].ToString() != "")
177                model.UserId = Convert.ToInt32(dr["userid"]);
178            if (dr["UserName"].ToString() != "")
179                model.UserName = dr["UserName"].ToString();
180            if (dr["nickname"].ToString() != "")
181                model.NickName = dr["nickname"].ToString();
182            if (dr["userpassword"].ToString() != "")
183                model.UserPassword = dr["userpassword"].ToString();
184            if (dr["realname"].ToString() != "")
185                model.RealName = dr["realname"].ToString();
186            if (dr["Groupid"].ToString() != "")
187                model.GroupId = Convert.ToInt32(dr["Groupid"]);
188            if (dr["gender"].ToString() != "")
189                model.Gender = Convert.ToInt32(dr["gender"]);
190            if (dr["Question"].ToString() != "")
191                model.Question = dr["question"].ToString();
192            if (dr["Answer"].ToString() != "")
193                model.Answer = dr["answer"].ToString();
194            if (dr["Email"].ToString() != "")
195                model.Email = dr["Email"].ToString();
196            if (dr["Regtime"].ToString() != "")
197                model.RegTime = dr["regtime"].ToString();
198            if (dr["Address"].ToString() != "")
199                model.Address = dr["Address"].ToString();
200            if (dr["Icq"].ToString() != "")
201                model.Icq = dr["icq"].ToString();
202            if (dr["qq"].ToString() != "")
203                model.Qq = dr["qq"].ToString();
204            if (dr["Yahoo"].ToString() != "")
205                model.Yahoo = dr["yahoo"].ToString();
206            if (dr["msn"].ToString() != "")
207                model.Msn = dr["msn"].ToString();
208            if (dr["Islocked"].ToString() != "")
209                model.IsLocked = Convert.ToInt32(dr["islocked"]);
210            if (dr["Balance"].ToString() != "")
211                model.Balance = Convert.ToDecimal(dr["balance"]);
212            if (dr["UserExp"].ToString() != "")
213                model.UserExp = Convert.ToInt32(dr["userExp"]);
214
215            if (dr["LastLogInIP"].ToString() != "") model.LastLogInIP = dr["LastLogInIP"].ToString();
216            if (dr["LogInTimes"].ToString() != "") model.LogInTimes = Convert.ToInt32( dr["LogInTimes"].ToString());
217            if (dr["LastLogInTime"].ToString() != "") model.LastLogInTime = dr["LastLogInTime"].ToString();
218
219            return model;
220        }
221
222        /**//// <summary>
223        /// 
224        /// </summary>
225        /// <param name="sql"></param>
226        /// <returns></returns>
227        public DataSet Query(string sql)
228        ...{
229            if (string.IsNullOrEmpty(sql))
230                return null;
231
232            DataSet dataset = new DataSet();
233            try
234            ...{
235                
236                dataset = Database.ExecuteDataSet(sql);
237            }
238            catch (Exception ex)
239            ...{
240                throw ex;
241            }
242            return dataset;
243        }
244
245        /**//// <summary>
246        /// 
247        /// </summary>
248        /// <param name="model"></param>
249        /// <param name="filter"></param>
250        /// <returns></returns>
251        public int Update(UserInfo model, string filter)        
252        ...{
253            int result;
254            if ( string.IsNullOrEmpty(filter) )
255            ...{
256                throw new Exception("The 'filter' can not be null!");
257            }
258            string sql = @"update " + tableName + " set  UserName=@UserName, nickname=@nickname,userpassword=@userpassword, realname=@realname, Groupid=@Groupid, gender = @gender,question=@question, answer =@answer, Email=@Email, regtime =@regtime,Address=@Address,icq =@icq,qq =@qq,yahoo=@yahoo,msn= @msn,islocked=@islocked,balance=@balance,userExp=@userExp,LastLogInIP=@LastLogInIP,LogInTimes=@LogInTimes,LastLogInTime=@LastLogInTime " + " where " + filter; 
259            OleDbParameter[] prams = ...{
260                Database.MakeInParam("@UserName",OleDbType.VarWChar,20,model.UserName),
261                Database.MakeInParam("@NickName",OleDbType.VarWChar,20,model.NickName),
262                Database.MakeInParam("@UserPassword",OleDbType.VarWChar,32,model.UserPassword),
263                Database.MakeInParam("@RealName",OleDbType.VarWChar,20,model.RealName),
264                Database.MakeInParam("@GroupId",OleDbType.UnsignedTinyInt,2,model.GroupId),
265                Database.MakeInParam("@Gender",OleDbType.UnsignedTinyInt,1,model.Gender),
266                Database.MakeInParam("@Question",OleDbType.VarWChar,50,model.Question),
267                Database.MakeInParam(<