public void CanCommitTransaction()
{
using (var sw = new MsSqlWorker(ConnectionString))
using (var tran = sw.TransactionBegin())
{
var insertsCount = sw.Exec(
command: @"insert into numbers values (@number, @square, @sqrt, @is_prime, @as_text)",
parameters: new SwParameters
{
{ "number", 4 },
{ "square", 16L },
{ "sqrt", 2.0 },
{ "is_prime", false },
{ "as_text", "four" },
},
transaction: tran);
Assert.AreEqual(1, insertsCount);
tran.Commit();
}
using (var sw = new MsSqlWorker(ConnectionString))
{
Assert.AreEqual(
expected: (4, 16L, 2.0, false, "four"),
actual: sw.Query("select * from numbers where number = 4", dr => ((int)dr[0], (long)dr[1], (double)dr[2], (bool)dr[3], (string)dr[4])).Single());
}
}
public void AddDevicePerson(long phone, string device_id)
{
try
{
using (var connection = new MsSqlWorker(GetConnection()))
{
connection.Exec(
@"
insert into PersonDevice (phone, device_id)
values(@phone, @device_id)
", parameters: new SwParameters
{
{ "phone", phone },
{ "device_id", device_id }
});
if (_cachePhonePerson.Count != 0)
{
_cacheDevicePerson[device_id] = _cachePhonePerson[phone];
}
}
}
catch (Exception e)
{
HandleExceptionSql(e);
throw;
}
}
public void TransactionRolledBackIfNotCommitted()
{
using (var sw = new MsSqlWorker(ConnectionString))
using (var tran = sw.TransactionBegin())
{
var insertsCount = sw.Exec(
command: @"insert into numbers values (@number, @square, @sqrt, @is_prime, @as_text)",
parameters: new SwParameters
{
{ "number", 100500 },
{ "square", -1 },
{ "sqrt", 0.0 },
{ "is_prime", false },
{ "as_text", (string)null },
},
transaction: tran);
Assert.AreEqual(1, insertsCount);
}
using (var sw = new MsSqlWorker(ConnectionString))
{
Assert.AreEqual(
expected: 0,
actual: sw.Query("select COUNT(*) from numbers where number = 100500", dr => (int)dr[0]).Single());
}
}
public void CanExecWithSwParameters()
{
using (var sw = new MsSqlWorker(ConnectionString))
{
var insertsCount = sw.Exec(
command: @"insert into numbers values (@number, @square, @sqrt, @is_prime, @as_text)",
parameters: new SwParameters {
{ "number", 3 },
{ "square", 9, System.Data.DbType.Int64 },
{ "sqrt", Math.Sqrt(3), System.Data.DbType.Double, System.Data.ParameterDirection.Input },
{ "is_prime", true },
{ "as_text", "three" },
});
Assert.AreEqual(1, insertsCount);
var inserted = sw.Query(
command: @"select * from numbers where number = 3",
transformFunction: dr => (
number: (int)dr[0],
square: (long)dr[1],
sqrt: (double)dr[2],
is_prime: (bool)dr[3],
as_text: dr.GetNullableString(4)
))
.Single();
Assert.AreEqual((3, 9L, 1.7320508075688773, true, "three"), inserted);
}
}
public void CanExecWithParametersDictionary()
{
using (var sw = new MsSqlWorker(ConnectionString))
{
var insertsCount = sw.Exec(
command: @"insert into numbers values (@number, @square, @sqrt, @is_prime, @as_text)",
parameters: new Dictionary <string, object> {
{ "number", 2 },
{ "square", 4L },
{ "sqrt", Math.Sqrt(2) },
{ "is_prime", true },
{ "as_text", "two" },
});
Assert.AreEqual(1, insertsCount);
var inserted = sw.Query(
command: @"select * from numbers where number = 2",
transformFunction: dr => (
number: (int)dr[0],
square: (long)dr[1],
sqrt: (double)dr[2],
is_prime: (bool)dr[3],
as_text: dr.GetNullableString(4)
))
.Single();
Assert.AreEqual((2, 4L, 1.4142135623730951, true, "two"), inserted);
}
}
public void CanExecWithParametersArray()
{
using (var sw = new MsSqlWorker(ConnectionString))
{
var insertsCount = sw.Exec(
command: @"insert into numbers values (@number, @square, @sqrt, @is_prime, @as_text)",
parameters: new []
{
new SqlParameter("number", 1),
new SqlParameter("square", 1L),
new SqlParameter("sqrt", 1.0),
new SqlParameter("is_prime", false),
new SqlParameter("as_text", "one"),
});
Assert.AreEqual(1, insertsCount);
var inserted = sw.Query(
command: @"select * from numbers where number = 1",
transformFunction: dr => (
number: (int)dr[0],
square: (long)dr[1],
sqrt: (double)dr[2],
is_prime: (bool)dr[3],
as_text: dr.GetNullableString(4)
))
.Single();
Assert.AreEqual((1, 1L, 1.0, false, "one"), inserted);
}
}
public async Task CheckOutputParameters()
{
await using var sw = new MsSqlWorker(ConnectionString);
MsSqlWorker.DbParametersConstructor args = new SwParameters
{
{ "primePosition", 1 },
{ "number", 0, System.Data.DbType.Int32, System.Data.ParameterDirection.Output },
{ "square", 0L, DbType.Int64, ParameterDirection.Output },
{ "sqrt", 0.0, DbType.Double, ParameterDirection.Output },
{ "result", 0, DbType.Int32, ParameterDirection.ReturnValue },
};
await sw.ExecAsync("GetPrimeNumber", args, commandType : System.Data.CommandType.StoredProcedure);
Assert.AreEqual((int)args[1].Value, 2);
Assert.AreEqual((int)args[4].Value, 1);
args[0].Value = 2;
await sw.ExecAsync("GetPrimeNumber", args, commandType : System.Data.CommandType.StoredProcedure);
Assert.AreEqual((int)args["number"].Value, 3);
Assert.AreEqual((int)args["result"].Value, 1);
Func <int, int, int, Task> assert = async(position, number, result) =>
{
args["primePosition"].Value = position;
await sw.ExecAsync("GetPrimeNumber", args, commandType : System.Data.CommandType.StoredProcedure);
Assert.AreEqual((int)args["number"].Value, number);
Assert.AreEqual((int)args["result"].Value, result);
};
await assert(3, 5, 1);
await assert(4, 7, 1);
await assert(5, 11, 1);
await assert(6, 13, 1);
await assert(7, 17, 1);
await assert(8, 19, 1);
await assert(9, 23, 1);
await assert(10, 29, 1);
await assert(11, 31, 1);
args[0].Value = 100500;
await sw.ExecAsync("GetPrimeNumber", args, commandType : System.Data.CommandType.StoredProcedure);
Assert.AreEqual((int)args["result"].Value, 0);
}
public void CanBulkInsert()
{
using (var sw = new MsSqlWorker(ConnectionString))
{
void bulkInsertAndCheck(int start, int length, int chunkSize)
{
using (var tran = sw.SqlTransactionBegin())
{
var rangeToInsert = Enumerable
.Range(start, length)
.Select(i => new { number = i, square = (long)i * i, sqrt = Math.Sqrt(i), is_prime = _primes.Contains(i), as_text = (string)null })
.ToArray();
sw.BulkCopyWithReflection(
source: rangeToInsert,
targetTableName: "numbers",
transaction: tran);
tran.Commit();
CollectionAssert.AreEquivalent(
expected: rangeToInsert,
actual: sw.Query(
"select * from numbers where number >= @min_number",
dr => new { number = (int)dr[0], square = (long)dr[1], sqrt = (double)dr[2], is_prime = (bool)dr[3], as_text = dr.GetNullableString(4) },
parameters: new SwParameters {
{ "min_number", start }
})
.ToArray());
}
}
bulkInsertAndCheck(5, 3, 1);
bulkInsertAndCheck(8, 7, 2);
bulkInsertAndCheck(15, 10, 3);
bulkInsertAndCheck(25, 11, 3);
bulkInsertAndCheck(36, 16, 5);
bulkInsertAndCheck(52, 18, 5);
bulkInsertAndCheck(70, 20, 5);
bulkInsertAndCheck(90, 20, 7);
bulkInsertAndCheck(110, 0, 11);
bulkInsertAndCheck(110, 10, 11);
bulkInsertAndCheck(120, 11, 11);
bulkInsertAndCheck(131, 20, 13);
}
}
public async Task SizeForSqlParameter()
{
await using var sw = new MsSqlWorker(ConnectionString);
MsSqlWorker.DbParametersConstructor args = new SwParameters
{
{ "number", 1 },
{ "name", default(string), DbType.String, ParameterDirection.Output, 100 },
};
Func <int, string, Task> assert = async(number, name) => {
args[0].Value = number;
await sw.ExecAsync("NumberName", args, commandType : CommandType.StoredProcedure);
Assert.AreEqual(args["name"].Value, name);
};
await assert(2, "two");
await assert(3, "three");
}
public async System.Threading.Tasks.Task CheckAsyncIEnumerable()
{
await using var sw = new MsSqlWorker(ConnectionString);
await using var tran = await sw.TransactionBeginAsync();
var n = 1;
await foreach (var x in sw.QueryAsync(
@"select number, square, sqrt, is_prime from dbo.numbers n",
dr => new {
number = (int)dr[0],
square = (long)dr[1],
sqrt = (double)dr[2],
is_prime = (bool)dr[3],
},
transaction: tran
))
{
Assert.AreEqual(x.number, n);
++n;
}
}
public bool AddLocation(string device_id, double lat, double lon, int radius)
{
try
{
if (_cacheDevicePerson.TryGetValue(device_id, out var person))
{
using (var connection = new MsSqlWorker(GetConnection()))
{
connection.Exec(
@"
insert into [Feedback] (id, person_id,external_reference, feedback_time, feedback_type, feedback_location )
select newid()
, (select top(1) phone from [PersonDevice] where device_id = @device_id)
, @device_id
, GETUTCDATE()
, 'device'
, geography::STPointFromText(@locationText, 4326)
.STBuffer(@radius)
", parameters: new SwParameters
{
{ "device_id", device_id },
{ "locationText", $"POINT( {lon.ToString(CultureInfo.InvariantCulture)} {lat.ToString(CultureInfo.InvariantCulture)})" },
{ "radius", radius < 0 ? 0 : (radius > Radius ? Radius : radius) }
});
person.LastLocationUpdateRequest = DateTime.UtcNow;
return(true);
}
}
return(false);
}
catch (Exception e)
{
HandleExceptionSql(e);
throw;
}
}
public async System.Threading.Tasks.Task MultipleAsyncEnumeration()
{
await using var sw = new MsSqlWorker(ConnectionString);
var enumeration = sw.QueryAsync(
@"select number, square, sqrt, is_prime from dbo.numbers n where n.number < @maxNumber",
dr => new {
i = (int)dr[0],
square = (long)dr[1],
},
new SwParameters
{
{ "maxNumber", 10 },
}
);
for (var i = 1; i <= 2; ++i)
{
await foreach (var x in enumeration)
{
Assert.AreEqual(x.square, x.i * x.i);
}
}
}
private void CheckUpdatePhones()
{
if (!LastPersonUpdates.HasValue)
{
var requestTime = DateTime.UtcNow;
using (var connection = new MsSqlWorker(GetConnection()))
{
var persons = connection.Query(
@"
IF(@lastTime is null or EXISTS(SELECT 1 FROM [ServiceTimestamp]
WHERE [service_code] = 'Person' and last_start > @lastTime))
BEGIN
SELECT
isnull(isnull(name_first, name_last), name_patr) as name,
[quarantine_location].EnvelopeCenter().Lat as lat ,
[quarantine_location].EnvelopeCenter().Long as lon,
p.quarantine_stop,
p.phone,
pd.device_id
FROM Person p
left join [PersonDevice] pd on pd.[phone] = p.phone
END
", (dr) =>
{
var phone = dr.GetInt64(4);
return(new
{
Person = _cachePhonePerson.AddOrUpdate(phone,
(p) => new PersonCacheObject()
{
Person = GetPersonFromReader(dr), LastUpdate = requestTime
},
(p, old) =>
{
if (old.LastUpdate == requestTime)
{
return old;
}
return new PersonCacheObject()
{
Person = GetPersonFromReader(dr),
LastUpdate = requestTime,
LastLocationUpdateRequest = old.LastLocationUpdateRequest,
};
}),
Phone = phone,
DeviceId = dr.GetNullableString(5)
});
}, parameters: new SwParameters
{
{ "@lastTime", LastPersonUpdates },
}).ToArray();
if (persons?.Length > 0)
{
_cacheDevicePerson.Clear();
foreach (var p in persons)
{
if (p.DeviceId?.Length > 0)
{
_cacheDevicePerson[p.DeviceId] = p.Person;
}
}
foreach (var delPhone in _cachePhonePerson.Where(w => w.Value.LastUpdate != requestTime).Select(a => a.Key))
{
_cachePhonePerson.TryRemove(delPhone, out var _);
}
}
LastPersonUpdates = DateTime.UtcNow.AddSeconds(-1);
}
}
}
private PersonCacheObject GetPersonFormDb(long?phone = null, string?device_id = null)
{
using (var connection = new MsSqlWorker(GetConnection()))
{
DateTime requestTime = DateTime.UtcNow;
var persons = connection.Query(
@"
SELECT
isnull(isnull(name_first, name_last), name_patr) as name,
[quarantine_location].EnvelopeCenter().Lat as lat ,
[quarantine_location].EnvelopeCenter().Long as lon,
p.quarantine_stop,
p.phone,
pd.device_id
FROM Person p
left join [PersonDevice] pd on pd.[phone] = p.phone
where (@phone is null or p.phone = @phone )
and (@device_id is null or pd.device_id = @device_id)
", (dr) =>
{
var phone = dr.GetInt64(4);
return(new
{
Person = _cachePhonePerson.AddOrUpdate(phone,
(p) => new PersonCacheObject()
{
Person = GetPersonFromReader(dr), LastUpdate = requestTime
},
(p, old) =>
{
if (old.LastUpdate == requestTime)
{
return old;
}
return new PersonCacheObject()
{
Person = GetPersonFromReader(dr),
LastUpdate = requestTime,
LastLocationUpdateRequest = old.LastLocationUpdateRequest,
};
}),
Phone = phone,
DeviceId = dr.GetNullableString(5)
});
}, parameters: new SwParameters
{
{ "@lastTime", LastPersonUpdates },
}).ToArray();
if (persons?.Length > 0)
{
foreach (var p in persons)
{
if (p.DeviceId?.Length > 0)
{
_cacheDevicePerson[p.DeviceId] = p.Person;
}
}
}
return(persons?.FirstOrDefault().Person);
}
}
public void TestConfig()
{
bool dbExists;
Assert.IsNotNull(ConnectionStringMaster);
Assert.IsNotNull(ConnectionString);
using (var sw = new MsSqlWorker(ConnectionStringMaster))
{
Assert.AreEqual("hello", sw.Query("select 'hello'", dr => dr[0]).Single());
dbExists = sw.Query(
@"declare @true bit = 1, @false bit = 0;
SELECT CASE when DB_ID('sqlworker_test') IS NULL then @false else @true end;",
dr => (bool)dr[0])
.Single();
}
if (Config["recreateDb"]?.ToLower() == "true" || !dbExists)
{
using (var sw = new MsSqlWorker(ConnectionStringMaster))
{
sw.Exec(@"
IF DB_ID('sqlworker_test') IS NOT NULL
ALTER DATABASE sqlworker_test SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE IF EXISTS sqlworker_test;
CREATE DATABASE sqlworker_test;
ALTER DATABASE sqlworker_test SET RECOVERY SIMPLE;"
);
}
using (var sw = new MsSqlWorker(ConnectionString))
{
Assert.AreEqual("hello", sw.Query("select 'hello'", dr => dr[0]).Single());
}
using (var sw = new MsSqlWorker(ConnectionString))
using (var dt = new System.Data.DataTable("numbers"))
{
dt.Columns.Add("number", typeof(int));
dt.Columns.Add("square", typeof(long));
dt.Columns.Add("sqrt", typeof(double));
dt.Columns.Add("is_prime", typeof(bool));
dt.Columns.Add(new System.Data.DataColumn("as_text", typeof(string))
{
MaxLength = 400
});
sw.CreateTableByDataTable(dt, true);
sw.Exec(@"
CREATE UNIQUE CLUSTERED INDEX [PK_number] ON [dbo].[numbers]([number] ASC)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
");
sw.Exec(@"
CREATE UNIQUE NONCLUSTERED INDEX [IX_square] ON [dbo].[numbers]([square] ASC)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
");
sw.Exec(@"
CREATE UNIQUE NONCLUSTERED INDEX [IX_sqrt] ON [dbo].[numbers]([sqrt] ASC)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
");
sw.Exec(@"
CREATE PROCEDURE GetPrimeNumber
@primePosition int,
@number int output,
@square bigint output,
@sqrt float output
AS
BEGIN
SELECT
@number = number,
@square = square,
@sqrt = sqrt
FROM numbers
WHERE is_prime = 1
ORDER BY number
OFFSET @primePosition - 1 ROWS
FETCH NEXT 1 ROWS ONLY;
RETURN @@ROWCOUNT;
END");
sw.Exec(@"
CREATE PROCEDURE NumberName
@number int,
@name nvarchar(100) output
AS
BEGIN
SELECT @name = as_text
FROM numbers
WHERE number = @number
END
");
}
}
}
public void CanExec()
{
using var sw = new MsSqlWorker(ConnectionString);
sw.Exec("DELETE FROM sqlworker_test.dbo.numbers");
}