/// <summary>
        ///
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="parameterValues"></param>
        /// <returns></returns>
        public override object ExecuteScalar(SqlWrap sql, IDictionary <string, object> parameterValues)
        {
            ArgumentAssertion.IsNotNull(sql, "sql");

            Stopwatch stopwatch    = null;
            var       sqlProcessor = ObjectIOCFactory.GetSingleton <DataSettings>().SqlProcessor;

            if (sqlProcessor.EnableTrace)
            {
                stopwatch = Stopwatch.StartNew();
            }

            var sqlText = this.TransformSql(sql.SqlText, parameterValues);

            var result = MySqlHelper.ExecuteScalar(this.ConnectionString, sqlText, sql.CommandTimeout, ConvertToDbParams(parameterValues));

            if (result == DBNull.Value)
            {
                result = null;
            }

            if (sqlProcessor.EnableTrace)
            {
                stopwatch.Stop();
                sqlProcessor.Log(LogLevel.Trace, string.Format("[{0}]ExecuteSql({1})\r\n\t{2}", stopwatch.Elapsed, sql.FullName, sqlText), null);
            }
            return(result);
        }
        static string GetPaginationSql(SqlWrap sql, Pagination pagination)
        {
            var paginationSql = ParsePaginationSql(sql);

            var startIndex      = (pagination.PageIndex - 1) * pagination.PageSize;
            var whereExpression = string.IsNullOrEmpty(paginationSql.WhereExpression) ? "" : string.Concat("where ", paginationSql.WhereExpression);

            var sqlBuilder = new StringBuilder(1204);

            sqlBuilder.AppendFormat("select count(*) from {0} {1};\r\n", paginationSql.Tables, whereExpression);
            if (string.IsNullOrEmpty(sql.PrimaryKey))
            {
                sqlBuilder.AppendFormat("select {0} from {1} {2}", paginationSql.Columns, paginationSql.Tables, whereExpression);
                if (false == string.IsNullOrEmpty(paginationSql.OrderExpression))
                {
                    sqlBuilder.AppendFormat(" order by {0}", paginationSql.OrderExpression);
                }
                sqlBuilder.AppendFormat(" limit {0},{1}", startIndex, pagination.PageSize);
            }
            else
            {
                sqlBuilder.AppendFormat("select {0} from {1} _AAA, (", paginationSql.Columns, paginationSql.Tables);

                sqlBuilder.AppendFormat("\r\n  select {0} _zzzId from {1} {2}", sql.PrimaryKey, paginationSql.Tables, whereExpression);
                if (false == string.IsNullOrEmpty(paginationSql.OrderExpression))
                {
                    sqlBuilder.AppendFormat(" order by {0}", paginationSql.OrderExpression);
                }
                sqlBuilder.AppendFormat(" limit {0},{1}", startIndex, pagination.PageSize);

                sqlBuilder.AppendFormat("\r\n ) _ZZZ where _AAA.{0}=_ZZZ._zzzId", sql.PrimaryKey);
                sqlBuilder.AppendFormat(" order by {0}", paginationSql.OrderExpression);
            }
            return(sqlBuilder.ToString());
        }
        /// <summary>
        ///
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="parameterValues"></param>
        /// <param name="pagination"></param>
        /// <returns></returns>
        public override DataTable ExecutePaginationTable(SqlWrap sql, IDictionary <string, object> parameterValues, Pagination pagination)
        {
            ArgumentAssertion.IsNotNull(sql, "sql");
            ArgumentAssertion.IsNotNull(pagination, "pagination");

            Stopwatch stopwatch    = null;
            var       sqlProcessor = ObjectIOCFactory.GetSingleton <DataSettings>().SqlProcessor;

            if (sqlProcessor.EnableTrace)
            {
                stopwatch = Stopwatch.StartNew();
            }

            var sqlText = GetPaginationSql(sql, pagination);

            sqlText = this.TransformSql(sqlText, parameterValues);

            var dataSet    = MySqlHelper.ExecuteDataSet(this.ConnectionString, sqlText, sql.CommandTimeout, ConvertToDbParams(parameterValues));
            var totalCount = dataSet.Tables[0].Rows[0][0].Convert <int>();

            pagination.TotalCount = totalCount;

            if (sqlProcessor.EnableTrace)
            {
                stopwatch.Stop();
                sqlProcessor.Log(LogLevel.Trace, string.Format("[{0}]ExecuteSql({1})\r\n\t{2}", stopwatch.Elapsed, sql.FullName, sqlText), null);
            }

            return(dataSet.Tables[1]);
        }
        /// <summary>
        ///
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <param name="parameterValues"></param>
        /// <returns></returns>
        public override T ExecuteIdentity <T>(SqlWrap sql, IDictionary <string, object> parameterValues)
        {
            ArgumentAssertion.IsNotNull(sql, "sql");
            var identityValue = this.ExecuteScalar(sql, parameterValues);

            return(identityValue.Convert <T>(default(T)));
        }
        static string GetPaginationSql(SqlWrap sql, Pagination pagination)
        {
            ArgumentAssertion.IsNotNull(sql, "sql");

            var paginationSql = PaginationSql.Parse(sql);

            var startIndex      = (pagination.PageIndex - 1) * pagination.PageSize;
            var endIndex        = pagination.PageIndex * pagination.PageSize;
            var whereExpression = string.IsNullOrEmpty(paginationSql.WhereExpression) ? "" : string.Concat("where ", paginationSql.WhereExpression);

            var sqlBuilder = new StringBuilder(1204);

            sqlBuilder.AppendFormat("select count(*) from {0} {1};\r\n", paginationSql.Tables, whereExpression);
            if (string.IsNullOrEmpty(sql.PrimaryKey))
            {
                sqlBuilder.AppendFormat("select {0} from (", paginationSql.Columns);
                sqlBuilder.AppendFormat("\r\n  select {0},row_number() over(order by {3}) _row_number from {1} {2}", paginationSql.Columns, paginationSql.Tables, whereExpression, paginationSql.OrderExpression);
                sqlBuilder.AppendFormat("\r\n ) _ZZZ where _row_number>{0} and _row_number<= {1} order by _row_number", startIndex, endIndex);
            }
            else
            {
                sqlBuilder.AppendFormat("select {0} from {1} _AAA, (", paginationSql.Columns, paginationSql.Tables);
                sqlBuilder.AppendFormat("\r\n  select {0} _zzzId,row_number() over(order by {3}) _row_number from {1} {2}", sql.PrimaryKey, paginationSql.Tables, whereExpression, paginationSql.OrderExpression);
                sqlBuilder.AppendFormat("\r\n ) _ZZZ where _row_number>{0} and _row_number<= {1} and _AAA.{2}=_ZZZ._zzzId order by _row_number", startIndex, endIndex, sql.PrimaryKey);
            }
            return(sqlBuilder.ToString());
        }
Beispiel #6
0
 private static SqlWrapException BuildSqlWrapException(Exception ex, SqlWrap sqlWrap, IDictionary <string, object> parameterValues)
 {
     if (ex is SqlWrapException)
     {
         return((SqlWrapException)ex);
     }
     else
     {
         return(new SqlWrapException(ex, sqlWrap.FullName, parameterValues));
     }
 }
Beispiel #7
0
        static IDatabaseProvider GetDatabaseProvider(SqlWrap sqlWrap, string partitionValues)
        {
            var dbConfig = SqlMapping.GetDatabase(sqlWrap.DbName);

            if ((dbConfig.DBType & sqlWrap.SupportDbType) == 0)
            {
                throw new ArgumentOutOfRangeException(string.Format("SqlWrap[{0}] 不支持类型为{1}的数据库[{2}]", sqlWrap.FullName, dbConfig.DBType, sqlWrap.DbName));
            }

            var dbProvider = DatabaseProviderFactory.GetDatabaseProvider(dbConfig);

            return(dbProvider);
        }
 static PaginationSql ParsePaginationSql(SqlWrap sql)
 {
     if (null == sql.PaginationSql)
     {
         lock (sql)
         {
             if (null == sql.PaginationSql)
             {
                 var parser = new PaginationSqlParser(sql.SqlText);
                 sql.PaginationSql = parser.Parse();
             }
         }
     }
     return(sql.PaginationSql);
 }
Beispiel #9
0
        /// <summary>
        /// 通过指定SqlWrap,执行后返回查询数据
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="parameterValues"></param>
        /// <param name="pagination"></param>
        /// <param name="partitionValues">分区字段值列表</param>
        /// <returns></returns>
        public static DataTable ExecutePaginationTable(SqlWrap sql, IDictionary <string, object> parameterValues, Pagination pagination, string partitionValues)
        {
            ArgumentAssertion.IsNotNull(sql, "sql");

            var dbProvider = GetDatabaseProvider(sql, partitionValues);

            try
            {
                var dataset = dbProvider.ExecutePaginationTable(sql, parameterValues, pagination);
                return(dataset);
            }
            catch (Exception ex)
            {
                throw BuildSqlWrapException(ex, sql, parameterValues);
            }
        }
Beispiel #10
0
        /// <summary>
        /// 通过指定SqlWrap,执行后返回自增值
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="parameterValues"></param>
        /// <param name="partitionValues">分区字段值列表</param>
        /// <returns></returns>
        public static T ExecuteIdentity <T>(SqlWrap sql, IDictionary <string, object> parameterValues, string partitionValues)
        {
            ArgumentAssertion.IsNotNull(sql, "sql");

            var dbProvider = GetDatabaseProvider(sql, partitionValues);

            try
            {
                var identity = dbProvider.ExecuteIdentity <T>(sql, parameterValues);
                return(identity);
            }
            catch (Exception ex)
            {
                throw BuildSqlWrapException(ex, sql, parameterValues);
            }
        }
Beispiel #11
0
        /// <summary>
        ///
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="parameterValues"></param>
        /// <param name="partitionValues"></param>
        /// <returns></returns>
        public static int ExecuteNonQuery(SqlWrap sql, IDictionary <string, object> parameterValues, string partitionValues)
        {
            ArgumentAssertion.IsNotNull(sql, "sql");

            var dbProvider = GetDatabaseProvider(sql, partitionValues);

            try
            {
                var rowCount = dbProvider.ExecuteNonQuery(sql, parameterValues);
                return(rowCount);
            }
            catch (Exception ex)
            {
                throw BuildSqlWrapException(ex, sql, parameterValues);
            }
        }
Beispiel #12
0
        /// <summary>
        /// 通过指定SQL名称执行SQL语句,获得DbDataReader后,使用委托Action[DbDataReader]处理数据流
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="parameterValues"></param>
        /// <param name="partitionValues"></param>
        /// <param name="action"></param>
        public static void ExecuteReader(SqlWrap sql, IDictionary <string, object> parameterValues, string partitionValues, Action <DbDataReader> action)
        {
            ArgumentAssertion.IsNotNull(sql, "sql");

            if (null == action)
            {
                throw new ArgumentNullException("action");
            }

            var dbProvider = GetDatabaseProvider(sql, partitionValues);

            try
            {
                using (var dbReader = dbProvider.ExecuteReader(sql, parameterValues))
                {
                    action(dbReader);
                }
            }
            catch (Exception ex)
            {
                throw BuildSqlWrapException(ex, sql, parameterValues);
            }
        }
Beispiel #13
0
 /// <summary>
 ///
 /// </summary>
 /// <param name="sql"></param>
 /// <param name="parameterValues"></param>
 /// <returns></returns>
 public abstract DataSet ExecuteDataSet(SqlWrap sql, IDictionary <string, object> parameterValues);
Beispiel #14
0
 /// <summary>
 ///
 /// </summary>
 /// <param name="sql"></param>
 /// <param name="parameterValues"></param>
 /// <returns></returns>
 public abstract int ExecuteNonQuery(SqlWrap sql, IDictionary <string, object> parameterValues);
Beispiel #15
0
 /// <summary>
 ///
 /// </summary>
 /// <typeparam name="T"></typeparam>
 /// <param name="sql"></param>
 /// <param name="parameterValues"></param>
 /// <returns></returns>
 public abstract T ExecuteIdentity <T>(SqlWrap sql, IDictionary <string, object> parameterValues);
Beispiel #16
0
 /// <summary>
 ///
 /// </summary>
 /// <param name="sql"></param>
 /// <param name="parameterValues"></param>
 /// <param name="pagination"></param>
 /// <returns></returns>
 public abstract DataTable ExecutePaginationTable(SqlWrap sql, IDictionary <string, object> parameterValues, Pagination pagination);
Beispiel #17
0
 /// <summary>
 ///
 /// </summary>
 /// <param name="sql"></param>
 /// <param name="parameterValues"></param>
 /// <returns></returns>
 public abstract DbDataReader ExecuteReader(SqlWrap sql, IDictionary <string, object> parameterValues);
Beispiel #18
0
 /// <summary>
 ///
 /// </summary>
 /// <param name="sql"></param>
 /// <param name="parameterValues"></param>
 /// <param name="pagination"></param>
 /// <returns></returns>
 public static DataTable ExecutePaginationTable(SqlWrap sql, IDictionary <string, object> parameterValues, Pagination pagination)
 {
     return(ExecutePaginationTable(sql, parameterValues, pagination, null));
 }
Beispiel #19
0
 /// <summary>
 /// 通过指定SqlWrap,执行后返回查询数据
 /// </summary>
 /// <param name="sql"></param>
 /// <param name="parameterValues"></param>
 /// <returns></returns>
 public static DataSet ExecuteDataSet(SqlWrap sql, IDictionary <string, object> parameterValues)
 {
     return(ExecuteDataSet(sql, parameterValues, null));
 }
Beispiel #20
0
 /// <summary>
 /// 通过指定SqlWrap,执行后返回单值
 /// </summary>
 /// <param name="sql"></param>
 /// <param name="parameterValues"></param>
 /// <returns></returns>
 public static object ExecuteScalar(SqlWrap sql, IDictionary <string, object> parameterValues)
 {
     return(ExecuteScalar(sql, parameterValues, null));
 }
Beispiel #21
0
 /// <summary>
 /// 通过指定SQL名称执行SQL语句,获得DbDataReader后,使用委托Action[DbDataReader]处理数据流
 /// </summary>
 /// <param name="sql"></param>
 /// <param name="parameterValues"></param>
 /// <param name="action"></param>
 public static void ExecuteReader(SqlWrap sql, IDictionary <string, object> parameterValues, Action <DbDataReader> action)
 {
     ExecuteReader(sql, parameterValues, null, action);
 }
Beispiel #22
0
 /// <summary>
 ///
 /// </summary>
 /// <param name="sql"></param>
 /// <param name="parameterValues"></param>
 /// <returns></returns>
 public abstract object ExecuteScalar(SqlWrap sql, IDictionary <string, object> parameterValues);
Beispiel #23
0
 /// <summary>
 /// 通过指定SqlWrap,执行后返回自增值
 /// </summary>
 /// <param name="sql"></param>
 /// <param name="parameterValues"></param>
 /// <returns></returns>
 public static T ExecuteIdentity <T>(SqlWrap sql, IDictionary <string, object> parameterValues)
 {
     return(ExecuteIdentity <T>(sql, parameterValues, null));
 }
Beispiel #24
0
 public IEnumerable <TEntity> SqlQuery <TEntity>(SqlWrap spWrap) where TEntity : class
 {
     return(SqlQuery <TEntity>(spWrap.Sql, spWrap.Parameters.Cast <object>().ToArray()));
 }
Beispiel #25
0
 /// <summary>
 ///
 /// </summary>
 /// <param name="sql"></param>
 /// <param name="parameterValues"></param>
 /// <returns></returns>
 public static int ExecuteNonQuery(SqlWrap sql, IDictionary <string, object> parameterValues)
 {
     return(ExecuteNonQuery(sql, parameterValues, null));
 }