C# FirebirdClient.FbConnection类代码示例

#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 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;
        }

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

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

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

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

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

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