/// <summary> /// 用拼接SQL的方式, /// 把当前表以及其使用到的所有外键表链接起来, /// 拼接成一条SQL子查询,供分页的存储过程使用 /// </summary> private String PreparedTable(T_Task_User entity, bool isFuzzySearch, bool isSignleResult) { String sql = getFKColumnsSQL("[gdce_task].dbo.T_Task_User", T_Task_User.DBColumns, isFuzzySearch, isSignleResult); if (entity == null) { return(sql); } if (entity.id != null) { sql += " and [gdce_task].dbo.T_Task_User.id = " + entity.id; } if (entity.taskId != null) { sql += " and [gdce_task].dbo.T_Task_User.taskId = " + entity.taskId; } if (entity.userId != null) { sql += String.Format(" and [gdce_task].dbo.T_Task_User.userId = '{0}' ", AntiInjection(entity.userId)); } if (entity.descr != null) { sql += String.Format(" and [gdce_task].dbo.T_Task_User.descr = '{0}' ", AntiInjection(entity.descr)); } return(AntiInjection(sql)); }
public int Add(T_Task_User entity) { AntiSQLInjection(entity); StringBuilder sql = new StringBuilder(); sql.Append(" exec sp_executesql N'"); sql.Append(" INSERT INTO [gdce_task].dbo.[T_Task_User] ( "); sql.Append(" [taskId],"); sql.Append(" [userId],"); sql.Append(" [descr],"); sql.Remove(sql.Length - 1, 1); sql.Append(" ) values ( "); sql.Append(entity.taskId != null ? "@taskId," : "DEFAULT,"); sql.Append(entity.userId != null ? "@userId," : "DEFAULT,"); sql.Append(entity.descr != null ? "@descr," : "DEFAULT,"); sql.Remove(sql.Length - 1, 1); sql.Append(" );select case when @@identity is not null then @@identity else 1 end as id;', N'"); sql.Append(entity.taskId != null ? "@taskId nvarchar(max)," : ""); sql.Append(entity.userId != null ? "@userId nvarchar(max)," : ""); sql.Append(entity.descr != null ? "@descr nvarchar(max)," : ""); sql.Remove(sql.Length - 1, 1); sql.Append("', "); sql.Append(entity.taskId != null ? "@taskId=N'" + entity.taskId + "'," : ""); sql.Append(entity.userId != null ? "@userId=N'" + entity.userId + "'," : ""); sql.Append(entity.descr != null ? "@descr=N'" + entity.descr + "'," : ""); sql.Remove(sql.Length - 1, 1); return(Convert.ToInt32(DBHelper.ExecuteDataTable(sql.ToString()).Rows[0][0])); }
/// <summary> /// 以实体类作为查询条件获取符合条件的数据集,NULL值不作为查询条件 /// </summary> public List <T_Task_User> Search(T_Task_User entity, int pageNum, int pageSize, String criteria, T_Task_User.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 int Update(T_Task_User entity) { AntiSQLInjection(entity); StringBuilder sql = new StringBuilder(); sql.Append(" exec sp_executesql N'"); sql.Append(" UPDATE [gdce_task].dbo.[T_Task_User] SET "); sql.Append(" [taskId] = @taskId,"); sql.Append(" [userId] = @userId,"); sql.Append(" [descr] = @descr,"); sql.Remove(sql.Length - 1, 1); sql.Append(" WHERE [id] = ''" + entity.id + "'' ', N'"); sql.Append("@taskId nvarchar(max),"); sql.Append("@userId nvarchar(max),"); sql.Append("@descr nvarchar(max),"); sql.Remove(sql.Length - 1, 1); sql.Append("', "); sql.Append("@taskId=" + (entity.taskId != null ? "N'" + entity.taskId + "'," : "NULL,")); sql.Append("@userId=" + (entity.userId != null ? "N'" + entity.userId + "'," : "NULL,")); sql.Append("@descr=" + (entity.descr != null ? "N'" + entity.descr + "'," : "NULL,")); sql.Remove(sql.Length - 1, 1); return(DBHelper.ExecuteNonQuery(sql.ToString())); }
public T_Task_User GetById(Int32 id) { T_Task_User entity = new T_Task_User() { id = id }; return(GetByModel(entity, false)); }
//处理本类的所有属性,把String类型里面的单引号处理一下 public void AntiSQLInjection(T_Task_User 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_User GetByModel(T_Task_User entity, bool isLazy) { T_Task_User result = GetByModel(entity); if (result == null)//如果传入的实体为空(或者根据ID找不到),则需要new一个空对象 { result = new T_Task_User(); } if (!isLazy) { using (T_TaskDAO dao = new T_TaskDAO()) result.taskIdEntitys = dao.GetAllByModel(new T_Task()); using (T_UserDAO dao = new T_UserDAO()) result.userIdEntitys = dao.GetAllByModel(new T_User()); } return(result); }
public List <T_Task_User> DtToList(DataTable dt) { List <T_Task_User> entities = new List <T_Task_User>(); foreach (DataRow dr in dt.Rows) { T_Task_User entity = new T_Task_User(dr, ""); try { if (entity.taskId != null) { entity.taskIdEntity = new T_Task(dr, "taskId_"); } if (entity.userId != null) { entity.userIdEntity = new T_User(dr, "userId_"); } } catch (Exception ex) { } entities.Add(entity); } return(entities); }
public T_Task_User GetByModel(T_Task_User 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_User(dt.Rows[0], ""); if (entity.taskId != null) { entity.taskIdEntity = new T_Task(dt.Rows[0], "taskId_"); } if (entity.userId != null) { entity.userIdEntity = new T_User(dt.Rows[0], "userId_"); } return(entity); } else { return(null); } }
/// <summary> /// 以实体类作为查询条件获取符合条件的数据集,NULL值不作为查询条件 /// </summary> public List <T_Task_User> GetAllByModel(T_Task_User entity) { String sql = String.Format("exec sp_executesql N'{0}'", PreparedTable(entity, false, false)); return(DtToList(DBHelper.ExecuteDataTable(sql))); }