public virtual IEnumerable <T> List <T>(string query) where T : class
{
using (var connection = _dbConnectionFactory.Create())
{
return(connection
.Query <T>(query)
.ToList());
}
}
public IdentityAuthOptionsConfig[] GetAll()
{
string sql = $"select * from {_tableName} order by {nameof(IdentityAuthOptionsConfig.CreateTime)} desc";
_logger.Debug($"{nameof(GetAll)} sql:{sql}");
using (IDbConnection cone = _connectionProvider.Create())
{
cone.Open();
return(cone.Query <IdentityAuthOptionsConfig>(sql).ToArray());
}
}
public virtual Guid Run(Contact contact)
{
var sqlCommand = _customConfiguration.Value.UpdateCommand;
var contactGuid = contact.Uuid;
if (contactGuid == Guid.Empty)
{
contactGuid = Guid.NewGuid();
sqlCommand = _customConfiguration.Value.InsertCommand;
}
using (var connection = _dbConnectionFactory.Create())
{
connection.Execute(sqlCommand, new
{
contact.FirstName,
contact.LastName,
contact.HomePhone,
contact.WorkPhone,
contact.MobilePhone,
Uuid = contactGuid
});
}
return(contactGuid);
}
public static IDatabase GetMySqlDatabase(string connectionString)
{
var factory = new DbConnectionFactory(connectionString, MySqlProvider.Instance);
var config = new DapperExtensionsConfiguration(typeof(AutoClassMapper <>), new List <Assembly>(), new MySqlDialect());
return(new Database(factory.Create(), new SqlGeneratorImpl(config)));
}
public async Task <DbData> ExtractAsync(string viewName, ViewParameters parameters)
{
using (DbConnection connection = DbConnectionFactory.Create(_dbEngine, _connectionString))
{
try
{
_logger.LogDebug("Extract db data async via \"View\" started");
DbData result = null;
await connection.OpenAsync().ConfigureAwait(false);
string cmdText = SqlStatmentsGenerator.CreateSelectStatement(SqlStatmentsGenerator.SelectAllColumns, viewName, parameters);
using (IDbCommand command = DbCommandFactory.Create(_dbEngine, connection, cmdText))
{
command.CommandType = CommandType.Text;
result = await ReadDataImplAsync((DbCommand)command);
}
connection.Close();
_logger.LogDebug("Extract db data async via \"View\" completed");
return(result);
}
catch (Exception e)
{
_logger.LogError($"An error occured during async data extraction via \"View\", exception: {e}");
return(null);
}
}
}
public void Creates_Connection()
{
var connection = _factory.Create();
Assert.That(connection, Is.Not.Null);
Assert.That(connection, Is.InstanceOf<SqlConnection>());
Assert.That(connection.ConnectionString, Is.EqualTo(_connectionString));
}
public void Create()
{
// Act
var result = _target.Create();
// Assert
Assert.IsNotNull(result);
}
public Tuple <IDataReader, IDbConnection> ExecuteDbReader(string connectionString, string cmdText)
{
IDbConnection connection = DbConnectionFactory.Create(_dbEngine, connectionString);
IDbCommand command = DbCommandFactory.Create(_dbEngine, connection, cmdText);
connection.Open();
return(new Tuple <IDataReader, IDbConnection>(ExecuteDbReader(command as DbCommand), connection));
}
public virtual IEnumerable <Contact> Run()
{
using (var connection = _dbConnectionFactory.Create())
{
return(connection
.Query <Contact>(_customConfiguration.Value.ContactsQuery)
.ToList());
}
}
public async Task <Tuple <DbDataReader, DbConnection> > ExecuteDbReaderAsync(string connectionString, string cmdText)
{
DbConnection connection = DbConnectionFactory.Create(_dbEngine, connectionString);
await connection.OpenAsync();
IDbCommand command = DbCommandFactory.Create(_dbEngine, connection, cmdText);
return(new Tuple <DbDataReader, DbConnection>(await ExecuteDbReaderAsync(command as DbCommand), connection));
}
public string HashPassword(string password)
{
using (var cn = DbConnectionFactory.Create("DefaultConnection"))
{
cn.Open();
var sql = "SELECT dbo.HashPassword(@RawPassword)";
return(cn.Query <string>(sql, new { RawPassword = password }).Single());
}
}
public void UsingDataBase(Action <IDatabase> action)
{
var factory = new DbConnectionFactory(connectionString, PostgreSqlProvider.Instance);
var config = new DapperExtensionsConfiguration(typeof(AutoClassMapper <>), new List <Assembly>(), new PostgreSqlDialect());
using (IDatabase db = new Database(factory.Create(), new SqlGeneratorImpl(config)))
{
action(db);
}
}
public virtual void Run(Guid uuid)
{
using (var connection = _dbConnectionFactory.Create())
{
connection.Execute(_customConfiguration.Value.DeleteCommand, new
{
Uuid = uuid
});
}
}
public void ExpressiveCommandValidation()
{
var factory = DbConnectionFactory.Create(() =>
{
var conn = Substitute.For <IDbConnection>();
return(conn);
});
Assert.Throws <ArgumentNullException>(() => factory.Command(null));
Assert.Throws <ArgumentException>(() => factory.Command(string.Empty));
}
/// <summary>
/// Full sql command execution in one call without manual connection open and close, constructing
/// proper instance of DbCommand
/// </summary>
/// <param name="connectionString"> Database connection string </param>
/// <param name="cmdText"> Script that should be executed </param>
/// <returns>
/// True if execution was successful, otherwise - false
/// </returns>
public bool ExecuteNonQuery(string connectionString, string cmdText)
{
using (DbConnection connection = DbConnectionFactory.Create(_dbEngine, connectionString))
{
IDbCommand command = DbCommandFactory.Create(_dbEngine, connection, cmdText);
connection.Open();
bool result = ExecuteNonQuery(command as DbCommand);
connection.Close();
return(result);
}
}
public void Test1()
{
var factory = new DbConnectionFactory(connectionString, MySqlProvider.Instance);
var config = new DapperExtensionsConfiguration(typeof(AutoClassMapper <>), new List <Assembly>(), new MySqlDialect());
IDatabase db = new Database(factory.Create(), new SqlGeneratorImpl(config));
var users = db.Select <User>();
//var p = db.Select<Post>(x => x.Id == 1);
//var id = db.Insert(new User
//{
// Id = 9,
// Name = "Lua3"
//});
//Console.WriteLine(id);
//UsingDbConnectionFactory(db =>
//{
// //var r = db.Select<User>().ToList();
// //r.Count().ShouldNotBe(0);
// var sql =@"
// select * from post as p
// left join #user as u on u.id = p.ownerid
// Order by p.Id";
// var data = db.Query<Post, User, Post>(sql, (p, user) => { p.Owner = user; return p; });
// var post = data.First();
//});
//var factory = new DbConnectionFactory(connectionString, PostgreSqlProvider.Instance);
//var config = new DataAccessExtensionsConfiguration(typeof(AutoClassMapper<>), new List<Assembly>(), new PostgreSqlDialect());
//IDatabase db2 = new Database(factory.Create(), new SqlGeneratorImpl(config));
//db2.RunInTransaction(() =>
//{
// //var id = db2.Insert(new User
// //{
// // Name = "New1"
// //});
// //db2.Insert(new UserEx
// //{
// // Id = id,
// // NameEx = "New1Ex"
// //});
//});
////var users = db2.GetList<User>();
}
public async Task <bool> ExecuteNonQueryAsync(string connectionString, string cmdText)
{
using (DbConnection connection = DbConnectionFactory.Create(_dbEngine, connectionString))
{
IDbCommand command = DbCommandFactory.Create(_dbEngine, connection, cmdText);
await connection.OpenAsync().ConfigureAwait(false);
bool result = await ExecuteNonQueryAsync(command as DbCommand);
connection.Close();
return(result);
}
}
private bool ExecuteStatement(string connectionString, string statement)
{
bool result = true;
using (IDbConnection connection = DbConnectionFactory.Create(_dbEngine, connectionString))
{
connection.Open();
IDbCommand command = DbCommandFactory.Create(_dbEngine, connection, statement);
result = ExecuteNonQuery(command);
connection.Close();
return(result);
}
}
public Task <MyAppUser> FindByNameAsync(string userName)
{
using (var cn = DbConnectionFactory.Create("DefaultConnection"))
{
cn.Open();
var sql = " SELECT " +
" Convert(nvarchar(MAX), UserID) AS ID , LoginCode AS UserName , FirstName , LastName , EMail , Password AS HashedPassword " +
" FROM " +
" UserInfo " +
" WHERE " +
" LoginCode = @UserName ";
var users = cn.Query <MyAppUser>(sql, new { UserName = userName });
return(Task.FromResult(users.FirstOrDefault()));
}
}
protected override async Task <PagedResponse <UserProfileDto> > Handle(PagedQueryRequest <GetProfilesQuery, UserProfileDto> request)
{
const string query = "SELECT " +
"Id, UserName, Email, Created, Modified, LastLogged " +
"FROM dbo.Users " +
"Order By Id " +
"OFFSET @Offset ROWS " +
"FETCH NEXT @Next ROWS ONLY " +
"SELECT COUNT(*) from dbo.Users";
await using var conn = DbConnectionFactory.Create();
using var multiQuery = await conn.QueryMultipleAsync(query, request.Pager);
var results = await multiQuery.ReadAsync <UserProfileDto>();
var totalCount = await multiQuery.ReadFirstAsync <int>();
return(new PagedResponse <UserProfileDto>(results, request.Pager, totalCount));
}
public async Task <DbData> ExtractAsync(string storedProcedureName, IList <StoredProcedureParameter> parameters)
{
using (DbConnection connection = DbConnectionFactory.Create(_dbEngine, _connectionString))
{
try
{
_logger.LogDebug("Extract db data async via \"Stored procedure\" started");
DbData result = null;
await connection.OpenAsync().ConfigureAwait(false);
using (IDbCommand command = DbCommandFactory.Create(_dbEngine, connection, storedProcedureName))
{
command.CommandType = CommandType.StoredProcedure;
// command.CommandText = "call " + command.CommandText;
// add parameters
if (parameters != null && parameters.Count > 0)
{
foreach (StoredProcedureParameter parameter in parameters)
{
if (string.IsNullOrEmpty(parameter.ParameterName))
{
throw new InvalidDataException("parameter name can't be null or empty");
}
DbParameter procedureParameter = DbParameterFactory.Create(_dbEngine, parameter.ParameterName, parameter.ParameterType,
parameter.ParameterValue);
command.Parameters.Add(procedureParameter);
}
}
result = await ReadDataImplAsync((DbCommand)command);
}
connection.Close();
_logger.LogDebug("Extract db data async via \"Stored procedure\" completed");
return(result);
}
catch (Exception e)
{
_logger.LogError($"An error occured during async data extraction via \"Stored procedure\", exception: {e}");
return(null);
}
}
}
public static IServiceCollection AddCustomConfiguration(this IServiceCollection services, IConfiguration configuration)
{
services.AddTransient((serviceProvider) => DbConnectionFactory.Create(configuration.GetConnectionString("Default")));
services.Configure <AppSettings>(configuration);
//var configService = new ServiceConfiguration();
//services.AddTransient<ServiceConfiguration>((serviceProvider) => { return configService; });
var workerConfig = new WorkerConfiguration
{
WorkerConfigs = new List <WorkerConfig>()
};
ConfigurationBinder.Bind(configuration, "WorkerConfiguration", workerConfig.WorkerConfigs);
services.AddTransient((serviceProvider) => { return(workerConfig); });
services.AddScoped(provider => provider.GetService <IOptionsMonitor <AppSettings> >().CurrentValue);
return(services);
}
/// <summary>
/// Registers the type mappings with the Unity container.
/// </summary>
/// <param name="container">The unity container to configure.</param>
/// <remarks>
/// There is no need to register concrete types such as controllers or API controllers
/// (unless you want to change the defaults), as Unity allows resolving a concrete type even
/// if it was not previously registered.
/// </remarks>
public static void RegisterTypes(IUnityContainer container)
{
// NOTE: To load from web.config uncomment the line below. Make sure to add a
// Microsoft.Practices.Unity.Configuration to the using statements. container.LoadConfiguration();
// TODO: Register your types here container.RegisterType<IProductRepository, ProductRepository>();
// DbConnection
container.RegisterType <DbConnection>(
new PerRequestLifetimeManager(),
new InjectionFactory(_ =>
{
return(DbConnectionFactory.Create());
})
);
// コンポーネント属性の型を登録
RegisterTypesOfComponent(container);
// ServiceLocatorの作成、登録
IServiceLocator serviceLocator = new UnityServiceLocator(container);
ServiceLocator.SetLocatorProvider(() => serviceLocator);
}
public async Task ProcessaEncomendasAsync()
{
using (var conn = dbConnectionFactory.Create())
{
var plataformaRepository = new PlataformaRepository(conn, this._config);
var logService = new LogService();
bool geraLog = _config.GetValue <bool>("GeraArquivoLog");
var listPedidos = await plataformaRepository.GetPedidosAsync();
var totalPedidos = listPedidos.Count;
Console.WriteLine($"-------------------------------------------------------------------------------------------------------");
Console.WriteLine($"Processando Encomendas - ({totalPedidos}) - {DateTime.Now}");
Console.WriteLine($"-------------------------------------------------------------------------------------------------------");
if (listPedidos.Count > 0)
{
Stopwatch sw = new Stopwatch();
sw.Start();
foreach (var pedido in listPedidos)
{
try
{
bool status = await plataformaRepository.CheckPedidoAsync(pedido.Pedido);
//teste();
//Console.WriteLine(GerarTrackingUrl(pedido));
if (status)
{
await PostPedidosAsync(pedido, plataformaRepository);
}
else
{
Console.WriteLine($"Pedido já cadastrado - OrderId: {pedido.Pedido}");
}
}
catch (Exception e)
{
string retorno = $"Erro OrderId: {pedido.Pedido} - ({e.Message}) - {DateTime.Now}.";
Console.WriteLine(retorno);
await plataformaRepository.PedidoLogAsync(pedido.Pedido, retorno);
if (geraLog)
{
string fileName = $"{pedido.Pedido}";
var log = new RetornoLog();
log.acao = $"PostEncomendaPlataforma";
log.message = retorno;
log.request = pedido;
log.response = e.Message;
await logService.LogGerarArquivoAsync(fileName, "Plataforma", log);
}
}
}
sw.Stop();
Console.WriteLine($"Processamento de Pedidos da Plataforma Concluido com Sucesso. Total ({totalPedidos}) - {DateTime.Now} - ({sw.ElapsedMilliseconds / 1000}s)");
}
}
}
