Example #1
0
        /// <summary>
        /// 获得链接的配置或语句。
        /// </summary>
        /// <param name="nameOrSql">表名、视图名、存储过程名</param>
        /// <returns></returns>
        public static string GetConn(string nameOrSql, out string fixName)
        {
            string firstTableName = null;
            string conn           = null;

            fixName = nameOrSql;
            int index = nameOrSql.LastIndexOf(')');

            if (nameOrSql.IndexOf(' ') == -1)//单表。
            {
                #region 单表
                if (nameOrSql.IndexOf('.') > -1) //dbname.tablename
                {
                    string[] items = nameOrSql.Split('.');
                    conn    = items[0] + "Conn";
                    fixName = items[items.Length - 1];
                }
                else
                {
                    firstTableName = nameOrSql;
                }
                #endregion
            }
            else if (index == -1)
            {
                //sql 语句
                firstTableName = GetFirstTableNameFromSql(nameOrSql);
                fixName        = SqlCreate.SqlToViewSql(nameOrSql);//Sql修正为视图
            }
            else// 视图
            {
                string viewSQL  = nameOrSql;
                string startSql = viewSQL.Substring(0, index + 1);   //a部分
                viewSQL = viewSQL.Substring(index + 1).Trim();       //b部分。ddd.v_xxx
                if (viewSQL.Contains(".") && !viewSQL.Contains(" ")) //修改原对像
                {
                    string[] items = viewSQL.Split('.');
                    fixName = startSql + " " + items[items.Length - 1];
                    conn    = items[0] + "Conn";
                }
                else
                {
                    firstTableName = GetFirstTableNameFromSql(startSql);
                }
            }
            if (!string.IsNullOrEmpty(firstTableName))
            {
                TableInfo info = GetTableInfoByName(firstTableName);
                if (info != null && info.Parent != null)
                {
                    if (nameOrSql == firstTableName)
                    {
                        fixName = info.Name;
                    }
                    conn = info.Parent.ConnName;
                }
            }
            return(conn);
        }
Example #2
0
        public static string GetSql(DataBaseType dalType, string version, int pageIndex, int pageSize, object objWhere, string tableName, int rowCount, string columns, string primaryKey, bool primaryKeyIsidentity)
        {
            if (string.IsNullOrEmpty(columns))
            {
                columns = "*";
            }
            pageIndex    = pageIndex == 0 ? 1 : pageIndex;
            string where = SqlFormat.GetIFieldSql(objWhere);
            if (string.IsNullOrEmpty(where))
            {
                where = "1=1";
            }
            if (pageSize == 0)
            {
                return(string.Format(top1Pager, columns, tableName, where));
            }
            if (rowCount > 0)//分页查询。
            {
                where = SqlCreate.AddOrderBy(where, primaryKey);
            }
            int    topN     = pageIndex * pageSize;//Top N 最大数
            int    max      = (pageIndex - 1) * pageSize;
            int    rowStart = (pageIndex - 1) * pageSize + 1;
            int    rowEnd   = rowStart + pageSize - 1;
            string orderBy  = string.Empty;

            if (pageIndex == 1 && dalType != DataBaseType.Oracle)//第一页(oracle时 rownum 在排序条件为非数字时,和row_number()的不一样,会导致结果差异,所以分页统一用row_number()。)
            {
                switch (dalType)
                {
                case DataBaseType.Access:
                case DataBaseType.MsSql:
                case DataBaseType.Sybase:
                case DataBaseType.Txt:
                case DataBaseType.Xml:
                    return(string.Format(top1Pager, "top " + pageSize + " " + columns, tableName, where));

                //case DalType.Oracle:
                //    return string.Format(top1Pager, columns, tableName, "rownum<=" + pageSize + " and " + where);
                case DataBaseType.SQLite:
                case DataBaseType.MySql:
                case DataBaseType.PostgreSQL:
                    return(string.Format(top1Pager, columns, tableName, where + " limit " + pageSize));
                }
            }
            else
            {
                switch (dalType)
                {
                case DataBaseType.Access:
                case DataBaseType.MsSql:
                case DataBaseType.Sybase:
                    int leftNum   = rowCount % pageSize;
                    int pageCount = leftNum == 0 ? rowCount / pageSize : rowCount / pageSize + 1;                                                                                                                             //页数
                    if (pageIndex == pageCount && dalType != DataBaseType.Sybase)                                                                                                                                             // 最后一页Sybase 不支持双Top order by
                    {
                        return(string.Format(top2Pager, pageSize + " " + columns, "top " + (leftNum == 0 ? pageSize : leftNum) + " * ", tableName, ReverseOrderBy(where, primaryKey), GetOrderBy(where, false, primaryKey))); //反序
                    }
                    if ((pageCount > 1000 || rowCount > 100000) && pageIndex > pageCount / 2)                                                                                                                                 // 页数过后半段,反转查询
                    {
                        orderBy = GetOrderBy(where, false, primaryKey);
                        if (dalType != DataBaseType.MsSql)             //mssql是用rownumber,不用反转,
                        {
                            where = ReverseOrderBy(where, primaryKey); //事先反转一次。
                        }
                        topN = rowCount - max;                         //取后面的
                        int rowStartTemp = rowCount - rowEnd;
                        rowEnd   = rowCount - rowStart + 1;            //网友反馈修正(数据行要+1)
                        rowStart = rowStartTemp + 1;                   //网友反馈修正(数据行要+1)
                    }
                    break;

                case DataBaseType.Txt:
                case DataBaseType.Xml:
                    return(string.Format(top1Pager, columns, tableName, where + " limit " + pageSize + " offset " + pageIndex));
                }
            }


            switch (dalType)
            {
            case DataBaseType.MsSql:
            case DataBaseType.Oracle:
                if (version.StartsWith("08"))
                {
                    goto temtable;
                    // goto top3;//sql 2000
                }
                int index = tableName.LastIndexOf(')');
                if (index > 0)
                {
                    tableName = tableName.Substring(0, index + 1);
                }
                string v         = dalType == DataBaseType.Oracle ? "" : " v";
                string onlyWhere = "where " + SqlCreate.RemoveOrderBy(where);
                onlyWhere = SqlFormat.RemoveWhereOneEqualsOne(onlyWhere);
                return(string.Format(rowNumberPager, GetOrderBy(where, false, primaryKey), (columns == "*" ? "t.*" : columns), tableName, onlyWhere, v, rowStart, rowEnd));

            case DataBaseType.Sybase:
temtable:
                if (primaryKeyIsidentity)
                {
                    bool isOk = columns == "*";
                    if (!isOk)
                    {
                        string   kv    = SqlFormat.NotKeyword(primaryKey);
                        string[] items = columns.Split(',');
                        foreach (string item in items)
                        {
                            if (string.Compare(SqlFormat.NotKeyword(item), kv, StringComparison.OrdinalIgnoreCase) == 0)
                            {
                                isOk = true;
                                break;
                            }
                        }
                    }
                    else
                    {
                        columns = "t.*";
                        index   = tableName.LastIndexOf(')');
                        if (index > 0)
                        {
                            tableName = tableName.Substring(0, index + 1);
                        }
                        tableName += " t ";
                    }
                    if (isOk)
                    {
                        return(string.Format(tempTablePagerWithidentity, DateTime.Now.Millisecond, topN, primaryKey, tableName, where, pageSize, columns, rowStart, rowEnd, orderBy));
                    }
                }
                return(string.Format(tempTablePager, DateTime.Now.Millisecond, pageIndex * pageSize + " " + columns, tableName, where, pageSize, rowStart, rowEnd, orderBy));

            case DataBaseType.Access:
top3:
                if (!string.IsNullOrEmpty(orderBy))     // 反转查询
                {
                    return(string.Format(top4Pager, columns, (rowCount - max > pageSize ? pageSize : rowCount - max), topN, tableName, where, GetOrderBy(where, true, primaryKey), GetOrderBy(where, false, primaryKey), orderBy));
                }
                return(string.Format(top3Pager, (rowCount - max > pageSize ? pageSize : rowCount - max), columns, topN, tableName, where, GetOrderBy(where, true, primaryKey), GetOrderBy(where, false, primaryKey)));

            case DataBaseType.SQLite:
            case DataBaseType.MySql:
            case DataBaseType.PostgreSQL:
                if (max > 500000 && primaryKeyIsidentity && Convert.ToString(objWhere) == "" && !tableName.Contains(" "))    //单表大数量时的优化成主键访问。
                {
                    where = string.Format("{0}>=(select {0} from {1} limit {2}, 1) limit {3}", primaryKey, tableName, max, pageSize);
                    return(string.Format(top1Pager, columns, tableName, where));
                }
                return(string.Format(top1Pager, columns, tableName, where + " limit " + pageSize + " offset " + max));
            }
            return((string)Error.Throw("Pager::No Be Support:" + dalType.ToString()));
        }
Example #3
0
        /// <summary>
        /// 获得最优(可能会切换数据库)链接的配置或语句。
        /// </summary>
        /// <param name="nameOrSql">表名、视图名、存储过程名</param>
        /// <returns></returns>
        public static string GetConn(string nameOrSql, out string fixName, string priorityConn, out string dbName)
        {
            string firstTableName = null;
            string conn           = null;

            nameOrSql = nameOrSql.Trim();
            fixName   = nameOrSql;
            dbName    = string.Empty;
            if (nameOrSql.IndexOf(' ') == -1)//单表。
            {
                #region 单表
                if (nameOrSql.IndexOf('.') > -1) //dbname.tablename
                {
                    string[] items = nameOrSql.Split('.');
                    dbName  = items[0];
                    conn    = dbName + "Conn";
                    fixName = SqlFormat.NotKeyword(items[items.Length - 1]);
                }
                else
                {
                    firstTableName = SqlFormat.NotKeyword(nameOrSql);
                }
                #endregion
            }
            else
            {
                if (nameOrSql[0] == '(')  // 视图
                {
                    int    index    = nameOrSql.LastIndexOf(')');
                    string viewSQL  = nameOrSql;
                    string startSql = viewSQL.Substring(0, index + 1);   //a部分
                    viewSQL = viewSQL.Substring(index + 1).Trim();       //b部分。ddd.v_xxx
                    if (viewSQL.Contains(".") && !viewSQL.Contains(" ")) //修改原对像
                    {
                        string[] items = viewSQL.Split('.');
                        fixName = startSql + " " + items[items.Length - 1];
                        conn    = items[0] + "Conn";
                    }
                    else
                    {
                        firstTableName = GetFirstTableNameFromSql(startSql);
                    }
                }
                else
                {
                    //sql 语句
                    firstTableName = GetFirstTableNameFromSql(nameOrSql);
                    fixName        = SqlCreate.SqlToViewSql(nameOrSql);//Sql修正为视图
                }
            }
            if (!string.IsNullOrEmpty(firstTableName))
            {
                TableInfo info = GetTableInfoByName(firstTableName, priorityConn);
                if (info != null && info.DBInfo != null)
                {
                    if (nameOrSql == firstTableName)
                    {
                        fixName = info.Name;
                    }
                    conn = info.DBInfo.ConnName;
                }
            }
            return(string.IsNullOrEmpty(conn) ? priorityConn : conn);
        }
Example #4
0
        public static MDataColumn GetColumns(string tableName, ref DbBase dbHelper)
        {
            tableName = Convert.ToString(SqlCreate.SqlToViewSql(tableName));
            DalType dalType = dbHelper.dalType;

            tableName = SqlFormat.Keyword(tableName, dbHelper.dalType);

            string key = GetSchemaKey(tableName, dbHelper.DataBase, dbHelper.dalType);

            if (CacheManage.LocalInstance.Contains(key))//缓存里获取
            {
                return(CacheManage.LocalInstance.Get <MDataColumn>(key).Clone());
            }
            switch (dalType)
            {
            case DalType.SQLite:
            case DalType.MySql:
                tableName = SqlFormat.NotKeyword(tableName);
                break;

            case DalType.Txt:
            case DalType.Xml:
                tableName = Path.GetFileNameWithoutExtension(tableName);    //视图表,带“.“的,会出问题
                string      fileName = dbHelper.Con.DataSource + tableName + (dalType == DalType.Txt ? ".txt" : ".xml");
                MDataColumn mdc      = MDataColumn.CreateFrom(fileName);
                mdc.dalType = dalType;
                return(mdc);
            }

            MDataColumn mdcs = new MDataColumn();

            mdcs.dalType = dbHelper.dalType;
            //如果table和helper不在同一个库
            DbBase helper = dbHelper.ResetDbBase(tableName);

            helper.IsAllowRecordSql = false;//内部系统,不记录SQL表结构语句。
            try
            {
                bool isView = tableName.Contains(" ");//是否视图。
                if (!isView)
                {
                    isView = Exists("V", tableName, ref helper);
                }
                MCellStruct mStruct = null;
                SqlDbType   sqlType = SqlDbType.NVarChar;
                if (isView)
                {
                    string sqlText = SqlFormat.BuildSqlWithWhereOneEqualsTow(tableName);// string.Format("select * from {0} where 1=2", tableName);
                    mdcs = GetViewColumns(sqlText, ref helper);
                }
                else
                {
                    mdcs.AddRelateionTableName(SqlFormat.NotKeyword(tableName));
                    switch (dalType)
                    {
                    case DalType.MsSql:
                    case DalType.Oracle:
                    case DalType.MySql:
                    case DalType.Sybase:
                        #region Sql
                        string sql = string.Empty;
                        if (dalType == DalType.MsSql)
                        {
                            string dbName = null;
                            if (!helper.Version.StartsWith("08"))
                            {
                                //先获取同义词,检测是否跨库
                                string realTableName = Convert.ToString(helper.ExeScalar(string.Format(SynonymsName, SqlFormat.NotKeyword(tableName)), false));
                                if (!string.IsNullOrEmpty(realTableName))
                                {
                                    string[] items = realTableName.Split('.');
                                    tableName = realTableName;
                                    if (items.Length > 0)    //跨库了
                                    {
                                        dbName = realTableName.Split('.')[0];
                                    }
                                }
                            }

                            sql = GetMSSQLColumns(helper.Version.StartsWith("08"), dbName ?? helper.DataBase);
                        }
                        else if (dalType == DalType.MySql)
                        {
                            sql = GetMySqlColumns(helper.DataBase);
                        }
                        else if (dalType == DalType.Oracle)
                        {
                            sql = GetOracleColumns();
                        }
                        else if (dalType == DalType.Sybase)
                        {
                            tableName = SqlFormat.NotKeyword(tableName);
                            sql       = GetSybaseColumns();
                        }
                        helper.AddParameters("TableName", tableName, DbType.String, 150, ParameterDirection.Input);
                        DbDataReader sdr = helper.ExeDataReader(sql, false);
                        if (sdr != null)
                        {
                            long   maxLength;
                            bool   isAutoIncrement = false;
                            short  scale           = 0;
                            string sqlTypeName     = string.Empty;
                            while (sdr.Read())
                            {
                                short.TryParse(Convert.ToString(sdr["Scale"]), out scale);
                                if (!long.TryParse(Convert.ToString(sdr["MaxSize"]), out maxLength))    //mysql的长度可能大于int.MaxValue
                                {
                                    maxLength = -1;
                                }
                                else if (maxLength > int.MaxValue)
                                {
                                    maxLength = int.MaxValue;
                                }
                                sqlTypeName             = Convert.ToString(sdr["SqlType"]);
                                sqlType                 = DataType.GetSqlType(sqlTypeName);
                                isAutoIncrement         = Convert.ToBoolean(sdr["IsAutoIncrement"]);
                                mStruct                 = new MCellStruct(mdcs.dalType);
                                mStruct.ColumnName      = Convert.ToString(sdr["ColumnName"]).Trim();
                                mStruct.OldName         = mStruct.ColumnName;
                                mStruct.SqlType         = sqlType;
                                mStruct.IsAutoIncrement = isAutoIncrement;
                                mStruct.IsCanNull       = Convert.ToBoolean(sdr["IsNullable"]);
                                mStruct.MaxSize         = (int)maxLength;
                                mStruct.Scale           = scale;
                                mStruct.Description     = Convert.ToString(sdr["Description"]);
                                mStruct.DefaultValue    = SqlFormat.FormatDefaultValue(dalType, sdr["DefaultValue"], 0, sqlType);
                                mStruct.IsPrimaryKey    = Convert.ToString(sdr["IsPrimaryKey"]) == "1";
                                switch (dalType)
                                {
                                case DalType.MsSql:
                                case DalType.MySql:
                                case DalType.Oracle:
                                    mStruct.IsUniqueKey  = Convert.ToString(sdr["IsUniqueKey"]) == "1";
                                    mStruct.IsForeignKey = Convert.ToString(sdr["IsForeignKey"]) == "1";
                                    mStruct.FKTableName  = Convert.ToString(sdr["FKTableName"]);
                                    break;
                                }

                                mStruct.SqlTypeName = sqlTypeName;
                                mStruct.TableName   = SqlFormat.NotKeyword(tableName);
                                mdcs.Add(mStruct);
                            }
                            sdr.Close();
                            if (dalType == DalType.Oracle && mdcs.Count > 0)    //默认没有自增概念,只能根据情况判断。
                            {
                                MCellStruct firstColumn = mdcs[0];
                                if (firstColumn.IsPrimaryKey && firstColumn.ColumnName.ToLower().Contains("id") && firstColumn.Scale == 0 && DataType.GetGroup(firstColumn.SqlType) == 1 && mdcs.JointPrimary.Count == 1)
                                {
                                    firstColumn.IsAutoIncrement = true;
                                }
                            }
                        }
                        #endregion
                        break;

                    case DalType.SQLite:
                        #region SQlite
                        if (helper.Con.State != ConnectionState.Open)
                        {
                            helper.Con.Open();
                        }
                        DataTable sqliteDt = helper.Con.GetSchema("Columns", new string[] { null, null, tableName });
                        if (!helper.isOpenTrans)
                        {
                            helper.Con.Close();
                        }
                        int    size;
                        short  sizeScale;
                        string dataTypeName = string.Empty;

                        foreach (DataRow row in sqliteDt.Rows)
                        {
                            object len = row["NUMERIC_PRECISION"];
                            if (len == null)
                            {
                                len = row["CHARACTER_MAXIMUM_LENGTH"];
                            }
                            short.TryParse(Convert.ToString(row["NUMERIC_SCALE"]), out sizeScale);
                            if (!int.TryParse(Convert.ToString(len), out size))    //mysql的长度可能大于int.MaxValue
                            {
                                size = -1;
                            }
                            dataTypeName = Convert.ToString(row["DATA_TYPE"]);
                            if (dataTypeName == "text" && size > 0)
                            {
                                sqlType = DataType.GetSqlType("varchar");
                            }
                            else
                            {
                                sqlType = DataType.GetSqlType(dataTypeName);
                            }
                            //COLUMN_NAME,DATA_TYPE,PRIMARY_KEY,IS_NULLABLE,CHARACTER_MAXIMUM_LENGTH AUTOINCREMENT

                            mStruct              = new MCellStruct(row["COLUMN_NAME"].ToString(), sqlType, Convert.ToBoolean(row["AUTOINCREMENT"]), Convert.ToBoolean(row["IS_NULLABLE"]), size);
                            mStruct.Scale        = sizeScale;
                            mStruct.Description  = Convert.ToString(row["DESCRIPTION"]);
                            mStruct.DefaultValue = SqlFormat.FormatDefaultValue(dalType, row["COLUMN_DEFAULT"], 0, sqlType);    //"COLUMN_DEFAULT"
                            mStruct.IsPrimaryKey = Convert.ToBoolean(row["PRIMARY_KEY"]);
                            mStruct.SqlTypeName  = dataTypeName;
                            mStruct.TableName    = SqlFormat.NotKeyword(tableName);
                            mdcs.Add(mStruct);
                        }
                        #endregion
                        break;

                    case DalType.Access:
                        #region Access
                        DataTable       keyDt, valueDt;
                        string          sqlText = SqlFormat.BuildSqlWithWhereOneEqualsTow(tableName);// string.Format("select * from {0} where 1=2", tableName);
                        OleDbConnection con     = new OleDbConnection(helper.Con.ConnectionString);
                        OleDbCommand    com     = new OleDbCommand(sqlText, con);
                        con.Open();
                        keyDt   = com.ExecuteReader(CommandBehavior.KeyInfo).GetSchemaTable();
                        valueDt = con.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, new object[] { null, null, SqlFormat.NotKeyword(tableName) });
                        con.Close();
                        con.Dispose();

                        if (keyDt != null && valueDt != null)
                        {
                            string    columnName = string.Empty, sqlTypeName = string.Empty;
                            bool      isKey = false, isCanNull = true, isAutoIncrement = false;
                            int       maxSize      = -1;
                            short     maxSizeScale = 0;
                            SqlDbType sqlDbType;
                            foreach (DataRow row in keyDt.Rows)
                            {
                                columnName      = row["ColumnName"].ToString();
                                isKey           = Convert.ToBoolean(row["IsKey"]);       //IsKey
                                isCanNull       = Convert.ToBoolean(row["AllowDBNull"]); //AllowDBNull
                                isAutoIncrement = Convert.ToBoolean(row["IsAutoIncrement"]);
                                sqlTypeName     = Convert.ToString(row["DataType"]);
                                sqlDbType       = DataType.GetSqlType(sqlTypeName);
                                short.TryParse(Convert.ToString(row["NumericScale"]), out maxSizeScale);
                                if (Convert.ToInt32(row["NumericPrecision"]) > 0)    //NumericPrecision
                                {
                                    maxSize = Convert.ToInt32(row["NumericPrecision"]);
                                }
                                else
                                {
                                    long len = Convert.ToInt64(row["ColumnSize"]);
                                    if (len > int.MaxValue)
                                    {
                                        maxSize = int.MaxValue;
                                    }
                                    else
                                    {
                                        maxSize = (int)len;
                                    }
                                }
                                mStruct              = new MCellStruct(columnName, sqlDbType, isAutoIncrement, isCanNull, maxSize);
                                mStruct.Scale        = maxSizeScale;
                                mStruct.IsPrimaryKey = isKey;
                                mStruct.SqlTypeName  = sqlTypeName;
                                mStruct.TableName    = SqlFormat.NotKeyword(tableName);
                                foreach (DataRow item in valueDt.Rows)
                                {
                                    if (columnName == item[3].ToString())    //COLUMN_NAME
                                    {
                                        if (item[8].ToString() != "")
                                        {
                                            mStruct.DefaultValue = SqlFormat.FormatDefaultValue(dalType, item[8], 0, sqlDbType);    //"COLUMN_DEFAULT"
                                        }
                                        break;
                                    }
                                }
                                mdcs.Add(mStruct);
                            }
                        }

                        #endregion
                        break;
                    }
                }
                helper.ClearParameters();
            }
            catch (Exception err)
            {
                helper.debugInfo.Append(err.Message);
            }
            finally
            {
                helper.IsAllowRecordSql = true;//恢复记录SQL表结构语句。
                if (helper != dbHelper)
                {
                    helper.Dispose();
                }
            }
            if (mdcs.Count > 0)
            {
                //移除被标志的列:
                string[] fields = AppConfig.DB.HiddenFields.Split(',');
                foreach (string item in fields)
                {
                    string field = item.Trim();
                    if (!string.IsNullOrEmpty(field) & mdcs.Contains(field))
                    {
                        mdcs.Remove(field);
                    }
                }
            }
            if (!CacheManage.LocalInstance.Contains(key))
            {
                CacheManage.LocalInstance.Add(key, mdcs.Clone());
            }
            return(mdcs);
        }