/// <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());
}
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));
}
}
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);
}
/// <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);
}
}
/// <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);
}
}
/// <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);
}
}
/// <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);
}
}
/// <summary> /// /// </summary> /// <param name="sql"></param> /// <param name="parameterValues"></param> /// <returns></returns> public abstract DataSet ExecuteDataSet(SqlWrap sql, IDictionary <string, object> parameterValues);
/// <summary> /// /// </summary> /// <param name="sql"></param> /// <param name="parameterValues"></param> /// <returns></returns> public abstract int ExecuteNonQuery(SqlWrap sql, IDictionary <string, object> parameterValues);
/// <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);
/// <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);
/// <summary> /// /// </summary> /// <param name="sql"></param> /// <param name="parameterValues"></param> /// <returns></returns> public abstract DbDataReader ExecuteReader(SqlWrap sql, IDictionary <string, object> parameterValues);
/// <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));
}
/// <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));
}
/// <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));
}
/// <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);
}
/// <summary> /// /// </summary> /// <param name="sql"></param> /// <param name="parameterValues"></param> /// <returns></returns> public abstract object ExecuteScalar(SqlWrap sql, IDictionary <string, object> parameterValues);
/// <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));
}
public IEnumerable <TEntity> SqlQuery <TEntity>(SqlWrap spWrap) where TEntity : class
{
return(SqlQuery <TEntity>(spWrap.Sql, spWrap.Parameters.Cast <object>().ToArray()));
}
/// <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));
}