public int Delete(Role Role_Code)
{
DBHelper db = new DBHelper();
DbCommand cmd = db.GetSqlStringCommand("DELETE FROM Role WHERE Role_Code = '" + Role_Code.Role_Code+"'");
return db.ExecuteNonQuery(cmd);
}
public int Update(Role Role)
{
DBHelper db = new DBHelper();
DbCommand cmd = db.GetStoredProcCommand("p_Role_update");
db.AddInParameter(cmd, "@Role_Code", DbType.Guid, Role.Role_Code);
db.AddInParameter(cmd, "@Role_Name", DbType.String, Role.Role_Name);
db.AddInParameter(cmd, "@DES", DbType.String, Role.DES);
return db.ExecuteNonQuery(cmd);
}
public int Save(Role Role)
{
DBHelper db = new DBHelper();
DbCommand cmd = db.GetStoredProcCommand("p_Role_insert");//返回主键
db.AddInParameter(cmd, "@Role_Name", DbType.String, Role.Role_Name);
db.AddInParameter(cmd, "@DES", DbType.String, Role.DES);
object res = db.ExecuteScalar(cmd);
return res == null ? -1 :1;
}
public System.Linq.IQueryable<UserInfo> GetAll()
{
IList<UserInfo> list = new List<UserInfo>();
string sql = "select * from [UserInfo]";
DBHelper db = new DBHelper();
DbCommand cmd = db.GetSqlStringCommand(sql);
DataTable dt = db.ExecuteDataTable(cmd);
foreach (DataRow dataRow in dt.Rows)
{
list.Add(this.DataRowToBean(dataRow));
}
return list.AsQueryable();
}
public UserInfo GetById(object id)
{
StringBuilder strSql = new StringBuilder();
strSql.Append(
"select UserID, Link_Tel, Mob_Tel, LoginID, Password, UserName, Spell_Index, Sex, IdentityCard, Birthday, EMail, Addr, State ");
strSql.Append(" from [UserInfo] ");
strSql.Append(" where UserID='" + id.ToString() + "'");
DBHelper db = new DBHelper();
DbCommand cmd = db.GetSqlStringCommand(strSql.ToString());
DataTable dt = db.ExecuteDataTable(cmd);
foreach (DataRow row in dt.Rows)
{
return this.DataRowToBean(row);
}
return null;
}
public int Update(UserInfo t)
{
DBHelper db = new DBHelper();
DbCommand cmd = db.GetStoredProcCommand("p_UserInfo_Update");
db.AddInParameter(cmd, "@UserID", DbType.String, t.UserID);
db.AddInParameter(cmd, "@Link_Tel", DbType.String, t.Link_Tel);
db.AddInParameter(cmd, "@Mob_Tel", DbType.String, t.Mob_Tel);
db.AddInParameter(cmd, "@LoginID", DbType.String, t.LoginID);
db.AddInParameter(cmd, "@Password", DbType.String, t.Password);
db.AddInParameter(cmd, "@UserName", DbType.String, t.UserName);
db.AddInParameter(cmd, "@Spell_Index", DbType.String, t.Spell_Index);
db.AddInParameter(cmd, "@Sex", DbType.String, t.Sex);
db.AddInParameter(cmd, "@IdentityCard", DbType.String, t.IdentityCard);
db.AddInParameter(cmd, "@Birthday", DbType.String, t.Birthday);
db.AddInParameter(cmd, "@EMail", DbType.String, t.EMail);
db.AddInParameter(cmd, "@Addr", DbType.String, t.Addr);
db.AddInParameter(cmd, "@State", DbType.String, t.State);
return db.ExecuteNonQuery(cmd);
}
public int Save(UserInfo t)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("delete from [UserInfo] ");
strSql.Append(" where UserID='" + t.UserID.ToString() + "'");
DBHelper db = new DBHelper();
DbCommand cmd = db.GetSqlStringCommand(strSql.ToString());
int rows = db.ExecuteNonQuery(cmd);
if (rows > 0)
{
return 1;
}
else
{
return -1;
}
}
public PageBean<Role> GetPageList(int pageIndex, int pageSize, string sqlWhere, string order, bool sotr)
{
PageBean<Role> pageBean = new PageBean<Role>();
IList<Role> list = new List<Role>();
DBHelper db = new DBHelper();
string strSql = string.Empty;
if (sotr)
{
strSql = "select * from (select * ,ROW_NUMBER() over(order by " + order +
" desc ) num from Role where " +
sqlWhere + ") Tabl where num BETWEEN " + (pageIndex - 1) * pageSize + " and " +
(pageSize) * (pageIndex);
}
else
{
strSql = "select * from (select * ,ROW_NUMBER() over(order by " + order +
" asc ) num from Role where " +
sqlWhere + ") Tabl where num BETWEEN " + (pageIndex - 1) * pageSize + " and " +
(pageSize) * (pageIndex);
}
DbCommand cmd = db.GetSqlStringCommand(strSql);
DataTable dt = db.ExecuteDataTable(cmd);
foreach (DataRow row in dt.Rows)
{
Role ht = this.DataRowToBean(row);
list.Add(ht);
}
pageBean.rows = list;
pageBean.total = this.SelectRolePageCount(sqlWhere);
return pageBean;
}
public IQueryable<Role> SearchFor(string sqlWhere)
{
IList<Role> list = new List<Role>();
DBHelper db = new DBHelper();
DbCommand cmd = db.GetSqlStringCommand("SELECT * FROM Role where 1=1 and " + sqlWhere);
DataTable dt = db.ExecuteDataTable(cmd);
foreach (DataRow row in dt.Rows)
{
list.Add(this.DataRowToBean(row));
}
return list.AsQueryable();
}
public IQueryable<Role> GetAll()
{
IList<Role> list=new List<Role>();
DBHelper db = new DBHelper();
DbCommand cmd = db.GetSqlStringCommand("select * from Role");
DataTable dt = db.ExecuteDataTable(cmd);
foreach (DataRow dataRow in dt.Rows)
{
list.Add(this.DataRowToBean(dataRow));
}
return list.AsQueryable();
}
public Role GetById(object id)
{
DBHelper db = new DBHelper();
DbCommand cmd = db.GetSqlStringCommand("select * from Role where Role_Code='"+id.ToString()+"'");
DataTable dt = db.ExecuteDataTable(cmd);
foreach (DataRow dataRow in dt.Rows)
{
return this.DataRowToBean(dataRow);
}
return null;
}
public int Delete(UserInfo t)
{
string sql = "delete from [UserInfo] where UserID='" + t.UserID + "'";
DBHelper db = new DBHelper();
DbCommand cmd = db.GetSqlStringCommand(sql);
return db.ExecuteNonQuery(cmd);
}
public UserInfo SelectUserByLogID(string logID)
{
UserInfo user = new UserInfo();
//user.UserRoleses=new List<UserRoles>();
String sql = "select * from [UserInfo] u left join UserRoles ur on u.UserID=ur.UID where u.LoginID='" +
logID + "'";
DBHelper db = new DBHelper();
DbCommand cmd = db.GetSqlStringCommand(sql);
DataTable dt = db.ExecuteDataTable(cmd);
int i = 0;
foreach (DataRow row in dt.Rows)
{
if (i == 0)
{
user = this.DataRowToBean(row);
user.UserRoleses = new List<UserRoles>();
user.UserRoleses.Add(this.DataRowToUserRoleBean(row));
}
else
{
user.UserRoleses.Add(this.DataRowToUserRoleBean(row));
}
i = 1;
return user;
}
return null;
}
private long SelectUserPageCount(string sqlWhere)
{
DBHelper db = new DBHelper();
DbCommand cmd = db.GetSqlStringCommand("select count(*) from [UserInfo] where 1=1 " + sqlWhere);
return Convert.ToInt32(db.ExecuteScalar(cmd));
}
public int SelectRolePageCount(string searchCondition)
{
DBHelper db = new DBHelper();
DbCommand cmd = db.GetSqlStringCommand("select count(*) from Role where 1=1 " + searchCondition);
return Convert.ToInt32(db.ExecuteScalar(cmd));
}
public Role SelectRole(string Role_Code)
{
DBHelper db = new DBHelper();
DbCommand cmd = db.GetSqlStringCommand("SELECT * FROM Role WHERE Role_Code = " + Role_Code);
DataTable dt = db.ExecuteDataTable(cmd);
foreach (DataRow row in dt.Rows)
{
return this.DataRowToBean(row);
}
return null;
}
public PageBean<UserInfo> GetPageList(int pageIndex, int pageSize, string sqlWhere)
{
PageBean<UserInfo> pageBean = new PageBean<UserInfo>();
IList<UserInfo> list = new List<UserInfo>();
DBHelper db = new DBHelper();
string strSql =
"select * from (select * ,ROW_NUMBER() over(order by UserName) num from [UserInfo] where 1=1 " +
sqlWhere + ") Tabl where num BETWEEN " + (pageIndex - 1)*pageSize + " and " + (pageSize)*(pageIndex);
DbCommand cmd = db.GetSqlStringCommand(strSql);
DataTable dt = db.ExecuteDataTable(cmd);
foreach (DataRow row in dt.Rows)
{
UserInfo ht = this.DataRowToBean(row);
list.Add(ht);
}
pageBean.rows = list;
pageBean.total = this.SelectUserPageCount(sqlWhere);
return pageBean;
}