public void FillFromStatementWithDataReaderWithObjectReturnAndCustomMappings()
{
var da = new SqlDataAccess(ConnectionString);
var helper = new SqlDataAccessHelper();
helper.SetRetrievalMode(DataActionRetrievalType.DataReader);
helper.AddStatement(
"select name [Map1],database_id [Map2],is_read_only [Map3],service_broker_guid [Map4],create_date [Map5] from sys.databases where name in (@master,@model,@msdb) order by 1 asc");
helper.AddParameter("master", "master", SqlDbType.VarChar)
.AddParameter("model", "model", SqlDbType.VarChar)
.AddParameter("msdb", "msdb", SqlDbType.VarChar)
.AddCustomMapping("Map1", "Name")
.AddCustomMapping("Map2", "Id")
.AddCustomMapping("Map3", "ReadOnly")
.AddCustomMapping("Map4", "BrokerGuid")
.AddCustomMapping("Map5", "CreateDate");
var values = da.Fill <DatabaseModel>(helper);
this.OutputTestTimings(helper.ExecutionContext);
Assert.AreEqual(values.Count, 3);
Assert.AreEqual(values[0].Name, "master");
Assert.AreEqual(values[1].Name, "model");
Assert.AreEqual(values[2].Name, "msdb");
}
public void Fill_FromStatementWithDataReader_WithObjectReturn()
{
var da = new SqlDataAccess(connectionString);
var helper = new SqlDataAccessHelper();
helper.SetRetrievalMode(DataActionRetrievalType.DataReader);
helper.AddStatement("select name,database_id [Id],is_read_only [ReadOnly],service_broker_guid [BrokerGuid],create_date [CreateDate] from sys.databases where name in (@master,@model,@msdb) order by 1 asc");
helper.AddParameter("master", "master", SqlDbType.VarChar);
helper.AddParameter("model", "model", SqlDbType.VarChar);
helper.AddParameter("msdb", "msdb", SqlDbType.VarChar);
var values = da.Fill <DatabaseModel>(helper);
}
public void Fill_FromStatementWithDataReader_WithStringReturn()
{
var da = new SqlDataAccess(connectionString);
var helper = new SqlDataAccessHelper();
helper.SetRetrievalMode(DataActionRetrievalType.DataReader);
helper.AddStatement("select name from sys.databases where name in (@master,@model,@msdb) order by 1 asc");
helper.AddParameter("master", "master", SqlDbType.VarChar);
helper.AddParameter("model", "model", SqlDbType.VarChar);
helper.AddParameter("msdb", "msdb", SqlDbType.VarChar);
var values = da.Fill <string>(helper);
}
public async Task FillFromStatementWithDataReaderWithEnumReturnAsync()
{
var da = new SqlDataAccess(ConnectionString);
var helper = new SqlDataAccessHelper();
helper.SetRetrievalMode(DataActionRetrievalType.DataReader);
helper.AddStatement("select 0 [EnumType] union all select 1");
var values = await da.FillAsync <TestClassWithEnum>(helper);
this.OutputTestTimings(helper.ExecutionContext);
Assert.AreEqual(values.Count, 2);
Assert.IsTrue(values[0].EnumType == TestEnum.Val1);
Assert.IsTrue(values[1].EnumType == TestEnum.Val2);
}
public void EmptyDataReaderReturnShouldReturnAnEmptyCollectionTest()
{
var da = new SqlDataAccess(ConnectionString);
var helper = new SqlDataAccessHelper();
helper.SetRetrievalMode(DataActionRetrievalType.DataReader);
helper.AddStatement(
"select name,database_id [Id],is_read_only [ReadOnly],service_broker_guid [BrokerGuid],create_date [CreateDate] from sys.databases where name in ('testNotThere') order by 1 asc");
var values = da.Fill <DatabaseModel>(helper);
this.OutputTestTimings(helper.ExecutionContext);
Assert.IsTrue(values.Count == 0);
}
public async Task GetNullableTypesFromStatementWithDataSetTestAsync()
{
var da = new SqlDataAccess(ConnectionString);
var helper = new SqlDataAccessHelper();
helper.SetRetrievalMode(DataActionRetrievalType.DataSet);
helper.AddStatement(
"select null from sys.databases where name in (@master) union all select 1 from sys.databases where name in (@master) order by 1 asc");
helper.AddParameter("master", "master", SqlDbType.VarChar);
var returnValue = await da.FillAsync <int?>(helper);
this.OutputTestTimings(helper.ExecutionContext);
Assert.IsTrue(returnValue[0] == null);
Assert.IsTrue(returnValue[1] == 1);
}
public async Task GetFromStatementWithDataSetWithStringReturnAsync()
{
var da = new SqlDataAccess(ConnectionString);
var helper = new SqlDataAccessHelper();
helper.SetRetrievalMode(DataActionRetrievalType.DataSet);
helper.AddStatement(
"select top 1 name from sys.databases where name in (@master,@model,@msdb) order by 1 asc");
helper.AddParameter("master", "master", SqlDbType.VarChar);
helper.AddParameter("model", "model", SqlDbType.VarChar);
helper.AddParameter("msdb", "msdb", SqlDbType.VarChar);
var returnValue = await da.GetAsync <string>(helper);
this.OutputTestTimings(helper.ExecutionContext);
Assert.AreEqual(returnValue, "master");
}
public async Task GetFromStatementWithDataSetWithObjectReturnAsync()
{
var da = new SqlDataAccess(ConnectionString);
var helper = new SqlDataAccessHelper();
helper.SetRetrievalMode(DataActionRetrievalType.DataSet);
helper.AddStatement(
"select top 1 name,database_id [Id],is_read_only [ReadOnly],service_broker_guid [BrokerGuid],create_date [CreateDate] from sys.databases where name in (@master,@model,@msdb) order by 1 asc");
helper.AddParameter("master", "master", SqlDbType.VarChar);
helper.AddParameter("model", "model", SqlDbType.VarChar);
helper.AddParameter("msdb", "msdb", SqlDbType.VarChar);
var returnValue = await da.GetAsync <DatabaseModel>(helper);
this.OutputTestTimings(helper.ExecutionContext);
Assert.AreEqual(returnValue.Name, "master");
Assert.IsTrue(returnValue.Id != 0);
Assert.AreEqual(returnValue.BrokerGuid, Guid.Empty);
}
public async Task FillFromStatementWithDataReaderWithObjectReturnAsync()
{
var da = new SqlDataAccess(ConnectionString);
var helper = new SqlDataAccessHelper();
helper.SetRetrievalMode(DataActionRetrievalType.DataReader);
helper.AddStatement(
"select name,database_id [Id],is_read_only [ReadOnly],service_broker_guid [BrokerGuid],create_date [CreateDate] from sys.databases where name in (@master,@model,@msdb) order by 1 asc");
helper.AddParameter("master", "master", SqlDbType.VarChar);
helper.AddParameter("model", "model", SqlDbType.VarChar);
helper.AddParameter("msdb", "msdb", SqlDbType.VarChar);
var values = await da.FillAsync <DatabaseModel>(helper);
Assert.AreEqual(values.Count, 3);
Assert.AreEqual(values[0].Name, "master");
Assert.AreEqual(values[1].Name, "model");
Assert.AreEqual(values[2].Name, "msdb");
}
public void FillFromStatementWithDataSetWithStringReturn()
{
var da = new SqlDataAccess(ConnectionString);
var helper = new SqlDataAccessHelper();
helper.SetRetrievalMode(DataActionRetrievalType.DataSet);
helper.AddStatement("select name from sys.databases where name in (@master,@model,@msdb) order by 1 asc");
helper.AddParameter("master", "master", SqlDbType.VarChar);
helper.AddParameter("model", "model", SqlDbType.VarChar);
helper.AddParameter("msdb", "msdb", SqlDbType.VarChar);
var values = da.Fill <string>(helper);
this.OutputTestTimings(helper.ExecutionContext);
Assert.AreEqual(values.Count, 3);
Assert.AreEqual(values[0], "master");
Assert.AreEqual(values[1], "model");
Assert.AreEqual(values[2], "msdb");
}
public void ConnectionTimeoutTest()
{
var da = new SqlDataAccess(ConnectionString);
var helper = new SqlDataAccessHelper();
helper.SetRetrievalMode(DataActionRetrievalType.DataReader);
helper.SetTimeout(1);
helper.AddStatement("WAITFOR DELAY \'00:15\';");
try
{
var values = da.Fill <DbHelpModel>(helper);
Assert.Fail("Statement should time out");
}
catch (Exception e)
{
Assert.IsTrue(e.Message == "Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.");
}
}
public void PostProcessingTest()
{
var da = new SqlDataAccess(ConnectionString);
var helper = new SqlDataAccessHelper();
helper.SetRetrievalMode(DataActionRetrievalType.DataSet);
helper.AddStatement(
"select top 1 name,database_id [Id],is_read_only [ReadOnly],service_broker_guid [BrokerGuid],create_date [CreateDate] from sys.databases where name in (@master,@model,@msdb) order by 1 asc");
helper.AddParameter("master", "master", SqlDbType.VarChar);
helper.AddParameter("model", "model", SqlDbType.VarChar);
helper.AddParameter("msdb", "msdb", SqlDbType.VarChar);
helper.SetPostProcessFunction <string>("name", s => s.ToString() == "master" ? "MAPPING FUNCTION" : s);
var returnValue = da.Get <DatabaseModel>(helper);
this.OutputTestTimings(helper.ExecutionContext);
Assert.AreEqual(returnValue.Name, "MAPPING FUNCTION");
Assert.IsTrue(returnValue.Id != 0);
Assert.AreEqual(returnValue.BrokerGuid, Guid.Empty);
}
public void GetFromStatementWithDataSetTableSchmeaMapping()
{
var da = new SqlDataAccess(ConnectionString);
var helper = new SqlDataAccessHelper();
helper.SetRetrievalMode(DataActionRetrievalType.DataSet);
helper.AddStatement(
"select top 1 name [usp_Name],database_id [usp_Id],is_read_only [usp_ReadOnly],service_broker_guid [usp_BrokerGuid],create_date [usp_CreateDate] from sys.databases where name in (@master,@model,@msdb) order by 1 asc");
helper.AddParameter("master", "master", SqlDbType.VarChar);
helper.AddParameter("model", "model", SqlDbType.VarChar);
helper.AddParameter("msdb", "msdb", SqlDbType.VarChar);
helper.ExecutionContext.TableSchema = "usp_";
var returnValue = da.Get <DatabaseModel>(helper);
this.OutputTestTimings(helper.ExecutionContext);
Assert.AreEqual(returnValue.Name, "master");
Assert.IsTrue(returnValue.Id != 0);
Assert.AreEqual(returnValue.BrokerGuid, Guid.Empty);
}
public async Task FillToDictionaryFromStatementWithDataSetWithObjectReturnAsync()
{
var da = new SqlDataAccess(ConnectionString);
var helper = new SqlDataAccessHelper();
helper.SetRetrievalMode(DataActionRetrievalType.DataSet);
helper.AddStatement(
"select name,row_number() over (ORDER BY name ASC)[Id],is_read_only [ReadOnly],service_broker_guid [BrokerGuid],create_date [CreateDate] from sys.databases where name in (@master,@model,@msdb) order by 1 asc");
helper.AddParameter("master", "master", SqlDbType.VarChar);
helper.AddParameter("model", "model", SqlDbType.VarChar);
helper.AddParameter("msdb", "msdb", SqlDbType.VarChar);
var values = await da.FillToDictionaryAsync <int, DatabaseModel>("Id", helper);
this.OutputTestTimings(helper.ExecutionContext);
Assert.AreEqual(values.Keys.Count, 3);
Assert.AreEqual(values[1].Name, "master");
Assert.AreEqual(values[2].Name, "model");
Assert.AreEqual(values[3].Name, "msdb");
}