Create a gist now

Instantly share code, notes, and snippets.

What would you like to do?
Simple.Data extensions
using System.Collections.Generic;
using System.Data;
using System.Linq;
using Simple.Data.Ado;
namespace Simple.Data.Sql
{
public static class DataReaderExtensions
{
public static IEnumerable<IEnumerable<dynamic>> ToResultSets(this IDataReader reader)
{
return reader.ToMultipleDictionaries().ToResultSets();
}
public static IEnumerable<dynamic> ToRows(this IDataReader reader)
{
return reader.ToDictionaries().ToRows();
}
public static dynamic ToRow(this IDataRecord record)
{
return record.ToDictionary().ToRow();
}
private static IEnumerable<IEnumerable<SimpleRecord>> ToResultSets(this IEnumerable<IEnumerable<IDictionary<string, object>>> data)
{
return data.Select(ToRows).ToArray().AsEnumerable();
}
private static IEnumerable<SimpleRecord> ToRows(this IEnumerable<IDictionary<string, object>> rsData)
{
return rsData.Select(ToRow).ToArray().AsEnumerable();
}
private static SimpleRecord ToRow(this IDictionary<string, object> rowData)
{
return new SimpleRecord(rowData);
}
}
}
using System.Collections.Generic;
using System.Data;
using System.Linq;
namespace Simple.Data.Sql
{
public class DbCommandBuilder
{
public IDbCommand BuildCommand(IDbConnection connection, string sql, IEnumerable<KeyValuePair<string, object>> parameters)
{
return BuildCommand(connection, sql,
parameters.ToDictionary(kv => kv.Key, kv => kv.Value));
}
public IDbCommand BuildCommand(IDbConnection connection, string sql, IDictionary<string, object> parameters)
{
var cmd = connection.CreateCommand();
cmd.Connection = connection;
cmd.CommandText = sql;
parameters.ToList()
.ForEach(cmd.AddParameter);
return cmd;
}
}
}
using System;
using System.Collections.Generic;
using System.Data;
namespace Simple.Data.Sql
{
public static class DbCommandExtensions
{
public static IEnumerable<IEnumerable<dynamic>> ToResultSets(this IDbCommand command)
{
using (var rdr = command.ExecuteReader())
{
return rdr.ToResultSets();
}
}
public static IEnumerable<dynamic> ToRows(this IDbCommand command)
{
using (var rdr = command.ExecuteReader())
{
return rdr.ToRows();
}
}
public static dynamic ToRow(this IDbCommand command)
{
using (var rdr = command.ExecuteReader())
{
return rdr.ToRow();
}
}
public static void AddParameter(this IDbCommand command, KeyValuePair<string, object> parameterData)
{
var parameter = command.CreateParameter();
parameter.ParameterName = parameterData.Key;
parameter.Value = parameterData.Value ?? DBNull.Value;
command.Parameters.Add(parameter);
}
}
}
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
namespace Simple.Data.Sql
{
public static class DbConnectionExtensions
{
private const string SqlEmptyOrWhitespace = "Sql statement can't be empty or whitespace.";
public static IEnumerable<IEnumerable<dynamic>> ToResultSets(this IDbConnection connection, string sql,
IDictionary<string, object> parameters)
{
if (connection == null) throw new ArgumentNullException("connection");
if (sql == null) throw new ArgumentNullException("sql");
if (string.IsNullOrWhiteSpace(sql))
throw new ArgumentException(SqlEmptyOrWhitespace, "sql");
parameters = parameters ?? new Dictionary<string, object>();
var cmd = new DbCommandBuilder().BuildCommand(connection, sql, parameters);
return connection.WithOpenConnection(() => cmd.ToResultSets());
}
public static IEnumerable<IEnumerable<dynamic>> ToResultSets(this IDbConnection connection, string sql,
params KeyValuePair<string, object>[] parameters)
{
parameters = parameters ?? new KeyValuePair<string, object>[0];
return connection.ToResultSets(sql, parameters.ToDictionary(kv => kv.Key, kv => kv.Value));
}
public static IEnumerable<dynamic> ToRows(this IDbConnection connection, string sql,
IDictionary<string, object> parameters)
{
if (connection == null) throw new ArgumentNullException("connection");
if (sql == null) throw new ArgumentNullException("sql");
if (string.IsNullOrWhiteSpace(sql))
throw new ArgumentException(SqlEmptyOrWhitespace, "sql");
parameters = parameters ?? new Dictionary<string, object>();
var cmd = new DbCommandBuilder().BuildCommand(connection, sql, parameters);
return connection.WithOpenConnection(() => cmd.ToRows());
}
public static IEnumerable<dynamic> ToRows(this IDbConnection connection, string sql,
params KeyValuePair<string, object>[] parameters)
{
parameters = parameters ?? new KeyValuePair<string, object>[0];
return connection.ToRows(sql, parameters.ToDictionary(kv => kv.Key, kv => kv.Value));
}
public static dynamic ToRow(this IDbConnection connection, string sql, IDictionary<string, object> parameters)
{
if (connection == null) throw new ArgumentNullException("connection");
if (sql == null) throw new ArgumentNullException("sql");
if (string.IsNullOrWhiteSpace(sql))
throw new ArgumentException(SqlEmptyOrWhitespace, "sql");
parameters = parameters ?? new Dictionary<string, object>();
var cmd = new DbCommandBuilder().BuildCommand(connection, sql, parameters);
return connection.WithOpenConnection(() => cmd.ToRow());
}
public static dynamic ToRow(this IDbConnection connection, string sql,
params KeyValuePair<string, object>[] parameters)
{
parameters = parameters ?? new KeyValuePair<string, object>[0];
return connection.ToRow(sql, parameters.ToDictionary(kv => kv.Key, kv => kv.Value));
}
private static T WithOpenConnection<T>(this IDbConnection connection, Func<T> func)
{
if (connection.State != ConnectionState.Closed)
return func();
try
{
connection.Open();
return func();
}
finally
{
connection.Close();
}
}
}
}
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
namespace Simple.Data.Sql
{
class Program
{
static void Main(string[] args)
{
const string connStr = @"Server=.\SQLExpress;Database=Chinook;Integrated Security=SSPI";
const string sql =@"
-- Data Query
SELECT TOP 5 Album.AlbumId, Artist.Name, Album.Title
FROM Artist
INNER JOIN Album
ON Album.ArtistId = Artist.ArtistId
WHERE Album.Title LIKE @p0
ORDER BY Album.Title, Artist.Name;
-- Count Query
SELECT COUNT(*) AS [Count]
FROM Artist
INNER JOIN Album
ON Album.ArtistId = Artist.ArtistId
WHERE Album.Title LIKE @p0";
var data = new SqlConnection(connStr)
.ToResultSets(sql, new KeyValuePair<string, object>("p0", "a%"))
.ToArray();
var rows = data.ElementAt(0).ToArray();
var count = (long)data.ElementAt(1).Single().Count;
Console.WriteLine("Displaying rows {0} through {1} of {2}", rows.Any() ? 1 : 0, rows.Length, count);
Console.WriteLine();
foreach (var row in rows)
{
Console.WriteLine("{0} by {1}", row.Title, row.Name);
}
Console.WriteLine();
Console.WriteLine("Press any key to quit");
Console.ReadKey();
}
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment