public static NpgsqlConnection GetNpgsqlConnection()
{
if (connectionQueue.Count > 0)
{
lock (connectionQueue)
{
if (connectionQueue.Count > 0)
{
return(connectionQueue.Dequeue());
}
else
{
var conection = baseConnection.CloneWith(connectionString);
conection.Open();
return(conection);
}
}
}
else
{
var conection = baseConnection.CloneWith(connectionString);
conection.Open();
return(conection);
}
}
public void RecreateDatabase()
{
string sql = string.Empty;
var assembly = typeof(DapperMapping).Assembly;
var stream = assembly.GetManifestResourceStream("TarokScoreBoard.Infrastructure.Scripts.intitdb.sql");
using (var sr = new StreamReader(stream))
sql = sr.ReadToEnd();
var realConn = new NpgsqlConnection(connectionString);
tempConn = realConn.CloneWith($"User ID={realConn.UserName};Host={realConn.Host};Port={realConn.Port};Database={maintenaceDbName}");
tempConn.Open();
var testDatabaseInstanceName = realConn.Database;
var dropCommand = tempConn.CreateCommand();
dropCommand.CommandText = $"drop database if exists {testDatabaseInstanceName}";
output?.WriteLine("dropping db");
dropCommand.ExecuteNonQuery();
var createDbCOmmand = tempConn.CreateCommand();
createDbCOmmand.CommandText = $"create database {testDatabaseInstanceName}";
output?.WriteLine("creating db");
createDbCOmmand.ExecuteNonQuery();
output?.WriteLine($"changing db to {testDatabaseInstanceName}");
tempConn.ChangeDatabase(testDatabaseInstanceName);
var initDbCommand = tempConn.CreateCommand();
initDbCommand.CommandText = sql;
output?.WriteLine($"executing init script");
initDbCommand.ExecuteNonQuery();
}
/// <summary>
/// Clones an existing database connection by retaining all connection settings except that
/// the new connection will be opened to target a new database.
/// </summary>
/// <param name="connection">The existing connection.</param>
/// <param name="database">The target database for the new connection.</param>
/// <returns>The new <see cref="NpgsqlConnection"/>.</returns>
public static NpgsqlConnection OpenDatabase(this NpgsqlConnection connection, string database)
{
Covenant.Requires <ArgumentNullException>(!string.IsNullOrEmpty(database), nameof(database));
var targetConnection = connection.CloneWith($"host={connection.Host};port={connection.Port};database={database};user id={connection.UserName}");
targetConnection.Open();
return(targetConnection);
}
/// <summary>
/// Asynchronously clones an existing database connection by retaining all connection settings except
/// that the connection will be opened to target a new database.
/// </summary>
/// <param name="connection">The existing connection.</param>
/// <param name="database">The target database for the new connection.</param>
/// <returns>The new <see cref="NpgsqlConnection"/>.</returns>
public static async Task <NpgsqlConnection> OpenDatabaseAsync(this NpgsqlConnection connection, string database)
{
await SyncContext.Clear;
Covenant.Requires <ArgumentNullException>(!string.IsNullOrEmpty(database), nameof(database));
var targetConnection = connection.CloneWith($"host={connection.Host};port={connection.Port};database={database};user id={connection.UserName}");
await targetConnection.OpenAsync();
return(targetConnection);
}
public void Prepare(string payload, NpgsqlConnection conn)
{
Conn = conn.CloneWith(conn.ConnectionString);
this.Payload = JsonConvert.DeserializeObject(payload);
this.TableName = Payload.table;
this.ExaminationTemplateData = Payload.new_data;
this.TemplateId = ExaminationTemplateData.id.Value.ToString();
this.TemplateName = ExaminationTemplateData.description.ToString();
this.ExaminationTemplateMobileData = Payload.examination_template_mobile_items;
this.ResponsesSets = Payload.responses_set;
this.Template = new Templates(TemplateId, TemplateName, ExaminationTemplateMobileData, ResponsesSets);
}
private void VerificaFechamento(String vendedor, String dataFech)
{
var conne = new NpgsqlConnection(ConfigurationManager.AppSettings["ConexaoFch"]);
conne.Open();
String sql = $"select fech_data_fechamento, fech_vend_codigo from fechamento where fech_data_fechamento = '{dataFech}' and fech_vend_codigo = '{vendedor}' and fech_status = 'N'";
var cmd = new NpgsqlCommand(sql, conne);
//cmd.Parameters.AddWithValue("fech_data_fechamento", NpgsqlTypes.NpgsqlDbType.Date, dataFech);
var dr = cmd.ExecuteReader();
if (dr.Read())
{
DateTime comparaData = dr.GetSafeValue <DateTime>("fech_data_fechamento");
String comparaVendedor = dr.GetSafeValue <String>("fech_vend_codigo");
if (comparaData.ToString("yyyy-MM-dd") == dataFech && comparaVendedor == vendedor)
{
var dialogResult = MessageBox.Show($"Detectamos que o fechamento do vendedor {vendedor} da data {dataFech} já foi importado, deseja cancelar o movimento e reimportar?", "Opções: ", MessageBoxButtons.YesNo, MessageBoxIcon.Warning);
if (dialogResult == DialogResult.Yes)
{
//var transaction = conne.BeginTransaction();
var newConne = conne.CloneWith(conne.ConnectionString);
try
{
sql = $" update fechamento set fech_status = 'C' where fech_data_fechamento = '{dataFech}' " +
$" and fech_vend_codigo = '{vendedor}'";
newConne.Open();
cmd = new NpgsqlCommand(sql, newConne);
dr = cmd.ExecuteReader();
dr.Read();
dr.Close();
newConne.Close();
//int linhasInseridas = cmd.ExecuteNonQuery();
//transaction.Commit();
}
catch (Exception e)
{
MessageBox.Show("Erro inesperado: " + e.Message.ToString(), "ERROR", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
else
{
return;
}
conne.Close();
}
}
}
