/// <summary>
/// Returns a cold observable that compiles a SQL query with
/// provided bind parameter values, that publishes the rows in the result
/// set for each subscription.
/// </summary>
/// <param name="This">The asynchronous database connection.</param>
/// <param name="query">The SQL query to compile and Query.</param>
/// <param name="values">The bind parameter values.</param>
/// <returns>A cold observable of rows in the result set.</returns>
public static IObservable<IReadOnlyList<IResultSetValue>> Query(
this IAsyncDatabaseConnection This, ISqlQuery query, params object[] values)
{
Contract.Requires(This != null);
Contract.Requires(query != null);
Contract.Requires(values != null);
return This.Query(query.ToString(), values);
}
public CacheKey(ISqlQuery query)
{
// The key must always be made from a clone - otherwise, if the original query is changed & rerun, it will still
// match the cached value (being the same object)
Query = SqlQueryUtility.QueryAsSql(query.GetQuery(), query.Parameters);
ISqlQueryMaker comp = query as ISqlQueryMaker;
if (comp != null)
{
TableName = comp.TableName;
}
}
public static IDataReader AddToCache(ISqlQuery query, IDataReader data)
{
ICacheKey key = new CacheKey(query);
CachedDataReader cached = new CachedDataReader(data);
int newSize = cached.Size() + key.Query.Length;
ICacheItem item;
if (newSize < CacheObjectSizeLimit)
{
while (CacheSize + newSize > CacheSizeLimit && QueryOrder.Count > 0)
{
ICacheKey oldestKey = QueryOrder.Peek();
if (!RemoveFromCache(oldestKey))
{
break;
}
}
if (QueryOrder.Count == 0 && CacheSize != 0)
{
CacheSize = 0;
QueryResultCache.Clear();
// this should be logged but thrwing an error seems too much... test the hellout of this?
}
// It is possible that we failed to remove it from the cache, so just bail out
// if still won't fit
if (CacheSize + newSize <= CacheSizeLimit)
{
item = new CacheItem(cached, newSize);
if (QueryResultCache.TryAdd(key, item))
{
QueryOrder.Enqueue(key);
}
}
else
{
}
}
// whether or not we end up caching the datareader, we must return the in-memory copy because you can't go back
// after reading a datareader. We only try to cache when buffering is enabled, so nothing's really lost.
return cached;
}
public virtual int Count(IDbConnection connection,
ISqlQuery query,
IDbTransaction transaction = null,
CommandBehavior commandBehavior = CommandBehavior.Default)
{
int count;
if (query is ISqlQueryMaker)
{
var comp = (ISqlQueryMaker)query;
count= Count(connection,comp.TableName,comp.Where.ToString(),comp.Parameters,transaction,commandBehavior);
}
else
{
ValidateQueryType(query.GetQuery(), "select");
var countQuery = new SqlQueryDef("SELECT COUNT(*) FROM (" + query + ") q", query.Parameters);
count= RunQueryScalar(connection, countQuery,transaction, commandBehavior);
}
count -= query.FirstRow;
if (query.TotalRows > 0)
{
count = Math.Min(count, query.TotalRows);
}
return count;
}
public async Task <List <T> > GetResultsAsync <T>(ISqlQuery query)
{
var sql = ReplaceSql(_sql);
return((await query.QueryListAsync <T>(sql, _searcheObj)).ToList());
}
public virtual IDataReader RunStoredProcedure(IDbConnection connection,
ISqlQuery query,
IDbTransaction transaction = null,
CommandBehavior commandBehavior = CommandBehavior.Default)
{
throw new NotImplementedException("Can't use SP.");
//if (query.FirstRow >0 || query.TotalRows > 0)
//{
// throw new NotImplementedException("Can't use row selection for SP results... yet.");
//}
//IDataReader reader = null;
//using (IDbCommand cmd = GetCommand(connection, query, transaction))
//{
// cmd.CommandType = CommandType.StoredProcedure;
// ExecuteSqlFinal(new Action(() =>
// {
// reader = cmd.ExecuteReader(commandBehavior);
// }));
// cmd.Parameters.Clear();
//}
//OnQueryComplete();
//return reader;
}
protected virtual object[] OnQueryExecuted(ISqlQuery query, object[] results, ILog log)
{
return query.QueryType == typeof (SqlDmlActivity) ? CalculateSqlDmlActivityIncrease(results, log) : results;
}
/// <summary>
/// Adds a subquery to the SELECT statement.
/// </summary>
/// <param name="expression">A subquery.</param>
/// <returns>The query itself.</returns>
public SqlQuery Select(ISqlQuery expression)
{
if (expression == null)
throw new ArgumentNullException("expression");
this.Select(expression.ToString());
return this;
}
/// <summary>
/// 初始化应用程序控制器
/// </summary>
/// <param name="service">应用程序服务</param>
/// <param name="sqlQuery">Sql查询对象</param>
public ApplicationController(IApplicationService service, ISqlQuery sqlQuery) : base(service)
{
ApplicationService = service;
SqlQuery = sqlQuery;
}
protected void ClearCacheAfterChange(ISqlQuery query)
{
if (query.QueryType == QueryType.Delete ||
query.QueryType == QueryType.Insert ||
query.QueryType == QueryType.Update)
{
if (query is ISqlQueryMaker)
{
SqlQueryBuilderConfig.RemoveAllForTable(((ISqlQueryMaker)query).TableName);
}
else
{
SqlQueryBuilderConfig.Clear();
}
}
}
public virtual void AddNotExists(ISqlQuery subSqlQuery)
{
sb.Append(" NOT EXISTS (" + subSqlQuery.GetText() + ") ");
initialized = true;
}
/// <summary>
/// Creates a new EXISTS criteria</summary>
/// <param name="query">
/// Expression</param>
/// <returns></returns>
public static BaseCriteria Exists(ISqlQuery query)
{
return new UnaryCriteria(CriteriaOperator.Exists, new Criteria(query));
}
/// <summary>
/// Belirtilen SqlQuery i içeren yeni bir
/// kriter oluşturur.</summary>
/// <param name="query">
/// Query nesnesi (genellikle sub query).</param>
public Criteria(ISqlQuery query)
: this(query.ToString())
{
}
public virtual IDataReader RunQuery(IDbConnection connection,
ISqlQuery query,
IDbTransaction transaction = null,
CommandBehavior commandBehavior = CommandBehavior.Default)
{
IDataReader dataReader = null;
string sql;
if (query.FirstRow > 0 || query.TotalRows > 0)
{
sql = GetQueryForRows(query);
} else {
sql = query.GetQuery();
}
var newQuery = new SqlQueryDef(sql, query.Parameters);
BeforeRunQuery(query);
using (IDbCommand cmd = GetCommand(connection, newQuery, transaction))
{
ExecuteSqlFinal(new Action(() =>
{
dataReader = cmd.ExecuteReader(commandBehavior);
}));
cmd.Parameters.Clear();
}
OnQueryComplete();
return dataReader;
}
protected abstract int InsertAndReturnNewID(IDbConnection conn,
ISqlQuery query,
IDbTransaction transaction = null,
CommandBehavior commandBehavior = CommandBehavior.Default);
protected abstract string GetQueryForRows(ISqlQuery query);
protected virtual IDbCommand GetCommand(IDbConnection connection,
ISqlQuery query,
IDbTransaction transaction)
{
LastQuery = query;
var lastParameters = new List<IDataParameter>();
IDbCommand cmd = connection.CreateCommand();
cmd.CommandText = query.GetQuery();
if (query.Parameters != null)
{
foreach (var item in query.Parameters)
{
if (HasParameter(query.GetQuery(), item.ParameterName)) {
IDataParameter parm;
if (item is QueryParameter)
{
parm = cmd.CreateParameter();
parm.ParameterName = item.ParameterName;
parm.Value = item.Value == null ? DBNull.Value : item.Value;
parm.DbType = item.DbType;
parm.Direction = item.Direction;
OnMapParameter(item, parm);
}
else
{
parm = item;
}
cmd.Parameters.Add(parm);
lastParameters.Add(parm);
}
}
}
if (transaction != null) {
cmd.Transaction = transaction;
}
return cmd;
}
public async Task ExecuteAsync(ISqlQuery query)
{
var sql = ReplaceSql(_sql);
await query.ExecuteAsync(sql, _searcheObj);
}
/// <summary>
/// Creates a new EXISTS criteria</summary>
/// <param name="query">
/// Expression</param>
/// <returns></returns>
public static BaseCriteria Exists(ISqlQuery query)
{
return(new UnaryCriteria(CriteriaOperator.Exists, new Criteria(query)));
}
public QueryContext(ISqlQuery query)
{
_query = query;
_creationTime = DateTime.Now;
}
/// <summary>
/// Compiles a SQL query, returning the an <see cref="IEnumerable<T>"/> of rows in the result set.
/// </summary>
/// <param name="This">The database connection.</param>
/// <param name="query">The SQL statement to compile and Query.</param>
/// <returns>An <see cref="IEnumerable<T>"/> of rows in the result set.</returns>
public static IEnumerable<IReadOnlyList<IResultSetValue>> Query(this IDatabaseConnection This, ISqlQuery query)
{
Contract.Requires(This != null);
Contract.Requires(query != null);
return This.Query(query.ToString());
}
/// <summary>
/// Finds an entity by an arbitrary query
/// </summary>
/// <param name="query">A query implementation to execute</param>
/// <returns>A list of matching entity references, or an empty list if a match was not found</returns>
public IEnumerable<Dishwasher> Find(ISqlQuery query)
{
throw new System.NotImplementedException();
}
/// <summary>
/// 初始化应用程序服务
/// </summary>
/// <param name="unitOfWork">工作单元</param>
/// <param name="applicationRepository">应用程序仓储</param>
/// <param name="sqlQuery">Sql查询对象</param>
public ApplicationService(ISampleUnitOfWork unitOfWork, IApplicationRepository applicationRepository, ISqlQuery sqlQuery)
: base(unitOfWork, applicationRepository)
{
ApplicationRepository = applicationRepository;
SqlQuery = sqlQuery;
}
public static bool RemoveFromCache(ISqlQuery query)
{
ICacheKey key = new CacheKey(query);
return RemoveFromCache(key);
}
/// <summary>
/// Adds a subquery to the FROM statement, with given short name.
/// </summary>
/// <param name="subQuery">A subquery</param>
/// <param name="alias">Alias that contains the short name.</param>
/// <returns>The query itself.</returns>
/// <remarks>This overload requires that alias has a table name.</remarks>
public SqlQuery From(ISqlQuery subQuery, IAlias alias)
{
if (subQuery == null)
throw new ArgumentNullException("subQuery");
if (alias == null)
throw new ArgumentNullException("alias");
return From(subQuery.ToString(), alias);
}
public static bool TryGetCachedData(ISqlQuery query, out IDataReader data)
{
var key = new CacheKey(query);
ICacheItem cached;
if (QueryResultCache.TryGetValue(key, out cached)) {
if (cached.Expired)
{
RemoveFromCache(key);
data = null;
return false;
}
else
{
CachedDataReader reader = ((CacheItem)cached).Data;
reader.Reset();
data = new CachedDataReaderView(reader);
return true;
}
} else {
data=null;
return false;
}
}
protected override object[] OnQueryExecuted(ISqlQuery query, object[] results, ILog log)
{
results = base.OnQueryExecuted(query, results, log);
ApplyDatabaseDisplayNames(IncludedDatabases, results);
return results;
}
/// <summary>
/// 获取Guid值
/// </summary>
/// <param name="sqlQuery">Sql查询对象</param>
/// <param name="connection">数据库连接</param>
public static async Task <Guid> ToGuidAsync(this ISqlQuery sqlQuery, IDbConnection connection = null)
{
return(Convert.ToGuid(await sqlQuery.ToScalarAsync(connection)));
}
/// <summary>
/// Note that FirstRow is zero-indexed, so 0 means ignore (or the first row should be the usual first row).
/// </summary>
/// <param name="query"></param>
/// <returns></returns>
protected override string GetQueryForRows(ISqlQuery query)
{
if (query.FirstRow == 0)
{
SqlClause clause = SqlClause.From | SqlClause.Where |
SqlClause.Having | SqlClause.GroupBy | SqlClause.OrderBy;
if (query is ISqlQueryMaker)
{
var comp = (ISqlQueryMaker)query;
return String.Format("SELECT TOP {0} {1} {2}",
comp.TotalRows,
comp.Select,
comp.GetQuery( clause));
} else {
string sql = query.GetQuery();
int pos = sql.ToLower().LastIndexOf("order by");
string order="";
if (pos>0) {
order = sql.Substring(pos);
sql = sql.Substring(0,pos-1);
}
return String.Format("SELECT TOP {0} * FROM ({1}) __subquery {2}",
query.TotalRows,
sql,
order);
}
}
else
{
if (query is ISqlQueryMaker)
{
var comp = (ISqlQueryMaker)query;
string querySQL = "";
string where = comp.Where.ToString();
// always us PK as the final sort to eliminate possible paging problems
string orderByClause = " ORDER BY " + comp.OrderBy.Clone().AddIfNotPresent(comp.PrimaryKey).ToString();
// Add TablePrimaryKey to sort phrase to ensure that there is a consistent sort order, if the user-defined order is not explicit for every record.
// Pagination can be wrong otherwise.
//SortPhrase += (SortOrder==""?"":",")+TablePrimaryKey;
if (comp.TotalRows > 0)
{
querySQL = @"SELECT " + comp.Select + " FROM " + comp.From + " WHERE " + comp.PrimaryKey + " IN " +
"(SELECT TOP " + comp.TotalRows + " " + comp.PrimaryKey + " FROM " + comp.From + " WHERE [WhereSubstitution1]" + comp.PrimaryKey + " NOT IN " +
"(SELECT TOP " + comp.FirstRow + " " + comp.PrimaryKey + " FROM " + comp.From + "[WhereSubstitution2]" + orderByClause + ")" +
orderByClause + ")" +
orderByClause;
}
else
{
querySQL = @"SELECT " + comp.Select + " FROM " + comp.From + " WHERE " + comp.PrimaryKey + " NOT IN " +
"(SELECT TOP " + comp.FirstRow + " " + comp.PrimaryKey + " FROM " + comp.From + "[WhereSubstitution2]" + orderByClause + ")" +
orderByClause;
}
querySQL = querySQL.Replace("[WhereSubstitution1]", where!="" ?
where + " AND " : "");
querySQL = querySQL.Replace("[WhereSubstitution2]", where != "" ?
" WHERE " + where : "");
return querySQL;
}
else
{
throw new InvalidOperationException("I can't do paging in an ad-hoc query.");
}
}
}
/// <summary>
/// 获取可空整型值
/// </summary>
/// <param name="sqlQuery">Sql查询对象</param>
/// <param name="connection">数据库连接</param>
public static int?ToIntOrNull(this ISqlQuery sqlQuery, IDbConnection connection = null)
{
return(Convert.ToIntOrNull(sqlQuery.ToScalar(connection)));
}
/// <summary>
/// Execute the given query and returns the result as a read only list
/// </summary>
public static IReadOnlyList <T> ToList <T>(this ISqlQuery <T> select, DbContext context)
{
return(DoConnection(context, conn => select.ToList(conn)));
}
/// <summary>
/// 获取可空整型值
/// </summary>
/// <param name="sqlQuery">Sql查询对象</param>
/// <param name="connection">数据库连接</param>
public static async Task <int?> ToIntOrNullAsync(this ISqlQuery sqlQuery, IDbConnection connection = null)
{
return(Convert.ToIntOrNull(await sqlQuery.ToScalarAsync(connection)));
}
public async Task <T> GetResultAsync <T>(ISqlQuery query)
{
var sql = ReplaceSql(_sql);
return((await query.QueryListAsync <T>(sql, _searcheObj)).FirstOrDefault());
}
/// <summary>
/// 获取float值
/// </summary>
/// <param name="sqlQuery">Sql查询对象</param>
/// <param name="connection">数据库连接</param>
public static float ToFloat(this ISqlQuery sqlQuery, IDbConnection connection = null)
{
return(Convert.ToFloat(sqlQuery.ToScalar(connection)));
}
/// <summary>
/// Initializes a new instance of the <see cref="Criteria"/> class containing
/// a query's string representation.
/// </summary>
/// <param name="query">The query.</param>
public Criteria(ISqlQuery query)
: this(query.ToString())
{
}
/// <summary>
/// 获取float值
/// </summary>
/// <param name="sqlQuery">Sql查询对象</param>
/// <param name="connection">数据库连接</param>
public static async Task <float> ToFloatAsync(this ISqlQuery sqlQuery, IDbConnection connection = null)
{
return(Convert.ToFloat(await sqlQuery.ToScalarAsync(connection)));
}
public virtual void AddExists(ISqlQuery subSqlQuery)
{
sb.Append(" EXISTS (" + subSqlQuery.Statement + ") ");
initialized = true;
}
protected void ProcessConstructorData(IQClassData data)
{
ISqlQuery query = data.Query;
if (query != null)
{
_Query = query;
// Sync the two table names -- TODO fix this, perhaps always create a query instead of having sep.
// properties?
if (String.IsNullOrEmpty(_Query.TableName))
{
_Query.TableName = data.TableName;
}
else if (String.IsNullOrEmpty(data.TableName))
{
data.TableName = _Query.TableName;
}
}
if (!String.IsNullOrEmpty(data.TableName))
{
if (!String.IsNullOrEmpty(TableName) && TableName.ToLower() != data.TableName.ToLower())
{
throw new Exception("A table name was specified in the constructor, but another one was already indetified for the class.");
}
TableName = data.TableName;
}
if (!String.IsNullOrEmpty(data.PrimaryKey))
{
string nameLower = data.PrimaryKey.ToLower();
Field fldTemp;
if (!temporaryFieldInfo.TryGetValue(nameLower, out fldTemp))
{
fldTemp = new Field();
temporaryFieldInfo[nameLower] = fldTemp;
fldTemp.Name = data.PrimaryKey;
}
fldTemp.PK = true;
}
}
/// <summary>
/// Compiles a SQL query.
/// </summary>
/// <param name="This">The database connection.</param>
/// <param name="query">The SQL query to compile.</param>
/// <returns>The compiled statement.</returns>
public static IStatement PrepareStatement(this IDatabaseConnection This, ISqlQuery query)
{
Contract.Requires(This != null);
Contract.Requires(query != null);
return This.PrepareStatement(query.ToString());
}
/// <summary>
/// 初始化仓储
/// </summary>
/// <param name="dbContextProvider">DbContext服务</param>
/// <param name="sqlQuery">Sql查询工具</param>
public SysUsrAuthRepository(IDbContextProvider <SCRMDbContext> dbContextProvider, ISqlQuery sqlQuery) : base(dbContextProvider)
{
_sqlQuery = sqlQuery;
}
/// <summary>
/// 获取double值
/// </summary>
/// <param name="sqlQuery">Sql查询对象</param>
/// <param name="connection">数据库连接</param>
public static double ToDouble(this ISqlQuery sqlQuery, IDbConnection connection = null)
{
return(Util.Helpers.Convert.ToDouble(sqlQuery.ToScalar(connection)));
}
/// <summary>
/// Occurs right before any query is run
/// </summary>
/// <param name="query"></param>
protected virtual void BeforeRunQuery(ISqlQuery query)
{
// clear the cache before actually running the update query
ClearCacheAfterChange(query);
}
/// <inheritdoc /> public virtual IEnumerable <IAnnotation> For(ISqlQuery sqlQuery, bool designTime) => Enumerable.Empty <IAnnotation>();
/// <summary>
/// 获取字符串值
/// </summary>
/// <param name="sqlQuery">Sql查询对象</param>
/// <param name="connection">数据库连接</param>
public static async Task <string> ToStringAsync(this ISqlQuery sqlQuery, IDbConnection connection = null)
{
return((await sqlQuery.ToScalarAsync(connection)).SafeString());
}
/// <summary>
/// 初始化仓储
/// </summary>
/// <param name="dbContextProvider">DbContext服务</param>
/// <param name="sqlQuery">Sql查询工具</param>
public MdmGoodsListRepository(IDbContextProvider <SCRMDbContext> dbContextProvider, ISqlQuery sqlQuery) : base(dbContextProvider)
{
_sqlQuery = sqlQuery;
}
/// <summary>
/// 获取可空decimal值
/// </summary>
/// <param name="sqlQuery">Sql查询对象</param>
/// <param name="connection">数据库连接</param>
public static decimal?ToDecimalOrNull(this ISqlQuery sqlQuery, IDbConnection connection = null)
{
return(Util.Helpers.Convert.ToDecimalOrNull(sqlQuery.ToScalar(connection)));
}
/// <summary>
/// 初始化仓储
/// </summary>
/// <param name="dbContextProvider">DbContext服务</param>
/// <param name="sqlQuery">Sql查询工具</param>
public MdmDutyMstrRepository(IDbContextProvider <SCRMDbContext> dbContextProvider, ISqlQuery sqlQuery) : base(dbContextProvider)
{
_sqlQuery = sqlQuery;
}
/// <summary>
/// 获取可空日期值
/// </summary>
/// <param name="sqlQuery">Sql查询对象</param>
/// <param name="connection">数据库连接</param>
public static DateTime?ToDateOrNull(this ISqlQuery sqlQuery, IDbConnection connection = null)
{
return(Util.Helpers.Convert.ToDateOrNull(sqlQuery.ToScalar(connection)));
}
/// <summary>
/// Queues the specified query.
/// </summary>
/// <param name="query">The query.</param>
public void Queue(ISqlQuery query)
{
_queries.Add(query);
}
/// <summary>
/// 获取Guid值
/// </summary>
/// <param name="sqlQuery">Sql查询对象</param>
/// <param name="connection">数据库连接</param>
public static Guid ToGuid(this ISqlQuery sqlQuery, IDbConnection connection = null)
{
return(Util.Helpers.Convert.ToGuid(sqlQuery.ToScalar(connection)));
}
/// <summary>
/// Adds a subquery to the SELECT statement.
/// </summary>
/// <param name="expression">A subquery.</param>
/// <param name="columnName">A column name</param>
/// <returns>The query itself.</returns>
public SqlQuery Select(ISqlQuery expression, string columnName)
{
if (expression == null)
throw new ArgumentNullException("expression");
if (string.IsNullOrEmpty(columnName))
throw new ArgumentNullException("columnName");
this.Select(expression.ToString(), columnName);
return this;
}
/// <summary>
/// 获取整型值
/// </summary>
/// <param name="sqlQuery">Sql查询对象</param>
/// <param name="connection">数据库连接</param>
public static async Task <int> ToIntAsync(this ISqlQuery sqlQuery, IDbConnection connection = null)
{
return(Util.Helpers.Convert.ToInt(await sqlQuery.ToScalarAsync(connection)));
}
public static void LogSqlException(this ILog log, SqlException sqlException, ISqlQuery query, string connString)
{
var connectionString = new SqlConnectionStringBuilder(connString);
log.Error(string.Empty);
switch (sqlException.Number)
{
case 297: // User cannot log on via Windows Auth
case 18456: // User cannot login via SQL Auth
if (connectionString.IntegratedSecurity)
{
// System.Data.SqlClient.SqlException: Login failed. The login is from an untrusted domain and cannot be used with Windows authentication.
log.ErrorFormat("The Windows service is running as user '{0}', however, the user cannot access the server '{1}'. " +
"Consider changing the connection string in the configuration file " +
"or adding permissions to your SQL Server (see readme.md).",
Environment.UserName, connectionString.DataSource);
}
else
{
// System.Data.SqlClient.SqlException: Login failed for user '<user id>'.
log.ErrorFormat("User '{0}' cannot access the server '{1}'. " +
"Consider changing the connection string in the configuration file " +
"or adding permissions to your SQL Server (see readme.md).",
connectionString.UserID, connectionString.DataSource);
}
break;
case 4060: // Missing database user
// System.Data.SqlClient.SqlException: Cannot open database "Junk" requested by the login. The login failed.
if (connectionString.IntegratedSecurity)
{
if (Environment.UserInteractive)
{
log.ErrorFormat("The plugin is running as user '{0}', however, the user cannot access the database '{1}'. " +
"Ensure the login has a user in the database (see readme.md).",
Environment.UserName, connectionString.InitialCatalog);
}
else
{
log.ErrorFormat("The Windows service is running as user '{0}', however, the user cannot access the database '{1}'. " +
"Ensure the login has a user in the database (see readme.md).",
Environment.UserName, connectionString.InitialCatalog);
}
}
else
{
log.ErrorFormat("User '{0}' cannot access the database '{1}'. " +
"Ensure the login has a user in the database (see readme.md).",
connectionString.UserID, connectionString.InitialCatalog);
}
break;
case 10060:
case 10061:
case 11001:
case 40615:
if (sqlException.Message.Contains("sp_set_firewall_rule"))
{
var relevantErrorMessage = Regex.Replace(sqlException.Message, @"change to take effect\.(.*)$", string.Empty, RegexOptions.Singleline);
log.Error("Azure SQL Error: " + relevantErrorMessage);
}
else
{
log.ErrorFormat("Timeout connecting to server at '{0}'. Verify that the connection string is correct and the server is reachable.",
connectionString.DataSource);
}
break;
default:
log.ErrorFormat("Error collecting metric '{0}': {1}", query.QueryName, sqlException.Message);
log.ErrorFormat("SQL Exception Details: Class {0}, Number {1}, State {2}", sqlException.Class, sqlException.Number, sqlException.State);
log.Error(@"Check the error log for more details at 'C:\ProgramData\New Relic\MicrosoftSQLServerPlugin\ErrorDetailOutput.log'");
log.Error("For additional help, contact New Relic support at https://support.newrelic.com/home. Please paste all log messages above into the support request.");
break;
}
}
/// <summary>
/// 获取可空float值
/// </summary>
/// <param name="sqlQuery">Sql查询对象</param>
/// <param name="connection">数据库连接</param>
public static float?ToFloatOrNull(this ISqlQuery sqlQuery, IDbConnection connection = null)
{
return(Util.Helpers.Convert.ToFloatOrNull(sqlQuery.ToScalar(connection)));
}
protected static void LogVerboseSqlResults(ISqlQuery query, IEnumerable<object> results)
{
// This could be slow, so only proceed if it actually gets logged
if (!_VerboseSqlOutputLogger.IsInfoEnabled) return;
var verboseLogging = new StringBuilder();
verboseLogging.AppendFormat("Executed {0}", query.ResourceName).AppendLine();
foreach (object result in results)
{
verboseLogging.AppendLine(result.ToString());
}
_VerboseSqlOutputLogger.Info(verboseLogging.ToString());
}
/// <summary>
/// 获取可空float值
/// </summary>
/// <param name="sqlQuery">Sql查询对象</param>
/// <param name="connection">数据库连接</param>
public static async Task <float?> ToFloatOrNullAsync(this ISqlQuery sqlQuery, IDbConnection connection = null)
{
return(Util.Helpers.Convert.ToFloatOrNull(await sqlQuery.ToScalarAsync(connection)));
}
private void LogErrorSummary(ILog log, Exception e, ISqlQuery query)
{
var sqlException = e.InnerException as SqlException;
if (sqlException == null) return;
log.LogSqlException(sqlException, query, ConnectionString);
}
/// <summary>
/// Queues a query for use in a transaction.
/// </summary>
/// <param name="qry">The qry.</param>
public void QueueForTransaction(ISqlQuery qry)
{
QueueForTransaction(qry.GetCommand());
}
protected override int InsertAndReturnNewID(IDbConnection conn,
ISqlQuery query,
IDbTransaction transaction = null,
CommandBehavior commandBehavior = CommandBehavior.Default)
{
int result = 0;
BeforeRunQuery(query);
var newQuery= new SqlQueryDef(query.GetQuery() + "; SET @___ID=SCOPE_IDENTITY();", query.Parameters);
using (IDbCommand cmd = GetCommand(conn, newQuery, transaction))
{
IDataParameter ID = cmd.CreateParameter();
ID.ParameterName = "@___ID";
ID.DbType = System.Data.DbType.Int32;
ID.Direction = ParameterDirection.Output;
ID.Value = -1;
cmd.Parameters.Add(ID);
ExecuteSqlFinal(new Action(() =>
{
cmd.ExecuteScalar();
}));
result = Convert.ToInt32(ID.Value.ToString());
cmd.Parameters.Clear();
cmd.Dispose();
}
if (commandBehavior == CommandBehavior.CloseConnection)
{
conn.Close();
}
OnQueryComplete();
return result;
}
/// <summary>
/// Execute the given query and returns the result as a read only list
/// </summary>
public static async Task <IReadOnlyList <T> > ToListAsync <T>(this ISqlQuery <T> select, DbContext context)
{
return(await DoConnectionAsync(context, conn => select.ToListAsync(conn)));
}
