System.Data.SqlClient.SqlConnection.CreateCommand()

private static bool SafeDatabaseExists(InstanceInfo instance, string dbName)
        {
            try
            {
                SqlTestDB masterDb = new SqlTestDB(instance, "master");
                using (SqlConnection connection = masterDb.OpenSqlConnection())
                {
                    using (SqlCommand command = connection.CreateCommand())
                    {
                        command.CommandText = string.Format(CultureInfo.CurrentCulture, "select count(*) from sys.databases where [name]='{0}'", dbName);
                        object result = command.ExecuteScalar();
                        int count;
                        return result != null && int.TryParse(result.ToString(), out count) && count > 0;
                    }
                }
            }
            catch (Exception ex)
            {
                Debug.WriteLine(ex.Message);
                return false;
            }
        }

private static SqlCommand GetCommandObject(SqlConnection conn, string sqlCommandText, int commandTimeOut)
        {
            SqlCommand cmd = conn.CreateCommand();
            // reasonable hard code to prevent hang client.  
            cmd.CommandTimeout = commandTimeOut;
            cmd.CommandText = String.Format(CultureInfo.InvariantCulture, _setLockTimeoutDefault, GetLockTimeoutMS());
            cmd.ExecuteNonQuery();
            cmd.CommandText = sqlCommandText;
            return cmd;
        }

public static void ExecuteNonQuery(SqlConnection conn, string sql, int commandTimeout = CommonConstants.DefaultCommandTimeout)
        {

            SqlCommand cmd = conn.CreateCommand();
            try
            {
                cmd.CommandType = CommandType.Text;
                cmd.CommandTimeout = commandTimeout;

                // Set seven-sets
                cmd.CommandText = "SET ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER ON;";
                cmd.ExecuteNonQuery();

                cmd.CommandText = "SET NUMERIC_ROUNDABORT OFF;";
                cmd.ExecuteNonQuery();

                cmd.CommandText = sql;
                cmd.ExecuteNonQuery();
            }
            catch (SqlException ex)
            {
                Console.WriteLine("Exception{0}{1}{0}While executing TSQL:{0}{2}",
                    Environment.NewLine,
                    ex,
                    sql);
                throw ex;
            }
        }

public static bool CreateDatabase(string dbName, string dbFileName)
        {
            try
            {
                string connectionString =
                    String.Format(@"Data Source=(LocalDB)\mssqllocaldb;Initial Catalog=master;Integrated Security=True");
                using (var connection = new SqlConnection(connectionString))
                {
                    connection.Open();
                    SqlCommand cmd = connection.CreateCommand();


                    DetachDatabase(dbName);

                    cmd.CommandText = String.Format("CREATE DATABASE {0} ON (NAME = N'{0}', FILENAME = '{1}')", dbName,
                        dbFileName);
                    cmd.ExecuteNonQuery();
                }

                if (File.Exists(dbFileName)) return true;
                else return false;
            }
            catch
            {
                throw;
            }
        }

public static bool DetachDatabase(string dbName)
        {
            try
            {
                string connectionString =
                    String.Format(@"Data Source=(LocalDB)\mssqllocaldb;Initial Catalog=master;Integrated Security=True");
                using (var connection = new SqlConnection(connectionString))
                {
                    connection.Open();
                    SqlCommand cmd = connection.CreateCommand();
                    cmd.CommandText = String.Format("exec sp_detach_db '{0}'", dbName);
                    cmd.ExecuteNonQuery();

                    return true;
                }
            }
            catch
            {
                return false;
            }
        }

public void PerformSqlTask(int TaskNumber, MulreplacedhreadedOrderInsertMain ParentForm)
        {
            var errorOccurred = false;

            while (!errorOccurred)
            {
                try
                {
                    using (var con = new SqlConnection(ConnectionStringTextBox.Text))
                    {
                        var startingTime = DateTime.Now;

                        con.Open();

                        using (var selectCommand = con.CreateCommand())
                        {
                            var da = new SqlDataAdapter(selectCommand);
                            var rnd = new Random(TaskNumber);

                            selectCommand.CommandText = "SELECT TOP(1) PersonID FROM [Application].People WHERE IsEmployee <> 0 ORDER BY NEWID();";
                            var personTable = new DataTable("Person");
                            da.Fill(personTable);

                            var salespersonID = (int)(personTable.Rows[0]["PersonID"]);

                            selectCommand.CommandText = "SELECT TOP(1) 1 AS OrderReference, c.CustomerID, c.PrimaryContactPersonID AS ContactPersonID, CAST(DATEADD(day, 1, SYSDATETIME()) AS date) AS ExpectedDeliveryDate, CAST(FLOOR(RAND() * 10000) + 1 AS nvarchar(20)) AS CustomerPurchaseOrderNumber, CAST(0 AS bit) AS IsUndersupplyBackordered, N'Auto-generated' AS Comments, c.DeliveryAddressLine1 + N', ' + c.DeliveryAddressLine2 AS DeliveryInstructions FROM Sales.Customers AS c ORDER BY NEWID();";
                            var orderTable = new DataTable("Orders");
                            da.Fill(orderTable);

                            selectCommand.CommandText = "SELECT TOP(7) 1 AS OrderReference, si.StockItemID, si.StockItemName AS [Description], FLOOR(RAND() * 10) + 1 AS Quanreplacedy FROM Warehouse.StockItems AS si WHERE IsChillerStock = 0 ORDER BY NEWID()";
                            if (rnd.Next(1, 100) < 4)
                            {
                                selectCommand.CommandText += "UNION ALL SELECT TOP(1) 1 AS OrderReference, si.StockItemID, si.StockItemName AS [Description], FLOOR(RAND() * 10) + 1 AS Quanreplacedy FROM Warehouse.StockItems AS si WHERE IsChillerStock <> 0 ORDER BY NEWID()";
                            }
                            selectCommand.CommandText += ";";
                            var orderLinesTable = new DataTable("OrderLines");
                            da.Fill(orderLinesTable);

                            using (var insertCommand = con.CreateCommand())
                            {
                                insertCommand.CommandType = CommandType.StoredProcedure;
                                insertCommand.CommandText = "Website.InsertCustomerOrders";

                                var orderList = new SqlParameter("@Orders", SqlDbType.Structured);
                                orderList.TypeName = "Website.OrderList";
                                orderList.Value = orderTable;
                                insertCommand.Parameters.Add(orderList);

                                var orderLineList = new SqlParameter("@OrderLines", SqlDbType.Structured);
                                orderLineList.TypeName = "Website.OrderLineList";
                                orderLineList.Value = orderLinesTable;
                                insertCommand.Parameters.Add(orderLineList);

                                var ordersCreatedByPersonID = new SqlParameter("@OrdersCreatedByPersonID", SqlDbType.Int);
                                ordersCreatedByPersonID.Value = salespersonID;
                                insertCommand.Parameters.Add(ordersCreatedByPersonID);

                                var salespersonPersonID = new SqlParameter("@SalespersonPersonID", SqlDbType.Int);
                                salespersonPersonID.Value = salespersonID;
                                insertCommand.Parameters.Add(salespersonPersonID);

                                insertCommand.ExecuteNonQuery();
                            }
                        }
                        con.Close();

                        ParentForm.UpdateTotals((int) DateTime.Now.Subtract(startingTime).TotalMilliseconds);
                    }
                }
                catch (Exception ex)
                {
                    errorOccurred = true;
                    ParentForm.errorHasOccurred = true;
                    ParentForm.errorDetails = ex.ToString();
                }
            }
        }

public async Task<StateResult<Offset>> GetLastCdcOffsetAsync(string executionId, string tableName)
        {
            using (var conn = await GetConnectionAsync())
            {
                var command = conn.CreateCommand();
                command.CommandText = "SELECT TOP 1 Lsn, SeqVal FROM [CdcTools].[ChangeState] WHERE ExecutionId = @ExecutionId AND TableName = @TableName";
                command.Parameters.Add("ExecutionId", SqlDbType.VarChar, 50).Value = executionId;
                command.Parameters.Add("TableName", SqlDbType.VarChar, 200).Value = tableName;

                using (var reader = await command.ExecuteReaderAsync())
                {
                    if(reader.Read())
                    {
                        var offset = new Offset();
                        offset.Lsn = (byte[])reader["Lsn"];
                        offset.SeqVal = (byte[])reader["SeqVal"];

                        return new StateResult<Offset>(Result.StateReturned, offset);
                    }
                    else
                    {
                        var offset = new Offset();
                        offset.Lsn = new byte[10];
                        offset.SeqVal = new byte[10];

                        return new StateResult<Offset>(Result.NoStoredState, offset);
                    }
                }
            }
        }

public async Task<ChangeBatch> GetChangeBatchAsync(TableSchema tableSchema, byte[] fromLsn, byte[] toLsn, int batchSize)
        {
            var fromStr = BitConverter.ToString(fromLsn);
            var toStr = BitConverter.ToString(toLsn);
            var batch = new ChangeBatch();

            if (!HasValue(fromLsn))
                return batch;

            using (var conn = await GetConnectionAsync())
            {
                var command = conn.CreateCommand();
                command.Parameters.Add("@from_lsn", SqlDbType.Binary, 10).Value = fromLsn;
                command.Parameters.Add("@to_lsn", SqlDbType.Binary, 10).Value = toLsn;

                command.CommandText = string.Format(@"SELECT TOP {0} *
FROM [cdc].[fn_cdc_get_all_changes_dbo_{1}](@from_lsn, @to_lsn, 'all')
ORDER BY __$seqval", batchSize + 1, tableSchema.TableName);

                using (var reader = await command.ExecuteReaderAsync())
                {
                    int ctr = 0;
                    while (await reader.ReadAsync())
                    {
                        ctr++;
                        if (ctr <= batchSize)
                        {
                            var changeRecord = new ChangeRecord();
                            changeRecord.ChangeType = (ChangeType)(int)reader["__$operation"];
                            changeRecord.TableName = tableSchema.TableName;

                            changeRecord.Lsn = (byte[])reader["__$start_lsn"];
                            var lsn = new BigInteger(changeRecord.Lsn.Reverse().ToArray());
                            changeRecord.LsnStr = lsn.ToString();

                            changeRecord.SeqVal = (byte[])reader["__$seqval"];
                            var seqVal = new BigInteger(changeRecord.SeqVal.Reverse().ToArray());
                            changeRecord.SeqValStr = seqVal.ToString();

                            var recordIdSb = new StringBuilder();
                            foreach (var pkCol in tableSchema.PrimaryKeys.OrderBy(x => x.OrdinalPosition))
                            {
                                recordIdSb.Append(reader[pkCol.ColumnName].ToString());
                                recordIdSb.Append("|");
                            }
                            changeRecord.ChangeKey = recordIdSb.ToString();


                            foreach (var column in tableSchema.Columns)
                                changeRecord.Data.Add(column.Name.ToLower(), reader[column.Name]);

                            batch.Changes.Add(changeRecord);

                            if (ctr == 1)
                            {
                                batch.FromLsn = changeRecord.Lsn;
                                batch.FromSeqVal = changeRecord.SeqVal;
                            }

                            if (ctr == batchSize) // not else if as could be batchSzie fo 1
                            {
                                batch.ToLsn = changeRecord.Lsn;
                                batch.ToSeqVal = changeRecord.SeqVal;
                            }
                        }
                        else
                        {
                            batch.MoreChanges = true;

                            var lastLsn = new BigInteger(batch.ToLsn.Reverse().ToArray());
                            var lsn = new BigInteger(((byte[])reader["__$start_lsn"]).Reverse().ToArray());
                            if (lastLsn.Equals(lsn))
                                batch.MoreOfLastTransaction = true;
                        }
                    }
                }
            }

            return batch;
        }

private async Task<List<TableColumn>> GetTableColumnsAsync(string tableName)
        {
            var columns = new List<TableColumn>();

            using (var conn = await GetOpenConnectionAsync())
            {
                using (var command = conn.CreateCommand())
                {
                    command.CommandText = TableSchemaQueryBuilder.GetColumnsOfTableQuery();
                    command.CommandTimeout = 30;
                    command.Parameters.Add("TableName", SqlDbType.VarChar).Value = tableName;

                    using (var reader = await command.ExecuteReaderAsync())
                    {
                        while (await reader.ReadAsync())
                        {
                            var column = GetTableColumn(reader);
                            if(column.ColumnName.IndexOf("msrepl", StringComparison.OrdinalIgnoreCase) == -1)
                                columns.Add(column);
                        }
                    }
                }
            }

            return columns;
        }

private async Task InsertStoreTransactionIdAsync(string executionId, TransactionId transactionId)
        {
            using (var conn = await GetConnectionAsync())
            {
                var command = conn.CreateCommand();
                command.CommandText = @"INSERT INTO [CdcTools].[TransactionState]([ExecutionId],[Lsn],[LastUpdate])
VALUES(@ExecutionId,@Lsn,GETUTCDATE())";
                command.Parameters.Add("ExecutionId", SqlDbType.VarChar, 50).Value = executionId;
                command.Parameters.Add("Lsn", SqlDbType.Binary, 10).Value = transactionId.Lsn;
                await command.ExecuteNonQueryAsync();
            }
        }

private async Task UpdateStoreTransactionIdAsync(string executionId, TransactionId transactionId)
        {
            using (var conn = await GetConnectionAsync())
            {
                var command = conn.CreateCommand();
                command.CommandText = @"UPDATE [CdcTools].[TransactionState]
SET [Lsn] = @Lsn,
    [LastUpdate] = GETUTCDATE()
WHERE ExecutionId = @ExecutionId";
                command.Parameters.Add("ExecutionId", SqlDbType.VarChar, 50).Value = executionId;
                command.Parameters.Add("Lsn", SqlDbType.Binary, 10).Value = transactionId.Lsn;
                await command.ExecuteNonQueryAsync();
            }
        }

private async Task InsertStorePkOffsetAsync(string executionId, string tableName, PrimaryKeyValue pkValue)
        {
            using (var conn = await GetConnectionAsync())
            {
                var command = conn.CreateCommand();
                command.CommandText = @"INSERT INTO [CdcTools].[FullLoadState]([ExecutionId],[TableName],[PrimaryKeyValue],[LastUpdate])
VALUES(@ExecutionId,@TableName,@PrimaryKeyValue,GETUTCDATE())";
                command.Parameters.Add("ExecutionId", SqlDbType.VarChar, 50).Value = executionId;
                command.Parameters.Add("TableName", SqlDbType.VarChar, 200).Value = tableName;
                command.Parameters.Add("PrimaryKeyValue", SqlDbType.VarChar, -1).Value = JsonConvert.SerializeObject(pkValue);
                await command.ExecuteNonQueryAsync();
            }
        }

public async Task<long> GetRowCountAsync(TableSchema tableSchema)
        {
            using (var conn = await GetOpenConnectionAsync())
            {
                var command = conn.CreateCommand();
                command.CommandText = $"SELECT COUNT(*) FROM {tableSchema.Schema}.{tableSchema.TableName}";
                return (int) await command.ExecuteScalarAsync();
            }
        }

public async Task<FullLoadBatch> GetFirstBatchAsync(TableSchema tableSchema, int batchSize)
        {
            var batch = new FullLoadBatch();
            batch.TableSchema = tableSchema;

            using (var conn = await GetOpenConnectionAsync())
            {
                var command = conn.CreateCommand();
                command.CommandText = $"SELECT TOP {batchSize} * FROM {tableSchema.Schema}.{tableSchema.TableName} ORDER BY {tableSchema.GetOrderedPrimaryKeyColumns()};";

                using (var reader = await command.ExecuteReaderAsync())
                {
                    int ctr = 1;
                    while (await reader.ReadAsync())
                    {
                        var change = new FullLoadRecord();
                        change.ChangeKey = GetRecordId(reader, tableSchema);
                        change.BatchSeqNo = ctr;

                        foreach (var column in tableSchema.Columns)
                            change.Data.Add(column.Name, reader[column.Name]);

                        batch.Records.Add(change);
                        ctr++;
                    }
                }
            }

            if (batch.Records.Any())
            {
                batch.FirstRowKey = GetKey(batch.Records.First(), tableSchema);
                batch.LastRowKey = GetKey(batch.Records.Last(), tableSchema);
            }

            return batch;
        }

public async Task<FullLoadBatch> GetBatchAsync(TableSchema tableSchema, PrimaryKeyValue lastRetrievedKey, int batchSize)
        {
            var batch = new FullLoadBatch();
            batch.TableSchema = tableSchema;

            using (var conn = await GetOpenConnectionAsync())
            {
                var command = conn.CreateCommand();
                command.CommandText = TableSchemaQueryBuilder.GetExtractQueryUsingAllKeys(tableSchema, batchSize);
                
                foreach(var pk in tableSchema.PrimaryKeys.OrderBy(x => x.OrdinalPosition))
                {
                    var columnSchema = tableSchema.GetColumn(pk.ColumnName);
                    var value = lastRetrievedKey.GetValue(pk.OrdinalPosition);
                    command.Parameters.Add(CreateSqlParameter(columnSchema, "@p"+pk.OrdinalPosition, value));
                }

                using (var reader = await command.ExecuteReaderAsync())
                {
                    int ctr = 1;
                    while (await reader.ReadAsync())
                    {
                        var change = new FullLoadRecord();
                        change.ChangeKey = GetRecordId(reader, tableSchema);
                        change.BatchSeqNo = ctr;
                        
                        foreach (var column in tableSchema.Columns)
                            change.Data.Add(column.Name, reader[column.Name]);

                        batch.Records.Add(change);
                        ctr++;
                    }
                }
            }

            if (batch.Records.Any())
            {
                batch.FirstRowKey = GetKey(batch.Records.First(), tableSchema);
                batch.LastRowKey = GetKey(batch.Records.Last(), tableSchema);
            }

            return batch;
        }

private async Task UpdateStoreCdcOffsetAsync(string executionId, string tableName, Offset offset)
        {
            using (var conn = await GetConnectionAsync())
            {
                var command = conn.CreateCommand();
                command.CommandText = @"UPDATE [CdcTools].[ChangeState]
SET [Lsn] = @Lsn,
    [SeqVal] = @SeqVal,
    [LastUpdate] = GETUTCDATE()
WHERE ExecutionId = @ExecutionId
AND TableName = @TableName";
                command.Parameters.Add("ExecutionId", SqlDbType.VarChar, 50).Value = executionId;
                command.Parameters.Add("TableName", SqlDbType.VarChar, 200).Value = tableName;
                command.Parameters.Add("Lsn", SqlDbType.Binary, 10).Value = offset.Lsn;
                command.Parameters.Add("SeqVal", SqlDbType.Binary, 10).Value = offset.SeqVal;
                await command.ExecuteNonQueryAsync();
            }
        }

private async Task UpdateStorePkOffsetAsync(string executionId, string tableName, PrimaryKeyValue pkValue)
        {
            using (var conn = await GetConnectionAsync())
            {
                var command = conn.CreateCommand();
                command.CommandText = @"UPDATE [CdcTools].[FullLoadState]
SET [PrimaryKeyValue] = @PrimaryKeyValue,
    [LastUpdate] = GETUTCDATE()
WHERE ExecutionId = @ExecutionId
AND TableName = @TableName";
                command.Parameters.Add("ExecutionId", SqlDbType.VarChar, 50).Value = executionId;
                command.Parameters.Add("TableName", SqlDbType.VarChar, 200).Value = tableName;
                command.Parameters.Add("PrimaryKeyValue", SqlDbType.VarChar, -1).Value = JsonConvert.SerializeObject(pkValue);
                await command.ExecuteNonQueryAsync();
            }
        }

public async Task<byte[]> GetMinValidLsnAsync(string tableName)
        {
            using (var conn = await GetConnectionAsync())
            {
                var command = conn.CreateCommand();
                command.CommandText = string.Format(@"
DECLARE @from_lsn binary (10)
SET @from_lsn = sys.fn_cdc_get_min_lsn('{0}')
IF @from_lsn = 0x00000000000000000000
	SET @from_lsn = (SELECT TOP 1 __$start_lsn FROM [cdc].[dbo_{0}_CT] ORDER BY __$start_lsn)

SELECT @from_lsn", tableName);


                var result = await command.ExecuteScalarAsync();
                if (result != DBNull.Value)
                {
                    var lsn = (byte[])result;
                    return lsn;
                }
            }

            return new byte[10];
        }

public async Task<byte[]> GetMaxLsnAsync()
        {
            using (var conn = await GetConnectionAsync())
            {
                var command = conn.CreateCommand();
                command.CommandText = "SELECT sys.fn_cdc_get_max_lsn()";
                var maxLsn = (byte[])await command.ExecuteScalarAsync();

                return maxLsn;
            }
        }

public async Task<ChangeBatch> GetChangeBatchAsync(TableSchema tableSchema, byte[] fromLsn, byte[] fromSeqVal, byte[] toLsn, int batchSize)
        {
            var batch = new ChangeBatch();

            if (!HasValue(fromLsn))
                return batch;
            
            using (var conn = await GetConnectionAsync())
            {
                var command = conn.CreateCommand();
                command.Parameters.Add("@from_lsn", SqlDbType.Binary, 10).Value = fromLsn;
                command.Parameters.Add("@from_seqval", SqlDbType.Binary, 10).Value = fromSeqVal;
                command.Parameters.Add("@to_lsn", SqlDbType.Binary, 10).Value = toLsn;

                command.CommandText = string.Format(@"SELECT TOP {0} *
FROM [cdc].[fn_cdc_get_all_changes_dbo_{1}](@from_lsn, @to_lsn, 'all')
WHERE __$seqval >= @from_seqval
ORDER BY __$seqval", batchSize + 1, tableSchema.TableName);

                using (var reader = await command.ExecuteReaderAsync())
                {
                    int ctr = 0;
                    while (await reader.ReadAsync())
                    {
                        ctr++;
                        if (ctr <= batchSize)
                        {
                            var changeRecord = new ChangeRecord();
                            changeRecord.ChangeType = (ChangeType)(int)reader["__$operation"];
                            changeRecord.TableName = tableSchema.TableName;

                            changeRecord.Lsn = (byte[])reader["__$start_lsn"];
                            var lsn = new BigInteger(changeRecord.Lsn.Reverse().ToArray());
                            changeRecord.LsnStr = lsn.ToString();

                            changeRecord.SeqVal = (byte[])reader["__$seqval"];
                            var seqVal = new BigInteger(changeRecord.SeqVal.Reverse().ToArray());
                            changeRecord.SeqValStr = seqVal.ToString();

                            var recordIdSb = new StringBuilder();
                            foreach (var pkCol in tableSchema.PrimaryKeys.OrderBy(x => x.OrdinalPosition))
                            {
                                recordIdSb.Append(reader[pkCol.ColumnName].ToString());
                                recordIdSb.Append("|");
                            }
                            changeRecord.ChangeKey = recordIdSb.ToString();


                            foreach (var column in tableSchema.Columns)
                                changeRecord.Data.Add(column.Name.ToLower(), reader[column.Name]);

                            batch.Changes.Add(changeRecord);

                            if (ctr == 1)
                            {
                                batch.FromLsn = changeRecord.Lsn;
                                batch.FromSeqVal = changeRecord.SeqVal;
                            }
                            else
                            {
                                batch.ToLsn = changeRecord.Lsn;
                                batch.ToSeqVal = changeRecord.SeqVal;
                            }
                        }
                        else
                        {
                            batch.MoreChanges = true;

                            var lastLsn = new BigInteger(batch.ToLsn.Reverse().ToArray());
                            var lsn = new BigInteger(((byte[])reader["__$start_lsn"]).Reverse().ToArray());
                            if (lastLsn.Equals(lsn))
                                batch.MoreOfLastTransaction = true;
                        }
                    }
                }
            }

            return batch;
        }

private static void InsertQueueSql(string resourceGroup, string queueName)
        {
            using (var conn = new SqlConnection(ConnStr))
            {
                conn.Open();
                var command = conn.CreateCommand();
                command.CommandText = $"INSERT INTO RBR.Resources(ResourceGroup, ResourceName) VALUES(@ResourceGroup, @ResourceName)";
                command.Parameters.Add("ResourceGroup", SqlDbType.VarChar, 100).Value = resourceGroup;
                command.Parameters.Add("ResourceName", SqlDbType.VarChar, 1000).Value = queueName;
                command.ExecuteNonQuery();
            }
        }

private async Task InsertStoreCdcOffsetAsync(string executionId, string tableName, Offset offset)
        {
            using (var conn = await GetConnectionAsync())
            {
                var command = conn.CreateCommand();
                command.CommandText = @"INSERT INTO [CdcTools].[ChangeState]([ExecutionId],[TableName],[Lsn],[SeqVal],[LastUpdate])
VALUES(@ExecutionId,@TableName,@Lsn,@SeqVal,GETUTCDATE())";
                command.Parameters.Add("ExecutionId", SqlDbType.VarChar, 50).Value = executionId;
                command.Parameters.Add("TableName", SqlDbType.VarChar, 200).Value = tableName;
                command.Parameters.Add("Lsn", SqlDbType.Binary, 10).Value = offset.Lsn;
                command.Parameters.Add("SeqVal", SqlDbType.Binary, 10).Value = offset.SeqVal;
                await command.ExecuteNonQueryAsync();
            }
        }

public async Task<StateResult<PrimaryKeyValue>> GetLastPkOffsetAsync(string executionId, string tableName)
        {
            using (var conn = await GetConnectionAsync())
            {
                var command = conn.CreateCommand();
                command.CommandText = "SELECT TOP 1 PrimaryKeyValue FROM [CdcTools].[FullLoadState] WHERE ExecutionId = @ExecutionId AND TableName = @TableName";
                command.Parameters.Add("ExecutionId", SqlDbType.VarChar, 50).Value = executionId;
                command.Parameters.Add("TableName", SqlDbType.VarChar, 200).Value = tableName;

                using (var reader = await command.ExecuteReaderAsync())
                {
                    if (reader.Read())
                    {
                        var json = reader["PrimaryKeyValue"].ToString();
                        var pkValue = JsonConvert.DeserializeObject<PrimaryKeyValue>(json);
                        return new StateResult<PrimaryKeyValue>(Result.StateReturned, pkValue);
                    }
                    else
                    {
                        return new StateResult<PrimaryKeyValue>(Result.NoStoredState, null);
                    }
                }
            }
        }

private async Task<List<TablePrimaryKey>> GetTablePrimaryKeysAsync()
        {
            var primaryKeys = new List<TablePrimaryKey>();

            using (var conn = await GetOpenConnectionAsync())
            {
                using (var command = conn.CreateCommand())
                {
                    command.CommandText = TableSchemaQueryBuilder.GetPrimaryKeysQuery;
                    command.CommandTimeout = 30;

                    using (var reader = await command.ExecuteReaderAsync())
                    {
                        while (await reader.ReadAsync())
                        {
                            var pk = GetPrimaryKey(reader);
                            primaryKeys.Add(pk);
                        }
                    }
                }
            }

            return primaryKeys;
        }

private async Task<List<TablePrimaryKey>> GetTablePrimaryKeysAsync(string tableName)
        {
            var primaryKeys = new List<TablePrimaryKey>();

            using (var conn = await GetOpenConnectionAsync())
            {
                using (var command = conn.CreateCommand())
                {
                    command.CommandText = TableSchemaQueryBuilder.GetPrimaryKeyColumnsOfTableQuery();
                    command.CommandTimeout = 30;
                    command.Parameters.Add("TableName", SqlDbType.VarChar).Value = tableName;

                    using (var reader = await command.ExecuteReaderAsync())
                    {
                        while (await reader.ReadAsync())
                        {
                            var pk = GetPrimaryKey(reader);
                            primaryKeys.Add(pk);
                        }
                    }
                }
            }

            return primaryKeys;
        }

public async Task<StateResult<TransactionId>> GetLastTransactionIdAsync(string executionId)
        {
            using (var conn = await GetConnectionAsync())
            {
                var command = conn.CreateCommand();
                command.CommandText = "SELECT TOP 1 Lsn FROM [CdcTools].[TransactionState] WHERE ExecutionId = @ExecutionId";
                command.Parameters.Add("ExecutionId", SqlDbType.VarChar, 50).Value = executionId;
                
                using (var reader = await command.ExecuteReaderAsync())
                {
                    if(reader.Read())
                    {
                        var lsn = (byte[])reader["Lsn"];
                        var lsnInt = new BigInteger(lsn.Reverse().ToArray());
                        var lsnStr = lsnInt.ToString();
                        var id = new TransactionId(lsn, lsnStr, lsnInt);

                        return new StateResult<TransactionId>(Result.TransactionIdReturned, id);
                    }
                    else
                    {
                        var lsn = new byte[10];
                        var lsnInt = 0;
                        var lsnStr = "0";
                        var id = new TransactionId(lsn, lsnStr, lsnInt);

                        return new StateResult<TransactionId>(Result.NoStoredTransationId, id);
                    }
                }
            }
        }

public static void EnsureResourceGroup(string resourceGroup, int leaseExpirySeconds)
        {
            bool rgExists = false;

            using (var conn = new SqlConnection(ConnStr))
            {
                conn.Open();
                var command = conn.CreateCommand();
                command.CommandText = "SELECT COUNT(*) FROM RBR.ResourceGroups WHERE ResourceGroup = @ResourceGroup";
                command.Parameters.Add("ResourceGroup", SqlDbType.VarChar, 100).Value = resourceGroup;
                int count = (int)command.ExecuteScalar();
                rgExists = count == 1;

                if (!rgExists)
                {
                    command.Parameters.Clear();
                    command.CommandText = $"INSERT INTO RBR.ResourceGroups(ResourceGroup, FencingToken, LeaseExpirySeconds) VALUES(@ResourceGroup, 1, @LeaseExpirySeconds)";
                    command.Parameters.Add("ResourceGroup", SqlDbType.VarChar, 100).Value = resourceGroup;
                    command.Parameters.Add("LeaseExpirySeconds", SqlDbType.Int).Value = leaseExpirySeconds;
                    command.ExecuteNonQuery();
                    Console.WriteLine("Created consumer group");
                }
                else
                {
                    command.Parameters.Clear();
                    command.CommandText = $"UPDATE RBR.ResourceGroups SET LeaseExpirySeconds = @LeaseExpirySeconds WHERE ResourceGroup = @ResourceGroup";
                    command.Parameters.Add("ResourceGroup", SqlDbType.VarChar, 100).Value = resourceGroup;
                    command.Parameters.Add("LeaseExpirySeconds", SqlDbType.Int).Value = leaseExpirySeconds;
                    command.ExecuteNonQuery();
                    Console.WriteLine("Consumer group exists");
                }
            }
        }

public async Task RelinquishLeaseAsync(RelinquishLeaseRequest relinquishLeaseRequest)
        {
            using (var conn = new SqlConnection(this.connectionString))
            {
                await conn.OpenAsync();
                var command = conn.CreateCommand();
                command.CommandText = @"UPDATE [RBR].[ResourceGroups]
   SET [CoordinatorId] = NULL
 WHERE ResourceGroup = @ResourceGroup
AND [CoordinatorId] = @ClientId
AND [FencingToken] = @FencingToken";
                command.Parameters.Add("@ResourceGroup", SqlDbType.VarChar, 100).Value = relinquishLeaseRequest.ResourceGroup;
                command.Parameters.AddWithValue("@ClientId", relinquishLeaseRequest.ClientId);
                command.Parameters.AddWithValue("@FencingToken", relinquishLeaseRequest.FencingToken);
                await command.ExecuteNonQueryAsync();
            }
        }

private static List<string> GetQueuesFromSqlServer(string resourceGroup)
        {
            var queues = new List<string>();

            using (var conn = new SqlConnection(ConnStr))
            {
                conn.Open();
                var command = conn.CreateCommand();
                command.CommandText = "SELECT ResourceName FROM RBR.Resources WHERE ResourceGroup = @ResourceGroup";
                command.Parameters.Add("ResourceGroup", SqlDbType.VarChar, 100).Value = resourceGroup;
                using (var reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        queues.Add(reader.GetString(0));
                    }
                }
            }

            return queues;
        }

private static void DeleteQueueSql(string resourceGroup, string queueName)
        {
            using (var conn = new SqlConnection(ConnStr))
            {
                conn.Open();
                var command = conn.CreateCommand();
                command.CommandText = $"DELETE FROM RBR.Resources WHERE ResourceGroup = @ResourceGroup AND ResourceName = @ResourceName";
                command.Parameters.Add("ResourceGroup", SqlDbType.VarChar, 100).Value = resourceGroup;
                command.Parameters.Add("ResourceName", SqlDbType.VarChar, 1000).Value = queueName;
                command.ExecuteNonQuery();
            }
        }

public async Task<List<string>> GetResourcesAsync(string resourceGroup)
        {
            var resources = new List<string>();
            using (var conn = new SqlConnection(this.connectionString))
            {
                await conn.OpenAsync();
                var command = conn.CreateCommand();
                command.CommandText = "SELECT ResourceName FROM [RBR].[Resources] WHERE ResourceGroup = @ResourceGroup";
                command.Parameters.Add("@ResourceGroup", SqlDbType.VarChar, 100).Value = resourceGroup;
                using (var reader = await command.ExecuteReaderAsync())
                {
                    while (await reader.ReadAsync())
                        resources.Add(reader.GetString(0));
                }
            }

            return resources;
        }

internal SqlCommand getNewCommand()
        {
            return this.connection.CreateCommand();
        }

See More Examples