?? usersdal.cs
字號:
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using Model;
namespace DAL
{
/// <summary>
/// UsersDAL類
/// </summary>
public class UsersDAL : IDisposable
{
public UsersInfo Select(int id)
{
string strSQL = "SELECT * FROM Users where id="+id;
UsersInfo objInfo = new UsersInfo();
using (SqlDataReader rdr = SqlHelper.ExecuteReader(SqlHelper.ConnString, CommandType.Text, strSQL, null))
{
if (rdr.Read())
{
objInfo.Id = Convert.ToInt32(rdr["Id"]);
objInfo.LoginPwd = rdr["LoginPwd"].ToString();
objInfo.FriendshipPolicy =new FriendshipPolicyInfo(Convert.ToInt32(rdr["FriendshipPolicyId"]));
objInfo.NickName = rdr["NickName"].ToString();
objInfo.FaceId = Convert.ToInt32(rdr["FaceId"]);
objInfo.Sex = rdr["Sex"].ToString();
objInfo.Age = Convert.ToInt32(rdr["Age"]);
objInfo.Name = rdr["Name"].ToString();
objInfo.Star= new StarInfo(Convert.ToInt32(rdr["StarId"]));
objInfo.BloodType= new BloodTypeInfo(Convert.ToInt32(rdr["BloodTypeId"]));
}
}
return objInfo;
}
public List<UsersInfo> SelectAll()
{
string strSQL = "SELECT * FROM Users";
List<UsersInfo> objItems = new List<UsersInfo>();
using (SqlDataReader rdr = SqlHelper.ExecuteReader(SqlHelper.ConnString, CommandType.Text, strSQL, null))
{
while (rdr.Read())
{
UsersInfo objInfo = new UsersInfo();
objInfo.Id = Convert.ToInt32(rdr["Id"]);
objInfo.LoginPwd = rdr["LoginPwd"].ToString();
objInfo.FriendshipPolicy = new FriendshipPolicyInfo(Convert.ToInt32(rdr["FriendshipPolicyId"]));
objInfo.NickName = rdr["NickName"].ToString();
objInfo.FaceId = Convert.ToInt32(rdr["FaceId"]);
objInfo.Sex = rdr["Sex"].ToString();
objInfo.Age = Convert.ToInt32(rdr["Age"]);
objInfo.Name = rdr["Name"].ToString();
objInfo.Star = new StarInfo(Convert.ToInt32(rdr["StarId"]));
objInfo.BloodType = new BloodTypeInfo(Convert.ToInt32(rdr["BloodTypeId"]));
objItems.Add(objInfo);
}
}
return objItems;
}
public void Insert(UsersInfo entity)
{
string strSQL = "INSERT INTO [Users](LoginPwd,FriendshipPolicyId,NickName,FaceId,Sex,Age,Name,StarId,BloodTypeId) VALUES(@LoginPwd,@FriendshipPolicyId,@NickName,@FaceId,@Sex,@Age,@Name,@StarId,@BloodTypeId) ";
SqlParameter[] pars = new SqlParameter[]{
new SqlParameter("@LoginPwd",entity.LoginPwd),
new SqlParameter("@FriendshipPolicyId",entity.FriendshipPolicy.Id),
new SqlParameter("@NickName",entity.NickName),
new SqlParameter("@FaceId",entity.FaceId),
new SqlParameter("@Sex",entity.Sex),
new SqlParameter("@Age",entity.Age),
new SqlParameter("@Name",entity.Name),
new SqlParameter("@StarId",entity.Star.Id),
new SqlParameter("@BloodTypeId",entity.BloodType.Id)
};
SqlHelper.ExecuteNonQuery(SqlHelper.ConnString, CommandType.Text, strSQL, pars);
}
/// <summary>
/// 用戶注冊
/// </summary>
/// <param name="entity"></param>
/// <returns></returns>
public object Register(UsersInfo entity)
{
string strSQL = "INSERT INTO [Users](LoginPwd,FriendshipPolicyId,NickName,FaceId,Sex,Age,Name,StarId,BloodTypeId) VALUES(@LoginPwd,@FriendshipPolicyId,@NickName,@FaceId,@Sex,@Age,@Name,@StarId,@BloodTypeId) select @@identity";
SqlParameter[] pars = new SqlParameter[]{
new SqlParameter("@LoginPwd",entity.LoginPwd),
new SqlParameter("@FriendshipPolicyId",entity.FriendshipPolicy.Id),
new SqlParameter("@NickName",entity.NickName),
new SqlParameter("@FaceId",entity.FaceId),
new SqlParameter("@Sex",entity.Sex),
new SqlParameter("@Age",entity.Age),
new SqlParameter("@Name",entity.Name),
new SqlParameter("@StarId",entity.Star.Id),
new SqlParameter("@BloodTypeId",entity.BloodType.Id)
};
return SqlHelper.ExecuteScalar(SqlHelper.ConnString, CommandType.Text, strSQL, pars);
}
/// <summary>
/// 好友搜索
/// </summary>
/// <param name="entity"></param>
/// <returns></returns>
public List<UsersInfo> Search(UsersInfo entity)
{
string strSQL = "SELECT * FROM Users WHERE 1=1";
//進入條件組合查詢
if (entity != null)
{
if (entity.Id > 0)
strSQL += " and id=" + entity.Id;
}
List<UsersInfo> objItems = new List<UsersInfo>();
using (SqlDataReader rdr = SqlHelper.ExecuteReader(SqlHelper.ConnString, CommandType.Text, strSQL, null))
{
while (rdr.Read())
{
UsersInfo objInfo = new UsersInfo();
objInfo.Id = Convert.ToInt32(rdr["Id"]);
//objInfo.LoginPwd = rdr["LoginPwd"].ToString();
objInfo.FriendshipPolicy = new FriendshipPolicyInfo(Convert.ToInt32(rdr["FriendshipPolicyId"]));
objInfo.NickName = rdr["NickName"].ToString();
objInfo.FaceId = Convert.ToInt32(rdr["FaceId"]);
objInfo.Sex = rdr["Sex"].ToString();
objInfo.Age = Convert.ToInt32(rdr["Age"]);
objInfo.Name = rdr["Name"].ToString();
objInfo.Star = new StarInfo(Convert.ToInt32(rdr["StarId"]));
objInfo.BloodType = new BloodTypeInfo(Convert.ToInt32(rdr["BloodTypeId"]));
objItems.Add(objInfo);
}
}
return objItems;
}
public List<UsersInfo> Search(int userId, string nickName,int fromAge ,int toAge, string sex)
{
string strSQL = "SELECT * FROM Users WHERE 1=1";
if (userId > 0)
strSQL += " and Id="+userId;
if (!string.IsNullOrEmpty(nickName))
strSQL += " and NickName like '%" + nickName + "%'";
if (fromAge > 0)
strSQL += " and Age >=" + fromAge;
if (toAge > 0)
strSQL += " and Age <=" + toAge;
if (!string.IsNullOrEmpty(sex))
strSQL += " and Sex ='" + sex+"'";
List<UsersInfo> objItems = new List<UsersInfo>();
using (SqlDataReader rdr = SqlHelper.ExecuteReader(SqlHelper.ConnString, CommandType.Text, strSQL, null))
{
while (rdr.Read())
{
UsersInfo objInfo = new UsersInfo();
objInfo.Id = Convert.ToInt32(rdr["Id"]);
//objInfo.LoginPwd = rdr["LoginPwd"].ToString();
objInfo.FriendshipPolicy = new FriendshipPolicyInfo(Convert.ToInt32(rdr["FriendshipPolicyId"]));
objInfo.NickName = rdr["NickName"].ToString();
objInfo.FaceId = Convert.ToInt32(rdr["FaceId"]);
objInfo.Sex = rdr["Sex"].ToString();
objInfo.Age = Convert.ToInt32(rdr["Age"]);
objInfo.Name = rdr["Name"].ToString();
objInfo.Star = new StarInfo(Convert.ToInt32(rdr["StarId"]));
objInfo.BloodType = new BloodTypeInfo(Convert.ToInt32(rdr["BloodTypeId"]));
objItems.Add(objInfo);
}
}
return objItems;
}
/// <summary>
/// 更新基本資料
/// </summary>
/// <param name="entity"></param>
public void UpdateProfile(UsersInfo entity)
{
string strSQL = "UPDATE Users SET NickName =@NickName,FaceId = @FaceId,Sex = @Sex,Age = @Age,Name = @Name,StarId =@StarId,BloodTypeId =@BloodTypeId WHERE Id=@Id";
SqlParameter[] pars = new SqlParameter[]{
new SqlParameter("@NickName",entity.NickName),
new SqlParameter("@FaceId",entity.FaceId),
new SqlParameter("@Sex",entity.Sex),
new SqlParameter("@Age",entity.Age),
new SqlParameter("@Name",entity.Name),
new SqlParameter("@StarId",entity.Star.Id),
new SqlParameter("@BloodTypeId",entity.BloodType.Id),
new SqlParameter("@Id",entity.Id)
};
SqlHelper.ExecuteNonQuery(SqlHelper.ConnString, CommandType.Text, strSQL, pars);
}
/// <summary>
/// 更改密碼及身份驗證
/// </summary>
/// <param name="password"></param>
/// <param name="identity"></param>
public void ChangeSecurity(int id, string password, int identity)
{
string strSQL = "UPDATE Users SET LoginPwd =@LoginPwd,FriendshipPolicyId = @FriendshipPolicyId WHERE Id=@Id";
SqlParameter[] pars = new SqlParameter[]{
new SqlParameter("@LoginPwd",password),
new SqlParameter("@FriendshipPolicyId",identity),
new SqlParameter("@Id",id)
};
SqlHelper.ExecuteNonQuery(SqlHelper.ConnString, CommandType.Text, strSQL, pars);
}
/// <summary>
/// 更改身份驗證
/// </summary>
/// <param name="password"></param>
/// <param name="identity"></param>
public void ChangeIdentity(int id, int identity)
{
string strSQL = string.Format("UPDATE Users SET FriendshipPolicyId = {0} WHERE Id={1}",identity,id);
SqlHelper.ExecuteNonQuery(SqlHelper.ConnString, CommandType.Text, strSQL, null);
}
/// <summary>
/// 更換頭像
/// </summary>
/// <param name="faceId"></param>
public void ChangeFace(int id, int faceId)
{
string strSQL = string.Format("UPDATE Users SET FaceId ={0} WHERE Id={1}", faceId, id);
SqlHelper.ExecuteNonQuery(SqlHelper.ConnString, CommandType.Text, strSQL, null);
}
#region IDisposable 成員
public void Dispose()
{
GC.SuppressFinalize(this);
}
#endregion
}
}
?? 快捷鍵說明
復制代碼
Ctrl + C
搜索代碼
Ctrl + F
全屏模式
F11
切換主題
Ctrl + Shift + D
顯示快捷鍵
?
增大字號
Ctrl + =
減小字號
Ctrl + -