private static List <TableSchema> GetTables(FbConnection con)
{
string[] restrict3 = { null, null, null };
string[] restrict4 = { null, null, null, null };
var aStore = new List <TableSchema>();
restrict4[0] = null;
restrict4[1] = null;
restrict4[2] = null;
restrict4[3] = "TABLE";
var dtTables = con.GetSchema("Tables", restrict4);
for (var i = 0; i < dtTables.Rows.Count; i++)
{
var tRow = dtTables.Rows[i];
var eTable = new TableSchema {
Name = tRow["TABLE_NAME"].ToString()
};
// Columns
restrict3[0] = null;
restrict3[1] = null;
restrict3[2] = eTable.Name;
var dtShema = con.GetSchema("Columns", restrict3);
if (dtShema.Rows.Count > 0)
{
eTable.Columns = new TableColumnSchema[dtShema.Rows.Count];
}
for (var j = 0; j < dtShema.Rows.Count; j++)
{
var cRow = dtShema.Rows[j];
var eColumn = new TableColumnSchema
{
Name = cRow["COLUMN_NAME"].ToString(),
Size = Convert.ToInt32(cRow["COLUMN_SIZE"], CultureInfo.InvariantCulture),
Type = TypeFbToDbsm(cRow["COLUMN_DATA_TYPE"].ToString()),
Nullable = Convert.ToBoolean(cRow["IS_NULLABLE"], CultureInfo.InvariantCulture)
};
eColumn.DefaultValue = HelpDbscColumnDefault(con, eColumn.Name, eTable.Name);
eColumn.DefaultValue = string.IsNullOrEmpty(eColumn.DefaultValue) ? null : eColumn.DefaultValue;
eColumn.DecimalPrecision = cRow["NUMERIC_PRECISION"] == DBNull.Value
? 0
: Convert.ToInt32(cRow["NUMERIC_PRECISION"], CultureInfo.InvariantCulture);
eColumn.DecimalScale = Convert.ToInt32(cRow["NUMERIC_SCALE"], CultureInfo.InvariantCulture);
eTable.Columns[j] = eColumn;
}
aStore.Add(eTable);
}
return(aStore);
}
private static List <DBGenerator> GetGenerators(FbConnection con)
{
using (var cmd = con.CreateCommand())
{
var generators = new List <DBGenerator>();
string[] restrict4 = { null, null, null, null };
restrict4[0] = null;
restrict4[1] = null;
restrict4[2] = null;
restrict4[3] = null;
var dtGenerators = con.GetSchema("Generators", restrict4);
for (var i = 0; i < dtGenerators.Rows.Count; i++)
{
var trRow = dtGenerators.Rows[i];
if (Convert.ToBoolean(trRow["IS_SYSTEM_GENERATOR"], CultureInfo.InvariantCulture))
{
continue;
}
var eGenerator = new DBGenerator
{
Name = trRow["GENERATOR_NAME"].ToString()
};
cmd.CommandText = string.Format("SELECT gen_id(\"{0}\", 0) FROM rdb$database", eGenerator.Name);
eGenerator.StartValue = Convert.ToInt32(cmd.ExecuteScalar(), CultureInfo.InvariantCulture);
generators.Add(eGenerator);
}
return(generators);
}
}
protected FbCommand BuildCommand(FbConnection connection, string collectionName, string[] restrictions)
{
string filter = String.Format("CollectionName='{0}'", collectionName);
StringBuilder builder = GetCommandText(restrictions);
DataRow[] restriction = connection.GetSchema(DbMetaDataCollectionNames.Restrictions).Select(filter);
FbTransaction transaction = connection.InnerConnection.ActiveTransaction;
FbCommand command = new FbCommand(builder.ToString(), connection, transaction);
if (restrictions != null && restrictions.Length > 0)
{
int index = 0;
for (int i = 0; i < restrictions.Length; i++)
{
string rname = restriction[i]["RestrictionName"].ToString();
if (restrictions[i] != null)
{
// Catalog, Schema and TableType are no real restrictions
if (!rname.EndsWith("Catalog") && !rname.EndsWith("Schema") && rname != "TableType")
{
string pname = String.Format(CultureInfo.CurrentUICulture, "@p{0}", index++);
command.Parameters.Add(pname, FbDbType.VarChar, 255).Value = restrictions[i];
}
}
}
}
return(command);
}
public static DataTable GetCharacterSets(ConnectionData connectionData)
{
var dataTable = new DataTable();
var access = new DataAccess(connectionData);
var connectionString = access.CreateConnectionString();
try
{
using (var connection = new FbConnection(connectionString))
{
connection.Open();
//var pr = connection.GetSchema();
dataTable = connection.GetSchema("Tables", new string[] { null, null, null,
"TABLE" });
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
return(dataTable);
}
protected FbCommand BuildCommand(FbConnection connection, string collectionName, string[] restrictions)
{
SetMajorVersionNumber(connection);
var filter = string.Format("CollectionName='{0}'", collectionName);
var builder = GetCommandText(restrictions);
var restriction = connection.GetSchema(DbMetaDataCollectionNames.Restrictions).Select(filter);
var transaction = connection.InnerConnection.ActiveTransaction;
var command = new FbCommand(builder.ToString(), connection, transaction);
if (restrictions != null && restrictions.Length > 0)
{
var index = 0;
for (var i = 0; i < restrictions.Length; i++)
{
var rname = restriction[i]["RestrictionName"].ToString();
if (restrictions[i] != null)
{
// Catalog, Schema and TableType are no real restrictions
if (!rname.EndsWith("Catalog") && !rname.EndsWith("Schema") && rname != "TableType")
{
var pname = string.Format("@p{0}", index++);
command.Parameters.Add(pname, FbDbType.VarChar, 255).Value = restrictions[i];
}
}
}
}
return(command);
}
public override void LoadObjects(MetadataList objects, MetadataLoadingOptions loadingOptions)
{
if (!Connected)
{
Connect();
}
try
{
// load tables and views
DataTable schemaTable = _connection.GetSchema("Tables", null);
MetadataObjectFetcherFromDatatable mof = new MetadataObjectFetcherFromDatatable(objects, loadingOptions);
mof.NameFieldName = "TABLE_NAME";
mof.TypeFieldName = "TABLE_TYPE";
mof.TableType = new string[] { "TABLE" };
mof.SystemTableType = new string[] { "SYSTEM_TABLE" };
mof.ViewType = new string[] { "VIEW" };
mof.TrimSpaces = true;
mof.Datatable = schemaTable;
mof.LoadMetadata();
// load procedures
schemaTable = _connection.GetSchema("Procedures", null);
mof = new MetadataObjectFetcherFromDatatable(objects, loadingOptions);
mof.NameFieldName = "PROCEDURE_NAME";
mof.SystemFieldName = "IS_SYSTEM_PROCEDURE";
mof.SystemFieldValue = 1;
mof.TrimSpaces = true;
mof.Datatable = schemaTable;
mof.DefaultObjectType = MetadataType.Procedure;
mof.LoadMetadata();
}
catch (Exception exception)
{
throw new QueryBuilderException(exception.Message, exception);
}
}
public bool TableExists(string tablename)
{
FbConnection fb = new FbConnection();
fb.GetSchema("Tables");
System.Data.Common.DbConnection db;
db = new FbConnection();
//IDbConnection db;
//db.
return(false);
}
internal override void LoadAll()
{
try
{
var cn = new FbConnection(_dbRoot.ConnectionString);
cn.Open();
DataTable metaData1 = cn.GetSchema("ForeignKeys", new[] { null, null, Table.Name });
DataTable metaData2 = cn.GetSchema("ForeignKeys", new[] { null, null, null, null, null, Table.Name });
cn.Close();
DataRowCollection rows = metaData2.Rows;
int count = rows.Count;
for (int i = 0; i < count; i++)
{
metaData1.ImportRow(rows[i]);
}
PopulateArrayNoHookup(metaData1);
ForeignKey key = null;
string keyName = "";
foreach (DataRow row in metaData1.Rows)
{
keyName = row["FK_NAME"] as string;
key = GetByName(keyName);
key.AddForeignColumn(null, null, (string)row["PK_TABLE_NAME"], (string)row["PK_COLUMN_NAME"], true);
key.AddForeignColumn(null, null, (string)row["FK_TABLE_NAME"], (string)row["FK_COLUMN_NAME"],
false);
}
}
catch (Exception ex)
{
string m = ex.Message;
}
}
protected override void PopulateTables(DbObjectCollection <Table> tablesCollection)
{
using (var tables = connection.GetSchema("Tables")) {
foreach (DataRow row in tables.Rows)
{
if ((short)row["IS_SYSTEM_TABLE"] == 0)
{
var tableType = ((string)row["TABLE_TYPE"]).ToUpper();
var table = new FbTable(this, tableType == "VIEW")
{
TableName = (string)row["TABLE_NAME"]
};
var schema = row["TABLE_SCHEMA"] as string;
if (!string.IsNullOrEmpty(schema))
{
table.TableSchema = schema;
}
table.Name = table.TableName;
tablesCollection.Add(table);
}
}
}
}
public bool TableExists(string tableName)
{
using (FbConnection conn = CreateConnection())
{
try
{
DataTable dt = conn.GetSchema("TABLES", BuildSchemaQueryRestrictions(null, null, tableName, null));
using (DataTableReader dtr = dt.CreateDataReader())
return(dtr.Read());
}
finally
{
conn.Close();
}
}
}
internal override void LoadAll()
{
try
{
var cn = new FbConnection(_dbRoot.ConnectionString);
cn.Open();
DataTable metaData = cn.GetSchema("Procedures", new[] { Database.Name });
cn.Close();
PopulateArray(metaData);
}
catch (Exception ex)
{
string m = ex.Message;
}
}
internal override void LoadForTable()
{
try
{
var cn = new FbConnection(_dbRoot.ConnectionString);
cn.Open();
DataTable metaData = cn.GetSchema("Columns", new[] { null, null, Table.Name });
PopulateArray(metaData);
LoadExtraData(cn, Table.Name, "T");
cn.Close();
}
catch (Exception ex)
{
string m = ex.Message;
}
}
public static bool DatabaseHelperTableExists(string tableName)
{
FbConnection connection = new FbConnection(GetConnectionString());
string[] restrictions = new string[4];
restrictions[2] = tableName;
connection.Open();
DataTable table = connection.GetSchema("Tables", restrictions);
connection.Close();
if (table != null)
{
return(table.Rows.Count > 0);
}
return(false);
}
internal override void LoadAll()
{
try
{
string type = dbRoot.ShowSystemData ? "SYSTEM TABLE" : "TABLE";
var cn = new FbConnection(_dbRoot.ConnectionString);
cn.Open();
DataTable metaData = cn.GetSchema("Tables", new[] { null, null, null, type });
cn.Close();
PopulateArray(metaData);
}
catch (Exception ex)
{
string m = ex.Message;
}
}
internal override void LoadAll()
{
try
{
var cn = new FbConnection(_dbRoot.ConnectionString);
cn.Open();
DataTable metaData = cn.GetSchema("Views", new string[] { null, null, null });
cn.Close();
metaData.Columns["VIEW_NAME"].ColumnName = "TABLE_NAME";
PopulateArray(metaData);
}
catch (Exception ex)
{
string m = ex.Message;
}
}
internal override void LoadAll()
{
try
{
var cn = new FbConnection(_dbRoot.ConnectionString);
cn.Open();
DataTable metaData = cn.GetSchema("Indexes", new[] { null, null, Table.Name });
cn.Close();
metaData.Columns["IS_UNIQUE"].ColumnName = "UNIQUE";
metaData.Columns["INDEX_TYPE"].ColumnName = "TYPE";
metaData.Columns["ORDINAL_POSITION"].ColumnName = "CARDINALITY";
PopulateArray(metaData);
}
catch (Exception ex)
{
string m = ex.Message;
}
}
public static DataTable GetViewColumns(ConnectionData connectionData, string view)
{
var dataTable = new DataTable();
var access = new DataAccess(connectionData);
var connectionString = access.CreateConnectionString();
try
{
using (var connection = new FbConnection(connectionString))
{
connection.Open();
dataTable = connection.GetSchema("ViewColumns");
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
return(dataTable);
}
public static DataTable GetPrimaryKeys(ConnectionData connectionData, string table)
{
var dataTable = new DataTable();
var access = new DataAccess(connectionData);
var connectionString = access.CreateConnectionString();
try
{
using (var connection = new FbConnection(connectionString))
{
connection.Open();
dataTable = connection.GetSchema("PrimaryKeys", new string[] { null, null, table });
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
return(dataTable);
}
internal override void LoadAll()
{
try
{
var cn = new FbConnection(_dbRoot.ConnectionString);
cn.Open();
DataTable metaData = cn.GetSchema("Domains", null);
cn.Close();
if (metaData.Columns.Contains("DOMAIN_DATA_TYPE"))
{
metaData.Columns["DOMAIN_DATA_TYPE"].ColumnName = "DATA_TYPE";
}
PopulateArray(metaData);
LoadExtraData(cn);
}
catch (Exception ex)
{
string m = ex.Message;
}
}
public static DataTable GetColumns(ConnectionData connectionData, string tableName)
{
var dataTable = new DataTable();
var access = new DataAccess(connectionData);
var connectionString = access.CreateConnectionString();
try
{
using (var connection = new FbConnection(connectionString))
{
connection.Open();
dataTable = connection.GetSchema("Columns", new[] { null, null, tableName });
//dataTable = QueryProcessing.ExecuteQuery(connectionData, SystemTables.GetColumns("").ToString());
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
return(dataTable);
}
/// <summary>
/// Gets the name of the tables within this database, excluding system tables ($).
/// </summary>
/// <returns>ICollection<string> - Collection of table names</returns>
public ICollection <string> GetTableNames()
{
ICollection <string> results = new List <string>();
using (FbConnection cn = (FbConnection)GetConnnection())
{
if (cn.State != ConnectionState.Open)
{
cn.Open();
var tableNames = cn.GetSchema("Tables");
foreach (System.Data.DataRow row in tableNames.Rows)
{
if (!row["TABLE_NAME"].ToString().Contains("$"))
{
results.Add((string)row["TABLE_NAME"]);
System.Diagnostics.Debug.WriteLine($"Adding table: {row["TABLE_NAME"]}");
}
}
cn.Close();
}
}
return(results);
}
private DataTable GetShema(string name)
{
return(_con.GetSchema(name));
}
private void Init()
{
HasError = false;
try
{
try
{
fbConnection.Open(); //открываем БД
fbDBInfo = new FbDatabaseInfo(fbConnection);
// Get the list of User Tables
// Restrictions:
// TABLE_CATALOG
// TABLE_SCHEMA
// TABLE_NAME
// TABLE_TYPE
System.Data.DataTable userTables = fbConnection.GetSchema("Tables", new string[] { null, null, null, "TABLE" });
// Get the list of System Tables
// Restrictions:
// TABLE_CATALOG
// TABLE_SCHEMA
// TABLE_NAME
// TABLE_TYPE
System.Data.DataTable systemTables = fbConnection.GetSchema("Tables", new string[] { null, null, null, "SYSTEM TABLE" });
}
finally
{
fbConnection.Close();
}
}
catch (DllNotFoundException ex)
{
StringBuilder sb = new StringBuilder();
sb.Append("Не удалось создать подключение к базе данных.\n");
sb.Append("В папке с программой не найдены необходимые библиотеки\n");
sb.Append("fbembed.dll, ib_util.dll, icudt30.dll, icuin30.dll, icuuc30.dll\n");
sb.Append("Переустановите программу или обратитесь к разработчику.");
HasError = true;
LastException = ex;
throw new InvalidOperationException(sb.ToString());
}
catch (FbException ex)
{
StringBuilder sb = new StringBuilder();
sb.Append("Не удалось создать подключение к базе данных.\n");
switch (ex.ErrorCode)
{
case 335544344:
sb.Append("Файл базы данных используется другой программой.\n");
break;
case 335544346:
sb.Append("База данных повреждена.\n");
break;
case 335544415:
sb.Append("База данных повреждена.\n");
break;
case 335544472:
sb.Append("У Вас нет доступа к базе данных, проверьте параметры подключения.\n");
break;
case 335544352:
sb.Append("У Вас отсутствуют разрешения, проверьте параметры подключения.\n");
break;
default:
sb.Append("\tкод ошибки: ");
sb.Append(ex.ErrorCode.ToString());
sb.Append("\n\tописание: ");
sb.Append(ex.Message);
break;
}
HasError = true;
LastException = ex;
throw new InvalidOperationException(sb.ToString());
}
catch (Exception ex)
{
StringBuilder sb = new StringBuilder();
sb.Append("Не удалось создать подключение к базе данных.\n");
sb.Append("Произошла неизвестная ошибка.\n");
sb.Append("\n\tописание: ");
sb.Append(ex.Message);
HasError = true;
LastException = ex;
throw new InvalidOperationException(sb.ToString());
}
}
private void buttonOK_Click(object sender, EventArgs e)
{
#region oop exkurs
/*
* Auto auto = new Auto("grün");
* Auto auto2 = new Auto("blau");
*
* List<Auto> alleAutos = new List<Auto>();
* alleAutos.Add(auto);
* alleAutos.Add(auto2);
*
* Auto a = alleAutos[1];
*
* MessageBox.Show(a.Farbe);
*
*
* //MessageBox.Show("Farbe Auto 1: " + auto.Farbe);
* //MessageBox.Show("Farbe Auto 2: " + auto2.Farbe);
*
*
* for (int i = 0; i < 50; i++)
* {
* if (i % 3 == 0)
* auto.Beschleunigen(5);
* else
* auto2.Beschleunigen(5);
* }
*
*
*
* //MessageBox.Show("Geschw. Auto 1: " + auto.AktuelleGeschwindigkeit);
* //MessageBox.Show("Geschw Auto 2: " + auto2.AktuelleGeschwindigkeit);
*
*
* return;
*/
#endregion
comboTables.Items.Clear();
string connectionString =
@"User =SYSDBA;" +
@"Password=masterkey;" +
@"Database=D:\5_daten\TimeCounter\Projekt-BLJ\src/TADATA3.FDB;" +
@"ServerType=1";
FbConnection con = new FbConnection(connectionString);
con.Open();
DataTable t = con.GetSchema("Tables");
DataTable dt = new DataTable();
FbCommand cmd = new FbCommand();
cmd.Connection = con;
FbDataAdapter adap = new FbDataAdapter();
adap.SelectCommand = cmd;
AddTableDataToGrid("schedules", cmd, adap);
AddTableDataToGrid("ATTENDANT", cmd, adap);
AddTableDataToGrid("DEPARTMENTS", cmd, adap);
AddTableDataToGrid("DEVICES", cmd, adap);
AddTableDataToGrid("EXTRATIME", cmd, adap);
AddTableDataToGrid("IDENTIFICATION", cmd, adap);
AddTableDataToGrid("PAYCLASS", cmd, adap);
AddTableDataToGrid("PAYRULE", cmd, adap);
AddTableDataToGrid("PLANNING", cmd, adap);
AddTableDataToGrid("USERS", cmd, adap);
AddTableDataToGrid("WORKCODES", cmd, adap);
//GetTableDataToDatabase("USERS", cmd, adap);
/*
* FbCommand cmd2 = new FbCommand("Update users set password = ''");
* cmd2.Connection = con;
* int affectedRecords = cmd2.ExecuteNonQuery();
*/
con.Close();
}
static void Main(string[] args)
{
string pathdatabase = ConfigurationManager.AppSettings.Get("pathdatabase");
string connectionString =
@"User =SYSDBA;" +
@"Password=masterkey;" +
@"Database=" + pathdatabase + ";" +
@"ServerType=1";
FbConnection con = new FbConnection(connectionString);
con.Open();
DataTable tab = con.GetSchema("Tables");
DataTable dtu = new DataTable();
DataTable dtt = new DataTable();
FbCommand cmdfd = new FbCommand();
cmdfd.Connection = con;
FbDataAdapter adap = new FbDataAdapter();
adap.SelectCommand = cmdfd;
cmdfd.CommandText = "select * from USERS";
adap.Fill(dtu);
cmdfd.CommandText = "select * from ATTENDANT order by \"WHEN\" asc";
adap.Fill(dtt);
Console.WriteLine("Getting Connection ...");
MySqlConnection conn = DBUtils.GetDBConnection();
try
{
Console.WriteLine("Openning Connection ...");
conn.Open();
Console.WriteLine("Connection successful!");
}
catch (Exception e)
{
Console.WriteLine("Error: " + e.Message);
}
List <User> allusers = new List <User>();
List <Stamp> allstamps = new List <Stamp>();
List <Day> allDays = new List <Day>();
List <User> allusersfromedb = new List <User>();
List <Stamp> allstampsfromdb = new List <Stamp>();
List <Day> allDaysfromdb = new List <Day>();
List <Stamp> allstampscopy = new List <Stamp>();
List <Month> allMonths = new List <Month>();
CreateUser(dtu, allusers);
CreateStamp(dtt, allstamps);
delDoubleStamps(allstamps, allstampscopy);
CreateDay(allstamps, allDays);
createMonthsfromDB(conn, allMonths, allDays);
getTime(allDays);
addDaysToMonths(allMonths, allDays);
getuserfromDB(conn, allusersfromedb);
getStampsfromDB(conn, allstampsfromdb);
getDaysfromDB(conn, allDaysfromdb);
InsertUsersInToDB(conn, allusers, allusersfromedb);
InsertStampsInToDB(conn, allstamps, allstampsfromdb);
foreach (Month m in allMonths)
{
if (m.MonthCompleted == false)
{
InsertDaysInToDB(conn, m.Days, allDaysfromdb);
}
}
Console.WriteLine("Transfer successful!");
Console.ReadKey();
}
static void Main(string[] args)
{
string
tmpString = string.Empty;
StreamWriter
fstr_out = null;
FbConnection
cn = null;
FbCommand
cmd = null;
FbDataReader
rdr = null;
FbDataAdapter
da = null;
DataTable
tmpDataTable = null;
object
tmpObject;
int
tmpInt = int.MinValue;
long
tmpLong = long.MinValue;
try
{
try
{
fstr_out = new StreamWriter("log.log", false, System.Text.Encoding.GetEncoding(1251));
fstr_out.AutoFlush = true;
ConnectionStringSettingsCollection
connectionStrings = ConfigurationManager.ConnectionStrings;
if (connectionStrings != null)
{
try
{
tmpString = connectionStrings["FirebirdServer"].ConnectionString;
}
catch
{
tmpString = string.Empty;
}
}
cn = new FbConnection(tmpString);
cn.StateChange += new StateChangeEventHandler(conn_StateChange);
cn.InfoMessage += new FbInfoMessageEventHandler(conn_InfoMessage);
cn.Open();
fstr_out.WriteLine("ConnectionString: " + cn.ConnectionString);
fstr_out.WriteLine("ConnectionTimeout: " + cn.ConnectionTimeout.ToString());
fstr_out.WriteLine("Database: " + cn.Database);
fstr_out.WriteLine("DataSource: " + cn.DataSource);
fstr_out.WriteLine("PacketSize: " + cn.PacketSize);
fstr_out.WriteLine("ServerVersion: " + cn.ServerVersion);
fstr_out.WriteLine("State: " + cn.State.ToString());
fstr_out.WriteLine();
#if TEST_COMMENT
if (cmd == null)
{
cmd = cn.CreateCommand();
}
cmd.CommandType = CommandType.Text;
cmd.CommandText = "select 5/**/*3 from rdb$database";
tmpObject = cmd.ExecuteScalar();
#endif
#if TEST_SMTH
if (cmd == null)
{
cmd = cn.CreateCommand();
}
cmd.CommandType = CommandType.Text;
cmd.CommandText = "select * from TestDate order by FDate";
cmd.Parameters.Clear();
if (da == null)
{
da = new FbDataAdapter(cmd);
}
else
{
da.SelectCommand = cmd;
}
if (tmpDataTable != null)
{
tmpDataTable.Reset();
}
else
{
tmpDataTable = new DataTable();
}
da.Fill(tmpDataTable);
tmpString = "";
for (int i = 0; i < tmpDataTable.Rows.Count; ++i)
{
if (tmpString != string.Empty)
{
tmpString += " ";
}
tmpString += Convert.ToDateTime(tmpDataTable.Rows[i]["FDate"]).ToString("yyyy-MM-dd");
}
#endif
if (tmpDataTable != null)
{
tmpDataTable.Reset();
}
else
{
tmpDataTable = new DataTable();
}
fstr_out.WriteLine("FbConnection.GetSchema()");
tmpDataTable = cn.GetSchema();
tmpString = string.Empty;
foreach (DataColumn col in tmpDataTable.Columns)
{
if (tmpString != string.Empty)
{
tmpString += "\t";
}
tmpString += col.ColumnName;
}
fstr_out.WriteLine("\t" + tmpString);
foreach (DataRow row in tmpDataTable.Rows)
{
tmpString = string.Empty;
foreach (DataColumn col in tmpDataTable.Columns)
{
if (tmpString != string.Empty)
{
tmpString += "\t";
}
tmpString += !row.IsNull(col.ColumnName) ? row[col.ColumnName] : "NULL";
}
fstr_out.WriteLine("\t" + tmpString);
}
fstr_out.WriteLine();
cmd = cn.CreateCommand();
cmd.Transaction = cn.BeginTransaction(new FbTransactionOptions {
TransactionBehavior = FbTransactionBehavior.ReadCommitted
} /*IsolationLevel.ReadCommitted*/);
cmd.CommandType = CommandType.Text;
cmd.CommandText = "select * from \"Staff\"";
rdr = cmd.ExecuteReader();
do
{
if (rdr.HasRows)
{
for (int i = 0; i < rdr.FieldCount; ++i)
{
fstr_out.WriteLine(rdr.GetName(i) + " GetDataTypeName(): \"" + rdr.GetDataTypeName(i) + "\" GetFieldType(): \"" + rdr.GetFieldType(i) + "\"");
}
while (rdr.Read())
{
fstr_out.WriteLine(rdr["Name"] + " " + rdr["Salary"]);
}
}
} while (rdr.NextResult());
rdr.Close();
if (tmpDataTable != null)
{
tmpDataTable.Reset();
}
else
{
tmpDataTable = new DataTable();
}
if (da != null)
{
da.SelectCommand = cmd;
}
else
{
da = new FbDataAdapter(cmd);
}
da.Fill(tmpDataTable);
foreach (DataRow row in tmpDataTable.Rows)
{
fstr_out.WriteLine(row["Name"] + " " + row["Salary"]);
}
#if TEST_STORED_PROCEDURES
if (cmd == null)
{
cmd = cn.CreateCommand();
}
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "SP1";
FbCommandBuilder.DeriveParameters(cmd);
for (int i = 0; i < cmd.Parameters.Count; ++i)
{
fstr_out.WriteLine(cmd.Parameters[i].ParameterName + " " + cmd.Parameters[i].Direction + " " + cmd.Parameters[i].FbDbType + " " + cmd.Parameters[i].DbType);
}
cmd.Parameters["@ARGFIRST"].Value = 7;
cmd.Parameters["@ARGSECOND"].Value = 8;
cmd.ExecuteNonQuery();
if (!Convert.IsDBNull(cmd.Parameters["@RESULT"].Value))
{
fstr_out.WriteLine(cmd.Parameters["@RESULT"].Value);
}
cmd.CommandText = "SP1_1";
FbCommandBuilder.DeriveParameters(cmd);
cmd.Parameters["@ARGFIRST"].Value = 20;
cmd.Parameters["@ARGSECOND"].Value = 8;
cmd.ExecuteNonQuery();
if (!Convert.IsDBNull(cmd.Parameters["@RESULT"].Value))
{
fstr_out.WriteLine(cmd.Parameters["@RESULT"].Value);
}
cmd.CommandType = CommandType.Text;
cmd.CommandText = "select * from SP1_1 (?, ?)";
cmd.Parameters.Clear();
FbParameter
tmpParameter;
tmpParameter = cmd.CreateParameter();
tmpParameter.Direction = ParameterDirection.Input;
tmpParameter.DbType = DbType.Decimal;
tmpParameter.ParameterName = "@ARGSECOND";
tmpParameter.Value = 8;
cmd.Parameters.Add(tmpParameter);
tmpParameter = cmd.CreateParameter();
tmpParameter.Direction = ParameterDirection.Input;
tmpParameter.DbType = DbType.Decimal;
tmpParameter.ParameterName = "@ARGFIRST";
tmpParameter.Value = 20;
cmd.Parameters.Add(tmpParameter);
if ((tmpObject = cmd.ExecuteScalar()) != null &&
!Convert.IsDBNull(tmpObject))
{
tmpInt = Convert.ToInt32(tmpObject);
}
fstr_out.WriteLine(tmpInt);
if (tmpDataTable != null)
{
tmpDataTable.Reset();
}
else
{
tmpDataTable = new DataTable();
}
if (da != null)
{
da.SelectCommand = cmd;
}
else
{
da = new FbDataAdapter(cmd);
}
da.Fill(tmpDataTable);
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "SP2";
FbCommandBuilder.DeriveParameters(cmd);
for (int i = 0; i < cmd.Parameters.Count; ++i)
{
fstr_out.WriteLine(cmd.Parameters[i].ParameterName + " " + cmd.Parameters[i].Direction + " " + cmd.Parameters[i].FbDbType + " " + cmd.Parameters[i].DbType);
}
cmd.Parameters["@TESTID"].Value = 1;
if (tmpDataTable != null)
{
tmpDataTable.Reset();
}
else
{
tmpDataTable = new DataTable();
}
if (da != null)
{
da.SelectCommand = cmd;
}
else
{
da = new FbDataAdapter(cmd);
}
da.Fill(tmpDataTable);
/*
* cmd.CommandType = CommandType.Text;
* cmd.CommandText = "{call SP2(?, ?)}";
* cmd.Parameters.Clear();
*
* tmpParameter = cmd.CreateParameter();
* tmpParameter.Direction = ParameterDirection.Input;
* tmpParameter.DbType = DbType.Int32;
* tmpParameter.ParameterName = "@TESTID";
* tmpParameter.Value = 1;
* cmd.Parameters.Add(tmpParameter);
*
* tmpParameter = cmd.CreateParameter();
* tmpParameter.Direction = ParameterDirection.Output;
* tmpParameter.DbType = DbType.String;
* tmpParameter.ParameterName = "@TESTVALUE";
* cmd.Parameters.Add(tmpParameter);
*
* if (tmpDataTable != null)
* tmpDataTable.Reset();
* else
* tmpDataTable = new DataTable();
*
* if (da != null)
* da.SelectCommand = cmd;
* else
* da = new FbDataAdapter(cmd);
*
* da.Fill(tmpDataTable);
*/
cmd.Parameters.Clear();
cmd.CommandType = CommandType.Text;
//cmd.CommandText = "\"Stub\""; //error
//cmd.CommandText = "exec \"Stub\""; //error
//cmd.CommandText = "execute \"Stub\""; //error
cmd.CommandText = "execute procedure \"Stub\"";
cmd.ExecuteNonQuery();
//cmd.CommandText = "{call \"Stub\"}"; //error
//cmd.ExecuteNonQuery();
#endif
#if TEST_BLOB
cmd.CommandType = CommandType.Text;
FileStream
fs;
byte[]
Blob;
#if TEST_BLOB_SAVE
cmd.CommandText = "update \"TestDataTypes\" set \"CBlob\" = @CBlob";
cmd.Parameters.Clear();
cmd.Parameters.Add("@CBlob", FbDbType.Binary);
fs = new FileStream("welcome.bmp", FileMode.Open, FileAccess.Read);
Blob = new byte[fs.Length];
fs.Read(Blob, 0, Blob.Length);
cmd.Parameters["@CBlob"].Value = Blob;
tmpInt = cmd.ExecuteNonQuery();
#endif
cmd.CommandText = "select * from \"TestDataTypes\"";
cmd.Parameters.Clear();
rdr = cmd.ExecuteReader();
do
{
if (rdr.HasRows)
{
for (int i = 0; i < rdr.FieldCount; ++i)
{
fstr_out.WriteLine(rdr.GetName(i) + " GetDataTypeName(): \"" + rdr.GetDataTypeName(i) + "\" GetFieldType(): \"" + rdr.GetFieldType(i) + "\"");
}
tmpInt = rdr.GetOrdinal("CBlob");
while (rdr.Read())
{
tmpString = "FromBlob.bmp";
if (File.Exists(tmpString))
{
File.Delete(tmpString);
}
if (!rdr.IsDBNull(tmpInt))
{
Blob = (byte[])rdr["CBlob"];
fs = new FileStream(tmpString, FileMode.Create);
fs.Write(Blob, 0, Blob.Length);
fs.Close();
}
tmpString = "FromBlob_1.bmp";
if (File.Exists(tmpString))
{
File.Delete(tmpString);
}
if (!rdr.IsDBNull(tmpInt))
{
tmpLong = rdr.GetBytes(tmpInt, 0, null, 0, int.MaxValue);
Blob = new byte[tmpLong];
rdr.GetBytes(tmpInt, 0, Blob, 0, Blob.Length);
fs = new FileStream(tmpString, FileMode.Create);
fs.Write(Blob, 0, Blob.Length);
fs.Close();
}
}
}
} while (rdr.NextResult());
rdr.Close();
#endif
cmd.Transaction.Rollback();
/*
* cn.ChangeDatabase("E:\\USI.Fil\\Base\\NODE.IB6");
* fstr_out.WriteLine(conn.Database);
*
* cmd.Transaction = conn.BeginTransaction(IsolationLevel.ReadCommitted);
*
* cmd.CommandType = CommandType.Text;
* cmd.CommandText = "select * from \"Filial\"";
* tmpDataTable.Reset();
* da.Fill(tmpDataTable);
*
* cmd.Transaction.Rollback();
*/
cmd = null;
cn.Close();
}
catch (Exception eException)
{
Console.WriteLine(eException.GetType().FullName + Environment.NewLine + "Message: " + eException.Message + Environment.NewLine + "StackTrace:" + Environment.NewLine + eException.StackTrace);
}
}
finally
{
if (rdr != null && !rdr.IsClosed)
{
rdr.Close();
}
if (cmd != null)
{
cmd.Transaction.Rollback();
}
if (cn != null && cn.State == ConnectionState.Open)
{
cn.Close();
}
if (fstr_out != null)
{
fstr_out.Close();
}
}
}
private void LoadExtraData(FbConnection cn, string name, string type)
{
try
{
int dialect = 1;
object o = null;
short scale = 0;
try
{
FbConnectionStringBuilder cnString = new FbConnectionStringBuilder(cn.ConnectionString);
dialect = cnString.Dialect;
}
catch {}
// AutoKey Data
Dictionary <string, object[]> autoKeyFields = new Dictionary <string, object[]>();
DataTable triggers = cn.GetSchema("Triggers", new string[] { null, null, name });
foreach (DataRow row in triggers.Rows)
{
int isSystemTrigger = Convert.ToInt32(row["IS_SYSTEM_TRIGGER"]);
int isInactive = Convert.ToInt32(row["IS_INACTIVE"]);
int triggerType = Convert.ToInt32(row["TRIGGER_TYPE"]);
if ((isSystemTrigger == 0) && (isInactive == 0) && (triggerType == 1))
{
string source = row["SOURCE"].ToString();
int end = 0;
do
{
string field = null, generatorName = string.Empty;
int tmp, increment = 1, seed = 0;
end = source.IndexOf("gen_id(", end, StringComparison.CurrentCultureIgnoreCase);
if (end >= 0)
{
string s = source.Substring(0, end);
int start = s.LastIndexOf(".");
if (start >= 0 && start < end)
{
field = s.Substring(start).Trim(' ', '.', '=').ToUpper();
}
int end2 = source.IndexOf(")", end);
string s2 = source.Substring(0, end2);
int start2 = s2.LastIndexOf(",");
if (start2 >= 0 && start2 < end2)
{
if (int.TryParse(s2.Substring(start2 + 1).Trim(' ', ','), out tmp))
{
increment = tmp;
}
s2 = s2.Substring(0, start2);
generatorName = s2.Substring(end + 7).Trim();
}
if (field != null)
{
autoKeyFields[field] = new object[] { increment, seed, generatorName };
}
end += 7;
}
} while (end != -1);
}
}
string select = "select r.rdb$field_name, f.rdb$field_scale AS SCALE, f.rdb$computed_source AS IsComputed, f.rdb$field_type as FTYPE, f.rdb$field_sub_type AS SUBTYPE, f.rdb$dimensions AS DIM from rdb$relation_fields r, rdb$types t, rdb$fields f where r.rdb$relation_name='" + name + "' and f.rdb$field_name=r.rdb$field_source and t.rdb$field_name='RDB$FIELD_TYPE' and f.rdb$field_type=t.rdb$type order by r.rdb$field_position;";
// Column Data
FbDataAdapter adapter = new FbDataAdapter(select, cn);
DataTable dataTable = new DataTable();
adapter.Fill(dataTable);
// Dimension Data
string dimSelect = "select r.rdb$field_name AS Name , d.rdb$dimension as DIM, d.rdb$lower_bound as L, d.rdb$upper_bound as U from rdb$fields f, rdb$field_dimensions d, rdb$relation_fields r where r.rdb$relation_name='" + name + "' and f.rdb$field_name = d.rdb$field_name and f.rdb$field_name=r.rdb$field_source order by d.rdb$dimension;";
FbDataAdapter dimAdapter = new FbDataAdapter(dimSelect, cn);
DataTable dimTable = new DataTable();
dimAdapter.Fill(dimTable);
if (this._array.Count > 0)
{
Column col = this._array[0] as Column;
f_TypeName = new DataColumn("TYPE_NAME", typeof(string));
col._row.Table.Columns.Add(f_TypeName);
f_TypeNameComplete = new DataColumn("TYPE_NAME_COMPLETE", typeof(string));
col._row.Table.Columns.Add(f_TypeNameComplete);
this.f_IsComputed = new DataColumn("IS_COMPUTED", typeof(bool));
col._row.Table.Columns.Add(f_IsComputed);
short ftype = 0;
short dim = 0;
DataRowCollection rows = dataTable.Rows;
int count = this._array.Count;
Column c = null;
for (int index = 0; index < count; index++)
{
c = (Column)this[index];
if (autoKeyFields.ContainsKey(c.Name.ToUpper()))
{
c._row["IS_AUTO_KEY"] = true;
c._row["AUTO_KEY_INCREMENT"] = autoKeyFields[c.Name][0];
c._row["AUTO_KEY_SEED"] = autoKeyFields[c.Name][1];
c._row["AUTO_KEY_SEQUENCE"] = autoKeyFields[c.Name][2];
}
if (!c._row.IsNull("DOMAIN_NAME"))
{
// Special Hack, if there is a domain
c._row["TYPE_NAME"] = c._row["DOMAIN_NAME"] as string;
c._row["TYPE_NAME_COMPLETE"] = c._row["DOMAIN_NAME"] as string;
o = rows[index]["IsComputed"];
if (o != DBNull.Value)
{
c._row["IS_COMPUTED"] = true;
}
scale = (short)rows[index]["SCALE"];
if (scale < 0)
{
c._row["NUMERIC_SCALE"] = Math.Abs(scale);
}
continue;
}
try
{
string bigint = c._row["COLUMN_DATA_TYPE"] as string;
if (bigint.ToUpper() == "BIGINT")
{
c._row["TYPE_NAME"] = "BIGINT";
c._row["TYPE_NAME_COMPLETE"] = "BIGINT";
continue;
}
}
catch {}
// public const int blr_text = 14;
// public const int blr_text2 = 15;
// public const int blr_short = 7;
// public const int blr_long = 8;
// public const int blr_quad = 9;
// public const int blr_int64 = 16;
// public const int blr_float = 10;
// public const int blr_double = 27;
// public const int blr_d_float = 11;
// public const int blr_timestamp = 35;
// public const int blr_varying = 37;
// public const int blr_varying2 = 38;
// public const int blr_blob = 261;
// public const int blr_cstring = 40;
// public const int blr_cstring2 = 41;
// public const int blr_blob_id = 45;
// public const int blr_sql_date = 12;
// public const int blr_sql_time = 13;
// Step 1: DataTypeName
ftype = (short)rows[index]["FTYPE"];
switch (ftype)
{
case 7:
c._row["TYPE_NAME"] = "SMALLINT";
break;
case 8:
c._row["TYPE_NAME"] = "INTEGER";
break;
case 9:
c._row["TYPE_NAME"] = "QUAD";
break;
case 10:
c._row["TYPE_NAME"] = "FLOAT";
break;
case 11:
c._row["TYPE_NAME"] = "DOUBLE PRECISION";
break;
case 12:
c._row["TYPE_NAME"] = "DATE";
break;
case 13:
c._row["TYPE_NAME"] = "TIME";
break;
case 14:
c._row["TYPE_NAME"] = "CHAR";
break;
case 16:
c._row["TYPE_NAME"] = "NUMERIC";
break;
case 27:
c._row["TYPE_NAME"] = "DOUBLE PRECISION";
break;
case 35:
if (dialect > 2)
{
c._row["TYPE_NAME"] = "TIMESTAMP";
}
else
{
c._row["TYPE_NAME"] = "DATE";
}
break;
case 37:
c._row["TYPE_NAME"] = "VARCHAR";
break;
case 40:
c._row["TYPE_NAME"] = "CSTRING";
break;
case 261:
short subtype = (short)rows[index]["SUBTYPE"];
switch (subtype)
{
case 0:
c._row["TYPE_NAME"] = "BLOB(BINARY)";
break;
case 1:
c._row["TYPE_NAME"] = "BLOB(TEXT)";
break;
default:
c._row["TYPE_NAME"] = "BLOB(UNKNOWN)";
break;
}
break;
}
scale = (short)rows[index]["SCALE"];
if (scale < 0)
{
c._row["TYPE_NAME"] = "NUMERIC";
c._row["NUMERIC_SCALE"] = Math.Abs(scale);
}
o = rows[index]["IsComputed"];
if (o != DBNull.Value)
{
c._row["IS_COMPUTED"] = true;
}
// Step 2: DataTypeNameComplete
string s = c._row["TYPE_NAME"] as string;
switch (s)
{
case "VARCHAR":
case "CHAR":
c._row["TYPE_NAME_COMPLETE"] = s + "(" + c.CharacterMaxLength + ")";
break;
case "NUMERIC":
switch ((int)c._row["COLUMN_SIZE"])
{
case 2:
c._row["TYPE_NAME_COMPLETE"] = s + "(4, " + c.NumericScale.ToString() + ")";
break;
case 4:
c._row["TYPE_NAME_COMPLETE"] = s + "(9, " + c.NumericScale.ToString() + ")";
break;
case 8:
c._row["TYPE_NAME_COMPLETE"] = s + "(15, " + c.NumericScale.ToString() + ")";
break;
default:
c._row["TYPE_NAME_COMPLETE"] = "NUMERIC(18,0)";
break;
}
break;
case "BLOB(TEXT)":
case "BLOB(BINARY)":
c._row["TYPE_NAME_COMPLETE"] = "BLOB";
break;
default:
c._row["TYPE_NAME_COMPLETE"] = s;
break;
}
s = c._row["TYPE_NAME_COMPLETE"] as string;
dim = 0;
o = rows[index]["DIM"];
if (o != DBNull.Value)
{
dim = (short)o;
}
if (dim > 0)
{
dimTable.DefaultView.RowFilter = "Name = '" + c.Name + "'";
dimTable.DefaultView.Sort = "DIM";
string a = "[";
bool bFirst = true;
foreach (DataRowView vrow in dimTable.DefaultView)
{
DataRow row = vrow.Row;
if (!bFirst)
{
a += ",";
}
a += row["L"].ToString() + ":" + row["U"].ToString();
bFirst = false;
}
a += "]";
c._row["TYPE_NAME_COMPLETE"] = s + a;
c._row["TYPE_NAME"] = c._row["TYPE_NAME"] + ":A";
}
}
}
}
catch (Exception ex)
{
string e = ex.Message;
}
}
public static void Connect()
{
#region constant literals
// Set the ServerType to 1 for connect to the embedded server
string connectionString =
"User=sysdba;" +
"Password=masterkey;" +
"Database=D:\\Work\\IXP220\\IXPConnector\\IXPConnector\\FirebirdDB\\DB220.FDB;" +
"DataSource=localhost;" +
"Port=3050;" +
"Dialect=3;" +
"Charset=NONE;" +
"Role=;" +
"Connection lifetime=15;" +
"Pooling=true;" +
"MinPoolSize=0;" +
"MaxPoolSize=50;" +
"Packet Size=8192;" +
"ServerType=0";
//D:\Work\IXP220\IXPConnector\IXPConnector\FirebirdDB\DB220.FDB
#endregion
try
{
FbConnection con = new FbConnection();
con.ConnectionString = connectionString;
con.Open();
Console.WriteLine("You are now connected! \r\n");
//var commandString = "SELECT * FROM MASTER WHERE MST_SQ like '1'";
var commandString = "SELECT MASTER.MST_LAST_NAME, TRANSACK.TR_DATE, TRANSACK.TR_TIME, EVENT_TYPE.ET_DESC"
+ " FROM TRANSACK"
+ " INNER JOIN MASTER"
+ " ON TRANSACK.TR_MSTSQ=MASTER.MST_SQ"
+ " INNER JOIN EVENT_TYPE"
+ " ON TRANSACK.TR_EVENT=EVENT_TYPE.ET_TYPENO"
+ " WHERE MASTER.MST_SQ like '1'";
/* var commandString = "SELECT MASTER.MST_LAST_NAME, TRANSACK.TR_DATE, TRANSACK.TR_TIME, EVENT_TYPE.ET_DESC"
+ " FROM TRANSACK"
+ " INNER JOIN MASTER"
+ " ON TRANSACK.TR_MSTSQ=MASTER.MST_SQ"
+ " INNER JOIN EVENT_TYPE"
+ " ON TRANSACK.TR_EVENT=EVENT_TYPE.ET_TYPENO"
+ " WHERE MASTER.MST_SQ like '21'";*/
//gets the names of the users - SELECT MST_FIRST_NAME, MST_LAST_NAME, MST_SQ FROM MASTER
//BIO_TAG
//REPORT_TRANSACK
//COLUMN_DETAIL
//CONFIGURATION
//DEPARTMENT S_ID, DPT_NO, DPT_NAME,
//EVENT_TYPE
//REASON - RN_NO (1,2,11) / RN_DESC (Работна среща, Лична Работа, Изход)
//STATUS_TRANSACK
//TAG
//TAG_T_A_G
//TRANSACK
//MASTER - holds the names of the users, MST_EMAIL, DPT_NO, MST_FIRST_NAME, MST_LAST_NAME, MST_SQ
//MASTER_SITE - S_ID, MST_SQ_CTRL, MST_SQ
var transaction = con.BeginTransaction(IsolationLevel.Serializable);
FbCommand command = new FbCommand(commandString, con, transaction);
command.ExecuteNonQuery();
IDataReader reader = command.ExecuteReader();
Console.WriteLine(commandString + " returns");
while (reader.Read())
{
for (int i = 0; i < reader.FieldCount; i++)
{
if (true)
{
}
Console.Write(reader.GetValue(i) + "\t");
}
Console.WriteLine();
}
transaction.Commit();
command.Dispose(); // Thus!
transaction.Dispose();
// Get the available metadata Collection names
DataTable metadataCollections = con.GetSchema();
//foreach (DataColumn row in metadataCollections.Columns)
//{
// foreach (object item in row.ColumnName)
// {
// if (item is int)
// {
// Console.Write("Int: {0} |", item);
// }
// else if (item is string)
// {
// Console.Write("String: {0} |", item);
// }
// else if (item is DateTime)
// {
// Console.Write("DateTime: {0} |", item);
// }
// Console.WriteLine();
// }
// Console.WriteLine(row.Table.Columns.Count);
//}
// Get datatype information
DataTable dataTypes = con.GetSchema(DbMetaDataCollectionNames.DataTypes);
// Get DataSource Information
DataTable dataSourceInformation = con.GetSchema(DbMetaDataCollectionNames.DataSourceInformation);
// Get available reserved word
DataTable reservedWords = con.GetSchema(DbMetaDataCollectionNames.ReservedWords);
// Get the list of User Tables
// Restrictions:
// TABLE_CATALOG
// TABLE_SCHEMA
// TABLE_NAME
// TABLE_TYPE
DataTable userTables = con.GetSchema("Tables", new string[] { null, null, null, "TABLE" });
// Get the list of System Tables
// Restrictions:
// TABLE_CATALOG
// TABLE_SCHEMA
// TABLE_NAME
// TABLE_TYPE
DataTable systemTables = con.GetSchema("Tables", new string[] { null, null, null, "SYSTEM TABLE" });
// Get Table Columns
// Restrictions:
// TABLE_CATALOG
// TABLE_SCHEMA
// TABLE_NAME
// COLUMN_NAME
DataTable tableColumns = con.GetSchema("Columns", new string[] { null, null, "TableName" });
con.Close();
Console.WriteLine("The connection was closed.");
}
catch (Exception)
{
Console.WriteLine("Couldn't open the connection. Try Again!" + " This is the connection string: " + connectionString);
return;
}
}
public override void RecreateDataBase()
{
// ConnectionString Builder
FbConnectionStringBuilder csb = new FbConnectionStringBuilder();
csb.DataSource = "localhost";
csb.Dialect = 3;
csb.Charset = "UTF8";
csb.Pooling = false;
csb.UserID = "SYSDBA"; // default user
csb.Password = "******"; // default password
string serverConnectionString = csb.ToString();
csb.Database = csb.Database = FQDBFile;
string databaseConnectionString = csb.ToString();
Console.WriteLine("-------------------------");
Console.WriteLine("Using Firebird Database ");
Console.WriteLine("-------------------------");
base.RecreateDataBase();
// Create simple user
FbSecurity security = new FbSecurity();
security.ConnectionString = serverConnectionString;
var userData = security.DisplayUser(FbUserName);
if (userData == null)
{
userData = new FbUserData();
userData.UserName = FbUserName;
userData.UserPassword = FbUserPass;
security.AddUser(userData);
}
// Try to shutdown & delete database
if (File.Exists(FQDBFile))
{
FbConfiguration configuration = new FbConfiguration();
configuration.ConnectionString = databaseConnectionString;
try
{
configuration.DatabaseShutdown(FbShutdownMode.Forced, 0);
Thread.Sleep(1000);
}
finally
{
File.Delete(FQDBFile);
}
}
// Create the new DB
FbConnection.CreateDatabase(databaseConnectionString, 4096, true, true);
if (!File.Exists(FQDBFile))
{
throw new Exception("Database failed to create");
}
// Create the Schema
string script = @"
CREATE TABLE Users(
UserId integer PRIMARY KEY NOT NULL,
Name varchar(200),
Age integer,
DateOfBirth timestamp,
Savings decimal(10,5),
Is_Male smallint,
UniqueId char(38),
TimeSpan time,
TestEnum varchar(10),
HouseId integer,
SupervisorId integer
);
CREATE TABLE ExtraUserInfos(
ExtraUserInfoId integer PRIMARY KEY NOT NULL,
UserId integer NOT NULL,
Email varchar(200),
Children integer
);
CREATE TABLE Houses(
HouseId integer PRIMARY KEY NOT NULL,
Address varchar(200)
);
CREATE TABLE CompositeObjects(
Key1ID integer PRIMARY KEY NOT NULL,
Key2ID integer NOT NULL,
Key3ID integer NOT NULL,
TextData varchar(512),
DateEntered timestamp NOT NULL,
DateUpdated timestamp
);
CREATE GENERATOR USERS_USERID_GEN;
CREATE GENERATOR EXTRAUSERINFOS_ID_GEN;
CREATE GENERATOR HOUSES_HOUSEID_GEN;
SET TERM ^ ;
CREATE TRIGGER BI_USERS_USERID FOR USERS
ACTIVE BEFORE INSERT
POSITION 0
AS
BEGIN
IF (NEW.USERID IS NULL) THEN
NEW.USERID = GEN_ID(USERS_USERID_GEN, 1);
END^
CREATE TRIGGER BI_EXTRAUSERINFOS_ID1 FOR EXTRAUSERINFOS
ACTIVE BEFORE INSERT
POSITION 0
AS
BEGIN
IF (NEW.EXTRAUSERINFOID IS NULL) THEN
NEW.EXTRAUSERINFOID = GEN_ID(EXTRAUSERINFOS_ID_GEN, 1);
END^
CREATE TRIGGER BI_HOUSES_HOUSEID FOR HOUSES
ACTIVE BEFORE INSERT
POSITION 0
AS
BEGIN
IF (NEW.HOUSEID IS NULL) THEN
NEW.HOUSEID = GEN_ID(HOUSES_HOUSEID_GEN, 1);
END^
SET TERM ; ^
CREATE ROLE %role%;
GRANT SELECT, UPDATE, INSERT, DELETE ON Users TO ROLE %role%;
GRANT SELECT, UPDATE, INSERT, DELETE ON ExtraUserInfos TO ROLE %role%;
GRANT SELECT, UPDATE, INSERT, DELETE ON Houses TO ROLE %role%;
GRANT SELECT, UPDATE, INSERT, DELETE ON CompositeObjects TO ROLE %role%;
GRANT %role% TO %user%;
".Replace("%role%", FbRole).Replace("%user%", FbUserName);
/*
* Using new connection so that when a transaction is bound to Connection if it rolls back
* it doesn't blow away the tables
*/
using (var conn = new FbConnection(databaseConnectionString))
{
FbScript fbScript = new FbScript(script);
fbScript.Parse();
FbBatchExecution fbBatch = new FbBatchExecution(conn, fbScript);
fbBatch.Execute(true);
conn.Open();
Console.WriteLine("Tables (CreateDB): " + Environment.NewLine);
var dt = conn.GetSchema("Tables", new[] { null, null, null, "TABLE" });
foreach (DataRow row in dt.Rows)
{
Console.WriteLine(row[2]);
}
conn.Close();
}
}