C# 操作FireBird(火鸟)数据库
连接火鸟数据库分两种方式：
(1)服务器方式
本地装有FireBird数据库，这种方式相对简单一些，只用到一个类(FirebirdSql.Data.FirebirdClient)就好；
(2)嵌入式方式
本地无须装FireBird数据库，但是需要引用一堆文件(说实话我也搞不清哪些要、哪些不要)，如下图：
具体代码实现：
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using FirebirdSql.Data.FirebirdClient;
using FirebirdSql.Data.Services;
namespace DC.DBHelp.FireBird
{
public class FireBirdHelper
{
private FbConnection _conn = null;
private string _strConn = "";
private readonly string _pathSys;
private readonly string _path;
/// <summary>
/// 构造函数
/// </summary>
/// <param name="pathSys">应用程序路径</param>
/// <param name="path">数据库fdb文件路径</param>
/// <param name="isServer">true=服务器模式；false=嵌入式模式;</param>
public FireBirdHelper(string pathSys, string path, bool isServer=false)
{
_pathSys = pathSys;
_path = path;
_strConn = isServer ? GetConnString_Server() : GetConnString_Client();
_conn = new FbConnection(_strConn);
}
/// <summary>
/// 服务器式连接
/// </summary>
/// <param name="path"></param>
/// <returns></returns>
private string GetConnString_Server()
{
FbConnectionStringBuilder cs = new FbConnectionStringBuilder();
cs.UserID = "SYSDBA";
cs.Password = "masterkey";
cs.Database = _path;
cs.DataSource = "localhost";
cs.Charset = "UTF8";
cs.Port = 3050;
cs.Dialect = 3;
cs.Role = "";
cs.ConnectionLifeTime = 15;
cs.Pooling = true;
cs.MinPoolSize = 0;
cs.MaxPoolSize = 50;
cs.PacketSize = 8192;
cs.ServerType = FbServerType.Default;
return cs.ToString();
}
/// <summary>
/// 嵌入式连接
/// </summary>
/// <param name="path"></param>
/// <returns></returns>
private string GetConnString_Client()
{
FbConnectionStringBuilder cs = new FbConnectionStringBuilder();
cs.ClientLibrary = $"{_pathSys}\\fbembed.dll";
cs.UserID = "SYSDBA";
cs.Password = "masterkey";
cs.Database = _path;
cs.Charset = "UTF8";
cs.ServerType = FbServerType.Embedded;
return cs.ToString();
}
private void MyOpen()
{
if (_conn.State != ConnectionState.Open)
_conn.Open();
}
private void MyClose()
{
if (_conn.State != ConnectionState.Closed)
_conn.Close();
}
/// <summary>
/// 数据库连接测试 (链接目标库)
/// </summary>
/// <param name="di"></param>
/// <param name="errInfo">连接失败时的异常信息</param>
/// <returns></returns>
public bool ConnectTest(out string errInfo)
{
errInfo = string.Empty;
try
{
_conn = new FbConnection(_strConn);
MyOpen();
MyClose();
}
catch (Exception ex)
{
errInfo = $"连接失败。失败原因如下：{ex.Message}";
}
return string.IsNullOrEmpty(errInfo);
}
public DataSet ExecuteSqlGetDs(string sql)
{
DataSet ds = new DataSet();
MyOpen();
FbCommand cmd = new FbCommand(sql, _conn);
cmd.CommandType = CommandType.Text;
cmd.CommandTimeout = 60;
FbDataAdapter fbAda = new FbDataAdapter(cmd);
fbAda.Fill(ds);
fbAda.Dispose();
cmd.Dispose();
MyClose();
return ds;
}
public DataTable ExecuteSqlGetDt(string sql,int topRows)
{
DataTable dt = new DataTable();
MyOpen();
FbCommand cmd = new FbCommand(sql, _conn);
cmd.CommandType = CommandType.Text;
cmd.CommandTimeout = 60;
FbDataAdapter fbAda = new FbDataAdapter(cmd);
fbAda.Fill(0,topRows,dt);
fbAda.Dispose();
cmd.Dispose();
MyClose();
return dt;
}
/// <summary>
/// 执行SQL,返回受影响的行数
/// </summary>
public int ExecuteNonQuery(string sql, int timeOut = 5)
{
MyOpen();
FbCommand cmd = new FbCommand(sql, _conn);
cmd.CommandType = CommandType.Text;
cmd.CommandTimeout = 60;
int res = cmd.ExecuteNonQuery();
cmd.Dispose();
MyClose();
return res;
}
/// <summary>
/// Get the list of User Tables
/// TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE
/// </summary>
/// <returns></returns>
public DataTable GetTableNameList_User()
{
MyOpen();
DataTable dta=_conn.GetSchema("Tables", new string[] { null, null, null, "TABLE" });
MyClose();
if (dta != null)
{
DataTable dt = new DataTable();
dt.Columns.Add("Name");
foreach (DataRow dra in dta.Rows)
{
DataRow dr = dt.NewRow();
dr[0] = dra["TABLE_NAME"].ToString().Trim();
dt.Rows.Add(dr);
}
return dt;
}
return null;
}
/// <summary>
/// Get the list of System Tables
/// TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE
/// </summary>
/// <returns></returns>
public DataTable GetTableNameList_Sys()
{
MyOpen();
DataTable dta = _conn.GetSchema("Tables", new string[] { null, null, null, "SYSTEM TABLE" });
MyClose();
return dta;
}
/// <summary>
/// Get Table Columns
/// TABLE_NAME,COLUMN_NAME,COLUMN_DATA_TYPE,COLUMN_SIZE,ORDINAL_POSITION
/// </summary>
/// <param name="tableName"></param>
/// <returns></returns>
public DataTable GetColumnInfo(string tableName = "")
{
MyOpen();
DataTable dta =string.IsNullOrWhiteSpace(tableName)
? _conn.GetSchema("Columns", new string[] { null, null, null })
: _conn.GetSchema("Columns", new string[] { null, null, tableName.ToUpper() });
MyClose();
return dta;
}
/// <summary>
/// 数据库还原
/// </summary>
/// <param name="pathBak">文件路径</param>
public void RestoreDb(string pathBak)
{
try
{
FbRestore res = new FbRestore();
res.ConnectionString = _strConn;
res.BackupFiles.Add(new FbBackupFile(pathBak, 2048));
res.Verbose = true;
res.PageSize = 4096;
res.Options = FbRestoreFlags.Create | FbRestoreFlags.Replace;
res.Execute();
}
catch (Exception ex)
{
}
}
/// <summary>
/// 数据库备份
/// </summary>
/// <param name="pathBak">文件路径</param>
public void BackUpDb(string pathBak)
{
try
{
FbBackup bakup = new FbBackup();
bakup.ConnectionString = _strConn;
bakup.BackupFiles.Add(new FbBackupFile(pathBak, 2048));
bakup.Verbose = true;
bakup.Options = FbBackupFlags.IgnoreLimbo;
bakup.Execute();
}
catch (Exception ex)
{
}
}
}
}