Exemple #1
0
        /// <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));
        }
Exemple #2
0
        public T_Task_History GetById(Int32 id)
        {
            T_Task_History entity = new T_Task_History()
            {
                id = id
            };

            return(GetByModel(entity, false));
        }
Exemple #3
0
        /// <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));
        }
Exemple #4
0
        //处理本类的所有属性,把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);
                }
            }
        }
Exemple #5
0
        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);
        }
Exemple #6
0
        /// <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));
        }
Exemple #7
0
        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);
        }
Exemple #8
0
        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);
            }
        }
Exemple #9
0
        /// <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()));
        }
Exemple #10
0
        /// <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]));
        }
Exemple #11
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)));
        }