/// <summary>
/// Executes sql statement and returns concatenated result as string.
/// </summary>
/// <param name="sql">SQL query that will be executed.</param>
/// <returns>Task</returns>
public static async Task <string> GetString(this IQueryMapper mapper, SqlCommand cmd)
{
var sb = new StringBuilder();
await mapper.Sql(cmd).Map(reader => sb.Append(reader[0]));
return(sb.ToString());
}
public async Task ReturnsConstant(bool useAsync)
{
// Arrange
int constant = new Random().Next();
constant = constant % 10000;
int result = 0;
var sql = String.Format("select {0} 'a'", constant);
// Action
var t = mapper.Sql(sql).Map(reader => { result = reader.GetInt32(0); });
if (useAsync)
{
await t;
}
else
{
t.Wait();
}
// Assert
Assert.Equal(constant, result);
}
/// <summary>
/// Set the query text on the mapper.
/// </summary>
/// <returns>Query Mapper.</returns>
public static IQueryMapper Sql(this IQueryMapper mapper, string query)
{
var cmd = new SqlCommand(query);
return(mapper.Sql(cmd));
}
public async Task CRUD(bool useCommand)
{
List <string> errors = new List <string>();
IQueryMapper mapper = CreateNewMapper(errors);
var command = CreateNewCommand(errors);
var sqlCmd = new SqlCommand();
int ID = -1;
lock (lockIdentity)
{
ID = identity++;
}
string NAME = "MSFT" + ID;
string NAME2 = "MDCS" + ID;
string NAME3 = "Microsoft" + ID;
string NAME4 = "MS" + ID;
int count = -1;
mapper = CreateNewMapper(errors);
if (useCommand)
{
sqlCmd = new SqlCommand();
sqlCmd.CommandText = "select count(*) from Company where CompanyId = @ID";
sqlCmd.Parameters.AddWithValue("ID", ID);
await mapper.Sql(sqlCmd).Map(reader => count = reader.GetInt32(0));
}
else
{
await mapper
.Sql("select count(*) from Company where CompanyId = @ID")
.Param("ID", System.Data.DbType.Int32, ID)
.Map(reader => count = reader.GetInt32(0));
}
Assert.Equal(0, count);
command = CreateNewCommand(errors);
if (useCommand)
{
sqlCmd.CommandText = "insert into Company(companyId, Name) values(@ID, @NAME)";
sqlCmd.Parameters.Clear();
sqlCmd.Parameters.AddWithValue("ID", ID);
sqlCmd.Parameters.AddWithValue("NAME", NAME);
await command.Sql(sqlCmd).Exec();
}
else
{
await
command
.Sql("insert into Company(companyId, Name) values(@ID, @NAME)")
.Param("ID", System.Data.DbType.Int32, ID)
.Param("NAME", System.Data.DbType.String, NAME)
.Exec();
}
mapper = CreateNewMapper(errors);
if (useCommand)
{
sqlCmd.CommandText = "select count(*) from Company where CompanyId = @ID";
sqlCmd.Parameters.Clear();
sqlCmd.Parameters.AddWithValue("ID", ID);
await mapper.Sql(sqlCmd).Map(reader => count = reader.GetInt32(0));
}
else
{
await
mapper
.Sql("select count(*) from Company where CompanyId = @ID")
.Param("ID", System.Data.DbType.Int32, ID)
.Map(reader => count = reader.GetInt32(0));
}
Assert.Equal(1, count);
mapper = CreateNewMapper(errors);
int? id = null;
string name = null;
if (useCommand)
{
sqlCmd.CommandText = "select CompanyId, Name from Company where CompanyId = @ID";
sqlCmd.Parameters.Clear();
sqlCmd.Parameters.AddWithValue("ID", ID);
await mapper
.Sql(sqlCmd)
.Map(reader => { id = reader.GetInt32(0); name = reader.GetString(1); });
}
else
{
await
mapper
.Sql("select CompanyId, Name from Company where CompanyId = @ID")
.Param("ID", System.Data.DbType.Int32, ID)
.Map(reader => { id = reader.GetInt32(0); name = reader.GetString(1); });
}
Assert.Equal(ID, id);
Assert.Equal(NAME, name);
if (useCommand)
{
await command
.Sql("update Company set Name = '" + NAME2 + "' where CompanyId = " + ID)
.Exec();
}
else
{
await command
.Sql("update Company set Name = '" + NAME2 + "' where CompanyId = " + ID)
.Exec();
}
mapper = CreateNewMapper(errors);
if (useCommand)
{
sqlCmd.CommandText = "select Name from Company where CompanyId = @ID";
sqlCmd.Parameters.Clear();
sqlCmd.Parameters.AddWithValue("ID", ID);
await mapper
.Sql(sqlCmd)
.Map(reader => { name = reader.GetString(0); });
}
else
{
await mapper
.Sql("select Name from Company where CompanyId = @ID")
.Param("ID", System.Data.DbType.Int32, ID)
.Map(reader => { name = reader.GetString(0); });
}
Assert.Equal(NAME2, name);
command = CreateNewCommand(errors);
if (useCommand)
{
sqlCmd.CommandText = "delete Company where CompanyId = @ID";
sqlCmd.Parameters.Clear();
sqlCmd.Parameters.AddWithValue("ID", ID);
await command.Sql(sqlCmd).Exec();
}
else
{
await command
.Sql("delete Company where CompanyId = @ID")
.Param("ID", System.Data.DbType.Int32, ID)
.Exec();
}
mapper = CreateNewMapper(errors);
if (useCommand)
{
sqlCmd.CommandText = "select count(*) from Company where CompanyId = @ID";
sqlCmd.Parameters.Clear();
sqlCmd.Parameters.AddWithValue("ID", ID);
await mapper.Sql(sqlCmd).Map(reader => count = reader.GetInt32(0));
}
else
{
await mapper
.Sql("select count(*) from Company where CompanyId = @ID")
.Param("ID", System.Data.DbType.Int32, ID)
.Map(reader => count = reader.GetInt32(0));
}
Assert.Equal(0, count);
id = null;
command = CreateNewCommand(errors);
if (useCommand)
{
sqlCmd.CommandText = "insert into Company(Name) output inserted.CompanyId values(@NAME)";
sqlCmd.Parameters.Clear();
sqlCmd.Parameters.AddWithValue("NAME", NAME3);
await command.Sql(sqlCmd).Map(reader => id = reader.GetInt32(0));
}
else
{
await command
.Sql("insert into Company(Name) output inserted.CompanyId values(@NAME)")
.Param("NAME", System.Data.DbType.String, NAME3)
.Map(reader => id = reader.GetInt32(0));
}
mapper = CreateNewMapper(errors);
if (useCommand)
{
sqlCmd.CommandText = "select count(*) from Company where CompanyId = @ID";
sqlCmd.Parameters.Clear();
sqlCmd.Parameters.AddWithValue("ID", id);
await mapper.Sql(sqlCmd).Map(reader => count = reader.GetInt32(0));
}
else
{
await mapper
.Sql("select count(*) from Company where CompanyId = @ID")
.Param("ID", System.Data.DbType.Int32, id)
.Map(reader => count = reader.GetInt32(0));
}
Assert.Equal(1, count);
mapper = CreateNewMapper(errors);
if (useCommand)
{
sqlCmd.CommandText = "select Name from Company where CompanyId = @ID";
sqlCmd.Parameters.Clear();
sqlCmd.Parameters.AddWithValue("ID", id);
await mapper.Sql(sqlCmd).Map(reader => name = reader.GetString(0));
}
else
{
await mapper
.Sql("select Name from Company where CompanyId = @ID")
.Param("ID", System.Data.DbType.Int32, id)
.Map(reader => name = reader.GetString(0));
}
Assert.Equal(NAME3, name);
string oldname = null;
string newname = null;
await command.Sql("update Company SET Name = '" + NAME4 + "' output deleted.Name, inserted.Name where CompanyId = " + id).Map(reader => { oldname = reader.GetString(0); newname = reader.GetString(1); });
Assert.Equal(NAME3, oldname);
Assert.Equal(NAME4, newname);
name = null;
int deletedId = -1;
command = CreateNewCommand(errors);
if (useCommand)
{
sqlCmd.CommandText = "delete Company output deleted.CompanyId, deleted.Name where CompanyId = @ID";
sqlCmd.Parameters.Clear();
sqlCmd.Parameters.AddWithValue("ID", id);
await command.Sql(sqlCmd).Map(
reader => { deletedId = reader.GetInt32(0); name = reader.GetString(1); });
}
else
{
await command
.Sql("delete Company output deleted.CompanyId, deleted.Name where CompanyId = @ID")
.Param("ID", System.Data.DbType.Int32, id)
.Map(reader => { deletedId = reader.GetInt32(0); name = reader.GetString(1); });
}
Assert.Equal(NAME4, name);
Assert.Equal(id, deletedId);
Assert.Empty(errors);
}
public static Task ExecuteReader(this IQueryMapper mapper, SqlCommand cmd, Func <DbDataReader, Task> callback)
{
return(mapper.Sql(cmd).Map(callback));
}
public static Task Map(this IQueryMapper mapper, string sql, Func <DbDataReader, Task> callback)
{
var cmd = new SqlCommand(sql);
return(mapper.Sql(cmd).Map(callback));
}
public static Task Map(this IQueryMapper mapper, DbCommand cmd, Action <DbDataReader> callback)
{
return(mapper.Sql(cmd).Map(callback));
}