/// <summary>
        ///  得到where语句
        /// </summary>
        /// <param name="filters"></param>
        /// <returns></returns>
        internal virtual string GetWhereString(QueryPageFilter filters)
        {
            StringBuilder builder = new StringBuilder();
            int           index   = 0;

            if (filters.Filters != null && filters.Filters.Length > 0)
            {
                foreach (var item in filters.Filters)
                {
                    if (index != 0)
                    {
                        builder.Append(" " + filters.FilterAndOrType.ToString() + " ");
                    }
                    string str = string.Empty;
                    if (item is QueryFilter)
                    {
                        str = GetSqlSign(item as QueryFilter);
                    }
                    if (item is AndOrQueryFilter)
                    {
                        str = GetSqlSign(item as AndOrQueryFilter);
                    }
                    builder.Append(str);
                    index++;
                }
            }
            if (index == 0)
            {
                return("1=1");
            }
            return(builder.ToString());
        }
        public Dictionary <string, object>[] GetQueryResultN(QueryPageFilter filter)
        {
            string tableName1 = filter.TableName;

            if (TableToTableNameDic != null && TableToTableNameDic.ContainsKey(filter.TableName))
            {
                tableName1 = TableToTableNameDic[filter.TableName];
            }
            if (!DataBaseKyFieldTableDic.ContainsKey(tableName1))
            {
                throw new Exception("缺少表或视图:" + tableName1 + "!请联系开发人员!");
            }
            filter.TableName = tableName1;
            var result = GetQueryResultFromDB(filter);

            if (filter.IsReturnCount != true)
            {
                if (result.Result != null)
                {
                    var list = ConvertDataTable(result.Result);
                    return(list.ToArray());
                }
            }
            return(null);
        }
 /// <summary>
 /// 得到值
 /// </summary>
 /// <param name="fieldValue"></param>
 /// <returns></returns>
 internal virtual string GetFieldValue(object fieldValue)
 {
     if (QueryPageFilter.IsSqlFilter(fieldValue) == true)
     {
         throw new Exception("数据中含SQL注入,请误使用!");
     }
     if (fieldValue is string)
     {
         string str = fieldValue.ToString();
         if (str.IndexOf("'") > -1)
         {
             str = str.Replace("'", "''");
         }
         return(string.Format("'{0}'", str));
     }
     if (fieldValue is DBNull || fieldValue is DateTime || fieldValue is Guid)
     {
         return(string.Format("'{0}'", fieldValue.ToString()));
     }
     if (fieldValue is Boolean)
     {
         bool t = (Boolean)fieldValue;
         return(t ? 1.ToString() : 0.ToString());
     }
     else
     {
         if (fieldValue == null)
         {
             return(null);
         }
         return(fieldValue.ToString());
     }
 }
        /// <summary>
        /// 得到group by语句
        /// </summary>
        /// <param name="filters"></param>
        /// <returns></returns>
        internal virtual string GetGroupByStrings(QueryPageFilter filters)
        {
            string fields = string.Empty;

            if (filters != null && filters.GroupByFieldNames != null && filters.GroupByFieldNames.Length > 0)
            {
                StringBuilder builder = new StringBuilder();
                for (int i = 0; i < filters.GroupByFieldNames.Length; i++)
                {
                    builder.Append(filters.GroupByFieldNames[i]);
                    if (i != filters.GroupByFieldNames.Length - 1)
                    {
                        builder.Append(",");
                    }
                }
                fields = builder.ToString();
            }
            var groupBy = string.Empty;

            if (!string.IsNullOrEmpty(fields))
            {
                groupBy = "group by " + fields;
            }
            return(groupBy);
        }
        /// <summary>
        /// 得到order by语句
        /// </summary>
        /// <param name="filters"></param>
        /// <returns></returns>
        internal virtual string GetOrderByString(QueryPageFilter filters)
        {
            string orderBy = string.Empty;

            if (filters != null && filters.OrderFieldNames != null && filters.OrderFieldNames.Length > 0)
            {
                StringBuilder builder = new StringBuilder();
                for (int i = 0; i < filters.OrderFieldNames.Length; i++)
                {
                    builder.Append(filters.OrderFieldNames[i]);
                    if (filters.OrderByType == SQLOrderBy.Desc)
                    {
                        builder.Append(" desc ");
                    }
                    else
                    {
                        builder.Append(" asc ");
                    }
                    if (i != filters.OrderFieldNames.Length - 1)
                    {
                        builder.Append(",");
                    }
                }
                orderBy = " order by " + builder.ToString();
            }
            return(orderBy);
        }
        public void UpdateObjects(Dictionary <string, object> objs, QueryPageFilter filter)
        {
            string tableName1 = filter.TableName;

            if (TableToTableNameDic != null && TableToTableNameDic.ContainsKey(filter.TableName))
            {
                tableName1 = TableToTableNameDic[filter.TableName];
            }
            if (!DataBaseKyFieldTableDic.ContainsKey(tableName1))
            {
                throw new Exception("缺少表或视图:" + tableName1 + "!请联系开发人员!");
            }
            filter.TableName = tableName1;
            string        whereStr = GetWhereString(filter);
            StringBuilder fields   = new StringBuilder();

            foreach (var item in objs)
            {
                SqlField field;
                var      typeName = DicTableToTableNameFields[filter.TableName][item.Key];
                if (typeName == "datetime" && item.Value != null)
                {
                    var date = DateTime.Parse(item.Value.ToString());
                    field = new SqlField(item.Key, date, CurrPrividerType);
                }
                else
                {
                    field = new SqlField(item.Key, item.Value, CurrPrividerType);
                }
                if (fields.Length > 0)
                {
                    fields.Append(",");
                }
                fields.Append(field.GetKeyEqualsValueString());
            }

            var updateSql = string.Format("update {0} set {1} where {2} ", tableName1, fields.ToString(), whereStr);
            var conn      = DBClassHelper.OpenConnect(CurrConnectionString, SqlHelperFactory.GetSqlPrividerTypeName(CurrPrividerType));
            int count     = DBClassHelper.Execute(updateSql, conn);

            conn.Close();
        }
        /// <summary>
        /// 得到查询字段
        /// </summary>
        /// <param name="filters"></param>
        /// <returns></returns>
        internal virtual string GetQueryFields(QueryPageFilter filters)
        {
            if (filters.ReturnFieldNames == null || filters.ReturnFieldNames.Length == 0)
            {
                return(GetFieldFromTable(filters.TableName));
            }
            StringBuilder builder = new StringBuilder();

            for (int i = 0; i < filters.ReturnFieldNames.Length; i++)
            {
                if (builder.Length > 0)
                {
                    builder.Append(",");
                }
                else
                {
                    builder.Append(filters.ReturnFieldNames[i]);
                }
            }
            return(builder.ToString());
        }
        public void DeleteObjects(QueryPageFilter filters)
        {
            string tableName1 = filters.TableName;

            if (TableToTableNameDic != null && TableToTableNameDic.ContainsKey(filters.TableName))
            {
                tableName1 = TableToTableNameDic[filters.TableName];
            }
            if (!DataBaseKyFieldTableDic.ContainsKey(tableName1))
            {
                throw new Exception("缺少表或视图:" + tableName1 + "!请联系开发人员!");
            }
            filters.TableName = tableName1;
            string whereStr = GetWhereString(filters);
            string strSql   = string.Empty;

            strSql = "delete from " + filters.TableName + " where " + whereStr;
            var conn  = DBClassHelper.OpenConnect(CurrConnectionString, SqlHelperFactory.GetSqlPrividerTypeName(CurrPrividerType));
            int count = DBClassHelper.Execute(strSql, conn);

            conn.Close();
        }
示例#9
0
        /*
         * public override void ExecuteSqlList(List<string> sqlList)
         * {
         *  var sqls = sqlList.Where(m => string.IsNullOrEmpty(m) == false).ToList();
         *  var conn = DBClassHelper.OpenConnect(CurrConnectionString, SqlHelperFactory.GetSqlPrividerTypeName(CurrPrividerType));
         *  System.Data.IDbCommand cmd = conn.CreateCommand();
         *  { // <-------------------
         *      try
         *      {
         *          foreach (var item in sqls)
         *          {
         *              cmd.CommandText = item;
         *              cmd.ExecuteNonQuery();
         *          }
         *          cmd.Dispose();
         *      }
         *      catch
         *      {
         *          cmd.Dispose();
         *          throw; // <-------------------
         *      }
         *      finally
         *      {
         *          conn.Close();
         *      }
         *  }
         * }
         */
        /*
         * public override void UpdateSystemTable(string TableName, List<Column> colomns)
         * {
         *  string def = string.Empty;
         *  var builder = new StringBuilder();
         *  var builder2 = new StringBuilder();
         *  var descBuilder = new StringBuilder();
         *  if (TableToTableNameDic.ContainsKey(TableName))
         *  {
         *      int i = 1;
         *      foreach (var item in colomns)
         *      {
         *          i++;
         *          string len = "";
         *          string isNullStr;
         *          if (item.Precision == 0) len = "";
         *          else if (item.Precision == -1) len = "(MAX)";
         *          else len = string.Format("({0})", item.Precision);
         *
         *          if (item.TypeName == "varchar" && string.IsNullOrEmpty(len)) len = "(50)";
         *          if (item.Default != null && !string.IsNullOrEmpty(item.Default))//修改数据库默认值,删除约束,添加约束(添加常量默认值必须加‘ ’;例  'GAAJ')
         *          {
         *              if (item.Default == "'auto'")
         *              {
         *
         *                  //String sql2 = string.Format(@" ALTER TABLE {0} ADD {1} INT IDENTITY (1,1);", TableName, item.ColumnName);
         *                  //builder2.AppendLine(sql2);
         *                  continue;
         *
         *              }
         *              else
         *              {
         *                  def = "((" + item.Default + "))";
         *                  string sql2 = string.Format(@"IF EXISTS(select a.name as 用户表,b.name as 字段名,d.name as 字段默认值约束 from sysobjects a
         *                                            inner join syscolumns b on (a.id=b.id)inner join syscomments c on ( b.cdefault=c.id )
         *                                            inner join sysobjects d on (c.id=d.id)
         *                                             where a.name='{0}'and b.name='{1}')
         *                                             declare @tablename{2} varchar(30)
         *                                             declare @fieldname{2} varchar(50)
         *                                             declare @sql{2} varchar(300)
         *
         *                                             set @tablename{2}='{0}'
         *                                             set @fieldname{2}='{1}'
         *                                             set @sql{2}=''
         *                                             select @sql{2}=@sql{2}+'
         *                                             alter table ['+a.name+'] drop constraint ['+d.name+']'
         *                                             from sysobjects a
         *                                             join syscolumns b on a.id=b.id
         *                                             join syscomments c on b.cdefault=c.id
         *                                             join sysobjects d on c.id=d.id
         *                                             where a.name=@tablename{2} and b.name=@fieldname{2}
         *                                             exec(@sql{2})", TableName, item.ColumnName, i);
         *
         *                  string sql3 = string.Format(@" ALTER TABLE {0}  ADD DEFAULT {1} FOR {2} WITH VALUES", TableName, def, item.ColumnName);
         *
         *                  builder2.AppendLine(sql2 + sql3);
         *              }
         *          }
         *          if (item.IsPrimaryKey == true)
         *          {
         *              isNullStr = "NOT NULL";
         *          }
         *          else
         *          {
         *              isNullStr = "NULL";
         *          }
         *          string sql = string.Format(
         *              @" if exists(select * from syscolumns where id=object_id('{0}') and name='{1}')
         *                          alter table {0} alter column  {1}  {2}{3} {4} ;  else   alter table {0}  add   {1}  {2}{3} {4} ", TableName, item.ColumnName, item.TypeName, len, isNullStr);
         *          var str = string.Format("if exists(SELECT c.name,p.value FROM sys.extended_properties p ,sys.columns c " +
         *              "WHERE p.major_id=OBJECT_ID('{0}') and p.major_id=c.object_id and p.minor_id=c.column_id and c.name = '{2}') " +
         *              "EXECUTE sp_updateextendedproperty N'MS_Description', '{1}', N'user', N'dbo', N'table', N'{0}', N'column', N'{2}';" +
         *              "else EXECUTE sp_addextendedproperty N'MS_Description', '{1}', N'user', N'dbo', N'table', N'{0}', N'column', N'{2}';", TableName, item.Label, item.ColumnName);
         *          builder.AppendLine(sql);
         *          descBuilder.AppendLine(str);
         *      }
         *  }
         *  else
         *  {
         *      var keyCol = colomns.Where(m => m.IsPrimaryKey == true).FirstOrDefault();
         *      if (keyCol == null)
         *          throw new Exception("缺少主键!");
         *      builder.AppendLine(string.Format("CREATE TABLE {0} (", TableName));
         *      foreach (var item in colomns)
         *      {
         *          string defs = string.Empty;
         *          if (item.Default != null)
         *          {
         *              defs = " DEFAULT((" + item.Default + "))";
         *          }
         *          string len = "";
         *          if (item.Precision == 0) len = "";
         *          else if (item.Precision == -1) len = "(MAX)";
         *          else len = string.Format("({0})", item.Precision);
         *
         *          if (item.TypeName == "varchar" && string.IsNullOrEmpty(len)) len = "(50)";
         *          if (item.Default != null && item.Default == "'auto'")
         *          {
         *              builder.AppendLine(string.Format("{0}  INT IDENTITY (1,1),", item.ColumnName));
         *          }
         *          else
         *          {
         *              builder.AppendLine(string.Format("{0}   {1}{2}  {3} null  {4},", item.ColumnName, item.TypeName, len, (item.IsNull ? "" : " not "), defs));
         *          }
         *
         *          var str = string.Format("EXECUTE sp_addextendedproperty N'MS_Description', '{1}', N'user', N'dbo', N'table', N'{0}', N'column', N'{2}'", TableName, item.Label, item.ColumnName);
         *          descBuilder.AppendLine(str);
         *      }
         *      builder.AppendLine(string.Format(" constraint PK_{0} primary key clustered ({1})); ", TableName, keyCol.ColumnName));
         *  }
         *  // builder.AppendLine(descBuilder.ToString());
         *  var strSql = builder.ToString();
         *  var descSql = descBuilder.ToString();
         *  var defSql = builder2.ToString();
         *  if (!string.IsNullOrEmpty(strSql))
         *  {
         *      var conn = DBClassHelper.OpenConnect(CurrConnectionString, SqlHelperFactory.GetSqlPrividerTypeName(CurrPrividerType));
         *      int count = DBClassHelper.Execute(strSql, conn);
         *      if (!string.IsNullOrEmpty(descSql))
         *      {
         *          DBClassHelper.Execute(descSql, conn);
         *      }
         *      if (!string.IsNullOrEmpty(defSql))
         *      {
         *          DBClassHelper.Execute(defSql, conn);
         *      }
         *      GetSqlTableInfo();
         *      conn.Close();
         *  }
         * }
         */
        internal override FilterQueryResult GetQueryResultFromDB(QueryPageFilter filters)
        {
            FilterQueryResult result   = new FilterQueryResult();
            string            whereStr = GetWhereString(filters);
            string            orderBy  = GetOrderByString(filters);
            string            groupBy  = GetGroupByStrings(filters);
            var    fields = GetQueryFields(filters);
            string sql    = "select count(*) from " + filters.TableName + " where " + whereStr;
            string strSql = string.Empty;

            if (filters.IsPage == true)
            {
                var index = filters.PageSize * (filters.PageIndex - 1);
                if (index < 0)
                {
                    index = 0;
                }
                var keyField       = m_DataBaseKyFieldTableDic[filters.TableName];
                var allFieldsArr   = m_DicTableToTableNameFields[filters.TableName];
                var queryFieldsArr = fields.Replace(" ", "").Split(',');

                if (string.IsNullOrEmpty(keyField) && fields.Replace(" ", "") == "*" && allFieldsArr.ContainsKey("ID"))
                {
                    keyField = "ID";
                }
                if (string.IsNullOrEmpty(keyField) && fields.Replace(" ", "") == "*" && !allFieldsArr.ContainsKey("ID"))
                {
                    keyField = queryFieldsArr.First();
                }
                if (string.IsNullOrEmpty(keyField) && fields.Replace(" ", "") != "*" && queryFieldsArr.Contains("ID"))
                {
                    keyField = "ID";
                }
                if (string.IsNullOrEmpty(keyField) && fields.Replace(" ", "") != "*" && !queryFieldsArr.Contains("ID"))
                {
                    keyField = queryFieldsArr.First();
                }

                if (string.IsNullOrEmpty(orderBy))
                {
                    orderBy = " order by " + keyField + " ASC ";
                }
                else
                {
                    orderBy = orderBy + "," + keyField + " ASC ";
                }

                var pageSize = filters.PageSize;
                if (pageSize == 0)
                {
                    pageSize = 1;
                }
                //分页查询优化,FZP
                strSql = string.Format("select {0} from {1} where {2} {3} offset {4} rows fetch next {5} rows only", fields, filters.TableName, whereStr, orderBy, index, pageSize);
            }
            else
            {
                strSql = "select  " + fields + " from " + filters.TableName + " where " + whereStr + groupBy + orderBy;
            }
            var conn       = DBClassHelper.OpenConnect(CurrConnectionString, SqlHelperFactory.GetSqlPrividerTypeName(CurrPrividerType));
            var tableCount = DBClassHelper.ExecuteQueryToDataTable(sql, conn);
            int count      = int.Parse(tableCount.Rows[0][0].ToString());

            if (filters.IsReturnCount == false)
            {
                var table = DBClassHelper.ExecuteQueryToDataTable(strSql, conn);
                result.Result           = table;
                result.Result.TableName = filters.TableName;
            }
            result.TotalCount = count;
            conn.Close();
            return(result);
        }