SSDB.cs

using System;
using System.Collections.Generic;
using System.Linq;

using System.Data;
using System.Data.SqlClient;

using PropertyInfo = System.Reflection.PropertyInfo;

/*
  Issues:
    - Select into item would be happy if it had a special type it returned with type safe values for error and empty.
    - No way to do transactions accross requests.
*/

 
namespace SuperSimple.DB
{
  // Simple DB support
  class DB
  {
    private static void Addparms(SqlCommand cmd, object parms)
    {
      // parameter objects take the form new { propname : "value", ... } 
      foreach (PropertyInfo prop in parms.GetType().GetProperties())
      {
        cmd.Parameters.AddWithValue("@" + prop.Name, prop.GetValue(parms, null));
      }
    }
 
    public static int ExecuteNonQuery(string SQL, string connectionString, CommandType cType = CommandType.Text, object parms = null)
    {
      using (SqlConnection conn = new SqlConnection(connectionString))
      {
        using (SqlCommand cmd = conn.CreateCommand())
        {
          cmd.CommandType = cType;
          cmd.CommandText = SQL;

          if (parms != null)
            Addparms(cmd, parms);

          conn.Open();

          return cmd.ExecuteNonQuery();
        }
      }
    }

    public static IEnumerable<dynamic> SelectIntoList(string SQLselect, string connectionString, CommandType cType = CommandType.Text, object parms = null)
    {
      using (SqlConnection conn = new SqlConnection(connectionString))
      {
        using (SqlCommand cmd = conn.CreateCommand())
        {
          cmd.CommandType = cType;
          cmd.CommandText = SQLselect;

          if (parms != null)
            Addparms(cmd, parms);

          conn.Open();

          using (SqlDataReader reader = cmd.ExecuteReader())
          {

            if (reader.Read())  // read the first one to get the columns collection
            {
              var cols = reader.GetSchemaTable()
                           .Rows
                           .OfType<DataRow>()
                           .Select(r => r["ColumnName"]);

              do
              {
                dynamic t = new System.Dynamic.ExpandoObject();

                foreach (string col in cols)
                {
                  ((IDictionary<System.String, System.Object>)t)[col] = reader[col];
                }

                yield return t;
              } while (reader.Read());
            }
          }

          conn.Close();
        }
      }
    }
     
    public static dynamic SelectIntoItem(string SQLselect, string connectionString, CommandType cType = CommandType.Text, object parms = null)
    {
       
      using (SqlConnection conn = new SqlConnection(connectionString))
      {
        using (SqlCommand cmd = conn.CreateCommand())
        {
          dynamic result = new System.Dynamic.ExpandoObject();

          cmd.CommandType = cType;
          cmd.CommandText = SQLselect;

          if (parms != null)
            Addparms(cmd, parms);

          conn.Open();


          using (SqlDataReader reader = cmd.ExecuteReader())
          {
            if (reader.Read())  // read the first one to get the columns collection
            {
              var cols = reader.GetSchemaTable()
                           .Rows
                           .OfType<DataRow>()
                           .Select(r => r["ColumnName"]);

              foreach (string col in cols)
              {
                ((IDictionary<System.String, System.Object>)result)[col] = reader[col];
              }
              result.Empty = false;

              if (reader.Read())
              {
                // error, what to do?
                result.Error = true;
                result.ErrorMessage = "More than one row in result set.";
              }
              else
              {
                result.Error = false;
              }

            }
            else
            {
              result.Empty = true;
              result.Error = false;
            }
          }

          conn.Close();

          return result;
        }
      }
    }

  }
}