public override void SetRangeInHash(string key, IEnumerable <KeyValuePair <string, string> > keyValuePairs)
{
if (key == null)
{
throw new ArgumentNullException(nameof(key));
}
if (keyValuePairs == null)
{
throw new ArgumentNullException(nameof(keyValuePairs));
}
string sql =
$@";merge [{_storage.SchemaName}].Hash with (holdlock) as Target
using (VALUES (@key, @field, @value)) as Source ([Key], Field, Value)
on Target.[Key] = Source.[Key] and Target.Field = Source.Field
when matched then update set Value = Source.Value
when not matched then insert ([Key], Field, Value) values (Source.[Key], Source.Field, Source.Value);";
_storage.UseTransaction(_dedicatedConnection, (connection, transaction) =>
{
var commandBatch = new SqlCommandBatch(preferBatching: _storage.CommandBatchMaxTimeout.HasValue);
foreach (var keyValuePair in keyValuePairs)
{
commandBatch.Append(sql,
new SqlParameter("@key", key),
new SqlParameter("@field", keyValuePair.Key),
new SqlParameter("@value", (object)keyValuePair.Value ?? DBNull.Value));
}
commandBatch.Connection = connection;
commandBatch.Transaction = transaction;
commandBatch.CommandTimeout = _storage.CommandTimeout;
commandBatch.CommandBatchMaxTimeout = _storage.CommandBatchMaxTimeout;
commandBatch.ExecuteNonQuery();
});
}
public override void Commit()
{
_storage.UseTransaction(_dedicatedConnectionFunc(), (connection, transaction) =>
{
var commandBatch = new SqlCommandBatch(preferBatching: _storage.CommandBatchMaxTimeout.HasValue);
foreach (var lockedResource in _lockedResources)
{
commandBatch.Append(
"set nocount on;exec sp_getapplock @Resource=@resource, @LockMode=N'Exclusive'",
new SqlParameter("@resource", lockedResource));
}
foreach (var command in _commandQueue)
{
commandBatch.Append(command.Item1, command.Item2);
}
commandBatch.Connection = connection;
commandBatch.Transaction = transaction;
commandBatch.CommandTimeout = _storage.CommandTimeout;
commandBatch.CommandBatchMaxTimeout = _storage.CommandBatchMaxTimeout;
commandBatch.ExecuteNonQuery();
foreach (var queueCommand in _queueCommandQueue)
{
queueCommand(connection, transaction);
}
});
foreach (var command in _afterCommitCommandQueue)
{
command();
}
}
public override string CreateExpiredJob(
Job job,
IDictionary <string, string> parameters,
DateTime createdAt,
TimeSpan expireIn)
{
if (job == null)
{
throw new ArgumentNullException(nameof(job));
}
if (parameters == null)
{
throw new ArgumentNullException(nameof(parameters));
}
var queryString =
$@"insert into [{_storage.SchemaName}].Job (InvocationData, Arguments, CreatedAt, ExpireAt)
output inserted.Id
values (@invocationData, @arguments, @createdAt, @expireAt)";
var invocationData = InvocationData.SerializeJob(job);
var payload = invocationData.SerializePayload(excludeArguments: true);
var queryParameters = new DynamicParameters();
queryParameters.Add("@invocationData", payload, DbType.String, size: -1);
queryParameters.Add("@arguments", invocationData.Arguments, DbType.String, size: -1);
queryParameters.Add("@createdAt", createdAt, DbType.DateTime);
queryParameters.Add("@expireAt", createdAt.Add(expireIn), DbType.DateTime);
var parametersArray = parameters.ToArray();
if (parametersArray.Length <= 4)
{
if (parametersArray.Length == 1)
{
queryString = $@"
set xact_abort on; set nocount on; declare @jobId bigint;
begin tran;
insert into [{_storage.SchemaName}].Job (InvocationData, Arguments, CreatedAt, ExpireAt) values (@invocationData, @arguments, @createdAt, @expireAt);
select @jobId = scope_identity(); select @jobId;
insert into [{_storage.SchemaName}].JobParameter (JobId, Name, Value) values (@jobId, @name, @value);
commit tran;";
queryParameters.Add("@name", parametersArray[0].Key, DbType.String, size: 40);
queryParameters.Add("@value", parametersArray[0].Value, DbType.String, size: -1);
}
else if (parametersArray.Length == 2)
{
queryString = $@"
set xact_abort on; set nocount on; declare @jobId bigint;
begin tran;
insert into [{_storage.SchemaName}].Job (InvocationData, Arguments, CreatedAt, ExpireAt) values (@invocationData, @arguments, @createdAt, @expireAt);
select @jobId = scope_identity(); select @jobId;
insert into [{_storage.SchemaName}].JobParameter (JobId, Name, Value) values (@jobId, @name1, @value1), (@jobId, @name2, @value2);
commit tran;";
queryParameters.Add("@name1", parametersArray[0].Key, DbType.String, size: 40);
queryParameters.Add("@value1", parametersArray[0].Value, DbType.String, size: -1);
queryParameters.Add("@name2", parametersArray[1].Key, DbType.String, size: 40);
queryParameters.Add("@value2", parametersArray[1].Value, DbType.String, size: -1);
}
else if (parametersArray.Length == 3)
{
queryString = $@"
set xact_abort on; set nocount on; declare @jobId bigint;
begin tran;
insert into [{_storage.SchemaName}].Job (InvocationData, Arguments, CreatedAt, ExpireAt) values (@invocationData, @arguments, @createdAt, @expireAt);
select @jobId = scope_identity(); select @jobId;
insert into [{_storage.SchemaName}].JobParameter (JobId, Name, Value) values (@jobId, @name1, @value1), (@jobId, @name2, @value2), (@jobId, @name3, @value3);
commit tran;";
queryParameters.Add("@name1", parametersArray[0].Key, DbType.String, size: 40);
queryParameters.Add("@value1", parametersArray[0].Value, DbType.String, size: -1);
queryParameters.Add("@name2", parametersArray[1].Key, DbType.String, size: 40);
queryParameters.Add("@value2", parametersArray[1].Value, DbType.String, size: -1);
queryParameters.Add("@name3", parametersArray[2].Key, DbType.String, size: 40);
queryParameters.Add("@value3", parametersArray[2].Value, DbType.String, size: -1);
}
else if (parametersArray.Length == 4)
{
queryString = $@"
set xact_abort on; set nocount on; declare @jobId bigint;
begin tran;
insert into [{_storage.SchemaName}].Job (InvocationData, Arguments, CreatedAt, ExpireAt) values (@invocationData, @arguments, @createdAt, @expireAt);
select @jobId = scope_identity(); select @jobId;
insert into [{_storage.SchemaName}].JobParameter (JobId, Name, Value) values (@jobId, @name1, @value1), (@jobId, @name2, @value2), (@jobId, @name3, @value3), (@jobId, @name4, @value4);
commit tran;";
queryParameters.Add("@name1", parametersArray[0].Key, DbType.String, size: 40);
queryParameters.Add("@value1", parametersArray[0].Value, DbType.String, size: -1);
queryParameters.Add("@name2", parametersArray[1].Key, DbType.String, size: 40);
queryParameters.Add("@value2", parametersArray[1].Value, DbType.String, size: -1);
queryParameters.Add("@name3", parametersArray[2].Key, DbType.String, size: 40);
queryParameters.Add("@value3", parametersArray[2].Value, DbType.String, size: -1);
queryParameters.Add("@name4", parametersArray[3].Key, DbType.String, size: 40);
queryParameters.Add("@value4", parametersArray[3].Value, DbType.String, size: -1);
}
return(_storage.UseConnection(_dedicatedConnection, connection => connection
.ExecuteScalar <long>(queryString, queryParameters, commandTimeout: _storage.CommandTimeout)
.ToString()));
}
return(_storage.UseTransaction(_dedicatedConnection, (connection, transaction) =>
{
var jobId = connection.ExecuteScalar <long>(
queryString,
queryParameters,
transaction,
commandTimeout: _storage.CommandTimeout).ToString();
var insertParameterSql =
$@"insert into [{_storage.SchemaName}].JobParameter (JobId, Name, Value) values (@jobId, @name, @value)";
using (var commandBatch = new SqlCommandBatch(connection, transaction, preferBatching: _storage.CommandBatchMaxTimeout.HasValue))
{
commandBatch.CommandTimeout = _storage.CommandTimeout;
commandBatch.CommandBatchMaxTimeout = _storage.CommandBatchMaxTimeout;
foreach (var parameter in parametersArray)
{
commandBatch.Append(insertParameterSql,
new SqlCommandBatchParameter("@jobId", DbType.Int64)
{
Value = long.Parse(jobId)
},
new SqlCommandBatchParameter("@name", DbType.String, 40)
{
Value = parameter.Key
},
new SqlCommandBatchParameter("@value", DbType.String, -1)
{
Value = (object)parameter.Value ?? DBNull.Value
});
}
commandBatch.ExecuteNonQuery();
}
return jobId;
}, null));
}
public override void SetRangeInHash(string key, IEnumerable <KeyValuePair <string, string> > keyValuePairs)
{
if (key == null)
{
throw new ArgumentNullException(nameof(key));
}
if (keyValuePairs == null)
{
throw new ArgumentNullException(nameof(keyValuePairs));
}
var sql = $@"
set xact_abort off;
begin try
insert into [{_storage.SchemaName}].Hash ([Key], Field, Value) values (@key, @field, @value);
if @@ROWCOUNT = 0 update [{_storage.SchemaName}].Hash set Value = @value where [Key] = @key and Field = @field;
end try
begin catch
declare @em nvarchar(4000), @es int, @est int;
select @em=error_message(),@es=error_severity(),@est=error_state();
IF ERROR_NUMBER() not in (2601, 2627) raiserror(@em, @es, @est);
update [{_storage.SchemaName}].Hash set Value = @value where [Key] = @key and Field = @field;
end catch";
var lockResourceKey = $"{_storage.SchemaName}:Hash:Lock";
_storage.UseTransaction(_dedicatedConnection, (connection, transaction) =>
{
using (var commandBatch = new SqlCommandBatch(connection, transaction, preferBatching: _storage.CommandBatchMaxTimeout.HasValue))
{
if (!_storage.Options.DisableGlobalLocks)
{
commandBatch.Append(
"SET XACT_ABORT ON;exec sp_getapplock @Resource=@resource, @LockMode=N'Exclusive', @LockOwner=N'Transaction', @LockTimeout=-1;",
new SqlCommandBatchParameter("@resource", DbType.String, 255)
{
Value = lockResourceKey
});
}
foreach (var keyValuePair in keyValuePairs)
{
commandBatch.Append(sql,
new SqlCommandBatchParameter("@key", DbType.String)
{
Value = key
},
new SqlCommandBatchParameter("@field", DbType.String, 100)
{
Value = keyValuePair.Key
},
new SqlCommandBatchParameter("@value", DbType.String, -1)
{
Value = (object)keyValuePair.Value ?? DBNull.Value
});
}
if (!_storage.Options.DisableGlobalLocks)
{
commandBatch.Append(
"exec sp_releaseapplock @Resource=@resource, @LockOwner=N'Transaction';",
new SqlCommandBatchParameter("@resource", DbType.String, 255)
{
Value = lockResourceKey
});
}
commandBatch.CommandTimeout = _storage.CommandTimeout;
commandBatch.CommandBatchMaxTimeout = _storage.CommandBatchMaxTimeout;
commandBatch.ExecuteNonQuery();
}
});
}
public override string CreateExpiredJob(
Job job,
IDictionary <string, string> parameters,
DateTime createdAt,
TimeSpan expireIn)
{
if (job == null)
{
throw new ArgumentNullException(nameof(job));
}
if (parameters == null)
{
throw new ArgumentNullException(nameof(parameters));
}
string createJobSql =
$@"insert into [{_storage.SchemaName}].Job (InvocationData, Arguments, CreatedAt, ExpireAt)
output inserted.Id
values (@invocationData, @arguments, @createdAt, @expireAt)";
var invocationData = InvocationData.Serialize(job);
return(_storage.UseConnection(_dedicatedConnection, connection =>
{
var jobId = connection.ExecuteScalar <long>(
createJobSql,
new
{
invocationData = JobHelper.ToJson(invocationData),
arguments = invocationData.Arguments,
createdAt = createdAt,
expireAt = createdAt.Add(expireIn)
},
commandTimeout: _storage.CommandTimeout).ToString();
if (parameters.Count > 0)
{
string insertParameterSql =
$@"insert into [{_storage.SchemaName}].JobParameter (JobId, Name, Value)
values (@jobId, @name, @value)";
var commandBatch = new SqlCommandBatch(preferBatching: _storage.CommandBatchMaxTimeout.HasValue);
foreach (var parameter in parameters)
{
commandBatch.Append(insertParameterSql,
new SqlParameter("@jobId", long.Parse(jobId)),
new SqlParameter("@name", parameter.Key),
new SqlParameter("@value", (object)parameter.Value ?? DBNull.Value));
}
commandBatch.Connection = connection;
commandBatch.CommandTimeout = _storage.CommandTimeout;
commandBatch.CommandBatchMaxTimeout = _storage.CommandBatchMaxTimeout;
commandBatch.ExecuteNonQuery();
}
return jobId;
}));
}
public override void SetRangeInHash(string key, IEnumerable <KeyValuePair <string, string> > keyValuePairs)
{
if (key == null)
{
throw new ArgumentNullException(nameof(key));
}
if (keyValuePairs == null)
{
throw new ArgumentNullException(nameof(keyValuePairs));
}
var sql =
$@";merge [{_storage.SchemaName}].Hash with (holdlock, forceseek) as Target
using (VALUES (@key, @field, @value)) as Source ([Key], Field, Value)
on Target.[Key] = Source.[Key] and Target.Field = Source.Field
when matched then update set Value = Source.Value
when not matched then insert ([Key], Field, Value) values (Source.[Key], Source.Field, Source.Value);";
var lockResourceKey = $"{_storage.SchemaName}:Hash:Lock";
_storage.UseTransaction(_dedicatedConnection, (connection, transaction) =>
{
using (var commandBatch = new SqlCommandBatch(connection, transaction, preferBatching: _storage.CommandBatchMaxTimeout.HasValue))
{
if (!_storage.Options.DisableGlobalLocks)
{
commandBatch.Append(
"SET XACT_ABORT ON;exec sp_getapplock @Resource=@resource, @LockMode=N'Exclusive', @LockOwner=N'Transaction', @LockTimeout=-1;",
new SqlCommandBatchParameter("@resource", DbType.String, 255)
{
Value = lockResourceKey
});
}
foreach (var keyValuePair in keyValuePairs)
{
commandBatch.Append(sql,
new SqlCommandBatchParameter("@key", DbType.String, 100)
{
Value = key
},
new SqlCommandBatchParameter("@field", DbType.String, 100)
{
Value = keyValuePair.Key
},
new SqlCommandBatchParameter("@value", DbType.String, -1)
{
Value = (object)keyValuePair.Value ?? DBNull.Value
});
}
if (!_storage.Options.DisableGlobalLocks)
{
commandBatch.Append(
"exec sp_releaseapplock @Resource=@resource, @LockOwner=N'Transaction';",
new SqlCommandBatchParameter("@resource", DbType.String, 255)
{
Value = lockResourceKey
});
}
commandBatch.CommandTimeout = _storage.CommandTimeout;
commandBatch.CommandBatchMaxTimeout = _storage.CommandBatchMaxTimeout;
commandBatch.ExecuteNonQuery();
}
});
}
