Esempio n. 1
0
        /// <summary>
        /// 拼接 查询最大数 SQL语句
        /// </summary>
        /// <param name="propertyName">属性字段</param>
        /// <returns></returns>
        public static StringBuilder SelectMaxSql <T>(string propertyName) where T : new()
        {
            //表名
            string table = EntityAttributeHelper.GetEntityTable <T>();

            return(new StringBuilder("SELECT MAX(" + propertyName + ") FROM " + table + "  WHERE 1=1 "));
        }
Esempio n. 2
0
        /// <summary>
        /// 拼接 查询 SQL语句,自定义条件
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="where">条件</param>
        /// <param name="allFieid">是否查询所有字段</param>
        /// <returns></returns>
        public static StringBuilder SelectSql <T>(string where, bool allFieid = false) where T : class, new()
        {
            //表名
            string table = EntityAttributeHelper.GetEntityTable <T>();

            PropertyInfo[] props     = EntityAttributeHelper.GetProperties(typeof(T));
            StringBuilder  sbColumns = new StringBuilder();

            if (allFieid)
            {
                sbColumns.Append(" * ");
            }
            else
            {
                foreach (PropertyInfo prop in props)
                {
                    //string propertytype = prop.PropertyType.ToString();
                    sbColumns.Append("[" + prop.Name + "],");
                }
                if (sbColumns.Length > 0)
                {
                    sbColumns.Remove(sbColumns.ToString().Length - 1, 1);
                }
            }

            if (string.IsNullOrWhiteSpace(where))
            {
                where = " WHERE 1 = 1";
            }

            string strSql = "SELECT {0} FROM {1} {2}";

            strSql = string.Format(strSql, sbColumns.ToString(), table + " ", where);
            return(new StringBuilder(strSql));
        }
Esempio n. 3
0
        /// <summary>
        /// 拼接 查询条数 SQL语句
        /// </summary>
        /// <returns></returns>
        public static StringBuilder SelectCountSql <T>() where T : new()
        {
            //表名
            string table = EntityAttributeHelper.GetEntityTable <T>();

            return(new StringBuilder("SELECT Count(1) FROM " + table + " WHERE 1=1 "));
        }
Esempio n. 4
0
        /// <summary>
        /// 拼接删除SQL语句
        /// </summary>
        /// <param name="where">删除条件</param>
        /// <returns></returns>
        public static StringBuilder DeleteSql <T>(string where)
        {
            //表名
            string table = EntityAttributeHelper.GetEntityTable <T>();

            if (string.IsNullOrWhiteSpace(where))
            {
                where = "1 = 1";
            }

            return(new StringBuilder("Delete From " + table + " " + where));
        }
Esempio n. 5
0
        /// <summary>
        /// 泛型方法,反射生成UpdateSql语句
        /// </summary>
        /// <param name="entity">实体类</param>
        /// <param name="pkName">主键</param>
        /// <param name="where">自定义条件</param>
        /// <returns>int</returns>
        public static StringBuilder UpdateSql <T>(T entity, string pkName = "", string where = "")
        {
            Type type = entity.GetType();
            //表名
            string table = EntityAttributeHelper.GetEntityTable <T>();
            //主键
            string keyField = EntityAttributeHelper.GetEntityKey <T>();
            //获取不做映射的字段
            List <string> notMappedField = EntityAttributeHelper.GetNotMappedFields <T>();

            PropertyInfo[] props = type.GetProperties();
            StringBuilder  sb    = new StringBuilder();

            sb.Append(" Update ");
            sb.Append(table);
            sb.Append(" Set ");
            bool isFirstValue = true;

            foreach (PropertyInfo prop in props)
            {
                if (!notMappedField.Contains(prop.Name))
                {
                    if (prop.GetValue(entity, null) != null && keyField != prop.Name)
                    {
                        if (isFirstValue)
                        {
                            isFirstValue = false;
                            sb.Append("[" + prop.Name + "]");
                            sb.Append("=");
                            sb.Append(DbParameters.CreateDbParmCharacter() + prop.Name);
                        }
                        else
                        {
                            sb.Append(",[" + prop.Name + "]");
                            sb.Append("=");
                            sb.Append(DbParameters.CreateDbParmCharacter() + prop.Name);
                        }
                    }
                }
            }

            if (!string.IsNullOrEmpty(pkName))
            {
                sb.Append(" Where ").Append(pkName).Append("=").Append(DbParameters.CreateDbParmCharacter() + pkName);
            }
            else if (!string.IsNullOrEmpty(where))
            {
                sb.Append(" " + where);
            }
            return(sb);
        }
Esempio n. 6
0
        /// <summary>
        /// 拼接 查询条数 SQL语句
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="where"></param>
        /// <returns></returns>
        public static StringBuilder SelectCountSql <T>(string where) where T : new()
        {
            //表名
            string table = EntityAttributeHelper.GetEntityTable <T>();

            if (!string.IsNullOrEmpty(where))
            {
                if (!where.ToLower().Contains("where"))
                {
                    where = " WHERE " + where;
                }
            }
            return(new StringBuilder("SELECT Count(1) FROM " + table + " " + where));
        }
Esempio n. 7
0
        /// <summary>
        /// 泛型方法,反射生成InsertSql语句
        /// </summary>
        /// <param name="entity">实体类</param>
        /// <returns>int</returns>
        public static StringBuilder InsertSql <T>(T entity)
        {
            Type type = entity.GetType();
            //表名
            string table = EntityAttributeHelper.GetEntityTable <T>();
            //获取不做映射的字段
            List <string> notMappedField = EntityAttributeHelper.GetNotMappedFields <T>();

            StringBuilder sb = new StringBuilder();

            sb.Append(" Insert Into ");
            sb.Append(table);
            sb.Append("(");
            StringBuilder sp       = new StringBuilder();
            StringBuilder sb_prame = new StringBuilder();

            PropertyInfo[] props = type.GetProperties();
            foreach (PropertyInfo prop in props)
            {
                if (!notMappedField.Contains(prop.Name))
                {
                    object value = prop.GetValue(entity, null);
                    if (value != null)
                    {
                        if (value.GetType() == typeof(DateTime))
                        {
                            var time = (DateTime)value;
                            if (time.Ticks != 0 && time.Kind != DateTimeKind.Unspecified)
                            {
                                sb_prame.Append(",[" + prop.Name + "]");
                                sp.Append("," + DbParameters.CreateDbParmCharacter() + "" + (prop.Name));
                            }
                        }
                        else
                        {
                            sb_prame.Append(",[" + prop.Name + "]");
                            sp.Append("," + DbParameters.CreateDbParmCharacter() + "" + (prop.Name));
                        }
                    }
                }
            }
            sb.Append(sb_prame.ToString().Substring(1, sb_prame.ToString().Length - 1) + ") Values (");
            sb.Append(sp.ToString().Substring(1, sp.ToString().Length - 1) + ")");
            return(sb);
        }
Esempio n. 8
0
        /// <summary>
        /// 拼接删除SQL语句
        /// </summary>
        /// <param name="entity">实体类</param>
        /// <returns></returns>
        public static StringBuilder DeleteSql <T>(T entity)
        {
            Type type = entity.GetType();

            PropertyInfo[] props = type.GetProperties();
            //表名
            string table = EntityAttributeHelper.GetEntityTable <T>();

            StringBuilder sb = new StringBuilder("Delete From " + table + " Where 1=1");

            foreach (PropertyInfo prop in props)
            {
                if (prop.GetValue(entity, null) != null)
                {
                    sb.Append(" AND " + prop.Name + " = " + DbParameters.CreateDbParmCharacter() + "" + prop.Name + "");
                }
            }
            return(sb);
        }
Esempio n. 9
0
        /// <summary>
        /// 拼接 查询 SQL语句
        /// </summary>
        /// <param name="top">显示条数</param>
        /// <returns></returns>
        public static StringBuilder SelectSql <T>(int top) where T : new()
        {
            //表名
            string table = EntityAttributeHelper.GetEntityTable <T>();

            PropertyInfo[] props     = EntityAttributeHelper.GetProperties(typeof(T));
            StringBuilder  sbColumns = new StringBuilder();

            foreach (PropertyInfo prop in props)
            {
                sbColumns.Append(prop.Name + ",");
            }
            if (sbColumns.Length > 0)
            {
                sbColumns.Remove(sbColumns.ToString().Length - 1, 1);
            }
            string strSql = "SELECT top {0} {1} FROM {2} WHERE 1=1 ";

            strSql = string.Format(strSql, top, sbColumns.ToString(), table + " ");
            return(new StringBuilder(strSql));
        }
Esempio n. 10
0
        /// <summary>
        /// 泛型方法,反射生成UpdateSql语句
        /// </summary>
        /// <param name="entity">实体类</param>
        /// <param name="where">更新条件</param>
        /// <returns>int</returns>
        public static StringBuilder UpdateSql <T>(T entity, string where = "")
        {
            Type type = entity.GetType();
            //表名
            string table = EntityAttributeHelper.GetEntityTable <T>();
            //主键
            string keyField = EntityAttributeHelper.GetEntityKey <T>();
            //获取不做映射的字段
            List <string> notMappedField = EntityAttributeHelper.GetNotMappedFields <T>();

            PropertyInfo[] props = type.GetProperties();
            StringBuilder  sb    = new StringBuilder();

            sb.Append("Update ");
            sb.Append(table);
            sb.Append(" Set ");
            bool isFirstValue = true;

            foreach (PropertyInfo prop in props)
            {
                if (!notMappedField.Contains(prop.Name))
                {
                    object value = prop.GetValue(entity, null);
                    if (value != null && keyField != prop.Name)
                    {
                        if (value.GetType() == typeof(DateTime))
                        {
                            var time = (DateTime)value;
                            if (time.Ticks != 0 && time.Kind != DateTimeKind.Unspecified)
                            {
                                if (isFirstValue)
                                {
                                    isFirstValue = false;
                                    sb.Append("[" + prop.Name + "]");
                                    sb.Append("=");
                                    sb.Append(DbParameters.CreateDbParmCharacter() + prop.Name);
                                }
                                else
                                {
                                    sb.Append(",[" + prop.Name + "]");
                                    sb.Append("=");
                                    sb.Append(DbParameters.CreateDbParmCharacter() + prop.Name);
                                }
                            }
                        }
                        else
                        {
                            if (isFirstValue)
                            {
                                isFirstValue = false;
                                sb.Append("[" + prop.Name + "]");
                                sb.Append("=");
                                sb.Append(DbParameters.CreateDbParmCharacter() + prop.Name);
                            }
                            else
                            {
                                sb.Append(",[" + prop.Name + "]");
                                sb.Append("=");
                                sb.Append(DbParameters.CreateDbParmCharacter() + prop.Name);
                            }
                        }
                    }
                }
            }
            sb.Append(" Where ").Append(keyField).Append("=").Append(DbParameters.CreateDbParmCharacter() + keyField);
            return(sb);
        }