在C＃中扩展DataTable [英] Extend DataTable in C#
问题描述
$ SourceManager 的静态构造函数遍历所有模块/类，并发现实现 ISource 的所有类。它将实例化这些中的每一个，并将其中的 IEnumerable 作为静态属性（称为 IEnumerable< ISource>来源。为了简单起见， ISource 有两个属性， DataTable Table {get; } 和 string UniqueName {get; } 。当实例化每个不同的 ISource 负责从SQL，MDX等填充其表。对于所有的 ISource 是我写的，到目前为止，加载表与所有 DataRow s实例化已经足够了。然而，我现在有一种情况，我想加载表与 DataRow 的懒惰，而不是全部在前面。我该怎么做？我将通过一个例子。
A static constructor for class SourceManager goes through all modules/classes and discovers all classes that implement ISource. It will instantiate each one of these and expose an IEnumerable of them as a static property called IEnumerable<ISource> Sources. For simplicity ISource has two properties, DataTable Table { get; } and string UniqueName { get; }. When instantiated each different ISource is responsible for populating its Table from SQL, MDX, etc. For all the ISource's I've written thus far, loading the Table with all DataRows when instantiated has been sufficient. However I now have a situation where I'd like to load the Table with DataRow's lazily, rather than all up front. How do I do this? I will walk through an example.
PermissionSource 实现 ISource 。它的表属性，具有私有集的值为新的PermissionDataTable（） 。 UniqueName 是权限。到目前为止，没有从数据库加载到这个表属性中的权限。
PermissionSource implements ISource. Its Table property, that has a private set is given a value of new PermissionDataTable(). Its UniqueName is "Permissions". As of right now there are no permissions loaded from the database into this Table property.
ISource permissionSource = SourceManager.Sources.
Where(s => "Permission".Equals(s.UniqueName)).First();
现在我们已经获得了 PermissionSource 但是通过一个接口。让我们获得许可。
Now we've obtained the PermissionSource, but through an interface. Let's get a permission.
DataRow row = permissionSource.Table.Rows.Cast<DataRow>().
Where(r => r["PermissionName"].Equals("PermissionName")).First()
我已经覆盖了 PermissionDataTable 中的行属性，以便上述，以某种方式获取数据库中与PermissionName相关联的权限的值。其他权限没有加载。
I've overriden the Rows property in PermissionDataTable so that the above, somehow, gets the value of the permissions associated with "PermissionName" in the database. Other permissions are not loaded.
我在权限系统中没有选择，我没有选择不使用数据表。
I don't have a choice in the permissions system and I don't have a choice to not use a DataTable.
编辑：
例如，我需要覆盖 DataTable 的行属性。 行虽然是 DataRowCollection 是密封。因此，在创建最小的自定义DataTable实现方面，就像我想要做的那样，没有太多的事情可以做。
In my example I would need to override the Rows property of DataTable. Rows, though, is a DataRowCollection which is sealed. Thus there isn't much that can be done in terms of creating a minimal custom DataTable implementation like I want to do.
推荐答案
我不知道我明白了使用DataTable的限制，但是当我需要刷新DataTable中的数据或使用不同的标准重新填充数据时，我曾经做过一件事情是创建一个从DataTable派生的新类包括对DataAdapter的引用，其中包含原始用于填充DataTable的连接和选择信息。
I am not sure I understand your restrictions in using a DataTable, but one thing I've done in the past when I needed to "refresh" the data in a DataTable or repopulate it using different criteria is to create a new class derived from DataTable that includes a reference to a DataAdapter with the connection and selection information originally used to fill the DataTable.
例如，DataTable子类可能看起来像 LazyDataTable 下面的代码。请注意，我添加了几种访问行的不同方法。查看本文末尾附近的 PermissionSource 和主要程序代码后，可能会更有意义。另外请注意，我并没有包括在每种情况下正确打开和关闭数据库连接的所有详细信息。如何处理这将取决于您的数据库访问模型（例如连接池，共享连接等）。
For example, the DataTable sub-class could look something like the LazyDataTable code below. Note that I have added several different methods of accessing the Rows. They might make more sense after taking a look at the PermissionSource and main Program code near the end of this post. Also, please note that I have not included all of the details related to properly opening and closing database connections in every case. How you handle that will depend on your model for database access (e.g. connection pooling, shared connections etc).
//using System.Data.Common;
public class LazyDataTable : DataTable {
protected DbDataAdapter Adapter { get; set; }
public LazyDataTable(DbDataAdapter a) {
Adapter = a;
}
/// <summary>
/// Save changes back to the database, using the DataAdapter
/// </summary>
public void Update() {
Adapter.Update(this);
}
/// <summary>
/// Fill this datatable using the SelectCommand in the DataAdapter
/// The DB connection and query have already been set.
/// </summary>
public void Fill() {
Adapter.Fill(this);
}
/// <summary>
/// read and return one row at a time, using IEnumerable syntax
/// (this example does not actually add the row to this table,
/// but that can be done as well, if desired.
/// </summary>
public IEnumerable<DataRow> LazyReadRows() {
using (var reader = OpenReader()) {
//Get the schema from the reader and copy it to this table.
var schema = reader.GetSchemaTable();
var values = new object[schema.Columns.Count];
while (reader.Read()) {
reader.GetValues(values);
var row = schema.NewRow();
row.ItemArray = values;
yield return row;
}
}
}
/// <summary>
/// Fill one row at a time, and return the new row.
/// </summary>
public DataRow ReadRow() {
if (_reader == null || _reader.IsClosed)
_reader = OpenReader();
//Get the schema from the reader and copy it to this table.
if (this.Columns.Count == 0)
this.Columns.AddRange(_reader.GetSchemaTable().Columns.Cast<DataColumn>().ToArray());
if (!_reader.Read()) {
_reader.Dispose();
return null;
}
var values = new object[_reader.FieldCount];
_reader.GetValues(values);
return this.Rows.Add(values);
}
private DbDataReader _reader = null;
private DbDataReader OpenReader() {
OpenConnect();
return Adapter.SelectCommand.ExecuteReader();
}
private void OpenConnect() {
var cn = Adapter.SelectCommand.Connection;
if (cn.State == ConnectionState.Closed)
cn.Open();
}
/// <summary>
/// Change a Parameter in the SelectCommand, to filter which rows to retrieve.
/// </summary>
public void SetSelectParam(string name, object value) {
var selparams = Adapter.SelectCommand.Parameters;
selparams[name].Value = value;
}
}
然后你的 PermissionSource 将创建一个 LazyDataTable ，并适当地设置DataAdapter（包括连接和SELECT命令）。它不会填写DataTable，而是将其返回空，稍后再由应用程序代码填写。所以你的 PermissionSource 可能会像下面的代码一样。我使用 System.Data.OleDb 数据对象作为示例，但您将使用任何所需的ADO提供程序。
Then your PermissionSource would create a LazyDataTable and set the DataAdapter (including the connection and SELECT command) appropriately. It wouldn't fill the DataTable, but would instead return it empty, to be filled later, by the application code. So your PermissionSource might something like the code below. I've used System.Data.OleDb data objects as an example, but you would use whatever ADO providers you want.
interface ISource {
public DataTable Table { get; }
string UniqueName { get; }
}
public class PermissionSource : ISource {
/// <summary>
/// Loads a DataTable with all of the information to load it lazily.
/// </summary>
public DataTable Table {
get {
const string SELECT_CMD = "SELECT * FROM [Permissions] WHERE ([PermissionName] IS NULL OR [PermissionName]=@p1) AND [OtherProperty]=@p2";
var conn = new OleDbConnection("...ConnectionString...");
var selectCmd = new OleDbCommand(SELECT_CMD, conn);
selectCmd.Parameters.AddWithValue("p1", "PermissionName");
selectCmd.Parameters.AddWithValue("p2", 0);
var adapter = new OleDbDataAdapter(selectCmd);
var builder = new OleDbCommandBuilder(adapter); //used to generate the UPDATE and DELETE commands...
adapter.UpdateCommand = builder.GetUpdateCommand(); //etc.
//Do NOT fill the table here. Instead, let the caller fill it.
return new LazyDataTable(adapter);
}
}
public string UniqueName { get { return "Permission"; } }
}
您的主程序代码将使用 PermissionSource 和 LazyDataTable 如下：
Your main program code would use PermissionSource and LazyDataTable as follows:
static class Program {
void GetPermissions() {
ISource permissionSource = SourceManager.Sources.
Where(s => "Permission".Equals(s.UniqueName)).First();
var table = permissionSource.Table as LazyDataTable;
table.SetSelectParam("PermissionName", "Admin");
//If you want to fill ALL rows in one step:
table.Fill();
// OR If you want to fill one row at a time, and add it to the table:
DataRow row;
while(null != (row = table.ReadRow())) {
//do something with each individual row. Exit whenever desired.
Console.WriteLine(row["PermissionName"]);
}
// OR If you prefer IEnumerable semantics:
DataRow row = table.LazyReadRows().FirstOrDefault(someValue.Equals(row["columnname"]));
//OR use foreach, etc. Rows are still ONLY read one at a time, each time IEnumerator.MoveNext() is called.
foreach (var row in table.LazyReadRows())
if (row["someColumn"] == "someValue")
DoSomething(row["anothercolumn"]);
}
}
你可以混合和匹配显示的LazyDataTable的部分这里在你的应用程序限制内完成你想要的。当然，如果您切换到不同的共享数据模式，那么当然会更好一些，但是如果您必须从每个Source返回一个DataTable，那么至少您可以在必要时返回一个功能更强大的DataTable按照我在这里展示的方式进行分类。这样，您可以传回更多信息，您可以根据需要填写表格。
我仍然鼓励您查看LinqToSQL以及可能尝试转换为简单地传回一个DbDataReader或类似于我在此显示的LazyDataTable的其他对象，这将允许您自定义原始查询（例如通过使用 SetSelectParam 方法），并一次读取一行中的数据。
You can certainly mix and match parts of the LazyDataTable shown here to achieve exactly what you want within your application constraints. It would of course be much better if you could switch to a different model of sharing data, but if you MUST return a DataTable from each Source, then at least you can return a more functional DataTable when necessary by subclassing it as I've demonstrated here. This allows you to pass back more information, which you can use to fill the table as you see fit.
I would still encourage you to look into LinqToSQL as well as possibly trying to switch to simply passing back a DbDataReader or some other object similar to the LazyDataTable I've shown here that will allow you to both customize the original query (e.g. by using the SetSelectParam method) and also to read the data in one row at a time.
希望帮助！
这篇关于在C＃中扩展DataTable的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
