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();
     }
 }
Example #3
1
		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;
                });
        }
Example #7
0
        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();
 }
Example #9
0
        /// <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
                    });
                }
            }
        }
Example #10
0
        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;
        }
Example #11
0
        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;
        }
Example #12
0
        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;
            }
        }
Example #14
0
        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;
        }
Example #20
0
        /// <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;
        }
Example #21
0
        /// <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;
        }
Example #23
0
		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;
            }
        }
Example #27
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);
 }
Example #28
0
        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?;
                }
            }
        }
Example #29
0
        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;
            }
        }
Example #33
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);
            }
        }