private static async Task TransactionSample() { using (var connection = new SqlConnection(GetConnectionString())) { await connection.OpenAsync(); SqlTransaction tx = connection.BeginTransaction(); try { string sql = "INSERT INTO Sales.CreditCard (CardType, CardNumber, ExpMonth, ExpYear)" + "VALUES (@CardType, @CardNumber, @ExpMonth, @ExpYear); " + "SELECT SCOPE_IDENTITY()"; var command = new SqlCommand(); command.CommandText = sql; command.Connection = connection; command.Transaction = tx; var p1 = new SqlParameter("CardType", SqlDbType.NVarChar, 50); var p2 = new SqlParameter("CardNumber", SqlDbType.NVarChar, 25); var p3 = new SqlParameter("ExpMonth", SqlDbType.TinyInt); var p4 = new SqlParameter("ExpYear", SqlDbType.SmallInt); command.Parameters.AddRange(new SqlParameter[] { p1, p2, p3, p4 }); command.Parameters["CardType"].Value = "MegaWoosh"; command.Parameters["CardNumber"].Value = "08154711128"; command.Parameters["ExpMonth"].Value = 4; command.Parameters["ExpYear"].Value = 2019; object id = await command.ExecuteScalarAsync(); WriteLine($"record added with id: {id}"); command.Parameters["CardType"].Value = "NeverLimits"; command.Parameters["CardNumber"].Value = "987654321011"; command.Parameters["ExpMonth"].Value = 12; command.Parameters["ExpYear"].Value = 2025; id = await command.ExecuteScalarAsync(); WriteLine($"record added with id: {id}"); // throw new Exception("abort"); tx.Commit(); } catch (Exception ex) { WriteLine($"error {ex.Message}, rolling back"); tx.Rollback(); } } }
public override async Task<HealthCheckStatus> Check() { using (var connection = new SqlConnection(_connectionString)) { await connection.OpenAsync().ConfigureAwait(false); var cmd = new SqlCommand("select 1", connection); await cmd.ExecuteScalarAsync().ConfigureAwait(false); return HealthCheckStatus.Passed(); } }
public static async Task Insert( this Tag NewTag, SqlConnection Connection, SqlTransaction Transaction = null ) { using( SqlCommand Command = new SqlCommand() ) { Command.Connection = Connection; if( null != Transaction ) Command.Transaction = Transaction; Command.CommandText = "INSERT Tags ( Text ) OUTPUT INSERTED.Id VALUES ( @Text )"; Command.Parameters.Add( "Text", System.Data.SqlDbType.VarChar ).Value = NewTag.Text; NewTag.Id = (short)await Command.ExecuteScalarAsync(); } }
/// <inheritdoc/> public async Task<int> SaveFileMetaDataAsync(FileMetaDataToStore metaData) { int fileId; using (var connection = new SqlConnection(connectionString)) { using (var command = new SqlCommand("InsertFileMetaData", connection)) { command.CommandType = CommandType.StoredProcedure; command.Parameters.AddWithValue("@FileName", metaData.FileName); command.Parameters.AddWithValue("@MimeType", metaData.MimeType); command.Parameters.AddWithValue("@ApplicationName", metaData.ApplicationName); command.Parameters.AddWithValue("@InternalStoragePath", metaData.StoragePath); command.Parameters.AddWithValue("@Checksum", metaData.Checksum); await connection.OpenAsync(); fileId = Convert.ToInt32(await command.ExecuteScalarAsync()); } } return fileId; }
/// <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 Task<long?> GetLastId() { var connection = CreateAndOpenConnection(); var transaction = connection.BeginTransaction(System.Data.IsolationLevel.ReadUncommitted); var cmd = new SqlCommand(_getLastIdSQL.Replace("{TableName}", MessageTableName), connection, transaction); return cmd.ExecuteScalarAsync<long?>() .ContinueWith(t => { connection.Close(); return t.Result; }); }
public async Task PersistAnomaly(DynoCardAnomalyEvent anomaly) { try { // //Store the data in SQL DB using (Sql.SqlConnection conn = new Sql.SqlConnection(ConnectionString)) { conn.Open(); var insertEvent = new StringBuilder("INSERT INTO [ACTIVE].[EVENT] ([PU_ID], [EV_EPOC_DATE], [EV_ANOMALY_ID], [EV_UPDATE_DATE], [EV_UPDATE_BY]) ") .Append("OUTPUT INSERTED.EV_ID ") .Append($"VALUES ({anomaly.PumpId}, CONVERT(int, DATEDIFF(ss, '01-01-1970 00:00:00', '{anomaly.Timestamp}')), '{anomaly.AnomalyId}', '{DateTime.Now}', 'edgeModule') "); var insertEventDetail = new StringBuilder("INSERT INTO [ACTIVE].[EVENT_DETAIL] ([EV_ID], [DC_ID], [ED_TRIGGERED_EVENTS], [ED_UPDATE_DATE], [ED_UPDATE_BY]) ") .Append("VALUES ({0}, ") .Append($"{anomaly.DynoCard.Id}, '{anomaly.DynoCard.TriggeredEvents}', '{DateTime.Now}', 'edgeModule'); "); using (Sql.SqlCommand anomalyCommand = new Sql.SqlCommand()) { anomalyCommand.Connection = conn; string eventSQL = insertEvent.ToString(); //System.Console.WriteLine($"Event SQL: {eventSQL}"); anomalyCommand.CommandText = eventSQL; var eventID = (int)await anomalyCommand.ExecuteScalarAsync(); string eventDetailSQL = string.Format(insertEventDetail.ToString(), eventID); //System.Console.WriteLine($"Event detail SQL: {eventDetailSQL}"); anomalyCommand.CommandText = eventDetailSQL; await anomalyCommand.ExecuteScalarAsync(); } } } catch (Exception ex) { System.Console.WriteLine($"Exception persisting anomaly: {ex.Message}"); } }
public Task<long> GenerateMessageId(string key) { var connection = CreateAndOpenConnection(); var transaction = connection.BeginTransaction(IsolationLevel.ReadUncommitted); var cmd = new SqlCommand(_getMessageIdSql.Replace("{TableName}", MessageTableName), connection, transaction); cmd.Parameters.AddWithValue("EventKey", key); return cmd.ExecuteScalarAsync<long>() .ContinueWith(idTask => { // We purposely don't commit the transaction, we just wanted the ID anyway, not the record connection.Close(); return idTask; }) .Unwrap(); }
/// <summary> /// Determines whether SQLServer is ready. /// </summary> public async Task <Status> IsReadyAsync(CancellationToken cancellationToken) { using (var connection = new SqlConnection(_serverConnectionString)) { await connection.OpenAsync(cancellationToken); using (System.Data.SqlClient.SqlCommand command = connection.CreateCommand()) { command.CommandText = "SELECT Count(name) FROM sys.databases"; return(new Status { IsReady = (int)await command.ExecuteScalarAsync(cancellationToken) >= 4 }); } } }
public async Task<bool> Create() { bool success = false; Exception err = null; string sql = "INSERT INTO dbo.ItemLists (name, description, status, created_by, category, category_id, group_id) OUTPUT INSERTED.id VALUES (@name, @description, @status, @created_by, @category, @category_id, @group_id)"; if (db.State != ConnectionState.Open) await db.OpenAsync(); SqlTransaction trans = db.BeginTransaction(); try { SqlCommand cmd = new SqlCommand(sql, db, trans); cmd.Parameters.AddWithValue("@name", name); cmd.Parameters.AddWithValue("@description", description); cmd.Parameters.AddWithValue("@status", status); cmd.Parameters.AddWithValue("@created_by", created_by); cmd.Parameters.AddWithValue("@category", category); cmd.Parameters.AddWithValue("@category_id", category_id); cmd.Parameters.AddWithValue("@group_id", group_id); object id = await cmd.ExecuteScalarAsync(); if (id != null && id.GetType() == typeof(Guid)) { this.id = ((Guid)id); success = true; } trans.Commit(); } catch (Exception e) { err = e; trans.Rollback(); } finally { db.Close(); } if (err != null) { throw err; } this.created_at = DateTime.Now; return success; }
public async Task<bool> Register() { bool success = false; Exception err = null; string sql = "INSERT INTO dbo.Users (facebook_id, gender, email, name, first_name, last_name, isAnonymous) OUTPUT INSERTED.id VALUES (@facebook_id, @gender, @email, @name, @first_name, @last_name, @isAnonymous)"; if (db.State != ConnectionState.Open) await db.OpenAsync(); SqlTransaction trans = db.BeginTransaction(); try { SqlCommand cmd = new SqlCommand(sql, db, trans); cmd.Parameters.AddWithValue("@facebook_id", facebook_id); cmd.Parameters.AddWithValue("@gender", gender); cmd.Parameters.AddWithValue("@email", email); cmd.Parameters.AddWithValue("@name", name); cmd.Parameters.AddWithValue("@last_name", last_name); cmd.Parameters.AddWithValue("@first_name", first_name); cmd.Parameters.AddWithValue("@isAnonymous", isAnonymous); object id = await cmd.ExecuteScalarAsync(); if (id != null && id.GetType() == typeof(Guid)) { this.id = ((Guid)id); success = true; } trans.Commit(); } catch (Exception e) { err = e; trans.Rollback(); } finally { db.Close(); } if (err != null) { throw err; } return success; }
public async Task<bool> LinkDevice() { bool success = false; Exception err = null; string sql = "INSERT INTO dbo.DeviceOwners (device_id, user_id) OUTPUT INSERTED.id VALUES (@device_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); object id = await cmd.ExecuteScalarAsync(); if (id != null && id.GetType() == typeof(Guid)) { this.id = ((Guid)id); success = true; this.linked_at = DateTime.Now; } trans.Commit(); } catch (Exception e) { err = e; trans.Rollback(); } finally { db.Close(); } if (err != null) { throw err; } return success; }
/// <summary> /// Creates the specified CourseSchedule data row. /// </summary> public async Task Create(IDatabaseConnection databaseConnection, CourseScheduleDataRow courseScheduleDataRow) { // Build the SQL command. using (SqlCommand sqlCommand = new SqlCommand("INSERT INTO [CourseSchedule] VALUES (@courseScheduleCode, @sessionID, @dayOfWeek, @time); SELECT CAST(SCOPE_IDENTITY() AS INT);")) { // Use the specified database connection. SqlConnection sqlConnection = (databaseConnection as DatabaseConnection).SqlConnection; sqlCommand.Connection = sqlConnection; // Set the SQL command parameter values. this.SetSqlCommandParameterValues(sqlCommand, courseScheduleDataRow, setPrimaryKeyValue: false); // Execute the SQL command. int courseScheduleID = (int)await sqlCommand.ExecuteScalarAsync(); // Assign the generated CourseScheduleID. courseScheduleDataRow.CourseScheduleID = courseScheduleID; } }
public async Task<bool> Create() { bool success = false; Exception err = null; string sql = "INSERT INTO dbo.Memberships (user_id, group_id, status) OUTPUT INSERTED.id VALUES (@user_id, @group_id, @status)"; if (db.State != ConnectionState.Open) await db.OpenAsync(); SqlTransaction trans = db.BeginTransaction(); try { SqlCommand cmd = new SqlCommand(sql, db, trans); cmd.Parameters.AddWithValue("@user_id", user_id); cmd.Parameters.AddWithValue("@group_id", group_id); cmd.Parameters.AddWithValue("@status", status); object id = await cmd.ExecuteScalarAsync(); if (id != null && id.GetType() == typeof(Guid)) { this.id = ((Guid)id); success = true; } trans.Commit(); } catch (Exception e) { err = e; trans.Rollback(); } finally { db.Close(); } if (err != null) { throw err; } return success; }
/// <summary> /// Creates the specified Session data row. /// </summary> public async Task Create(IDatabaseConnection databaseConnection, SessionDataRow sessionDataRow) { // Build the SQL command. using (SqlCommand sqlCommand = new SqlCommand("INSERT INTO [Session] VALUES (@sessionCode, @name, @startDate); SELECT CAST(SCOPE_IDENTITY() AS INT);")) { // Use the specified database connection. SqlConnection sqlConnection = (databaseConnection as DatabaseConnection).SqlConnection; sqlCommand.Connection = sqlConnection; // Set the SQL command parameter values. this.SetSqlCommandParameterValues(sqlCommand, sessionDataRow, setPrimaryKeyValue: false); // Execute the SQL command. int sessionID = (int)await sqlCommand.ExecuteScalarAsync(); // Assign the generated SessionID. sessionDataRow.SessionID = sessionID; } }
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); } }
private static async Task<IDictionary<string, int>> WriteZonesAsync(IEnumerable<string> zones) { var dictionary = new Dictionary<string, int>(); var cs = ConfigurationManager.ConnectionStrings["tzdb"].ConnectionString; using (var connection = new SqlConnection(cs)) { var command = new SqlCommand("[Tzdb].[AddZone]", connection) { CommandType = CommandType.StoredProcedure }; command.Parameters.Add("@Name", SqlDbType.VarChar, 50); await connection.OpenAsync(); foreach (var zone in zones) { command.Parameters[0].Value = zone; var id = (int)await command.ExecuteScalarAsync(); dictionary.Add(zone, id); } connection.Close(); } return dictionary; }
public async Task ReportMetrics(IClientPerformanceMetrics metricsData) { using (var conn = new SqlConnection(connectionString)) { conn.Open(); using (var tx = conn.BeginTransaction()) { var command1 = new SqlCommand(CLIENT_READ_SINGLE_ROW); command1.Parameters.Add(new SqlParameter { ParameterName = "@id", DbType = DbType.String, Value = deploymentId }); command1.Parameters.Add(new SqlParameter { ParameterName = "@clientid", DbType = DbType.String, Value = clientId }); command1.Connection = conn; command1.Transaction = tx; var result = (Int32)await command1.ExecuteScalarAsync(); var command2 = new SqlCommand((result > 0) ? CLIENT_UPDATE_ROW : CLIENT_INSERT_ROW); ConvertToClientMetricsRow(metricsData, command2); command2.Connection = conn; command2.Transaction = tx; await command2.ExecuteNonQueryAsync(); tx.Commit(); } } }
static async Task<SqlConnection> ExecuteCommand(string state, SqlConnection topConnection) { string sql = "WAITFOR DELAY '00:00:00.25';select transaction_id from sys.dm_tran_current_transaction"; SqlCommand cmd = new SqlCommand(sql); var localConn = await ConnectionManager.GetSqlConnectionAsync(); cmd.Connection = localConn; if (state.StartsWith("lastTask-recurse")) { var currScope = DbConnectionScope.Current; bool isEqual = Object.ReferenceEquals(topConnection, localConn); bool IsMustBeEqual = currScope.Option == DbConnectionScopeOption.Required; bool isTestPassed = isEqual == IsMustBeEqual; Console.WriteLine(); Console.WriteLine("Reusing connection test Passed: {0}, state: {1}", isTestPassed, state); Console.WriteLine(); } object res = null; try { res = await cmd.ExecuteScalarAsync().ConfigureAwait(false); } catch (Exception ex) { Console.WriteLine(state + " " + ex.Message); } Console.WriteLine("Thread: {0}, TransactID: {1}, state: {2}", Thread.CurrentThread.ManagedThreadId, res, state); return localConn; }
/// <summary> /// Execute a SqlCommand (that returns a 1x1 resultset) against the specified SqlTransaction /// using the provided parameters. /// </summary> /// <remarks> /// e.g.: /// int orderCount = (int)ExecuteScalarAsync(trans, CommandType.StoredProcedure, "GetOrderCount", new SqlParameter("@prodid", 24)); /// </remarks> /// <param name="transaction">A valid SqlTransaction</param> /// <param name="commandType">The CommandType (stored procedure, text, etc.)</param> /// <param name="commandText">The stored procedure name or T-SQL command</param> /// <param name="commandParameters">An array of SqlParamters used to execute the command</param> /// <returns>An object containing the value in the 1x1 resultset generated by the command</returns> public async static Task<object> ExecuteScalarAsync(SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] commandParameters) { if (transaction == null) throw new ArgumentNullException("transaction"); if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction"); // Create a command and prepare it for execution var cmd = new SqlCommand(); await PrepareCommandAsync(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters).ConfigureAwait(false); // Execute the command & return the results var retval = await cmd.ExecuteScalarAsync().ConfigureAwait(false); // Detach the SqlParameters from the command object, so they can be used again cmd.Parameters.Clear(); return retval; }
/// <summary> /// Execute a SqlCommand (that returns a 1x1 resultset) against the specified SqlConnection /// using the provided parameters. /// </summary> /// <remarks> /// e.g.: /// int orderCount = (int)ExecuteScalarAsync(conn, CommandType.StoredProcedure, "GetOrderCount", new SqlParameter("@prodid", 24)); /// </remarks> /// <param name="connection">A valid SqlConnection</param> /// <param name="commandType">The CommandType (stored procedure, text, etc.)</param> /// <param name="commandText">The stored procedure name or T-SQL command</param> /// <param name="commandParameters">An array of SqlParamters used to execute the command</param> /// <returns>An object containing the value in the 1x1 resultset generated by the command</returns> public async static Task<object> ExecuteScalarAsync(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters) { if (connection == null) throw new ArgumentNullException("connection"); // Create a command and prepare it for execution var cmd = new SqlCommand(); var mustCloseConnection = await PrepareCommandAsync(cmd, connection, null, commandType, commandText, commandParameters).ConfigureAwait(false); // Execute the command & return the results var retval = await cmd.ExecuteScalarAsync().ConfigureAwait(false); // Detach the SqlParameters from the command object, so they can be used again cmd.Parameters.Clear(); if (mustCloseConnection) connection.Close(); return retval; }
public async Task<int> SessionIsValid(string sessionKey) { string procName = "proc_Sessions_Select"; Task<int> t = Task.Run<int>(async () => { SqlCommand cmd = null; using (SqlConnection con = new SqlConnection(conString)) { await con.OpenAsync(); using (cmd = new SqlCommand(procName, con)) { cmd.CommandType = System.Data.CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@sessionKey", sessionKey); int count = (int)await cmd.ExecuteScalarAsync(); return count; } } }); return await t; }
public static async Task Insert( this Speaker NewSpeaker, SqlConnection Connection, SqlTransaction Transaction = null ) { using( SqlCommand Command = new SqlCommand() ) { Command.Connection = Connection; if( null != Transaction ) Command.Transaction = Transaction; Command.CommandText = "INSERT Speakers ( EmailAddress, DisplayName, PasswordHash ) OUTPUT INSERTED.Id VALUES ( @EmailAddress, @DisplayName, @PasswordHash )"; Command.Parameters.Add( "EmailAddress", System.Data.SqlDbType.VarChar ).Value = NewSpeaker.EmailAddress; Command.Parameters.Add( "DisplayName", System.Data.SqlDbType.VarChar ).Value = NewSpeaker.DisplayName; Command.Parameters.Add( "PasswordHash", System.Data.SqlDbType.VarBinary ).Value = NewSpeaker.PasswordHash; NewSpeaker.Id = (short)await Command.ExecuteScalarAsync(); } }
public async Task<object> CallSPScalarAsync(string tableName, string procedureName, params SqlParameter[] parameters) { object o = null; try { Open(); using (var command = new SqlCommand(procedureName, _sqlConnection) { CommandTimeout = 1000, CommandType = CommandType.StoredProcedure }) { foreach (SqlParameter sqlp in parameters) command.Parameters.Add(sqlp); o = await command.ExecuteScalarAsync(); } Close(); } catch { Close(); throw; } return o; }
private async Task<int> PurgeBatch(DateTime minTimestampToKeep, int batchSize) { var sql = @" DELETE TOP(@BatchSize) [PackageStatistics] WHERE [Timestamp] < @MinTimestampToKeep AND [Key] < (SELECT [DownloadStatsLastAggregatedId] FROM [GallerySettings]) SELECT @@ROWCOUNT"; using (var connection = await Source.ConnectTo()) { var command = new SqlCommand(sql, connection); command.Parameters.AddWithValue("@BatchSize", batchSize); command.Parameters.AddWithValue("@MinTimestampToKeep", minTimestampToKeep); return await command.ExecuteScalarAsync() as int? ?? 0; } }
private async Task<int> GetNumberOfRecordsToPurge(SqlConnectionStringBuilder Source, DateTime minTimestampToKeep) { var sql = @" SELECT COUNT(*) FROM PackageStatistics WITH (NOLOCK) WHERE [Timestamp] < @MinTimestampToKeep"; using (var connection = await Source.ConnectTo()) { var command = new SqlCommand(sql, connection); command.Parameters.AddWithValue("@MinTimestampToKeep", minTimestampToKeep); return await command.ExecuteScalarAsync() as int? ?? 0; } }
public static async Task <bool> DoesObjectExistAsync(this SqlCommand command, string name, string type, string schema = "dbo") { ArgCheck.NotNull(nameof(command), command); return((await command.ExecuteScalarAsync <int>($@"IF OBJECT_ID('[{schema}].[{name}]', '{type}') IS NOT NULL SELECT 1 ELSE SELECT 0").ConfigureAwait(false)) == 1); }
private static async Task<int?> GetTargetCursor(SqlConnectionStringBuilder target, ReplicationTargetMarker targetMarker) { using (var connection = await target.ConnectTo()) { using (var command = new SqlCommand("GetCursor", connection)) { command.CommandType = CommandType.StoredProcedure; command.Parameters.AddWithValue("@minTimestamp", targetMarker.MinTimestamp); command.Parameters.AddWithValue("@maxTimestamp", targetMarker.MaxTimestamp); return await command.ExecuteScalarAsync() as int?; } } }
public async Task<bool> FindByFacebookID() { bool success = false; Exception err = null; string sql = "SELECT id FROM dbo.Users WITH (NOLOCK) WHERE facebook_id LIKE @facebook_id"; if (db.State != ConnectionState.Open) await db.OpenAsync(); try { SqlCommand cmd = new SqlCommand(sql, db); cmd.Parameters.AddWithValue("@facebook_id", facebook_id); object id = await cmd.ExecuteScalarAsync(); if (id != null && id.GetType() == typeof(Guid)) { this.id = ((Guid)id); success = true; } } catch (Exception e) { err = e; } finally { db.Close(); } if (err != null) { throw err; } return success; }
public void ScalarAsync() { IDbCommand command = new SqlCommand(); Assert.Throws<InvalidOperationException>(async () => await command.ExecuteScalarAsync()); }
private async Task<DateTime?> GetMinTimestampToKeep(SqlConnectionStringBuilder Destination) { // Get the most recent cursor window that is older than the days we want to keep. // By getting the MAX(MinTimestamp), we'll delete statistics older than the beginning of the // most recent window that has begun processing (but isn't guaranteed to have completed). // Note that we made sure to treat DaysToKeep as a NEGATIVE number for the expected behavior var sql = @" SELECT MAX(MinTimestamp) FROM CollectorCursor WHERE MinTimestamp <= DATEADD(day, -ABS(@DaysToKeep), convert(date, GETUTCDATE()))"; using (var connection = await Destination.ConnectTo()) { SqlCommand command = new SqlCommand(sql, connection); command.Parameters.AddWithValue("@DaysToKeep", DaysToKeep); return await command.ExecuteScalarAsync() as DateTime?; } }
private async Task<int> AggregateBatch(int batchSize) { using (var connection = await PackageDatabase.ConnectTo()) { var command = new SqlCommand(AggregateStatsSql, connection); command.Parameters.AddWithValue("@BatchSize", batchSize); return await command.ExecuteScalarAsync() as int? ?? 0; } }
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)); }
private async Task<string> CheckVersion(string versionSql) { using (var sqlConnection = new SqlConnection { ConnectionString = this.connectionString }) using (var sqlCommand = new SqlCommand(versionSql, sqlConnection)) { sqlConnection.Open(); return await sqlCommand.ExecuteScalarAsync() as string; } }
public async Task<object> Load(Type valueType, string user, string name, Type storageType) { const string sql = "SELECT [Value] " + "FROM [dbo].[Settings] " + "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(sql, connection); command.Parameters.AddWithValue("@Type", valueType.FullName); command.Parameters.AddWithValue("@User", (object)user ?? DBNull.Value); command.Parameters.AddWithValue("@Name", (object)name ?? DBNull.Value); var value = await command.ExecuteScalarAsync(); return Convert.ChangeType(value, storageType); } }