嵌入式数据库包含很多,比如常用的sqlite、Access,此类文件类数据库不支持并发,是限制了桌面端系统的瓶颈。
sqlite并发可使用内存模式,弊端是系统异常或者电脑断电是造成数据丢失。
可尝试使用BerkeleyDB 或者FireBird 内嵌是数据库来满足需求。
本篇讲述FireBird 数据库
FireBird 是一种嵌入式关系型的数据库,支持高并发等特性。
使用方法:
- 数据库打开
- 增删改查
- 数据库创建、还原、备份
可视化工具
主要代码 详见demo
/// <summary>
/// Open FbConnection
/// </summary>
/// <remarks>
/// FbConnection conn = OpenConnection();
/// </remarks>
/// <returns>FbConnection</returns>
public FbConnection OpenConnection()
{
FbConnection connection = null;
try
{
connection = new FbConnection(connStr);
connection.Open();
}
catch
{
if (connection != null)
connection.Close();
}
return connection;
}
/// <summary>
/// Execute a FbCommand (that returns no resultset) using an existing SQL Transaction
/// using the provided parameters.
/// </summary>
/// <remarks>
/// int result = ExecuteNonQuery( CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
/// </remarks>
/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
/// <param name="commandText">the stored procedure name or T-SQL command</param>
/// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
/// <returns>an int representing the number of rows affected by the command</returns>
public int ExecuteNonQuery(CommandType cmdType, string cmdText, params FbParameter[] commandParameters)
{
FbCommand cmd = new FbCommand();
using (FbConnection conn = OpenConnection())
{
PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
int val = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return val;
}
}
/// <summary>
/// Execute a FbCommand (that returns no resultset) using an existing SQL Transaction
/// using the provided parameters.
/// </summary>
/// <remarks>
/// int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
/// </remarks>
/// <param name="trans">an existing sql transaction</param>
/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
/// <param name="commandText">the stored procedure name or T-SQL command</param>
/// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
/// <returns>an int representing the number of rows affected by the command</returns>
public int ExecuteNonQuery(FbTransaction trans, CommandType cmdType, string cmdText, params FbParameter[] commandParameters)
{
FbCommand cmd = new FbCommand();
using (FbConnection conn = OpenConnection())
{
PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
int val = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return val;
}
}
/// <summary>
/// Execute a FbCommand that returns a resultset against the database specified in the connection string
/// using the provided parameters.
/// </summary>
/// <remarks>
/// SqlDataReader r = ExecuteReader( CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
/// </remarks>
/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
/// <param name="commandText">the stored procedure name or T-SQL command</param>
/// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
/// <returns>A SqlDataReader containing the results</returns>
public FbDataReader ExecuteReader(CommandType cmdType, string cmdText, params FbParameter[] commandParameters)
{
FbCommand cmd = new FbCommand();
FbConnection conn = OpenConnection();
// we use a try/catch here because if the method throws an exception we want to
// close the connection throw code, because no datareader will exist, hence the
// commandBehaviour.CloseConnection will not work
try
{
PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
FbDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
cmd.Parameters.Clear();
return rdr;
}
catch
{
conn.Close();
throw;
}
}
/// <summary>
/// Execute a FbCommand that returns the first column of the first record against an existing database connection
/// using the provided parameters.
/// </summary>
/// <remarks>
/// Object obj = ExecuteScalar(CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
/// </remarks>
/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
/// <param name="commandText">the stored procedure name or T-SQL command</param>
/// <param name="commandParameters">an array of FbParameter used to execute the command</param>
/// <returns>An object that should be converted to the expected type using Convert.To{Type}</returns>
public object ExecuteScalar(CommandType cmdType, string cmdText, params FbParameter[] commandParameters)
{
FbCommand cmd = new FbCommand();
using (FbConnection conn = OpenConnection())
{
PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
object val = cmd.ExecuteScalar();
cmd.Parameters.Clear();
return val;
}
}
/// <summary>
/// Return a DataSet
/// </summary>
public void DoLoadDataSet(DataSet dataSet, string cmdText)
{
try
{
using (var da = new FbDataAdapter(cmdText, connStr))
{
da.Fill(dataSet, "dataSet");
}
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// Get the list of User Tables
/// TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE
/// </summary>
/// <returns></returns>
public DataTable GetTableNameList_User()
{
using (FbConnection conn = OpenConnection())
{
DataTable dta = conn.GetSchema("Tables", new string[] { null, null, null, "TABLE" });
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()
{
using (FbConnection conn = OpenConnection())
{
DataTable dta = conn.GetSchema("Tables", new string[] { null, null, null, "SYSTEM TABLE" });
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 = "")
{
using (FbConnection conn = OpenConnection())
{
DataTable dta = string.IsNullOrWhiteSpace(tableName)
? conn.GetSchema("Columns", new string[] { null, null, null })
: conn.GetSchema("Columns", new string[] { null, null, tableName.ToUpper() });
return dta;
}
}
/// <summary>
/// 创建数据库
/// </summary>
/// <param name="pathScript">1.sql以;分割 </param>
public void CreateEmbeddedDb(string pathScript)
{
if (File.Exists(path))
{
return;
}
// create a new database
FbConnection.CreateDatabase(connStr);
// parse the SQL script
FbScript script = new FbScript(pathScript);
script.Parse();
// execute the SQL script
using (FbConnection c = new FbConnection(connStr))
{
FbBatchExecution fbe = new FbBatchExecution(c);
foreach (FbStatement cmd in script.Results)
{
fbe.Statements.Add(cmd);
}
fbe.Execute();
}
}
/// <summary>
/// 数据库还原
/// </summary>
/// <param name="pathBak">文件路径</param>
public void RestoreDb(string pathBak)
{
try
{
FbRestore res = new FbRestore();
res.ConnectionString = connStr;
res.BackupFiles.Add(new FbBackupFile(pathBak, 2048));
res.Verbose = true;
res.PageSize = 4096;
res.Options = FbRestoreFlags.Create | FbRestoreFlags.Replace;
res.Execute();
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// 数据库备份
/// </summary>
/// <param name="pathBak">文件路径</param>
public void BackUpDb(string pathBak)
{
try
{
FbBackup bakup = new FbBackup();
bakup.ConnectionString = connStr;
bakup.BackupFiles.Add(new FbBackupFile(pathBak, 2048));
bakup.Verbose = true;
bakup.Options = FbBackupFlags.IgnoreLimbo;
bakup.Execute();
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// Prepare a command for execution
/// </summary>
/// <param name="cmd">FbCommand object</param>
/// <param name="conn">FbConnection object</param>
/// <param name="trans">FbTransaction object</param>
/// <param name="cmdType">Cmd type e.g. stored procedure or text</param>
/// <param name="cmdText">Command text, e.g. Select * from Products</param>
/// <param name="cmdParms">FbParameter to use in the command</param>
private void PrepareCommand(FbCommand cmd, FbConnection conn, FbTransaction trans, CommandType cmdType, string cmdText, FbParameter[] cmdParms)
{
if (conn.State != ConnectionState.Open)
conn.Open();
cmd.Connection = conn;
cmd.CommandText = cmdText;
if (trans != null)
cmd.Transaction = trans;
cmd.CommandType = cmdType;
if (cmdParms != null)
{
foreach (FbParameter parm in cmdParms)
cmd.Parameters.Add(parm);
}
}
/// <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();
}