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; }