.NET — Examples of use (连接池，更新blob字段，事件，备份，恢复，取数据库系统信息)

.NET — 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();

}

其它有用信息：

http://blog.csdn.net/salc3k/article/details/6918143