如何在while循环中从ADO.NET获取动态数据
本文关键字：NET ADO 获取 动态 数据 while 循环 | 更新日期: 2023-09-27 18:27:02
ASP.NET MVC4应用程序使用修改后的WebMatrix Dynamicrecord从ADO.NET获取动态数据并将其显示在WebGrid中。
运行应用程序导致异常
不存在数据时读取的尝试无效
方法
private object GetNonNullValue(int i)
在线
var value = Record[i];
如中所述，使用注释中所示的foreach在Mono中不起作用https://github.com/npgsql/npgsql/issues/295。所以应用程序使用while，但while在Windows中不起作用。
如何在while循环中获取动态数据？
完整解决方案可在http://wikisend.com/download/360760/invalidattemptoreadwhennodataispresent.zip
控制器：
using Eeva.Business;
using Eeva.Erp.ViewModels;
using Npgsql;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Web.Mvc;
namespace Eeva.Erp.Controllers
{
public class ReportController : Controller
{
public ActionResult Test()
{
var data = TestData();
return View("ReportData", new ReportDataViewModel(data, ""));
}
IEnumerable<dynamic> TestData()
{
using (var connection = new NpgsqlConnection(ConnectionString()))
{
connection.Open();
DbCommand command = (DbCommand)connection.CreateCommand();
command.CommandText = "select 'A' union select 'B'";
using (command)
{
using (DbDataReader reader = command.ExecuteReader())
{
IEnumerable<string> columnNames = null;
while (reader.Read())
{
if (columnNames == null)
columnNames = GetColumnNames(reader);
yield return new EevaDynamicRecord(columnNames, reader);
}
//foreach (DbDataRecord record in reader)
//{
// if (columnNames == null)
// columnNames = GetColumnNames(record);
// yield return new EevaDynamicRecord(columnNames, record);
//}
}
}
}
}
static IEnumerable<string> GetColumnNames(IDataRecord record)
{
// Get all of the column names for this query
for (int i = 0; i < record.FieldCount; i++)
yield return record.GetName(i);
}
static string ConnectionString()
{
return new NpgsqlConnectionStringBuilder()
{
Host = "localhost",
UserName = "postgres",
}.ConnectionString;
}
}
}
ViewModel:
using System.Collections.Generic;
using System.Web.Mvc;
using Eeva.Business;
namespace Eeva.Erp.ViewModels
{
public class ReportDataViewModel
{
public IEnumerable<dynamic> Rows { get; set; }
public string Source;
public ReportDataViewModel(IEnumerable<dynamic> rows, string source)
{
Rows = rows;
Source = source;
}
}
}
视图：
@model Eeva.Erp.ViewModels.ReportDataViewModel
@using System.Web.Helpers
@{ Layout = null;
var gd = new WebGrid(source: Model.Rows );
}
<!DOCTYPE html>
<html>
<head></head>
<body>
@gd.GetHtml()
</body>
</html>
Dynamicrecord是从MVC4源代码中使用的，经过修改：
// Copyright (c) Microsoft Open Technologies, Inc. All rights reserved. See License.txt in the project root for license information.
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Diagnostics;
using System.Dynamic;
using System.Globalization;
using System.Linq;
using System.Text;
using WebMatrix.Data.Resources;
namespace Eeva.Business
{
public sealed class EevaDynamicRecord : DynamicObject, ICustomTypeDescriptor
{
public EevaDynamicRecord(IEnumerable<string> columnNames, IDataRecord record)
{
Debug.Assert(record != null, "record should not be null");
Debug.Assert(columnNames != null, "columnNames should not be null");
Columns = columnNames.ToList();
Record = record;
}
public IList<string> Columns { get; private set; }
private IDataRecord Record { get; set; }
public object this[string name]
{
get
{
for (int i = 0; i < Record.FieldCount; i++)
{
string normname = Record.GetName(i);
if (normname.Equals(name, StringComparison.InvariantCultureIgnoreCase))
return GetNonNullValue(i);
}
throw new InvalidOperationException("No column " + name);
}
}
public object this[int index]
{
get
{
return GetNonNullValue(index); // GetValue(Record[index]);
}
}
public string Field(int fldNo)
{
return Record.GetName(fldNo).ToUpperInvariant();
}
public override bool TryGetMember(GetMemberBinder binder, out object result)
{
result = this[binder.Name];
return true;
}
private object GetNonNullValue(int i)
{
var value = Record[i];
if (DBNull.Value == value || value == null)
{
var tt = Record.GetFieldType(i).Name;
switch (tt)
{
case "Decimal":
case "Int32":
case "Double":
return 0;
case "String":
return "";
case "DateTime":
return null;
case "Boolean":
// kui seda pole, siis demos lao kartoteek kartoteegi kaart annab vea:
return false;
}
return null;
}
if (value is decimal? || value is decimal)
return Convert.ChangeType(value, typeof(double));
if (value is string)
return value.ToString().TrimEnd();
return value;
}
public override IEnumerable<string> GetDynamicMemberNames()
{
return Columns;
}
private void VerifyColumn(string name)
{
// REVIEW: Perf
if (!Columns.Contains(name, StringComparer.OrdinalIgnoreCase))
{
throw new InvalidOperationException(
String.Format(CultureInfo.CurrentCulture,
"Invalid Column Name " + name));
}
}
AttributeCollection ICustomTypeDescriptor.GetAttributes()
{
return AttributeCollection.Empty;
}
string ICustomTypeDescriptor.GetClassName()
{
return null;
}
string ICustomTypeDescriptor.GetComponentName()
{
return null;
}
TypeConverter ICustomTypeDescriptor.GetConverter()
{
return null;
}
EventDescriptor ICustomTypeDescriptor.GetDefaultEvent()
{
return null;
}
PropertyDescriptor ICustomTypeDescriptor.GetDefaultProperty()
{
return null;
}
object ICustomTypeDescriptor.GetEditor(Type editorBaseType)
{
return null;
}
EventDescriptorCollection ICustomTypeDescriptor.GetEvents(Attribute[] attributes)
{
return EventDescriptorCollection.Empty;
}
EventDescriptorCollection ICustomTypeDescriptor.GetEvents()
{
return EventDescriptorCollection.Empty;
}
PropertyDescriptorCollection ICustomTypeDescriptor.GetProperties(Attribute[] attributes)
{
return ((ICustomTypeDescriptor)this).GetProperties();
}
PropertyDescriptorCollection ICustomTypeDescriptor.GetProperties()
{
// Get the name and type for each column name
var properties = from columnName in Columns
let columnIndex = Record.GetOrdinal(columnName)
let type = Record.GetFieldType(columnIndex)
select new DynamicPropertyDescriptor(columnName, type);
return new PropertyDescriptorCollection(properties.ToArray(), readOnly: true);
}
object ICustomTypeDescriptor.GetPropertyOwner(PropertyDescriptor pd)
{
return this;
}
private class DynamicPropertyDescriptor : PropertyDescriptor
{
private static readonly Attribute[] _empty = new Attribute[0];
private readonly Type _type;
public DynamicPropertyDescriptor(string name, Type type)
: base(name, _empty)
{
_type = type;
}
public override Type ComponentType
{
get { return typeof(EevaDynamicRecord); }
}
public override bool IsReadOnly
{
get { return true; }
}
public override Type PropertyType
{
get { return _type; }
}
public override bool CanResetValue(object component)
{
return false;
}
public override object GetValue(object component)
{
EevaDynamicRecord record = component as EevaDynamicRecord;
// REVIEW: Should we throw if the wrong object was passed in?
if (record != null)
{
return record[Name];
}
return null;
}
public override void ResetValue(object component)
{
throw new InvalidOperationException(
String.Format(CultureInfo.CurrentCulture,
"DataResources.RecordIsReadOnly", Name));
}
public override void SetValue(object component, object value)
{
throw new InvalidOperationException(
String.Format(CultureInfo.CurrentCulture,
"DataResources.RecordIsReadOnly", Name));
}
public override bool ShouldSerializeValue(object component)
{
return false;
}
}
}
}
我也在http://forums.asp.net/p/2013821/5795169.aspx
更新
有问题地创建每个动态记录的副本解决了问题：
Dictionary<string, object> Clonedict;
public EevaDynamicRecord Clone()
{
var res = new EevaDynamicRecord(Columns, Record);
res.Clonedict = new Dictionary<string, object>();
for (int i = 0; i < Record.FieldCount; i++)
res.Clonedict[Record.GetName(i)] = Record[i]);
return res;
// this also does not work:
// return (EevaDynamicRecord)this.MemberwiseClone();
}
有更好的解决方案吗？
我不知道这一定会起作用，但为了建议它，我需要发布一个答案，因为它包括一个代码示例。
如果我是对的，并且问题是因为在命令超出范围后试图从读取器访问数据而发生的，那么这将解决这个问题。
将您的Test方法替换为：
public ActionResult Test()
{
var data = TestData().ToArray();
return View("ReportData", new ReportDataViewModel(data, ""));
}
如果这没有帮助，请尝试用以下方法替换TestData方法：
IEnumerable<dynamic> TestData()
{
List<dynamic> results = new List<dynamic>();
using (var connection = new NpgsqlConnection(ConnectionString()))
{
connection.Open();
DbCommand command = (DbCommand)connection.CreateCommand();
command.CommandText = "select 'A' union select 'B'";
using (command)
{
using (DbDataReader reader = command.ExecuteReader())
{
IEnumerable<string> columnNames = null;
while (reader.Read())
{
if (columnNames == null)
columnNames = GetColumnNames(reader);
results.Add(new EevaDynamicRecord(columnNames, reader));
}
//foreach (DbDataRecord record in reader)
//{
// if (columnNames == null)
// columnNames = GetColumnNames(record);
// yield return new EevaDynamicRecord(columnNames, record);
//}
}
}
}
return results;
}