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