Beispiel #1
0
        /// <summary>
        /// 解释EndWith条件
        /// </summary>
        /// <param name="list"></param>
        /// <param name="entityType"></param>
        /// <param name="paramName"></param>
        /// <param name="type"></param>
        /// <param name="lstIndex"></param>
        public static string EndWith(Scope scope, ParamList list, Type entityType, string paramName, DbType type, int lstIndex)
        {
            DBInfo        db            = EntityInfoManager.GetEntityHandle(entityType).DBInfo;
            string        paramVal      = db.CurrentDbAdapter.FormatValueName(DataAccessCommon.FormatParam(paramName, lstIndex));
            string        paramKey      = db.CurrentDbAdapter.FormatParamKeyName(DataAccessCommon.FormatParam(paramName, lstIndex));
            StringBuilder sql           = new StringBuilder(500);
            ScopeType     ctype         = scope.ScopeType;
            string        connectString = DataAccessCommon.GetConnectString(scope);

            if (list != null)
            {
                sql.Append(" ");
                sql.Append(connectString);
                sql.Append(" (" + db.CurrentDbAdapter.FormatParam(paramName) + " like " + db.CurrentDbAdapter.ConcatString("'%'", paramVal) + ")");
                list.AddNew(paramKey, type, scope.Value1);
            }
            else
            {
                sql.Append(" ");
                sql.Append(connectString);
                string curValue = scope.Value1.ToString();
                sql.Append(" (" + db.CurrentDbAdapter.FormatParam(paramName) + " like '%" + scope.Value1 + "')");
            }
            return(sql.ToString());
        }
Beispiel #2
0
        /// <summary>
        /// 解释MoreThen条件
        /// </summary>
        /// <param name="list"></param>
        /// <param name="entityType"></param>
        /// <param name="paramName"></param>
        /// <param name="type"></param>
        /// <param name="lstIndex"></param>
        public static string MoreThen(Scope scope, ParamList list, Type entityType, string paramName, DbType type, int lstIndex)
        {
            DBInfo        db            = EntityInfoManager.GetEntityHandle(entityType).DBInfo;
            string        paramVal      = db.CurrentDbAdapter.FormatValueName(DataAccessCommon.FormatParam(paramName, lstIndex));
            string        paramKey      = db.CurrentDbAdapter.FormatParamKeyName(DataAccessCommon.FormatParam(paramName, lstIndex));
            StringBuilder sql           = new StringBuilder(500);
            ScopeType     ctype         = scope.ScopeType;
            string        connectString = DataAccessCommon.GetConnectString(scope);

            if (list != null)
            {
                sql.Append(" ");
                sql.Append(connectString);
                sql.Append(" (");
                sql.Append(db.CurrentDbAdapter.FormatParam(paramName));
                sql.Append(" >= ");
                sql.Append(paramVal);
                sql.Append(")");
                list.AddNew(paramKey, type, scope.Value1);
            }
            else
            {
                sql.Append(" ");
                sql.Append(connectString);
                sql.Append(" (");
                sql.Append(db.CurrentDbAdapter.FormatParam(paramName));
                sql.Append(" >= ");
                sql.Append(DataAccessCommon.FormatValue(scope.Value1, type, db));
                sql.Append(")");
            }
            return(sql.ToString());
        }
Beispiel #3
0
        /// <summary>
        /// 查询并且返回DataSet(游标分页)
        /// </summary>
        /// <param name="sql">要查询的SQL语句</param>
        /// <param name="objPage">分页对象</param>
        /// <param name="oper">数据库对象</param>
        /// <param name="curType">映射的实体类型(如果用回数据库的原列名,则此为null)</param>
        /// <returns></returns>
        public static DataTable QueryDataTable(string sql, PageContent objPage, DataBaseOperate oper, Type curType)
        {
            DataTable ret       = new DataTable();
            ParamList lstParams = new ParamList();

            lstParams.AddNew("@sql", DbType.AnsiString, sql);
            lstParams.AddNew("@currentIndex", DbType.Int32, objPage.GetStarIndex() + 1);
            lstParams.AddNew("@pagesize", DbType.Int32, objPage.PageSize);
            lstParams.AddNew("@maxRecords", DbType.Int64, objPage.MaxSelectRecords);
            IDataReader reader = null;

            try
            {
                InitProc(oper);
                reader = oper.Query(ProcName, lstParams, CommandType.StoredProcedure, null);
                if (reader.NextResult())//第二个结果集为查询结果
                {
                    if (curType == null)
                    {
                        ret = CacheReader.GenerateDataTable(reader, "newDt", false);
                    }
                    else
                    {
                        ret = CacheReader.GenerateDataTable(reader, "newDt", curType, false);
                    }
                }
                if (reader.NextResult())//第三个结果集为总行数
                {
                    if (reader.Read())
                    {
                        int totalRecord = reader.GetInt32(0);
                        objPage.TotalRecords = totalRecord;
                        //int totalPage = (int)Math.Ceiling((double)objPage.TotalRecords / (double)objPage.PageSize);
                        //objPage.TotalPage = totalPage;
                        if (objPage.CurrentPage >= objPage.TotalPage - 1)
                        {
                            objPage.CurrentPage = objPage.TotalPage - 1;
                        }
                    }
                }
            }
            finally
            {
                reader.Close();
            }
            return(ret);
        }
Beispiel #4
0
        /// <summary>
        /// 获取所有关系
        /// </summary>
        /// <param name="oper"> </param>
        /// <param name="info"> </param>
        /// <param name="childNames">null则查询所有表</param>
        /// <returns></returns>
        public List <TableRelationAttribute> GetRelation(DataBaseOperate oper, DBInfo info, IEnumerable <string> childNames)
        {
            string        sql   = "SELECT t1.CONSTRAINT_NAME,t1.TABLE_NAME, t1.COLUMN_NAME, t1.POSITION_IN_UNIQUE_CONSTRAINT,  t1.REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE t1  INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS t2  ON t2.TABLE_SCHEMA = t1.TABLE_SCHEMA  AND t2.TABLE_NAME = t1.TABLE_NAME  AND t2.CONSTRAINT_NAME = t1.CONSTRAINT_NAME WHERE t1.TABLE_SCHEMA = ?dbName  AND t2.CONSTRAINT_TYPE = 'FOREIGN KEY'";
            StringBuilder sqlFk = new StringBuilder();
            StringBuilder sqlPk = new StringBuilder();

            //sql.Append("SELECT constraint_schema,constraint_name,unique_constraint_name,table_name,referenced_table_name FROM `information_schema`.`REFERENTIAL_CONSTRAINTS`;");
            sqlFk.Append(sql);
            sqlPk.Append(sql);
            ParamList lstParam = new ParamList();

            lstParam.AddNew("?dbName", DbType.String, oper.DataBaseName);
            string childName = Buffalo.DB.DataBaseAdapter.SqlServer2KAdapter.DBStructure.AllInTableNames(childNames);

            if (!string.IsNullOrEmpty(childName))
            {
                sqlFk.Append(" and t1.TABLE_NAME in(" + childName + ")");
            }
            if (!string.IsNullOrEmpty(childName))
            {
                sqlPk.Append(" and t1.REFERENCED_TABLE_NAME in(" + childName + ")");
            }
            List <TableRelationAttribute> lst = new List <TableRelationAttribute>();

            using (IDataReader reader = info.DefaultOperate.Query(sqlFk.ToString(), lstParam, null))
            {
                while (reader.Read())
                {
                    TableRelationAttribute tinfo = new TableRelationAttribute();
                    tinfo.Name        = reader["CONSTRAINT_NAME"] as string;
                    tinfo.SourceTable = reader["TABLE_NAME"] as string;
                    tinfo.SourceName  = reader["COLUMN_NAME"] as string;
                    tinfo.TargetTable = reader["REFERENCED_TABLE_NAME"] as string;
                    tinfo.TargetName  = reader["REFERENCED_COLUMN_NAME"] as string;
                    tinfo.IsParent    = true;
                    lst.Add(tinfo);
                }
            }
            using (IDataReader reader = info.DefaultOperate.Query(sqlPk.ToString(), lstParam, null))
            {
                while (reader.Read())
                {
                    TableRelationAttribute tinfo = new TableRelationAttribute();
                    tinfo.Name        = reader["CONSTRAINT_NAME"] as string;
                    tinfo.TargetTable = reader["TABLE_NAME"] as string;
                    tinfo.TargetName  = reader["COLUMN_NAME"] as string;
                    tinfo.SourceTable = reader["REFERENCED_TABLE_NAME"] as string;
                    tinfo.SourceName  = reader["REFERENCED_COLUMN_NAME"] as string;
                    tinfo.IsParent    = false;
                    lst.Add(tinfo);
                }
            }
            return(lst);
        }
Beispiel #5
0
        /// <summary>
        /// 返回Like的查询字符串
        /// </summary>
        /// <param name="scope">条件类</param>
        /// <param name="list">参数列表</param>
        /// <param name="paranName">所属的字段名</param>
        /// <param name="type">当前的数据库类型</param>
        /// <param name="lstIndex">当前索引的标识未辨别同名字段的参数,可设置为0</param>
        /// <param name="entityType">当前实体的类型</param>
        /// <param name="connectString">条件连接的字符串</param>
        /// <param name="isFreeText">是否全文检索</param>
        /// <returns></returns>
        public static string GetLikeSql(Scope scope, ParamList list, string paranName, DbType type, int lstIndex, Type entityType, string connectString, bool isFreeText)
        {
            //string fullName = entityType.FullName + "." + scope.PropertyName;
            DBInfo db       = EntityInfoManager.GetEntityHandle(entityType).DBInfo;
            string ret      = " " + connectString;
            string paramVal = db.CurrentDbAdapter.FormatValueName(DataAccessCommon.FormatParam(paranName, lstIndex));
            string paramKey = db.CurrentDbAdapter.FormatParamKeyName(DataAccessCommon.FormatParam(paranName, lstIndex));

            if (isFreeText && !NoiseWord.IsNoiseWord(scope.Value1.ToString()))
            {
                if (list != null)
                {
                    ret += db.CurrentDbAdapter.FreeTextLike(paranName, paramVal);

                    list.AddNew(paramKey, type, scope.Value1);
                }
                else
                {
                    ret += db.CurrentDbAdapter.FreeTextLike(paranName, DataAccessCommon.FormatValue(scope.Value1, type, db));
                }
            }
            else
            {
                if (list != null)
                {
                    string curValue = scope.Value1.ToString();
                    curValue = FilterLikeValue(curValue);
                    ret     += " (" + db.CurrentDbAdapter.FormatParam(paranName) + " like " + db.CurrentDbAdapter.ConcatString("'%'", paramVal, "'%'") + ")";
                    list.AddNew(paramKey, type, curValue);
                }
                else
                {
                    string curValue = scope.Value1.ToString();
                    curValue = FilterLikeValue(curValue);
                    ret     += " (" + db.CurrentDbAdapter.FormatParam(paranName) + " like '%" + curValue + "%')";
                }
            }
            return(ret);
        }
Beispiel #6
0
        /// <summary>
        /// 查询并且返回集合(游标分页)
        /// </summary>
        /// <param name="sql">要查询的SQL语句</param>
        /// <param name="objPage">分页对象</param>
        /// <param name="oper">数据库对象</param>
        /// <returns></returns>
        public static IDataReader Query(string sql, PageContent objPage, DataBaseOperate oper)
        {
            ParamList lstParams = new ParamList();

            lstParams.AddNew("@sql", DbType.AnsiString, sql);
            lstParams.AddNew("@currentIndex", DbType.Int32, objPage.GetStarIndex() + 1);
            lstParams.AddNew("@pagesize", DbType.Int32, objPage.PageSize);
            lstParams.AddNew("@maxRecords", DbType.Int64, objPage.MaxSelectRecords);
            //lstParams.AddNew("@@total", DbType.Int64, 0, ParameterDirection.Output);
            IDataReader reader = null;

            InitProc(oper);
            reader = oper.Query(ProcName, lstParams, CommandType.StoredProcedure, null);
            if (reader.NextResult())//第二个结果集为查询记录数
            {
                if (objPage.IsFillTotalRecords)
                {
                    if (reader.Read())
                    {
                        int totalRecord = reader.GetInt32(0);
                        objPage.TotalRecords = totalRecord;
                        //int totalPage = (int)Math.Ceiling((double)objPage.TotalRecords / (double)objPage.PageSize);
                        //objPage.TotalPage = totalPage;
                        if (objPage.CurrentPage >= objPage.TotalPage - 1)
                        {
                            objPage.CurrentPage = objPage.TotalPage - 1;
                        }
                    }
                }
            }

            if (reader.NextResult())//第三个结果集为真正记录
            {
                return(reader);
            }

            return(null);
        }
Beispiel #7
0
        /// <summary>
        /// 获取表信息
        /// </summary>
        /// <param name="oper"></param>
        /// <param name="info"></param>
        /// <param name="tableNames"></param>
        /// <returns></returns>
        public List <DBTableInfo> GetTablesInfo(DataBaseOperate oper, DBInfo info, IEnumerable <string> tableNames)
        {
            string inTable = Buffalo.DB.DataBaseAdapter.SqlServer2KAdapter.DBStructure.AllInTableNames(tableNames);
            string sql     = "SELECT t1.TABLE_NAME,t1.COLUMN_NAME,t1.COLUMN_COMMENT, t1.DATA_TYPE, t1.CHARACTER_OCTET_LENGTH, t1.NUMERIC_PRECISION, t1.NUMERIC_SCALE, CASE t1.IS_NULLABLE WHEN 'NO' THEN 0 ELSE 1 END IS_NULLABLE, t1.COLUMN_TYPE,t1.COLUMN_KEY,t1.EXTRA FROM INFORMATION_SCHEMA.COLUMNS t1 where t1.TABLE_SCHEMA = ?dbName";

            if (!string.IsNullOrEmpty(inTable))
            {
                sql += " and t1.TABLE_NAME in(" + inTable + ")";
            }
            Dictionary <string, DBTableInfo> dicTables = new Dictionary <string, DBTableInfo>();
            ParamList lstParam = new ParamList();

            lstParam.AddNew("?dbName", DbType.String, oper.DataBaseName);

            List <DBTableInfo> tables = GetTableNames(oper, info, tableNames);

            foreach (DBTableInfo table in tables)
            {
                dicTables[table.Name] = table;
                table.Params          = new List <EntityParam>();
                table.RelationItems   = new List <TableRelationAttribute>();
            }

            using (IDataReader reader = oper.Query(sql.ToString(), lstParam, null))
            {
                while (reader.Read())
                {
                    string tableName = reader["TABLE_NAME"] as string;
                    if (string.IsNullOrEmpty(tableName))
                    {
                        continue;
                    }
                    DBTableInfo table = null;
                    dicTables.TryGetValue(tableName, out table);
                    if (table == null)
                    {
                        continue;
                    }
                    FillParam(table, reader);
                }
            }
            List <TableRelationAttribute> lstRelation = GetRelation(oper, info, tableNames);

            Buffalo.DB.DataBaseAdapter.SqlServer2KAdapter.DBStructure.FillRelation(dicTables, lstRelation);

            return(tables);
        }
        /// <summary>
        /// 填充版本控制的信息
        /// </summary>
        /// <param name="where"></param>
        /// <param name="where"></param>
        /// <param name="info"></param>
        /// <param name="list"></param>
        /// <param name="curValue"></param>
        protected internal void FillWhereConcurrency(StringBuilder where,
                                                     EntityPropertyInfo info, ParamList list, object curValue, ref int index)
        {
            string paramValW = EntityInfo.DBInfo.CurrentDbAdapter.FormatValueName(DataAccessCommon.FormatParam(info.ParamName, index));
            string paramKeyW = EntityInfo.DBInfo.CurrentDbAdapter.FormatParamKeyName(DataAccessCommon.FormatParam(info.ParamName, index));

            index++;
            if (DefaultType.IsDefaultValue(curValue))
            {
                throw new Exception("版本控制字段:" + info.PropertyName + " 必须有当前版本值");
            }
            where.Append(" and ");
            where.Append(EntityInfo.DBInfo.CurrentDbAdapter.FormatParam(info.ParamName));
            where.Append("=");
            where.Append(paramValW);
            list.AddNew(paramKeyW, info.SqlType, curValue);
        }
        private object FillUpdateConcurrency(StringBuilder sql,
                                             EntityPropertyInfo info, ParamList list, object curValue, ref int index)
        {
            object newValue = NewConcurrencyValue(curValue);

            if (newValue != null)
            {
                string paramValV = EntityInfo.DBInfo.CurrentDbAdapter.FormatValueName(DataAccessCommon.FormatParam(info.ParamName, index));
                string paramKeyV = EntityInfo.DBInfo.CurrentDbAdapter.FormatParamKeyName(DataAccessCommon.FormatParam(info.ParamName, index));

                sql.Append(",");
                sql.Append(EntityInfo.DBInfo.CurrentDbAdapter.FormatParam(info.ParamName));
                sql.Append("=");
                sql.Append(paramValV);
                list.AddNew(paramKeyV, info.SqlType, newValue);
                index++;
            }
            return(newValue);
        }
Beispiel #10
0
        /// <summary>
        /// 重建参数集合
        /// </summary>
        /// <param name="lstPrm"></param>
        /// <returns></returns>
        public virtual ParamList RebuildParamList(ref string sql, ParamList lstPrm)
        {
            ParamList     lstRet = new ParamList();
            StringBuilder newSql = new StringBuilder();
            Dictionary <string, DBParameter> dicPrm = new Dictionary <string, DBParameter>();

            foreach (DBParameter prm in lstPrm)
            {
                dicPrm[prm.ParameterName] = prm;
            }
            Queue <RebuildParamInfo> queStrPrm = FindAllParams(sql);
            DBParameter curPrm = null;

            RebuildParamInfo curprmInfo = null;

            if (queStrPrm.Count > 0)
            {
                curprmInfo = queStrPrm.Dequeue();
            }
            for (int i = 0; i < sql.Length; i++)
            {
                if (curprmInfo != null && curprmInfo.Index == i)
                {
                    if (dicPrm.TryGetValue(curprmInfo.ParamName, out curPrm))
                    {
                        string      pName  = "P" + lstRet.Count;
                        DBParameter newPrm = lstRet.AddNew(FormatParamKeyName(pName), curPrm.DbType, curPrm.Value, curPrm.Direction);
                        newPrm.ValueName = FormatValueName(pName);
                        newSql.Append(newPrm.ValueName);
                        i += curprmInfo.ParamName.Length - 1;

                        if (queStrPrm.Count > 0)
                        {
                            curprmInfo = queStrPrm.Dequeue();
                        }
                        continue;
                    }
                }
                newSql.Append(sql[i]);
            }
            sql = newSql.ToString();
            return(lstRet);
        }
Beispiel #11
0
        /// <summary>
        /// 获取所有用户表
        /// </summary>
        /// <param name="oper"></param>
        /// <param name="info"></param>
        /// <returns></returns>
        private List <DBTableInfo> GetTableNames(DataBaseOperate oper, DBInfo info, IEnumerable <string> tableNames)
        {
            ParamList lstParam = new ParamList();

            lstParam.AddNew("?dbName", DbType.String, oper.DataBaseName);
            List <DBTableInfo> lstName = new List <DBTableInfo>();
            string             inTable = Buffalo.DB.DataBaseAdapter.SqlServer2KAdapter.DBStructure.AllInTableNames(tableNames);
            string             sql     = _sqlTables;

            if (!string.IsNullOrEmpty(inTable))
            {
                sql += " and TABLE_NAME in(" + inTable + ")";
            }

            using (IDataReader reader = oper.Query(sql, lstParam, null))
            {
                while (reader.Read())
                {
                    DBTableInfo tableInfo = new DBTableInfo();
                    tableInfo.Name = reader[0] as string;
                    string type = reader[1] as string;
                    if (!string.IsNullOrEmpty(type))
                    {
                        if (type.Trim() == "VIEW")
                        {
                            tableInfo.IsView = true;
                        }
                    }
                    string comment = reader[2] as string;
                    tableInfo.Description = comment;

                    lstName.Add(tableInfo);
                }
            }
            return(lstName);
        }