protected override Movement GetModel(FirebirdSql.Data.FirebirdClient.FbDataReader reader)
{
return(new Movement
{
ShopCode = reader.GetString(0),
MovementNumber = reader.GetInt32(1),
MovementType = reader.GetString(2),
MovementDescription = reader.GetString(3),
ArticleCode = reader.GetString(4),
ColorCode = reader.GetString(5),
SizeCode = reader.GetString(6),
MovementDirection = reader.GetString(7),
MovementTime = reader.GetDateTime(9),
MovementDate = reader.GetDateTime(8),
SalesPerson = reader.GetString(10),
CostPrice = reader.Get <Decimal?>(11),
UnitPrice = reader.Get <Decimal?>(12),
DocumentLine = reader.GetString(13),
DocumentNumber = reader.GetString(14),
LocationFrom = reader.GetString(15),
LocationTo = reader.GetString(16),
Quantity = reader.GetInt32(17),
TransactionNumber = reader.GetString(18),
LotNumber = reader.GetString(19)
});
}
protected override Customer GetModel(FirebirdSql.Data.FirebirdClient.FbDataReader reader)
{
return(new Customer
{
ShopCode = reader.GetString(0),
Client = reader.GetInt32(1),
Name = reader.GetString(2),
FirstName = reader.GetString(3),
Address1 = reader.GetString(4),
Address2 = reader.GetString(5),
Address3 = reader.GetString(6),
Email = reader.GetString(7),
PostCode = reader.GetString(8),
City = reader.GetString(9),
TelephonePersonal = reader.GetString(10),
TelephoneBusiness = reader.GetString(11),
BirthDay = reader.GetDateTime(12),
CreditCard = reader.GetString(13),
TotalAmountSpend = reader.GetDecimal(14),
FirstBuy = reader.GetDateTime(15),
LastBuy = reader.GetDateTime(16),
CreationTime = reader.GetDateTime(17),
LastModificationTime = reader.GetDateTime(18)
});
}
protected override Order GetModel(FirebirdSql.Data.FirebirdClient.FbDataReader reader)
{
return(new Order
{
OrderDate = reader.GetDateTime(0),
OrderTime = reader.GetDateTime(1),
SaleType = reader.GetString(2),
Ticket = reader.GetString(3),
Line = reader.GetInt32(4),
ArticleCode = reader.GetString(5),
ColorCode = reader.GetString(6),
SizeCode = reader.GetString(7),
Quantity = reader.GetInt32(8),
PurchasePrice = reader.GetDecimal(9),
SalePrice = reader.GetDecimal(10),
BrutoPrice = reader.GetDecimal(11),
VAT = reader.GetDecimal(12),
NettoPrice = reader.GetDecimal(13),
DiscountPercentage = reader.GetDecimal(14),
DiscountValue = reader.GetDecimal(15),
ShopCode = reader.GetString(16),
SalesPerson = reader.GetString(17),
Client = reader.GetString(18)
});
}
public void firebird()
{
//var connector = DataSources.Get("Results");
// List list = new List<KeyValuePair<string, string>>();
string con_string =
"User=SYSDBA;" +
"Password=masterkey;" +
"Database=C:\\Program Files (x86)\\Firebird\\Firebird_3_0\\examples\\empbuild\\employee.FDB;" +
"DataSource=localhost;" +
"Port=3050;" +
// "Dialect=3;" +
"Charset=NONE;" +
"Role=";
/*
* "Connection lifetime=15;" +
* "Pooling=true;" +
* "MinPoolSize=0;" +
* "MaxPoolSize=50;" +
* "Packet Size=8192;" +
* "ServerType=0";
*/
FirebirdSql.Data.FirebirdClient.FbConnection con = new FirebirdSql.Data.FirebirdClient.FbConnection(con_string);
con.Open();
Ranorex.Report.Info(con.ServerVersion.ToString());
string query = "SELECT a.COUNTRY, a.CURRENCY FROM COUNTRY a";
FirebirdSql.Data.FirebirdClient.FbCommand command = new FirebirdSql.Data.FirebirdClient.FbCommand(query, con);
FirebirdSql.Data.FirebirdClient.FbDataReader reader = command.ExecuteReader();
DataTable datatable = new DataTable();
datatable.Load(reader);
con.Close();
int rows = datatable.Rows.Count;
if (rows > 0)
{
for (int i = 0; i < rows; i++)
{
string[] stringData = Array.ConvertAll <object, string>(datatable.Rows[i].ItemArray.ToArray(), o => o.ToString());
Ranorex.Report.Info("Country: " + datatable.Rows[i][0] + " Currency: " + datatable.Rows[i][1]);
//list.add(new KeyValuePair<string, int>("A", 1));
}
}
}
protected override Statistic GetModel(FirebirdSql.Data.FirebirdClient.FbDataReader reader)
{
return new Statistic
{
Atmosphere = reader.GetString(0),
Context = reader.GetString(1),
DateTime = reader.GetDateTime(2),
ShopCode = reader.GetString(3),
TotalSales = reader.GetDecimal(4),
ClientSales = reader.GetDecimal(5),
GeneralSales = reader.GetDecimal(6),
UnitsSold = reader.GetDecimal(7),
UnitsPerClient = reader.GetDecimal(8),
TotalAmount = reader.GetDecimal(9),
SalesPerClient = reader.GetDecimal(10),
VisitorCount = reader.Get<Int32?>(11),
VisitorsWithNoSales = reader.Get<Int32?>(12)
};
}
protected override Stock GetModel(FirebirdSql.Data.FirebirdClient.FbDataReader reader)
{
return(new Stock
{
DateTime = reader.GetDateTime(0),
ShopCode = reader.GetString(1),
ArticleCode = reader.GetString(2),
ColorCode = reader.GetString(3),
SizeCode = reader.GetString(4),
InStock = reader.GetInt32(5),
Maximum = reader.GetInt32(6),
Minimum = reader.GetInt32(7),
Reserved = reader.GetInt32(8),
Ordered = reader.GetInt32(9),
Delivered = reader.GetInt32(10),
Available = reader.GetInt32(11),
TotalIn = reader.GetInt32(12),
TotalOut = reader.GetInt32(13),
WAC = reader.Get <Decimal?>(14)
});
}
protected override Item GetModel(FirebirdSql.Data.FirebirdClient.FbDataReader reader)
{
return(new Item
{
DateTime = reader.GetDateTime(0),
ArticleCode = reader.GetString(1),
ColorCode = reader.GetString(2),
ColorName = reader.GetString(3),
SizeCode = reader.GetString(4),
Barcode = reader.GetString(5),
DescriptionLong = reader.GetString(6),
DescriptionShort = reader.GetString(7),
Price = reader.GetDecimal(8),
OriginCode = reader.GetString(9),
OriginName = reader.GetString(10),
LabelCode = reader.GetString(11),
LabelName = reader.GetString(12),
SegmentCode = reader.GetString(13),
SegmentName = reader.GetString(14),
GroupCode = reader.GetString(15),
GroupName = reader.GetString(16)
});
}
/// <summary>
/// Transfere dados do banco de dados atual para um banco de dados de destino.
/// Conexão com o banco de destino precisa estar aberta.
/// </summary>
/// <returns>Número de linhas transferidas.</returns>
/// <param name="p_query">Consulta SQL para buscar os dados no banco atual.</param>
/// <param name="p_insert">Comando de inserção para inserir cada linha no banco de destino.</param>
/// <param name="p_destdatabase">Conexão com o banco de destino.</param>
public override uint Transfer(string p_query, Spartacus.Database.Command p_insert, Spartacus.Database.Generic p_destdatabase)
{
uint v_transfered = 0;
if (this.v_con == null)
{
try
{
this.v_con = new FirebirdSql.Data.FirebirdClient.FbConnection(this.v_connectionstring);
this.v_con.Open();
this.v_cmd = new FirebirdSql.Data.FirebirdClient.FbCommand(p_query, this.v_con);
this.v_reader = this.v_cmd.ExecuteReader();
while (v_reader.Read())
{
for (int i = 0; i < v_reader.FieldCount; i++)
p_insert.SetValue(this.FixColumnName(v_reader.GetName(i)).ToLower(), v_reader[i].ToString());
p_destdatabase.Execute(p_insert.GetUpdatedText());
v_transfered++;
}
return v_transfered;
}
catch (FirebirdSql.Data.FirebirdClient.FbException e)
{
throw new Spartacus.Database.Exception(e);
}
finally
{
if (this.v_reader != null)
{
this.v_reader.Close();
this.v_reader = null;
}
if (this.v_cmd != null)
{
this.v_cmd.Dispose();
this.v_cmd = null;
}
if (this.v_con != null)
{
this.v_con.Close();
this.v_con = null;
}
}
}
else
{
try
{
this.v_cmd.CommandText = p_query;
this.v_reader = this.v_cmd.ExecuteReader();
while (v_reader.Read())
{
for (int i = 0; i < v_reader.FieldCount; i++)
p_insert.SetValue(this.FixColumnName(v_reader.GetName(i)).ToLower(), v_reader[i].ToString());
p_destdatabase.Execute(p_insert.GetUpdatedText());
v_transfered++;
}
return v_transfered;
}
catch (FirebirdSql.Data.FirebirdClient.FbException e)
{
throw new Spartacus.Database.Exception(e);
}
finally
{
if (this.v_reader != null)
{
this.v_reader.Close();
this.v_reader = null;
}
}
}
}
/// <summary>
/// Inicializa uma nova instancia da classe <see cref="Spartacus.Database.Firebird"/>.
/// </summary>
/// <param name='p_source'>
/// IP do servidor Firebird.
/// </param>
/// <param name='p_port'>
/// Porta de conexão.
/// </param>
/// <param name='p_file'>
/// Caminho completo para o arquivo FDB ou GDB.
/// </param>
/// <param name='p_user'>
/// Usuário do Firebird.
/// </param>
/// <param name='p_password'>
/// Senha do Firebird.
/// </param>
public Firebird(string p_source, string p_port, string p_file, string p_user, string p_password)
: base(p_source, p_port, p_file, p_user, p_password)
{
this.v_connectionstring = "DataSource=" + this.v_host + ";"
+ "Port=" + this.v_port + ";"
+ "Database=" + this.v_service + ";"
+ "User="******";"
+ "Password="******";"
+ "Dialect=3;Charset=NONE;Role=;";
this.v_con = null;
this.v_cmd = null;
this.v_reader = null;
}
/// <summary>
/// Realiza uma consulta no banco de dados, armazenando os dados de retorno em uma string HTML.
/// </summary>
/// <param name='p_sql'>
/// Código SQL a ser consultado no banco de dados.
/// </param>
/// <param name='p_id'>
/// ID da tabela no HTML.
/// </param>
/// <param name='p_options'>
/// Opções da tabela no HTML.
/// </param>
public override string QueryHtml(string p_sql, string p_id, string p_options)
{
string v_html;
if (this.v_con == null)
{
try
{
this.v_con = new FirebirdSql.Data.FirebirdClient.FbConnection(this.v_connectionstring);
this.v_con.Open();
this.v_cmd = new FirebirdSql.Data.FirebirdClient.FbCommand(p_sql, this.v_con);
this.v_reader = this.v_cmd.ExecuteReader();
v_html = "<table id='" + p_id + "' " + p_options + "><thead><tr>";
for (int i = 0; i < v_reader.FieldCount; i++)
v_html += "<th>" + this.FixColumnName(this.v_reader.GetName(i)) + "</th>";
v_html += "</tr></thead><tbody>";
while (this.v_reader.Read())
{
v_html += "<tr>";
for (int i = 0; i < this.v_reader.FieldCount; i++)
v_html += "<td>" + this.v_reader[i].ToString() + "</td>";
v_html += "</tr>";
}
v_html += "</tbody></table>";
return v_html;
}
catch (FirebirdSql.Data.FirebirdClient.FbException e)
{
throw new Spartacus.Database.Exception(e);
}
finally
{
if (this.v_reader != null)
{
this.v_reader.Close();
this.v_reader = null;
}
if (this.v_cmd != null)
{
this.v_cmd.Dispose();
this.v_cmd = null;
}
if (this.v_con != null)
{
this.v_con.Close();
this.v_con = null;
}
}
}
else
{
try
{
this.v_cmd.CommandText = p_sql;
this.v_reader = this.v_cmd.ExecuteReader();
v_html = "<table id='" + p_id + "' " + p_options + "><thead><tr>";
for (int i = 0; i < v_reader.FieldCount; i++)
v_html += "<th>" + this.FixColumnName(this.v_reader.GetName(i)) + "</th>";
v_html += "</tr></thead><tbody>";
while (this.v_reader.Read())
{
v_html += "<tr>";
for (int i = 0; i < this.v_reader.FieldCount; i++)
v_html += "<td>" + this.v_reader[i].ToString() + "</td>";
v_html += "</tr>";
}
v_html += "</tbody></table>";
return v_html;
}
catch (FirebirdSql.Data.FirebirdClient.FbException e)
{
throw new Spartacus.Database.Exception(e);
}
finally
{
if (this.v_reader != null)
{
this.v_reader.Close();
this.v_reader = null;
}
}
}
}
/// <summary>
/// Realiza uma consulta no banco de dados, armazenando os dados de retorno em um <see creg="System.Data.DataTable"/>.
/// Utiliza um DataReader para buscar em blocos. Conexão com o banco precisa estar aberta.
/// </summary>
/// <param name='p_sql'>
/// Código SQL a ser consultado no banco de dados.
/// </param>
/// <param name='p_tablename'>
/// Nome virtual da tabela onde deve ser armazenado o resultado, para fins de cache.
/// </param>
/// <param name='p_startrow'>
/// Número da linha inicial.
/// </param>
/// <param name='p_endrow'>
/// Número da linha final.
/// </param>
/// <param name='p_hasmoredata'>
/// Indica se ainda há mais dados a serem lidos.
/// </param>
public override System.Data.DataTable Query(string p_sql, string p_tablename, uint p_startrow, uint p_endrow, out bool p_hasmoredata)
{
System.Data.DataTable v_table = null;
System.Data.DataRow v_row;
try
{
if (this.v_reader == null)
{
this.v_cmd.CommandText = p_sql;
this.v_reader = this.v_cmd.ExecuteReader();
this.v_currentrow = 0;
}
v_table = new System.Data.DataTable(p_tablename);
for (int i = 0; i < v_reader.FieldCount; i++)
v_table.Columns.Add(this.FixColumnName(this.v_reader.GetName(i)), typeof(string));
p_hasmoredata = false;
while (this.v_reader.Read())
{
p_hasmoredata = true;
if (this.v_currentrow >= p_startrow && this.v_currentrow <= p_endrow)
{
v_row = v_table.NewRow();
for (int i = 0; i < this.v_reader.FieldCount; i++)
v_row[i] = this.v_reader[i].ToString();
v_table.Rows.Add(v_row);
}
this.v_currentrow++;
if (this.v_currentrow > p_endrow)
break;
}
if (! p_hasmoredata)
{
this.v_reader.Close();
this.v_reader = null;
}
return v_table;
}
catch (FirebirdSql.Data.FirebirdClient.FbException e)
{
throw new Spartacus.Database.Exception(e);
}
}
/// <summary>
/// Realiza uma consulta no banco de dados, armazenando os dados de retorno em um <see cref="System.Data.DataTable"/>.
/// </summary>
/// <param name='p_sql'>
/// Código SQL a ser consultado no banco de dados.
/// </param>
/// <param name='p_tablename'>
/// Nome virtual da tabela onde deve ser armazenado o resultado, para fins de cache.
/// </param>
/// <param name='p_progress'>Evento de progresso da execução da consulta.</param>
/// <returns>Retorna uma <see cref="System.Data.DataTable"/> com os dados de retorno da consulta.</returns>
public override System.Data.DataTable Query(string p_sql, string p_tablename, Spartacus.Utils.ProgressEventClass p_progress)
{
System.Data.DataTable v_table = null;
System.Data.DataRow v_row;
uint v_counter = 0;
p_progress.FireEvent(v_counter);
if (this.v_con == null)
{
try
{
this.v_con = new FirebirdSql.Data.FirebirdClient.FbConnection(this.v_connectionstring);
this.v_con.Open();
this.v_cmd = new FirebirdSql.Data.FirebirdClient.FbCommand(p_sql, this.v_con);
this.v_reader = this.v_cmd.ExecuteReader();
v_table = new System.Data.DataTable(p_tablename);
for (int i = 0; i < v_reader.FieldCount; i++)
v_table.Columns.Add(this.FixColumnName(this.v_reader.GetName(i)), typeof(string));
while (this.v_reader.Read())
{
v_row = v_table.NewRow();
for (int i = 0; i < this.v_reader.FieldCount; i++)
v_row[i] = this.v_reader[i].ToString();
v_table.Rows.Add(v_row);
v_counter++;
p_progress.FireEvent(v_counter);
}
return v_table;
}
catch (FirebirdSql.Data.FirebirdClient.FbException e)
{
throw new Spartacus.Database.Exception(e);
}
finally
{
if (this.v_reader != null)
{
this.v_reader.Close();
this.v_reader = null;
}
if (this.v_cmd != null)
{
this.v_cmd.Dispose();
this.v_cmd = null;
}
if (this.v_con != null)
{
this.v_con.Close();
this.v_con = null;
}
}
}
else
{
try
{
this.v_cmd.CommandText = p_sql;
this.v_reader = this.v_cmd.ExecuteReader();
v_table = new System.Data.DataTable(p_tablename);
for (int i = 0; i < v_reader.FieldCount; i++)
v_table.Columns.Add(this.FixColumnName(this.v_reader.GetName(i)), typeof(string));
while (this.v_reader.Read())
{
v_row = v_table.NewRow();
for (int i = 0; i < this.v_reader.FieldCount; i++)
v_row[i] = this.v_reader[i].ToString();
v_table.Rows.Add(v_row);
v_counter++;
p_progress.FireEvent(v_counter);
}
return v_table;
}
catch (FirebirdSql.Data.FirebirdClient.FbException e)
{
throw new Spartacus.Database.Exception(e);
}
finally
{
if (this.v_reader != null)
{
this.v_reader.Close();
this.v_reader = null;
}
}
}
}
/// <summary>
/// Lista os nomes e tipos de colunas de uma determinada consulta.
/// </summary>
/// <returns>Matriz com os nomes e tipos de colunas.</returns>
/// <param name="p_sql">Consulta SQL.</param>
public override string[,] GetColumnNamesAndTypes(string p_sql)
{
string[,] v_matrix;
if (this.v_con == null)
{
try
{
this.v_con = new FirebirdSql.Data.FirebirdClient.FbConnection(this.v_connectionstring);
this.v_con.Open();
this.v_cmd = new FirebirdSql.Data.FirebirdClient.FbCommand(p_sql, this.v_con);
this.v_reader = this.v_cmd.ExecuteReader();
v_matrix = new string[v_reader.FieldCount, 2];
for (int i = 0; i < v_reader.FieldCount; i++)
{
v_matrix[i, 0] = this.FixColumnName(this.v_reader.GetName(i));
v_matrix[i, 1] = this.v_reader.GetDataTypeName(i);
}
return v_matrix;
}
catch (FirebirdSql.Data.FirebirdClient.FbException e)
{
throw new Spartacus.Database.Exception(e);
}
finally
{
if (this.v_reader != null)
{
this.v_reader.Close();
this.v_reader = null;
}
if (this.v_cmd != null)
{
this.v_cmd.Dispose();
this.v_cmd = null;
}
if (this.v_con != null)
{
this.v_con.Close();
this.v_con = null;
}
}
}
else
{
try
{
this.v_cmd.CommandText = p_sql;
this.v_reader = this.v_cmd.ExecuteReader();
v_matrix = new string[v_reader.FieldCount, 2];
for (int i = 0; i < v_reader.FieldCount; i++)
{
v_matrix[i, 0] = this.FixColumnName(this.v_reader.GetName(i));
v_matrix[i, 1] = this.v_reader.GetDataTypeName(i);
}
return v_matrix;
}
catch (FirebirdSql.Data.FirebirdClient.FbException e)
{
throw new Spartacus.Database.Exception(e);
}
finally
{
if (this.v_reader != null)
{
this.v_reader.Close();
this.v_reader = null;
}
}
}
}
/// <summary>
/// Transfere dados do banco de dados atual para um banco de dados de destino.
/// Conexão com o banco de destino precisa estar aberta.
/// </summary>
/// <returns>Número de linhas transferidas.</returns>
/// <param name="p_query">Consulta SQL para buscar os dados no banco atual.</param>
/// <param name="p_table">Nome da tabela de destino.</param>
/// <param name="p_insert">Comando de inserção para inserir cada linha no banco de destino.</param>
/// <param name="p_destdatabase">Conexão com o banco de destino.</param>
/// <param name="p_log">Log de inserção.</param>
/// <param name='p_startrow'>Número da linha inicial.</param>
/// <param name='p_endrow'>Número da linha final.</param>
/// <param name='p_hasmoredata'>Indica se ainda há mais dados a serem lidos.</param>
public override uint Transfer(string p_query, string p_table, Spartacus.Database.Command p_insert, Spartacus.Database.Generic p_destdatabase, ref string p_log, uint p_startrow, uint p_endrow, out bool p_hasmoredata)
{
uint v_transfered = 0;
System.Collections.ArrayList v_rows = new System.Collections.ArrayList();
try
{
if (this.v_reader == null)
{
this.v_cmd.CommandText = p_query;
this.v_reader = this.v_cmd.ExecuteReader();
this.v_currentrow = 0;
}
p_hasmoredata = false;
while (v_reader.Read())
{
p_hasmoredata = true;
if (this.v_currentrow >= p_startrow && this.v_currentrow <= p_endrow)
{
for (int i = 0; i < v_reader.FieldCount; i++)
p_insert.SetValue(this.FixColumnName(v_reader.GetName(i)).ToLower(), v_reader[i].ToString());
v_rows.Add(p_insert.GetUpdatedText());
v_transfered++;
}
this.v_currentrow++;
if (this.v_currentrow > p_endrow)
break;
}
if (! p_hasmoredata)
{
this.v_reader.Close();
this.v_reader = null;
}
else
{
try
{
p_destdatabase.InsertBlock(p_table, v_rows);
}
catch (Spartacus.Database.Exception e)
{
p_log += e.v_message + "\n";
}
}
return v_transfered;
}
catch (FirebirdSql.Data.FirebirdClient.FbException e)
{
throw new Spartacus.Database.Exception(e);
}
}
/// <summary>
/// Inicializa uma nova instancia da classe <see cref="Spartacus.Database.Firebird"/>.
/// </summary>
/// <param name='p_file'>
/// Caminho completo para o arquivo FDB ou GDB.
/// </param>
/// <param name='p_user'>
/// Usuário do Firebird.
/// </param>
/// <param name='p_password'>
/// Senha do Firebird.
/// </param>
public FirebirdEmbed(string p_file, string p_user, string p_password)
: base(p_file, p_user, p_password)
{
this.v_connectionstring = "ServerType=1;"
+ "Database=" + p_file + ";"
+ "User="******";"
+ "Password="******";"
+ "Dialect=3;Charset=NONE;Role=;";
this.v_con = null;
this.v_cmd = null;
this.v_reader = null;
}
