/// <summary>
/// 更新一条数据
/// </summary>
public bool Update(TF.RunSafty.Entry.nav model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("update Tab_Nav set ");
strSql.Append("typeName=@typeName,");
strSql.Append("NavUrl=@NavUrl,");
strSql.Append("StrSort=@StrSort,");
strSql.Append("TrNavUrl=@TrNavUrl");
strSql.Append(" where TypeID=@TypeID");
SqlParameter[] parameters =
{
new SqlParameter("@typeName", SqlDbType.NVarChar, 50),
new SqlParameter("@NavUrl", SqlDbType.NVarChar, 100),
new SqlParameter("@TypeID", SqlDbType.VarChar, 50),
new SqlParameter("@StrSort", SqlDbType.Int, 4),
new SqlParameter("@TrNavUrl", SqlDbType.NVarChar, 100)
};
parameters[0].Value = model.typeName;
parameters[1].Value = model.NavUrl;
parameters[2].Value = model.TypeID;
parameters[3].Value = model.StrSort;
parameters[4].Value = model.TrNavUrl;
int rows = SqlHelps.ExecuteNonQuery(ConnectionString, CommandType.Text, strSql.ToString(), parameters);
if (rows > 0)
{
return(true);
}
else
{
return(false);
}
}
/// <summary>
///获得数据列表
/// </summary>
/// <param name="pageIndex"></param>
/// <param name="pageCount"></param>
/// <returns></returns>
public DataTable QueryCheckDiary(int pageIndex, int pageCount, string Begintime, string EndTimeStr, string UsersId)
{
StringBuilder strSqlWhere = new StringBuilder();
strSqlWhere.Append(" and 1=1");
if (Begintime != "" && Begintime != null)
{
strSqlWhere.Append(" and convert(varchar, addtime, 23)>='" + Begintime + "'");
}
if (EndTimeStr != "" && EndTimeStr != null)
{
strSqlWhere.Append(" and convert(varchar, addtime, 23)<='" + EndTimeStr + "'");
}
if (UsersId != "" && UsersId != null)
{
strSqlWhere.Append(" and UserId='" + UsersId + "'");
}
string strSql = @"select top " + pageCount.ToString() + " * from VIEW_Diary where Id not in (select top " + ((pageIndex - 1) * pageCount).ToString() + @" Id from Tab_Diary where 1=1" + strSqlWhere.ToString() + " order by addtime desc)" + strSqlWhere.ToString() + " order by addtime desc";
return(SqlHelps.ReturnDataSet(ConnectionString, CommandType.Text, strSql).Tables[0]);
}
/// <summary>
/// 删除项目
/// </summary>
/// <param name="nid"></param>
/// <returns></returns>
public int Delete(string id)
{
string strSql = "delete Tab_Diary where Id= @id";
SqlParameter[] sqlParameter =
{
new SqlParameter("id", id)
};
return(SqlHelps.ExecuteNonQuery(ConnectionString, CommandType.Text, strSql, sqlParameter));
}
/// <summary>
/// 删除角色
/// </summary>
/// <param name="nid"></param>
/// <returns></returns>
public int Delete(int id)
{
string strSql = "delete tab_Roles where Id= @id";
SqlParameter[] sqlParameter =
{
new SqlParameter("id", id)
};
return(SqlHelps.ExecuteNonQuery(SqlHelps.SQLConnString, CommandType.Text, strSql, sqlParameter));
}
/// <summary>
/// 修改用户名称
/// </summary>
/// <returns></returns>
public bool UpdatePassWord(TF.RunSafty.Entry.Users model)
{
string strSql = @"update Tab_User set passWord=@passWord where Id=@id";
SqlParameter[] sqlParameter =
{
new SqlParameter("id", model.id),
new SqlParameter("@passWord", model.passWord)
};
return(SqlHelps.ExecuteNonQuery(ConnectionString, CommandType.Text, strSql, sqlParameter) > 0);
}
/// <summary>
/// 修改角色名称
/// </summary>
/// <returns></returns>
public bool UpdatePowers(TF.RunSafty.Entry.Roles model)
{
string strSql = @"update tab_Roles set RolesPowers=@RolesPowers where Id=@id";
SqlParameter[] sqlParameter =
{
new SqlParameter("id", model.Id),
new SqlParameter("RolesPowers", model.RolesPowers)
};
return(SqlHelps.ExecuteNonQuery(SqlHelps.SQLConnString, CommandType.Text, strSql, sqlParameter) > 0);
}
/// <summary>
/// 看是否已经存在该typeid的类别
/// </summary>
/// <param name="typeID"></param>
/// <returns></returns>
public bool IfExists(string typeID)
{
string strSql = "select TypeName from Tab_Nav where TypeParentID=@typeID";
SqlParameter param = new SqlParameter("@typeID", typeID);
object obj = SqlHelps.ExecuteScalar(ConnectionString, CommandType.Text, strSql, param);
if (obj != null && obj.ToString() != "")
{
return(true);
}
return(false);
}
/// <summary>
/// 更新一条数据
/// </summary>
public int Update(TF.RunSafty.Entry.Diary model)
{
string strSql = @"update Tab_Diary set AddTime=@AddTime,DiaryContent=@DiaryContent,UserId=@UserId where Id=@id";
SqlParameter[] sqlParameter =
{
new SqlParameter("id", model.Id),
new SqlParameter("AddTime", model.AddTime),
new SqlParameter("DiaryContent", model.DiaryContent),
new SqlParameter("UserId", model.UserId)
};
return(SqlHelps.ExecuteNonQuery(ConnectionString, CommandType.Text, strSql, sqlParameter));
}
/// <summary>
///查询出所有数据
/// </summary>
/// <param name="pageIndex"></param>
/// <param name="pageCount"></param>
/// <returns></returns>
public DataTable QueryCheckRoles(int pageIndex, int pageCount, string RolesName)
{
StringBuilder strSqlWhere = new StringBuilder();
strSqlWhere.Append(" and 1=1");
if (!string.IsNullOrEmpty(RolesName))
{
strSqlWhere.Append(" and RolesName like '%" + RolesName + "%'");
}
string strSql = @"select top " + pageCount.ToString() + " * from tab_Roles where Id not in (select top " + ((pageIndex - 1) * pageCount).ToString() + @" Id from tab_Roles where 1=1" + strSqlWhere.ToString() + " order by id desc)" + strSqlWhere.ToString() + " order by id desc";
return(SqlHelps.ReturnDataSet(ConnectionString, CommandType.Text, strSql).Tables[0]);
}
/// <summary>
/// 修改用户名称
/// </summary>
/// <returns></returns>
public bool Update(TF.RunSafty.Entry.Users model)
{
string strSql = @"update Tab_User set userNumber=@userNumber,userName=@userName,roseId=@roseId where Id=@id";
SqlParameter[] sqlParameter =
{
new SqlParameter("id", model.id),
new SqlParameter("@userNumber", model.userNumber),
new SqlParameter("@userName", model.userName),
new SqlParameter("@roseId", model.roseId)
};
return(SqlHelps.ExecuteNonQuery(ConnectionString, CommandType.Text, strSql, sqlParameter) > 0);
}
/// <summary>
/// 获取导航内容
/// </summary>
public DataTable GetListForNav(string strWhere)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select *");
strSql.Append(" FROM Tab_Nav ");
if (strWhere.Trim() != "")
{
strSql.Append(" where " + strWhere);
}
strSql.Append(" order by StrSort asc");
return(SqlHelps.ReturnDataSet(ConnectionString, CommandType.Text, strSql.ToString()).Tables[0]);
}
/// <summary>
/// 查询个数
/// </summary>
/// <returns></returns>
public int QueryCheckRolesCount(string RolesName)
{
StringBuilder strSqlWhere = new StringBuilder();
strSqlWhere.Append(" 1=1");
if (string.IsNullOrEmpty(RolesName))
{
strSqlWhere.Append(" and RolesName like '%" + RolesName + "%'");
}
string strSql = "select count(*) from tab_Roles where" + strSqlWhere.ToString() + "";
return(Convert.ToInt32(SqlHelps.ExecuteScalar(ConnectionString, CommandType.Text, strSql)));
}
/// <summary>
/// 查询个数
/// </summary>
/// <returns></returns>
public int IsExistCheckRoles(string userNumber)
{
StringBuilder strSqlWhere = new StringBuilder();
strSqlWhere.Append(" 1=1");
if (!string.IsNullOrEmpty(userNumber))
{
strSqlWhere.Append(" and userNumber = '" + userNumber + "'");
}
string strSql = "select count(*) from Tab_User where" + strSqlWhere.ToString() + "";
return(Convert.ToInt32(SqlHelps.ExecuteScalar(ConnectionString, CommandType.Text, strSql)));
}
/// <summary>
/// 查询个数
/// </summary>
/// <returns></returns>
public int QueryCheckUsersCount(int id)
{
StringBuilder strSqlWhere = new StringBuilder();
strSqlWhere.Append(" 1=1");
if (id != null && id != 0)
{
strSqlWhere.Append(" and roseId = " + id);
}
string strSql = "select count(*) from Tab_User where" + strSqlWhere.ToString();
return(Convert.ToInt32(SqlHelps.ExecuteScalar(ConnectionString, CommandType.Text, strSql)));
}
/// <summary>
/// 登录
/// </summary>
/// <param name="name"></param>
/// <param name="pwd"></param>
/// <returns></returns>
public DataTable login(string userNumber, string Password)
{
string sql = " where";
sql += " userNumber=@userNumber and passWord=@passWord";
string strSql = "select * from Tab_User " + sql;
SqlParameter[] sqlParams =
{
new SqlParameter("userNumber", userNumber),
new SqlParameter("passWord", Password)
};
return(SqlHelps.ReturnDataSet(ConnectionString, CommandType.Text, strSql, sqlParams).Tables[0]);
}
/// <summary>
/// 查询个数
/// </summary>
/// <returns></returns>
public int IsExistCheckRoles(string RolesName)
{
StringBuilder strSqlWhere = new StringBuilder();
strSqlWhere.Append(" 1=1");
if (!string.IsNullOrEmpty(RolesName))
{
strSqlWhere.Append(" and RolesName = '" + RolesName + "'");
}
string strSql = "select count(*) from tab_Roles where" + strSqlWhere.ToString() + "";
return(Convert.ToInt32(SqlHelps.ExecuteScalar(SqlHelps.SQLConnString, CommandType.Text, strSql)));
}
/// <summary>
///查询出所有数据
/// </summary>
/// <param name="pageIndex"></param>
/// <param name="pageCount"></param>
/// <returns></returns>
public DataTable getAllName(string name)
{
StringBuilder strSqlWhere = new StringBuilder();
strSqlWhere.Append(" 1=1");
if (!string.IsNullOrEmpty(name))
{
strSqlWhere.Append(" and userName like '%" + name + "%'");
}
string strSql = "select top 10 * from Tab_User where " + strSqlWhere.ToString();
return(SqlHelps.ReturnDataSet(ConnectionString, CommandType.Text, strSql).Tables[0]);
}
/// <summary>
///查询出所有数据
/// </summary>
/// <param name="pageIndex"></param>
/// <param name="pageCount"></param>
/// <returns></returns>
public DataTable getAllNameByuserNumber(string Number)
{
StringBuilder strSqlWhere = new StringBuilder();
strSqlWhere.Append(" 1=1");
if (!string.IsNullOrEmpty(Number))
{
strSqlWhere.Append(" and userNumber = '" + Number + "'");
}
string strSql = "select top 1 * from Tab_User where " + strSqlWhere.ToString();
return(SqlHelps.ReturnDataSet(ConnectionString, CommandType.Text, strSql).Tables[0]);
}
/// <summary>
/// 得到一个对象实体
/// </summary>
public TF.RunSafty.Entry.Roles GetModel(int Id)
{
string strSql = "select * from tab_Roles where Id='" + Id + "'";
DataSet ds = SqlHelps.ReturnDataSet(SqlHelps.SQLConnString, CommandType.Text, strSql);
TF.RunSafty.Entry.Roles rl = new TF.RunSafty.Entry.Roles();
if (ds.Tables[0].Rows.Count > 0)
{
return(DataRowToModel(ds.Tables[0].Rows[0]));
}
else
{
return(null);
}
}
/// <summary>
/// 得到一个对象实体
/// </summary>
public TF.RunSafty.Entry.Diary GetModel(int Id)
{
string strSql = "select * from Tab_Diary where Id='" + Id + "'";
DataSet ds = SqlHelps.ReturnDataSet(ConnectionString, CommandType.Text, strSql);
TF.RunSafty.Entry.Diary model = new TF.RunSafty.Entry.Diary();
if (ds.Tables[0].Rows.Count > 0)
{
return(DataRowToModel(ds.Tables[0].Rows[0]));
}
else
{
return(null);
}
}
/// <summary>
/// 添加角色
/// </summary>
/// <param name="name"></param>
/// <returns></returns>
public int Add(TF.RunSafty.Entry.Roles model)
{
string strSql = @"insert into tab_Roles (RolesName) values
(@RolesName)";
SqlParameter[] sqlParameter =
{
new SqlParameter("RolesName", model.RolesName)
};
object obj = SqlHelps.ExecuteNonQuery(SqlHelps.SQLConnString, CommandType.Text, strSql, sqlParameter);
if (DBNull.Value.Equals(obj))
{
return(0);
}
return(Convert.ToInt32(obj));
}
/// <summary>
/// 增加一条数据
/// </summary>
public int Add(TF.RunSafty.Entry.Diary model)
{
string strSql = @"insert into Tab_Diary (AddTime,DiaryContent,UserId) values
(@AddTime,@DiaryContent,@UserId)";
SqlParameter[] sqlParameter =
{
new SqlParameter("AddTime", model.AddTime),
new SqlParameter("DiaryContent", model.DiaryContent),
new SqlParameter("UserId", model.UserId)
};
object obj = SqlHelps.ExecuteNonQuery(ConnectionString, CommandType.Text, strSql, sqlParameter);
if (DBNull.Value.Equals(obj))
{
return(0);
}
return(Convert.ToInt32(obj));
}
/// <summary>
/// 获得数据列表个数
/// </summary>
/// <returns></returns>
public int QueryCheckDiaryCount(string Begintime, string EndTimeStr)
{
StringBuilder strSqlWhere = new StringBuilder();
strSqlWhere.Append(" 1=1");
if (Begintime != "" && Begintime != null)
{
strSqlWhere.Append(" and convert(varchar, addtime, 23)>='" + Begintime + "'");
}
if (EndTimeStr != "" && EndTimeStr != null)
{
strSqlWhere.Append(" and convert(varchar, addtime, 23)<='" + EndTimeStr + "'");
}
string strSql = "select count(*) from VIEW_Diary where " + strSqlWhere + "";
return(Convert.ToInt32(SqlHelps.ExecuteScalar(ConnectionString, CommandType.Text, strSql)));
}
/// <summary>
/// 删除一条数据
/// </summary>
public bool Delete(string typeID)
{
//该表无主键信息，请自定义主键/条件字段
StringBuilder strSql = new StringBuilder();
strSql.Append("delete from Tab_Nav ");
strSql.Append(" where TypeID=@typeID");
SqlParameter param = new SqlParameter("@typeID", typeID);
int rows = SqlHelps.ExecuteNonQuery(ConnectionString, CommandType.Text, strSql.ToString(), param);
if (rows > 0)
{
return(true);
}
else
{
return(false);
}
}
/// <summary>
/// 添加用户
/// </summary>
/// <param name="name"></param>
/// <returns></returns>
public int Add(TF.RunSafty.Entry.Users model)
{
string strSql = @"insert into Tab_User (userNumber,userName,passWord,roseId) values
(@userNumber,@userName,@passWord,@roseId)";
SqlParameter[] sqlParameter =
{
new SqlParameter("@userNumber", model.userNumber),
new SqlParameter("@userName", model.userName),
new SqlParameter("@passWord", model.passWord),
new SqlParameter("@roseId", model.roseId)
};
object obj = SqlHelps.ExecuteNonQuery(ConnectionString, CommandType.Text, strSql, sqlParameter);
if (DBNull.Value.Equals(obj))
{
return(0);
}
return(Convert.ToInt32(obj));
}
/// <summary>
/// 得到一个对象实体
/// </summary>
public TF.RunSafty.Entry.nav GetModel()
{
//该表无主键信息，请自定义主键/条件字段
StringBuilder strSql = new StringBuilder();
strSql.Append("select top 1 TypeID,TypeParentID,typeName from Tab_Nav ");
strSql.Append(" where ");
SqlParameter[] parameters =
{
};
TF.RunSafty.Entry.nav model = new TF.RunSafty.Entry.nav();
DataSet ds = SqlHelps.ReturnDataSet(ConnectionString, CommandType.Text, 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(TF.RunSafty.Entry.nav model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("insert into Tab_Nav(");
strSql.Append("TypeID,TypeParentID,typeName,NavUrl,StrSort,TrNavUrl)");
strSql.Append(" values (");
strSql.Append("@TypeID,@TypeParentID,@typeName,@NavUrl,@StrSort,@TrNavUrl)");
SqlParameter[] parameters =
{
new SqlParameter("@TypeID", SqlDbType.VarChar, 50),
new SqlParameter("@TypeParentID", SqlDbType.VarChar, 50),
new SqlParameter("@typeName", SqlDbType.NVarChar, 50),
new SqlParameter("@NavUrl", SqlDbType.NVarChar, 100),
new SqlParameter("@StrSort", SqlDbType.Int, 4),
new SqlParameter("@TrNavUrl", SqlDbType.NVarChar, 100)
};
parameters[0].Value = model.TypeID;
parameters[1].Value = model.TypeParentID;
parameters[2].Value = model.typeName;
parameters[3].Value = model.NavUrl;
parameters[4].Value = model.StrSort;
parameters[5].Value = model.TrNavUrl;
int rows = SqlHelps.ExecuteNonQuery(ConnectionString, CommandType.Text, strSql.ToString(), parameters);
if (rows > 0)
{
return(true);
}
else
{
return(false);
}
}
/// <summary>
///通过时间查询出在这段时间内每一天的数量
/// </summary>
/// <param name="pageIndex"></param>
/// <param name="pageCount"></param>
/// <returns></returns>
public DataTable GetallCount(string beginTime, string endTime)
{
string strSql = "select convert(varchar(100), AddTime, 23) as strDate,count(*) as countNum from Tab_Diary where AddTime>='" + beginTime + "' and AddTime<='" + endTime + "' group by convert(varchar(100), AddTime, 23) order by strDate desc";
return(SqlHelps.ReturnDataSet(ConnectionString, CommandType.Text, strSql).Tables[0]);
}
/// <summary>
/// 获取那个最大的typeid
/// </summary>
/// <param name="typeID"></param>
/// <returns></returns>
public DataSet GetMaxId(string typeID)
{
string strSql = "select max(typeID) as maxId from Tab_Nav where TypeParentID='" + typeID + "'";
return(SqlHelps.ReturnDataSet(ConnectionString, CommandType.Text, strSql.ToString()));
}
/// <summary>
///查询出所有数据
/// </summary>
/// <param name="pageIndex"></param>
/// <param name="pageCount"></param>
/// <returns></returns>
public DataTable getAllRolesName()
{
string strSql = @"select id,RolesName from tab_Roles order by id desc";
return(SqlHelps.ReturnDataSet(SqlHelps.SQLConnString, CommandType.Text, strSql).Tables[0]);
}