/// <summary>
/// 得到一个对象实体
/// </summary>
public User DataRowToModel(DataRow row)
{
User model=new User();
if (row != null)
{
foreach (DataColumn col in row.Table.Columns)
{
if (row[col] != null && row[col].ToString() != "")
{
switch (col.ColumnName)
{
case "id":
model.Uid = int.Parse(row[col].ToString());
break;
case "account":
model.Account = row[col].ToString();
break;
case "email":
model.Email = row[col].ToString();
break;
case "password":
model.Password = row[col].ToString();
break;
case "state":
model.State = int.Parse(row[col].ToString());
break;
case "name":
model.Name = row[col].ToString();
break;
case "phone":
model.Phone = row[col].ToString();
break;
case "company":
model.Company = row[col].ToString();
break;
case "address":
model.Address = row[col].ToString();
break;
case "sex":
model.Sex = row[col].ToString();
break;
case "register_time":
model.RegisterTime = DateTime.Parse(row[col].ToString());
break;
}
}
}
}
return model;
}
/// <summary>
/// 增加一条数据
/// </summary>
public bool Add(User model)
{
StringBuilder strSql=new StringBuilder();
strSql.Append("insert into user(");
strSql.Append("account,email,password,state,name,phone,company,address,sex,register_time)");
strSql.Append(" values (");
strSql.Append("?account,?email,?password,?state,?name,?phone,?company,?address,?sex,?register_time)");
MySqlParameter[] parameters = {
new MySqlParameter("?account", MySqlDbType.VarChar,20),
new MySqlParameter("?email", MySqlDbType.VarChar,50),
new MySqlParameter("?password", MySqlDbType.VarChar,50),
new MySqlParameter("?state", MySqlDbType.UInt16,4),
new MySqlParameter("?name", MySqlDbType.VarChar,20),
new MySqlParameter("?phone", MySqlDbType.VarChar,20),
new MySqlParameter("?company", MySqlDbType.VarChar,200),
new MySqlParameter("?address", MySqlDbType.VarChar,500),
new MySqlParameter("?sex", MySqlDbType.Enum),
new MySqlParameter("?register_time", MySqlDbType.DateTime)};
parameters[0].Value = model.Account;
parameters[1].Value = model.Email;
parameters[2].Value = model.Password;
parameters[3].Value = model.State;
parameters[4].Value = model.Name;
parameters[5].Value = model.Phone;
parameters[6].Value = model.Company;
parameters[7].Value = model.Address;
parameters[8].Value = model.Sex;
parameters[9].Value = model.RegisterTime;
int rows=MysqlHelper.ExecuteNonQuery(strSql.ToString(),parameters);
if (rows > 0)
{
return true;
}
else
{
return false;
}
}
/// <summary>
/// 更新一条数据
/// </summary>
public bool Update(User model)
{
StringBuilder strSql=new StringBuilder();
strSql.Append("update user set ");
strSql.Append("account=?account,");
strSql.Append("email=?email,");
strSql.Append("password=?password,");
strSql.Append("state=?state,");
strSql.Append("name=?name,");
strSql.Append("phone=?phone,");
strSql.Append("company=?company,");
strSql.Append("address=?address,");
strSql.Append("sex=?sex,");
strSql.Append("register_time=?register_time");
strSql.Append(" where id=?id");
MySqlParameter[] parameters = {
new MySqlParameter("?account", MySqlDbType.VarChar,20),
new MySqlParameter("?email", MySqlDbType.VarChar,50),
new MySqlParameter("?password", MySqlDbType.VarChar,50),
new MySqlParameter("?state", MySqlDbType.Int16,4),
new MySqlParameter("?name", MySqlDbType.VarChar,20),
new MySqlParameter("?phone", MySqlDbType.VarChar,20),
new MySqlParameter("?company", MySqlDbType.VarChar,200),
new MySqlParameter("?address", MySqlDbType.VarChar,500),
new MySqlParameter("?sex", MySqlDbType.Enum),
new MySqlParameter("?register_time", MySqlDbType.DateTime),
new MySqlParameter("?id", MySqlDbType.Int32,10)};
parameters[0].Value = model.Account;
parameters[1].Value = model.Email;
parameters[2].Value = model.Password;
parameters[3].Value = model.State;
parameters[4].Value = model.Name;
parameters[5].Value = model.Phone;
parameters[6].Value = model.Company;
parameters[7].Value = model.Address;
parameters[8].Value = model.Sex;
parameters[9].Value = model.RegisterTime;
parameters[10].Value = model.Uid;
int rows=MysqlHelper.ExecuteNonQuery(strSql.ToString(),parameters);
if (rows > 0)
{
return true;
}
else
{
return false;
}
}
public User GetModelByEmail(string email, string password)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select id,account,email,password,state,name,phone,company,address,sex from user ");
strSql.Append(" where email=?email and password=?password");
MySqlParameter[] parameters = {
new MySqlParameter("?email", MySqlDbType.VarChar,50),
new MySqlParameter("?password", MySqlDbType.VarChar,50)
};
parameters[0].Value = email;
parameters[1].Value = password;
User model = new User();
DataSet ds = MysqlHelper.ExecuteDataSet(strSql.ToString(), parameters);
if (ds.Tables[0].Rows.Count > 0)
{
return DataRowToModel(ds.Tables[0].Rows[0]);
}
else
{
return null;
}
}
/// <summary>
/// 得到一个对象实体
/// </summary>
public User GetModel(int id)
{
StringBuilder strSql=new StringBuilder();
strSql.Append("select id,account,email,password,state,name,phone,company,address,sex,register_time from user ");
strSql.Append(" where id=?id");
MySqlParameter[] parameters = {
new MySqlParameter("?id", MySqlDbType.Int32)
};
parameters[0].Value = id;
User model=new User();
DataSet ds=MysqlHelper.ExecuteDataSet(strSql.ToString(),parameters);
if(ds.Tables[0].Rows.Count>0)
{
return DataRowToModel(ds.Tables[0].Rows[0]);
}
else
{
return null;
}
}
/// <summary>
/// 增加一条数据
/// </summary>
public bool Add(User model)
{
return dal.Add(model);
}
/// <summary>
/// 更新一条数据
/// </summary>
public bool Update(User model)
{
return dal.Update(model);
}
public int Register(string userName, string password, string email)
{
if (!Common.FormatValidation.VerifyName(userName))
return -3;
if (!Common.FormatValidation.VerifyEmail(email))
return -4;
if (!Common.FormatValidation.VerifyPassword(password))
return -5;
if (dal.ExistsAccount(userName))
return -1;
if (dal.ExistsEmail(email))
return -2;
User user = new User();
user.Account = userName;
user.Name = userName;
user.Email = email;
user.Password = password;
user.State = 1;
user.RegisterTime = DateTime.Now;
if (dal.Add(user))
return 1;
else
return 0;
}