下次自动登录
现在的位置:
& 综合 & 正文
C#操作firebird数据库大全
Download firebird下：
。Can I use a relative path to the database in the connection string?
Yes. It can be relative to the working directory.
。How many concurrent connections can you open using the embedded Firebird?
It's not limited. However, only one application can open a database at a time. The application can be a regular Firebird
server or your application using the fbembed.dll.
。What files are required to use the embedded Firebird?
The following files are required:
·fbembed.dll (the embedded Firebird itself)
·FirebirdSql.Data.Firebird.dll (the Firebird ADO.NET Provider assembly)
The following files are recommended:
·firebird.msg (friendly error messages)
The following files are optional:
·aliases.conf
·firebird.conf
·ib_util.dll
·intl/fbintl.dll
·udf/fb_udf.dll
·udf/ib_udf.dll
。Where should I put the embedded Firebird files in my application?
Put them in the working folder of your application. Typically it will be the directory where your .exe file lies.
Connect to a embedded database
Firebird ADO.NET provider supports a new class (FbConnectionStringBuilder) that makes the work with connection strings much easier:
You can create a connection string by specifying the properties item by item:
FbConnectionStringBuilder csb = new FbConnectionString();
csb.ClientLibrary = "fbembed.dll";//把该文件放入应用程序所在目录
csb.UserID = "SYSDBA";
csb.Password = "masterkey";
csb.Database = "lix.fdb";
csb.ServerType = FbServerType.E //csb.ServerType = 1, embedded Firebird
FbConnection c = new FbConnection(csb.ToString());
FbConnectionStringBuilder Properties Overview
FbConnectionStringBuilder Property
Description
ConnectionLifeTime
System.Int64
When a connection is returned to the pool, its creation time is compared with the current time, and the connection is destroyed if that time span (in seconds) exceeds the value specified by connection
ConnectionString
System.String
Here you can get or set the entire connection string.
ConnectionTimeout
System.Int32
The time to wait while trying to establish a connection before terminating the attempt and generating an error.
System.String
The database path to establish the connection. Can be relative to the server executable or fbembed.dll (embedded Firebird).
DataSource
System.String
The server name for establish the connection.
System.Byte
Database dialect (1 or 3). Use 3 unless you want specifically use dialect 1 for backwards compatiblity.
System.Int32
Indicates the number of rows that will be fetched at the same time on Read calls into the internal row buffer.
System.String
Connection character set.
IsolationLevel
System.Data.IsolationLevel
The maximum number of connections allowed in the pool.
MaxPoolSize
System.Int32
The maximun pool size.
MinPoolSize
System.Int32
The minimum number of connections allowed in the pool.
PacketSize
System.Int16
The size (in bytes) of network packets used to communicate with an instance of Firebird Server.
System.String
The password for the Firebird user account.
System.Boolean
When true, the FbConnection object is drawn from the appropriate pool, or if necessary, is created and added to the appropriate pool.
System.Int32
The port number in the server for establish the connection
System.String
The user role name.
ServerType
System.Int32
When 0 the provider will use the GDS implementation used for connections to Firebird Super or Classic servers, when 1 the provider will use the GDS implementation used
for connections to the Firebird embedded server.
System.String
The firebird User account for login.
· Connection String
Charset keyword Parameters：
Firebird Charset
Description
American Standard Code for Information Interchange.
Big5, Traditional Chinese.
MS-DOS United States, Australia, New Zealand, South Africa.
MS-DOS Latin-1.
MS-DOS Portugues.
MS-DOS Icelandic.
MS-DOS Canadian French.
MS-DOS Nordic.
JIS X , 0212, EUC encoding, Japanese.
GB2312, EUC encoding, Simplified Chinese.
ISO 8859-1, Latin alphabet No. 1.
ISO 8859-2, Latin alphabet No. 2.
Windows Korean.
ISO2022-JP
Windows Japanese.
Japanese (Shift-JIS)
UNICODE_FSS
Eight-bit Unicode Transformation Format.
Windows Eastern European.
Windows Cyrillic.
Windows Latin-1.
Windows Greek.
Windows Turkish.
Windows Hebrew.
Windows Turkish.
Windows Baltic.
Connection Pooling
Firebird ADO.NET provider supports connection pooling. By default, connection pooling is turned on. That means that when you call Close method on FbConnection instance the connection to the server is not closed but is
returned to the pool.
Connection pooling is useful especially for Web applications. Each page request would otherwise need to open a new connection and that can be time expensive.
You can modify the behavior of connection pooling in the connection string by using these parameters:
Default value
Description
Enables or disables connection pooling for this connection. If it is false, a new connection will always be opened.
MinPoolSize
The minimum connections that are always open in the pool
MaxPoolSize
The maximum connections that will be in the pool.
Connection Lifetime
How long should the connection remain in the pool (in seconds) after its creation. It is checked when the connection is returned to the pool. 0 means that the connection never expires.
Since version 1.7 of the Firebird ADO.NET Provider you can check the number of connections in the pool using FbConnection.GetPooledConnectionCount().
You can explicitly clear a pool using FbConnection.ClearPool() or all pools using FbConnection.ClearAllPools().
Things to remember:
· The connection pools are created per connection string. If you modify the connection string a new connection (and a pool) will be created.
· The connection is returned to the pool when calling Close() method of FbConnection.
· When you use connection pooling it is better to open the connection as late as possible and close as soon as possible.
Create a New Database From an SQL Script
The following method will create a new database from an SQL script. It uses the embedded Firebird but you
can switch to a standalone server by changing the connection string. It requires Firebird ADO.NET Provider 1.7a.
private static void CreateEmbeddedDb(string pathDb, string pathScript)
// construct the connection string
FbConnectionStringBuilder csb = new FbConnectionStringBuilder();
csb.ServerType = FbServerType.E //1, we are using the embedded Firebird
csb.Database = pathDb;
if (File.Exists(csb.Database))
throw new ApplicationException("The database file does already exist.");
// create a new database
FbConnection.CreateDatabase(csb.ToString());
// parse the SQL script
FbScript script = new FbScript(pathScript);
script.Parse();
// execute the SQL script
using(FbConnection c = new FbConnection(csb.ToString()))
FbBatchExecution fbe = new FbBatchExecution(c);
foreach (string cmd in script.Results)
fbe.SqlStatements.Add(cmd);
fbe.Execute();
Using FbDataAdapter to Fill a Dataset
TEST.SQL Script (Testing Database)
Run the following script to create the testing database. It creates a simple table called MYTABLE with a single row.
CREATE TABLE MYTABLE (
VARCHAR(20)
INSERT INTO MYTABLE (ID, VAL) VALUES (1, 'Test');
Reading values
You can fill an untyped DataSet using the following code:
CreateEmbeddedDb(database, "test.sql");
FbConnectionStringBuilder csb = new FbConnectionStringBuilder();
csb.ServerType = FbServerType.E
csb.Database =
DataSet ds = new DataSet();
FbDataAdapter da = new FbDataAdapter("SELECT * FROM mytable WHERE id &= @id", csb.ToString());
da.SelectCommand.Parameters.Add("@id", 1);
da.Fill(ds);
Firebird and .NET Framework Data Types Mapping
Here is the mapping of Firebird data types to .NET Framework data types.
Use the specified FbDataReader method to read the data from a given column.
Firebird type
FbDataReader method
System.Int64
GetInt64()
BIGINT Reading Example (C#)
System.Byte[]
GetBytes()
BLOB Reading Example (C#)
BLOB SUB_TYPE 1
System.String
GetString()
BLOB SUB_TYPE 1 Reading Example (C#)
System.String
GetString()
CHAR Reading Example (C#)
System.DateTime
GetDateTime()
DATE Reading Example (C#)
System.Decimal
GetDecimal()
DECIMAL Reading Example (C#)
DOUBLE PRECISION
System.Double
GetDouble()
DOUBLE PRECISION Reading Example (C#)
System.Float
GetFloat()
FLOAT Reading Example (C#)
System.Int32
GetInt32()
INTEGER Reading Example (C#)
System.Decimal
GetDecimal()
NUMERIC Reading Example (C#)
System.Int16
GetInt16()
SMALLINT Reading Example (C#)
System.DateTime
GetDateTime()
TIME Reading Example (C#)
System.DateTime
GetDateTime()
TIMESTAMP Reading Example (C#)
System.String
GetString()
VARCHAR Reading Example (C#)
BIGINT Reading Example (C#)
TEST.SQL Script (Testing Database)
Run the following script to create the testing database. It creates a simple table called MYTABLE with a single row.
CREATE TABLE MYTABLE (
INSERT INTO MYTABLE (ID, VAL) VALUES (1, 123);
Reading values
The following sample prints the values from the column "VAL" to console:
string database = "test.fdb";
CreateEmbeddedDb(database, "test.sql");
FbConnectionStringBuilder csb = new FbConnectionStringBuilder();
csb.ServerType = 1;
csb.Database =
using (FbConnection c = new FbConnection(csb.ToString()))
FbCommand cmd = new FbCommand("SELECT val FROM mytable", c);
using (FbDataReader r = cmd.ExecuteReader())
while (r.Read())
long val = r.GetInt64(0);
Console.WriteLine("Value: " + val);
BLOB Reading Example (C#)
TEST.SQL Script (Testing Database)
Run the following script to create the testing database. It creates a simple table called MYTABLE with a single row:
CREATE TABLE MYTABLE (
BLOB SUB_TYPE 0 SEGMENT SIZE 80
Reading values
Because it is not possible to insert binary BLOB value in the SQL script we need to insert it in the code before reading.
The following sample prints the values from the column "VAL" to console:
string database = "test.fdb";
CreateEmbeddedDb(database, "test.sql");
FbConnectionStringBuilder csb = new FbConnectionStringBuilder();
csb.ServerType = 1;
csb.Database =
using (FbConnection c = new FbConnection(csb.ToString()))
FbCommand cmdInsert = new FbCommand("INSERT INTO mytable(id, val) VALUES(@id, @val)", c);
cmdInsert.Parameters.Add("@id", 1);
cmdInsert.Parameters.Add("@val", new byte[] {1, 2, 3});
cmdInsert.ExecuteNonQuery();
FbCommand cmd = new FbCommand("SELECT val FROM mytable", c);
using (FbDataReader r = cmd.ExecuteReader())
while (r.Read())
int size = 20;
byte[] bytes = new byte[size];
long count = r.GetBytes(0, 0, bytes, 0, size);
for(int i = 0; i & i++)
Console.WriteLine("Value: " + bytes[i]);
BLOB SUB_TYPE 1 Reading Example (C#)
TEST.SQL Script (Testing Database)
Run the following script to create the testing database. It creates a simple table called MYTABLE with a single row.
CREATE TABLE MYTABLE (
BLOB SUB_TYPE 1 SEGMENT SIZE 80
INSERT INTO MYTABLE (ID, VAL) VALUES (1, '123');
Reading values
The following sample prints the values from the column "VAL" to console:
string database = "test.fdb";
CreateEmbeddedDb(database, "test.sql");
FbConnectionStringBuilder csb = new FbConnectionStringBuilder();
csb.ServerType = 1;
csb.Database =
using (FbConnection c = new FbConnection(csb.ToString()))
FbCommand cmd = new FbCommand("SELECT val FROM mytable", c);
using (FbDataReader r = cmd.ExecuteReader())
while (r.Read())
string val = r.GetString(0);
Console.WriteLine("Value: " + val);
CHAR Reading Example (C#)
TEST.SQL Script (Testing Database)
Run the following script to create the testing database. It creates a simple table called MYTABLE with a single row.
CREATE TABLE MYTABLE (
INSERT INTO MYTABLE (ID, VAL) VALUES (1, '123');
Reading values
The following sample prints the values from the column "VAL" to console:
string database = "test.fdb";
CreateEmbeddedDb(database, "test.sql");
FbConnectionStringBuilder csb = new FbConnectionStringBuilder();
csb.ServerType = 1;
csb.Database =
using (FbConnection c = new FbConnection(csb.ToString()))
FbCommand cmd = new FbCommand("SELECT val FROM mytable", c);
using (FbDataReader r = cmd.ExecuteReader())
while (r.Read())
string val = r.GetString(0);
Console.WriteLine("Value: " + val);
DATE Reading Example (C#)
TEST.SQL Script (Testing Database)
Run the following script to create the testing database. It creates a simple table called MYTABLE with a single row.
CREATE TABLE MYTABLE (
INSERT INTO MYTABLE (ID, VAL) VALUES (1, '');
Reading values
The following sample prints the values from the column "VAL" to console:
string database = "test.fdb";
CreateEmbeddedDb(database, "test.sql");
FbConnectionStringBuilder csb = new FbConnectionStringBuilder();
csb.ServerType = 1;
csb.Database =
using (FbConnection c = new FbConnection(csb.ToString()))
FbCommand cmd = new FbCommand("SELECT val FROM mytable", c);
using (FbDataReader r = cmd.ExecuteReader())
while (r.Read())
DateTime val = r.GetDateTime(0);
Console.WriteLine("Value: " + val);
DECIMAL Reading Example (C#)
TEST.SQL Script (Testing Database)
Run the following script to create the testing database. It creates a simple table called MYTABLE with a single row.
CREATE TABLE MYTABLE (
DECIMAL(15,2)
INSERT INTO MYTABLE (ID, VAL) VALUES (1, 123.2);
Reading values
The following sample prints the values from the column "VAL" to console:
string database = "test.fdb";
CreateEmbeddedDb(database, "test.sql");
FbConnectionStringBuilder csb = new FbConnectionStringBuilder();
csb.ServerType = 1;
csb.Database =
using (FbConnection c = new FbConnection(csb.ToString()))
FbCommand cmd = new FbCommand("SELECT val FROM mytable", c);
using (FbDataReader r = cmd.ExecuteReader())
while (r.Read())
decimal val = r.GetDecimal(0);
Console.WriteLine("Value: " + val);
DOUBLE PRECISION Reading Example (C#)
TEST.SQL Script (Testing Database)
Run the following script to create the testing database. It creates a simple table called MYTABLE with a single row.
CREATE TABLE MYTABLE (
DOUBLE PRECISION
INSERT INTO MYTABLE (ID, VAL) VALUES (1, 123.124);
Reading values
The following sample prints the values from the column "VAL" to console:
string database = "test.fdb";
CreateEmbeddedDb(database, "test.sql");
FbConnectionStringBuilder csb = new FbConnectionStringBuilder();
csb.ServerType = 1;
csb.Database =
using (FbConnection c = new FbConnection(csb.ToString()))
FbCommand cmd = new FbCommand("SELECT val FROM mytable", c);
using (FbDataReader r = cmd.ExecuteReader())
while (r.Read())
double val = r.GetDouble(0);
Console.WriteLine("Value: " + val);
FLOAT Reading Example (C#)
TEST.SQL Script (Testing Database)
Run the following script to create the testing database. It creates a simple table called MYTABLE with a single row.
CREATE TABLE MYTABLE (
INSERT INTO MYTABLE (ID, VAL) VALUES (1, 123.124);
Reading values
The following sample prints the values from the column "VAL" to console:
string database = "test.fdb";
CreateEmbeddedDb(database, "test.sql");
FbConnectionStringBuilder csb = new FbConnectionStringBuilder();
csb.ServerType = 1;
csb.Database =
using (FbConnection c = new FbConnection(csb.ToString()))
FbCommand cmd = new FbCommand("SELECT val FROM mytable", c);
using (FbDataReader r = cmd.ExecuteReader())
while (r.Read())
float val = r.GetFloat(0);
Console.WriteLine("Value: " + val);
INTEGER Reading Example (C#)
TEST.SQL Script (Testing Database)
Run the following script to create the testing database. It creates a simple table called MYTABLE with a single row.
CREATE TABLE MYTABLE (
INSERT INTO MYTABLE (ID, VAL) VALUES (1, 123);
Reading values
The following sample prints the values from the column "VAL" to console:
string database = "test.fdb";
CreateEmbeddedDb(database, "test.sql");
FbConnectionStringBuilder csb = new FbConnectionStringBuilder();
csb.ServerType = 1;
csb.Database =
using (FbConnection c = new FbConnection(csb.ToString()))
FbCommand cmd = new FbCommand("SELECT val FROM mytable", c);
using (FbDataReader r = cmd.ExecuteReader())
while (r.Read())
int val = r.GetInt32(0);
Console.WriteLine("Value: " + val);
NUMERIC Reading Example (C#)
TEST.SQL Script (Testing Database)
Run the following script to create the testing database. It creates a simple table called MYTABLE with a single row.
CREATE TABLE MYTABLE (
NUMERIC(15,2)
INSERT INTO MYTABLE (ID, VAL) VALUES (1, 123.2);
Reading values
The following sample prints the values from the column "VAL" to console:
string database = "test.fdb";
CreateEmbeddedDb(database, "test.sql");
FbConnectionStringBuilder csb = new FbConnectionStringBuilder();
csb.ServerType = 1;
csb.Database =
using (FbConnection c = new FbConnection(csb.ToString()))
FbCommand cmd = new FbCommand("SELECT val FROM mytable", c);
using (FbDataReader r = cmd.ExecuteReader())
while (r.Read())
decimal val = r.GetDecimal(0);
Console.WriteLine("Value: " + val);
SMALLINT Reading Example (C#)
TEST.SQL Script (Testing Database)
Run the following script to create the testing database. It creates a simple table called MYTABLE with a single row.
CREATE TABLE MYTABLE (
INSERT INTO MYTABLE (ID, VAL) VALUES (1, 123);
Reading values
The following sample prints the values from the column "VAL" to console:
string database = "test.fdb";
CreateEmbeddedDb(database, "test.sql");
FbConnectionStringBuilder csb = new FbConnectionStringBuilder();
csb.ServerType = 1;
csb.Database =
using (FbConnection c = new FbConnection(csb.ToString()))
FbCommand cmd = new FbCommand("SELECT val FROM mytable", c);
using (FbDataReader r = cmd.ExecuteReader())
while (r.Read())
short val = r.GetInt16(0);
Console.WriteLine("Value: " + val);
TIME Reading Example (C#)
TEST.SQL Script (Testing Database)
Run the following script to create the testing database. It creates a simple table called MYTABLE with a single row.
CREATE TABLE MYTABLE (
INSERT INTO MYTABLE (ID, VAL) VALUES (1, '00:01:00');
Reading values
The following sample prints the values from the column "VAL" to console:
string database = "test.fdb";
CreateEmbeddedDb(database, "test.sql");
FbConnectionStringBuilder csb = new FbConnectionStringBuilder();
csb.ServerType = 1;
csb.Database =
using (FbConnection c = new FbConnection(csb.ToString()))
FbCommand cmd = new FbCommand("SELECT val FROM mytable", c);
using (FbDataReader r = cmd.ExecuteReader())
while (r.Read())
DateTime val = r.GetDateTime(0);
Console.WriteLine("Value: " + val);
TIMESTAMP Reading Example (C#)
TEST.SQL Script (Testing Database)
Run the following script to create the testing database. It creates a simple table called MYTABLE with a single row.
CREATE TABLE MYTABLE (
INSERT INTO MYTABLE (ID, VAL) VALUES (1, ' 00:00:00');
Reading values
The following sample prints the values from the column "VAL" to console:
string database = "test.fdb";
CreateEmbeddedDb(database, "test.sql");
FbConnectionStringBuilder csb = new FbConnectionStringBuilder();
csb.ServerType = 1;
csb.Database =
using (FbConnection c = new FbConnection(csb.ToString()))
FbCommand cmd = new FbCommand("SELECT val FROM mytable", c);
using (FbDataReader r = cmd.ExecuteReader())
while (r.Read())
DateTime val = r.GetDateTime(0);
Console.WriteLine("Value: " + val);
VARCHAR Reading Example (C#)
TEST.SQL Script (Testing Database)
Run the following script to create the testing database. It creates a simple table called MYTABLE with a single row.
CREATE TABLE MYTABLE (
VARCHAR(20)
INSERT INTO MYTABLE (ID, VAL) VALUES (1, '123');
Reading values
The following sample prints the values from the column "VAL" to console:
string database = "test.fdb";
CreateEmbeddedDb(database, "test.sql");
FbConnectionStringBuilder csb = new FbConnectionStringBuilder();
csb.ServerType = 1;
csb.Database =
using (FbConnection c = new FbConnection(csb.ToString()))
FbCommand cmd = new FbCommand("SELECT val FROM mytable", c);
using (FbDataReader r = cmd.ExecuteReader())
while (r.Read())
string val = r.GetString(0);
Console.WriteLine("Value: " + val);
Transaction Isolation Levels
Every database operation in Firebird runs inside a transaction. The transactions have these features: Atomicity, Consistency, Isolation, Durability (read more about ACID). Let's talk a bit about the isolation feature.
Isolation says that the transaction shouldn't interfere with other transactions. Achieving full isolation would mean serializing the transactions and that would slow down the database. Therefore there
arefour transaction isolation levels defined and every of
【上篇】【下篇】}