C# 中的 Easy SQL-CE 访问实用程序

新版本

请注意：出于某种原因，大多数人仍然下载此 Sql CE 访问实用工具，而一个更好、功能更丰富的版本已经存在。请参阅本文：EasySqlCe。  

上述新版本中的改进：例如，只需一行代码，您现在就可以创建一个全新的数据库、创建一个新表、创建一个新索引或检查是否存在可用于 tabledirect 方法的索引。此外，您可以选择在搜索表时是否使用通配符（“%”）。此外，底层所有代码都是新的，并且可以在 .NET 3.5 上运行，而旧版本需要 .NET 4.0。快来试试吧！

旧版本

• 下载演示 - 59.89 KB

引言

我想知道为什么需要编写如此多的代码才能以编程方式读取、写入、更新或删除数据表中的记录。我经常使用 SQL Server Compact Edition 3.5。每次为每个新数据表构建一个数据库助手类很快就让我感到厌烦。因此，我创建了许多方法，无需任何 sql 代码或 SqlCe 对象即可访问任何 SqlCe 数据表。唯一的必要投入是一个小类，其中包含属性名与数据表中字段名相同的属性。我创建了一个简单的演示来展示它是如何（轻松）工作的。

背景

此解决方案提供了上述方法，这些方法允许通过使用包含与感兴趣表的所有字段匹配的属性的简单派生类的实例来简单访问任何 SqlCe 数据库中的任何表中的数据。反射用于提取类的属性。然后使用此信息构造 SQL 语句。数据库将在方法的作用域内打开，记录集将被读取（或写入、更新或删除）并关闭。所有记录都转换为上述帮助类的对象并添加到列表中（或者列表被写入、更新或删除）。在网上冲浪时，我从未遇到过这样的解决方案。如果它已经存在，请告诉我。此外，我去年才开始用 C# 编程，所以我预计会在这里或那里使用一些笨拙的编程。也许我又发明了轮子。请告诉我，也告诉我是否需要更改一些内容。

Using the Code

要读取数据，只需声明一个类，其名称与数据表完全相同，并添加名称与数据表中字段完全相同的属性。提供一个连接字符串（使用提供的方法）以及一个上述类的空 List 对象和一个相同的类的“search”对象，该对象包含要搜索的属性（如字段）。该方法使用匹配resultset 的对象填充列表，并返回一个整数，该整数计算检索到的对象的数量，如果发生错误则返回-1。其他函数的使用方式大致相同。显然，必须安装 SQL Server Compact Edition 3.5。数据库必须添加到解决方案中（只需将其拖到解决方案资源管理器中，无需 tableadapter 或其他任何东西）。不要忘记添加对System.Data.SqlServerCe的引用，当您要部署应用程序时，需要将sqlce-DLL 复制到您的应用程序文件夹：

• sqlceca35.dll
• sqlcecompact35.dll
• sqlceer35EN.dll
• sqlceme35.dll
• sqlceoledb35.dll
• sqlceqp35.dll
• sqlcese35.dll

您必须花些时间使用这些方法才能了解它们。演示窗体尽可能简单。我没有为delete 和update 添加示例，因为这些方法的用法相同。代码中也提供了说明。

工作原理

一个简单的帮助类如下所示

/// <summary>
/// Declare a helper-class. Note: the name of the class must be exactly 
/// the name of the DataTable.
/// </summary>        
private class TestTable : SQLCEtools.BaseClass  // this inheritance from 
// baseclass is actually not really needed at present. I wanted to add 
// extra functionality but didn't get that far up till now.
{
    /// <summary>
    /// Always add a constructor: DateTime needs to be set to 
    /// DateTimeNull
    /// </summary>
    public TestTable()
    {
        this.TestTableID = null;
        this.Name = null;
        this.Date = SQLCEtools.DateTimeNull;
    }
    /// <summary>
    /// This overload is added to demonstrate searching
    /// </summary>
    /// <param name="name">String containing field 'name'</param>
    public TestTable(string name)
    {
        this.TestTableID = null;
        if (!String.IsNullOrEmpty(name)) this.Name = name;
        else this.Name = null;
        this.Date = SQLCEtools.DateTimeNull;
    }
    
    // Other methods can be added to process data or validate. 
    
    // All properties need to be nullable because only if they are null 
    // they are not used as a search-term. Furthermore, the names and 
    // types of all properties must match the names and types of the 
    // fields in the datatable. Finally, the unique identifier must have
    // the attribute [UniqueIdentifier]
    [UniqueIdentifier]
    public int? TestTableID { get; set; }
    public string Name { get; set; }
    public DateTime Date { get; set; }
    public bool? Checked { get; set; }
}

使用上述帮助类，数据表的信息将作为泛型参数传递给方法。首先，基类方法的定义

private static int BaseRead<T>(List<T> data, string table, 
	T search, string connect, string comparer) where T : BaseClass, new()

从这个基方法派生出ReadData(...) 和ReadLikeData(...) 方法。ReadData(...) 使用带有WHERE 子句的SELECT 语句，该子句将字段进行相等性比较（'='），而ReadLikeData(...) 使用带有LIKE 关键字的WHERE 子句，并在值的两边附加 '%'。后者发生在方法后期。我实现了一个重载，该重载返回T 类型的List 而不是int。如果发生异常，则返回null 。

这些方法的用法非常简单。只需使用 'new TestTable()' 作为searchparameter 调用该方法，即可省略WHERE 子句。因此，所有记录都将从datatable 中检索出来

List<TestTable> alldata = SQLCEtools.ReadData(new TestTable(), Connection());

也可以使用重载构造函数直接搜索名称。此外，List<TestTable>可以替换为var

var alldata = SQLCEtools.ReadData(new TestTable("some_name"), Connection());

在ReadData方法中，使用反射来检索泛型类型T的属性

PropertyInfo[] propinfs = typeof(T).GetProperties();

此外，使用属性名构造了一个SELECT 语句。同时，仅使用那些不为null 的属性构造了一个WHERE 子句（因此需要在帮助类中为可空属性）。请注意dynamic类型的用法。对于DateTime 类型，需要额外的代码来执行此操作（null 被定义为 1800-01-01）

foreach (PropertyInfo p in propinfs)
{
    fields += fields == "" ? p.Name : ", " + p.Name;
    dynamic propvalue = p.GetValue(search, null);
    // Solutions for properties of type DateTime
    DateTime dt = new DateTime();
    Type type = propvalue != null ? propvalue.GetType() : null;
    if (propvalue != null && propvalue.GetType() == dt.GetType()) dt = propvalue;
    // DateTime 1753-01-01 equals null
    if (propvalue != null && dt != DateTimeNull)
        wherestr += wherestr == "" ? p.Name + " " + comparer + " @" + p.Name.ToLower() 
            : " AND " + p.Name + " " + comparer + " @" + p.Name.ToLower();
}
// Create SQL SELECT statement with properties and search
string sql = "SELECT " + fields + " FROM " + table;
sql += wherestr == "" ? "" : " WHERE " + wherestr;

在数据库阶段，再次使用反射将带有值的参数添加到 SQL 语句中。请注意，在此部分，ReadData(...) 和ReadLikeData(...) 之间存在差异。另请注意dynamic类型的另一项用法

SqlCeCommand cmd = new SqlCeCommand(sql, cn);
cmd.CommandType = CommandType.Text;
// Add propertyvalues to WHERE-statement using reflection
foreach (PropertyInfo p in propinfs)
{
    dynamic propvalue = p.GetValue(search, null);
    // Except for DateTime values 1753-01-01 (defined as null)
    if (propvalue != null && !(propvalue.GetType() is DateTime && 
				propvalue != DateTimeNull))
    {
        if (comparer == "LIKE") propvalue = "%" + propvalue + "%";
        cmd.Parameters.AddWithValue("@" + p.Name.ToLower(), propvalue);
    }
}
SqlCeResultSet rs = cmd.ExecuteResultSet(ResultSetOptions.Scrollable);

最后，使用反射将resultset 中的记录转换为泛型对象，并添加到作为参数传递的列表中。再次，请注意dynamic类型的用法

var dataitem = new T();  // Object to put the field-values in
foreach (PropertyInfo p in propinfs)
{
    // Read database fields using reflection
    PropertyInfo singlepropinf = typeof(T).GetProperty(p.Name);
    int ordinal = rs.GetOrdinal(p.Name);
    dynamic result = rs.GetValue(ordinal);
    // Conversion to null in case field is DBNull
    if (result is DBNull)
    {
        if (singlepropinf.PropertyType.Equals(typeof(DateTime)))
        {
            // Fill data item with datetimenull
            singlepropinf.SetValue(dataitem, DateTimeNull, null); 
        }
        else
        {
            // Fill data item with null
            singlepropinf.SetValue(dataitem, null, null); 
        }
    }
    else
    {
        // Or fill data item with value
        singlepropinf.SetValue(dataitem, result, null); 
    }
}
data.Add(dataitem);  // And add the record to List<T> data.

在将记录插入表的方法中，在移动到列表中的下一个对象之前执行了一个额外的 SQL 命令。该命令（'SELECT @@IDENTITY'）返回最后一个记录的标识列。当使用ExecuteScalar() 执行此命令时，该值可用于（在本例中）将新记录的 ID 返回给用户。

// now create new command to get identity
cmd.CommandText = "SELECT @@IDENTITY";
// Get new identifier value, convert to int32
int propID = Convert.ToInt32((decimal)cmd.ExecuteScalar());
// And put identifier value in ID property for later reference
ID.SetValue(dat, propID, null); 
// change commandtext back to original text
cmd.CommandText = sql;

声明规则一目了然

请根据以下规则在您的应用程序中声明一个类

• 类的名称必须与datatable 的名称匹配。
• 为了使WriteData方法正常工作，datatable 中的唯一标识符必须设置“autoincrement”和“seed”为“1”。
• 属性的名称必须与数据表中的字段名称匹配。类型必须与字段的类型匹配。
• 表示唯一标识符的属性必须具有[UniqueIdentifier]属性，以使write-和update-方法能够识别数据表的标识符。
• 这些方法将使用所有属性，除了那些为null 的属性。因此，所有属性的类型都必须是可空的。
• DateTime 是前一条规则的一个例外。如果使用DateTime ，则必须声明一个构造函数，该构造函数将DateTime 等于SQLCEtools.DateTimeNull，这是SQLCEtools类的一个static 属性，代表 1753-1-1（这是 SQL CE 中可能的最低日期。如果需要，可以替换为另一个日期）。

关注点

正如预期的那样，每次迭代recordset 时使用反射的开销随着记录数的增加而增加。当recordset 大于约 1000 条记录时，性能损失开始变得显著。在这种情况下，优化的tabledirect 方法是首选，并且可能带来超过 25% 的性能提升。在我的情况下，这种情况并不经常发生。无论如何，请始终为搜索的字段创建索引。

我仅在 SQL Server Compact Edition 3.5 上测试了这些方法。显然，您将自行承担使用该实用工具的风险。

最后，将此解决方案移植到 SQL Server 或任何其他数据库系统应该不难，尽管我设想当需要这些系统时，需要更复杂的 SQL 语句，而这些语句在这里并未提供。

历史

我于 2011 年 3 月 2 日在“stackoverflow.com”上发布了 'readdata' 方法的第一个尝试，作为一个问题。目前，此实用工具是我为日常专业用途编写的应用程序的一部分。此网站上的第一个版本发布于 2011 年 8 月 6 日。进行了以下更新：

• 2011 年 8 月 8 日：向窗体添加了一个搜索按钮，以演示搜索datatable。
• 2011 年 8 月 12 日的错误修复：当使用WriteData-或UpdateData-方法时，Null值会导致 SQL 错误。实际上，我以前从未尝试过写入null值。
• 2011 年 9 月 15 日：当一个对象（或一个对象列表）被插入到datatable中时，该方法现在会检索唯一标识符，并将其写入传递给参数的对象中相应的属性。此外，还添加了演示数据库中缺失的列。
• 2011 年 9 月 19 日：在此更新之前，WriteData-和UpdateData-方法假设使用反射检索的第一个属性是datatable的唯一标识符。这工作得很好。然而，MSDN 文档指出，使用反射检索属性的顺序是不可预测的。因此，为了避免将来出现 SQL 错误或不可预测的行为，声明了一个属性（[UniqueIdentifier]）。此属性现在必须放在帮助类中代表数据表唯一标识符的属性之前（记住datatable必须自动增量并设置种子）。此外，DateTimeNull 现在定义为 1753-1-1（这是 SQL-CE 中可能的最低日期）。