public static async Task UpdateRows() { string connectionString = ConfigurationManager.ConnectionStrings["ProgrammingCSharpConnection"].ConnectionString; using (SqlConnection connection = new SqlConnection(connectionString)) { SqlCommand command = new SqlCommand("UPDATE People SET FirstName='John'", connection); await connection.OpenAsync(); int numberOfUpdatedRows = await command.ExecuteNonQueryAsync(); Console.WriteLine("Updated {0} rows", numberOfUpdatedRows); } }
private async Task AddStatistic(SqlConnection con, PackageResult package) { const string sql = @"MERGE dbo.PackageStatistics AS t USING ( SELECT @name as 'Name', @latestSupportType as 'LatestSupportType' ) AS s ON t.Name = s.Name WHEN MATCHED THEN UPDATE SET LatestSupportType = s.LatestSupportType, [Count] = [Count] + 1 WHEN NOT MATCHED THEN INSERT (Name, LatestSupportType, Count) VALUES (s.Name, s.LatestSupportType, 1);"; try { using (var cmd = new SqlCommand(sql, con)) { cmd.Parameters.AddWithValue("@name", package.PackageName); cmd.Parameters.AddWithValue("@latestSupportType", package.SupportType.ToString()); await cmd.ExecuteNonQueryAsync(); } } catch (Exception ex) { Log.Logger.Error(ex, "Exception writing statistic {stat}", package.PackageName); } }
private async Task RunScriptAsync(string script, CancellationToken cancellationToken = default(CancellationToken)) { var cxnString = cfg.Properties["connection.connection_string"] + "; Pooling=No"; // Disable connection pooling so this won't be hindered by // problems encountered during the actual test string sql; using (var reader = new StreamReader(GetType().Assembly.GetManifestResourceStream(GetType().Namespace + "." + script))) { sql = await(reader.ReadToEndAsync()); } using (var cxn = new SqlConnection(cxnString)) { await(cxn.OpenAsync(cancellationToken)); foreach (var batch in Regex.Split(sql, @"^go\s*$", RegexOptions.IgnoreCase | RegexOptions.Multiline) .Where(b => !string.IsNullOrEmpty(b))) { using (var cmd = new System.Data.SqlClient.SqlCommand(batch, cxn)) { await(cmd.ExecuteNonQueryAsync(cancellationToken)); } } } }
/// <summary> /// This method is called whenever the module is sent a message from the EdgeHub. /// It just pipe the messages without any change. /// It prints all the incoming messages. /// </summary> static async Task <MessageResponse> ProcessInputAsa(Message message, object userContext) { int counterValue = Interlocked.Increment(ref counterAsaMessages); byte[] messageBytes = message.GetBytes(); string messageString = Encoding.UTF8.GetString(messageBytes); _logger.Log($"Received ASA message: {counterValue}"); if (!string.IsNullOrEmpty(messageString)) { var eventList = JsonConvert.DeserializeObject <List <MessageBodyAsaInput> >(messageString); string insertRowStatement = ""; foreach (MessageBodyAsaInput item in eventList) { insertRowStatement += $"INSERT INTO MeasurementsDB.dbo.AggreatedMeasurements (deviceId, timestamp, avgHumidity, avgTemperature) VALUES ({item.deviceId}', CONVERT(DATETIME2,'{item.WindowEndTime}', 127), {item.avgHumidity}, {item.avgTemperature});\n"; } //Store the data in SQL db using (Sql.SqlConnection conn = new Sql.SqlConnection(_sqlConnString)) { conn.Open(); using (Sql.SqlCommand cmd = new Sql.SqlCommand(insertRowStatement, conn)) { //Execute the command and log the # rows affected. var rows = await cmd.ExecuteNonQueryAsync(); _logger.Log($"{rows} rows were inserted"); } } } return(MessageResponse.Completed); }
public async Task<ActionResult> FileUpload(IEnumerable<HttpPostedFileBase> files) { var result = new List<FileUpload>(); foreach (var file in files) { byte[] data = new byte[file.ContentLength]; await file.InputStream.ReadAsync(data, 0, file.ContentLength); string fileName = Path.GetFileName(file.FileName); int Id; using (var conn = new SqlConnection(ConfigurationManager.ConnectionStrings["SamplesDb"].ConnectionString)) { using (var comm = new SqlCommand( "INSERT INTO Files (FileName, Data, ContentType)" + " VALUES(@FileName, @Data, @ContentType);" + " SELECT @Id=SCOPE_IDENTITY()", conn)) { comm.Parameters.AddWithValue("@FileName", fileName); comm.Parameters.AddWithValue("@Data", data); comm.Parameters.AddWithValue("@ContentType", file.ContentType); comm.Parameters.Add(new SqlParameter("@Id", System.Data.SqlDbType.Int)).Direction = System.Data.ParameterDirection.Output; await conn.OpenAsync(); await comm.ExecuteNonQueryAsync(); Id = (int)comm.Parameters["@Id"].Value; result.Add(new FileUpload { Id = Id, FileName = fileName,ContentType=file.ContentType }); } } } return View(result); }
public static async Task InsertPurchaseOrderHeaderAsync(string cnStr) { const string queryString = "INSERT INTO Purchasing.PurchaseOrderHeader " + "(RevisionNumber, Status, EmployeeID, VendorID, ShipMethodID, OrderDate, ShipDate, SubTotal, TaxAmt, Freight, ModifiedDate) " + "VALUES " + "(@RevisionNumber, @Status, @EmployeeID, @VendorID, @ShipMethodID, @OrderDate, @ShipDate, @SubTotal, @TaxAmt, @Freight, @ModifiedDate)"; var dt = DateTime.UtcNow; using (var cn = new SqlConnection(cnStr)) { using (var cmd = new SqlCommand(queryString, cn)) { cmd.Parameters.AddWithValue("@RevisionNumber", 1); cmd.Parameters.AddWithValue("@Status", 4); cmd.Parameters.AddWithValue("@EmployeeID", 258); cmd.Parameters.AddWithValue("@VendorID", 1580); cmd.Parameters.AddWithValue("@ShipMethodID", 3); cmd.Parameters.AddWithValue("@OrderDate", dt); cmd.Parameters.AddWithValue("@ShipDate", dt); cmd.Parameters.AddWithValue("@SubTotal", 123.40M); cmd.Parameters.AddWithValue("@TaxAmt", 12.34M); cmd.Parameters.AddWithValue("@Freight", 5.76M); cmd.Parameters.AddWithValue("@ModifiedDate", dt); await cn.OpenAsync().ConfigureAwait(false); await cmd.ExecuteNonQueryAsync().ConfigureAwait(false); } } }
public Task Send(Message[] messages) { if (messages == null || messages.Length == 0) { return TaskAsyncHelper.Empty; } SqlConnection connection = null; try { connection = new SqlConnection(_connectionString); connection.Open(); using (var cmd = new SqlCommand(_insertSql, connection)) { cmd.Parameters.AddWithValue("Payload", JsonConvert.SerializeObject(messages)); return cmd.ExecuteNonQueryAsync() .Then(() => connection.Close()) // close the connection if successful .Catch(ex => connection.Close()); // close the connection if it explodes } } catch (SqlException) { if (connection != null && connection.State != ConnectionState.Closed) { connection.Close(); } throw; } }
/// <summary> /// Performs a sequence of non blocking database operations. /// </summary> private async static Task NonBlockingDatabaseOperations() { // Build the database connection. using (SqlConnection sqlConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["SampleHttpApplication"].ConnectionString)) { // Open the database connection. await sqlConnection.OpenAsync(); // Insert the database row. SqlCommand insertSqlCommand = new SqlCommand("INSERT INTO [Session] VALUES('" + Guid.NewGuid() + "', 'Session Alpha', '2012-06-27 10:05:45'); SELECT CAST(SCOPE_IDENTITY() AS INT);", sqlConnection); int sessionID = (int)await insertSqlCommand.ExecuteScalarAsync(); // Select the database row. SqlCommand selectSqlCommand = new SqlCommand("SELECT * FROM [Session] WHERE [SessionID] = " + sessionID, sqlConnection); SqlDataReader sqlDataReader = await selectSqlCommand.ExecuteReaderAsync(); await sqlDataReader.ReadAsync(); sqlDataReader.Close(); // Update the database row. SqlCommand updateSqlCommand = new SqlCommand("UPDATE [Session] SET [SessionCode] = '" + Guid.NewGuid() + "', [Name] = 'Session Beta', [StartDate] = '2013-07-28 11:06:46' WHERE [SessionID] = " + sessionID, sqlConnection); await updateSqlCommand.ExecuteNonQueryAsync(); // Delete the database row. SqlCommand deleteSqlCommand = new SqlCommand("DELETE FROM [Session] WHERE [SessionID] = " + sessionID, sqlConnection); await deleteSqlCommand.ExecuteNonQueryAsync(); } }
public async Task InsertHttpRequestLogAsync(HttpRequestLog request) { using (var conn = new SqlConnection(_connectionString)) { await conn.OpenAsync(); var cmd = new SqlCommand("uspInsertRequestLog", conn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@Id", request.Id); cmd.Parameters.AddWithValue("@UserName", request.UserName); cmd.Parameters.AddWithValue("@UserIpAddress", request.UserIpAddress); cmd.Parameters.AddWithValue("@HttpAction", request.HttpAction); cmd.Parameters.AddWithValue("@RequestUrl", request.RequestUrl); cmd.Parameters.AddWithValue("@RequestHeader", request.RequestHeader); cmd.Parameters.AddWithValue("@RequestBody", request.RequestBody); cmd.Parameters.AddWithValue("@UserAgent", request.UserAgent); cmd.Parameters.AddWithValue("@DeviceInfo", request.DeviceInfo); cmd.Parameters.AddWithValue("@BrowserInfo", request.BrowserInfo); cmd.Parameters.AddWithValue("@IsAnonymous", request.IsAnonymous); cmd.Parameters.AddWithValue("@IsAuthenticated", request.IsAuthenticated); cmd.Parameters.AddWithValue("@IsGuest", request.IsGuest); cmd.Parameters.AddWithValue("@IsSystem", request.IsSystem); cmd.Parameters.AddWithValue("@RequestTimeStamp", request.RequestTimeStamp); await cmd.ExecuteNonQueryAsync(); conn.Close(); } }
public static Task<int> ExecuteNonQueryAsyncWithRetry( SqlCommand command, CancellationToken cancellationToken, IRetryPolicy retryPolicy) { return retryPolicy.ExecuteAsyncWithRetry(() => command.ExecuteNonQueryAsync(cancellationToken)); }
public static async Task TruncateTransactionalOutboxTableAsync(this SystemData.SqlConnection sqlConnection) { //CLEAR the Table for Integration Tests to validate: await using var sqlCmd = new SystemData.SqlCommand( SqlCommands.TruncateTransactionalOutbox, sqlConnection ); await sqlCmd.ExecuteNonQueryAsync().ConfigureAwait(false); }
public async Task Delete() { using (var connection = new SqlConnection(_connectionString)) { connection.Open(); var command = new SqlCommand("DROP TABLE Customers", connection); await command.ExecuteNonQueryAsync(); } }
public async Task ExecuteAsync(ScriptFile script, BuildItem.BuildActionType action) { if(action == BuildItem.BuildActionType.Drop || action == BuildItem.BuildActionType.DropAndCreate) { string cmdText = string.Format("DROP {0} [{1}].[{2}]", script.ScriptObject.ObjectType.ToString(), script.ScriptObject.SchemaName, script.ScriptObject.ObjectName); var cmd = new SqlCommand(cmdText, Connection); await cmd.ExecuteNonQueryAsync(); } }
private async Task CreateDatabase() { using(var connection = _localDbInstance.CreateConnection()) { await connection.OpenAsync(); using(var command = new SqlCommand($"CREATE DATABASE {_databaseName}", connection)) { await command.ExecuteNonQueryAsync(); } } }
private async Task<int> ExecuteQuery(string connectionString, string query) { using (var conn = new SqlConnection(connectionString)) { await conn.OpenAsync(); using (var cmd = new SqlCommand(query, conn)) { return await cmd.ExecuteNonQueryAsync(); } } }
public static async Task Update( this Tag TagToUpdate, SqlConnection Connection, SqlTransaction Transaction = null ) { using( SqlCommand Command = new SqlCommand() ) { Command.Connection = Connection; if( null != Transaction ) Command.Transaction = Transaction; Command.CommandText = "UPDATE Tags SET Text = @Text WHERE Id = @Id;"; Command.Parameters.Add( "Text", System.Data.SqlDbType.VarChar ).Value = TagToUpdate.Text; Command.Parameters.Add( "Id", System.Data.SqlDbType.SmallInt ).Value = TagToUpdate.Id; await Command.ExecuteNonQueryAsync(); } }
public async Task DeleteCacheItemAsync(string key) { using (var connection = new SqlConnection(ConnectionString)) { var command = new SqlCommand(SqlQueries.DeleteCacheItem, connection); command.Parameters.AddCacheItemId(key); await connection.OpenAsync(); await command.ExecuteNonQueryAsync(); } }
public static async Task FilterMessageAndSendMessage( [EdgeHubTrigger("input1")] Message messageReceived, [EdgeHub(OutputName = "output1")] IAsyncCollector <Message> output, ILogger logger) { const int temperatureThreshold = 20; byte[] messageBytes = messageReceived.GetBytes(); var messageString = System.Text.Encoding.UTF8.GetString(messageBytes); if (!string.IsNullOrEmpty(messageString)) { logger.LogInformation("Info: Received one non-empty message"); // Get the body of the message and deserialize it. var messageBody = JsonConvert.DeserializeObject <MessageBody>(messageString); //Store the data in SQL db const string str = "Data Source=tcp:sql,1433;Initial Catalog=MeasurementsDB;User Id=SA;Password=Strong!Passw0rd;TrustServerCertificate=False;Connection Timeout=30;"; using (Sql.SqlConnection conn = new Sql.SqlConnection(str)) { conn.Open(); var insertMachineTemperature = "INSERT INTO MeasurementsDB.dbo.TemperatureMeasurements VALUES (CONVERT(DATETIME2,'" + messageBody.timeCreated + "', 127), 'machine', " + messageBody.machine.temperature + ");"; var insertAmbientTemperature = "INSERT INTO MeasurementsDB.dbo.TemperatureMeasurements VALUES (CONVERT(DATETIME2,'" + messageBody.timeCreated + "', 127), 'ambient', " + messageBody.ambient.temperature + ");"; using (Sql.SqlCommand cmd = new Sql.SqlCommand(insertMachineTemperature + "\n" + insertAmbientTemperature, conn)) { //Execute the command and log the # rows affected. var rows = await cmd.ExecuteNonQueryAsync(); logger.LogInformation($"{rows} rows were updated"); } } if (messageBody != null && messageBody.machine.temperature > temperatureThreshold) { // Send the message to the output as the temperature value is greater than the threashold. using (var filteredMessage = new Message(messageBytes)) { // Copy the properties of the original message into the new Message object. foreach (KeyValuePair <string, string> prop in messageReceived.Properties) { filteredMessage.Properties.Add(prop.Key, prop.Value); } // Add a new property to the message to indicate it is an alert. filteredMessage.Properties.Add("MessageType", "Alert"); // Send the message. await output.AddAsync(filteredMessage); logger.LogInformation("Info: Received and transferred a message with temperature above the threshold"); } } } }
public async Task CreateMessageTypeAsync(MessageType messageType) { var messageTypes = new List<string>(); var validation = "NONE"; if (messageType.Validation.Equals(MessageTypeValidation.Empty)) { validation = "EMPTY"; } else if (messageType.Validation.Equals(MessageTypeValidation.WellFormedXml)) { validation = "WELL_FORMED_XML"; if (messageType.Validation.XmlSchemaCollectionName != null) { var collection = messageType.Validation.XmlSchemaCollectionName.Escaped(); validation = $"VALID_XML WITH SCHEMA COLLECTION {collection}"; } } var sql = $@" IF NOT EXISTS (SELECT 1 FROM sys.service_message_types m WHERE m.name = @name) BEGIN CREATE MESSAGE TYPE {messageType.Name.Escaped()} VALIDATION = {validation} END ELSE BEGIN ALTER MESSAGE TYPE {messageType.Name.Escaped()} VALIDATION = {validation} END "; var command = new SqlCommand(sql, connection) { Parameters = { new SqlParameter { ParameterName = "@name", Value = messageType.Name.Value, SqlDbType = SqlDbType.NVarChar, Size = 128, }, } }; await command.ExecuteNonQueryAsync(); }
public async Task<IEnumerable<string>> Post() { var result = new List<string>(); var watch = new Stopwatch(); using (var connection = new SqlConnection(_connectionString)) { connection.Open(); var command = new SqlCommand("CREATE TABLE Customers (Id uniqueidentifier, Forename nvarchar(200), Surname nvarchar(200), PRIMARY KEY (Id))", connection); await command.ExecuteNonQueryAsync(); command.CommandText = "INSERT INTO Customers (Id, Forename, Surname) VALUES(@Id, @Forename, @Surname)"; for (int i = 1; i < 11; i++) { AddInsertParameters(command, i); watch.Restart(); await command.ExecuteNonQueryAsync(); watch.Stop(); result.Add($"SQL INSERT {i} try: {watch.ElapsedMilliseconds}"); } } return result; }
public async Task RecordVisitAsync(string ipAddress) { const string insertStatement = "INSERT INTO Visits(IpAddress, CreatedOn) VALUES(@IpAddress, @CreatedOn)"; using (var conn = new SqlConnection(_connectionString)) { using (var cmd = new SqlCommand(insertStatement, conn)) { cmd.Parameters.Add("IpAddress", SqlDbType.NVarChar).Value = ipAddress; cmd.Parameters.Add("CreatedOn", SqlDbType.DateTimeOffset).Value = DateTimeOffset.Now; await conn.OpenAsync(); await cmd.ExecuteNonQueryAsync(); } } }
/// <summary> /// Writes the logevent to the database. /// </summary> /// <param name="args">The log event arguments.</param> /// <exception cref="System.Data.SqlException">Thrown if an error occured while saving to the database.</exception> /// <returns>A Task object that can be used to listen for completion of the work.</returns> /// <remarks> /// The SQL database the provider ConnectionString connects to, should contain an ArachneLog table. /// The table should have 5 columns: INT id NOT NULL, VARCHAR(10) level NOT NULL, DATETIME timstamp NOT NULL, VARCHAR(MAX) message NOT NULL, VARCHAR(MAX) exception /// </remarks> public async Task OnLogEventAsync(LogArgs args) { string connectionString = ConfigurationManager.ConnectionStrings[_connectionStringName].ConnectionString; using (SqlConnection con = new SqlConnection(connectionString)) { using (SqlCommand cmd = new SqlCommand("INSERT INTO ArachneLog (level, timestamp, message, exception) VALUES (@lvl, @time, @msg, @ex)", con)) { cmd.Parameters.AddWithValue("@lvl", Enum.GetName(typeof(LogLevel), args.Level)); cmd.Parameters.AddWithValue("@time", args.Timestamp); cmd.Parameters.AddWithValue("@msg", args.Message); cmd.Parameters.AddWithValue("@ex", args.Exception); await con.OpenAsync(); await cmd.ExecuteNonQueryAsync(); } } }
public static async Task InsertRowWithParameterizedQuery() { string connectionString = ConfigurationManager.ConnectionStrings["ProgrammingCSharpConnection"].ConnectionString; using (SqlConnection connection = new SqlConnection(connectionString)) { SqlCommand command = new SqlCommand("INSERT INTO People([FirstName], [LastName], [MiddleName]) VALUES(@firstName, @lastName, @middleName)", connection); await connection.OpenAsync(); command.Parameters.AddWithValue("@firstName", "John"); command.Parameters.AddWithValue("@lastName", "Doe"); command.Parameters.AddWithValue("@middleName", "Little"); int numberOfInsertedRows = await command.ExecuteNonQueryAsync(); Console.WriteLine("Inserted {0} rows", numberOfInsertedRows); } }
public async Task<int> DownloadCarparkData() { DataTable dt = await DownloadByUri("https://api.at.govt.nz/v2/public/display/parkinglocations?", DownloadType.Carpark).ConfigureAwait(false); using (SqlConnection con = new SqlConnection(Helper.GetCurrentDbConnectionString())) { using (SqlCommand cmd = new SqlCommand("Update_Table_Carpark", con)) { cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("tblCarpark", dt); con.Open(); await cmd.ExecuteNonQueryAsync().ConfigureAwait(false); } } return dt.Rows.Count; }
public async Task FinalizeRequestLogAsync(HttpRequestLog request) { using (var conn = new SqlConnection(_connectionString)) { await conn.OpenAsync(); var cmd = new SqlCommand("uspFinalizeRequestLog", conn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@Id", request.Id); cmd.Parameters.AddWithValue("@Response", request.Response); cmd.Parameters.AddWithValue("@ResponseBody", request.ResponseBody); cmd.Parameters.AddWithValue("@ResponseTimeStamp", request.ResposneTimeStamp); cmd.Parameters.AddWithValue("@RequestTotalTime", request.RequestTotalTime); await cmd.ExecuteNonQueryAsync(); conn.Close(); } }
protected async override Task<HttpResponseMessage> SendAsync( HttpRequestMessage Request, CancellationToken CancelToken ) { Guid SessionToken = GetSessionToken( Request ); if( !SessionToken.Equals( Guid.Empty ) ) { using( SqlConnection Connection = new SqlConnection( RoleEnvironment.GetConfigurationSettingValue( "DatabaseConnectionString" ) ) ) { Connection.Open(); using( SqlCommand Command = new SqlCommand() ) { Command.Connection = Connection; Command.CommandText = "SELECT EmailAddress FROM Speakers WHERE SessionToken = @SessionToken AND SessionExpires > @CurrentTime"; Command.Parameters.Add( "SessionToken", System.Data.SqlDbType.UniqueIdentifier ).Value = SessionToken; Command.Parameters.Add( "CurrentTime", System.Data.SqlDbType.DateTime ).Value = DateTime.UtcNow; using( SqlDataReader DataReader = await Command.ExecuteReaderAsync() ) { if( DataReader.Read() ) { GenericIdentity UserIdentity = new GenericIdentity( DataReader.GetString( 0 ) ); GenericPrincipal UserPrincipal = new GenericPrincipal( UserIdentity, null ); Thread.CurrentPrincipal = UserPrincipal; Request.GetRequestContext().Principal = UserPrincipal; } else { // return 401 HttpResponseMessage Response401 = new HttpResponseMessage( System.Net.HttpStatusCode.Unauthorized ); Response401.Headers.Add( "Access-Control-Allow-Origin", Request.Headers.GetValues( "Origin" ).First() ); Response401.Headers.Add( "Access-Control-Allow-Credentials", "true" ); return Response401; } } } HttpResponseMessage Response = await base.SendAsync( Request, CancelToken ); using( SqlCommand Command = new SqlCommand() ) { Command.Connection = Connection; Command.CommandText = "UPDATE Speakers SET SessionExpires = @SessionExpires WHERE SessionToken = @SessionToken"; Command.Parameters.Add( "SessionToken", System.Data.SqlDbType.UniqueIdentifier ).Value = SessionToken; Command.Parameters.Add( "SessionExpires", System.Data.SqlDbType.DateTime ).Value = DateTime.UtcNow.AddMinutes( 20 ); await Command.ExecuteNonQueryAsync(); } return Response; } } return await base.SendAsync( Request, CancelToken ); }
public async Task Revoke(string type) { using (var connection = new SqlConnection(connectionString)) { connection.Open(); using (var transaction = connection.BeginTransaction()) { using (var command = new SqlCommand(@" DELETE FROM [Data] WHERE [Owner] = @Owner AND [Type] = @Type ", connection, transaction)) { command.Parameters.AddWithValue("Owner", owner).DbType = DbType.AnsiString; command.Parameters.AddWithValue("Type", type).DbType = DbType.AnsiString; await command.ExecuteNonQueryAsync(); } transaction.Commit(); } } }
private static async Task DeadlockParticipantAsync(SqlConnection connection, bool isVictim, SemaphoreSlim myLock, SemaphoreSlim partnerLock, CancellationToken cancellationToken = default(CancellationToken)) { try { // // CLID = 1 has only 10 records, CLID = 3 has 100. This guarantees // which process will be chosen as the victim (the one which will have // less work to rollback) // var clid = isVictim ? 1 : 3; using (var cmd = new System.Data.SqlClient.SqlCommand("UPDATE DeadlockHelper SET Data = newid() WHERE CLId = @CLID", connection)) { // // Exclusive lock on some records in the table // cmd.Parameters.AddWithValue("@CLID", clid); await(cmd.ExecuteNonQueryAsync(cancellationToken)); } } finally { // // Notify partner that I have finished my work // myLock.Release(); } // // Wait for partner to finish its work // if (!await(partnerLock.WaitAsync(120000, cancellationToken))) { throw new InvalidOperationException("Wait for partner has taken more than two minutes"); } using (var cmd = new System.Data.SqlClient.SqlCommand("SELECT TOP 1 Data FROM DeadlockHelper ORDER BY Data", connection)) { // // Requires shared lock on table, should be blocked by // partner's exclusive lock // await(cmd.ExecuteNonQueryAsync(cancellationToken)); } }
public static async Task LogAsync(string cnStr, string logTableName) { string queryString = "INSERT INTO dbo." + logTableName + " (LogId, Message, LogTime) VALUES (@LogId, @Message, @LogTime)"; var logMessage = new LogMessage(); using (var cn = new SqlConnection(cnStr)) { using (var cmd = new SqlCommand(queryString, cn)) { cmd.Parameters.AddWithValue("@LogId", logMessage.LogId); cmd.Parameters.AddWithValue("@Message", logMessage.Message); cmd.Parameters.AddWithValue("@LogTime", logMessage.LogTime); await cn.OpenAsync().ConfigureAwait(false); await cmd.ExecuteNonQueryAsync().ConfigureAwait(false); } } }
public async Task RunCheck(Source source, string connectionString) { var playlists = await GetSongs(source.Url); using (SqlConnection conn = new SqlConnection(connectionString)) { conn.Open(); using (SqlCommand cmd = new SqlCommand(mergeSql, conn)) { foreach (var playlist in playlists.OrderBy(t => t.Key)) { cmd.Parameters.Clear(); cmd.Parameters.AddWithValue("Station", playlist.Value.Station); cmd.Parameters.AddWithValue("Title", playlist.Value.Title); cmd.Parameters.AddWithValue("Artist", playlist.Value.Artist); await cmd.ExecuteNonQueryAsync(); } } } }
public async Task DoAnotherCoolThing() { var sqlConnection = new SqlConnection(); var sqlCommand = new SqlCommand(); try { await sqlConnection.OpenAsync(); sqlCommand.Connection = sqlConnection; await sqlCommand.ExecuteNonQueryAsync(); } catch (Exception ex) { await _logger.LogAsync(ex); } finally { if (sqlConnection.State != ConnectionState.Closed) await sqlConnection.CloseAsync(); } }
public async Task ExecuteAsync(ScriptFile script, BuildItem.BuildActionType action) { string cmdText; switch (action) { case BuildItem.BuildActionType.Drop: return; case BuildItem.BuildActionType.Alter: cmdText = script.Sql.GetAlterScript(); break; case BuildItem.BuildActionType.Create: case BuildItem.BuildActionType.DropAndCreate: cmdText = script.Sql.GetCreateScript(); break; default: throw new NotSupportedException(string.Format("Unable to execute a script for build action type {0}.", action)); } var cmd = new SqlCommand(cmdText, Connection); await cmd.ExecuteNonQueryAsync(); }
private async Task<bool> DeleteNonOwner(Guid device_id, Guid user_id) { bool success = false; Exception err = null; string sql = "DELETE FROM dbo.DeviceOwners WHERE device_id = @device_id AND user_id != @user_id"; if (db.State != ConnectionState.Open) await db.OpenAsync(); SqlTransaction trans = db.BeginTransaction(); try { SqlCommand cmd = new SqlCommand(sql, db, trans); cmd.Parameters.AddWithValue("@device_id", device_id); cmd.Parameters.AddWithValue("@user_id", user_id); int count = await cmd.ExecuteNonQueryAsync(); if (count > 0) { success = true; } trans.Commit(); } catch (Exception e) { err = e; trans.Rollback(); } finally { db.Close(); } if (err != null) { throw err; } return success; }
public dynamic CommandExecute(CommandExecuteType execType) { try { dynamic result = 0; switch (execType) { case CommandExecuteType.NonQuery: result = command.ExecuteNonQuery(); break; case CommandExecuteType.NonQueryAsync: result = command.ExecuteNonQueryAsync(); break; case CommandExecuteType.Reader: result = command.ExecuteReader(); break; case CommandExecuteType.ReaderAsync: result = command.ExecuteReaderAsync(); break; case CommandExecuteType.Scalar: result = command.ExecuteScalar(); break; case CommandExecuteType.ScalarAsync: result = command.ExecuteScalarAsync(); break; } ClearParametersCommand(); return(result); } catch (Exception ex) { throw new ConnectionManagerException(string.Format("Unable to execute command caused by {0}", ex.Message), ex.InnerException); } }
public async Task <int> PersistDynoCard(DynoCard card) { int cardID = -1; if (card.SurfaceCard == null || card.SurfaceCard.NumberOfPoints == 0) { //If there's no surface card, return without inserting. return(-1); } try { // //Store the data in SQL DB using (Sql.SqlConnection conn = new Sql.SqlConnection(ConnectionString)) { conn.Open(); var insertDynoCard = new StringBuilder("INSERT INTO [ACTIVE].[DYNO_CARD] ([PU_ID], [DC_UPDATE_DATE], [DC_UPDATE_BY]) ") .Append("OUTPUT INSERTED.DC_ID ") .Append($"VALUES (1, '{DateTime.Now}', 'edgeModule') "); var insertSurfaceCard = new StringBuilder("INSERT INTO [ACTIVE].[CARD_HEADER] ") .Append("([DC_ID], [CH_EPOC_DATE], [CH_SCALED_MAX_LOAD], [CH_SCALED_MIN_LOAD], ") .Append("[CH_NUMBER_OF_POINTS], [CH_STROKE_LENGTH], ") .Append("[CH_STROKE_PERIOD], [CH_CARD_TYPE], [CH_UPDATE_DATE], [CH_UPDATE_BY]) ") .Append("OUTPUT INSERTED.CH_ID ") .Append("VALUES ({0}, ").Append($"CONVERT(int, DATEDIFF(ss, '01-01-1970 00:00:00', '{card.SurfaceCard.Timestamp}')), {card.SurfaceCard.ScaledMaxLoad}, {card.SurfaceCard.ScaledMinLoad}, ") .Append($"{card.SurfaceCard.NumberOfPoints}, {card.SurfaceCard.StrokeLength}, {card.SurfaceCard.StrokePeriod}, 'S', '{DateTime.Now}', 'edgeModule');"); var insertPumpCard = new StringBuilder("INSERT INTO [ACTIVE].[CARD_HEADER] ") .Append("([DC_ID], [CH_EPOC_DATE], [CH_SCALED_MAX_LOAD], [CH_SCALED_MIN_LOAD], ") .Append("[CH_NUMBER_OF_POINTS], [CH_GROSS_STROKE], [CH_NET_STROKE], [CH_PUMP_FILLAGE], ") .Append("[CH_FLUID_LOAD], [CH_CARD_TYPE], [CH_UPDATE_DATE], [CH_UPDATE_BY]) ") .Append("OUTPUT INSERTED.CH_ID ") .Append("VALUES ({0}, ").Append($"CONVERT(int, DATEDIFF(ss, '01-01-1970 00:00:00', '{card.PumpCard.Timestamp}')), {card.PumpCard.ScaledMaxLoad}, {card.PumpCard.ScaledMinLoad}, ") .Append($"{card.PumpCard.NumberOfPoints}, {card.PumpCard.GrossStroke}, {card.PumpCard.NetStroke}, {card.PumpCard.PumpFillage}, {card.PumpCard.FluidLoad}, ") .Append($"'P', '{DateTime.Now}', 'edgeModule'); "); var insertDetail = "INSERT INTO [ACTIVE].[CARD_DETAIL] ([CH_ID],[CD_POSITION],[CD_LOAD],[CD_UPDATE_DATE],[CD_UPDATE_BY]) VALUES ({0}, {1}, {2}, '{3}', 'edgeModule');"; using (Sql.SqlCommand dynoCardCommand = new Sql.SqlCommand()) { //Insert the DynoCard record dynoCardCommand.Connection = conn; string dynoCardInsertStatement = insertDynoCard.ToString(); //Console.WriteLine($"Dynocard insert: {dynoCardInsertStatement}"); dynoCardCommand.CommandText = dynoCardInsertStatement; var dynoCardID = await dynoCardCommand.ExecuteScalarAsync(); cardID = (int)dynoCardID; //Insert the Surface card header record string surfaceCardInsertStatement = string.Format(insertSurfaceCard.ToString(), dynoCardID); // Console.WriteLine($"Surface card insert: {surfaceCardInsertStatement}"); dynoCardCommand.CommandText = surfaceCardInsertStatement; var headerID = await dynoCardCommand.ExecuteScalarAsync(); //Insert the Surface card detail records foreach (var point in card.SurfaceCard.CardCoordinates) { string detailStatement = string.Format(insertDetail, headerID, point.Position, point.Load, DateTime.Now); //Console.WriteLine($"Surface Detail Statement: {detailStatement}"); dynoCardCommand.CommandText = detailStatement; await dynoCardCommand.ExecuteNonQueryAsync(); } if (card.PumpCard != null && card.PumpCard.NumberOfPoints != 0) { //Insert the Pump card header record string pumpCardInsertStatement = string.Format(insertPumpCard.ToString(), dynoCardID); //Console.WriteLine($"Pump card insert: {pumpCardInsertStatement}"); dynoCardCommand.CommandText = pumpCardInsertStatement; headerID = await dynoCardCommand.ExecuteScalarAsync(); //Insert the Pump card detail records foreach (var point in card.PumpCard.CardCoordinates) { string detailStatement = string.Format(insertDetail, headerID, point.Position, point.Load, DateTime.Now); //Console.WriteLine($"Pump Detail Statement: {detailStatement}"); dynoCardCommand.CommandText = detailStatement; await dynoCardCommand.ExecuteNonQueryAsync(); } } } } } catch (Exception ex) { System.Console.WriteLine($"Error trying to insert dyno card data: {ex.Message}"); System.Console.WriteLine(ex.StackTrace); await Task.FromResult(false); } return(await Task.FromResult(cardID)); }
public async Task <IActionResult> ExecuteCommand([FromBody] Command command) { if (!ModelState.IsValid) { return(BadRequest(ModelState)); } string connectionString = await GetConnectionString(command); var result = new CommandResult(); using (SqlClient.SqlConnection sqlConnection = new SqlClient.SqlConnection(connectionString)) { await sqlConnection.OpenAsync(); sqlConnection.InfoMessage += (sender, args) => { result.Messages.Add(args.Message); }; for (int batchIndex = 0; batchIndex < command.Sql.Count; batchIndex++) { string sql = command.Sql[batchIndex]; Log.LogInformation("Executing T-SQL batch {BatchNumber} of {BatchCount}...", batchIndex + 1, command.Sql.Count ); using (var sqlCommand = new SqlClient.SqlCommand(sql, sqlConnection)) { sqlCommand.CommandType = CommandType.Text; foreach (Parameter parameter in command.Parameters) { sqlCommand.Parameters.Add( parameter.ToSqlParameter() ); } try { result.ResultCode = await sqlCommand.ExecuteNonQueryAsync(); } catch (SqlClient.SqlException sqlException) { Log.LogError(sqlException, "Error while executing T-SQL: {ErrorMessage}", sqlException.Message); result.ResultCode = -1; result.Errors.AddRange( sqlException.Errors.Cast <SqlClient.SqlError>().Select( error => new SqlError { Kind = SqlErrorKind.TSql, Message = error.Message, Class = error.Class, Number = error.Number, State = error.State, Procedure = error.Procedure, Source = error.Source, LineNumber = error.LineNumber } ) ); if (command.StopOnError) { Log.LogInformation("Terminating command processing because the request was configured to stop on the first error encountered."); break; } } catch (Exception unexpectedException) { Log.LogError(unexpectedException, "Unexpected error while executing T-SQL: {ErrorMessage}", unexpectedException.Message); result.ResultCode = -1; result.Errors.Add(new SqlError { Kind = SqlErrorKind.Infrastructure, Message = $"Unexpected error while executing T-SQL: {unexpectedException.Message}" }); if (command.StopOnError) { Log.LogInformation("Terminating command processing because the request was configured to stop on the first error encountered."); break; } } } Log.LogInformation("Executed T-SQL batch {BatchNumber} of {BatchCount}.", batchIndex + 1, command.Sql.Count ); } } return(Ok(result)); }
public async Task <InvokeResult> AddItemAsync(DataStreamRecord item) { var fields = String.Empty; var values = String.Empty; foreach (var fld in _stream.Fields) { /* validation should happen long before this point, however if someone manipulated the value, it could be very, very bad * with a SQL injection attack, so error on the side of caution and never let it get through. */ if (!Validator.Validate(fld).Successful) { throw new Exception($"Invalid field name {fld.FieldName}"); } fields += String.IsNullOrEmpty(fields) ? $"{fld.FieldName}" : $",{fld.FieldName}"; values += String.IsNullOrEmpty(values) ? $"@{fld.FieldName}" : $",@{fld.FieldName}"; } fields += $",{_stream.DeviceIdFieldName},{_stream.TimeStampFieldName}"; values += $",@{_stream.DeviceIdFieldName},@{_stream.TimeStampFieldName}"; var sql = $"insert into [{_stream.DBTableName}] ({fields}) values ({values})"; using (var cn = new System.Data.SqlClient.SqlConnection(_connectionString)) using (var cmd = new System.Data.SqlClient.SqlCommand(sql, cn)) { cmd.CommandType = System.Data.CommandType.Text; foreach (var field in _stream.Fields) { object value = System.DBNull.Value; if (item.Data.ContainsKey(field.FieldName)) { value = item.Data[field.FieldName]; if (value == null) { value = System.DBNull.Value; } } if (value != System.DBNull.Value && field.FieldType.Value == DeviceAdmin.Models.ParameterTypes.GeoLocation) { var geoParts = value.ToString().Split(','); if (geoParts.Count() != 2) { return(InvokeResult.FromError($"Attmept to insert invalid geo code {value}")); } // Note geo codes ares stored HH.MMMMMM,HH.MMMMMM where lat comes first, SQL expects those to come lon then lat var parameter = new SqlParameter($"@{field.FieldName}", $"POINT({geoParts[1]} {geoParts[0]})") { Direction = ParameterDirection.Input, }; cmd.Parameters.Add(parameter); } else { cmd.Parameters.AddWithValue($"@{field.FieldName}", value); } } if (String.IsNullOrEmpty(item.Timestamp)) { item.Timestamp = DateTime.UtcNow.ToJSONString(); } cmd.Parameters.AddWithValue($"@{_stream.TimeStampFieldName}", item.Timestamp.ToDateTime()); cmd.Parameters.AddWithValue($"@{_stream.DeviceIdFieldName}", item.DeviceId); await cn.OpenAsync(); var insertResult = await cmd.ExecuteNonQueryAsync(); } return(InvokeResult.Success); }
private static async Task WriteLinksAsync(IDictionary<string, int> zones, ILookup<string, string> aliases) { var cs = ConfigurationManager.ConnectionStrings["tzdb"].ConnectionString; using (var connection = new SqlConnection(cs)) { var command = new SqlCommand("[Tzdb].[AddLink]", connection) { CommandType = CommandType.StoredProcedure }; command.Parameters.Add("@LinkZoneId", SqlDbType.Int); command.Parameters.Add("@CanonicalZoneId", SqlDbType.Int); await connection.OpenAsync(); foreach (var alias in aliases) { var canonicalId = zones[alias.Key]; foreach (var link in alias) { command.Parameters[0].Value = zones[link]; command.Parameters[1].Value = canonicalId; await command.ExecuteNonQueryAsync(); } } connection.Close(); } }
public async Task Save(Type valueType, string user, string name, object value) { const string sqlInsert = "INSERT [dbo].[Settings] VALUES(@Type, @User, @Name, @Value, @Version)"; const string sqlUpdate = "UPDATE [dbo].[Settings] " + "SET [Value] = @Value, " + " [Version] = @Version " + "WHERE [Type] = @Type " + " AND (@User IS NULL OR [User] = @User) " + " AND (@Name IS NULL OR [Name] = @Name)"; using (var connection = CreateConnection()) { var command = new SqlCommand(sqlUpdate, connection); command.Parameters.AddWithValue("@Value", value); command.Parameters.AddWithValue("@Type", valueType.FullName); command.Parameters.AddWithValue("@User", (object)user ?? DBNull.Value); command.Parameters.AddWithValue("@Name", (object)name ?? DBNull.Value); command.Parameters.AddWithValue("@Version", valueType.Assembly.GetName().Version.ToString()); var updatedRows = command.ExecuteNonQuery(); if (updatedRows == 0) { command.CommandText = sqlInsert; await command.ExecuteNonQueryAsync(); } } }