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