public void Issue178_Firebird()
{
const string cs = @"initial catalog=localhost:database;user id=SYSDBA;password=masterkey";
using (var connection = new FbConnection(cs))
{
connection.Open();
const string sql = @"select count(*) from Issue178";
try { connection.Execute("drop table Issue178"); }
catch { /* don't care */ }
connection.Execute("create table Issue178(id int not null)");
connection.Execute("insert into Issue178(id) values(42)");
// raw ADO.net
using (var sqlCmd = new FbCommand(sql, connection))
using (IDataReader reader1 = sqlCmd.ExecuteReader())
{
Assert.IsTrue(reader1.Read());
reader1.GetInt32(0).IsEqualTo(1);
Assert.IsFalse(reader1.Read());
Assert.IsFalse(reader1.NextResult());
}
// dapper
using (var reader2 = connection.ExecuteReader(sql))
{
Assert.IsTrue(reader2.Read());
reader2.GetInt32(0).IsEqualTo(1);
Assert.IsFalse(reader2.Read());
Assert.IsFalse(reader2.NextResult());
}
var count = connection.Query <int>(sql).Single();
count.IsEqualTo(1);
}
}
static FirebirdTestSuite()
{
Environment.SetEnvironmentVariable("NoCache", "True");
var init = false;
SqlMapper.AddTypeHandler <Guid>(new GuidTypeHandler());
//SqlMapper.AddTypeHandler<decimal>(new NumericTypeHandler());
//if (File.Exists(FileName))
//{
// File.Delete(FileName);
//}
var commandText = string.Empty;
try
{
var filename = Directory.GetCurrentDirectory() + "\\Test.Db.sfdb";
using (var connection = new FbConnection($"Database={filename};{ConnectionString}"))
{
connection.Open();
if (init)
{
var file = File.OpenText(".\\Scripts\\firebirdawlite.sql");
var line = string.Empty;
while ((line = file.ReadLine()) != null)
{
if (line.Equals("GO", StringComparison.OrdinalIgnoreCase))
{
if (!string.IsNullOrEmpty(commandText))
{
connection.Execute(commandText);
}
commandText = string.Empty;
}
else
{
commandText += "\r\n" + line;
}
}
}
connection.Execute("delete from Person");
}
}
catch (FbException ex)
{
if (ex.Message.Contains("Unable to complete network request"))
{
_skip = true;
}
else
{
throw;
}
}
}
static FirebirdTestSuite()
{
DbFile = Directory.GetCurrentDirectory() + DbName;
SqlDatabase.CacheQueries = false;
ResetDapperTypes();
SqlMapper.AddTypeHandler <Guid>(new GuidTypeHandler());
var init = false;
//if (File.Exists(DbFile))
//{
// File.Delete(DbFile);
//}
var commandText = string.Empty;
try
{
using (var connection = new FbConnection(ConnectionString))
{
connection.Open();
if (init)
{
using (var file = File.OpenText(".\\Scripts\\firebirdawlite.sql"))
{
var line = string.Empty;
while ((line = file.ReadLine()) != null)
{
if (line.Equals("GO", StringComparison.OrdinalIgnoreCase))
{
if (!string.IsNullOrEmpty(commandText))
{
connection.Execute(commandText);
}
commandText = string.Empty;
}
else
{
commandText += "\r\n" + line;
}
}
}
connection.Execute("delete from Person");
}
}
}
catch (FbException ex) when(ex.Message.Contains("Unable to complete network request"))
{
_skip = true;
}
}
private static void UpdateVersion(FbConnection connection, int version)
{
if (version == 1)
{
connection.Execute(string.Format(@"INSERT INTO ""HANGFIRE.SCHEMA"" (""VERSION"") VALUES ({0});", version));
}
else
{
connection.Execute(string.Format(@"UPDATE ""HANGFIRE.SCHEMA"" SET ""VERSION"" = {0};", version));
}
}
public int Excluir(string id)
{
using (var conexao = new FbConnection(_conexao))
{
return(conexao.Execute("DELETE FROM CLIENTE WHERE Id = @Id", new
{
Id = id
}));
}
}
public int Atualizar(CLIENTE cliente)
{
using (var conexao = new FbConnection(_conexao))
{
return(conexao.Execute("UPDATE CLIENTE SET NOME = @Nome WHERE ID = @Id", new
{
Id = cliente.ID,
Nome = cliente.NOME
}));
}
}
public int Persistir(CLIENTE cliente)
{
using (var conexao = new FbConnection(_conexao))
{
return(conexao.Execute("INSERT INTO CLIENTE VALUES (@Id, @Nome)", new
{
Id = cliente.ID,
Nome = cliente.NOME
}));
}
}
public string CreateExpiredJob(
Job job,
IDictionary <string, string> parameters,
DateTime createdAt,
TimeSpan expireIn)
{
if (job == null)
{
throw new ArgumentNullException("job");
}
if (parameters == null)
{
throw new ArgumentNullException("parameters");
}
string createJobSql = string.Format(@"INSERT INTO ""{0}.JOB"" (invocationdata, arguments, createdat, expireat)
VALUES (@invocationData, @arguments, @createdAt, @expireAt)
RETURNING id;", _options.Prefix);
var invocationData = InvocationData.Serialize(job);
var jobId = _connection.ExecuteScalar <int>(
createJobSql,
new
{
invocationData = JobHelper.ToJson(invocationData),
arguments = invocationData.Arguments,
createdAt = createdAt,
expireAt = createdAt.Add(expireIn)
}).ToString();
if (parameters.Count > 0)
{
var parameterArray = new object[parameters.Count];
int parameterIndex = 0;
foreach (var parameter in parameters)
{
parameterArray[parameterIndex++] = new
{
jobId = Convert.ToInt32(jobId, CultureInfo.InvariantCulture),
name = parameter.Key,
value = parameter.Value
};
}
string insertParameterSql = string.Format(@"INSERT INTO ""{0}.JOBPARAMETER"" (jobid, name, ""VALUE"")
VALUES (@jobId, @name, @value);", _options.Prefix);
_connection.Execute(insertParameterSql, parameterArray);
}
return(jobId);
}
public bool DeletarPaciente(int idPaciente)
{
FbConnection _conexao = ConexaoFirebird.getInstancia().getConexao();
try
{
_conexao.Open();
return(_conexao.Execute($@"DELETE FROM TB_PACIENTE
WHERE (ID_PACIENTE = {idPaciente})") > 0);
}
catch (Exception ex)
{
throw ex;
}
finally
{
_conexao.Close();
}
}
public bool InserirPaciente(Paciente paciente)
{
FbConnection _conexao = ConexaoFirebird.getInstancia().getConexao();
try
{
_conexao.Open();
return(_conexao.Execute($@"INSERT INTO TB_PACIENTE (NOME, IDADE, PESO, ALTURA)
VALUES ('{paciente.Nome}', {paciente.Idade}, {paciente.Peso}, {paciente.Altura}) ") > 0);
}
catch (Exception ex)
{
throw ex;
}
finally
{
_conexao.Close();
}
}
public bool EditarPaciente(Paciente paciente)
{
FbConnection _conexao = ConexaoFirebird.getInstancia().getConexao();
try
{
_conexao.Open();
return(_conexao.Execute($@"UPDATE TB_PACIENTE
SET NOME = '{paciente.Nome}',
IDADE = {paciente.Idade},
PESO = {paciente.Peso},
ALTURA = {paciente.Altura}
WHERE (ID_PACIENTE = {paciente.IdPaciente})") > 0);
}
catch (Exception ex)
{
throw ex;
}
finally
{
_conexao.Close();
}
}
static void Main(string[] args)
{
Console.WriteLine("Starting Against Test DB: " + args[0] + " " + args[1] + " " + args[2]);
Console.WriteLine("Enter to Start");
Console.ReadLine();
FbConnectionStringBuilder csb = new FbConnectionStringBuilder();
csb.DataSource = "localhost";
csb.Port = 3050;
csb.UserID = args[1];
csb.Password = args[2];
csb.Database = args[0];
csb.ServerType = FbServerType.Default;
Console.WriteLine("Dropping Database");
FbConnection.DropDatabase(csb.ToString());
Console.WriteLine("Creating Database");
FbConnection.CreateDatabase(csb.ToString());
FileInfo Schema = new FileInfo("schema.sql");
String DDL = Schema.OpenText().ReadToEnd();
Console.WriteLine("Applying Schema");
CreateDatabaseSchema(DDL, csb.ToString());
try
{
using (FbConnection db = new FbConnection(csb.ToString()))
{
db.Open();
List <int> loops = Enumerable.Range(1, 3).ToList();
foreach (int k in loops)
{
using (FbTransaction t = db.BeginTransaction())
{
try
{
List <int> words = Enumerable.Range(1, 1).ToList();
foreach (int i in words)
{
MemoDB m = new MemoDB();
m.MEMO = "TEST";
db.Insert(m, t);
}
if (k == 2)
{
Console.WriteLine("Simulating a Close");
db.Close();
}
else
{
t.Commit();
var count = db.Query <Int32>("select count(*) from memo").Single();
Console.WriteLine("Records Written: " + count);
}
}
catch (Exception ex)
{
t.Rollback();
WriteExceptionLog(ex);
}
}
}
}
}
catch (Exception ex)
{
WriteExceptionLog(ex);
}
Console.WriteLine("Stopped... Press Enter to Check for Validation.");
Console.ReadLine();
try
{
using (FbConnection db = new FbConnection(csb.ToString()))
{
db.Open();
Console.WriteLine("Counting");
var count = db.Query <Int32>("select count(*) from memo").Single();
Console.WriteLine("Records Written: " + count);
Console.WriteLine("Deleting");
db.Execute("delete from memo");
}
}
catch (Exception ex)
{
WriteExceptionLog(ex);
}
Console.WriteLine("Ending Application.");
Console.ReadLine();
}