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 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 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 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 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");
}
