/// <summary>
/// Created IActionResult object that contains the processed response result.
/// </summary>
/// <param name="pipe">Sql Pipe that will be used to fetch the data.</param>
/// <returns>ContentResult with the data processed by request.</returns>
public virtual async Task <IActionResult> GetResult(IQueryMapper mapper)
{
IActionResult result = null;
try
{
var json = await mapper
.GetString(cmd)
.ConfigureAwait(false);
result = new ContentResult()
{
Content = json,
StatusCode = StatusCodes.Status200OK,
ContentType = "application/json"
};
} catch (Exception ex)
{
result = new ContentResult()
{
Content = ex.Message,
StatusCode = StatusCodes.Status500InternalServerError
};
}
return(result);
}
public static IQueryMapper OnError(this IQueryMapper mapper, Action <Exception> handler)
{
var stmt = mapper as BaseStatement;
stmt.AddErrorHandler(new ActionErrorHandlerBuilder(handler));
return(mapper);
}
/// <summary>
/// Add action that will be executed once the underlying data source is changed.
/// Make sure that you call SqlDependency.Start(connString) once application starts, and SqlDependency.Stop(connString); when application stops.
/// </summary>
/// <param name="mapper">Mapper object that will execute the query.</param>
/// <param name="action">Action that will be executed once the query results change.</param>
/// <returns>Mapper.</returns>
public static IQueryMapper OnChange(this IQueryMapper mapper, OnChangeEventHandler action)
{
var cmd = ((mapper as Common.BaseStatement).Command as SqlCommand);
new SqlDependency(cmd).OnChange += action;
return(mapper);
}
/// <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 static IQueryMapper AddRls(this IQueryMapper mapper, string key, Func <string> value)
{
var stmt = mapper as BaseStatement;
stmt.SetCommandModifier(CreateRlsCommandModifier(key, value));
return(mapper);
}
public static IQueryMapper AddContextVariable(this IQueryMapper mapper, string key, Func <string> value, bool isReadOnly = true)
{
var stmt = mapper as BaseStatement;
stmt.SetCommandModifier(CreateSessionContextCommandModifier(key, value, isReadOnly));
return(mapper);
}
public async static Task <List <T> > Map <T>(this IQueryMapper query, Func <DbDataReader, T> mapper)
{
var res = new List <T>();
await query.Map(reader => { res.Add(mapper(reader)); });
return(res);
}
public IEnumerable <LogIn> GetLogInsWhichIdIsBetween(long begin, long end)
{
const string kExecute = @"
select id
,agente
,dtini
,dtfim
,filas
,1000 as tipo_id
,dtfim >= dtini as loggedout
from login
where id between @begin and @end
order by id asc
";
var mysql_connection_provider =
new MySqlConnectionProvider(
"Data Source=192.168.203.207;Initial Catalog=acaconb1;User ID=callflex;Password=$callflex$;");
var mysql_executor =
new MySqlQueryExecutor(mysql_connection_provider);
/*using (
* var conn =
* new MySqlConnection(
* "Data Source=192.168.203.207;Initial Catalog=acaconb1;User ID=callflex;Password=$callflex$;")
* )
* using (var builder = new CommandBuilder(conn)) {
* IDbCommand cmd =
* builder
* .AddParameter("@begin", begin)
* .AddParameter("@end", end)
* .SetText(kExecute)
* .Build();
* conn.Open();
* using (IDataReader reader = cmd.ExecuteReader()) {
* int[] ordinals = new int[] {
* reader.GetOrdinal("id"), reader.GetOrdinal("tipo_id"),
* reader.GetOrdinal("dtini"), reader.GetOrdinal("agente"),
* reader.GetOrdinal("dtfim"), reader.GetOrdinal("filas"),
* reader.GetOrdinal("loggedout")
* };
* //return Map(reader);
* }
* conn.Close();
* }*/
using (IQueryMapper <LogIn> mapper =
mysql_executor
.ExecuteQuery(kExecute,
LogInDto,
builder =>
builder
.AddParameter("@begin", begin)
.AddParameter("@end", end))) {
IEnumerable <LogIn> logins = mapper.Map(false);
//IEnumerable<LogIn> logins = Map(((QueryMapper<LogIn>) mapper).Reader);
return(logins);
}
//return Enumerable.Empty<LogIn>();
}
public DocumentQuery(IQueryMapper queryMapper, IDocumentQueryFactory queryFactory)
{
Guard.AgainstNull(queryMapper, nameof(queryMapper));
Guard.AgainstNull(queryFactory, nameof(queryFactory));
_queryMapper = queryMapper;
_queryFactory = queryFactory;
}
public static IQueryMapper Sql(this IQueryMapper mapper, DbCommand cmd)
{
if (mapper is BaseStatement)
{
(mapper as BaseStatement).SetCommand(cmd);
}
return(mapper);
}
public static IQueryMapper Param(this IQueryMapper mapper, string name, System.Data.DbType type, object value, int size = 0)
{
if (mapper is BaseStatement)
{
(mapper as BaseStatement).AddParameter(name, type, value, size);
}
return(mapper);
}
public PermissionQuery(IQueryMapper queryMapper, IPermissionQueryFactory queryFactory)
{
Guard.AgainstNull(queryMapper, nameof(queryMapper));
Guard.AgainstNull(queryFactory, nameof(queryFactory));
_queryMapper = queryMapper;
_queryFactory = queryFactory;
}
public MessageTypeDispatchedQuery(IQueryMapper queryMapper,
IMessageTypeDispatchedQueryFactory queryFactory)
{
Guard.AgainstNull(queryMapper, nameof(queryMapper));
Guard.AgainstNull(queryFactory, nameof(queryFactory));
_queryMapper = queryMapper;
_queryFactory = queryFactory;
}
public MessageTypeAssociationQuery(IQueryMapper queryMapper,
IMessageTypeAssociationQueryFactory queryFactory)
{
Guard.AgainstNull(queryMapper, nameof(queryMapper));
Guard.AgainstNull(queryFactory, nameof(queryFactory));
_queryMapper = queryMapper;
_queryFactory = queryFactory;
}
public static async Task <HttpResponseMessage> CreateODataResponse(
this HttpRequestMessage req,
TableSpec tableSpec,
IQueryMapper mapper)
{
var querySpec = OData.UriParser.Parse(tableSpec, req);
var sql = QueryBuilder.Build(querySpec, tableSpec).AsJson("value");
return(await CreateSqlResponse(req, mapper, sql));
}
public QueueQuery(IDatabaseGateway databaseGateway, IQueryMapper queryMapper, IQueueQueryFactory queryFactory)
{
Guard.AgainstNull(databaseGateway, nameof(databaseGateway));
Guard.AgainstNull(queryFactory, nameof(queryFactory));
Guard.AgainstNull(queryMapper, nameof(queryMapper));
_databaseGateway = databaseGateway;
_queryFactory = queryFactory;
_queryMapper = queryMapper;
}
public PrimitiveEventRepository(IDatabaseGateway databaseGateway, IQueryMapper queryMapper, IPrimitiveEventQueryFactory queryFactory)
{
Guard.AgainstNull(databaseGateway, "databaseGateway");
Guard.AgainstNull(queryMapper, "queryMapper");
Guard.AgainstNull(queryFactory, "queryFactory");
_databaseGateway = databaseGateway;
_queryMapper = queryMapper;
_queryFactory = queryFactory;
}
/// <summary>
/// Add action that will be executed once the underlying data source is changed.
/// Make sure that you call SqlDependency.Start(connString) once application starts, and SqlDependency.Stop(connString); when application stops.
/// </summary>
/// <param name="mapper">Mapper object that will execute the query.</param>
/// <param name="action">Action that will be executed once the query results change.</param>
/// <returns>Mapper.</returns>
public static IQueryMapper OnChange(this IQueryMapper mapper, Action <SqlNotificationEventArgs> action)
{
var cmd = ((mapper as Common.BaseStatement).Command as SqlCommand);
new SqlDependency(cmd)
.OnChange += (sender, e) => { if (e.Type == SqlNotificationType.Change)
{
action(e);
}
};
return(mapper);
}
public IEnumerable <long> GetSeriesIds(string name, int hash, int count)
{
using (IQueryMapper <long> mapper =
sql_query_executor_
.ExecuteQuery(schema_ + ".mtc_get_id_of_serie",
Mappers.Long,
builder =>
builder
.AddParameter("@name", name)
.AddParameter("@hash", hash)
.AddParameter("@tags_count", count))) {
return(mapper.Map(false));
}
}
protected RepositoryBase(IStore store, ICache <T> cache, IFactory <T> factory, ISchema <T> schema, ISchemaMapper <T> schemaMapper, IModelMapper <T> modelMapper, IPersistMapper <T> persistMapper, IQueryMapper <T> queryMapper, IFactory <ICommand> commandFactory, ISQLiteStatementFactory statementFactory, IFactory <IBatch> batchFactory, IFactory <IQuery <T> > queryFactory)
{
Store = store;
Cache = cache;
Factory = factory;
Schema = schema;
SchemaMapper = schemaMapper;
ModelMapper = modelMapper;
PersistMapper = persistMapper;
QueryMapper = queryMapper;
CommandFactory = commandFactory;
StatementFactory = statementFactory;
BatchFactory = batchFactory;
QueryFactory = queryFactory;
}
/// <summary>
/// Returns first row mapped as object T, or default value of T if no results are returned.
/// </summary>
/// <typeparam name="T">Generic type of the objects that wil be returned.</typeparam>
/// <param name="query">The IQueryMapper object.</param>
/// <param name="mapper">Mapper function that will convert DbDatareader to T</param>
/// <returns>First object in the result set of default(T).</returns>
public async static Task <T> FirstOrDefault <T>(this IQueryMapper query, Func <DbDataReader, T> mapper) where T : new()
{
var res = new T();
bool isNull = true;
await query.Map(reader => { res = mapper(reader); isNull = false; });
if (isNull)
{
return(default(T));
}
else
{
return(res);
}
}
public static async Task <HttpResponseMessage> CreateSqlResponse(
this HttpRequestMessage req,
IQueryMapper mapper,
SqlCommand sql
)
{
var httpStatus = HttpStatusCode.OK;
string body = await mapper
.OnError(ex => { httpStatus = HttpStatusCode.InternalServerError; })
.GetStringAsync(sql);
return(new HttpResponseMessage()
{
Content = new StringContent(body), StatusCode = httpStatus
});
}
public void should()
{
const string kExecute = @"
select distinct hc.cod_hist_cli
,hc.cod_dev
,cast(t.cod_cred as varchar) as cod_cred
,hc.data_cad
,isnull(hc.dt_agen_hist, hc.data_cad) as dt_agen_hist
,hc.usuario_cad
,hc.cod_ocor
,hc.data_up
,cast(case when hc.dt_agen_hist is null then 0 else 1 end as bit) as agendado
from historicos_clientes hc
inner join historicos_clientes_titulos hct on hct.cod_hist_cli = hc.cod_hist_cli
inner join titulos t with(nolock) on t.cod_tit = hct.cod_tit
where hc.cod_hist_cli between @min_cod_hist_cli and @max_cod_hist_cli
order by cod_hist_cli asc";
var map = new DataReaderMapperBuilder <CobEventDto>()
.Map(x => x.AgentId, "usuario_cad")
.Map(x => x.Segment, "cod_cred")
.Map(x => x.Date, "data_cad")
.Map(x => x.DebtorId, "cod_dev")
.Map(x => x.ExpiryDate, "dt_agen_hist")
.Map(x => x.Id, "cod_hist_cli", typeof(int))
.Map(x => x.TypeId, "cod_ocor")
.Map(x => x.IsScheduled, "agendado")
.Map(x => x.RecordDate, "data_up")
.Build();
var sql_connection_provider = new SqlConnectionProvider(@"Data Source=192.168.203.9\itau;Initial Catalog=cobsystems;User ID=nohros;Password=Noors03;");
var sql_executor = new SqlQueryExecutor(sql_connection_provider,
CommandType.Text);
using (IQueryMapper <CobEventDto> mapper =
sql_executor
.ExecuteQuery(kExecute,
() => map,
builder =>
builder
.AddParameter("@min_cod_hist_cli", 631583866)
.AddParameter("@max_cod_hist_cli", 631588866)))
{
Dynamics_.AssemblyBuilder.Save("nohros.tests.dll");
mapper.Map(false);
}
}
public DemoController(IQueryMapper queryMapper)
{
this.QueryMapper = queryMapper;
}
public Pipe()
{
sut = new Belgrade.SqlClient.SqlDb.QueryPipe(Util.Settings.MasterConnectionString);
mapper = new Belgrade.SqlClient.SqlDb.QueryMapper(Util.Settings.MasterConnectionString);
command = new Belgrade.SqlClient.SqlDb.Command(Util.Settings.MasterConnectionString);
}
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 Mapper()
{
mapper = new QueryMapper(Util.Settings.MasterConnectionString);
}
/// <summary>
/// Add a parameter with specified value to the mapper.
/// </summary>
/// <param name="mapper">Mapper where the parameter will be added.</param>
/// <param name="name">Name of the parameter.</param>
/// <param name="value">Value of the parameter.</param>
/// <returns>Mapper object.</returns>
public static IQueryMapper Param(this IQueryMapper mapper, string name, object value)
{
Util.AddParameterWithValue(mapper, name, value);
return(mapper);
}
/// <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));
}
/// <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, string sql)
{
var cmd = new SqlCommand(sql);
return(await mapper.GetString(cmd));
}
