C#操作firebird数据库大全
来源:互联网 发布:中世纪2 优化九 法国 编辑:程序博客网 时间:2024/06/06 03:51
FAQ:
。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.Embedded; //csb.ServerType = 1, embedded Firebird
- FbConnection c = new FbConnection(csb.ToString());
FbConnectionStringBuilder Properties Overview
FbConnectionStringBuilder Property
Type
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 lifetime.
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.
Database
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.
Dialect
System.Byte
Database dialect (1 or 3). Use 3 unless you want specifically use dialect 1 for backwards compatiblity.
FetchSize
System.Int32
Indicates the number of rows that will be fetched at the same time on Read calls into the internal row buffer.
Charset
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.
Password
System.String
The password for the Firebird user account.
Pooling
System.Boolean
When true, the FbConnection object is drawn from the appropriate pool, or if necessary, is created and added to the appropriate pool.
Port
System.Int32
The port number in the server for establish the connection
Role
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.
UserID
System.String
The firebird User account for login.
· Connection String Charset keyword Parameters:
Firebird Charset
Description
ASCII
American Standard Code for Information Interchange.
BIG_5
Big5, Traditional Chinese.
DOS437
MS-DOS United States, Australia, New Zealand, South Africa.
DOS850
MS-DOS Latin-1.
DOS860
MS-DOS Portugues.
DOS861
MS-DOS Icelandic.
DOS863
MS-DOS Canadian French.
DOS865
MS-DOS Nordic.
EUCJ_0208
JIS X 0201, 0208, 0212, EUC encoding, Japanese.
GB_2312
GB2312, EUC encoding, Simplified Chinese.
ISO8859_1
ISO 8859-1, Latin alphabet No. 1.
ISO8859_2
ISO 8859-2, Latin alphabet No. 2.
KSC_5601
Windows Korean.
ISO2022-JP
Windows Japanese.
SJIS_0208
Japanese (Shift-JIS)
UNICODE_FSS
Eight-bit Unicode Transformation Format.
WIN1250
Windows Eastern European.
WIN1251
Windows Cyrillic.
WIN1252
Windows Latin-1.
WIN1253
Windows Greek.
WIN1254
Windows Turkish.
WIN1254
Windows Hebrew.
Arabic
Windows Turkish.
WIN1257
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:
Parameter
Default value
Description
Pooling
true
Enables or disables connection pooling for this connection. If it is false, a new connection will always be opened.
MinPoolSize
0
The minimum connections that are always open in the pool
MaxPoolSize
100
The maximum connections that will be in the pool.
Connection Lifetime
0
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.Embedded; //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 (
- ID INTEGER,
- VAL VARCHAR(20)
- );
- INSERT INTO MYTABLE (ID, VAL) VALUES (1, 'Test');
Reading valuesYou can fill an untyped DataSet using the following code:
- CreateEmbeddedDb(database, "test.sql");
- FbConnectionStringBuilder csb = new FbConnectionStringBuilder();
- csb.ServerType = FbServerType.Embedded;
- csb.Database = 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
.NET type
FbDataReader method
Example
BIGINT
System.Int64
GetInt64()
BIGINT Reading Example (C#)
BLOB
System.Byte[]
GetBytes()
BLOB Reading Example (C#)
BLOB SUB_TYPE 1
System.String
GetString()
BLOB SUB_TYPE 1 Reading Example (C#)
CHAR
System.String
GetString()
CHAR Reading Example (C#)
DATE
System.DateTime
GetDateTime()
DATE Reading Example (C#)
DECIMAL
System.Decimal
GetDecimal()
DECIMAL Reading Example (C#)
DOUBLE PRECISION
System.Double
GetDouble()
DOUBLE PRECISION Reading Example (C#)
FLOAT
System.Float
GetFloat()
FLOAT Reading Example (C#)
INTEGER
System.Int32
GetInt32()
INTEGER Reading Example (C#)
NUMERIC
System.Decimal
GetDecimal()
NUMERIC Reading Example (C#)
SMALLINT
System.Int16
GetInt16()
SMALLINT Reading Example (C#)
TIME
System.DateTime
GetDateTime()
TIME Reading Example (C#)
TIMESTAMP
System.DateTime
GetDateTime()
TIMESTAMP Reading Example (C#)
VARCHAR
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 (
- ID INTEGER,
- VAL BIGINT
- );
- 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 = database;
- using (FbConnection c = new FbConnection(csb.ToString()))
- {
- c.Open();
- 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 (
- ID INTEGER,
- VAL 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 = database;
- using (FbConnection c = new FbConnection(csb.ToString()))
- {
- c.Open();
- 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 < count; i++)
- Console.WriteLine("Value: " + bytes[i]);
- }
- }
- }<span style="font-family: 'Courier New'; font-size: 16px; white-space: normal; "> </span>
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 (
ID INTEGER,
VAL 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 = database;
- using (FbConnection c = new FbConnection(csb.ToString()))
- {
- c.Open();
- 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);
- }
- }
- }<span style="font-family: 'Courier New'; font-size: 16px; white-space: normal; "> </span>
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 (
ID INTEGER,
VAL CHAR(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 = database;
- using (FbConnection c = new FbConnection(csb.ToString()))
- {
- c.Open();
- 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);
- }
- }
- }<span style="font-family: 'Courier New'; font-size: 16px; white-space: normal; "> </span>
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 (
ID INTEGER,
VAL DATE
);
INSERT INTO MYTABLE (ID, VAL) VALUES (1, '2005-10-01');
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 = database;
- using (FbConnection c = new FbConnection(csb.ToString()))
- {
- c.Open();
- 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 (
ID INTEGER,
VAL 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 = database;
- using (FbConnection c = new FbConnection(csb.ToString()))
- {
- c.Open();
- 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 (
ID INTEGER,
VAL 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 = database;
- using (FbConnection c = new FbConnection(csb.ToString()))
- {
- c.Open();
- 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 (
ID INTEGER,
VAL FLOAT
);
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 = database;
- using (FbConnection c = new FbConnection(csb.ToString()))
- {
- c.Open();
- 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 (
ID INTEGER,
VAL INTEGER
);
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 = database;
- using (FbConnection c = new FbConnection(csb.ToString()))
- {
- c.Open();
- 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 (
ID INTEGER,
VAL 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 = database;
- using (FbConnection c = new FbConnection(csb.ToString()))
- {
- c.Open();
- 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 (
ID INTEGER,
VAL SMALLINT
);
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 = database;
- using (FbConnection c = new FbConnection(csb.ToString()))
- {
- c.Open();
- 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 (
ID INTEGER,
VAL TIME
);
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 = database;
- using (FbConnection c = new FbConnection(csb.ToString()))
- {
- c.Open();
- 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 (
ID INTEGER,
VAL TIMESTAMP
);
INSERT INTO MYTABLE (ID, VAL) VALUES (1, '2005-11-15 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 = database;
- using (FbConnection c = new FbConnection(csb.ToString()))
- {
- c.Open();
- 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 (
ID INTEGER,
VAL 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 = database;
- using (FbConnection c = new FbConnection(csb.ToString()))
- {
- c.Open();
- 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 them represents a different balance between isolation and performance:
· Read uncommitted
· Read committed
· Repeatable read
· Serializable
Firebird doesn't match the standard (SQL92) isolation levels exactly. The following table compares the isolation levels that are supported by Firebird ADO.NET with the SQL92 standard.
Firebird ADO.NET Provider IsolationLevel
Firebird Isolation Level
Corresponding SQL92 Isolation Level
IsolationLevel.ReadUncommitted
READ COMMITTED
RECORD_VERSION
Read Committed
IsolationLevel.ReadCommitted (default)
READ COMMITTED
NO RECORD_VERSION
Read Committed
IsolationLevel.RepeatableRead
SNAPSHOT/CONCURRENCY
Repeatable Read
IsolationLevel.Serializable
SNAPSHOT TABLE STABILITY/CONSISTENCY
Serializable
All transactions have these additional Firebird transaction options set:
· WAIT - when using READ COMMITTED isolation level it waits for uncommitted transactions to finish before reading a row
· READ WRITE - allows data modifications
You can use the predefined isolation levels by calling public FbTransaction BeginTransaction(IsolationLevel) or you can tune the transaction options more precisely by using this BeginTransaction overload: public FbTransaction BeginTransaction(FbTransactionOptions).
IsolationLevel.ReadUncommitted
ANSI/ISO Read Uncommitted isolation level is not supported by Firebird. IsolationLevel.ReadUncommitted behaves like ReadCommitted but it returns the latest committed version of a rowand ignores any other (uncommitted) versions.
Reading
What it reads
The latest committed version of a row and ignores any other (uncommitted) versions.
Dirty reads
No
Phantom data
Possible
Nonrepeatable reads
Possible
Writing
Modification of data modified by other transactions since this transaction started
No
Other transactions can modify data read by this transaction
Yes
Usage
Suitable for
Short transactions that modify data.
IsolationLevel.ReadCommitted
Reading
What it reads
Waits for uncommitted transactions modifyinga row (to be either committed or rolled back).
Dirty reads
No
Phantom data
Possible
Nonrepeatable reads
Possible
Writing
Modification of data modified by other transactions since this transaction started
No
Other transactions can modify data read by this transaction
Yes
Usage
Suitable for
Short transactions that modify data.
IsolationLevel.RepeatableRead
Reading
What it reads
When the transaction starts, a snapshot of the whole database is made. The transaction reads from that snapshot.
Dirty reads
No
Phantom data
No
Nonrepeatable reads
Possible
Writing
Modification of data modified by other transactions since this transaction started
No
Other transactions can modify data read by this transaction
Yes
Usage
Suitable for
Long transactions that require lots of reading and stable data view (reports).
IsolationLevel.Serializable
Reading
What it reads
When the transaction starts, a snapshot of the whole database is made. The transaction reads from that snapshot.
Dirty reads
No
Phantom data
No
Nonrepeatable reads
Possible
Writing
Modification of data modified by other transactions since this transaction started
No
Other transactions can modify data read by this transaction
No. All tables that the transaction has read from are locked. Other transactions are prevented from writing to that tables.
Usage
Suitable for
Long transactions that require exclusive access to a table.
Detecting Firebird Server Installation
Each Firebird instance creates a REG_SZ value in the registry under the key
HKEY_LOCAL_MACHINE/SOFTWARE/Firebird Project/Firebird Server/Instances
The name for the default instance is "DefaultInstance". The value data contains the server root directory (e.g. "C:/Program Files/Firebird/Firebird_1_5/"). You can detect the server settings by reading firebird.conf (e.g. the TCP port) and aliases.conf (aliased databases).
Fulltext Search in Firebird
Firebird doesn't support fulltext search. You need to rely on third party tools. That seems odd, but it doesn't have to be so bad as it looks at first.
I am using DotLucene. It is an open source .NET library (ported from Java) that can index any data (structured or unstructed) that you are able to convert to raw text.
On a server, it is no problem to store the index in a separate directory (you can also load it to RAM to make your searches super fast - if you have enough RAM, of course). In a desktop application, it might be useful to store the index in a Firebird database.
For example: MySQL fulltext search has these drawbacks (compared to DotLucene):
.You can use it only in MyISAM tables (i.e. no transactions)
.You can't browse the index
.You need to store transformed text in the DB (i.e. HTML without tags)
.It doesn't support highlighting of the query words in the result
.You will hardly modify the sources to do custom changes
.The license doesn't allow to use it in commercial application for free
.It is reported to be slow on large data sets
Beginners: Stored Procedure Call Example (Non-query)
To call a stored procedure (that doesn't have return values) using Firebird ADO.NET Provider you can use the following code:
- FbCommand cmd = new FbCommand("STOREDPROCEDURENAME", new FbConnection(connectionString));
- cmd.CommandType = CommandType.StoredProcedure;
- cmd.Parameters.Add("@first", "paramvalue");
- cmd.Parameters.Add("@second", "anotherparamvalue");
- cmd.Connection.Open();
- try
- {
- cmd.ExecuteNonQuery();
- }
- finally
- {
- cmd.Connection.Close();
- }
Beginners: Filling a DataSet Using a Stored Procedure
You can fill an untyped DataSet using the following code:
DataSet ds = new DataSet();
FbDataAdapter da = new FbDataAdapter("SELECTINGSTOREDPROCEDURE", connectionString);
da.SelectCommand.CommandType = CommandType.StoredProcedure;
da.SelectCommand.Parameters.Add("@firstparam", "firstparamvalue");
da.Fill(ds, "myTableNameInDataset");
This code is using a default transaction, i.e. new transaction is started just for this stored procedure call and is committed immediately after. If you are using a FbDataAdapter to load the data there is no need to open and close the connection.
Using FbConnectionStringBuilder
Firebird ADO.NET provider supports a new class (FbConnectionStringBuilder) that makes the work with connection strings much easier:
1. Parsing a connection string
Create a new instance using public FbConnectionStringBuilder(string) constructor (it takes an existing connection string as a parameter). Then you can read the connection string values by using the FbConnectionStringBuilder properties. Example of reading the DataSource (server address) property:
FbConnectionStringBuilder csb = new FbConnectionStringBuilder("User=SYSDBA;Password=masterkey;Database=SampleDatabase.fdb;DataSource=localhost;Charset=NONE;");
Console.WriteLine(csb.DataSource);
2. Creating a connection string programmatically
You can create a connection string by specifying the properties item by item:
FbConnectionStringBuilder csb = new FbConnectionString();
csb.UserID = "SYSDBA";
csb.Password = "masterkey";
csb.Database = "mydb.fdb";
csb.ServerType = 1; // embedded Firebird
FbConnection c = new FbConnection(csb.ToString());
3. Modifying an existing connection string
You can also modify an existing connection string without complicated parsing. This example switches the type of server to embedded Firebird:
FbConnectionStringBuilder csb = new FbConnectionString(existingConnectionString);
csb.ServerType = 1;
FbConnection c = new FbConnection(csb.ToString());
Creating a Database Programmatically
The maximum you can specify:
Hashtable parameters = new Hashtable();
parameters.Add("User", "SYSDBA");
parameters.Add("Password", "masterkey");
parameters.Add("Database", @"c:/database.fdb");
parameters.Add("Dialect", 3);
parameters.Add("DataSource", "localhost");
parameters.Add("Port", "3050");
parameters.Add("Charset", "NONE");
parameters.Add("PageSize", "8192");
parameters.Add("ForcedWrite", true);
FbConnection.CreateDatabase(parameters);
The miminum you must specify:
Hashtable parameters = new Hashtable();
parameters.Add("User", "SYSDBA");
parameters.Add("Password", "masterkey");
parameters.Add("Database", @"c:/database.fdb");
FbConnection.CreateDatabase(parameters);
Batch SQL/DDL Execution
The FirebirdSql.Data.Firebird.Isql namespace provides classes for batch SQL/DDL execution. This allows you to make such operations as:
- creating the database structure programmatically
- updating the database structure automatically
- automating the maintenance procedure
- etc.
Running a batch script is not difficult. First you need to parse the script using FbScript class:
FbScript script = new FbScript("employee.sql");
script.Parse();
The result is stored in script.Results, one item per command. Now we can use FbBatchExecution classto run the script:
FbConnection c = new FbConnection(@"Database=employee.fdb;User=SYSDBA;Password=masterkey");
c.Open();
FbBatchExecution fbe = new FbBatchExecution(c);
foreach (string cmd in script.Results) {
fbe.SqlStatements.Add(cmd);
}
fbe.Execute();
c.Close();
By default, the transaction is committed after each command. You can disable this by callingfbe.Execute(false).
Here is a complete example that uses an embedded Firebird to create a new employee.fdb database and initialize the structure using a DDL script. The DDL script is generated from the example employee.fdb (it's modified on two places because of the current bugs in the provider, see the Known Issues below).
On my Athlon XP 2000+ the execution of this batch script takes 0.921 seconds - which is very good I guess.
Migration from MySQL I.
Why you should do that:
- stored procedures support
- views support
- transactions (these are also supported in InnoDB tables in MySQL)
- friendly open source license that allows commercial use and embedding for free
- embeddable (with a small runtime)
- hot backup
How to:
1) Autoincrement fields
There are no autoincrement fields in Firebird. You need to use a generator. It is a server variable that stores the last number used. You need to call it when inserting a new row:
- in an inserting stored procedure
- in a trigger
Given that we have a table
CREATE TABLE mytable (
id INTEGER,
mytext VARCHAR(20)
);
the generator would look like this:
CREATE GENERATOR GEN_MYTABLE_ID;
the trigger would look like this:
CREATE TRIGGER MYTABLE_BI FOR MYTABLE
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
IF (NEW.ID IS NULL) THEN
NEW.ID = GEN_ID(GEN_MYTABLE_ID,1);
END
and the inserting stored procedure like this:
SET TERM ^ ;
CREATE PROCEDURE SP_MYTABLEINSERT (
MYTEXT VARCHAR(20))
AS
DECLARE VARIABLE ID INTEGER;
BEGIN
ID = GEN_ID(GEN_MYTABLE_ID,1);
INSERT INTO MYTABLE (ID, MYTEXT) VALUES (:ID, :MYTEXT);
END^
SET TERM ; ^
2) NOW() MySQL:
SELECT * FROM mytable WHERE mydate = NOW();
Firebird:
SELECT * FROM mytable WHERE mydate = CURRENT_TIMESTAMP;
There are three special variables for current date and time:
- CURRENT_TIMESTAMP (date and time, TIMESTAMP type)
- CURRENT_DATE (date, DATE type)
- CURRENT_TIME (time, TIME type)
3) LIMIT x, y (return first y rows starting at offset x)
In Firebird it looks like this :
SELECT FIRST y SKIP x * FROM mytable;
LIMIT x (take first 10 rows) looks like this:
SELECT FIRST x * FROM mytable;
Date Functions
Cut and paste date functions. See Ivan Prenosil's site.
Day of week (American format: week starting on Sunday, Sunday is 0):
EXTRACT(WEEKDAY FROM D)
Day of week (ISO 8601 format: week starting on Monday, Monday is 1):
EXTRACT(WEEKDAY FROM D-1)+1
First day of a month:
D - EXTRACT(DAY FROM D) + 1;
Last day of a month:
D - EXTRACT(DAY FROM D) + 33 - EXTRACT(DAY FROM D - EXTRACT(DAY FROM D) + 32)
Number of days in a month:
EXTRACT(DAY FROM (D - EXTRACT(DAY FROM D) + 32 - EXTRACT(DAY FROM D - EXTRACT(DAY FROM D) + 32))
Week of a year (ISO 8601) stored procedure:
CREATE PROCEDURE YearWeek (D DATE)
RETURNS (WEEK_NO VARCHAR(8)) AS
DECLARE VARIABLE W INTEGER; /* week number */
DECLARE VARIABLE Y INTEGER; /* year the week belongs to */
BEGIN
W = (EXTRACT(YEARDAY FROM D) - EXTRACT(WEEKDAY FROM D-1) + 7) / 7;
Y = EXTRACT(YEAR FROM D);
IF (W=0) THEN BEGIN
Y = Y - 1;
D = D - EXTRACT(YEARDAY FROM D) - 1; /* last day of previous year; D is used as temporary variable here */
W = (EXTRACT(YEARDAY FROM D) - EXTRACT(WEEKDAY FROM D-1) + 7) / 7;
END
ELSE
IF (W=53 AND 4>EXTRACT(WEEKDAY FROM (D - EXTRACT(DAY FROM D) + 31))) THEN BEGIN
Y = Y + 1;
W = 1;
END
/* This is just formatting; you may prefer to make W and Y return parameters instead. */
IF (W<10) THEN WEEK_NO = '0'; ELSE WEEK_NO = '';
WEEK_NO = Y '/' WEEK_NO W;
SUSPEND;
END
Is leap year stored procedure:
CREATE PROCEDURE Is_LeapYear (D DATE) RETURNS (LY INTEGER) AS
BEGIN
IF ( 2 = EXTRACT(MONTH FROM (D - EXTRACT(YEARDAY FROM D) + 59)) ) THEN
LY = 1; /* leap year */
ELSE
LY = 0; /* normal year */
END
Date and Time Calculations
When doing arithmetic operations with TIMESTAMP values, Firebird works with them as decimal number, where
- the integral fraction is the number of days
- the decimal fraction is the part of a day
For example:
- Two days: 2.0
- One hour: 1.0/24.0
- One minute: 1.0/1440.0
- One second: 1.0/86400.0
Extracting the smaller units from a TIMESTAMP value:
- Number of seconds: VALUE*86400.0
- Number of minutes: VALUE*1440.0
- Number of hours: VALUE*24.0
Don't forget the decimal point in the numbers (e.g. 1.0), otherwise the result will be integer. I spent a lot time debugging a calculation that didn't work because I was dividing by an integer. Since that time, I remember.
Posted by Dan : 2:04 PM
Joining a String with a NULL Value
If you need to join strings with other values in Firebird you need to use || (double pipe) operator:
SELECT 'col1: ' || col1 || ', col2: ' || col2 FROM mytable;
The problem is that if any of col1 or col2 fields are NULL, the whole joined string will be also NULL. If this is not the intended behavior (and it usually is not) you can use COALESCE function to replace the NULL value with another value:
SELECT 'col1: ' || COALESCE(col1, 'NULL') || ', col2: ' || COALESCE(col2, 'NULL') FROM mytable;
EXECUTE STATEMENT
This statement executes SQL or DDL command specified as a (string) parameter. It is a great tool for stored procedures where you can dynamically create the SQL code to be executed.
Syntax:
/* For statements that return nothing or a single row */
EXECUTE STATEMENT statement [INTO variable_list];
/* For statements that return multiple rows */
FOR EXECUTE STATEMENT statement INTO variable_list DO
compound_statement
Example:
SET TERM ;
CREATE PROCEDURE MYPROC
RETURNS (
ID INTEGER,
MYTEXT VARCHAR(20) CHARACTER SET UNICODE_FSS)
AS
BEGIN
FOR EXECUTE STATEMENT 'SELECT id, mytext FROM mytable' INTO :id, :mytext DO
BEGIN
SUSPEND;
END
END
SET TERM ;
http://blog.csdn.net/salc3k/article/details/6918143
- c#操作firebird数据库大全
- C#操作firebird数据库大全
- C#连接firebird 数据库
- c#数据库操作大全
- c#数据库操作大全
- C#使用Firebird嵌入版数据库
- C#使用Firebird嵌入版数据库
- 在C#中使用FireBird嵌入式数据库
- 在C#中使用FireBird嵌入式数据库
- Firebird数据库异常及常用操作
- 用Node.js操作Firebird数据库。
- FIREBIRD 数据库
- FireBird 数据库
- c#操作Access数据库的方法大全
- C#数据库查询和操作大全
- C#数据库查询和操作大全
- C#数据库查询和操作大全
- JDBC操作firebird数据库出现乱码的应急解决方案
- 单例模式
- javascript深入理解js闭包
- zoj 2405 Specialized Four-Digit Numbers
- app上传appstore
- 字符串移位包含的问题
- C#操作firebird数据库大全
- 轻开B2C电子商务网站(20141231版)发布手册
- C语言实现整数反转-简练算法
- Java笔记——接口与实例变量类变量
- appstore上app版本升级
- JAVA基础应用——JFreeChart 使用
- android 编写布局文件Alt+/不能自动补全
- 博客开张篇
- 不拖控件ASP.NET——NVelocity(1)