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