Exemplo n.º 1
0
        /// <summary>
        /// 拼凑分页sql
        /// </summary>
        /// <param name="dbServer"></param>
        /// <param name="sql"></param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="err"></param>
        /// <returns></returns>
        private string SqlForPage(DbServerOutput dbServer, string sql, int pageIndex, int pageSize, ref ErrorInfo err)
        {
            string rownNum = "ROWNUM ";//初始化编号

            switch (dbServer.DbTypeName.ToUpper())
            {
            case "DB2":
                rownNum = string.Format(" ROW_NUMBER() OVER( PARTITION BY 1 ) ");
                break;

            case "ORACLE":
                rownNum = "ROWNUM ";
                break;

            case "SQLSERVER":
                string    nullSql = "SELECT * FROM ( " + sql + " ) T WHERE 1<>1";
                DataTable dt      = _dbServerApp.ExecuteGetTable(dbServer.Id, nullSql, ref err);
                rownNum = dt.Columns[0].Caption;
                rownNum = string.Format("ROW_NUMBER() OVER (ORDER BY {0} DESC)", rownNum);
                break;
            }
            int startNum = (pageIndex - 1) * pageSize;

            string nowsql = string.Format(@"        
                SELECT *
                FROM (SELECT {1} N, T.*
                        FROM ({0}) T)
                WHERE N > {2} AND N <= {3}", sql, rownNum, startNum, startNum + pageSize);

            return(nowsql);
        }
Exemplo n.º 2
0
        /// <summary>
        /// 执行sql得到一个DataTable
        /// </summary>
        /// <param name="dbserver"></param>
        /// <param name="sql"></param>
        /// <returns></returns>
        public DataTable ExecuteGetTable(DbServerOutput dbserver, string sql)
        {
            DataTable newTable = new DataTable();//初始化查询结果表
            var       _session = GetSession(dbserver);

            if (_session != null)
            {
                try
                {
                    _session.Open();
                    var dr = _session.ExecuteReader(sql);
                    newTable.Load(dr);//加载数据到datatable
                    _session.Closed();
                }
                catch (Exception ex)
                {
                    _session.Closed();
                    throw new Exception("sql执行失败:" + ex.Message);
                }
                finally
                {
                    _session.Closed();
                    _session.Dispose();
                }
            }
            return(newTable);
        }
Exemplo n.º 3
0
        /// <summary>
        /// 执行sql,根据dbserver
        /// </summary>
        /// <param name="dbserver"></param>
        /// <param name="sql"></param>
        /// <returns></returns>
        public int Execute(DbServerOutput dbserver, string sql)
        {
            int obj      = 0;//初始化
            var _session = GetSession(dbserver);

            if (_session != null)
            {
                try
                {
                    _session.Open();
                    obj = _session.Execute(sql);
                }
                catch (Exception ex)
                {
                    _session.Closed();
                    throw new Exception("sql执行失败:" + ex.Message);
                }
                finally
                {
                    _session.Closed();
                    _session.Dispose();
                }
            }
            return(obj);
        }
Exemplo n.º 4
0
        /// <summary>
        /// 获取数据库连接字符串
        /// </summary>
        /// <param name="dbserver"></param>
        /// <returns></returns>
        public string GetConnectStr(DbServerOutput dbserver)
        {
            string aesPwd = dbserver.Password;

            try
            {
                var p = EncryptHelper.AesDecrpt(dbserver.Password);
                aesPwd = p;
            }
            catch
            {
            }

            switch (dbserver.DbTypeName.ToUpper())
            {
            case "DB2":
                return(string.Format("Server={0}:{1};Database={2};UID={3};PWD={4};Connection Timeout =3600", dbserver.Ip, dbserver.Port, dbserver.DataCase, dbserver.User, aesPwd));

            case "ORACLE":
                return(string.Format("Data Source={0}:{1}/{2};User Id={3};Password={4};Connection Timeout =3600", dbserver.Ip, dbserver.Port, dbserver.DataCase, dbserver.User, aesPwd));

            case "MYSQL":
                //return string.Format("Data Source={0}:{1}/{2};User Id={3};Password={4};Connection Timeout =3600", dbserver.Ip, dbserver.Port, dbserver.DataCase, dbserver.User, dbserver.Password);
                break;

            case "SQLSERVER":
                //return string.Format("Data Source={0}:{1}/{2};User Id={3};Password={4};Connection Timeout =3600", dbserver.Ip, dbserver.Port, dbserver.DataCase, dbserver.User, dbserver.Password);
                break;

            default:    //默认oracle
                return(string.Format("Data Source={0}:{1}/{2};User Id={3};Password={4};Connection Timeout =3600", dbserver.Ip, dbserver.Port, dbserver.DataCase, dbserver.User, aesPwd));
            }
            return("");
        }
Exemplo n.º 5
0
        /// <summary>
        /// 传入sql及分页信息得到一个DataTable
        /// </summary>
        /// <param name="dbServer"></param>
        /// <param name="sql"></param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <returns></returns>
        public DataTable ExecuteGetTable(DbServerOutput dbServer, string sql, int pageIndex, int pageSize)
        {
            #region 设置分页
            string rownNum = "ROWNUM ";//初始化编号

            switch (dbServer.DbTypeName.ToUpper())
            {
            case "DB2":
                rownNum = string.Format(" ROW_NUMBER() OVER( PARTITION BY 1 ) ");
                break;

            case "ORACLE":
                rownNum = "ROWNUM ";
                break;

            case "SQLSERVER":
                string    nullSql = "SELECT * FROM ( " + sql + " ) T WHERE 1<>1";
                DataTable dt      = ExecuteGetTable(dbServer, nullSql);
                rownNum = dt.Columns[0].Caption;
                rownNum = string.Format("ROW_NUMBER() OVER (ORDER BY {0} DESC)", rownNum);
                break;
            }
            int startNum = (pageIndex - 1) * pageSize;

            string nowsql = string.Format(@"        
                SELECT *
                FROM (SELECT {1} N, T.*
                        FROM ({0}) T)
                WHERE N > {2} AND N <= {3}", sql, rownNum, startNum, startNum + pageSize);
            #endregion

            #region 执行sql
            DataTable newTable = new DataTable();//初始化查询结果表
            var       _session = GetSession(dbServer);
            if (_session != null)
            {
                try
                {
                    _session.Open();
                    var dr = _session.ExecuteReader(nowsql);
                    newTable.Load(dr);//加载数据到datatable
                    _session.Closed();
                }
                catch (Exception ex)
                {
                    _session.Closed();
                    throw new Exception("sql执行失败:" + ex.Message);
                }
                finally
                {
                    _session.Closed();
                    _session.Dispose();
                }
            }
            #endregion
            return(newTable);
        }
Exemplo n.º 6
0
 /// <summary>
 /// 根据ID获取某个数据库
 /// </summary>
 /// <param name="id"></param>
 /// <returns></returns>
 public DbServerOutput GetDbServer(long id)
 {
     try
     {
         var resDbServer = new DbServerOutput();
         var db          = _dbServerRepository.Get(id);
         if (db != null)
         {
             resDbServer            = db.MapTo <DbServerOutput>();
             resDbServer.DbTagName  = db.DbTag == null ? "" : db.DbTag.Name;
             resDbServer.DbTypeName = db.DbType == null ? "" : db.DbType.Name;
         }
         return(resDbServer);
     }
     catch (Exception ex)
     {
         throw new UserFriendlyException("操作错误,对象或已被删除!");
     }
 }
Exemplo n.º 7
0
        /// <summary>
        ///  根据数据库信息获取链接
        /// </summary>
        /// <param name="dbserver"></param>
        /// <returns></returns>
        public IDbSession GetSession(DbServerOutput dbserver)
        {
            IDbSession _session = null;

            try
            {
                if (dbserver != null)
                {
                    var          conStr = dbserver == null ? null : GetConnectStr(dbserver);
                    DatabaseType dbType;

                    switch (dbserver.DbTypeName.ToUpper())
                    {
                    case "DB2":
                        dbType = DatabaseType.Db2;
                        break;

                    case "ORACLE":
                        dbType = DatabaseType.Oracle;
                        break;

                    case "MYSQL":
                        dbType = DatabaseType.MySql;
                        break;

                    case "SQLSERVER":
                        dbType = DatabaseType.SqlServer;
                        break;

                    default:
                        dbType = DatabaseType.Oracle;
                        break;
                    }
                    _session = new DefaultSession(conStr, dbType);
                }
                return(_session);
            }
            catch (Exception ex)
            {
                throw new Exception("创建数据库连接失败:" + ex.Message);
            }
        }
Exemplo n.º 8
0
        /// <summary>
        /// 获得DataReader
        /// </summary>
        /// <param name="dbserver"></param>
        /// <param name="sql"></param>
        /// <returns></returns>
        public IDataReader ExecuteDataReader(DbServerOutput dbserver, string sql)
        {
            IDataReader dr       = null;
            var         _session = GetSession(dbserver);

            if (_session != null)
            {
                try
                {
                    _session.Open();
                    dr = _session.ExecuteReader(sql);
                    return(dr);
                }
                catch (Exception ex)
                {
                    _session.Closed();
                    _session.Dispose();
                    throw new Exception("sql执行失败:" + ex.Message);
                }
            }
            return(dr);
        }
Exemplo n.º 9
0
        /// <summary>
        /// 根据传入code执行报表,返回datatable
        /// </summary>
        /// <param name="code"></param>
        /// <param name="queryParams"></param>
        /// <param name="err"></param>
        /// <returns></returns>
        public DataTable GetDataTableFromCode(string code, string queryParams, ref ErrorInfo err)
        {
            DataTable dt = new DataTable();

            if (!string.IsNullOrEmpty(code))
            {
                var ent = _reportRepository.FirstOrDefault(p => p.Code == code);
                if (ent != null)
                {
                    string dbType = "";//数据库种类

                    #region 获取数据库
                    DbServerOutput dbServer = new DbServerOutput();
                    //中心库
                    if (ent.DbServerId == null)
                    {
                        var connection = ConnectionMsg.GetCurConnection();//获取当前承载库连接信息
                        dbType   = connection.DbType.ToString();
                        dbServer = null;
                    }
                    else
                    {
                        dbServer = _dbServerApp.GetDbServer(ent.DbServerId.Value);
                        dbType   = dbServer.DbTypeName;
                        if (dbServer == null)
                        {
                            err.IsError = true;
                            err.Message = "未找到当前code对应的数据库编号[" + ent.DbServerId.Value + "]!";
                            return(dt);
                        }
                    }
                    #endregion

                    //替换全局变量
                    string sql = ReplaceGlobalVar(ent.Sql);
                    //替换变量(内置或自定义、外置)
                    sql = ReplaceDefaultValue(sql, queryParams);
                    //当IsPlaceholder==true时,拼凑查询筛选条件
                    if (ent.IsPlaceholder == null || !ent.IsPlaceholder.Value)
                    {
                        sql = AnalysisParam(dbType, sql, queryParams);
                    }
                    if (dbServer == null)
                    {
                        dt = DbHelper.ExecuteGetTable(sql);
                    }
                    else
                    {
                        dt = _dbServerApp.ExecuteGetTable(dbServer.Id, sql);//执行sql
                    }
                }
                else
                {
                    err.IsError = true;
                    err.Message = "未找到编号为[" + code + "]的报表!";
                }
            }
            else
            {
                err.IsError = true;
                err.Message = "传入的code代码不能为空!";
            }
            return(dt);
        }
Exemplo n.º 10
0
        /// <summary>
        /// 根据code代码及传入条件拼凑和执行sql
        /// </summary>
        /// <param name="code"></param>
        /// <param name="rows"></param>
        /// <param name="page"></param>
        /// <param name="queryParams"></param>
        /// <param name="sidx"></param>
        /// <param name="sord"></param>
        /// <param name="err"></param>
        /// <returns></returns>
        public string ExcuteReportSql(string code, int rows, int page, string queryParams, string sidx, string sord, ref ErrorInfo err)
        {
            if (!string.IsNullOrEmpty(code))
            {
                var ent = _reportRepository.FirstOrDefault(p => p.Code == code);
                if (ent != null)
                {
                    string    dbType   = "";              //数据库种类
                    int       records  = 0;               //总数量
                    DataTable endTable = new DataTable(); //查询结果

                    #region 获取数据库
                    DbServerOutput dbServer = new DbServerOutput();
                    //中心库
                    if (ent.DbServerId == null)
                    {
                        var connection = ConnectionMsg.GetCurConnection();//获取当前承载库连接信息
                        dbType   = connection.DbType.ToString();
                        dbServer = null;
                    }
                    else
                    {
                        dbServer = _dbServerApp.GetDbServer(ent.DbServerId.Value);
                        dbType   = dbServer.DbTypeName;
                        if (dbServer == null)
                        {
                            err.IsError = true;
                            err.Message = "未找到当前code对应的数据库编号[" + ent.DbServerId.Value + "]!";
                            return("");
                        }
                    }
                    #endregion

                    //替换全局变量
                    string sql = ReplaceGlobalVar(ent.Sql);
                    //替换变量(内置或自定义、外置)
                    sql = ReplaceDefaultValue(sql, queryParams);
                    //当IsPlaceholder==true时,拼凑查询筛选条件
                    if (ent.IsPlaceholder == null || !ent.IsPlaceholder.Value)
                    {
                        sql = AnalysisParam(dbType, sql, queryParams);
                    }
                    sql = SqlForOrder(sql, sidx, sord);                            //生成排序sql
                    string sqlPage = SqlForPage(dbType, sql, page, rows, ref err); //拼凑分页sql

                    //获取总数
                    string sqlC = string.Format(@"select count(1) from ({0})", sql);
                    if (dbServer != null)
                    {
                        //记录总数
                        object num = _dbServerApp.ExecuteScalar(ent.DbServerId.Value, sqlC, ref err);
                        records  = Convert.ToInt32(num);
                        endTable = _dbServerApp.ExecuteGetTable(ent.DbServerId.Value, sqlPage);
                    }
                    else//根据承载库查询
                    {
                        try
                        {
                            object num = DbHelper.ExecuteScalar(sqlC);
                            records  = Convert.ToInt32(num);
                            endTable = DbHelper.ExecuteGetTable(sqlPage);
                        }
                        catch (Exception ex)
                        {
                            err.IsError = true;
                            err.Message = "查询数据错误:" + ex.Message;
                            return("");
                        }
                    }
                    //拼凑json串
                    string result = "{\"records\":" + records + ",\"page\":" + page + ",\"total\":" + Math.Ceiling(decimal.Divide(records, rows)) + ",\"rows\":" + JSON.DecodeToStr(endTable) + "}";
                    return(result);
                }
                else
                {
                    err.IsError = true;
                    err.Message = "未找到编号为[" + code + "]的报表!";
                }
            }
            else
            {
                err.IsError = true;
                err.Message = "传入的代码不能为空!";
            }
            return("");
        }