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