简易数据访问层

Plamen Kovandjiev

4.56/5 (25投票s)

2008 年 10 月 20 日

CPOL

7分钟阅读

440135

6366

该库为不同的数据源提供了简单的数据访问工具。

下载源代码 - 1.21 MB

引言

该库为不同的数据源提供了简单统一的访问。当前版本支持 MSSQL、PostgreSQL、FireBird、MySQL、SQLite、Oracle、SqlServerCE 和 OLEDB。用户可以轻松扩展该库并添加新数据库。

库中使用了几种设计模式——抽象工厂用于选择必要的数据源，以及泛型单例模式，它保证数据源只有一个实例。

该库可以轻松地将数据从表格式转换为列表格式，这有助于实现实体数据模型 (EDM) 模式。通过存储过程 (SP) 和在更高层（如业务层）轻松处理接收到的数据，可以达到最佳性能。

Using the Code

该库的入口点是数据层 (Data Layer)。它的目的是向与数据库通信的特定类提供 IDataLayer 类型的接口。Data Layer 类只包含一个方法：GetInstance，它返回所选连接的适当类实例。

示例

private IDataLayer _dal;
        
public FormDataLayerTest()
{
    _dal = DataLayer.GetInstance();
}

实例化所需的设置位于 app.config 文件中。以下配置是强制性的

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <configSections>
    <section name="DataAccessLayer" 
      type="System.Configuration.DictionarySectionHandler, System, 
            Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" />
 </configSections>
  <DataAccessLayer>
    <add key="ConnectionString" 
      value="Server=localhost;User=SYSDBA;Password=masterkey;
             Database=C:\Program Files\Firebird\Firebird_2_1\
             examples\empbuild\EMPLOYEE.FDB"/>
    <add key="DatabaseType" value="Firebird"/>
    <!-- This key optional  -->
    <!-- This is a default value -->
    <add key="Singleton" value="true"/>
   </DataAccessLayer>
</configuration>

在这种情况下，创建了一个连接到 Firebird 数据库的实例，其 ConnectionString 为 Server=localhost;User=SYSDBA;Password=masterkey;Database=C:\Program Files\Firebird\Firebird_2_1\examples\empbuild\EMPLOYEE.FDB。该实例是单例类型的。

GetInstance 方法的重载有

_dal = DataLayer.GetInstance(DatabaseTypes.MSSql);

创建了一个支持连接到 MSSQL 数据库的类。该实例是单例类型的。

IDataLayer GetInstance(DatabaseTypes dbType, string connectionString);

创建了一个支持连接到 DatabaseTypes 类型数据库的类。此外，连接字符串作为参数传递。该实例是单例类型的。

IDataLayer GetInstance(DatabaseTypes dbType, string connectionString, bool singleton);

创建了一个支持连接到 DatabaseTypes 类型数据库的类。此外，连接字符串作为参数传递。可以从中选择该实例是否为单例类型。

IDataLayer GetInstance(DatabaseTypes dbType, bool singleton);

创建了一个支持连接到 DatabaseTypes 类型数据库的类。可以从中选择该实例是否为单例类型。

示例

_dal = DataLayer.GetInstance(DatabaseTypes.MSSql, false);
// Set connection string
_dal.ConnectionString = "Persist Security Info=True;Initial " + 
  "Catalog=Northwind;Data Source=(LOCAL);User ID=sa;Password=masterkey;";

在此，创建了一个支持连接到 MSSQL 数据库的类。还添加了连接字符串。

打开数据库连接

有两种方式

用户名和密码在连接字符串中设置。然后
```
_dal.Open();
```
可以直接调用以建立连接。
```
bool Open(); 
```
如果连接成功建立，则返回。
如果连接字符串中未设置用户名和密码，或者它们无效，则使用方法的以下重载
```
bool Open(string userName, string password);
```
它再次返回是否成功建立了连接。

通过以下方式检查由数据库操作引起的错误

if (_dal.IsError)
    MessageBox.Show(_dal.LastError);

LastError 是一个包含错误消息的属性。可以通过抛出异常来处理错误

_dal.RERaiseException = true;

该库支持查询的默认类型。如果未显式设置，则为

_dal.DefaultCommandType = CommandType.Text;

如果主要使用存储过程，则可以更改查询类型

_dal.DefaultCommandType = CommandType.StoredProcedure;

目的不是为每次查询更改类型。以下是一些示例

版本 1.3 中命令的顺序更改为以下

如果需要，SQL 查询、过程名称或表名称将设置为查询类型。如果类型与 _dal.DefaultCommandType 不同，则必须显式设置。例如，如果主要使用文本查询，现在想执行存储过程，则执行以下操作

_dal.Sql = "CustOrderHist";
_dal.CommandType = CommandType.StoredProcedure;

如果查询包含参数，则设置它们

_dal.AddParameter("CustomerID", "ALFKI");

调用执行查询的方法（取决于所需结果，方法不同；下面将对其进行描述）

DataTable dt = _dal.ExecuteDataTable();

在这种情况下，结果由多行多列组成，通过使用“ExecuteDataTable”，将其填充到一个表中。

使用查询执行方法

示例中的所有查询都针对 MS SQL Server 和 Northwind 数据库。您可以在下载文件中的此处找到创建脚本：..\SQL\ instnwnd.sql。

int ExecuteNonQuery();

它用于数据定义语言 (DDL) 语句和数据操作语言 (DML) 语句——INSERT、UPDATE、DELETE 和不返回结果的存储过程。它也可能用于具有输出参数的存储过程——可以在查询执行后读取它们。

示例

_dal.Sql = "   INSERT INTO Customers"
                + "       ( CustomerID,  CompanyName)"
                + " VALUES"
                + "       (@CustomerID, @CompanyName)";
_dal.AddParameter("CustomerID", "TESTC");
_dal.AddParameter("CompanyName", "Test company name");
 
int rowsAffected = _dal.ExecuteNonQuery();

结果返回受影响的行数。对于此特定示例，为 1。

object ExecuteScalar();

结果返回 RowSet 的第一行和第一列。如果 RowSet 为空，则返回 null。它主要用于返回单个值的 SELECT 查询。也用于返回主键值的 INSERT 查询。

示例

_dal.Sql =
    "  SELECT EmployeeID"
    + "  FROM Employees"
    + " WHERE EmployeeID > @ID"
    + " ORDER BY LastName";
_dal.AddParameter("ID", 2, DbType.Int32);
 
object o = _dal.ExecuteScalar();

DataTable ExecuteDataTable();

它主要用于返回多行多列的 SELECT 查询或存储过程。

示例

_dal.Sql =
    "  SELECT EmployeeID, LastName, FirstName, Title"
       + "  FROM Employees"
    + " WHERE EmployeeID > @ID"
    + " ORDER BY LastName";
_dal.AddParameter("ID", 2, DbType.Int32);
 
DataTable dt = _dal.ExecuteDataTable();

结果是一个填充了数据的表。在这种情况下，它有 4 列和 7 行。

DataSet ExecuteDataSet();

它通常用于返回多个表的连续 SELECT 查询。

示例

_dal.Sql = "SelInvoiceOrder";
_dal.CommandType = CommandType.StoredProcedure;
// ParameterDirection of ReturnValue
// must be specified before the other parameters.
IDbDataParameter ret = _dal.AddParameter("RETURN_VALUE", DbType.Int32, 
                                         ParameterDirection.ReturnValue);
_dal.AddParameter("EmployeeID", 1, DbType.Int32, ParameterDirection.Input);
_dal.AddParameter("OrderID", 10248, DbType.Int32, ParameterDirection.Input);
// Add parameters which will later be read a output values
IDbDataParameter outEmployeeCount = _dal.AddParameter("EmployeeCount", 
                                    DbType.Int32, ParameterDirection.Output);
IDbDataParameter outOrderCount = _dal.AddParameter("OrderCount", 
                                 DbType.Int32, ParameterDirection.Output);
DataSet ds = _dal.ExecuteDataSet();

// Reading the results of the parameters
int retValue = ret.Value == DBNull.Value ? 0 : (int)ret.Value;
int outInvCount = 
  outEmployeeCount.Value == DBNull.Value ? 0 : (int)outEmployeeCount.Value;
int outOrdCount = 
  outOrderCount.Value == DBNull.Value ? 0 : (int)outOrderCount.Value;

结果是一组填充了数据的表。在这种情况下，执行一个返回 2 个表并具有 3 个输出参数的 SP。

存储过程 "SelInvoiceOrder" 已添加到 "instnwnd.sql" 脚本中。

List<T> ExecuteAndFillList<T>();

它用于查询由多行多列组成的数据。数据被填充到指定类类型的列表中。它简化了后续使用 LINQ 的数据处理。

Characteristics

要设置类的属性，它们必须与列名相同，并具有“set;”访问权限。属性类型和从数据库读取的列类型必须相同。

示例

用于查询数据的类类型

public class IdValueEntity
{
    public Int32 Id { get; set; }
    public string Value { get; set; }
}

查询

_dal.Sql =
    "  SELECT OrderID AS ID, ProductID, ProductName AS Value, UnitPrice"
    + "  FROM [dbo].[Order Details Extended]";
// CommandType.Text is default
//_dal.CommandType = CommandType.Text;
List<IdValueEntity> list = _dal.ExecuteAndFillList<IdValueEntity>();

要设置类的属性，将更改结果中的列名：OrderID -> ID 和 ProductName -> Value。类中不存在的列将被简单地跳过。在这种情况下，列表包含 2155 个条目。

T ExecuteAndFill<T>();

它类似于之前的查询，但仅提取结果中的第一行。

示例

_dal.Sql =
    "  SELECT OrderID AS ID, ProductID, ProductName AS Value, UnitPrice"
    + "  FROM [dbo].[Order Details Extended]";
        // CommandType.Text is default
//_dal.CommandType = CommandType.Text;
 
IdValueEntity ent = _dal.ExecuteAndFill<IdValueEntity>();

要设置类的属性，将更改结果中的列名：OrderID -> ID 和 ProductName -> Value。类中不存在的列将被简单地跳过。

使用 OLEDB 连接

在以下示例中，使用了一个测试 Excel 文件。 "Sheet1" 工作表中的单元格 "D2" 被填充值为 55。

_dal = DataLayer.GetInstance(DatabaseTypes.OleDB);
            
_dal.ConnectionString =
            @"Provider=Microsoft.Jet.OLEDB.4.0;"
            + "Data Source="
            // Excel file name
            + "ExcelWorksheet.xls"
            + ";Extended Properties=\"Excel 8.0;HDR=NO;\"";

string sheetName = "Sheet1";
string cell = "D2";
_dal.Sql =
    " UPDATE [" + sheetName + "$" + cell + ":" + cell + "]"
    + " SET F1 = :value";
_dal.AddParameter("value", 55, DbType.Int32);
 
int rowsAffected = _dal.ExecuteNonQuery();

大部分示例和其他查询可以在测试应用程序中找到。为了测试特定的存储过程，创建了一个新的存储过程，并将其添加到 Northwind 数据库的创建数据库脚本中——..\SQL\instnwnd.sql。

内部逻辑

如果更改了连接字符串，当调用 Open 或 Execute 方法之一时，连接将关闭并重新打开。
如果连接已断开或未打开，则在调用 Execute 时会打开。如果失败，则会抛出异常。
当给出新的 SQL 语句时，将清除之前的参数（如果存在），并设置新的查询类型，默认为 DefaultCommandType。

关注点

该库提供了一种简单的工作方式。无需关心打开连接及其状态——如果需要重新连接或打开连接，会自动完成。另一个有用功能是查询结果可以轻松保存到应用程序使用的实体中。这样，保留了处理数据库的强大功能——如优化和特定查询。至于应用程序——数据以一种易于操作的方式接收。

历史

版本 1.3：对库的基本功能进行了更改。查询参数为标准类型，现在可以是输入-输出。查询和参数传递的处理方式已更改。新方法 ExecuteAndFill 允许从查询中提取单个数据行。

方法 ExecuteAndFillList 和 ExecuteAndFill 已针对效率进行了优化。该库与 MONO 100% 兼容。