/// <summary> /// Executes the specified command asynchronously. /// </summary> /// <param name="command">The command.</param> /// <param name="cancellationToken">The cancellation token.</param> /// <returns> /// A <see cref="Task" /> that will return a <see cref="object">scalar value</see>. /// </returns> /// <exception cref="System.ArgumentNullException">Thrown when <paramref name="command" /> is <c>null</c>.</exception> public async Task <object> ExecuteScalarAsync(SqlQueryCommand command, CancellationToken cancellationToken) { if (command == null) { throw new ArgumentNullException("command"); } using (var dbConnection = _dbProviderFactory.CreateConnection()) { dbConnection.ConnectionString = _settings.ConnectionString; await dbConnection.OpenAsync(cancellationToken); try { using (var dbCommand = dbConnection.CreateCommand()) { dbCommand.Connection = dbConnection; dbCommand.CommandTimeout = _commandTimeout; dbCommand.CommandType = command.Type; dbCommand.CommandText = command.Text; dbCommand.Parameters.Clear(); dbCommand.Parameters.AddRange(command.Parameters); return(dbCommand.ExecuteScalarAsync(cancellationToken)); } } finally { dbConnection.Close(); } } }
/// <summary> /// Executes the specified command. /// </summary> /// <param name="command">The command.</param> /// <returns>A <see cref="DbDataReader"/>.</returns> /// <exception cref="System.ArgumentNullException">Thrown when <paramref name="command" /> is <c>null</c>.</exception> public DbDataReader ExecuteReader(SqlQueryCommand command) { if (command == null) { throw new ArgumentNullException("command"); } var dbConnection = _dbProviderFactory.CreateConnection(); dbConnection.ConnectionString = _connectionString; dbConnection.Open(); try { using (var dbCommand = dbConnection.CreateCommand()) { dbCommand.Connection = dbConnection; dbCommand.CommandTimeout = _commandTimeout; dbCommand.CommandType = command.Type; dbCommand.CommandText = command.Text; dbCommand.Parameters.AddRange(command.Parameters); return(dbCommand.ExecuteReader(CommandBehavior.CloseConnection | CommandBehavior.SequentialAccess)); } } catch { dbConnection.Close(); throw; } }
/// <summary> /// Executes the specified command. /// </summary> /// <param name="command">The command.</param> /// <returns>A <see cref="object">scalar value</see>.</returns> /// <exception cref="System.ArgumentNullException">Thrown when <paramref name="command" /> is <c>null</c>.</exception> public object ExecuteScalar(SqlQueryCommand command) { if (command == null) { throw new ArgumentNullException("command"); } var dbConnection = _dbProviderFactory.CreateConnection(); dbConnection.ConnectionString = _settings.ConnectionString; dbConnection.Open(); try { using (var dbCommand = dbConnection.CreateCommand()) { dbCommand.Connection = dbConnection; dbCommand.CommandTimeout = _commandTimeout; dbCommand.CommandType = command.Type; dbCommand.CommandText = command.Text; dbCommand.Parameters.AddRange(command.Parameters); return(dbCommand.ExecuteScalar()); } } catch { dbConnection.Close(); throw; } }
/// <summary> /// Executes the specified command asynchronously. /// </summary> /// <param name="command">The command.</param> /// <param name="cancellationToken">The cancellation token.</param> /// <returns> /// A <see cref="Task" /> that will return a <see cref="DbDataReader">data reader</see>. /// </returns> /// <exception cref="System.ArgumentNullException">Thrown when <paramref name="command" /> is <c>null</c>.</exception> public async Task <DbDataReader> ExecuteReaderAsync(SqlQueryCommand command, CancellationToken cancellationToken) { if (command == null) { throw new ArgumentNullException("command"); } var dbConnection = _dbProviderFactory.CreateConnection(); dbConnection.ConnectionString = _settings.ConnectionString; await dbConnection.OpenAsync(cancellationToken); try { using (var dbCommand = dbConnection.CreateCommand()) { dbCommand.Connection = dbConnection; dbCommand.CommandTimeout = _commandTimeout; dbCommand.CommandType = command.Type; dbCommand.CommandText = command.Text; dbCommand.Parameters.AddRange(command.Parameters); return(await dbCommand.ExecuteReaderAsync(CommandBehavior.CloseConnection | CommandBehavior.SequentialAccess, cancellationToken)); } } catch { dbConnection.Close(); throw; } }
public void QueryProcedureUnlessReturnsExpectedInstance(IEnumerable<SqlQueryCommand> actual, SqlQueryCommand[] expected) { var actualArray = actual.ToArray(); Assert.That(actualArray.Length, Is.EqualTo(expected.Length)); for (var index = 0; index < actualArray.Length; index++) { Assert.That(actualArray[index].Text, Is.EqualTo(expected[index].Text)); Assert.That(actualArray[index].Parameters, Is.EquivalentTo(expected[index].Parameters).Using(new SqlParameterEqualityComparer())); } }
/// <summary> /// Searches Blog Posts /// </summary> public IEnumerable <BlogPost> SearchBlogPosts(string searchTerm) { if (string.IsNullOrWhiteSpace(searchTerm)) { throw new ArgumentNullException("searchTerm"); } var query = "SELECT " + " Slug "+ " , Title " + " , Author " + " , PostContent = LEFT(PostContent, 600) " + " , DateCreated " + "FROM " + " be_Posts with(nolock) "+ "WHERE " + " IsDeleted = 0 "+ " AND IsPublished = 1 "+ " AND "+ " ( "+ " Title LIKE '%' + @SearchTerm + '%' "+ " OR Description LIKE '%' + @SearchTerm + '%' "+ " OR PostContent LIKE '%' + @SearchTerm + '%' "+ " OR Slug LIKE '%' + @SearchTerm + '%' "+ " ) "+ "ORDER BY " + " DateCreated DESC"; using (var command = SqlQueryCommand.Make(query) .UsingConnection(this.BrewgrBlogConnection.ConnectionString) .WithParam("@SearchTerm", searchTerm)) { var results = command.GetDataSet(); foreach (DataRow dataRow in results.Tables[0].Rows) { yield return(new BlogPost { Slug = dataRow["Slug"].ToString(), Title = dataRow["Title"].ToString(), Author = dataRow["Author"].ToString(), PostContent = StringCleaner.CleanForPreviewText(dataRow["PostContent"].ToString(), 400), DateCreated = Convert.ToDateTime(dataRow["DateCreated"]) }); } } }
/// <summary> /// Executes the specified command. /// </summary> /// <param name="command">The command.</param> /// <returns>A <see cref="object">scalar value</see>.</returns> /// <exception cref="System.ArgumentNullException">Thrown when <paramref name="command" /> is <c>null</c>.</exception> public object ExecuteScalar(SqlQueryCommand command) { if (command == null) { throw new ArgumentNullException("command"); } using (var dbCommand = _dbConnection.CreateCommand()) { dbCommand.Connection = _dbConnection; dbCommand.CommandTimeout = _commandTimeout; dbCommand.CommandType = command.Type; dbCommand.CommandText = command.Text; dbCommand.Parameters.AddRange(command.Parameters); return(dbCommand.ExecuteScalar()); } }
/// <summary> /// Executes the specified command asynchronously. /// </summary> /// <param name="command">The command.</param> /// <param name="cancellationToken">The cancellation token.</param> /// <returns> /// A <see cref="Task" /> that will return a <see cref="object">scalar value</see>. /// </returns> /// <exception cref="System.ArgumentNullException">Thrown when <paramref name="command" /> is <c>null</c>.</exception> public Task <object> ExecuteScalarAsync(SqlQueryCommand command, CancellationToken cancellationToken) { if (command == null) { throw new ArgumentNullException("command"); } using (var dbCommand = _dbConnection.CreateCommand()) { dbCommand.Connection = _dbConnection; dbCommand.CommandTimeout = _commandTimeout; dbCommand.CommandType = command.Type; dbCommand.CommandText = command.Text; dbCommand.Parameters.AddRange(command.Parameters); return(dbCommand.ExecuteScalarAsync(cancellationToken)); } }
/// <summary> /// Executes the specified command asynchronously. /// </summary> /// <param name="command">The command.</param> /// <param name="cancellationToken">The cancellation token.</param> /// <returns> /// A <see cref="Task" /> that will return a <see cref="DbDataReader">data reader</see>. /// </returns> /// <exception cref="System.ArgumentNullException">Thrown when <paramref name="command" /> is <c>null</c>.</exception> public Task <DbDataReader> ExecuteReaderAsync(SqlQueryCommand command, CancellationToken cancellationToken) { if (command == null) { throw new ArgumentNullException("command"); } using (var dbCommand = _dbConnection.CreateCommand()) { dbCommand.Connection = _dbConnection; dbCommand.CommandTimeout = _commandTimeout; dbCommand.CommandType = command.Type; dbCommand.CommandText = command.Text; dbCommand.Parameters.AddRange(command.Parameters); return(dbCommand.ExecuteReaderAsync(CommandBehavior.SequentialAccess, cancellationToken)); } }
/// <summary> /// Executes the specified command asynchronously. /// </summary> /// <param name="command">The command.</param> /// <returns> /// A <see cref="Task" /> that will return a <see cref="DbDataReader">data reader</see>. /// </returns> /// <exception cref="System.ArgumentNullException">Thrown when <paramref name="command" /> is <c>null</c>.</exception> public Task<DbDataReader> ExecuteReaderAsync(SqlQueryCommand command) { return ExecuteReaderAsync(command, CancellationToken.None); }
/// <summary> /// Executes the specified command asynchronously. /// </summary> /// <param name="command">The command.</param> /// <param name="cancellationToken">The cancellation token.</param> /// <returns> /// A <see cref="Task" /> that will return a <see cref="object">scalar value</see>. /// </returns> /// <exception cref="System.ArgumentNullException">Thrown when <paramref name="command" /> is <c>null</c>.</exception> public Task<object> ExecuteScalarAsync(SqlQueryCommand command, CancellationToken cancellationToken) { if (command == null) throw new ArgumentNullException("command"); using (var dbCommand = _dbConnection.CreateCommand()) { dbCommand.Connection = _dbConnection; dbCommand.CommandTimeout = _commandTimeout; dbCommand.CommandType = command.Type; dbCommand.CommandText = command.Text; dbCommand.Parameters.AddRange(command.Parameters); return dbCommand.ExecuteScalarAsync(cancellationToken); } }
/// <summary> /// Executes the specified command asynchronously. /// </summary> /// <param name="command">The command.</param> /// <returns> /// A <see cref="Task" /> that will return a <see cref="object">scalar value</see>. /// </returns> /// <exception cref="System.ArgumentNullException">Thrown when <paramref name="command" /> is <c>null</c>.</exception> public Task<object> ExecuteScalarAsync(SqlQueryCommand command) { return ExecuteScalarAsync(command, CancellationToken.None); }
/// <summary> /// Executes the specified command. /// </summary> /// <param name="command">The command.</param> /// <returns>A <see cref="object">scalar value</see>.</returns> /// <exception cref="System.ArgumentNullException">Thrown when <paramref name="command" /> is <c>null</c>.</exception> public object ExecuteScalar(SqlQueryCommand command) { if (command == null) throw new ArgumentNullException("command"); using (var dbCommand = _dbConnection.CreateCommand()) { dbCommand.Connection = _dbConnection; dbCommand.CommandTimeout = _commandTimeout; dbCommand.CommandType = command.Type; dbCommand.CommandText = command.Text; dbCommand.Parameters.AddRange(command.Parameters); return dbCommand.ExecuteScalar(); } }
public async Task<HttpResponseMessage> Get(string featureClass, string returnValues, [FromUri] SearchOptions options) { #region validation var errors = ""; if (string.IsNullOrEmpty(featureClass)) { errors = "featureClass is a required field. Input was empty. "; } else if (CommandExecutor.ExecuteCommand(new IsSafeSqlCommand(featureClass))) { errors += "featureClass contains unsafe characters. Don't be a jerk. "; } if (string.IsNullOrEmpty(returnValues)) { errors += "returnValues is a required field. Input was empty. "; } else if (CommandExecutor.ExecuteCommand(new IsSafeSqlCommand(returnValues))) { errors += "returnValues contains unsafe characters. Don't be a jerk. "; } if (options == null) { errors += "Search options did not bind correctly. Sorry. "; return Request.CreateResponse(HttpStatusCode.InternalServerError, new ResultContainer<SearchResult> { Status = (int) HttpStatusCode.InternalServerError, Message = errors }); } if (!string.IsNullOrEmpty(options.Predicate) && CommandExecutor.ExecuteCommand(new IsSafeSqlCommand(options.Predicate))) { errors += "Predicate contains unsafe characters. Don't be a jerk. "; } if (errors.Length > 0) { return Request.CreateResponse(HttpStatusCode.BadRequest, new ResultContainer<SearchResult> { Status = (int) HttpStatusCode.BadRequest, Message = errors }); } #endregion // ReSharper disable PossibleNullReferenceException - handled in validation featureClass = featureClass.ToUpperInvariant(); var isStraightSql = !returnValues.ToUpperInvariant().Contains("SHAPE@") && string.IsNullOrEmpty(options.Geometry); // ReSharper restore PossibleNullReferenceException if (isStraightSql) { var sqlQueryCommand = new SqlQueryCommand(featureClass, returnValues, options.Predicate); var list = CommandExecutor.ExecuteCommand(sqlQueryCommand); if (!string.IsNullOrEmpty(sqlQueryCommand.ErrorMessage)) { string message; var error = sqlQueryCommand.ErrorMessage.ToUpperInvariant(); if (error.Contains("INVALID COLUMN NAME")) { const string pattern = @"\'.*?\'"; var matches = new Regex(pattern).Matches(sqlQueryCommand.ErrorMessage); var badColumns = new Collection<string>(); foreach (var match in matches) { badColumns.Add(match.ToString()); } message = "{0} does not contain an attribute {1}. Check your spelling.".With(featureClass, string.Join( " or ", badColumns)); } else if (error.Contains("AN EXPRESSION OF NON-BOOLEAN TYPE SPECIFIED IN A CONTEXT WHERE A CONDITION IS EXPECTED")) { message = "{0} is not a valid ArcObjects where clause.".With(options.Predicate); } else { message = "{0} probably does not exist. Check your spelling.".With(featureClass); } return Request.CreateResponse(HttpStatusCode.BadRequest, new ResultContainer<List<SearchResult>> { Message = message, Status = (int) HttpStatusCode.BadRequest }) .AddCache() .AddTypeHeader(typeof (ResultContainer<List<SearchResult>>)); } if (list.Any()) { if (options.AttributeStyle != AttributeStyle.Identical) { list = CommandExecutor.ExecuteCommand(new FormatAttributesCommand(options.AttributeStyle, list)); } } return Request.CreateResponse(HttpStatusCode.OK, new ResultContainer<List<SearchResult>> { Result = list ?? new List<SearchResult>(), Status = (int) HttpStatusCode.OK }) .AddCache() .AddTypeHeader(typeof (ResultContainer<List<SearchResult>>)); } var queryArgs = new SpatialQueryArgs(featureClass, returnValues, options); var client = new HttpClient(); var requestUri = ConfigurationManager.AppSettings["search_url"] .With(queryArgs.ToQueryString()); HttpResponseMessage request; try { request = await client.GetAsync(requestUri); } catch (AggregateException) { return Request.CreateResponse(HttpStatusCode.InternalServerError, new ResultContainer<List<SearchResult>> { Status = (int) HttpStatusCode.InternalServerError, Message = "I'm sorry, it seems as though the request had issues." }); } try { request.EnsureSuccessStatusCode(); } catch (Exception) { return Request.CreateResponse(HttpStatusCode.InternalServerError, new ResultContainer<List<SearchResult>> { Status = (int) HttpStatusCode.InternalServerError, Message = "I'm sorry, we were unable to communicate with the SOE." }); } var response = await request.Content.ReadAsAsync<SearchResponse>(new[] { new TextPlainResponseFormatter() }); if (!response.IsSuccessful) { var error = response.Error.Message.ToUpperInvariant(); var message = error; if (error == "DBMS TABLE NOT FOUND") { message = "{0} does not exist. Check your spelling.".With(featureClass); } return Request.CreateResponse(HttpStatusCode.BadRequest, new ResultContainer<List<SearchResult>> { Status = (int) HttpStatusCode.BadRequest, Message = message }) .AddTypeHeader(typeof (ResultContainer<List<SearchResult>>)); } if (response.Results == null) { return Request.CreateResponse(HttpStatusCode.OK, new ResultContainer<List<SearchResult>> { Status = (int) HttpStatusCode.OK, Result = new List<SearchResult>() }) .AddCache() .AddTypeHeader(typeof (ResultContainer<List<SearchResult>>)); } var resultsWithGeometry = response.Results.Select(x => new SearchResult { Attributes = x.Attributes, Geometry = ParseGeometry(x.Geometry) }).ToList(); if (options.AttributeStyle != AttributeStyle.Identical) { resultsWithGeometry = CommandExecutor.ExecuteCommand(new FormatAttributesCommand(options.AttributeStyle, resultsWithGeometry)); } return Request.CreateResponse(HttpStatusCode.OK, new ResultContainer<List<SearchResult>> { Status = (int) HttpStatusCode.OK, Result = resultsWithGeometry }) .AddCache() .AddTypeHeader(typeof (ResultContainer<List<SearchResult>>)); }
public void QueryProcedureReturnsExpectedInstance(SqlQueryCommand actual, SqlQueryCommand expected) { Assert.That(actual.Text, Is.EqualTo(expected.Text)); Assert.That(actual.Parameters, Is.EquivalentTo(expected.Parameters).Using(new SqlParameterEqualityComparer())); }
/// <summary> /// Executes the specified command. /// </summary> /// <param name="command">The command.</param> /// <returns>A <see cref="object">scalar value</see>.</returns> /// <exception cref="System.ArgumentNullException">Thrown when <paramref name="command" /> is <c>null</c>.</exception> public object ExecuteScalar(SqlQueryCommand command) { if (command == null) throw new ArgumentNullException("command"); var dbConnection = _dbProviderFactory.CreateConnection(); dbConnection.ConnectionString = _settings.ConnectionString; dbConnection.Open(); try { using (var dbCommand = dbConnection.CreateCommand()) { dbCommand.Connection = dbConnection; dbCommand.CommandTimeout = _commandTimeout; dbCommand.CommandType = command.Type; dbCommand.CommandText = command.Text; dbCommand.Parameters.AddRange(command.Parameters); return dbCommand.ExecuteScalar(); } } catch { dbConnection.Close(); throw; } }
/// <summary> /// Executes the specified command asynchronously. /// </summary> /// <param name="command">The command.</param> /// <param name="cancellationToken">The cancellation token.</param> /// <returns> /// A <see cref="Task" /> that will return a <see cref="DbDataReader">data reader</see>. /// </returns> /// <exception cref="System.ArgumentNullException">Thrown when <paramref name="command" /> is <c>null</c>.</exception> public Task<DbDataReader> ExecuteReaderAsync(SqlQueryCommand command, CancellationToken cancellationToken) { if (command == null) throw new ArgumentNullException("command"); using (var dbCommand = _dbConnection.CreateCommand()) { dbCommand.Connection = _dbConnection; dbCommand.CommandTimeout = _commandTimeout; dbCommand.CommandType = command.Type; dbCommand.CommandText = command.Text; dbCommand.Parameters.AddRange(command.Parameters); return dbCommand.ExecuteReaderAsync(CommandBehavior.SequentialAccess, cancellationToken); } }
/// <summary> /// Executes the specified command asynchronously. /// </summary> /// <param name="command">The command.</param> /// <returns> /// A <see cref="Task" /> that will return a <see cref="object">scalar value</see>. /// </returns> /// <exception cref="System.ArgumentNullException">Thrown when <paramref name="command" /> is <c>null</c>.</exception> public Task <object> ExecuteScalarAsync(SqlQueryCommand command) { return(ExecuteScalarAsync(command, CancellationToken.None)); }
/// <summary> /// Executes the specified command asynchronously. /// </summary> /// <param name="command">The command.</param> /// <param name="cancellationToken">The cancellation token.</param> /// <returns> /// A <see cref="Task" /> that will return a <see cref="object">scalar value</see>. /// </returns> /// <exception cref="System.ArgumentNullException">Thrown when <paramref name="command" /> is <c>null</c>.</exception> public async Task<object> ExecuteScalarAsync(SqlQueryCommand command, CancellationToken cancellationToken) { if (command == null) throw new ArgumentNullException("command"); using (var dbConnection = _dbProviderFactory.CreateConnection()) { dbConnection.ConnectionString = _settings.ConnectionString; await dbConnection.OpenAsync(cancellationToken); try { using (var dbCommand = dbConnection.CreateCommand()) { dbCommand.Connection = dbConnection; dbCommand.CommandTimeout = _commandTimeout; dbCommand.CommandType = command.Type; dbCommand.CommandText = command.Text; dbCommand.Parameters.Clear(); dbCommand.Parameters.AddRange(command.Parameters); return dbCommand.ExecuteScalarAsync(cancellationToken); } } finally { dbConnection.Close(); } } }
/// <summary> /// Executes the specified command asynchronously. /// </summary> /// <param name="command">The command.</param> /// <param name="cancellationToken">The cancellation token.</param> /// <returns> /// A <see cref="Task" /> that will return a <see cref="DbDataReader">data reader</see>. /// </returns> /// <exception cref="System.ArgumentNullException">Thrown when <paramref name="command" /> is <c>null</c>.</exception> public async Task<DbDataReader> ExecuteReaderAsync(SqlQueryCommand command, CancellationToken cancellationToken) { if (command == null) throw new ArgumentNullException("command"); var dbConnection = _dbProviderFactory.CreateConnection(); dbConnection.ConnectionString = _settings.ConnectionString; await dbConnection.OpenAsync(cancellationToken); try { using (var dbCommand = dbConnection.CreateCommand()) { dbCommand.Connection = dbConnection; dbCommand.CommandTimeout = _commandTimeout; dbCommand.CommandType = command.Type; dbCommand.CommandText = command.Text; dbCommand.Parameters.AddRange(command.Parameters); return await dbCommand.ExecuteReaderAsync(CommandBehavior.CloseConnection | CommandBehavior.SequentialAccess, cancellationToken); } } catch { dbConnection.Close(); throw; } }
public void QueryStatementFormatReturnsExpectedInstance(SqlQueryCommand actual, SqlQueryCommand expected) { Assert.That(actual.Text, Is.EqualTo(expected.Text)); Assert.That(actual.Parameters, Is.EquivalentTo(expected.Parameters).Using(new SqlParameterEqualityComparer())); }
/// <summary> /// Executes the specified command asynchronously. /// </summary> /// <param name="command">The command.</param> /// <returns> /// A <see cref="Task" /> that will return a <see cref="DbDataReader">data reader</see>. /// </returns> /// <exception cref="System.ArgumentNullException">Thrown when <paramref name="command" /> is <c>null</c>.</exception> public Task <DbDataReader> ExecuteReaderAsync(SqlQueryCommand command) { return(ExecuteReaderAsync(command, CancellationToken.None)); }