#pragma warning restore 649
public override void CreateDatabase(string constr)
{
var csb = new FbConnectionStringBuilder(constr) { Pooling = false };
FbConnection.CreateDatabase(csb.ConnectionString, 16384, false, true);
using (var con = new FbConnection(csb.ConnectionString))
using (var cmd = con.CreateCommand())
{
con.Open();
#region bug drug block
//cmd.CommandText = @"CREATE TABLE crdb (tid INTEGER, name CHAR(120));";
//cmd.ExecuteScalar();
//cmd.CommandText = @"DROP TABLE crdb;";
//cmd.ExecuteScalar();
#endregion
#region init actions: register udf functions
cmd.CommandText =
@"
DECLARE EXTERNAL FUNCTION strlen
CSTRING(32767)
RETURNS INTEGER BY VALUE
ENTRY_POINT 'IB_UDF_strlen' MODULE_NAME 'ib_udf';";
cmd.ExecuteScalar();
#endregion
}
}
public static void TestDatabaseConnection(Object sender, TestDatabaseConnectionEventArgs e)
{
using (var connection = new FbConnection(GetConnectionString(e.DatabaseSettings)))
{
connection.Open();
}
}
public FbDatabase(IContainerSettings settings)
{
var set = (FbDatabaseSettings)settings;
_conw = new FbConnection(set.ConnectionString);
try
{
_conw.Open();
SwitchTrigger("MD5_LIST_FROM_EMPTY", UpdateEmptyList() > 0);
}
catch (FbException e)
{
if (e.ErrorCode == 335544344)
{
if (System.Windows.Forms.MessageBox.Show("File not exists" + (set.isEmbedded ? " or database is opened" : string.Empty) + ".\r\nTry to create file?", "Error", MessageBoxButtons.YesNo) == DialogResult.No)
throw;
CreateDb(_conw.ConnectionString, Path.Combine(Helpers.AssemblyDirectory(Assembly.GetExecutingAssembly()), "struct.sql"));
_conw.Open();
}
else
throw;
}
if (_conw.State == System.Data.ConnectionState.Closed)
_conw.Open();
_CurrentSettings = (FbDatabaseSettings)set.Clone();
_conr = new FbConnection(set.ConnectionString);
_conr.Open();
InitTagsLength();
StoppedEvent.Set();
}
private void btnCheck_Click(object sender, EventArgs e)
{
try
{
if (txtPath.Text.Split('@').Length == 2)
{
FbConnectionStringBuilder cnString = new FbConnectionStringBuilder();
cnString.DataSource = txtPath.Text.Split('@')[0];
cnString.Database = txtPath.Text.Split('@')[1];
cnString.UserID = "SYSDBA";
cnString.Password = "******";
cnString.Charset = "win1251";
cnString.Dialect = 3;
using (FbConnection cn = new FbConnection(cnString.ToString()))
{
cn.Open();
FbCommand cmd = new FbCommand("SELECT PRICE.* FROM PRICE", cn);
cmd.ExecuteNonQuery();
}
MessageBox.Show("ok");
}
else
{
MessageBox.Show("Не верный формат строки!");
}
}
catch(Exception ex)
{
MessageBox.Show(ex.Message + "\n" + ex.Source);
}
}
public static bool ContratoTemItens(string contrato)
{
bool retorno = false;
conn = AcessoDados.AcessoDados.getConn();
FbCommand comando = new FbCommand("select COUNT(*) as contador from sci_licitacao_contrato_itens lci where lci.ctrcod = @CONTRATO",conn);
FbParameter IDCONTRATO = new FbParameter("@CONTRATO", FbDbType.Integer);
IDCONTRATO.Value = contrato;
comando.Parameters.Add(IDCONTRATO);
conn.Open();
FbDataReader dr = comando.ExecuteReader();
while (dr.Read()) {
if (Convert.ToInt16(dr["contador"]) > 0)
{
retorno = true;
}
else {
retorno = false;
}
}
conn.Close();
comando.Dispose();
return retorno;
}
public static bool ExecuteBatchScript(
string connectionString,
string pathToScriptFile)
{
FbScript script = new FbScript(pathToScriptFile);
if (script.Parse() > 0)
{
using (FbConnection connection = new FbConnection(connectionString))
{
connection.Open();
try
{
FbBatchExecution batch = new FbBatchExecution(connection, script);
batch.Execute(true);
}
catch (FbException ex)
{
log.Error(ex);
throw new Exception(pathToScriptFile, ex);
}
finally
{
connection.Close();
}
}
}
return true;
}
public belEscrituracao(belInfNFe pInfNFe, FbConnection pfbConexao)
{
bProdutorRural = false;
objInfNFe = pInfNFe;
string scdClifor = string.Empty;
Conexao = pfbConexao;//MontaConexaoEscritor();
if (objInfNFe.Empresa != null)
{
string sTipoLanc = TipoLancamento();
string sDoc = string.Empty;
#region Clientes/Fornecedores
if (objInfNFe.BelDest.Cnpj != null)
{
sDoc = FormataString(objInfNFe.BelDest.Cnpj.ToString(), "CNPJ");
}
else
{
sDoc = FormataString(objInfNFe.BelDest.Cpf.ToString(), "CPF");
}
if (!RegistroExiste("CLIFOR", (objInfNFe.BelDest.Cnpj != null ? "CD_CGC = '" : "CD_CPF ='") + sDoc + "'", "CD_CLIFOR"))
{
scdClifor = CadastraCliFor(sDoc);
}
else
{
scdClifor = BuscaCodigoClifor(sDoc);
}
//OS_28165
String sQuery = string.Format("UPDATE CLIFOR SET CD_MUNIC = '{0}' WHERE CD_CLIFOR = '{1}'", objInfNFe.BelDest.Cmun, scdClifor);
using (FbCommand cmd = new FbCommand(sQuery, Conexao))
{
if (Conexao.State != ConnectionState.Open)
{
Conexao.Open();
}
cmd.ExecuteNonQuery();
}
objInfNFe.Cdclifor = scdClifor;
#endregion
bool bSaida = true; // NotaSaida();
if (sTipoLanc == "E")
{
bSaida = false;
}
Escritura(bSaida);
}
}
public static string RetornaProximoValorGenerator(string sNomeGen)
{
FbConnection con = new FbConnection(ConfigurationManager.ConnectionStrings["ConnectionStringFB"].ConnectionString.ToString());
try
{
StringBuilder sQuery = new StringBuilder();
sQuery.Append("Select ");
sQuery.Append("gen_id(" + sNomeGen + ",1) ");
sQuery.Append("from rdb$database ");
FbCommand command = new FbCommand(sQuery.ToString(), con);
con.Open();
return command.ExecuteScalar().ToString();
}
catch (Exception ex)
{
throw ex;
}
finally
{
con.Close();
}
}
private FbOptions GetSettings(string connectionString)
{
if (string.IsNullOrWhiteSpace(connectionString))
{
connectionString = Settings.Connection.ConnectionString;
}
if (ServerVersion != null)
{
return(this);
}
try
{
using (var connection = new Firebird.FbConnection(connectionString))
{
connection.Open();
ServerVersion = Data.FbServerProperties.ParseServerVersion(connection.ServerVersion);
using (var cmd = connection.CreateCommand())
{
cmd.CommandText = "SELECT MON$SQL_DIALECT FROM MON$DATABASE";
IsLegacyDialect = Convert.ToInt32(cmd.ExecuteScalar()) == 1;
}
connection.Close();
}
}
catch (Exception)
{
throw;
}
return(this);
}
//1 - exclusivo 0 - compartilhado
public static void VerificaProprietario(string nomeEntidade, FbConnection conn, ref bool empresa, ref bool filial)
{
ConfigurarDicionario();
if(conn.State == ConnectionState.Closed)
{
conn.Open();
}
using(FbCommand comando = conn.CreateCommand())
{
string nomeTabela = dicionario[nomeEntidade];
comando.CommandText = "SELECT ACESSOEMPRESA, ACESSOFILIAL FROM SYS_TABLES WHERE ENTIDADE = @ENTIDADE";
comando.Parameters.AddWithValue("@ENTIDADE", nomeTabela);
using (FbDataReader leitor = comando.ExecuteReader())
{
if (leitor.HasRows)
{
int indexEmpresa = leitor.GetOrdinal("ACESSOEMPRESA");
int indexFilial = leitor.GetOrdinal("ACESSOFILIAL");
while (leitor.Read())
{
empresa = Convert.ToBoolean(leitor.GetInt32(indexEmpresa));
filial = Convert.ToBoolean(leitor.GetInt32(indexFilial));
}
}
}
}
}
private void CreateTestTablesFb2(string connectionString)
{
using (var con = new FbConnection(connectionString))
using (var command = con.CreateCommand())
{
var sb = new StringBuilder();
sb.Append("recreate table test(");
sb.Append("int_test integer default 0 not null primary key,");
sb.Append("bigint_test bigint,");
sb.Append("blob_test blob,");
sb.Append("char_test char(20),");
sb.Append("date_test date,");
sb.Append("decimal_test decimal,");
sb.Append("double_test double precision,");
sb.Append("float_test float,");
sb.Append("numeric_test numeric,");
sb.Append("smallint_test smallint,");
sb.Append("time_test time,");
sb.Append("timestamp_test timestamp,");
sb.Append("varchar_test varchar(100)");
sb.Append(")");
command.CommandText = sb.ToString();
con.Open();
command.ExecuteNonQuery();
}
}
public DataTable RetornaNomeUsuarioCadastrado(string prontuario)
{
FbConnection conex = new FbConnection(conexao);
string query = "SELECT * FROM TBUSUARIO WHERE CDUSUARIO = '"+prontuario+"'";
FbCommand comando = new FbCommand(query, conex);
try
{
conex.Open();
FbDataAdapter datareader = new FbDataAdapter(comando);
DataTable usuarios = new DataTable();
datareader.Fill(usuarios);
return usuarios;
}
catch (Exception err)
{
throw err;
}
finally
{
conex.Close();
}
}
public static bool check_func(FbConnectionStringBuilder fc)
{
bool res_ = false;
using (FbConnection fb = new FbConnection(fc.ConnectionString))
{
try
{
fb.Open();
using (FbTransaction ft = fb.BeginTransaction())
{
using (FbCommand fcon = new FbCommand(sql_func,fb,ft))
{
using (FbDataReader fr = fcon.ExecuteReader())
{
while (fr.Read())
{
res_ = true;
}
fr.Dispose();
}
fcon.Dispose();
}
ft.Commit();
ft.Dispose();
}
}
catch { }
finally { fb.Close(); }
fb.Dispose();
}
return res_;
}
static int Main(string[] args)
{
int i = 0;
int max = args.Length < 1 ? 1 : int.Parse(args[0]);
while (i < max)
{
var connectionBuilder = new FbConnectionStringBuilder();
connectionBuilder.DataSource = "localhost";
connectionBuilder.Database = @"D:\db\Hoge2.FDB";
connectionBuilder.Charset = FbCharset.Utf8.ToString();
connectionBuilder.UserID = "SYSDBA";
connectionBuilder.Password = "******";
connectionBuilder.ServerType = FbServerType.Embedded;
connectionBuilder.Pooling = false;
using (var connection = new FbConnection(connectionBuilder.ConnectionString))
{
connection.Open();
connection.Close();
}
i++;
}
UnloadDll(Path.Combine(Path.GetDirectoryName(Assembly.GetEntryAssembly().Location), @"fbembed.dll"));
return DateTime.Now.ToIntTime();
}
public override IDatabase Execute()
{
var db = Database;
FbScript script;
using (var s = GetType().Assembly.GetManifestResourceStream(ScriptResourceName))
{
using (var r = new StreamReader(s, Encoding.UTF8))
{
script = new FbScript(r.ReadToEnd());
script.Parse();
}
}
try
{
using (var con = new FbConnection(db.ConnectionString))
{
con.Open();
}
}
catch
{
FbConnection.CreateDatabase(db.ConnectionString);
}
using (var con = new FbConnection(db.ConnectionString))
{
var be = new FbBatchExecution(con);
be.AppendSqlStatements(script);
be.Execute();
}
return db;
}
override internal void LoadForTable()
{
try
{
FbConnection cn = new FirebirdSql.Data.FirebirdClient.FbConnection(this._dbRoot.ConnectionString);
cn.Open();
DataTable metaData = cn.GetSchema("Columns", new string[] { null, null, this.Table.Name });
DataColumn c;
if (!metaData.Columns.Contains("IS_AUTO_KEY"))
{
c = metaData.Columns.Add("IS_AUTO_KEY", typeof(Boolean)); c.DefaultValue = false;
}
if (!metaData.Columns.Contains("AUTO_KEY_SEED"))
{
c = metaData.Columns.Add("AUTO_KEY_SEED"); c.DefaultValue = 0;
}
if (!metaData.Columns.Contains("AUTO_KEY_INCREMENT"))
{
c = metaData.Columns.Add("AUTO_KEY_INCREMENT"); c.DefaultValue = 0;
}
if (!metaData.Columns.Contains("AUTO_KEY_SEQUENCE"))
{
c = metaData.Columns.Add("AUTO_KEY_SEQUENCE"); c.DefaultValue = string.Empty;
}
PopulateArray(metaData);
LoadExtraData(cn, this.Table.Name, "T");
cn.Close();
}
catch (Exception ex)
{
string m = ex.Message;
}
}
private string GetJson(string CommandText, string ParentNodeName, params FbParameter[] pars)
{
FbConnectionStringBuilder connString = TeeboaConnection.ConnectionString;
using (FbConnection conn = new FbConnection(connString.ToString()))
{
using (FbCommand cmd = conn.CreateCommand())
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = CommandText;
for (int i = 0; i < pars.Length; i++)
{
cmd.Parameters.Add(pars[i]);
}
try
{
conn.Open();
using (FbDataReader dr = cmd.ExecuteReader())
{
return ParentNodeName == string.Empty ? stringify(dr) : stringify(dr, ParentNodeName);
}
}
catch (Exception ex)
{
return string.Format("Message: {1}{0}Method: {2}{0}", "<br />", ex.Message, ex.TargetSite);
}
}
}
}
private static void Main(string[] args)
{
if (File.Exists("Data/database.fdb"))
{
File.Delete("Data/database.fdb");
}
FbConnection.CreateDatabase(GetConnectionString());
using (var conn = new FbConnection(GetConnectionString()))
{
conn.Open();
if (File.Exists("Data/database.sql"))
{
var file = new FileInfo("Data/database.sql");
string script = file.OpenText().ReadToEnd();
using (FbCommand createTable = conn.CreateCommand())
{
createTable.CommandText = script;
createTable.ExecuteNonQuery();
}
}
}
}
internal static void Add(MovieInfo movieInfo)
{
string commandText = @"INSERT INTO MOVIE_DATA (
LOCATION, FILE_DATA_X, FILE_DATA_Y, FILE_DATA_DURATION, RAW_TITLE_PATH)
VALUES (@location, @file_data_x, @file_data_y, @file_duration, @raw_title_path)";
using (FbConnection connection = new FbConnection (string.Format (CONNECTION_STRING_FORMAT_FIREBIRD, DB_FILENAME)))
{
connection.Open ();
using (FbTransaction transaction = connection.BeginTransaction ())
{
using (FbCommand cmd = new FbCommand (commandText, connection, transaction))
{
cmd.Parameters.Add (new FbParameter ("@location", movieInfo.DvdName));
cmd.Parameters.Add (new FbParameter ("@file_data_x", movieInfo.FileData.X));
cmd.Parameters.Add (new FbParameter ("@file_data_y", movieInfo.FileData.Y));
cmd.Parameters.Add (new FbParameter ("@file_duration", movieInfo.FileData.Duration));
cmd.Parameters.Add (new FbParameter ("@raw_title_path", movieInfo.RawTitlePath));
cmd.ExecuteNonQuery ();
}
transaction.Commit ();
}
}
}
public static bool abrir()
{
conexao = new FbConnection(stringConexao);
conexao.Open();
transacao = conexao.BeginTransaction();
return true;
}
override internal void LoadAll()
{
try
{
FbConnection cn = new FirebirdSql.Data.FirebirdClient.FbConnection(this._dbRoot.ConnectionString);
cn.Open();
string dbName = cn.Database;
cn.Close();
int index = dbName.LastIndexOfAny(new char[] { '\\' });
if (index >= 0)
{
dbName = dbName.Substring(index + 1);
}
// We add our one and only Database
FirebirdDatabase database = (FirebirdDatabase)this.dbRoot.ClassFactory.CreateDatabase();
database._name = dbName;
database.dbRoot = this.dbRoot;
database.Databases = this;
this._array.Add(database);
}
catch {}
}
public void BeginTrasactionTest()
{
string connectionString = this.BuildConnectionString();
FbConnection conn01 = new FbConnection(connectionString);
conn01.Open();
FbTransaction txn01 = conn01.BeginTransaction(IsolationLevel.Unspecified);
txn01.Rollback();
conn01.Close();
FbConnection conn02 = new FbConnection(connectionString);
conn02.Open();
FbTransaction txn02 = conn02.BeginTransaction(IsolationLevel.ReadCommitted);
txn02.Rollback();
conn02.Close();
FbConnection conn03 = new FbConnection(connectionString);
conn03.Open();
FbTransaction txn03 = conn03.BeginTransaction(IsolationLevel.ReadUncommitted);
txn03.Rollback();
conn03.Close();
FbConnection conn04 = new FbConnection(connectionString);
conn04.Open();
FbTransaction txn04 = conn04.BeginTransaction(IsolationLevel.RepeatableRead);
txn04.Rollback();
conn04.Close();
FbConnection conn05 = new FbConnection(connectionString);
conn05.Open();
FbTransaction txn05 = conn05.BeginTransaction(IsolationLevel.Serializable);
txn05.Rollback();
conn05.Close();
}
private string NomeArqCte()
{
FbConnection con = null;
try
{
string sNomeArq = "";
FbCommand cmd = new FbCommand();
con = new FbConnection(HlpDbFuncoesGeral.MontaStringConexao());
cmd.Connection = con;
con.Open();
cmd.CommandText = "SP_CHAVEPRI";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Clear();
cmd.Parameters.Add("@SNOMEGENERATOR", FbDbType.VarChar, 31).Value = "GEN_NOMEARQXML";
sNomeArq = cmd.ExecuteScalar().ToString();
return "Cte_" + sEmp + sNomeArq.PadLeft(15, '0') + ".xml";
}
catch (Exception ex)
{
throw ex;
}
finally
{
con.Close();
}
}
override internal void LoadAll()
{
try
{
FbConnection cn = new FirebirdSql.Data.FirebirdClient.FbConnection(this._dbRoot.ConnectionString);
cn.Open();
string dbName = cn.Database;
cn.Close();
int index = dbName.LastIndexOfAny(new char[]{'\\'});
if (index >= 0)
{
dbName = dbName.Substring(index + 1);
}
// We add our one and only Database
FirebirdDatabase database = (FirebirdDatabase)this.dbRoot.ClassFactory.CreateDatabase();
database._name = dbName;
database.dbRoot = this.dbRoot;
database.Databases = this;
this._array.Add(database);
}
catch {}
}
private void Form1_Load(object sender, EventArgs e)
{
try
{
cadena = "User=SYSDBA;Password=masterkey;Database=C:\\Users\\Juan\\Desktop\\SAMYK\\samyk\\database.FDB;Datasource=localhost;Port=3050";
conexion = new FbConnection(cadena);
conexion.Open();
}
catch (Exception ex)
{
cadena = "User=SYSDBA;Password=masterkey;Database=C:\\Users\\Ezequiel-Pc\\Desktop\\samyk\\samyk\\database.FDB;Datasource=localhost;Port=3050";
conexion = new FbConnection(cadena);
conexion.Open();
}
try
{
MessageBox.Show("conexion establecida");
DataTable dtDatos = new DataTable();
string qry = armarQry();
FbDataAdapter mdaDatos = new FbDataAdapter(qry, conexion);
mdaDatos.Fill(dtDatos);
dataGridView1.DataSource = dtDatos;
}
catch (Exception ex)
{
MessageBox.Show("errdfsdfsdfsdfsdfsdfsdor" + ex);
}
finally
{
}
}
public static DBSchema LoadSchema(string constr)
{
var csb = new FbConnectionStringBuilder(constr);
//csb.Pooling = false;
using (var con = new FbConnection(csb.ConnectionString))
{
con.Open();
var dbsc =
new DBSchema
{
Name = csb.Database,
Tables = GetTables(con)
};
foreach (var table in dbsc.Tables)
{
table.Keys = GetKeys(con, table).ToArray();
table.Indexes = GetIndexes(con, table).ToArray();
}
dbsc.Generators = GetGenerators(con);
return dbsc;
}
}
public tcDadosConstrucaoCivil RettcDadosConstrucaoCivil(FbConnection Conn, string sNota)
{
try
{
StringBuilder sQuery = new StringBuilder();
sQuery.Append("select coalesce(clifor.cd_art,'') Art, coalesce(clifor.cd_obra,'')CodigoObra {0}");
sQuery.Append("from nf inner join clifor on nf.cd_clifor = clifor.cd_clifor {0}");
sQuery.Append("where nf.cd_nfseq = '{1}' and nf.cd_empresa = '{2}' {0}");
string sQueryEnd = string.Format(sQuery.ToString(), Environment.NewLine, sNota, belStatic.codEmpresaNFe);
FbCommand cmd = new FbCommand(sQueryEnd, Conn);
Conn.Open();
FbDataReader dr = cmd.ExecuteReader();
objtcDadosConstrucaoCivil = new tcDadosConstrucaoCivil();
while (dr.Read())
{
objtcDadosConstrucaoCivil.Art = dr["Art"].ToString();
objtcDadosConstrucaoCivil.CodigoObra = dr["CodigoObra"].ToString();
}
}
catch (Exception)
{
throw;
}
finally { Conn.Close(); }
return objtcDadosConstrucaoCivil;
}
public Conexao_Banco(/*string _codigo*/)
{
InitializeComponent();
aux_codigo = tx_pesquisa.Text;
//this.aux_codigo = _codigo;
/*string caminho = @"C:\Sistema de Registro da Producao Ambulatorial\FBCADASTRO_AGENDA.FDB";
string conexao = "User=SYSDBA;"
+ "PassWord=masterkey;"
+ "DataBase=" + caminho;*/
//DRIVER=Firebird/InterBase(r) driver;UID=SYSDBA;PWD=masterkey; DBNAME=MyServer/3051:C:\database\myData.fdb;
// @"DataSource=localhost; Database=C:\PastaFireBird\BANCOFIREBIRD.FDB;";
//User=SYSDBA; Password=masterkey; Database=D:\Meus Documentos\Faculdade - Esucri\Programação avançada I\Delphi com Banco\Banco\BANCO.FDB; DataSource=192.168.0.100;Dialect=3;PacketSize = 4096;"
string conexao = @"User=SYSDBA;Password=masterkey;Database=C:\Sistema de Registro da Producao Ambulatorial\FBCADASTRO_AGENDA.FDB;DataSource=RECEPCAO;Port=3050;Dialect=3;PacketSize=4096";
_fbConexao = new FbConnection(conexao);
try
{
_fbConexao.Open();
}
catch (Exception erro)
{
MessageBox.Show(erro.ToString());
}
dtgrd_view.DataSource = _dtTable;
_fbConexao.Close();
}
public DataTable RetornaTabelaUsuariosCadastrados()
{
FbConnection conex = new FbConnection(conexao);
string query = "SELECT CDUSUARIO, DSUSUARIO, DSMAE, DTNASCIMENTO FROM TBUSUARIO";
FbCommand comando = new FbCommand(query, conex);
try
{
conex.Open();
FbDataAdapter datareader = new FbDataAdapter(comando);
DataTable usuarios = new DataTable();
datareader.Fill(usuarios);
return usuarios;
}
catch (Exception err)
{
throw err;
}
finally
{
conex.Close();
}
}
public void InsertTest()
{
FbConnectionStringBuilder csb = base.BuildConnectionStringBuilder();
csb.Enlist = true;
using (TransactionScope scope = new TransactionScope())
{
using (FbConnection c = new FbConnection(csb.ToString()))
{
c.Open();
string sql = "insert into TEST (int_field, date_field) values (1002, @date)";
using (FbCommand command = new FbCommand(sql, c))
{
command.Parameters.Add("@date", FbDbType.Date).Value = DateTime.Now.ToString();
int ra = command.ExecuteNonQuery();
Assert.AreEqual(ra, 1);
}
}
scope.Complete();
}
}
public void SimpleSelectTest()
{
FbConnectionStringBuilder csb = base.BuildConnectionStringBuilder();
csb.Enlist = true;
using (TransactionScope scope = new TransactionScope())
{
using (FbConnection c = new FbConnection(csb.ToString()))
{
c.Open();
using (FbCommand command = new FbCommand("select * from TEST where (0=1)", c))
{
using (FbDataReader r = command.ExecuteReader())
{
while (r.Read())
{
}
}
}
}
scope.Complete();
}
}
static void Main(string[] args)
{
var builder = new FbConnectionStringBuilder();
builder.DataSource = "localhost";
builder.Database = @"D:\DB\FB3_EXTERNAL_TEST.FDB";
builder.Charset = FbCharset.Utf8.ToString();
builder.UserID = "SYSDBA";
builder.ServerType = FbServerType.Embedded;
builder.ClientLibrary = @"fb\fbclient.dll";
//DB作成
if (!File.Exists(builder.Database))
{
FbConnection.CreateDatabase(builder.ConnectionString);
}
//create procesure
var dllPath = new Uri(new Uri(Assembly.GetEntryAssembly().Location), @"../../Fb/plugins/FbExternalSample.dll").LocalPath;
var createSqls = new FbHelper().GetCreateStatements(dllPath);
using (var con = new FbConnection(builder.ConnectionString))
using (var command = con.CreateCommand())
{
con.Open();
foreach (var sql in createSqls)
{
command.CommandText = sql;
command.ExecuteNonQuery();
}
}
//実行
var sqls = new[] { "SELECT * FROM HellowWorld('Taro')", "SELECT * FROM GetNumbers(5)", "SELECT * FROM GetDemo('やったぜ！')" };
using (var con = new FbConnection(builder.ConnectionString))
using (var command = con.CreateCommand())
{
con.Open();
foreach (var sql in sqls)
{
Console.WriteLine("- SQL -");
Console.WriteLine(sql);
Console.WriteLine("- 実行結果 -");
command.CommandText = sql;
var reader = command.ExecuteReader();
while (reader.Read())
{
var result = "";
for (var i = 0; i < reader.FieldCount; i++)
{
result += $" {reader[i]}";
}
Console.WriteLine(result);
}
Console.WriteLine("------------------------------------");
}
}
Console.Read();
}
public override void Connect_To_Database(StorageConfig config)
{
myConfig = config as SQLiteConfig;
if (myConfig == null)
{
throw new Exception("Database Config is NULL");
}
try
{
string connString = string.Format("Data Source={0};Pooling=true;FailIfMissing=false", myConfig.DatabaseFile);
myDBConn = new FbConnection(connString);
myDBConn.Open();
if (myDBConn.State != System.Data.ConnectionState.Open)
{
throw new Exception();
}
}
catch (FbException ex)
{
throw ex;
}
catch (Exception ex)
{
throw ex;
}
}
internal override void LoadAll()
{
try
{
var cn = new FbConnection(_dbRoot.ConnectionString);
cn.Open();
string dbName = cn.Database;
cn.Close();
int index = dbName.LastIndexOfAny(new[] {'\\'});
if (index >= 0)
{
dbName = dbName.Substring(index + 1);
}
// We add our one and only Database
var database = (FirebirdDatabase) dbRoot.ClassFactory.CreateDatabase();
database._name = dbName;
database.dbRoot = dbRoot;
database.Databases = this;
_array.Add(database);
}
catch {}
}
override internal void LoadAll()
{
try
{
FbConnection cn = new FirebirdSql.Data.FirebirdClient.FbConnection(this._dbRoot.ConnectionString);
cn.Open();
DataTable metaData = cn.GetSchema("Procedures", new string[] { this.Database.Name });
cn.Close();
PopulateArray(metaData);
}
catch (Exception ex)
{
string m = ex.Message;
}
}
override internal void LoadAll()
{
try
{
using (FbConnection cn = new FirebirdSql.Data.FirebirdClient.FbConnection(this._dbRoot.ConnectionString))
{
cn.Open();
DataTable idxMetaData = cn.GetSchema("Indexes", new string[] { null, null, this.Table.Name });
if (!idxMetaData.Columns.Contains("CARDINALITY"))
{
idxMetaData.Columns.Add("CARDINALITY");
}
if (!idxMetaData.Columns.Contains("COLUMN_NAME"))
{
idxMetaData.Columns.Add("COLUMN_NAME");
}
idxMetaData.Columns["IS_UNIQUE"].ColumnName = "UNIQUE";
idxMetaData.Columns["INDEX_TYPE"].ColumnName = "TYPE";
DataTable metaData = idxMetaData.Clone();
metaData.Clear();
foreach (DataRow row in idxMetaData.Rows)
{
string indexName = (string)row["INDEX_NAME"];
DataTable metaDataColumns = cn.GetSchema("IndexColumns", new string[] { null, null, null, indexName });
metaDataColumns.DefaultView.Sort = "ORDINAL_POSITION ASC";
foreach (DataRowView vrow in metaDataColumns.DefaultView)
{
DataRow newrow = metaData.Rows.Add(row.ItemArray);
newrow["CARDINALITY"] = vrow["ORDINAL_POSITION"];
newrow["COLUMN_NAME"] = vrow["COLUMN_NAME"];
}
}
cn.Close();
PopulateArray(metaData);
}
}
catch (Exception ex)
{
string m = ex.Message;
}
}
override internal void LoadForView()
{
try
{
FbConnection cn = new FirebirdSql.Data.FirebirdClient.FbConnection(this._dbRoot.ConnectionString);
cn.Open();
DataTable metaData = cn.GetSchema("Columns", new string[] { null, null, this.View.Name });
PopulateArray(metaData);
LoadExtraData(cn, this.View.Name, "V");
cn.Close();
}
catch (Exception ex)
{
string m = ex.Message;
}
}
override internal void LoadAll()
{
try
{
FbConnection cn = new FirebirdSql.Data.FirebirdClient.FbConnection(this._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;
}
}
override internal void LoadAll()
{
try
{
string type = this.dbRoot.ShowSystemData ? "SYSTEM TABLE" : "TABLE";
FbConnection cn = new FirebirdSql.Data.FirebirdClient.FbConnection(this._dbRoot.ConnectionString);
cn.Open();
DataTable metaData = cn.GetSchema("Tables", new string[] { null, null, null, type });
cn.Close();
PopulateArray(metaData);
}
catch (Exception ex)
{
string m = ex.Message;
}
}
override internal void LoadAll()
{
try
{
FbConnection cn = new FirebirdSql.Data.FirebirdClient.FbConnection(this._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;
}
}
private void FixupDataTypes(DataTable metaData)
{
FbConnection cn = null;
try
{
int dialect = 1;
cn = new FirebirdSql.Data.FirebirdClient.FbConnection(this._dbRoot.ConnectionString);
cn.Open();
try
{
FbConnectionStringBuilder cnString = new FbConnectionStringBuilder(cn.ConnectionString);
dialect = cnString.Dialect;
}
catch {}
int count = this._array.Count;
Parameter p = null;
if (count > 0)
{
// 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='" + this.Procedure.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);
p = this._array[0] as Parameter;
f_TypeName = new DataColumn("TYPE_NAME", typeof(string));
p._row.Table.Columns.Add(f_TypeName);
f_TypeNameComplete = new DataColumn("TYPE_NAME_COMPLETE", typeof(string));
p._row.Table.Columns.Add(f_TypeNameComplete);
short ftype = 0;
short dim = 0;
DataRowCollection rows = metaData.Rows;
for (int index = 0; index < count; index++)
{
p = (Parameter)this[index];
if (p._row["NUMERIC_PRECISION"] == System.DBNull.Value)
{
p._row["NUMERIC_PRECISION"] = p._row["PARAMETER_SIZE"];
}
p._row["PARAMETER_NAME"] = (p._row["PARAMETER_NAME"] as String).Trim();
int dir = (int)p._row["PARAMETER_TYPE"];
p._row["PARAMETER_TYPE"] = dir == 0 ? 1 : 3;
// Step 1: DataTypeName
ftype = (short)rows[index]["FIELD_TYPE"];
switch (ftype)
{
case 7:
p._row["TYPE_NAME"] = "SMALLINT";
break;
case 8:
p._row["TYPE_NAME"] = "INTEGER";
break;
case 9:
p._row["TYPE_NAME"] = "QUAD";
break;
case 10:
p._row["TYPE_NAME"] = "FLOAT";
break;
case 11:
p._row["TYPE_NAME"] = "DOUBLE PRECISION";
break;
case 12:
p._row["TYPE_NAME"] = "DATE";
break;
case 13:
p._row["TYPE_NAME"] = "TIME";
break;
case 14:
p._row["TYPE_NAME"] = "CHAR";
break;
case 16:
if (p.NumericScale < 0)
{
p._row["TYPE_NAME"] = "NUMERIC";
}
else
{
p._row["TYPE_NAME"] = "BIGINT";
}
break;
case 27:
p._row["TYPE_NAME"] = "DOUBLE PRECISION";
break;
case 35:
if (dialect > 2)
{
p._row["TYPE_NAME"] = "TIMESTAMP";
}
else
{
p._row["TYPE_NAME"] = "DATE";
}
break;
case 37:
p._row["TYPE_NAME"] = "VARCHAR";
break;
case 40:
p._row["TYPE_NAME"] = "CSTRING";
break;
case 261:
short subtype = (short)rows[index]["PARAMETER_SUB_TYPE"];
switch (subtype)
{
case 0:
p._row["TYPE_NAME"] = "BLOB(BINARY)";
break;
case 1:
p._row["TYPE_NAME"] = "BLOB(TEXT)";
break;
default:
p._row["TYPE_NAME"] = "BLOB(UNKNOWN)";
break;
}
break;
}
int scale = p.NumericScale;
if (scale < 0)
{
p._row["TYPE_NAME"] = "NUMERIC";
p._row["NUMERIC_SCALE"] = Math.Abs(scale);
}
// Step 2: DataTypeNameComplete
string s = p._row["TYPE_NAME"] as string;
switch (s)
{
case "VARCHAR":
case "CHAR":
p._row["TYPE_NAME_COMPLETE"] = s + "(" + p.CharacterMaxLength + ")";
break;
case "NUMERIC":
switch ((int)p._row["PARAMETER_SIZE"])
{
case 2:
p._row["TYPE_NAME_COMPLETE"] = s + "(4, " + p.NumericScale.ToString() + ")";
break;
case 4:
p._row["TYPE_NAME_COMPLETE"] = s + "(9, " + p.NumericScale.ToString() + ")";
break;
case 8:
p._row["TYPE_NAME_COMPLETE"] = s + "(15, " + p.NumericScale.ToString() + ")";
break;
default:
p._row["TYPE_NAME_COMPLETE"] = "NUMERIC(18,0)";
break;
}
break;
case "BLOB(TEXT)":
case "BLOB(BINARY)":
p._row["TYPE_NAME_COMPLETE"] = "BLOB";
break;
default:
p._row["TYPE_NAME_COMPLETE"] = s;
break;
}
s = p._row["TYPE_NAME_COMPLETE"] as string;
dim = 0;
object o = rows[index]["DIM"];
if (o != DBNull.Value)
{
dim = (short)o;
}
if (dim > 0)
{
dimTable.DefaultView.RowFilter = "Name = '" + p.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 += "]";
p._row["TYPE_NAME_COMPLETE"] = s + a;
p._row["TYPE_NAME"] = p._row["TYPE_NAME"] + ":A";
}
}
}
}
catch (Exception ex)
{
string e = ex.Message;
}
if (cn != null)
{
cn.Close();
}
}
override internal void LoadAll()
{
try
{
using (FbConnection cn = new FirebirdSql.Data.FirebirdClient.FbConnection(this._dbRoot.ConnectionString))
{
cn.Open();
if (allFkData == null)
{
allFkData = cn.GetSchema("ForeignKeys");
allFkData.Columns.Add("COLUMN_NAME");
allFkData.Columns.Add("REFERENCED_COLUMN_NAME");
allFkData.Columns.Add("ORDINAL_POSITION");
allFkData.Columns.Add("DEFERRABILITY");
mappingHash = new NameValueCollection();
mappingHash["FK_TABLE_CATALOG"] = "TABLE_CATALOG";
mappingHash["FK_TABLE_SCHEMA"] = "TABLE_SCHEMA";
mappingHash["FK_TABLE_NAME"] = "TABLE_NAME";
mappingHash["PK_TABLE_CATALOG"] = "REFERENCED_TABLE_CATALOG";
mappingHash["PK_TABLE_SCHEMA"] = "REFERENCED_TABLE_SCHEMA";
mappingHash["PK_TABLE_NAME"] = "REFERENCED_TABLE_NAME";
mappingHash["ORDINAL"] = "ORDINAL_POSITION";
mappingHash["UPDATE_RULE"] = "UPDATE_RULE";
mappingHash["DELETE_RULE"] = "DELETE_RULE";
mappingHash["PK_NAME"] = "INDEX_NAME";
mappingHash["FK_NAME"] = "CONSTRAINT_NAME";
mappingHash["DEFERRABILITY"] = "DEFERRABILITY";
}
DataTable metaData = allFkData.Clone();
metaData.Clear();
foreach (DataRow row in allFkData.Rows)
{
if ((this.Table.Name == (string)row["TABLE_NAME"]) ||
(this.Table.Name == (string)row["REFERENCED_TABLE_NAME"]))
{
string indexName = (string)row["INDEX_NAME"];
string refTableName = (string)row["REFERENCED_TABLE_NAME"];
string isDef = (string)row["IS_DEFERRABLE"];
string initDef = (string)row["INITIALLY_DEFERRED"];
row["DEFERRABILITY"] = (isDef == "NO" ? 3 : (initDef == "YES" ? 2 : 1));
DataTable metaDataColumns = cn.GetSchema("IndexColumns", new string[] { null, null, null, indexName });
metaDataColumns.DefaultView.Sort = "ORDINAL_POSITION ASC";
DataTable metaDataPKIndex = cn.GetSchema("Indexes", new string[] { null, null, refTableName });
metaDataPKIndex.DefaultView.RowFilter = "IS_PRIMARY = True";
string refPkIndexName = (string)metaDataPKIndex.DefaultView[0]["INDEX_NAME"];
DataTable metaDataColumnsRefPk = cn.GetSchema("IndexColumns", new string[] { null, null, null, refPkIndexName });
metaDataColumnsRefPk.DefaultView.Sort = "ORDINAL_POSITION ASC";
if (metaDataColumnsRefPk.Rows.Count == metaDataColumns.Rows.Count)
{
for (int i = 0; i < metaDataColumnsRefPk.Rows.Count; i++)
{
DataRow newrow = metaData.Rows.Add(row.ItemArray);
newrow["ORDINAL_POSITION"] = metaDataColumnsRefPk.DefaultView[i]["ORDINAL_POSITION"];
newrow["COLUMN_NAME"] = metaDataColumns.DefaultView[i]["COLUMN_NAME"];
newrow["REFERENCED_COLUMN_NAME"] = metaDataColumnsRefPk.DefaultView[i]["COLUMN_NAME"];
}
}
}
}
cn.Close();
PopulateArrayNoHookup(metaData, mappingHash);
ForeignKey key = null;
string keyName = "";
foreach (DataRow row in metaData.Rows)
{
keyName = row["CONSTRAINT_NAME"] as string;
key = this.GetByName(keyName);
key.AddForeignColumn(null, null, (string)row["TABLE_NAME"], (string)row["COLUMN_NAME"], false);
key.AddForeignColumn(null, null, (string)row["REFERENCED_TABLE_NAME"], (string)row["REFERENCED_COLUMN_NAME"], true);
}
}
}
catch (Exception ex)
{
string m = ex.Message;
}
}
protected override bool _internal_Write()
{
//Look if the Connection is still open..
try
{
string sql = "SELECT id FROM " + dataTable + " WHERE id = 0";
myCmd = new FbCommand(sql, myDBConn);
myCmd.ExecuteNonQuery();
}
catch (Exception)
{
myDBConn.Close(); //Verbindung schließen!
myDBConn.Open();
if (myDBConn.State != System.Data.ConnectionState.Open)
{
Logging.LogText("Error ReConnecting to Database! Dataset:" + datasetConfig.Name, Logging.LogLevel.Error);
return(false);
}
myDBConn.ChangeDatabase(myConfig.DatabaseFile);
}
//Add the Fields to the Database
myCmd = new FbCommand(insertCommand, myDBConn);
for (int n = 0; n < _maxAdd; n++)
//foreach (IEnumerable<object> values in _intValueList)
{
myCmd.Parameters.Clear();
IEnumerable <object> values = _intValueList[n];
using (IEnumerator <DatasetConfigRow> e1 = fieldList.GetEnumerator())
using (IEnumerator <object> e2 = values.GetEnumerator())
{
while (e1.MoveNext() && e2.MoveNext())
{
//foreach (DatasetConfigRow field in fieldList)
//{
DatasetConfigRow field = e1.Current;
Object value = e2.Current; //values[fnr++];
switch (field.PLCTag.TagDataType)
{
case DotNetSiemensPLCToolBoxLibrary.DataTypes.TagDataType.LInt:
case DotNetSiemensPLCToolBoxLibrary.DataTypes.TagDataType.LWord:
case DotNetSiemensPLCToolBoxLibrary.DataTypes.TagDataType.Int:
case DotNetSiemensPLCToolBoxLibrary.DataTypes.TagDataType.Dint:
case DotNetSiemensPLCToolBoxLibrary.DataTypes.TagDataType.Word:
case DotNetSiemensPLCToolBoxLibrary.DataTypes.TagDataType.Dword:
case DotNetSiemensPLCToolBoxLibrary.DataTypes.TagDataType.Byte:
case DotNetSiemensPLCToolBoxLibrary.DataTypes.TagDataType.BCDByte:
case DotNetSiemensPLCToolBoxLibrary.DataTypes.TagDataType.BCDWord:
case DotNetSiemensPLCToolBoxLibrary.DataTypes.TagDataType.BCDDWord:
myCmd.Parameters.Add(new FbParameter("@" + field.DatabaseField, System.Data.DbType.String)
{
Value = value.ToString()
});
break;
case DotNetSiemensPLCToolBoxLibrary.DataTypes.TagDataType.Float:
case DotNetSiemensPLCToolBoxLibrary.DataTypes.TagDataType.LReal:
myCmd.Parameters.Add(new FbParameter("@" + field.DatabaseField, System.Data.DbType.String)
{
Value = value.ToString().Replace(',', '.')
});
break;
case DotNetSiemensPLCToolBoxLibrary.DataTypes.TagDataType.DateTime:
myCmd.Parameters.Add(new FbParameter("@" + field.DatabaseField, System.Data.DbType.String)
{
Value = ((DateTime)value).ToString("dd-MM-yyyy HH:mm:ss")
});
break;
case DotNetSiemensPLCToolBoxLibrary.DataTypes.TagDataType.String:
case DotNetSiemensPLCToolBoxLibrary.DataTypes.TagDataType.CharArray:
myCmd.Parameters.Add(new FbParameter("@" + field.DatabaseField, System.Data.DbType.String)
{
Value = (String)value
});
break;
}
}
}
int tryCounter = 0;
nomol:
try
{
myCmd.ExecuteNonQuery();
}
catch (Exception ex)
{
throw ex;
}
}
/*
* //Ringpufferarchiv...
* if (myProtokollDaten.MaxDatasets > 0)
* {
* string delstr = "DELETE FROM " + myProtokollDaten.DataBaseTable + " WHERE id <= (SELECT max(id) FROM " + myProtokollDaten.DataBaseTable + ") - (" + myProtokollDaten.MaxDatasets.ToString() + ")";
* myCmd.CommandText = delstr;
* myCmd.ExecuteNonQuery();
* }
*/
return(true);
}
