public DatabaseModel Create(DbConnection connection, IEnumerable <string> tables, IEnumerable <string> schemas) { ResetState(); _connection = (FirebirdClientConnection)connection; var connectionStartedOpen = _connection.State == ConnectionState.Open; if (!connectionStartedOpen) { _connection.Open(); } try { _databaseModel.DatabaseName = _connection.Database; _serverVersion = Data.Services.FbServerProperties.ParseServerVersion(_connection.ServerVersion); #warning Finish return(_databaseModel); } finally { if (!connectionStartedOpen) { _connection.Close(); } } }
private void bTEST_Click(object sender, EventArgs e) { try { using (FirebirdSql.Data.FirebirdClient.FbConnection fbc = new FirebirdSql.Data.FirebirdClient.FbConnection((new clDBInfo(tbDBName.Text, cbServer.Text, tbDBPath.Text, tbUser.Text, tbPassword.Text)).GetFbConnStr())) { fbc.Open(); FbServerProperties fbsp = new FbServerProperties(); fbsp.ConnectionString = (new clDBInfo(tbDBName.Text, cbServer.Text, tbDBPath.Text, tbUser.Text, tbPassword.Text)).GetFbConnStr(); string msg = ""; foreach (string s in fbsp.DatabasesInfo.Databases) { msg = msg + "\n" + s; } MessageBox.Show(string.Format("к серверу {0} успешно установлено соединение.\nВсего соединений к серверу БД: {1}\n {2}", cbServer.Text, fbsp.DatabasesInfo.ConnectionCount, msg)); fbc.Close(); } } catch (Exception ex) { MessageBox.Show(ex.Message); } }
private void bTEST_Click(object sender, EventArgs e) { try { using (FirebirdSql.Data.FirebirdClient.FbConnection fbc = new FirebirdSql.Data.FirebirdClient.FbConnection((new clDBInfo(tbDBName.Text, cbServer.Text, tbDBPath.Text, tbUser.Text, tbPassword.Text)).GetFbConnStr())) { fbc.Open(); FbServerProperties fbsp = new FbServerProperties(); fbsp.ConnectionString = (new clDBInfo(tbDBName.Text, cbServer.Text, tbDBPath.Text, tbUser.Text, tbPassword.Text)).GetFbConnStr(); string msg = ""; foreach (string s in fbsp.DatabasesInfo.Databases) { msg = msg + "\n" + s; } MessageBox.Show(string.Format("к серверу {0} успешно установлено соединение.\nВсего соединений к серверу БД: {1}\n {2}", cbServer.Text, fbsp.DatabasesInfo.ConnectionCount, msg)); fbc.Close(); } } catch (Exception ex) { MessageBox.Show(ex.Message); } }
public void TestConnection() { FirebirdSql.Data.FirebirdClient.FbConnection Conn = new FirebirdSql.Data.FirebirdClient.FbConnection(this.ConnStr); Conn.Open(); Conn.Close(); Conn.Dispose(); }
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 DatabaseModel Create(string connectionString, IEnumerable <string> tables, IEnumerable <string> schemas) { using (var connection = new FirebirdClientConnection(connectionString)) { return(Create(connection, tables, schemas)); } }
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)); } } }
} // End Constructor public cFireBird(string strConnectionString) { this.m_dbtDBtype = DataBaseEngine_t.FireBird; this.m_dblDBversion = 2.5; this.m_providerFactory = this.GetFactory(); //this.m_DatabaseConfiguration = new DataBase.Tools.cDataBaseConfiguration(); this.m_ConnectionString = new FirebirdSql.Data.FirebirdClient.FbConnectionStringBuilder(strConnectionString); this.m_SqlConnection = new FirebirdSql.Data.FirebirdClient.FbConnection(strConnectionString); }
public static Boolean ConnectToFirebird(DBEnvironment _environment) { try { var connectionString = GetFirebirdConnectionStringByEnvironment(_environment); using (var connection = new FirebirdSql.Data.FirebirdClient.FbConnection(connectionString)) { connection.Open(); return(true); } } catch (Exception) { throw; } }
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); } } }
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); } }
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); }
public override System.Data.IDbConnection GetConnection() { FirebirdSql.Data.FirebirdClient.FbConnection fbc = new FirebirdSql.Data.FirebirdClient.FbConnection(m_ConnectionString.ConnectionString); return(fbc); }
void ResetState() { _databaseModel = new DatabaseModel(); _connection = null; _serverVersion = null; }
/// <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> /// 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> /// 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> /// Transfere dados do banco de dados atual para um banco de dados de destino. /// Conexão com o banco de destino precisa estar aberta. /// </summary> /// <returns>Número de linhas transferidas.</returns> /// <param name="p_query">Consulta SQL para buscar os dados no banco atual.</param> /// <param name="p_insert">Comando de inserção para inserir cada linha no banco de destino.</param> /// <param name="p_destdatabase">Conexão com o banco de destino.</param> public override uint Transfer(string p_query, Spartacus.Database.Command p_insert, Spartacus.Database.Generic p_destdatabase) { uint v_transfered = 0; if (this.v_con == null) { try { this.v_con = new FirebirdSql.Data.FirebirdClient.FbConnection(this.v_connectionstring); this.v_con.Open(); this.v_cmd = new FirebirdSql.Data.FirebirdClient.FbCommand(p_query, this.v_con); this.v_reader = this.v_cmd.ExecuteReader(); while (v_reader.Read()) { for (int i = 0; i < v_reader.FieldCount; i++) p_insert.SetValue(this.FixColumnName(v_reader.GetName(i)).ToLower(), v_reader[i].ToString()); p_destdatabase.Execute(p_insert.GetUpdatedText()); v_transfered++; } return v_transfered; } catch (FirebirdSql.Data.FirebirdClient.FbException e) { throw new Spartacus.Database.Exception(e); } finally { if (this.v_reader != null) { this.v_reader.Close(); this.v_reader = null; } if (this.v_cmd != null) { this.v_cmd.Dispose(); this.v_cmd = null; } if (this.v_con != null) { this.v_con.Close(); this.v_con = null; } } } else { try { this.v_cmd.CommandText = p_query; this.v_reader = this.v_cmd.ExecuteReader(); while (v_reader.Read()) { for (int i = 0; i < v_reader.FieldCount; i++) p_insert.SetValue(this.FixColumnName(v_reader.GetName(i)).ToLower(), v_reader[i].ToString()); p_destdatabase.Execute(p_insert.GetUpdatedText()); v_transfered++; } return v_transfered; } catch (FirebirdSql.Data.FirebirdClient.FbException e) { throw new Spartacus.Database.Exception(e); } finally { if (this.v_reader != null) { this.v_reader.Close(); this.v_reader = null; } } } }
/// <summary> /// Inicializa uma nova instancia da classe <see cref="Spartacus.Database.Firebird"/>. /// </summary> /// <param name='p_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; }
/// <summary> /// Realiza uma consulta no banco de dados, armazenando os dados de retorno em um <see cref="System.Data.DataTable"/>. /// </summary> /// <param name='p_sql'> /// Código SQL a ser consultado no banco de dados. /// </param> /// <param name='p_tablename'> /// Nome virtual da tabela onde deve ser armazenado o resultado, para fins de cache. /// </param> /// <param name='p_progress'>Evento de progresso da execução da consulta.</param> /// <returns>Retorna uma <see cref="System.Data.DataTable"/> com os dados de retorno da consulta.</returns> public override System.Data.DataTable Query(string p_sql, string p_tablename, Spartacus.Utils.ProgressEventClass p_progress) { System.Data.DataTable v_table = null; System.Data.DataRow v_row; uint v_counter = 0; p_progress.FireEvent(v_counter); if (this.v_con == null) { try { this.v_con = new FirebirdSql.Data.FirebirdClient.FbConnection(this.v_connectionstring); this.v_con.Open(); this.v_cmd = new FirebirdSql.Data.FirebirdClient.FbCommand(p_sql, this.v_con); this.v_reader = this.v_cmd.ExecuteReader(); v_table = new System.Data.DataTable(p_tablename); for (int i = 0; i < v_reader.FieldCount; i++) v_table.Columns.Add(this.FixColumnName(this.v_reader.GetName(i)), typeof(string)); while (this.v_reader.Read()) { v_row = v_table.NewRow(); for (int i = 0; i < this.v_reader.FieldCount; i++) v_row[i] = this.v_reader[i].ToString(); v_table.Rows.Add(v_row); v_counter++; p_progress.FireEvent(v_counter); } return v_table; } catch (FirebirdSql.Data.FirebirdClient.FbException e) { throw new Spartacus.Database.Exception(e); } finally { if (this.v_reader != null) { this.v_reader.Close(); this.v_reader = null; } if (this.v_cmd != null) { this.v_cmd.Dispose(); this.v_cmd = null; } if (this.v_con != null) { this.v_con.Close(); this.v_con = null; } } } else { try { this.v_cmd.CommandText = p_sql; this.v_reader = this.v_cmd.ExecuteReader(); v_table = new System.Data.DataTable(p_tablename); for (int i = 0; i < v_reader.FieldCount; i++) v_table.Columns.Add(this.FixColumnName(this.v_reader.GetName(i)), typeof(string)); while (this.v_reader.Read()) { v_row = v_table.NewRow(); for (int i = 0; i < this.v_reader.FieldCount; i++) v_row[i] = this.v_reader[i].ToString(); v_table.Rows.Add(v_row); v_counter++; p_progress.FireEvent(v_counter); } return v_table; } catch (FirebirdSql.Data.FirebirdClient.FbException e) { throw new Spartacus.Database.Exception(e); } finally { if (this.v_reader != null) { this.v_reader.Close(); this.v_reader = null; } } } }
/// <summary> /// 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); } }
public static IDbConnection GetConnection(string provider, string cstring) { IDbConnection cn = null; switch (provider.ToLower()) { case "sqlite": if (cstring.Length > 0) { cn = new SqliteConnection(cstring); } break; case "oracle": if (cstring.Length > 0) { cn = new System.Data.OracleClient.OracleConnection(cstring); } break; case "postgresql": if (cstring.Length > 0) { cn = new Npgsql.NpgsqlConnection(cstring); } break; case "firebird.net": if (cstring.Length > 0) { cn = new FirebirdSql.Data.FirebirdClient.FbConnection(cstring); } break; case "mysql.net": // can't connect unless information provided; // when user wants to set the connection programmatically this they should do this if (cstring.Length > 0) { cn = new MySql.Data.MySqlClient.MySqlConnection(cstring); } break; case "sql": // can't connect unless information provided; // when user wants to set the connection programmatically this they should do this if (cstring.Length > 0) { cn = new SqlConnection(cstring); } break; case "odbc": cn = new OdbcConnection(cstring); break; //case "oledb": // cn = new OleDbConnection(cstring); // break; case "filedirectory": cn = new fyiReporting.Data.FileDirConnection(cstring); break; case "xml": cn = new fyiReporting.Data.XmlConnection(cstring); break; //case "webservice": // cn = new fyiReporting.Data.WebServiceConnection(cstring); // break; case "weblog": cn = new fyiReporting.Data.LogConnection(cstring); break; case "text": cn = new fyiReporting.Data.TxtConnection(cstring); break; case "itunes": cn = new fyiReporting.Data.iTunesConnection(cstring); break; #if EnableBundleLinux // See properties -> Build -> Conditional compilation symbols case "sqlite": cn = new Mono.Data.Sqlite.SqliteConnection(cstring); break; case "postgresql": cn = new Npgsql.NpgsqlConnection(cstring); break; #endif default: if (SqlEntries == null) { // if never initialized; we should init RdlEngineConfigInit(); } System.Console.WriteLine("Attempt to find provider"); SqlConfigEntry sce = SqlEntries[provider] as SqlConfigEntry; if (sce == null || sce.CodeModule == null) { if (sce != null && sce.ErrorMsg != null) { // error during initialization?? throw new Exception(sce.ErrorMsg); } break; } System.Console.WriteLine("Provider Create Instance"); object[] args = new object[] { cstring }; Assembly asm = sce.CodeModule; object o = asm.CreateInstance(sce.ClassName, false, BindingFlags.CreateInstance, null, args, null, null); if (o == null) { throw new Exception(string.Format(Strings.RdlEngineConfig_Error_UnableCreateInstance, sce.ClassName, provider)); } cn = o as IDbConnection; break; } return(cn); }
/// <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); } } }
/// <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> /// 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; } } } }