namespace Wish.GoldenPhoenix.DAL.SqlClient
{
public class SqlUserProvider : UserProvider
{
public override Users GetUserByID(int userId)
{
using (SqlConnection cn = new SqlConnection(this.ConnectionString))
{ SqlCommand cmd = new SqlCommand("GetUserByUserID", cn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@userid", userId); cn.Open();
Users user = null;
IDataReader dr = ExecuteReader(cmd);
while (dr.Read())
{
user = new Users();
user.UserId = userId;
user.Username = dr.GetString(1);
user.Nickname = dr.GetString(2);
user.Email = dr.GetString(4);
user.Sex = dr.GetString(7);
user.ShowEmail = dr.GetBoolean(8);
user.RegisterDate = dr.GetDateTime(dr.GetOrdinal("RegeditDate"));
user.Signature = dr.GetString(9);
user.ImageUrl = dr.GetString(10);
int no = dr.GetByte(15);
switch (no)
{
case 9: user.Type = UserType.Admin; break;
case 1: user.Type = UserType.Moderator; break;
default: user.Type = UserType.Normal; break;
}
}
return user;
}
} public override int GetUserIdByUsername(string name)
{
using (SqlConnection cn = new SqlConnection(this.ConnectionString))
{
SqlCommand cmd = new SqlCommand("GetUserIdByUsername", cn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@username", name);
cn.Open();
return Convert.ToInt32(cmd.ExecuteScalar());
}
}
public override int InsertUser(Users theUser)
{
using (SqlConnection cn = new SqlConnection(this.ConnectionString))
{
SqlCommand cmd = new SqlCommand("InsertUser", cn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Username", theUser.Username);
cmd.Parameters.AddWithValue("@NickName", theUser.Nickname);
cmd.Parameters.AddWithValue("@Password", theUser.Password);
cmd.Parameters.AddWithValue("@Email", theUser.Email);
cmd.Parameters.AddWithValue("@Question", theUser.Question);
cmd.Parameters.AddWithValue("@Answer", theUser.Answer);
cmd.Parameters.AddWithValue("@Sex", theUser.Sex);
cmd.Parameters.AddWithValue("@ShowEmail", theUser.ShowEmail);
cmd.Parameters.AddWithValue("@Signature", theUser.Signature);
cmd.Parameters.AddWithValue("@ImageUrl", theUser.ImageUrl);
cmd.Parameters.Add("@UserId", SqlDbType.Int).Direction = ParameterDirection.Output;
cn.Open();
int result = ExecuteNonQuery(cmd);
return (int)cmd.Parameters["@UserId"].Value;
}
} public override String GetUserNameByUserId(int userID)
{
string name = string.Empty;
using (SqlConnection cn = new SqlConnection(this.ConnectionString))
{
SqlCommand cmd = new SqlCommand("GetUserNameByUserId", cn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@UserID",userID);
cn.Open();
SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
while (dr.Read())
{
name = dr.GetString(dr.GetOrdinal("UserName"));
}
return name;
}
} public override bool ValidateEmail(string email)
{
using (SqlConnection cn = new SqlConnection(this.ConnectionString))
{
SqlCommand cmd = new SqlCommand("GetUserIdByEmail", cn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@email", email); cn.Open();
//就是下面这一句
object o = cmd.ExecuteScalar(); return o != null;
}
} public override List<Users> GetUserList()
{
return null;
} public override bool DeleteUser(int userId)
{
return false;
} public override bool UpdateUser(Users theUser)
{
using (SqlConnection cn = new SqlConnection(this.ConnectionString))
{
SqlCommand cmd = new SqlCommand("UpdateUser", cn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@NickName",theUser.Nickname);
cmd.Parameters.AddWithValue("@Sex",theUser.Sex);
cmd.Parameters.AddWithValue("@Email",theUser.Email);
cmd.Parameters.AddWithValue("@ShowEmail",theUser.ShowEmail);
cmd.Parameters.AddWithValue("@Signature",theUser.Signature);
cmd.Parameters.AddWithValue("@ImageUrl",theUser.ImageUrl);
cmd.Parameters.AddWithValue("@UserID",theUser.UserId); cn.Open(); int no = cmd.ExecuteNonQuery(); return no > 0;
}
}
}
}
{
public class SqlUserProvider : UserProvider
{
public override Users GetUserByID(int userId)
{
using (SqlConnection cn = new SqlConnection(this.ConnectionString))
{ SqlCommand cmd = new SqlCommand("GetUserByUserID", cn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@userid", userId); cn.Open();
Users user = null;
IDataReader dr = ExecuteReader(cmd);
while (dr.Read())
{
user = new Users();
user.UserId = userId;
user.Username = dr.GetString(1);
user.Nickname = dr.GetString(2);
user.Email = dr.GetString(4);
user.Sex = dr.GetString(7);
user.ShowEmail = dr.GetBoolean(8);
user.RegisterDate = dr.GetDateTime(dr.GetOrdinal("RegeditDate"));
user.Signature = dr.GetString(9);
user.ImageUrl = dr.GetString(10);
int no = dr.GetByte(15);
switch (no)
{
case 9: user.Type = UserType.Admin; break;
case 1: user.Type = UserType.Moderator; break;
default: user.Type = UserType.Normal; break;
}
}
return user;
}
} public override int GetUserIdByUsername(string name)
{
using (SqlConnection cn = new SqlConnection(this.ConnectionString))
{
SqlCommand cmd = new SqlCommand("GetUserIdByUsername", cn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@username", name);
cn.Open();
return Convert.ToInt32(cmd.ExecuteScalar());
}
}
public override int InsertUser(Users theUser)
{
using (SqlConnection cn = new SqlConnection(this.ConnectionString))
{
SqlCommand cmd = new SqlCommand("InsertUser", cn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Username", theUser.Username);
cmd.Parameters.AddWithValue("@NickName", theUser.Nickname);
cmd.Parameters.AddWithValue("@Password", theUser.Password);
cmd.Parameters.AddWithValue("@Email", theUser.Email);
cmd.Parameters.AddWithValue("@Question", theUser.Question);
cmd.Parameters.AddWithValue("@Answer", theUser.Answer);
cmd.Parameters.AddWithValue("@Sex", theUser.Sex);
cmd.Parameters.AddWithValue("@ShowEmail", theUser.ShowEmail);
cmd.Parameters.AddWithValue("@Signature", theUser.Signature);
cmd.Parameters.AddWithValue("@ImageUrl", theUser.ImageUrl);
cmd.Parameters.Add("@UserId", SqlDbType.Int).Direction = ParameterDirection.Output;
cn.Open();
int result = ExecuteNonQuery(cmd);
return (int)cmd.Parameters["@UserId"].Value;
}
} public override String GetUserNameByUserId(int userID)
{
string name = string.Empty;
using (SqlConnection cn = new SqlConnection(this.ConnectionString))
{
SqlCommand cmd = new SqlCommand("GetUserNameByUserId", cn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@UserID",userID);
cn.Open();
SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
while (dr.Read())
{
name = dr.GetString(dr.GetOrdinal("UserName"));
}
return name;
}
} public override bool ValidateEmail(string email)
{
using (SqlConnection cn = new SqlConnection(this.ConnectionString))
{
SqlCommand cmd = new SqlCommand("GetUserIdByEmail", cn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@email", email); cn.Open();
//就是下面这一句
object o = cmd.ExecuteScalar(); return o != null;
}
} public override List<Users> GetUserList()
{
return null;
} public override bool DeleteUser(int userId)
{
return false;
} public override bool UpdateUser(Users theUser)
{
using (SqlConnection cn = new SqlConnection(this.ConnectionString))
{
SqlCommand cmd = new SqlCommand("UpdateUser", cn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@NickName",theUser.Nickname);
cmd.Parameters.AddWithValue("@Sex",theUser.Sex);
cmd.Parameters.AddWithValue("@Email",theUser.Email);
cmd.Parameters.AddWithValue("@ShowEmail",theUser.ShowEmail);
cmd.Parameters.AddWithValue("@Signature",theUser.Signature);
cmd.Parameters.AddWithValue("@ImageUrl",theUser.ImageUrl);
cmd.Parameters.AddWithValue("@UserID",theUser.UserId); cn.Open(); int no = cmd.ExecuteNonQuery(); return no > 0;
}
}
}
}
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货