ORM or simple filling of the class with data from the stored procedure

Hello to all habrayusers, I decided to write my first article, which continues a series of essays about interaction with the database. It so happened that I turned up a small web project for implementation. ASP.NET MVC + ExtJS was chosen as the platform, but there was no solution for ORM at once. The problem was that it would not be desirable to involve a large industrial ORM solution like NHibernate or the Entity Framework, since the project will have two to three dozen stored procedures. At the same time, using the Microsoft DAAB wrapper does not work either. because in the MVC framework, the models are essentially copies of the database tables (well, just to simplify, we assume that) as a result, Readers, DataSets, and DataTables can do little to help us. Perhaps a good solution would be to use LinqToSql, but I really dislike it when requests are written not in SQL but in C #, I firmly believe that communication between the application and the database should occur only through stored procedures and functions. In other words, we need a class mapping on the result set of the stored procedure and the simplicity of its use in order to simply call the helper method and, using reflection, it returned a collection of instances filled with data from the procedure. I think the essence of the problem I have set out quite clearly, so we proceed to the implementation.

To begin with, let's declare our class to fill with data, of course, that it will completely copy the fields that the stored procedure returns:

public class Book
{
public int ID { get ; set ; }
public string Title { get ; set ; }
public string Author { get ; set ; }
public DateTime PublicationDate { get ; set ; }
}

* This source code was highlighted with Source Code Highlighter .

And here is the helper method for mapping:

public static List <T> GetSpResultset<T>( string spName)
{
List <T> list = new List <T>();
SqlConnection connection = new SqlConnection( " " );

connection.Open();

using ( SqlCommand command = new SqlCommand (spName, connection))
{
command.CommandType = CommandType.StoredProcedure;
using (IDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection))
{
PropertyInfo[] fields = typeof (T).GetProperties();
while (reader.Read())
{
T record = Activator.CreateInstance<T>();
foreach (PropertyInfo pi in fields)
{
if (pi.PropertyType.Name == typeof ( Int32 ).Name)
{
if (pi.CanWrite)
{
int value = reader.GetInt32(reader.GetOrdinal(pi.Name));
pi.SetValue(record, value , null );
}
}
else if (pi.PropertyType.Name == typeof (Int16).Name)
{
if (pi.CanWrite)
{
short value = Convert .ToInt16(reader.GetValue(reader.GetOrdinal(pi.Name)));
pi.SetValue(record, value , null );
}
}
else if (pi.PropertyType.Name == typeof ( String ).Name)
{
if (pi.CanWrite)
{
string value = reader.GetString(reader.GetOrdinal(pi.Name));
pi.SetValue(record, value , null );
}
}
else if (pi.PropertyType.Name == typeof ( DateTime ).Name)
{
if (pi.CanWrite)
{
DateTime value = reader.GetDateTime(reader.GetOrdinal(pi.Name));
pi.SetValue(record, value , null );
}
}
}
list.Add(record);
}
}
}
return list;
}

* This source code was highlighted with Source Code Highlighter .

The method is implemented as a generic method that returns the System.Collections.Generic.List collection filled with instances of our class, for ease of implementation, it omits passing parameters to the procedure and works with only 4 data types Int32, Int16, String and DateTime, but you be able to expand and supplement it functionally.

And of course, an example of use:

List <Book> books = GetSpResultset<Book>( "sp_GetBooks" );

* This source code was highlighted with Source Code Highlighter .

Well, that's all I wanted to tell in my article, I look forward to comments and constructive criticism.