How to get dynamic data using ado.net and show in in webgrid

ASP.NET MVC4 Application uses modified WebMatrix Dynamicrecord to get dynamic data from ADO.NET
and show it in WebGrid.
Running application causes stange exception

Invalid attempt to read when no data is present

in GetNonNullValue(int i)

at line

var value = Record[i];

Using foreach as shown in comment does not work in Mono as discussed in https://github.com/npgsql/npgsql/issues/295 . So application uses while but while does not work in Windows also.

I tried to update solution to use MVC 4.0.0.1 by pressing Update all in nuget package manager updates window but got exception that solution cannot updated.
How to get dynamic data in while loop and use latest MVC4 dll ?

Whole solution is available at http://wikisend.com/download/360760/invalidattemptoreadwhennodataispresent.zip

Controller:

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;
}
}
}

View:

@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 is used from MVC4 source code with modifications:

// 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;
}
}
}
}

I posted this also in http://stackoverflow.com/questions/26440458/how-to-get-dynamic-data-from-ado-net-in-while-loop

Leave a Reply