Ejemplo n.º 1
0
        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();
            }
        }
Ejemplo n.º 2
0
        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));
                }
            }
        }
Ejemplo n.º 3
0
        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;
        }
Ejemplo n.º 4
0
        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;
        }
Ejemplo n.º 5
0
        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;
        }
Ejemplo n.º 6
0
        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();
        }
Ejemplo n.º 7
0
        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();
        }
Ejemplo n.º 8
0
        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);
                }
            }
        }
Ejemplo n.º 9
0
        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;
            }
        }
Ejemplo n.º 10
0
        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);
        }
Ejemplo n.º 11
0
        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);
        }
Ejemplo n.º 12
0
        /// <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;
                    }
                }
            }
        }
Ejemplo n.º 13
0
        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();
                }
            }
        }
Ejemplo n.º 14
0
        /// <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;
                }
            }
        }
Ejemplo n.º 15
0
        /// <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();
                }

            }
        }
Ejemplo n.º 17
0
 /// <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;
     }
 }
Ejemplo n.º 18
0
        /// <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;
        }
Ejemplo n.º 19
0
 /// <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);
         }
     }
 }
Ejemplo n.º 20
0
        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);
            }
        }
Ejemplo n.º 21
0
        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;
            }
        }
Ejemplo n.º 22
0
        /// <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;
                    }
                }
            }
        }
Ejemplo n.º 23
0
        /// <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);
                }
            }
        }
Ejemplo n.º 24
0
        /// <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);
                }
            }
        }
Ejemplo n.º 25
0
        /// <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;
                    }
                }
            }
        }
Ejemplo n.º 26
0
 /// <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;
        }