|
Firebird .NET Provider - Examples of use
1. Connection pooling.
2. Update a text blob field.
3. Firebird events [v1.7].
4. Database backup [v1.7].
5. Database restore [v1.7].
6. Database Schema [v2.0].
1. Connection pooling
public static void Main(string[] args)
{
// Set the ServerType to 1 for connect to the embedded server
string connectionString =
"User=SYSDBA;" +
"Password=masterkey;" +
"Database=SampleDatabase.fdb;" +
"DataSource=localhost;" +
"Port=3050;" +
"Dialect=3;" +
"Charset=NONE;" +
"Role=;" +
"Connection lifetime=15;" +
"Pooling=true;" +
"MinPoolSize=0;" +
"MaxPoolSize=50;" +
"Packet Size=8192;" +
"ServerType=0";
FbConnection myConnection1 = new FbConnection(connectionString);
FbConnection myConnection2 = new FbConnection(connectionString);
FbConnection myConnection3 = new FbConnection(connectionString);
try
{
// Open two connections.
Console.WriteLine ("Open two connections.");
myConnection1.Open();
myConnection2.Open();
// Now there are two connections in the pool that matches the connection string.
// Return the both connections to the pool.
Console.WriteLine ("Return both of the connections to the pool.");
myConnection1.Close();
myConnection2.Close();
// Get a connection out of the pool.
Console.WriteLine ("Open a connection from the pool.");
myConnection1.Open();
// Get a second connection out of the pool.
Console.WriteLine ("Open a second connection from the pool.");
myConnection2.Open();
// Open a third connection.
Console.WriteLine ("Open a third connection.");
myConnection3.Open();
// Return the all connections to the pool.
Console.WriteLine ("Return all three connections to the pool.");
myConnection1.Close();
myConnection2.Close();
myConnection3.Close();
}
catch(Exception e)
{
Console.WriteLine(e.Message);
}
}
return to top
2. Update a text blob field.
public static void Main(string[] args)
{
// Set the ServerType to 1 for connect to the embedded server
string connectionString =
"User=SYSDBA;" +
"Password=masterkey;" +
"Database=SampleDatabase.fdb;" +
"DataSource=localhost;" +
"Port=3050;" +
"Dialect=3;" +
"Charset=NONE;" +
"Role=;" +
"Connection lifetime=15;" +
"Pooling=true;" +
"Packet Size=8192;" +
"ServerType=0";
FbConnection myConnection = new FbConnection(connectionString);
myConnection.Open();
FbTransaction myTransaction = myConnection.BeginTransaction();
FbCommand myCommand = new FbCommand();
myCommand.CommandText =
"UPDATE TEST_TABLE_01 SET CLOB_FIELD = @CLOB_FIELD WHERE INT_FIELD = @INT_FIELD";
myCommand.Connection = myConnection;
myCommand.Transaction = myTransaction;
myCommand.Parameters.Add("@INT_FIELD", FbType.Integer, "INT_FIELD");
myCommand.Parameters.Add("@CLOB_FIELD", FbType.Text, "CLOB_FIELD");
myCommand.Parameters[0].Value = 1;
myCommand.Parameters[1].Value = GetFileContents(@"GDS.CS");
// Execute Update
myCommand.ExecuteNonQuery();
// Commit changes
myTransaction.Commit();
// Free command resources in Firebird Server
myCommand.Dispose();
// Close connection
myConnection.Close();
}
public static string GetFileContents(string fileName)
{
StreamReader reader = new StreamReader(new FileStream(fileName, FileMode.Open));
string contents = reader.ReadToEnd();
reader.Close();
return contents;
}
return to top
3. Firebird events [v1.7].
static void Main(string[] args)
{
FbConnectionStringBuilder cs = new FbConnectionStringBuilder();
cs.DataSource = "localhost";
cs.Database = "employee.fdb";
cs.UserID = "SYSDBA";
cs.Password = "masterkey";
cs.Charset = "NONE";
cs.Pooling = false;
FbConnection connection = new FbConnection(cs.ToString());
connection.Open();
FbRemoteEvent revent = new FbRemoteEvent(connection);
revent.AddEvents(new string[] { "new_order" });
// Add callback to the Firebird events
revent.RemoteEventCounts += new FbRemoteEventEventHandler(EventCounts);
// Queue events
revent.QueueEvents();
string sql = "INSERT INTO SALES (PO_NUMBER, CUST_NO, SALES_REP, ORDER_STATUS, " +
"ORDER_DATE, SHIP_DATE, DATE_NEEDED, PAID, QTY_ORDERED, TOTAL_VALUE, " +
"DISCOUNT, ITEM_TYPE) VALUES (@po_number, 1004, 11, 'new', " +
"'1991-03-04 00:00:00', '1991-03-05 00:00:00', NULL, 'y', 10, 5000, " +
"0.100000001490116, 'hardware');";
FbCommand command = new FbCommand(sql, connection);
command.Parameters.Add("@po_number", FbDbType.Char, 8);
for (int i = 360; i < 365; i++)
{
command.Parameters[0].Value = "V91E0" + i.ToString();
command.ExecuteNonQuery();
}
System.Threading.Thread.Sleep(2000);
connection.Close();
}
static void EventCounts(object sender, FbRemoteEventEventArgs args)
{
Console.WriteLine("Event {0} has {1} counts.", args.Name, args.Counts);
}
return to top
4. Database backup [v1.7].
[STAThread]
static void Main(string[] args)
{
FbConnectionStringBuilder cs = new FbConnectionStringBuilder();
cs.UserID = "SYSDBA";
cs.Password = "masterkey";
cs.Database = "nunit_testdb";
FbBackup backupSvc = new FbBackup();
backupSvc.ConnectionString = cs.ToString();
backupSvc.BackupFiles.Add(new FbBackupFile(@"c:\testdb.gbk", 2048));
backupSvc.Verbose = true;
backupSvc.Options = FbBackupFlags.IgnoreLimbo;
backupSvc.ServiceOutput += new ServiceOutputEventHandler(ServiceOutput);
backupSvc.Execute();
}
static void ServiceOutput(object sender, ServiceOutputEventArgs e)
{
Console.WriteLine(e.Message);
}
return to top
5. Database restore [v1.7].
[STAThread]
static void Main(string[] args)
{
FbConnectionStringBuilder cs = new FbConnectionStringBuilder();
cs.UserID = "SYSDBA";
cs.Password = "masterkey";
cs.Database = "nunit_testdb";
FbRestore restoreSvc = new FbRestore();
restoreSvc.ConnectionString = cs.ToString();
restoreSvc.BackupFiles.Add(new FbBackupFile(@"c:\testdb.gbk", 2048));
restoreSvc.Verbose = true;
restoreSvc.PageSize = 4096;
restoreSvc.Options = FbRestoreFlags.Create | FbRestoreFlags.Replace;
restoreSvc.ServiceOutput += new ServiceOutputEventHandler(ServiceOutput);
restoreSvc.Execute();
}
static void ServiceOutput(object sender, ServiceOutputEventArgs e)
{
Console.WriteLine(e.Message);
}
return to top
6. Database Schema [v2.0].
[STAThread]
static void Main(string[] args)
{
FbConnectionStringBuilder cs = new FbConnectionStringBuilder();
cs.DataSource = "localhost";
cs.Database = "employee.fdb";
cs.UserID = "SYSDBA";
cs.Password = "masterkey";
cs.Charset = "NONE";
cs.Pooling = false;
FbConnection connection = new FbConnection(cs.ToString());
connection.Open();
// Get the available metadata Collection names
DataTable metadataCollections = connection.GetSchema();
// Get datatype information
DataTable dataTypes = connection.GetSchema(DbMetaDataCollectionNames.DataTypes);
// Get DataSource Information
DataTable dataSourceInformation = connection.GetSchema(DbMetaDataCollectionNames.DataSourceInformation);
// Get available reserved word
DataTable reservedWords = connection.GetSchema(DbMetaDataCollectionNames.ReservedWords);
// Get the list of User Tables
// Restrictions:
// TABLE_CATALOG
// TABLE_SCHEMA
// TABLE_NAME
// TABLE_TYPE
DataTable userTables = connection.GetSchema("Tables", new string[] { null, null, null, "TABLE" });
// Get the list of System Tables
// Restrictions:
// TABLE_CATALOG
// TABLE_SCHEMA
// TABLE_NAME
// TABLE_TYPE
DataTable systemTables = connection.GetSchema("Tables", new string[] { null, null, null, "SYSTEM TABLE" });
// Get Table Columns
// Restrictions:
// TABLE_CATALOG
// TABLE_SCHEMA
// TABLE_NAME
// COLUMN_NAME
DataTable tableColumns = connection.GetSchema("Columns", new string[] { null, null, "TableName" });
connection.Close();
}
return to top
Back to Developer's Corner.
| 
