/// <summary>
/// 以实体类作为查询条件获取符合条件的数据集，NULL值不作为查询条件
/// </summary>
public List <T_Task_History> Search(T_Task_History entity, int pageNum, int pageSize, String criteria, T_Task_History.Columns OrderBy, AscDesc AscDesc, out int totalRecords)
{
String sql = "";
//如果没有任何查询条件，则不开启模糊查询列以减少SQL消耗
if (String.IsNullOrWhiteSpace(criteria))
{
sql = this.PreparedTable(entity, false, false);
}
else
{
sql = this.PreparedTable(entity, true, false);
}
String tbl = "'(" + sql + ") as t'";
List <SqlParameter> pars = new List <SqlParameter>();
String crit = getSearchCriterias(criteria);
String order = OrderBy.ToString() + " " + AscDesc.ToString();
//有待改进，没危险的int数据用了参数插入，有风险的字符串反而直接组装SQL，虽然做了简单的去单引号处理
sql = String.Format("exec sp_paging {0},@pageNum,@pageSize,'{1}','{2}'", tbl, crit, order);
pars.Add(new SqlParameter("@pageNum", pageNum));
pars.Add(new SqlParameter("@pageSize", pageSize));
DataTable dt = DBHelper.ExecuteDataTable(sql, pars);
totalRecords = 0;
if (dt.Rows.Count > 0)
{
totalRecords = (int)dt.Rows[0]["TotalRecord"];
}
return(DtToList(dt));
}
public T_Task_History GetById(Int32 id)
{
T_Task_History entity = new T_Task_History()
{
id = id
};
return(GetByModel(entity, false));
}
/// <summary>
/// 更新一条数据，当isNullIgnored为false时，空字段将在数据库里面写入NULL值；
/// 当isNullIgnored为true时，空字段将不进行更新，此时用户可以少用一次getById来获取原对象进行更新
/// </summary>
public int Update(T_Task_History entity, bool isNullIgnored)
{
if (!isNullIgnored)
{
return(Update(entity));
}
List <SqlParameter> pars = new List <SqlParameter>();
StringBuilder sql = new StringBuilder();
sql.Append(" UPDATE [gdce_task].dbo.[T_Task_History] SET ");
if (entity.taskId != null)
{
sql.Append(" [taskId] = @taskId,");
pars.Add(new SqlParameter("@taskId", entity.taskId));
}
if (entity.userName != null)
{
sql.Append(" [userName] = @userName,");
pars.Add(new SqlParameter("@userName", entity.userName));
}
if (entity.content != null)
{
sql.Append(" [content] = @content,");
pars.Add(new SqlParameter("@content", entity.content));
}
if (entity.descr != null)
{
sql.Append(" [descr] = @descr,");
pars.Add(new SqlParameter("@descr", entity.descr));
}
if (entity.createTime != null)
{
sql.Append(" [createTime] = @createTime,");
pars.Add(new SqlParameter("@createTime", entity.createTime));
}
if (entity.userId != null)
{
sql.Append(" [userId] = @userId,");
pars.Add(new SqlParameter("@userId", entity.userId));
}
sql.Remove(sql.Length - 1, 1);
sql.Append(" where id = @id ;");
pars.Add(new SqlParameter("@id", entity.id));
return(DBHelper.ExecuteNonQuery(sql.ToString(), pars));
}
//处理本类的所有属性，把String类型里面的单引号处理一下
public void AntiSQLInjection(T_Task_History entity)
{
Type t = entity.GetType();
foreach (System.Reflection.PropertyInfo p in t.GetProperties())
{
if (p.GetValue(entity, null) is String)
{
String s = AntiInjection(p.GetValue(entity, null).ToString());
p.SetValue(entity, s, null);
}
}
}
public T_Task_History GetByModel(T_Task_History entity, bool isLazy)
{
T_Task_History result = GetByModel(entity);
if (result == null)//如果传入的实体为空（或者根据ID找不到）,则需要new一个空对象
{
result = new T_Task_History();
}
if (!isLazy)
{
using (T_TaskDAO dao = new T_TaskDAO())
result.taskIdEntitys = dao.GetAllByModel(new T_Task());
}
return(result);
}
/// <summary>
/// 用拼接SQL的方式，
/// 把当前表以及其使用到的所有外键表链接起来，
/// 拼接成一条SQL子查询，供分页的存储过程使用
/// </summary>
private String PreparedTable(T_Task_History entity, bool isFuzzySearch, bool isSignleResult)
{
String sql = getFKColumnsSQL("[gdce_task].dbo.T_Task_History", T_Task_History.DBColumns, isFuzzySearch, isSignleResult);
if (entity == null)
{
return(sql);
}
if (entity.id != null)
{
sql += " and [gdce_task].dbo.T_Task_History.id = " + entity.id;
}
if (entity.taskId != null)
{
sql += " and [gdce_task].dbo.T_Task_History.taskId = " + entity.taskId;
}
if (entity.userName != null)
{
sql += String.Format(" and [gdce_task].dbo.T_Task_History.userName = '******' ", AntiInjection(entity.userName));
}
if (entity.content != null)
{
sql += String.Format(" and [gdce_task].dbo.T_Task_History.content = '{0}' ", AntiInjection(entity.content));
}
if (entity.descr != null)
{
sql += String.Format(" and [gdce_task].dbo.T_Task_History.descr = '{0}' ", AntiInjection(entity.descr));
}
if (entity.createTime != null)
{
sql += String.Format(" and [gdce_task].dbo.T_Task_History.createTime = '{0}' ", entity.createTime.Value.ToString("yyyy-MM-dd HH:mm:ss"));
}
if (entity.userId != null)
{
sql += String.Format(" and [gdce_task].dbo.T_Task_History.userId = '{0}' ", AntiInjection(entity.userId));
}
return(AntiInjection(sql));
}
public List <T_Task_History> DtToList(DataTable dt)
{
List <T_Task_History> entities = new List <T_Task_History>();
foreach (DataRow dr in dt.Rows)
{
T_Task_History entity = new T_Task_History(dr, "");
try
{
if (entity.taskId != null)
{
entity.taskIdEntity = new T_Task(dr, "taskId_");
}
}
catch (Exception ex) { }
entities.Add(entity);
}
return(entities);
}
public T_Task_History GetByModel(T_Task_History entity)
{
String sql = String.Format("exec sp_executesql N'{0}'", PreparedTable(entity, false, true));
DataTable dt = DBHelper.ExecuteDataTable(sql);
if (dt != null && dt.Rows.Count > 0)
{
entity = new T_Task_History(dt.Rows[0], "");
if (entity.taskId != null)
{
entity.taskIdEntity = new T_Task(dt.Rows[0], "taskId_");
}
return(entity);
}
else
{
return(null);
}
}
/// <summary>
/// 更新一条数据，空字段将在数据库里面写入NULL值，请注意。
/// </summary>
public int Update(T_Task_History entity)
{
AntiSQLInjection(entity);
StringBuilder sql = new StringBuilder();
sql.Append(" exec sp_executesql N'");
sql.Append(" UPDATE [gdce_task].dbo.[T_Task_History] SET ");
sql.Append(" [taskId] = @taskId,");
sql.Append(" [userName] = @userName,");
sql.Append(" [content] = @content,");
sql.Append(" [descr] = @descr,");
sql.Append(" [createTime] = @createTime,");
sql.Append(" [userId] = @userId,");
sql.Remove(sql.Length - 1, 1);
sql.Append(" WHERE [id] = ''" + entity.id + "'' ', N'");
sql.Append("@taskId nvarchar(max),");
sql.Append("@userName nvarchar(max),");
sql.Append("@content nvarchar(max),");
sql.Append("@descr nvarchar(max),");
sql.Append("@createTime nvarchar(max),");
sql.Append("@userId nvarchar(max),");
sql.Remove(sql.Length - 1, 1);
sql.Append("', ");
sql.Append("@taskId=" + (entity.taskId != null ? "N'" + entity.taskId + "'," : "NULL,"));
sql.Append("@userName="******"N'" + entity.userName + "'," : "NULL,"));
sql.Append("@content=" + (entity.content != null ? "N'" + entity.content + "'," : "NULL,"));
sql.Append("@descr=" + (entity.descr != null ? "N'" + entity.descr + "'," : "NULL,"));
sql.Append("@createTime=" + (entity.createTime != null ? "N'" + entity.createTime + "'," : "NULL,"));
sql.Append("@userId=" + (entity.userId != null ? "N'" + entity.userId + "'," : "NULL,"));
sql.Remove(sql.Length - 1, 1);
return(DBHelper.ExecuteNonQuery(sql.ToString()));
}
/// <summary>
/// 新增一条数据，空字段将使用数据库默认值
/// </summary>
public int Add(T_Task_History entity)
{
List <SqlParameter> pars = new List <SqlParameter>();
StringBuilder sql = new StringBuilder();
String sql2 = " ";
sql.Append(" INSERT INTO [gdce_task].dbo.[T_Task_History] ( ");
if (entity.id != null)
{
sql.Append(" [id],");
pars.Add(new SqlParameter("@id", entity.id));
sql2 += " @id,";
}
if (entity.taskId != null)
{
sql.Append(" [taskId],");
pars.Add(new SqlParameter("@taskId", entity.taskId));
sql2 += " @taskId,";
}
if (entity.userName != null)
{
sql.Append(" [userName],");
pars.Add(new SqlParameter("@userName", entity.userName));
sql2 += " @userName,";
}
if (entity.content != null)
{
sql.Append(" [content],");
pars.Add(new SqlParameter("@content", entity.content));
sql2 += " @content,";
}
if (entity.descr != null)
{
sql.Append(" [descr],");
pars.Add(new SqlParameter("@descr", entity.descr));
sql2 += " @descr,";
}
if (entity.createTime != null)
{
sql.Append(" [createTime],");
pars.Add(new SqlParameter("@createTime", entity.createTime));
sql2 += " @createTime,";
}
if (entity.userId != null)
{
sql.Append(" [userId],");
pars.Add(new SqlParameter("@userId", entity.userId));
sql2 += " @userId,";
}
sql.Remove(sql.Length - 1, 1);//去掉末尾的逗号
sql2 = sql2.Remove(sql2.Length - 1, 1);
sql.Append(" ) values ( " + sql2 + ");select case when @@identity is not null then @@identity else 1 end as id;");
return(Convert.ToInt32(DBHelper.ExecuteDataTable(sql.ToString(), pars).Rows[0][0]));
}
/// <summary>
/// 以实体类作为查询条件获取符合条件的数据集，NULL值不作为查询条件
/// </summary>
public List <T_Task_History> GetAllByModel(T_Task_History entity)
{
String sql = String.Format("exec sp_executesql N'{0}'", PreparedTable(entity, false, false));
return(DtToList(DBHelper.ExecuteDataTable(sql)));
}