/// <summary>
/// 获得指定条件分页数据
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="model">表对应的模型对相</param>
/// <param name="target">条件</param>
/// <param name="pageSize">页面大小</param>
/// <param name="currentPage">页码</param>
/// <param name="orderField">排序字段</param>
/// <param name="orderValue">排序方式</param>
/// <returns></returns>
public static DataTable GetSome <T>(T model, string[] targets, int pageSize, int currentPage, string orderField, string orderValue)
{
Utility.SQLHelper db = new Utility.SQLHelper();
Type type = model.GetType();
string selectStr = "";
PropertyInfo[] propArray = type.GetProperties();
List <SqlParameter> paramslist = new List <SqlParameter>();
HashSet <string> HStargets = new HashSet <string>(targets);
foreach (PropertyInfo pi in propArray)
{
if (!HStargets.Contains(pi.Name) || pi.GetValue(model, null) == null)
{
continue;
}
selectStr += "[" + pi.Name + "]=@" + pi.Name + " and ";
paramslist.Add(new SqlParameter(pi.Name, pi.GetValue(model, null)));
}
selectStr = selectStr.Substring(0, selectStr.LastIndexOf(" and ", StringComparison.Ordinal));
SqlParameter[] parameters = paramslist.ToArray();
string sql = "select top {0} * from {1} where {2} and id not in(select top {3} id from {4} where {5} order by {6} {7}) order by {8} {9} ";
string tableName = Utility.Tool.ModelNameToTableName(type.Name);
sql = String.Format(sql, pageSize, tableName, selectStr,
pageSize * (currentPage - 1), tableName, selectStr, orderField, orderValue, orderField, orderValue);
return(db.ExecuteQuery(sql, parameters, CommandType.Text));
}
/// <summary>
/// 获得指定条件数据总行数
/// </summary>
public static double GetCount <T>(T model, string target)
{
Utility.SQLHelper db = new Utility.SQLHelper();
Type type = model.GetType();
string selectStr = "";
PropertyInfo[] propArray = type.GetProperties();
List <SqlParameter> paramslist = new List <SqlParameter>();
foreach (PropertyInfo pi in propArray)
{
if (pi.Name != target || pi.GetValue(model, null) == null)
{
continue;
}
selectStr += "[" + pi.Name + "]=@" + pi.Name + " and ";
paramslist.Add(new SqlParameter(pi.Name, pi.GetValue(model, null)));
}
selectStr = selectStr.Substring(0, selectStr.LastIndexOf(" and ", StringComparison.Ordinal));
string sql = String.Format("select count(*) from {0} where {1}", Utility.Tool.ModelNameToTableName(type.Name), selectStr);
SqlParameter[] parameters = paramslist.ToArray();
DataTable dt = db.ExecuteQuery(sql, parameters, CommandType.Text);
if (dt.Rows.Count > 0)
{
return(Convert.ToInt32(dt.Rows[0][0]));
}
return(0);
}
public static IList <int> GetList()
{
SQLHelper sqlLocal = new Utility.SQLHelper("");
string sql = "SELECT [Name] FROM [AuthCenter].[dbo].[Sys_User] AS A WITH(NOLOCK) WHERE A.RF = 'A' AND A.[Status] = 'A' ";
DataSet ds = sqlLocal.Query(sql);
if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
int count = ds.Tables[0].Rows.Count;
IList <int> list = new List <int>(count);
for (int i = 0; i < count; i++)
{
int port = 3000 + int.Parse(ds.Tables[0].Rows[i][0].ToString());
list.Add(port);
}
return(list);
}
return(new List <int>(0));
}
/// <summary>
/// 选择一系列数据
/// </summary>
/// <typeparam name="T">数据库模型</typeparam>
/// <param name="model">模型实例对象</param>
/// <param name="targets">数据查询条件</param>
/// <returns>DataTable数据表</returns>
public static DataTable GetList <T>(T model, string[] targets)
{
Utility.SQLHelper db = new Utility.SQLHelper();
Type type = model.GetType();
string selectStr = "";
PropertyInfo[] propArray = type.GetProperties();
List <SqlParameter> paramslist = new List <SqlParameter>();
HashSet <string> HStargets = new HashSet <string>(targets);
foreach (PropertyInfo pi in propArray)
{
if (!HStargets.Contains(pi.Name) || pi.GetValue(model, null) == null)
{
continue;
}
selectStr += "[" + pi.Name + "]=@" + pi.Name + " and ";
paramslist.Add(new SqlParameter(pi.Name, pi.GetValue(model, null)));
}
selectStr = selectStr.Substring(0, selectStr.LastIndexOf(" and ", StringComparison.Ordinal));
string sql = string.Format("select * from {0} where {1}", Utility.Tool.ModelNameToTableName(type.Name), selectStr);
SqlParameter[] parameters = paramslist.ToArray();
return(db.ExecuteQuery(sql, parameters, System.Data.CommandType.Text));
}
/// <summary>
/// 查询所有数据
/// </summary>
/// <param name="tableName">表名</param>
/// <returns></returns>
public static DataTable GetAll(string tableName)
{
string sql = String.Format("select * from {0}", tableName);
Utility.SQLHelper db = new Utility.SQLHelper();
return(db.ExecuteQuery(sql, CommandType.Text));
}
/// <summary>
/// 更新一系列数据
/// </summary>
/// <typeparam name="T">数据库模型</typeparam>
/// <param name="model">模型实例对象</param>
/// <param name="target">数据查询条件</param>
/// <returns>影响行数</returns>
public static int ChangeSome <T>(T model, string target)
{
Utility.SQLHelper db = new Utility.SQLHelper();
Type type = model.GetType();
string updateStr1 = "";
string updateStr2 = "";
PropertyInfo[] propArray = type.GetProperties();
PropertyInfo lastPi = type.GetProperty(target);
List <SqlParameter> paramslist = new List <SqlParameter>();
foreach (PropertyInfo pi in propArray)
{
if ((String.Compare(pi.Name, target) == 0) || (String.Compare(pi.Name, "ID", true) == 0) || pi.GetValue(model, null) == null)
{
continue;
}
updateStr1 += "[" + pi.Name + "]=@" + pi.Name + ",";
paramslist.Add(new SqlParameter(pi.Name, pi.GetValue(model, null)));
}
updateStr1 = updateStr1.TrimEnd(',');
updateStr2 = "[" + target + "]=@" + target;
paramslist.Add(new SqlParameter(target, lastPi.GetValue(model, null)));
string sql = string.Format("update {0} set {1} where {2}", Utility.Tool.ModelNameToTableName(type.Name), updateStr1, updateStr2);
SqlParameter[] parameters = paramslist.ToArray();
return(db.ExecuteNonQuery(sql, parameters, System.Data.CommandType.Text));
}
public static int Insert(Models.DB.User user)
{
string sql = string.Format("insert into Tb_User(Name,Password,RoleID,Enable) values(@Name,@Password,@RoleID,@Enable);select @id=SCOPE_IDENTITY()");
SqlParameter[] parameters =
{
new SqlParameter("Name", SqlDbType.NVarChar, 255),
new SqlParameter("Password", user.Password),
new SqlParameter("RoleID", SqlDbType.Int),
new SqlParameter("Enable", SqlDbType.Bit),
new SqlParameter("id", SqlDbType.Int)
};
parameters[0].Value = user.Name;
parameters[1].Value = user.Password;
parameters[2].Value = user.RoleId;
parameters[3].Value = user.Enable;
parameters[4].Direction = ParameterDirection.Output;
// int temp = Convert.ToInt32(parameters[4].Value);
Utility.SQLHelper db = new Utility.SQLHelper();
db.ExecuteNonQuery(sql, parameters, CommandType.Text);
int id = Convert.ToInt32(parameters[4].Value);
return(id);
}
public static int CreateOne <T>(T models)
{
Type type = models.GetType();
string insertStr1 = "";
string insertStr2 = "";
PropertyInfo[] propArray = type.GetProperties();
List <SqlParameter> paramslist = new List <SqlParameter>();
foreach (PropertyInfo pi in propArray)
{
if (String.Compare(pi.Name, "ID", true) == 0 || pi.GetValue(models, null) == null)
{
continue;
}
insertStr1 += "[" + pi.Name + "],";
insertStr2 += "@" + pi.Name + ",";
paramslist.Add(new SqlParameter(pi.Name, pi.GetValue(models, null)));
}
insertStr1 = insertStr1.TrimEnd(',');
insertStr2 = insertStr2.TrimEnd(',');
String tableName = "Tb_" + type.Name;
string sql = string.Format("insert into {0}({1}) values({2})", tableName, insertStr1, insertStr2);
SqlParameter[] parameters = paramslist.ToArray();
Utility.SQLHelper db = new Utility.SQLHelper();
return(db.ExecuteNonQuery(sql, parameters, System.Data.CommandType.Text));
}
/// <summary>
/// 查询指定表的分页数据
/// </summary>
/// <param name="tableName">表名</param>
/// <param name="pageSize">页大小</param>
/// <param name="currentPage">当前页号</param>
/// <param name="orderField">排序字段</param>
/// <param name="orderValue">排序方式</param>
/// <returns></returns>
public static DataTable GetSome(string tableName, int pageSize, int currentPage, string orderField, string orderValue)
{
string sql = "select top {0} * from(select row_number() over (order by {1} {2}) as RID, * from {3}) A where RID>{4}";
sql = String.Format(sql, pageSize, orderField, orderValue, tableName, pageSize * (currentPage - 1));
Utility.SQLHelper db = new Utility.SQLHelper();
return(db.ExecuteQuery(sql, CommandType.Text));
}
/// <summary>
/// 根据Int型数据选择列表
/// </summary>
/// <param name="UserID"></param>
/// <returns></returns>
public static System.Data.DataTable QueryOne <T>(T value, String TableName, String ValueName)
{
string sql = String.Format("SELECT * From {0} WHERE {1}=@{2}", TableName, ValueName, ValueName);
SqlParameter[] parameters =
{
new SqlParameter("@" + ValueName, value)
};
Utility.SQLHelper db = new Utility.SQLHelper();
return(db.ExecuteQuery(sql, parameters, CommandType.Text));
}
public static DataTable SearchByCollege(String TableName, int pageSize, int currentPage, String orderField, String orderValue, String College)
{
Utility.SQLHelper db = new Utility.SQLHelper();
String sql = String.Format("select top {0} * From(select row_number() over (order by {1} {2}) as RID, * from {3} where ([College]=@College)) A where RID>{4}", pageSize, orderField, orderValue, TableName, pageSize * (currentPage - 1));
SqlParameter[] parameters =
{
new SqlParameter("@College", College)
};
return(db.ExecuteQuery(sql, parameters, CommandType.Text));
}
public static DataTable CountByEnableAndCollege(Boolean enable, String College)
{
Utility.SQLHelper db = new Utility.SQLHelper();
String sql = "select count(*) from Tb_User,Tb_StudentInfoModel where Tb_User.ID=Tb_StudentInfoModel.UserId and Tb_User.Enable=@Enable and Tb_StudentInfoModel.College=@College";
SqlParameter[] parameters =
{
new SqlParameter("@Enable", enable),
new SqlParameter("@College", College)
};
return(db.ExecuteQuery(sql, parameters, CommandType.Text));
}
public static DataTable SelectByEnableAndCollege(int pageSize, int currentPage, String orderField, String orderValue, String College, Boolean Enable)
{
Utility.SQLHelper db = new Utility.SQLHelper();
String sql = String.Format("select top {0} * from(select row_number() over (order by {1} {2}) as RID,Tb_StudentInfoModel.* from Tb_User,Tb_StudentInfoModel where (Tb_User.ID=Tb_StudentInfoModel.UserId and Tb_User.Enable=@Enable and Tb_StudentInfoModel.College=@College)) A where RID>{3}", pageSize, orderField, orderValue, pageSize * (currentPage - 1));
SqlParameter[] parameters =
{
new SqlParameter("@Enable", Enable),
new SqlParameter("@College", College)
};
return(db.ExecuteQuery(sql, parameters, CommandType.Text));
}
public static DataTable FindProjectScore(int ProjectID, int MatchModelID)
{
Utility.SQLHelper db = new Utility.SQLHelper();
String sql = String.Format("Select * From Tb_ProjectScore Where ProjectID=@ProjectID And MatchModelID=@MatchModelID");
SqlParameter[] parameters =
{
new SqlParameter("@ProjectID", ProjectID),
new SqlParameter("@MatchModelID", MatchModelID)
};
return(db.ExecuteQuery(sql, parameters, CommandType.Text));
}
public static DataTable FindJudgeMatch(int JudgeID, DateTime Now)
{
Utility.SQLHelper db = new Utility.SQLHelper();
String sql = String.Format("Select * From (Tb_JudgeInfoModel INNER JOIN Tb_MatchJuryRelation ON Tb_JudgeInfoModel.Id=Tb_MatchJuryRelation.JudgeID INNER JOIN Tb_Match ON Tb_MatchJuryRelation.MatchID=Tb_Match.ID) where (Tb_JudgeInfoModel.Id=@JudgeID And datediff(d,Tb_Match.DeadLine,@DateTime)<0)");
SqlParameter[] parameters =
{
new SqlParameter("@JudgeID", JudgeID),
new SqlParameter("@DateTime", Now)
};
return(db.ExecuteQuery(sql, parameters, CommandType.Text));
}
public static DataTable QueryOne(String UserName)
{
string sql = "select * from Tb_User where Name=@Name";
SqlParameter[] parameters =
{
new SqlParameter("@Name", UserName)
};
Utility.SQLHelper db = new Utility.SQLHelper();
return(db.ExecuteQuery(sql, parameters, CommandType.Text));
}
public static int Work(string tableName, int id)
{
Utility.SQLHelper db = new Utility.SQLHelper();
string sql = string.Format("delete from {0} where ID=@ID", tableName);
SqlParameter[] parameters =
{
new SqlParameter("ID", id)
};
return(db.ExecuteNonQuery(sql, parameters, System.Data.CommandType.Text));
}
public static double Count(String TableName)
{
Utility.SQLHelper db = new Utility.SQLHelper();
string sql = string.Format("select count(*) from {0}", TableName);
DataTable dt = db.ExecuteQuery(sql, CommandType.Text);
if (dt.Rows.Count > 0)
{
return(Convert.ToInt32(dt.Rows[0][0]));
}
return(0);
}
public static int UpdatePaperUrl(int ID, String Url)
{
string sql = String.Format("update Tb_CupProjectModel set [PaperDoc] = @PaperDoc where [ID]=@ID");
SqlParameter[] parameters =
{
new SqlParameter("@PaperDoc", Url),
new SqlParameter("@ID", ID)
};
Utility.SQLHelper db = new Utility.SQLHelper();
return(db.ExecuteNonQuery(sql, parameters, System.Data.CommandType.Text));
}
public static int UpdateRemark(int ID, String Remark, String TableName)
{
string sql = String.Format("update {0} set [Remark] = @Remark where [ID]=@ID", TableName);
SqlParameter[] parameters =
{
new SqlParameter("@Remark", Remark),
new SqlParameter("@ID", ID)
};
Utility.SQLHelper db = new Utility.SQLHelper();
return(db.ExecuteNonQuery(sql, parameters, System.Data.CommandType.Text));
}
public static DataTable FindCheckRecord(int ProjectID, int MatchModelID)
{
Utility.SQLHelper db = new Utility.SQLHelper();
String sql = String.Format("select * from Tb_CheckRecord where [ProjectID]=@ProjectID AND [MatchModelID]=@MatchModelID");
SqlParameter[] parameters =
{
new SqlParameter("@ProjectID", ProjectID),
new SqlParameter("@MatchModelID", MatchModelID)
};
return(db.ExecuteQuery(sql, parameters, CommandType.Text));
}
public static DataTable QueryOne(String RoleName)
{
string sql = "select * from Tb_Role where Name=@Name";
SqlParameter[] parameters =
{
new SqlParameter("Name", SqlDbType.NVarChar, 255)
};
parameters[0].Value = RoleName;
Utility.SQLHelper db = new Utility.SQLHelper();
return(db.ExecuteQuery(sql, parameters, CommandType.Text));
}
/// <summary>
/// 多条件分页查询
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="model"></param>
/// <param name="targets"></param>
/// <param name="pageSize"></param>
/// <param name="currentPage"></param>
/// <returns></returns>
public static DataTable MatchSearchByStudent(String tableName, int pageSize, int currentPage, String orderField, String orderValue, String CollegeScau, String CollegeStudent, String Status)
{
Utility.SQLHelper db = new Utility.SQLHelper();
String sql = String.Format("select top {0} * From(select row_number() over (order by {1} {2}) as RID, * from {3} where ([College]=@CollegeScau OR [College]=@CollegeStudent) AND [Status]=@Status ) A where RID>{4}", pageSize, orderField, orderValue, tableName, pageSize * (currentPage - 1));
SqlParameter[] parameters =
{
new SqlParameter("@CollegeScau", CollegeScau),
new SqlParameter("@CollegeStudent", CollegeStudent),
new SqlParameter("@Status", Status)
};
return(db.ExecuteQuery(sql, parameters, CommandType.Text));
}
public static int UpdateProjectStatus(String Status, String TableName, int ID)
{
Utility.SQLHelper db = new Utility.SQLHelper();
String sql = String.Format("Update {0} set [Statu]=@Statu,[DeclarationDate]=@DeclarationDate where [ID]=@ID", TableName);
SqlParameter[] parameters =
{
new SqlParameter("@Statu", Status),
new SqlParameter("@DeclarationDate", System.DateTime.Now),
new SqlParameter("@ID", ID)
};
return(db.ExecuteNonQuery(sql, parameters, System.Data.CommandType.Text));
}
public static DataTable MatchCountByStudent(String CollegeScau, String CollegeStudent, String Status)
{
Utility.SQLHelper db = new Utility.SQLHelper();
String sql = String.Format("select * from Tb_Match where ([College]=@CollegeScau OR [College]=@CollegeStudent) AND [Status]=@Status");
SqlParameter[] parameters =
{
new SqlParameter("@CollegeScau", CollegeScau),
new SqlParameter("@CollegeStudent", CollegeStudent),
new SqlParameter("@Status", Status)
};
return(db.ExecuteQuery(sql, parameters, CommandType.Text));
}
/// <summary>
/// 更新数据库的值
/// </summary>
/// <param name="UniqueID"></param>
/// <returns></returns>
public static int UpdateUnique(int UniqueID)
{
SQLHelper sqlLocal = new Utility.SQLHelper("");
string sql = string.Format("UPDATE [{0}].[dbo].[UNIQUE] SET UniqueID = @UniqueID", "");
SqlParameter[] sqlParams = new SqlParameter[1] {
new SqlParameter("@UniqueID", UniqueID)
};
int reflect = sqlLocal.ExecuteSql(sql, sqlParams);
return(reflect);
}
/// <summary>
/// 获取用户权限
/// </summary>
/// <param name="sysno"></param>
/// <param name="appid"></param>
/// <returns></returns>
public static DataSet GetList(int sysno, string appid)
{
SQLHelper sqlLocal = new Utility.SQLHelper("");
SqlParameter[] sqlParams = new SqlParameter[2]
{
new SqlParameter("@USERID", sysno),
new SqlParameter("@ApplicationID", appid)
};
//A.Description,A.Name AS Category,C.Name AS [Page],C.[Key]
DataSet ds = sqlLocal.QueryTransaction("P_RIGHT", sqlParams);
return(ds);
}
/// <summary>
/// 取唯一值
/// </summary>
/// <returns></returns>
public static int GetUnique()
{
SQLHelper sqlLocal = new Utility.SQLHelper("");
string sql = string.Format("SELECT TOP(1) [UniqueID] FROM [{0}].[dbo].[UNIQUE] AS A WITH(NOLOCK)", "");
object result = sqlLocal.GetSingle(sql);
if (result == null)
{
return(0);
}
return(int.Parse(result.ToString()));
}
public static Double CountByCollege(String College, String TableName)
{
Utility.SQLHelper db = new Utility.SQLHelper();
String sql = String.Format("select count(*) from {0} where [College]=@College", TableName);
SqlParameter[] parameters =
{
new SqlParameter("@College", College)
};
DataTable dt = db.ExecuteQuery(sql, parameters, CommandType.Text);
if (dt.Rows.Count > 0)
{
return(Convert.ToInt32(dt.Rows[0][0]));
}
return(0);
}
/// <summary>
/// 获得指定条件数据总行数(双表)
/// </summary>
public static double GetCount <T, K>(T modelT, K modelK, string linkStr, string target)
{
Utility.SQLHelper db = new Utility.SQLHelper();
Type typeT = modelT.GetType();
Type typeK = modelK.GetType();
string selectStr = "";
PropertyInfo[] propArrayT = typeT.GetProperties();
PropertyInfo[] propArrayK = typeK.GetProperties();
List <SqlParameter> paramslist = new List <SqlParameter>();
foreach (PropertyInfo pi in propArrayT)
{
if (pi.Name != target || pi.Name == linkStr || pi.GetValue(modelT, null) == null)
{
continue;
}
selectStr += "[" + pi.Name + "]=@" + pi.Name + " and ";
paramslist.Add(new SqlParameter(pi.Name, pi.GetValue(modelT, null)));
}
foreach (PropertyInfo pi in propArrayK)
{
if (pi.Name != target || pi.Name == linkStr || pi.GetValue(modelK, null) == null)
{
continue;
}
selectStr += "[" + pi.Name + "]=@" + pi.Name + " and ";
paramslist.Add(new SqlParameter(pi.Name, pi.GetValue(modelK, null)));
}
selectStr += linkStr;
string TtableName = Utility.Tool.ModelNameToTableName(typeT.Name);
string KtableName = Utility.Tool.ModelNameToTableName(typeK.Name);
string sql = String.Format("select count(*) from {0} where {1}", TtableName + "," + KtableName, selectStr);
SqlParameter[] parameters = paramslist.ToArray();
DataTable dt = db.ExecuteQuery(sql, parameters, CommandType.Text);
if (dt.Rows.Count > 0)
{
return(Convert.ToInt32(dt.Rows[0][0]));
}
return(0);
}
