/// <summary>
/// Fills a datatable using a WhereBuilder object.
/// </summary>
/// <param name="dt">The datatable to fill.</param>
/// <param name="where">The WhereBuilder for querying.</param>
public virtual void Fill(DataTable dt, Where <T> where)
{
using (var c = OpenConnection())
{
var param = new Dictionary <string, object>();
var whereSql = where == null ? string.Empty : where.Build(param).ToString();
var selectClause = TSQLGenerator.BuildSelect(m_metadata) + " " + whereSql;
var cmd = c.CreateCommand();
cmd.CommandText = selectClause;
var sb = new StringBuilder();
foreach (var p in param)
{
sb.AppendFormat(" ^-- Parameter: {0} = '{1}'\r\n", p.Key, p.Value.ToString());
var dbparam = cmd.CreateParameter();
dbparam.ParameterName = p.Key;
dbparam.Value = p.Value;
cmd.Parameters.Add(dbparam);
}
s_log.Debug(x => x("Filling datatable for type {0} with '{1}'\r\n{2}", typeof(T).Name, selectClause, sb.ToString().TrimEnd()));
var sda = CreateAdapter(cmd);
sda.Fill(dt);
}
}
/// <summary>
/// Performs a query with a set of AND filters.
/// </summary>
/// <param name="where">The values to filter for. Each entry will be concatenated with AND.</param>
/// <returns></returns>
public IEnumerable <T> GetByWhere(Dictionary <string, object> @where)
{
return(m_connectionCtxFactory.InDatabase <IEnumerable <T> >((c, t) =>
{
var param = new Dictionary <string, object>();
var sql = TSQLGenerator.BuildSelect(m_metadata, @where, ref param);
return c.QueryFor <T>(sql, new DynamicParameters(param), transaction: t);
}));
}
/// <summary>
/// Performs a query with a set of AND filters.
/// </summary>
/// <param name="where">The values to filter for. Each entry will be concatenated with AND.</param>
/// <returns></returns>
public IEnumerable <T> GetByWhere(Dictionary <string, object> @where)
{
using (var c = OpenConnection())
{
var param = new Dictionary <string, object>();
var sql = TSQLGenerator.BuildSelect(m_metadata, @where, ref param);
return(c.QueryFor <T>(sql, new DynamicParameters(param)));
}
}
public static IEnumerable <T> GetAll <T>(this IDbConnection connection, IDbTransaction transaction = null, int?commandTimeout = null) where T : class
{
var type = typeof(T);
string sql;
if (!s_selectAllQueries.TryGetValue(type.TypeHandle, out sql))
{
var metadata = MetadataFor(type);
s_selectAllQueries[type.TypeHandle] = sql = TSQLGenerator.BuildSelect(metadata);
}
Log(sql, null);
return(connection.Query <T>(sql, transaction: transaction, commandTimeout: commandTimeout));
}
public void BuildSelectList_WithSchema()
{
var tableMD = GetTestMetadata();
tableMD.Schema = "testschema";
var values = new Dictionary <string, object>();
values.Add("aa", new int[] { 1, 2, 3 });
values.Add("cc", "test");
var parameters = new Dictionary <string, object>();
var sql = TSQLGenerator.BuildSelect(tableMD, values, ref parameters);
Assert.AreEqual("SELECT [a] AS 'aa',[b] AS 'bb',[c] AS 'cc' FROM [testschema].[testtable] WHERE [a] IN @aa AND [c]=@cc", sql);
Assert.AreEqual(2, parameters.Count);
}
public void BuildSelect_NoSchema()
{
var tableMD = GetTestMetadata();
Assert.IsNull(tableMD.Schema);
var values = new Dictionary <string, object>();
values.Add("aa", 1);
values.Add("cc", "test");
var parameters = new Dictionary <string, object>();
var sql = TSQLGenerator.BuildSelect(tableMD, values, ref parameters);
Assert.AreEqual("SELECT [a] AS 'aa',[b] AS 'bb',[c] AS 'cc' FROM [testtable] WHERE [a]=@aa AND [c]=@cc", sql);
Assert.AreEqual(2, parameters.Count);
}
public static T Get <T>(this IDbConnection connection, dynamic id, IDbTransaction transaction = null, int?commandTimeout = null) where T : class
{
var type = typeof(T);
var parameters = new Dictionary <string, object>();
var values = new Dictionary <string, object>();
var metadata = MetadataFor(type);
var keys = metadata.Properties.Where(x => x.IsPK);
if (!keys.Any())
{
throw new Exception("This only support entites with a single key property at the moment.");
}
if (keys.Count() > 1)
{
throw new Exception("This only support entites with a single key property at the moment.");
}
values.Add(keys.First().PropertyName, (object)id);
string sql;
if (!s_selectQueries.TryGetValue(type.TypeHandle, out sql))
{
s_selectQueries[type.TypeHandle] = sql = TSQLGenerator.BuildSelect(metadata, values, ref parameters);
}
else
{
TSQLGenerator.BuildParameters(metadata, values, ref parameters);
}
var dynParms = new DynamicParameters(parameters);
T obj = null;
Log(sql, dynParms);
obj = connection.Query <T>(sql, dynParms, transaction: transaction, commandTimeout: commandTimeout).FirstOrDefault();
return(obj);
}