private static void InsertTestData(string connectionString)
{
using (var connection = new FbConnection(connectionString))
{
connection.Open();
var commandText = new StringBuilder();
commandText.Append("insert into test (int_field, char_field, varchar_field, bigint_field, smallint_field, float_field, double_field, numeric_field, date_field, time_field, timestamp_field, clob_field, blob_field)");
commandText.Append(" values(@int_field, @char_field, @varchar_field, @bigint_field, @smallint_field, @float_field, @double_field, @numeric_field, @date_field, @time_field, @timestamp_field, @clob_field, @blob_field)");
using (var transaction = connection.BeginTransaction())
{
using (var command = new FbCommand(commandText.ToString(), connection, transaction))
{
// Add command parameters
command.Parameters.Add("@int_field", FbDbType.Integer);
command.Parameters.Add("@char_field", FbDbType.Char);
command.Parameters.Add("@varchar_field", FbDbType.VarChar);
command.Parameters.Add("@bigint_field", FbDbType.BigInt);
command.Parameters.Add("@smallint_field", FbDbType.SmallInt);
command.Parameters.Add("@float_field", FbDbType.Double);
command.Parameters.Add("@double_field", FbDbType.Double);
command.Parameters.Add("@numeric_field", FbDbType.Numeric);
command.Parameters.Add("@date_field", FbDbType.Date);
command.Parameters.Add("@time_Field", FbDbType.Time);
command.Parameters.Add("@timestamp_field", FbDbType.TimeStamp);
command.Parameters.Add("@clob_field", FbDbType.Text);
command.Parameters.Add("@blob_field", FbDbType.Binary);
command.Prepare();
for (int i = 0; i < 100; i++)
{
command.Parameters["@int_field"].Value = i;
command.Parameters["@char_field"].Value = "IRow " + i.ToString();
command.Parameters["@varchar_field"].Value = "IRow Number " + i.ToString();
command.Parameters["@bigint_field"].Value = i;
command.Parameters["@smallint_field"].Value = i;
command.Parameters["@float_field"].Value = (float)(i + 10) / 5;
command.Parameters["@double_field"].Value = Math.Log(i, 10);
command.Parameters["@numeric_field"].Value = (decimal)(i + 10) / 5;
command.Parameters["@date_field"].Value = DateTime.Now;
command.Parameters["@time_field"].Value = DateTime.Now;
command.Parameters["@timestamp_field"].Value = DateTime.Now;
command.Parameters["@clob_field"].Value = "IRow Number " + i.ToString();
command.Parameters["@blob_field"].Value = Encoding.Default.GetBytes("IRow Number " + i.ToString());
command.ExecuteNonQuery();
}
transaction.Commit();
}
}
}
}
/// <summary>Bind all parameters values to the specified query and execute the query.</summary>
/// <param name="transaction">The Firebird transaction</param>
/// <param name="query">The query.</param>
/// <param name="values">The values.</param>
public void BindParametersAndRunQuery(FbTransaction transaction, string query, object[] values)
{
using (FbCommand cmd = fbDBConnection.CreateCommand())
{
cmd.Transaction = transaction;
cmd.CommandText = query;
cmd.CommandType = CommandType.Text;
for (int i = 0; i < values.Length; i++)
{
if (Convert.IsDBNull(values[i]) || values[i] == null)
{
cmd.Parameters.Add("@" + ((i + 1).ToString()), FbDbType.Text).Value = string.Empty;
}
// Enums have an underlying type of Int32, but we want to store
// their string representation, not their integer value
else if (values[i].GetType().IsEnum)
{
cmd.Parameters.Add("@" + ((i + 1).ToString()), FbDbType.Text).Value = values[i].ToString();
}
else if (values[i].GetType() == typeof(DateTime))
{
DateTime d = (DateTime)values[i];
cmd.Parameters.Add("@" + ((i + 1).ToString()), FbDbType.Text).Value = d.ToString("dd.MM.yyyy, hh:mm:ss.000");
}
else if (values[i].GetType() == typeof(int))
{
int integer = (int)values[i];
cmd.Parameters.Add("@" + ((i + 1).ToString()), FbDbType.Integer).Value = integer;
}
else if (values[i].GetType() == typeof(float))
{
float f = (float)values[i];
cmd.Parameters.Add("@" + ((i + 1).ToString()), FbDbType.Float).Value = f;
}
else if (values[i].GetType() == typeof(double))
{
double d = (double)values[i];
cmd.Parameters.Add("@" + ((i + 1).ToString()), FbDbType.Double).Value = d;
}
else if (values[i].GetType() == typeof(byte[]))
{
byte[] bytes = values[i] as byte[];
cmd.Parameters.Add("@" + ((i + 1).ToString()), FbDbType.Binary).Value = bytes;
}
else
{
cmd.Parameters.Add("@" + ((i + 1).ToString()), FbDbType.Text).Value = values[i] as string;
}
}
cmd.Prepare();
cmd.ExecuteNonQuery();
cmd.Dispose();
}
}
public void ParameterDescribeTest()
{
var sql = "insert into TEST (int_field) values (@value)";
var command = new FbCommand(sql, Connection);
command.Prepare();
command.Parameters.Add("@value", FbDbType.Integer).Value = 100000;
command.ExecuteNonQuery();
command.Dispose();
}
public void PrepareTest()
{
// Insert data using a prepared statement
FbCommand command = new FbCommand("insert into PrepareTest(test_field) values(@test_field);", Connection);
command.Parameters.Add("@test_field", FbDbType.VarChar).Value = DBNull.Value;
command.Prepare();
for (int i = 0; i < 5; i++)
{
if (i < 1)
{
command.Parameters[0].Value = DBNull.Value;
}
else
{
command.Parameters[0].Value = i.ToString();
}
command.ExecuteNonQuery();
}
command.Dispose();
// Check that data is correct
FbCommand select = new FbCommand("select * from PrepareTest", Connection);
FbDataReader reader = select.ExecuteReader();
int count = 0;
while (reader.Read())
{
if (count == 0)
{
Assert.AreEqual(DBNull.Value, reader[0], "Invalid value.");
}
else
{
Assert.AreEqual(count, reader.GetInt32(0), "Invalid value.");
}
count++;
}
reader.Close();
select.Dispose();
}
public bool Copia(string novo, string parceiro, string codigo, string formula, string formula_pedido, float consultor, float vendedor, float filial, float limiar, string observacao, string racional, string servico, string ativo, short dias, string venpro, string venser, string conpro, string conser, string filpro, string filser, float frete,
string introducao, string fornecimento, string garantia, string condicao, string aceite, string imprime_ipi, ref string msg)
{
string sconsultor = consultor.ToString().Replace(',', '.');
string svendedor = vendedor.ToString().Replace(',', '.');
string sfilial = filial.ToString().Replace(',', '.');
string slimiar = limiar.ToString().Replace(',', '.');
string sfrete = frete.ToString().Replace(',', '.');
string sql = "insert into CARACTERISTICAS values(" +
"'" + parceiro + "'," +
"'" + novo + "'," +
"'" + formula + "'," +
sconsultor + "," +
svendedor + "," +
slimiar + "," +
"@observacao," +
"@racional," +
"'" + ativo + "'," +
"'" + formula_pedido + "'," +
"'" + servico + "'," +
dias + "," +
"'" + venpro + "'," +
"'" + venser + "'," +
"'" + conpro + "'," +
"'" + conser + "'," +
"'" + filpro + "'," +
"'" + filser + "'," +
sfilial + "," +
sfrete + "," +
"'" + introducao + "'," +
"'" + fornecimento + "'," +
"'" + garantia + "'," +
"'" + condicao + "'," +
"'" + aceite + "'," +
"'" + imprime_ipi + "'" +
")";
FbCommand cmd = new FbCommand(sql, Globais.bd);
try
{
cmd.Parameters.Add("@observacao", FbDbType.VarChar, 4000);
cmd.Parameters.Add("@racional", FbDbType.VarChar, 4000);
cmd.Parameters["@observacao"].Value = observacao;
cmd.Parameters["@racional"].Value = racional;
cmd.Prepare();
Log.Grava(Globais.sUsuario, cmd.CommandText);
cmd.ExecuteNonQuery();
}
catch (Exception err)
{
Log.Grava(Globais.sUsuario, "erro:" + err.Message);
msg = err.Message;
StreamWriter sw = new StreamWriter(new FileStream("c:\\soft\\soft.log", FileMode.Create));
sw.WriteLine(sql);
sw.WriteLine(msg);
sw.Close();
return(false);
}
sql = "select QTD_LIMIAR,PER_COMISSAO " +
"from COMISSAO_LIMIAR " +
"where cod_fornecedor='" + parceiro + "' and " +
" cod_caracteristica='" + codigo + "' " +
"order by QTD_LIMIAR";
cmd = new FbCommand(sql, Globais.bd);
FbDataReader reader = cmd.ExecuteReader(CommandBehavior.Default);
float comissao = 0;
short qtd_limiar = 0;
cComissaoLimiar comissao_limiar = new cComissaoLimiar();
while (reader.Read())
{
qtd_limiar = reader.GetInt16(0);
comissao = reader.GetFloat(1);
comissao_limiar.Inclui(parceiro, novo, qtd_limiar, comissao, ref msg);
}
reader.Close();
msg = "OK";
return(true);
}
public async Task BulkAddSystem(List <EddbSystem> systems)
{
if (systems == null)
{
Logger.Debug("Null system list passed to InjectSystemsToSql!");
return;
}
Logger.Info("Injecting " + systems.Count + " systems to SQL.");
Status = "Bulk inserting...";
FbConnection con2 = new FbConnection(
_fbConStr);
try
{
con2.Open();
}
catch (Exception ex)
{
Show(ex.ToString());
}
try
{
using (FbCommand insertSystem = con2.CreateCommand())
{
insertSystem.CommandText =
"INSERT INTO eddb_systems values (@id, @name, @x, @y, @z, @faction, @population, @government, @allegiance, @state, @security, @primary_economy, @power, @power_state, @needs_permit, @updated_at, @simbad_ref, @lowercase_name)";
insertSystem.Parameters.Clear();
insertSystem.Parameters.Add("@id", FbDbType.Integer);
insertSystem.Parameters.Add("@name", FbDbType.VarChar, 150);
insertSystem.Parameters.Add("@x", FbDbType.Double);
insertSystem.Parameters.Add("@y", FbDbType.Double);
insertSystem.Parameters.Add("@z", FbDbType.Double);
insertSystem.Parameters.Add("@faction", FbDbType.VarChar, 150);
insertSystem.Parameters.Add("@population", FbDbType.BigInt);
insertSystem.Parameters.Add("@government", FbDbType.VarChar, 130);
insertSystem.Parameters.Add("@allegiance", FbDbType.VarChar, 130);
insertSystem.Parameters.Add("@state", FbDbType.VarChar, 130);
insertSystem.Parameters.Add("@security", FbDbType.VarChar, 150);
insertSystem.Parameters.Add("@primary_economy", FbDbType.VarChar, 130);
insertSystem.Parameters.Add("@power", FbDbType.VarChar, 130);
insertSystem.Parameters.Add("@power_state", FbDbType.VarChar, 130);
insertSystem.Parameters.Add("@needs_permit", FbDbType.Integer);
insertSystem.Parameters.Add("@updated_at", FbDbType.BigInt);
insertSystem.Parameters.Add("@simbad_ref", FbDbType.VarChar, 150);
insertSystem.Parameters.Add("@lowercase_name", FbDbType.VarChar, 150);
FbTransaction tx = insertSystem.Connection.BeginTransaction();
insertSystem.Transaction = tx;
insertSystem.Prepare();
int i = 0;
foreach (EddbSystem system in systems)
{
if (_eddbworker != null)
{
_eddbworker.SystemCounter++;
}
insertSystem.Parameters["@id"].Value = system.id;
insertSystem.Parameters["@name"].Value = system.name;
insertSystem.Parameters["@x"].Value = system.x;
insertSystem.Parameters["@y"].Value = system.y;
insertSystem.Parameters["@z"].Value = system.z;
insertSystem.Parameters["@faction"].Value = system.faction;
insertSystem.Parameters["@population"].Value = system.population;
insertSystem.Parameters["@government"].Value = system.government;
insertSystem.Parameters["@allegiance"].Value = system.allegiance;
insertSystem.Parameters["@state"].Value = system.state;
insertSystem.Parameters["@security"].Value = system.security;
insertSystem.Parameters["@primary_economy"].Value = system.primary_economy;
insertSystem.Parameters["@power"].Value = system.power;
insertSystem.Parameters["@power_state"].Value = system.power_state;
insertSystem.Parameters["@needs_permit"].Value = system.needs_permit;
insertSystem.Parameters["@updated_at"].Value = system.updated_at;
insertSystem.Parameters["@simbad_ref"].Value = system.simbad_ref;
insertSystem.Parameters["@lowercase_name"].Value = system.name.ToLower();
await insertSystem.ExecuteNonQueryAsync();
i++;
if (i % 10000 == 0)
{
tx.Commit();
tx = insertSystem.Connection.BeginTransaction();
insertSystem.Transaction = tx;
}
}
Status = "Committing transaction...";
tx.Commit();
con2.Close();
}
Logger.Info("Completed injection.");
Status = "Ready!";
}
catch (Exception ex)
{
Logger.Debug("Exception in InjectSystemsToSql: " + ex.Message + "@" + ex.Source);
}
}
private static void InsertTestData(string connectionString)
{
FbConnection connection = new FbConnection(connectionString);
connection.Open();
StringBuilder commandText = new StringBuilder();
commandText.Append("insert into test (int_field, char_field, varchar_field, bigint_field, smallint_field, float_field, double_field, numeric_field, date_field, time_field, timestamp_field, clob_field, blob_field)");
commandText.Append(" values(@int_field, @char_field, @varchar_field, @bigint_field, @smallint_field, @float_field, @double_field, @numeric_field, @date_field, @time_field, @timestamp_field, @clob_field, @blob_field)");
FbTransaction transaction = connection.BeginTransaction();
FbCommand command = new FbCommand(commandText.ToString(), connection, transaction);
try
{
// Add command parameters
command.Parameters.Add("@int_field" , FbDbType.Integer);
command.Parameters.Add("@char_field" , FbDbType.Char);
command.Parameters.Add("@varchar_field" , FbDbType.VarChar);
command.Parameters.Add("@bigint_field" , FbDbType.BigInt);
command.Parameters.Add("@smallint_field" , FbDbType.SmallInt);
command.Parameters.Add("@float_field" , FbDbType.Double);
command.Parameters.Add("@double_field" , FbDbType.Double);
command.Parameters.Add("@numeric_field" , FbDbType.Numeric);
command.Parameters.Add("@date_field" , FbDbType.Date);
command.Parameters.Add("@time_Field" , FbDbType.Time);
command.Parameters.Add("@timestamp_field" , FbDbType.TimeStamp);
command.Parameters.Add("@clob_field" , FbDbType.Text);
command.Parameters.Add("@blob_field" , FbDbType.Binary);
command.Prepare();
for (int i = 0; i < 100; i++)
{
command.Parameters["@int_field"].Value = i;
command.Parameters["@char_field"].Value = "IRow " + i.ToString();
command.Parameters["@varchar_field"].Value = "IRow Number " + i.ToString();
command.Parameters["@bigint_field"].Value = i;
command.Parameters["@smallint_field"].Value = i;
command.Parameters["@float_field"].Value = (float)(i + 10)/5;
command.Parameters["@double_field"].Value = Math.Log(i, 10);
command.Parameters["@numeric_field"].Value = (decimal)(i + 10)/5;
command.Parameters["@date_field"].Value = DateTime.Now;
command.Parameters["@time_field"].Value = DateTime.Now;
command.Parameters["@timestamp_field"].Value= DateTime.Now;
command.Parameters["@clob_field"].Value = "IRow Number " + i.ToString();
command.Parameters["@blob_field"].Value = Encoding.Default.GetBytes("IRow Number " + i.ToString());
command.ExecuteNonQuery();
}
// Commit transaction
transaction.Commit();
}
catch (FbException)
{
transaction.Rollback();
throw;
}
finally
{
command.Dispose();
connection.Close();
}
}
public override DataTable GetSchemaTable()
{
CheckState();
if (_schemaTable != null)
{
return(_schemaTable);
}
DataRow schemaRow = null;
var tableCount = 0;
var currentTable = string.Empty;
_schemaTable = GetSchemaTableStructure();
/* Prepare statement for schema fields information */
var schemaCmd = new FbCommand(GetSchemaCommandText(), _command.Connection, _command.Connection.InnerConnection.ActiveTransaction);
try
{
schemaCmd.Parameters.Add("@TABLE_NAME", FbDbType.Char, 31);
schemaCmd.Parameters.Add("@COLUMN_NAME", FbDbType.Char, 31);
schemaCmd.Prepare();
_schemaTable.BeginLoadData();
for (var i = 0; i < _fields.Count; i++)
{
var isKeyColumn = false;
var isUnique = false;
var isReadOnly = false;
var precision = 0;
var isExpression = false;
/* Get Schema data for the field */
schemaCmd.Parameters[0].Value = _fields[i].Relation;
schemaCmd.Parameters[1].Value = _fields[i].Name;
var reader = schemaCmd.ExecuteReader(CommandBehavior.Default);
try
{
if (reader.Read())
{
isReadOnly = (IsReadOnly(reader) || IsExpression(reader)) ? true : false;
isKeyColumn = (reader.GetInt32(2) == 1) ? true : false;
isUnique = (reader.GetInt32(3) == 1) ? true : false;
precision = reader.IsDBNull(4) ? -1 : reader.GetInt32(4);
isExpression = IsExpression(reader);
}
}
finally
{
#if NET48 || NETSTANDARD2_0
reader.Dispose();
#else
reader.Dispose();
#endif
}
/* Create new row for the Schema Table */
schemaRow = _schemaTable.NewRow();
schemaRow["ColumnName"] = GetName(i);
schemaRow["ColumnOrdinal"] = i;
schemaRow["ColumnSize"] = _fields[i].GetSize();
if (_fields[i].IsDecimal())
{
schemaRow["NumericPrecision"] = schemaRow["ColumnSize"];
if (precision > 0)
{
schemaRow["NumericPrecision"] = precision;
}
schemaRow["NumericScale"] = _fields[i].NumericScale * (-1);
}
schemaRow["DataType"] = GetFieldType(i);
schemaRow["ProviderType"] = GetProviderType(i);
schemaRow["IsLong"] = _fields[i].IsLong();
schemaRow["AllowDBNull"] = _fields[i].AllowDBNull();
schemaRow["IsRowVersion"] = false;
schemaRow["IsAutoIncrement"] = false;
schemaRow["IsReadOnly"] = isReadOnly;
schemaRow["IsKey"] = isKeyColumn;
schemaRow["IsUnique"] = isUnique;
schemaRow["IsAliased"] = _fields[i].IsAliased();
schemaRow["IsExpression"] = isExpression;
schemaRow["BaseSchemaName"] = DBNull.Value;
schemaRow["BaseCatalogName"] = DBNull.Value;
schemaRow["BaseTableName"] = _fields[i].Relation;
schemaRow["BaseColumnName"] = _fields[i].Name;
_schemaTable.Rows.Add(schemaRow);
if (!string.IsNullOrEmpty(_fields[i].Relation) && currentTable != _fields[i].Relation)
{
tableCount++;
currentTable = _fields[i].Relation;
}
schemaCmd.Close();
}
if (tableCount > 1)
{
foreach (DataRow row in _schemaTable.Rows)
{
row["IsKey"] = false;
row["IsUnique"] = false;
}
}
_schemaTable.EndLoadData();
}
finally
{
#if NET48 || NETSTANDARD2_0
schemaCmd.Dispose();
#else
schemaCmd.Dispose();
#endif
}
return(_schemaTable);
}
