public void RunScriptFile(string ScriptFile)
{
if (!System.IO.File.Exists(ScriptFile))
{
throw new Exception("File '" + ScriptFile + "' does not exist.");
}
System.IO.StreamReader sr = null;
try {
sr = new System.IO.StreamReader(ScriptFile, false);
using (FirebirdSql.Data.FirebirdClient.FbConnection conn = new FirebirdSql.Data.FirebirdClient.FbConnection(this.ConnStr)) {
conn.Open();
using (FirebirdSql.Data.FirebirdClient.FbCommand cmd = new FirebirdSql.Data.FirebirdClient.FbCommand()) {
cmd.Connection = conn;
cmd.CommandType = CommandType.Text;
cmd.CommandText = sr.ReadToEnd();
cmd.ExecuteNonQuery();
}
conn.Close();
}
} catch (Exception ex) {
throw ex;
} finally {
sr.Close();
sr.Dispose();
}
}
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));
}
}
}
public string GetAssuntoByNome(string assunto)
{
string assuntoID = "";
var conn = Persist.GetConn.getConn();
using (conn)
{
conn.Open();
string sql = "Select ID from WebAssunto where descricao = '" + assunto + "'";
var cmd = new FirebirdSql.Data.FirebirdClient.FbCommand(sql, conn);
assuntoID = cmd.ExecuteScalar().ToString();
}
return assuntoID;
}
public bool ehFinalDeFluxo(string idSituacao)
{
bool retorno = false;
string sql = "SELECT FIMFLUXO FROM WEBSITUACAOPROCESSO WHERE ID =" + idSituacao;
var conn = Persist.GetConn.getConn();
using (conn)
{
conn.Open();
var cmd = new FirebirdSql.Data.FirebirdClient.FbCommand(sql, conn);
if (!string.IsNullOrEmpty(cmd.ExecuteScalar().ToString()))
{
retorno = true;
}
}
return retorno;
}
public static string getIDbyNumeroProcesso(string procNumero)
{
string retorno = string.Empty;
string sql = "SELECT ID FROM webprocesso proc where proc.numero = " + procNumero;
FirebirdSql.Data.FirebirdClient.FbConnection conn = AcessoDados.AcessoDados.getConn();
FirebirdSql.Data.FirebirdClient.FbCommand comando = new FirebirdSql.Data.FirebirdClient.FbCommand(sql, conn);
conn.Open();
FirebirdSql.Data.FirebirdClient.FbDataReader dr = comando.ExecuteReader();
while (dr.Read())
{
retorno = dr["ID"].ToString();
}
conn.Close();
return retorno;
}
public virtual void PrepareListResponsaveis(ASPxGridViewHeaderFilterEventArgs e)
{
e.Values.Clear();
FirebirdSql.Data.FirebirdClient.FbConnection conn = AcessoDados.AcessoDados.getConn();
FirebirdSql.Data.FirebirdClient.FbCommand comando = new FirebirdSql.Data.FirebirdClient.FbCommand("Select * from sci_usuarios where usu_idundfuncional is not null", conn);
conn.Open();
FirebirdSql.Data.FirebirdClient.FbDataReader dr = comando.ExecuteReader();
while (dr.Read()) {
e.AddValue(dr["usu_nome"].ToString(), dr["usu_nome"].ToString());
}
conn.Close();
}
public virtual void PrepareListCentroCusto(ASPxGridViewHeaderFilterEventArgs e)
{
e.Values.Clear();
FirebirdSql.Data.FirebirdClient.FbConnection conn = AcessoDados.AcessoDados.getConn();
FirebirdSql.Data.FirebirdClient.FbCommand comando = new FirebirdSql.Data.FirebirdClient.FbCommand("Select cennome from centrocusto", conn);
conn.Open();
FirebirdSql.Data.FirebirdClient.FbDataReader dr = comando.ExecuteReader();
while (dr.Read())
{
e.AddValue(dr["cennome"].ToString(), dr["cennome"].ToString());
}
conn.Close();
}
public DataTable TestQuery(string Query)
{
using (FirebirdSql.Data.FirebirdClient.FbConnection conn = new FirebirdSql.Data.FirebirdClient.FbConnection(this.ConnStr)) {
conn.Open();
using (FirebirdSql.Data.FirebirdClient.FbCommand cmd = new FirebirdSql.Data.FirebirdClient.FbCommand()) {
cmd.Connection = conn;
cmd.CommandType = CommandType.Text;
cmd.CommandText = Query;
DataTable dt = new DataTable();
using (FirebirdSql.Data.FirebirdClient.FbDataAdapter da = new FirebirdSql.Data.FirebirdClient.FbDataAdapter(cmd)) {
da.Fill(dt);
}
conn.Close();
return(dt);
}
}
}
public bool AssuntoTemFluxo(string assuntoID)
{
int ordenacao = 0;
var conn = Persist.GetConn.getConn();
using (conn)
{
conn.Open();
string sql = "Select coalesce(exigeordenacao,0) from webassunto where id = " + assuntoID;
var cmd = new FirebirdSql.Data.FirebirdClient.FbCommand(sql, conn);
ordenacao = (int)cmd.ExecuteScalar();
}
if (ordenacao == 1)
{
return true;
}
else {
return false;
}
}
private static DbCommand CreaCommandNoConnection(string sql, DbParameter[] param)
{
DbCommand cm = null;
switch (DataBaseAttuale)
{
case DataBase.Access:
cm = new System.Data.OleDb.OleDbCommand(sql, (System.Data.OleDb.OleDbConnection)Connessione);
break;
case DataBase.SQLite:
cm = null; //new System.Data.SQLite.SQLiteCommand(sql, (System.Data.SQLite.SQLiteConnection)Connessione);
break;
case DataBase.FireBird:
cm = new FirebirdSql.Data.FirebirdClient.FbCommand(sql, (FirebirdSql.Data.FirebirdClient.FbConnection)Connessione);
break;
default:
throw new NotImplementedException();
}
if ((param != null))
{
for (int x = 0; x <= param.Length - 1; x++)
{
if (param[x].DbType == DbType.Decimal)
{
param[x].DbType = DbType.Currency;
}
cm.Parameters.Add(param[x]);
}
}
return(cm);
}
public List <SchemaRow> GetSchema()
{
if (!string.IsNullOrEmpty(this.ColumnSchemaQuery))
{
if (this.ColumnSchemaQuery.IndexOf(this.TableNamePlaceHolder) == -1)
{
throw new Exception("Required placeholder for table name: '" + this.TableNamePlaceHolder + "'.");
}
}
List <SchemaRow> Schema = new List <SchemaRow>();
using (FirebirdSql.Data.FirebirdClient.FbConnection conn = new FirebirdSql.Data.FirebirdClient.FbConnection(this.ConnStr)) {
conn.Open();
//Using single result set
if (!string.IsNullOrEmpty(this.SchemaQuery))
{
using (FirebirdSql.Data.FirebirdClient.FbCommand cmd = new FirebirdSql.Data.FirebirdClient.FbCommand()) {
cmd.Connection = conn;
cmd.CommandType = CommandType.Text;
cmd.CommandText = this.SchemaQuery;
DataTable dtSchema = new DataTable();
using (FirebirdSql.Data.FirebirdClient.FbDataAdapter da = new FirebirdSql.Data.FirebirdClient.FbDataAdapter(cmd)) {
da.Fill(dtSchema);
}
conn.Close();
foreach (DataRow dr in dtSchema.Rows)
{
Schema.Add(this.SetColumnAttributes(dr["TABLE_NAME"].ToString(), dr["TABLE_TYPE"].ToString(), dr));
}
return(Schema);
}
}
//Way of the Table/Column
//Retrieve table schema first
List <SchemaRow> TableSchema = new List <SchemaRow>();
if (!string.IsNullOrEmpty(this.TableSchemaQuery))
{
//Using table schema query
using (FirebirdSql.Data.FirebirdClient.FbCommand cmd = new FirebirdSql.Data.FirebirdClient.FbCommand()) {
cmd.Connection = conn;
cmd.CommandType = CommandType.Text;
cmd.CommandText = this.TableSchemaQuery;
DataTable dtTableSchema = new DataTable();
using (FirebirdSql.Data.FirebirdClient.FbDataAdapter da = new FirebirdSql.Data.FirebirdClient.FbDataAdapter(cmd)) {
da.Fill(dtTableSchema);
}
TableSchema = this.GetInitialTables(dtTableSchema);
}
}
else
{
//Get by default using GetSchema
DataTable dtTableSchema = new DataTable();
dtTableSchema = conn.GetSchema(System.Data.SqlClient.SqlClientMetaDataCollectionNames.Tables);
TableSchema = this.GetInitialTables(dtTableSchema);
}
//Get columns for each table
if (!string.IsNullOrEmpty(this.ColumnSchemaQuery))
{
//Use column schema query
foreach (SchemaRow tsr in TableSchema)
{
DataTable dtColumnSchema = new DataTable();
using (FirebirdSql.Data.FirebirdClient.FbCommand cmd = new FirebirdSql.Data.FirebirdClient.FbCommand()) {
cmd.Connection = conn;
cmd.CommandType = CommandType.Text;
cmd.CommandText = this.ColumnSchemaQuery.Replace(this.TableNamePlaceHolder, tsr.Name);
using (FirebirdSql.Data.FirebirdClient.FbDataAdapter da = new FirebirdSql.Data.FirebirdClient.FbDataAdapter(cmd)) {
da.Fill(dtColumnSchema);
}
}
//Get column schema
foreach (DataRow dr in dtColumnSchema.Rows)
{
Schema.Add(this.SetColumnAttributes(tsr.Name, tsr.Type, dr));
}
}
}
conn.Close();
}
return(Schema);
}
/// <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;
}
}
}
}
private void xrLabel21_BeforePrint(object sender, System.Drawing.Printing.PrintEventArgs e)
{
if (lbIdAssunto.Text == "1" || lbIdAssunto.Text == "2")
{
XRLabel l = (XRLabel)sender;
string sql = "select first 1 tra.data_liquidacao, (select ordenador from sp_fluxus_getordenadorbydata(did.didorgao||did.didunidade,tra.DATA_ACEITE_NF)) " +
"from webtramite tra join webprocesso proc on proc.id = tra.idprocesso " +
"join webdid did on did.numero = proc.numero " +
"where proc.numero = " + xrLabel4.Text + " and tra.idprocesso = proc.id and tra.data_liquidacao is not null and tra.val_liquidado is not null and tra.nf_liquidacao is not null order by tra.id DESC";
FirebirdSql.Data.FirebirdClient.FbConnection conn = Persist.GetConn.getConn();
FirebirdSql.Data.FirebirdClient.FbCommand cmd = new FirebirdSql.Data.FirebirdClient.FbCommand(sql, conn);
if (conn.State == System.Data.ConnectionState.Closed) {
conn.Open();
}
FirebirdSql.Data.FirebirdClient.FbDataReader dr = cmd.ExecuteReader();
while (dr.Read()) {
l.Text = dr["ORDENADOR"].ToString();
}
}
}
/// <summary>
/// Transfere dados de um arquivo Excel para o banco de dados atual.
/// Conexão com o banco atual precisa estar aberta.
/// Não pára a execução se der um problema num comando de inserção específico.
/// </summary>
/// <returns>Número de linhas transferidas.</returns>
/// <param name="p_filename">Nome do arquivo de origem.</param>
/// <param name="p_separator">Separador de campos do arquivo CSV.</param>
/// <param name="p_delimitator">Delimitador de campos do arquivo CSV.</param>
/// <param name="p_header">Se deve considerar a primeira linha como cabeçalho ou não.</param>
/// <param name="p_encoding">Codificação para leitura do arquivo CSV.</param>
/// <param name="p_newtable">Nome da nova tabela a ser criada no banco de dados.</param>
/// <param name="p_progress">Evento de progresso.</param>
/// <param name="p_error">Evento de erro.</param>
public override uint TransferFromFile(string p_filename, char p_separator, char p_delimitator, bool p_header, System.Text.Encoding p_encoding, string p_newtable, Spartacus.Utils.ProgressEventClass p_progress, Spartacus.Utils.ErrorEventClass p_error)
{
Spartacus.Database.Command v_cmd;
Spartacus.Utils.Excel v_excel = null;
uint v_transfered = 0;
string v_createtable;
string v_insert;
try
{
v_excel = new Spartacus.Utils.Excel();
v_excel.Import(p_filename, p_separator, p_delimitator, p_header, p_encoding);
v_createtable = "create table " + p_newtable + " (";
for (int k = 0; k < v_excel.v_set.Tables[0].Columns.Count; k++)
{
if (k < v_excel.v_set.Tables[0].Columns.Count-1)
v_createtable += v_excel.v_set.Tables[0].Columns[k].ColumnName.ToLower() + " varchar,";
else
v_createtable += v_excel.v_set.Tables[0].Columns[k].ColumnName.ToLower() + " varchar)";
}
try
{
this.Execute(v_createtable);
}
catch (Spartacus.Database.Exception e)
{
p_error.FireEvent(v_createtable + "\n" + e.v_message);
}
v_cmd = new Spartacus.Database.Command();
v_cmd.v_text = "insert into " + p_newtable + " values (";
for (int k = 0; k < v_excel.v_set.Tables[0].Columns.Count; k++)
{
if (k < v_excel.v_set.Tables[0].Columns.Count-1)
v_cmd.v_text += "#" + v_excel.v_set.Tables[0].Columns[k].ColumnName.ToLower() + "#,";
else
v_cmd.v_text += "#" + v_excel.v_set.Tables[0].Columns[k].ColumnName.ToLower() + "#)";
v_cmd.AddParameter(v_excel.v_set.Tables[0].Columns[k].ColumnName.ToLower(), Spartacus.Database.Type.QUOTEDSTRING);
}
foreach (System.Data.DataRow r in v_excel.v_set.Tables[0].Rows)
{
foreach (System.Data.DataColumn c in v_excel.v_set.Tables[0].Columns)
v_cmd.SetValue(c.ColumnName, r[c].ToString());
v_insert = v_cmd.GetUpdatedText();
try
{
this.Execute(v_insert);
v_transfered++;
p_progress.FireEvent(v_transfered);
}
catch (Spartacus.Database.Exception e)
{
p_error.FireEvent(v_insert + "\n" + e.v_message);
}
}
return v_transfered;
}
catch (Spartacus.Utils.Exception e)
{
throw new Spartacus.Database.Exception(e);
}
catch (Spartacus.Database.Exception e)
{
throw e;
}
finally
{
if (v_excel != null)
{
v_excel.Clear();
v_excel = 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_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;
}
}
}
}
protected void ASPxGridView1_CustomColumnDisplayText1(object sender, ASPxGridViewColumnDisplayTextEventArgs e)
{
if (e.Column.FieldName == "NUMERO")
{
e.DisplayText = String.Format(@"{0:#\.0000}", Convert.ToInt64(e.Value));
}
if (e.Column.FieldName == "CCCMESES")
{
var conn = Persist.GetConn.getConn();
using (conn) {
conn.Open();
var query = "select cccdescricao from webcentrocustocontrole where cccmeses = " + e.Value;
var cmd = new FirebirdSql.Data.FirebirdClient.FbCommand(query, conn);
e.DisplayText = cmd.ExecuteScalar().ToString();
}
}
}
/// <summary>
/// Fecha a conexão com o banco de dados.
/// </summary>
public override void Close()
{
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;
}
}
/// <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>
/// Executa um código SQL no banco de dados.
/// </summary>
/// <param name='p_sql'>
/// Código SQL a ser executado no banco de dados.
/// </param>
public override void Execute(string p_sql)
{
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(Spartacus.Database.Command.RemoveUnwantedCharsExecute(p_sql), this.v_con);
this.v_cmd.ExecuteNonQuery();
}
catch (FirebirdSql.Data.FirebirdClient.FbException e)
{
throw new Spartacus.Database.Exception(e);
}
finally
{
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 = Spartacus.Database.Command.RemoveUnwantedCharsExecute(p_sql);
this.v_cmd.ExecuteNonQuery();
}
catch (FirebirdSql.Data.FirebirdClient.FbException e)
{
throw new Spartacus.Database.Exception(e);
}
}
}
private void btnOK_Click(object sender, EventArgs e)
{
Object obj = null;
string commandText = string.Empty;
if (txtDataSetName.Text == string.Empty)
{
MessageBox.Show("Please input a valid dataset name.");
return;
}
if (lstDataSetVariable.Items.Count == 0)
{
MessageBox.Show("Please load a variable datafile.");
return;
}
ESIL.DBUtility.FireBirdHelperBase fb = new ESIL.DBUtility.ESILFireBirdHelper();
if (CommonClass.Connection.State != ConnectionState.Open)
{
CommonClass.Connection.Open();
}
FirebirdSql.Data.FirebirdClient.FbConnection fbconnection = CommonClass.getNewConnection();
fbconnection.Open();
FirebirdSql.Data.FirebirdClient.FbTransaction fbtra = fbconnection.BeginTransaction(); FirebirdSql.Data.FirebirdClient.FbCommand fbCommand = new FirebirdSql.Data.FirebirdClient.FbCommand();
fbCommand.Connection = fbconnection;
fbCommand.CommandType = CommandType.Text;
fbCommand.Transaction = fbtra;
DataSet ds = new DataSet();
DataTable dt;
int rowCount;
string variableDatasetID = string.Empty;
int variableID = 0;
try
{
lblProgressBar.Visible = true;
progBarVariable.Visible = true;
progBarVariable.Value = 0;
progBarVariable.Minimum = 0;
progBarVariable.Maximum = 0;
progBarVariable.Refresh();
commandText = string.Format("select SETUPVARIABLEDATASETID from SetUpVariableDataSets where SETUPVARIABLEDATASETNAME='{0}' and SetupID={1}", txtDataSetName.Text, CommonClass.ManageSetup.SetupID);
obj = fb.ExecuteScalar(CommonClass.Connection, new CommandType(), commandText);
if (_datasetName == string.Empty)
{
if (obj != null)
{
MessageBox.Show("The dataset name has already been defined. Please enter a different name."); return;
}
commandText = "select max(SETUPVARIABLEDATASETID) from SETUPVARIABLEDATASETS";
obj = Convert.ToInt32(fb.ExecuteScalar(CommonClass.Connection, new CommandType(), commandText)) + 1;
variableDatasetID = obj.ToString();
commandText = string.Format("insert into SetUpVariableDataSets values({0},{1},'{2}')", variableDatasetID, CommonClass.ManageSetup.SetupID, txtDataSetName.Text);
fbCommand.CommandText = commandText;
fbCommand.ExecuteNonQuery();
commandText = string.Format("select GridDefinitionID from GridDefinitions where GridDefinitionName='{0}' and SetupID={1}", txtGridDefinition.Text, CommonClass.ManageSetup.SetupID);
obj = fb.ExecuteScalar(CommonClass.Connection, new CommandType(), commandText);
string gridDefinationID = obj.ToString();
int count = _dsSelectedData.Tables.Count;
foreach (DataTable dtcount in _dsSelectedData.Tables)
{
progBarVariable.Maximum += dtcount.Rows.Count;
}
commandText = "select max(SETUPVARIABLEID) from SetUpVariables";
obj = fb.ExecuteScalar(CommonClass.Connection, new CommandType(), commandText);
if (obj != null)
{
variableID = Convert.ToInt32(obj);
}
for (int i = 0; i < count; i++)
{
dt = _dsSelectedData.Tables[i].Clone();
dt = _dsSelectedData.Tables[i].Copy();
string variableName = dt.TableName;
commandText = string.Format("select SETUPVARIABLEID from SETUPVARIABLES where SETUPVARIABLENAME='{0}' and SETUPVARIABLEDATASETID='{1}'", variableName, variableDatasetID);
obj = fb.ExecuteScalar(CommonClass.Connection, new CommandType(), commandText);
if (obj == null)
{
variableID++;
commandText = string.Format("insert into SetUpVariables values({0},{1},'{2}','{3}')", variableID, variableDatasetID, variableName, gridDefinationID);
fbCommand.CommandText = commandText;
fbCommand.ExecuteNonQuery();
rowCount = dt.Rows.Count;
for (int j = 0; j < (rowCount / 125) + 1; j++)
{
commandText = "execute block as declare SetupVariableID int;" + " BEGIN ";
for (int k = 0; k < 125; k++)
{
if (j * 125 + k < dt.Rows.Count)
{
commandText = commandText + string.Format(" select SetupVariableID from SetupVariables where SetupVariableDataSetID={0} and SetupVariableName='{1}' and GridDefinitionID={2} into :SetupVariableID;", variableDatasetID, variableName, gridDefinationID);
progBarVariable.Value++;
progBarVariable.Refresh();
lblProgressBar.Text = Convert.ToString((int)((double)progBarVariable.Value * 100 / progBarVariable.Maximum)) + "%";
lblProgressBar.Refresh();
}
else
{
continue;
}
commandText = commandText + string.Format(" insert into SETUPGEOGRAPHICVARIABLES values (:SetupVariableID,{0},{1},{2});", dt.Rows[j * 125 + k][0], dt.Rows[j * 125 + k][1], dt.Rows[j * 125 + k][2]);
}
commandText = commandText + "END";
fbCommand.CommandText = commandText;
fbCommand.ExecuteNonQuery();
}
}
}
}
else
{
commandText = string.Format("select SETUPVARIABLEDATASETID from SetUpVariableDataSets where SETUPVARIABLEDATASETNAME='{0}' and SetupID={1}", _datasetName, CommonClass.ManageSetup.SetupID);
obj = fb.ExecuteScalar(CommonClass.Connection, new CommandType(), commandText);
variableDatasetID = obj.ToString();
commandText = string.Format("update SETUPVARIABLEDATASETS set SETUPVARIABLEDATASETNAME='{0}' where setupid={1} and SETUPVARIABLEDATASETID={2}", txtDataSetName.Text, CommonClass.ManageSetup.SetupID, variableDatasetID);
fbCommand.CommandText = commandText;
fbCommand.ExecuteNonQuery();
commandText = string.Format("select GridDefinitionID from GridDefinitions where GridDefinitionName='{0}' and SetupID={1}", txtGridDefinition.Text, CommonClass.ManageSetup.SetupID);
obj = fb.ExecuteScalar(CommonClass.Connection, new CommandType(), commandText);
string gridDefinationID = obj.ToString();
int count = _dsSelectedDataTemp.Tables.Count;
foreach (DataTable dtcount in _dsSelectedDataTemp.Tables)
{
progBarVariable.Maximum += dtcount.Rows.Count;
}
commandText = "select max(SETUPVARIABLEID) from SetUpVariables";
obj = fb.ExecuteScalar(CommonClass.Connection, new CommandType(), commandText);
if (obj != null)
{
variableID = Convert.ToInt32(obj);
}
for (int i = 0; i < count; i++)
{
dt = _dsSelectedDataTemp.Tables[i].Clone();
dt = _dsSelectedDataTemp.Tables[i].Copy();
string variableName = dt.TableName;
commandText = string.Format("select SETUPVARIABLEID from SETUPVARIABLES where SETUPVARIABLENAME='{0}' and SETUPVARIABLEDATASETID='{1}'", variableName, variableDatasetID);
obj = fb.ExecuteScalar(CommonClass.Connection, new CommandType(), commandText);
variableID++;
commandText = string.Format("insert into SetUpVariables values({0},{1},'{2}','{3}')", variableID, variableDatasetID, variableName, gridDefinationID);
fbCommand.CommandText = commandText;
fbCommand.ExecuteNonQuery();
rowCount = dt.Rows.Count;
for (int j = 0; j < (rowCount / 125) + 1; j++)
{
commandText = "execute block as declare SetupVariableID int;" + " BEGIN ";
for (int k = 0; k < 125; k++)
{
if (j * 125 + k < dt.Rows.Count)
{
commandText = commandText + string.Format(" select SetupVariableID from SetupVariables where SetupVariableDataSetID={0} and SetupVariableName='{1}' and GridDefinitionID={2} into :SetupVariableID;", variableDatasetID, variableName, gridDefinationID);
progBarVariable.Value++;
progBarVariable.Refresh();
lblProgressBar.Text = Convert.ToString((int)((double)progBarVariable.Value * 100 / progBarVariable.Maximum)) + "%";
lblProgressBar.Refresh();
}
else
{
continue;
}
commandText = commandText + string.Format(" insert into SETUPGEOGRAPHICVARIABLES values (:SetupVariableID,{0},{1},{2});", dt.Rows[j * 125 + k][0], dt.Rows[j * 125 + k][1], dt.Rows[j * 125 + k][2]);
}
commandText = commandText + "END";
fbCommand.CommandText = commandText;
fbCommand.ExecuteNonQuery();
}
}
}
fbtra.Commit();
fbCommand.Connection.Close();
progBarVariable.Visible = false;
lblProgressBar.Visible = false;
this.DialogResult = DialogResult.OK;
this.Close();
}
catch (Exception ex)
{
MessageBox.Show("Failed to load variable dataset.");
fbtra.Rollback();
progBarVariable.Value = 0;
progBarVariable.Visible = false;
lblProgressBar.Text = "";
lblProgressBar.Visible = false;
Logger.LogError(ex.Message);
}
}
private void xrLabel37_BeforePrint(object sender, System.Drawing.Printing.PrintEventArgs e)
{
string sql = "Select first 1 liq_responsavel from webtramite tra join webprocesso proc on proc.id = tra.idprocesso where proc.numero = " + xrLabel4.Text +
" and liq_responsavel is not null";
string resp = string.Empty;
FirebirdSql.Data.FirebirdClient.FbConnection conn = Persist.GetConn.getConn();
FirebirdSql.Data.FirebirdClient.FbCommand cmd = new FirebirdSql.Data.FirebirdClient.FbCommand(sql, conn);
conn.Open();
FirebirdSql.Data.FirebirdClient.FbDataReader dr = cmd.ExecuteReader();
while (dr.Read()) {
if (!Convert.IsDBNull(dr["liq_responsavel"])) {
resp = dr["liq_responsavel"].ToString();
}
}
conn.Close();
if (!resp.Equals(string.Empty)) {
XRLabel l = (XRLabel)sender;
l.Text = resp;
}
}
/// <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>
/// Realiza uma consulta no banco de dados, armazenando um único dado de retorno em uma string.
/// </summary>
/// <returns>
/// string com o dado de retorno.
/// </returns>
/// <param name='p_sql'>
/// Código SQL a ser consultado no banco de dados.
/// </param>
public override string ExecuteScalar(string p_sql)
{
object v_tmp;
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(Spartacus.Database.Command.RemoveUnwantedCharsExecute(p_sql), this.v_con);
v_tmp = this.v_cmd.ExecuteScalar();
if (v_tmp != null)
return v_tmp.ToString();
else
return "";
}
catch (FirebirdSql.Data.FirebirdClient.FbException e)
{
throw new Spartacus.Database.Exception(e);
}
finally
{
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 = Spartacus.Database.Command.RemoveUnwantedCharsExecute(p_sql);
v_tmp = this.v_cmd.ExecuteScalar();
if (v_tmp != null)
return v_tmp.ToString();
else
return "";
}
catch (FirebirdSql.Data.FirebirdClient.FbException e)
{
throw new Spartacus.Database.Exception(e);
}
}
}
/// <summary>
/// Insere um bloco de linhas em uma determinada tabela.
/// </summary>
/// <param name='p_table'>
/// Nome da tabela a serem inseridas as linhas.
/// </param>
/// <param name='p_rows'>
/// Lista de linhas a serem inseridas na tabela.
/// </param>
public override void InsertBlock(string p_table, System.Collections.ArrayList p_rows)
{
string v_block;
if (this.v_con == null)
{
try
{
this.v_con = new FirebirdSql.Data.FirebirdClient.FbConnection(this.v_connectionstring);
this.v_con.Open();
v_block = "execute block as begin\n";
for (int k = 0; k < p_rows.Count; k++)
v_block += "insert into " + p_table + " values " + (string)p_rows[k] + ";\n";
v_block += "end";
this.v_cmd = new FirebirdSql.Data.FirebirdClient.FbCommand(Spartacus.Database.Command.RemoveUnwantedCharsExecute(v_block), this.v_con);
this.v_cmd.ExecuteNonQuery();
}
catch (FirebirdSql.Data.FirebirdClient.FbException e)
{
throw new Spartacus.Database.Exception(e);
}
finally
{
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
{
v_block = "execute block as begin\n";
for (int k = 0; k < p_rows.Count; k++)
v_block += "insert into " + p_table + " values " + (string)p_rows[k] + ";\n";
v_block += "end";
this.v_cmd = new FirebirdSql.Data.FirebirdClient.FbCommand(Spartacus.Database.Command.RemoveUnwantedCharsExecute(v_block), this.v_con);
this.v_cmd.ExecuteNonQuery();
}
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 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>
/// Abre a conexão com o banco de dados.
/// </summary>
public override void Open()
{
try
{
this.v_con = new FirebirdSql.Data.FirebirdClient.FbConnection(this.v_connectionstring);
this.v_con.Open();
this.v_cmd = new FirebirdSql.Data.FirebirdClient.FbCommand();
this.v_cmd.Connection = this.v_con;
}
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;
}