#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 MainF(FbConnection connection)
{
_connection = connection;
InitializeComponent();
_editionViewer.FbConnection = _connection;
}
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 void ClearPool(FbConnection connection)
{
if (connection == null)
throw new ArgumentNullException("connection");
FbConnectionPoolManager.Instance.ClearPool(connection.options);
}
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 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();
}
}
public BaseTreeNavigator()
{
InitializeComponent();
//SetConnections();
// get the connection string from app.config and create the connections
string sqlConnectionString = ConfigurationManager.ConnectionStrings["sqlServerConnectionString"].ConnectionString;
string fbConnectionString = ConfigurationManager.ConnectionStrings["firebirdConnectionString"].ConnectionString;
//sql server
sqlConnection = new SqlConnection(sqlConnectionString);
sqlConnectionDaughter = new SqlConnection(sqlConnectionString);
sqlCommand = new SqlCommand();
sqlCommand.Connection = sqlConnection;
sqlCommandDaughter = new SqlCommand();
sqlCommandDaughter.Connection = sqlConnectionDaughter;
//firebird
fbConnection = new FbConnection(fbConnectionString);
fbConnectionDaughter = new FbConnection(fbConnectionString);
fbCommand = new FbCommand();
fbCommand.Connection = fbConnection;
fbCommandDaughter = new FbCommand();
fbCommandDaughter.Connection = fbConnectionDaughter;
}
private bool DataBaseOpenConnection()
{
var fb_con = new FbConnectionStringBuilder
{
Charset = charset,
UserID = userID,
Password = password,
Database = database,
ServerType = 0
};
fb = new FbConnection(fb_con.ToString());
try
{
if (fb.State != ConnectionState.Open)
{
fb.Open();
}
}
catch (Exception ex)
{
Log.Add(ex.Message + "\r\n" + ex.Source + "\r\n" + ex.TargetSite);
return false;
}
return true;
}
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();
}
protected FbCommand BuildCommand(FbConnection connection, string collectionName, string[] restrictions)
{
string filter = String.Format("CollectionName='{0}'", collectionName);
StringBuilder builder = this.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 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 IModel Select(FbConnection conn, FbTransaction trans, string code)
{
string query = "SELECT * FROM models WHERE modelcode = UPPER(@modelcode)";
IModel ret = null;
try
{
using (FbCommand cmd = new FbCommand(query, conn, trans))
{
cmd.Parameters.AddWithValue("modelcode", code.ToUpper());
using (FbDataReader reader = cmd.ExecuteReader())
{
if (reader.Read())
{
ret = new Model
{
Id = reader.GetInt32(0),
Code = reader.GetString(1),
ObjectIdRoot = reader.GetInt32(2)
};
}
}
}
}
catch (Exception ex)
{
throw new Exception(String.Format("Ошибка SQL запроса. {0}", ex.Message));
}
return ret;
}
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 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);
}
public static bool abrir()
{
conexao = new FbConnection(stringConexao);
conexao.Open();
transacao = conexao.BeginTransaction();
return true;
}
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();
}
}
}
}
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
{
}
}
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;
}
}
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 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 IEnumerable<DomainInfo> GetDomain(FbConnection con)
{
using (var command = con.CreateCommand())
{
command.CommandText =
$"select distinct f.rdb$field_type Type, f.rdb$field_sub_type SubType , f.rdb$character_length CharSize, trim(f.rdb$field_name) FieldName, f.rdb$field_precision FieldPrecision, f.rdb$field_scale FieldScale, f.rdb$field_length FieldLength, coalesce(f.rdb$validation_source, '') ValidationSource, coalesce(f.rdb$default_source, '') DefaultSource, f.rdb$null_flag NullFlag " +
"from rdb$fields f " +
$"where f.rdb$FIELD_NAME not starting with 'RDB$' and f.rdb$FIELD_NAME not starting with 'MON$' and f.rdb$FIELD_NAME not starting with 'SEC$' " +
"order by f.rdb$field_name; ";
var reader = command.ExecuteReader();
while (reader.Read())
{
var name = (string)reader["FieldName"];
var size = (reader["CharSize"] == DBNull.Value) ? null : (short?)reader["CharSize"];
var subType = (reader["SubType"] == DBNull.Value) ? null : (short?)reader["SubType"];
var precision = (reader["FieldPrecision"] == DBNull.Value) ? null : (short?)reader["FieldPrecision"];
var scale = (reader["FieldScale"] == DBNull.Value) ? null : (short?)reader["FieldScale"];
var fieldLength = (reader["FieldLength"] == DBNull.Value) ? null : (short?)reader["FieldLength"];
var type = new FieldType((short)reader["Type"], subType, size, precision, scale, fieldLength);
var validationSource = (string)reader["ValidationSource"];
var defaultSource = (string)reader["DefaultSource"];
var nullFlag = reader["NullFlag"] == DBNull.Value;
yield return new DomainInfo(name, type, validationSource, defaultSource, nullFlag);
}
}
}
protected string _executeNonQuery(FbConnection conn, FbTransaction trans, string query, Dictionary<string, object> prms, bool isReturning)
{
string ret = "";
using (FbCommand cmd = new FbCommand(query, conn, trans))
{
foreach (var prm in prms)
cmd.Parameters.AddWithValue(prm.Key, prm.Value);
if (isReturning)
{
FbParameter outparam = new FbParameter("@out", FbDbType.VarChar)
{
Direction = ParameterDirection.Output
};
cmd.Parameters.Add(outparam);
cmd.ExecuteNonQuery();
ret = outparam.Value as string;
}
else
cmd.ExecuteNonQuery();
}
return ret;
}
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;
}
}
protected List<Dictionary<string, string>> _executeReader(FbConnection conn, FbTransaction trans, string query, Dictionary<string, object> prms)
{
List<Dictionary<string, string>> ret = new List<Dictionary<string, string>>();
using (FbCommand cmd = new FbCommand(query, conn, trans))
{
foreach (var prm in prms)
cmd.Parameters.AddWithValue(prm.Key, prm.Value);
using (FbDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
Dictionary<string, string> row = new Dictionary<string, string>();
Object[] values = new Object[reader.FieldCount];
reader.GetValues(values);
for (int jj = 0; jj < reader.FieldCount; jj++)
row.Add(reader.GetName(jj).ToLower(), values[jj].ToString());
ret.Add(row);
}
}
}
return ret;
}
public override void PrepareDb()
{
// clean
if(File.Exists(PathToDb))
{
File.Delete(PathToDb);
}
// create db
FbConnection.CreateDatabase(pConnectionString);
// create tables
string script;
using(StreamReader reader = new StreamReader(PathToSchema))
{
script = reader.ReadToEnd();
}
FbScript sqls = new FbScript(script);
if(sqls.Parse() <= 0)
{
throw new DbException("No DB schema found.");
}
using(FbConnection connection = new FbConnection(pConnectionString))
{
connection.Open();
foreach(FbCommand create in sqls.Results.Select(sql => new FbCommand(sql, connection)))
{
create.ExecuteNonQuery();
}
}
}
public void CancelarNFseSistema(string sNumNfse, FbConnection Conn)
{
try
{
StringBuilder sSql = new StringBuilder();
sSql.Append("update nf ");
sSql.Append("set cd_recibocanc = '");
sSql.Append("CANCELADA");
sSql.Append("' ");
sSql.Append("where ");
sSql.Append("cd_empresa = '");
sSql.Append(belStatic.codEmpresaNFe);
sSql.Append("' ");
sSql.Append("and ");
sSql.Append("cd_numero_nfse = '");
sSql.Append(sNumNfse);
sSql.Append("'");
using (FbCommand cmdUpdate = new FbCommand(sSql.ToString(), Conn))
{
cmdUpdate.ExecuteNonQuery();
}
}
catch (Exception ex)
{
throw;
}
}
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 TcPedidoCancelamento BuscaDadosParaCancelamento(FbConnection Conn, string sCodCancelamento, string sSequencia)
{
try
{
StringBuilder sQuery = new StringBuilder();
sQuery.Append("select cidades.cd_municipio, empresa.cd_cgc Cnpj, empresa.cd_inscrmu, ");
sQuery.Append("coalesce(nf.cd_numero_nfse,'')cd_numero_nfse ");
sQuery.Append("from nf inner join empresa on nf.cd_empresa = empresa.cd_empresa ");
sQuery.Append("inner join cidades on (cidades.nm_cidnor = empresa.nm_cidnor) ");
sQuery.Append("where nf.cd_nfseq = '" + sSequencia + "' and ");
sQuery.Append("nf.cd_empresa = '" + belStatic.codEmpresaNFe + "'");
FbCommand Command = new FbCommand(sQuery.ToString(), Conn);
FbDataReader dr = Command.ExecuteReader();
dr.Read();
TcPedidoCancelamento objCancelamento = new TcPedidoCancelamento();
objCancelamento.InfPedidoCancelamento = new tcInfPedidoCancelamento();
objCancelamento.InfPedidoCancelamento.CodigoCancelamento = sCodCancelamento;
objCancelamento.InfPedidoCancelamento.IdentificacaoNfse = new tcIdentificacaoNfse();
objCancelamento.InfPedidoCancelamento.IdentificacaoNfse.CodigoMunicipio = dr["cd_municipio"].ToString();
objCancelamento.InfPedidoCancelamento.IdentificacaoNfse.Numero = dr["cd_numero_nfse"].ToString();
objCancelamento.InfPedidoCancelamento.IdentificacaoNfse.Cnpj = dr["Cnpj"].ToString();
objCancelamento.InfPedidoCancelamento.IdentificacaoNfse.InscricaoMunicipal = dr["cd_inscrmu"].ToString();
return objCancelamento;
}
catch (Exception)
{
throw;
}
}
public void PopulainfCarga(belinfCte objbelinfCte, FbConnection conn, string sCte)
{
try
{
StringBuilder sQuery = new StringBuilder();
sQuery.Append("Select ");
sQuery.Append("coalesce(conhecim.ds_prodpred,'')proPred, ");
sQuery.Append("coalesce(nfconhec.vl_nf,'')vMerc ");
sQuery.Append("from conhecim ");
sQuery.Append("join nfconhec on conhecim.nr_lanc = nfconhec.nr_lancconhecim ");
sQuery.Append("join empresa on conhecim.cd_empresa = empresa.cd_empresa ");
sQuery.Append("where conhecim.nr_lanc ='" + sCte + "' ");
sQuery.Append("and empresa.cd_empresa ='" + belStatic.CodEmpresaCte + "'");
FbCommand fbConn = new FbCommand(sQuery.ToString(), conn);
fbConn.ExecuteNonQuery();
FbDataReader dr = fbConn.ExecuteReader();
objbelinfCte.infCTeNorm = new belinfCTeNorm();
objbelinfCte.infCTeNorm.infCarga = new belinfCarga();
while (dr.Read())
{
objbelinfCte.infCTeNorm.infCarga.vCarga += Convert.ToDecimal(dr["vMerc"].ToString().Replace(".", ","));
objbelinfCte.infCTeNorm.infCarga.proPred =belUtil.TiraSimbolo( dr["proPred"].ToString(),"");
}
}
catch (Exception ex)
{
throw ex;
}
}
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 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();
}
}
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 static List <Device> test()
{
throw new NotImplementedException();
//string connectionString = "***REMOVED***Database=C:\\Tom\\Firebird_Database\\as.GDK;DataSource=127.0.0.1; Port = 3050; Dialect = 3; Charset = NONE; Role =; Connection lifetime = 15; Pooling = true; MinPoolSize = 0; MaxPoolSize = 50; Packet Size = 8192; ServerType = 0;";
//List<Device> dev = getDevices();
FirebirdSql.Data.FirebirdClient.FbConnection connection = new FirebirdSql.Data.FirebirdClient.FbConnection(connectionString);
//connection.Open();
//System.Data.DataTable table = connection.GetSchema();
FbConnection conn = new FbConnection(connectionString);
conn.Open();
String sql = "SELECT * FROM URZADZENIE_KLIENT";
FbCommand com = new FbCommand(sql, conn);
FbDataReader dr = com.ExecuteReader();
List <string> data = new List <string>();
while (dr.Read())
{
data.Add(dr.GetString(7));
}
dr.Close();
sql = "SELECT URZADZENIE_KLIENT.NR_FABRYCZNY, MODEL_URZADZENIA.NAZWA_1, MARKA_URZADZENIA.NAZWA_1 " + //, MARKA_URZADZENIA.NAZWA_1
"FROM URZADZENIE_KLIENT " +
"INNER JOIN MODEL_URZADZENIA " +
"ON URZADZENIE_KLIENT.ID_MODEL_URZADZENIA=MODEL_URZADZENIA.ID_MODEL_URZADZENIA " +
"INNER JOIN MARKA_URZADZENIA " +
"ON MODEL_URZADZENIA.ID_MARKA_URZADZENIA=MARKA_URZADZENIA.ID_MARKA_URZADZENIA";
com = new FbCommand(sql, conn);
dr = com.ExecuteReader();
data = new List <string>();
List <Device> devices = new List <Device>();
while (dr.Read())
{
devices.Add(new Device
{
serial_number = dr.GetString(0),
model = dr.GetString(1),
provider = dr.GetString(2)
});
}
dr.Close();
conn.Close();
return(devices);
}
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
{
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("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
{
FbConnection cn = new FirebirdSql.Data.FirebirdClient.FbConnection(this._dbRoot.ConnectionString);
FbDataAdapter adapter = new FbDataAdapter(BuildSql(this.Procedure.Name), cn);
DataTable metaData = new DataTable();
adapter.Fill(metaData);
metaData.Columns["PARAMETER_DIRECTION"].ColumnName = "PARAMETER_TYPE";
PopulateArray(metaData);
FixupDataTypes(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;
}
}
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;
}
}
public override void Delete()
{
FirebirdClientConnection.ClearAllPools();
FirebirdClientConnection.DropDatabase(_connection.ConnectionString);
}
public override void Create()
{
FirebirdClientConnection.CreateDatabase(_connection.ConnectionString);
}
public override void Delete()
{
FirebirdClientConnection.ClearPool((FirebirdClientConnection)_connection.DbConnection);
FirebirdClientConnection.DropDatabase(_connection.ConnectionString);
}
public FbConnectionInternal SetOwningConnection(FbConnection owningConnection)
{
_owningConnection = owningConnection;
return(this);
}
public FbDatabaseInfo(FbConnection connection)
{
_connection = connection;
}
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();
}
}
internal FbConnectionInternal Get(FbConnectionString connectionString, FbConnection owner)
{
CheckDisposed();
return(_pools.GetOrAdd(connectionString.NormalizedConnectionString, _ => new Pool(connectionString)).GetConnection(owner));
}
public FbCommand(string cmdText, FbConnection connection)
: this(cmdText, connection, null)
{
}
