TheChaseMan's Frenetic SoapBox

Gaining the Simplicty of DataAdapter.Fill with DataReader Extension Methods

Sean Chase — Sun, 27 Jun 2010 13:39:00 GMT

With all of the hype around Entity Framework and LINQ to SQL, there are still some of us out there that are still going old school and rolling our own POCOs. I know ORMs are supposed to be super-duper-bleeding-edge, but as much as I want to be like all of the other cool kids, for large reporting-based systems I end up writing some prettty nasty stored procs and fill up my POCOs. At my last gig we had a pretty sweet architecture built around a custom "typed table" concept. The idea was to simplify the DAL code (e.g. DataAdapter.Fill(customTable)) while getting the syntactic sugar in the IDE. Another idea I have is similar to that, except in the form of an extension method on DataReader.

First, we create a custom attribute type. This is much like LINQ to SQL where the column mapping is made between the property and the query result (or column name) from the database. We'll apply this to our POCO properties where applicable.

namespace PocoFiller {
     [AttributeUsage(AttributeTargets.Property)]
    public class ColumnMappingAttribute : Attribute {
        string source = string.Empty;
 
        public ColumnMappingAttribute() { }
 
        public string Source { get { return this.source; } set { this.source = value; } }
    }
}

Next, we create the extension method on data reader. It has to account for nullable types as well as non-nullables and we do that using some reflection trickery (yes this could be optimized and cleaned up a bit, but you get the idea)...

using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Reflection;
 
namespace PocoFiller {
    static public class DataReaderExtensions {
        static public System.Collections.Generic.List<T> ToList<T>(this SqlDataReader reader) where T : new() {
            return reader.ToList<T>(true);
        }
 
        static public System.Collections.Generic.List<T> ToList<T>(this SqlDataReader reader, bool ignoreConvertExceptions) where T : new() {
            var results = new List<T>();
            while (reader.Read()) {
                T result = new T();
                results.Add(result);
                Type t = result.GetType();
 
                PropertyInfo[] properties = t.GetProperties(System.Reflection.BindingFlags.IgnoreCase
                        | System.Reflection.BindingFlags.Public
                        | System.Reflection.BindingFlags.Instance);
 
                foreach (var property in properties) {
                    object[] attributes = property.GetCustomAttributes(typeof(ColumnMappingAttribute), true);
                    if (attributes != null && attributes.Length > 0) {
                        ColumnMappingAttribute cma = attributes[0] as ColumnMappingAttribute;
                        
                        object dataValue = reader[cma.Source];
                        if (DBNull.Value == dataValue) dataValue = null;
 
                        if (dataValue != null && property.PropertyType != dataValue.GetType()) {
                            try {
                                if (property.PropertyType.IsGenericType && property.PropertyType.GetGenericTypeDefinition() == typeof(Nullable<>)) {
                                    Type[] genericArgs = property.PropertyType.GetGenericArguments();
                                    if (genericArgs.Length > 0) {
                                        dataValue = Convert.ChangeType(dataValue, genericArgs[0]);
                                    }
                                }
                                else {
                                    dataValue = Convert.ChangeType(dataValue, property.PropertyType);
                                }
                            }
                            catch {
                                throw;
                            }
                        }
 
                        try {
                            property.SetValue(result, dataValue, null);
                        }
                        catch (ArgumentException) {
                            if (!ignoreConvertExceptions) {
                                throw;
                            }
                        }
 
                    }
                }
            }
            return results;
        }
    }
}

Then we simply create our POCO type(s) and apply the attribute to map the properties to the column values. In this example, I alias FirstName to "first_name" just for example...

namespace PocoFiller {
    public class Employee {
        [ColumnMapping(Source = "ContactID")]
        public int ContactID { get; set; }
 
        [ColumnMapping(Source = "EmployeeID")]
        public int? EmployeeID { get; set; }
 
        [ColumnMapping(Source = "first_name")]
        public string FirstName { get; set; }
 
        [ColumnMapping(Source = "LastName")]
        public string LastName { get; set; }
 
        [ColumnMapping(Source = "Title")]
        public string Title { get; set; }
 
        [ColumnMapping(Source = "HireDate")]
        public DateTime? HireDate { get; set; }
    }
}

Use the ToList<T> extension method and voila!

using System;
using System.Data.SqlClient;
using System.Data;
using System.Collections.Generic;
 
namespace PocoFiller {
    class Program {
        const string connectionString = "Integrated Security=SSPI;Data Source=.;Initial Catalog=AdventureWorks";
 
        static void Main(string[] args) {
            var sql = @"select c.ContactID, e.EmployeeID, c.FirstName first_name, c.LastName, e.Title, e.HireDate
                        from HumanResources.Employee e inner join Person.Contact c
                         on c.ContactID = e.ContactID
                        where e.ManagerID = 21";
 
            var employees = new List<Employee>();
            using (var connection = new SqlConnection(connectionString))
            using (var command = new SqlCommand(sql, connection)) {
                command.CommandType = CommandType.Text;
                connection.Open();
 
                using (SqlDataReader reader = command.ExecuteReader()) {
                    employees = reader.ToList<Employee>();
                }
            }
 
            foreach (var employee in employees) {
                Console.WriteLine("{0}\t{1}\t{2}\t{3}\t{4}", employee.ContactID,
                    employee.EmployeeID,
                    employee.FirstName,
                    employee.LastName,
                    employee.Title);
            }
        }
    }
}

SQL 2008 IDE Awesomeness

Sean Chase — Wed, 21 Apr 2010 09:58:00 GMT

Not really a messagebox I want to see from my IDE

LINQ Query to DataTable (DataRows)

Sean Chase — Sat, 06 Jun 2009 09:20:00 GMT

Interested in writing a LINQ query to fill a DataTable? Normally I don't condone writing ugly code but let's have some fun. In this example, an anonymous type array is created followed by a DataTable. Then a LINQ query that includes a generic Func delegate encapsulating a lambda expression which is invoked to create a new DataRow based on the DataTable structure and then fill it with the properties from the anonymous type. The results (IEnumerable) are then added to the DataTable and then the DataTable is serialized to the Console output stream. In the words of Dilbert: if this is still too readable I can photocopy it and then run it through the fax machine a few times...

using System;

using System.Data;

using System.Linq;

namespace App {

    class Program {

        static void Main(string[] args) {

            var pretendThisIsADatabaseTable = new[] { new { Name = "Steve", Age = 33 }, new { Name = "Doug", Age = 34 } };

            //Create empty destination data table

            DataTable personTable = new DataTable();

            personTable.TableName = "PersonTable";

            personTable.Columns.Add("Name", typeof(string));

            personTable.Columns.Add("Age", typeof(int));

            //Query anonymous type array as DataRow objects

            var results = from person in pretendThisIsADatabaseTable

                          select new Func<DataRow, string, int, DataRow>(

                              (DataRow row, string name, int age) => {

                                  row["Name"] = name;

                                  row["Age"] = age;

                                  return row;

                              }

                          )

                          .Invoke(personTable.NewRow(), person.Name, person.Age);

            //put rows into data table

            results.ToList().ForEach(row => personTable.Rows.Add(row));

            personTable.WriteXml(Console.Out);

        }

    }

}

EDIT: everything above is pure cheese. Here's a real solution via my old co-worker Shawn :)

class Program
    {
        static void Main(string[] args)
        {
            var data = new[] {
                            new {FirstName="Bill", LastName="Clinton", ID=1, WasPresident=true}
                            , new {FirstName="John", LastName="McCain", ID=2, WasPresident=false}
                            , new {FirstName="George", LastName="Bush", ID=2, WasPresident=true}
            };

            DataTable table = (from d in data
                               where d.WasPresident==true
                               select d).AsDataTable();

            table.WriteXml(Console.Out);
        }
    }

Extension method below is required for the sugar above (AsDataTable)

static class Extensions
    {
        public static DataTable AsDataTable<T>(this IEnumerable<T> enumberable)
        {
            DataTable table = new DataTable("Generated");

            T first = enumberable.FirstOrDefault();
            if (first == null)
                return table;

            PropertyInfo[] properties = first.GetType().GetProperties();
            foreach (PropertyInfo pi in properties)
                table.Columns.Add(pi.Name, pi.PropertyType);

            foreach (T t in enumberable)
            {
                DataRow row = table.NewRow();
                foreach (PropertyInfo pi in properties)
                    row[pi.Name]=t.GetType().InvokeMember(pi.Name,BindingFlags.GetProperty,null, t, null);
                table.Rows.Add(row);
            }

            return table;
        }
    }

Old School Architecture, Bleeding Edge Technology

Sean Chase — Wed, 03 Jun 2009 10:15:00 GMT

For a while now I've been in "lurker" mode in all of the .NET community blogs. One of the interesting transitions that's been going on is the demo code for...well...just about everything including LINQ (whether it's the ADO.NET Entity Framework, LINQ-to-SQL, etc) or even ASP.NET MVC which includes using LINQ. Specifically, the removal of application "layers." Don't get me wrong, I think logically there's great patterns of cohesion to support the whole ability necessary for things like ASP.NET MVC's indirection. However sometimes, looking at this code (not to mention writing it) gives me a sense of going backwards into monolithic-type architecture. So I've been pondering from an architecture/design perspective how to implement all of this cool stuff but at the same time keep my mind at ease. So in playing around with Northwind (my favorite example database of all time :) )

Data Access Layer - Simply generate an ADO.NET Entity Framework Model (.edmx)
Business Interface - This layer contains our use-cases where the UI leverages its services via messages, data structures, whatever makes sense.
Common Library - Since I don't like the idea of ADO.NET EF objects as my DTOs, I'd rather roll my own POJO (C#) objects (or even use DataTable or derivative of it).

//Common Library Assembly DTO Class

namespace Northwind.Common {

public class CustomerEntity {

public string Address { get; set; }

public string City { get; set; }

public string CompanyName { get; set; }

public string ContactName { get; set; }

public string ContactTitle { get; set; }

public string Country { get; set; }

public string CustomerID { get; set; }

public string Fax { get; set; }

public string Phone { get; set; }

public string PostalCode { get; set; }

public string Region { get; set; }

}

//Business Interface (or Facade) Assembly

namespace Northwind.BusinessInterface {

static public class CustomerInterface {

static public List<Northwind.Common.CustomerEntity> GetAll() {

using (var nwEntities = new NorthwindAPI.NorthwindEntities()) {

var results = from custs in nwEntities.Customers

select new Northwind.Common.CustomerEntity() {

Address = custs.Address,

City = custs.City,

CompanyName = custs.CompanyName,

ContactName = custs.ContactName,

ContactTitle = custs.ContactTitle,

Country = custs.Country,

CustomerID = custs.CustomerID,

Fax = custs.Fax,

Phone = custs.Phone,

PostalCode = custs.PostalCode,

Region = custs.Region

};

Debug.WriteLine(((ObjectQuery)results).ToTraceString());

return results.ToList();

}

//Client Console App

namespace TestClient {

class Program {

static void Main(string[] args) {

List<Northwind.Common.CustomerEntity> custs = Northwind.BusinessInterface.CustomerInterface.GetAll();

custs.ForEach(c => Console.WriteLine(c.CustomerID));

}

This is nothing new as far as architecture ideas go - many designers are running into the same philosophical conflict, but what is interesting is the other questions it raises. When you've had tried-and-true methodologies for many years, it's difficult to simply jump ship and feel good about compiling LINQ queries into your code versus writing stored procs, or using ORM entities throughout your application versus creating more vanilla DTOs. For something intended as only a Web application, how much do I care about tier separation versus logic-layers? We're assuming the application will end up only being a Web application. If it turns into a system that then needs to become published into a service, do you then refactor at that point, or do you plan ahead by separating layers as I'm inclined to do? What is the maintenance impact? How can we assume that our database is already designed and fleshed-out enough to the point where it is OK to use an ORM against it? Does this mean we design our database first? Generally it makes more sense to come up with our use-cases first, which is more of the BusinessInterface layer (and UI to some extent) before we identify what our system entity/attribute and data storage look like. If we start with use-cases (or tests for our TDD enthusiasts), will the database ever change during the development cycle? If so, will having to refactor my model and the corresponding ORM-generated classes have a significant impact on my team during development?

It is interesting stuff to think about. I'll keep lurking and see what others come up with too. Drop me a line if you have any interesting opinions on the subject. :)

401 Error Using a Domain Account Identity in an App Pool

Sean Chase — Tue, 10 Mar 2009 19:44:00 GMT

Long title - but that says it all. Today I wasted a large chunk of time trying to figure out why I was getting a challenge dialog on my ASP.NET site. The site was configured to use an application pool that I made. The app pool was set to run as a domain account identity and the NTFS permissions on all of the folders looked fine...but I kept getting prompted to enter my user credentials. Buried in a sea of security event log entries were several kerberos errors. It turns out that on this server, kerberos was on by default.

The solution was to run a simple command line to force IIS to use NTLM:

adsutil.vbs set w3svc/3/NTAuthenticationProviders "NTLM"

http://support.microsoft.com/default.aspx?scid=kb;EN-US;871179

Hope this saves someone some time if you run into the same problem. :)

Fun w/ Visual Studio 2008

Sean Chase — Tue, 27 Nov 2007 14:48:00 GMT

I'm sure you've heard by now the Visual Studio 2008 is RTM. If you haven't already, play around a bit with object/collection initializers and LINQ. One interesting thing I've learned today is the “let” clause which allows you to define a subexpression in a LINQ query. Also you can generate XML fairly easily in your LINQ queries (or anything else you want to transform for that matter).

class Program

{

static void Main(string[] args)

{

List<Person> people = new List<Person>(){

new Person { Name = "Jack", Age = 22, FavoriteColors = new string[] { "Green", "Red" } },

new Person { Name = "Jill", Age = 33, FavoriteColors = new string[] { "Black", "Blue" } }

};

var xmlSource = new XElement("People",

from person in people

let colors = String.Format("{0},{1}", person.FavoriteColors[0], person.FavoriteColors[1])

select new XElement("Person",

new XElement("Name", person.Name),

new XElement("Age", person.Age),

new XElement("FavoriteColors", colors)

)

);

Console.WriteLine(xmlSource);

/* output:

<FavoriteColors>Green,Red</FavoriteColors>

</Person>

<FavoriteColors>Black,Blue</FavoriteColors>

</Person>

</People>

}

OCI-22053: overflow error

Sean Chase — Thu, 25 Oct 2007 12:31:00 GMT

Just FYI - if you are using the System.Net.OracleClient namespace with 10g it is possible you will run into this error. Now before you start getting too excited about being able to set the ReturnProviderSpecificTypes property on an adapter, you can run into this problem with output parameters on an ExecuteNonQuery so it's probably a safer bet to use the OleDb provider with 10g.

Flame on if I'm wrong.

Getting Results from an Oracle Stored Proc

Sean Chase — Thu, 25 Oct 2007 12:13:00 GMT

I'm not going to bash Oracle...it does a good job of bashing itself. For those of us used to being productive using SQL Server (I've heard it said that Oracle sells its products to managers, Microsoft sells to developers), here's some interesting info on calling an Oracle proc.

OK, so you are used to SQL Server and you want to create a proc that ultimately (at some point) returns some results in the form of...well, a bunch of records!

create procedure foo as 
    ... 
    select a, b, c 
    from bar 
    where blah = 1

You jump into Oracle and try the same thing you'll get this nice error message:

PLS-00428: an INTO clause is expected in this SELECT statement. Also we are better than you M$ noobcakes and Bill Gates is a jerk. Windoze is teh suxxorz. ZOMG barrens chat.

The way around this is to use a ref cursor. It's silly and it's juvenile and it's like going back in time, but it works. http://support.microsoft.com/?id=309361

But before you get to pissed off about having to create an Oracle package and define a cursor type, with 9i and 10g you can use sys_refcursor and save the trouble.

CREATE OR REPLACE PROCEDURE Foo (io_cursor IN OUT sys_refcursor) AS

v_cursor sys_refcursor; ... begin open v_cursor for select a, b, c where blah = 1; io_cursor := v_cursor; end;

That's it, you're done! Yay!

Oh wait no you're not!!! Because if you want to test it you're probably thinking "Hey I can just type EXEC Foo in Query Analyzer." Sorry there's more pig $#!+ you have to go through than that. Isn't DBArtisan a great tool?!?! *vomits*

declare v_cursor sys_refcursor; a number; b number; c number begin Foo(io_cursor => v_cursor); loop fetch v_cursor into a, b, c; exit when v_cursor%NOTFOUND; dbms_output.put_line(a || b || c); end loop; close v_cursor; end;

Hopefully you're not stuck in Oracle hell (god I miss SQL Server 2005), but if you've had your nose jammed in it like a bad dog and you're not getting help from the elitist oracle buff community, and you run across this problem...hope it helps. :-)

Using the AJAX Toolkit AutoCompleteExtender with Visual Studio SP1

Sean Chase — Fri, 23 Feb 2007 14:20:00 GMT

Just a heads up if you use the AutoCompleteExtender Ajax control. I'm not sure if this is because I'm using Visual Studio SP1 (using Web Application Projects), but I could not get this control to work with an ASMX, but rather a WebMethod in the page's code-behind. Also, make sure your ServiceMethod attribute declaration is static!

<%@ Register Assembly="AjaxControlToolkit" Namespace="AjaxControlToolkit" TagPrefix="ajaxToolkit" %>

<form id="form1" runat="server">
<asp:ScriptManager ID="ScriptManager1" runat="server" />
<asp:TextBox runat="server" ID="myTextBox" Width="300" autocomplete="off" />
<ajaxToolkit:AutoCompleteExtender runat="server"
                                  ID="autoComplete1"
                                  TargetControlID="myTextBox"
                                  ServiceMethod="GetCompletionList"
                                  MinimumPrefixLength="1"
                                  CompletionInterval="1000"
                                  EnableCaching="true"
                                  CompletionSetCount="12" />
</form>

public partial class _Default : System.Web.UI.Page {
    [WebMethod]
    public static string[] GetCompletionList(string prefixText, int count) {
        string sql = String.Format("select companyName from customers where companyname like @companyname + '%'");

        List<string> companyList = new List<string>();
        using (SqlConnection connection = new SqlConnection("Integrated Security=SSPI;Initial Catalog=Northwind;Data Source=."))
        using (SqlCommand command = new SqlCommand(sql, connection)) {
            connection.Open();
            command.Parameters.AddWithValue("@companyname", prefixText);
            using (SqlDataReader reader = command.ExecuteReader()) {
                while (reader.Read()) {
                    companyList.Add(reader.GetString(0));
                }
            }
        }

        return companyList.ToArray();
    }
}

Too Many Breakpoints = Bad Visual Studio Performance

Sean Chase — Thu, 15 Feb 2007 15:18:00 GMT

Just wanted to share this in case anyone has issues with very slow build and debug time in Visual Studio for ASP.NET applications. This solution might sound a lot like holding tin foil over your head, but....try removing all of your breakpoints. "Clear All Breakpoints" can actually make good things happen.