我不确定我是否理解您使用 DataTable 的限制，但是当我需要“刷新”DataTable 中的数据或使用不同的条件重新填充它时，我过去做过的一件事是创建一个从 DataTable 派生的新类其中包括对 DataAdapter 的引用，其中包含最初用于填充 DataTable 的连接和选择信息。
例如，DataTable 子类可能类似于LazyDataTable代码如下。请注意，我添加了几种不同的访问行的方法。看完之后他们可能会更有意义PermissionSource和主要Program代码在本文末尾附近。另外，请注意，我没有包含与在每种情况下正确打开和关闭数据库连接相关的所有详细信息。如何处理取决于您的数据库访问模型（例如连接池、共享连接等）。
//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 提供程序。
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 and LazyDataTable如下：
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 的各个部分，以在应用程序限制内准确实现您想要的效果。如果您可以切换到不同的共享数据模型当然会更好，但是如果您MUST从每个源返回一个 DataTable，那么至少您可以在必要时通过子类化它来返回一个功能更强大的 DataTable，正如我在此处演示的那样。这允许您传回更多信息，您可以使用这些信息来填充您认为合适的表格。
我仍然鼓励您研究 LinqToSQL 以及可能尝试切换到简单地传回 DbDataReader 或类似于我在此处显示的 LazyDataTable 的其他对象，这将允许您自定义原始查询（例如，通过使用SetSelectParam方法），也可以一次读取一行数据。
希望有帮助！
