public static void Insert <T>(this IDbConnection connection,
T entityToInsert,
IDbTransaction transaction = null,
int?commandTimeout = null) where T : class
{
var type = typeof(T);
var metadata = MetadataFor(type);
string sql;
if (!s_insertQueries.TryGetValue(type.TypeHandle, out sql))
{
s_insertQueries[type.TypeHandle] = sql = TSQLGenerator.BuildInsert(metadata, withoutKeys: false);
}
Log(sql, entityToInsert);
connection.Execute(
sql,
entityToInsert,
transaction: transaction,
commandTimeout: commandTimeout);
}
public void BuildCreate_WithSchemaAndMultipleKeys()
{
var tableMD = GetTestMetadata();
tableMD.Properties.Add(new PropertyMetadata()
{
IsPK = true,
ColumnName = "a2",
PropertyName = "aa22"
});
tableMD.Schema = "testschema";
var sql = TSQLGenerator.BuildCreate(tableMD, true);
Assert.AreEqual(@"IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[testschema].[testtable]') AND type in (N'U'))
BEGIN
CREATE TABLE [testschema].[testtable](
[a] NOT NULL,
[a2] NOT NULL,
[b] NOT NULL,
[c] NOT NULL,
CONSTRAINT [PK_testtable] PRIMARY KEY CLUSTERED ( [a] ASC,[a2] ASC )
);
END
", sql);
}
public static bool Exists <T>(this IDbConnection connection, T entity, IDbTransaction transaction = null, int?commandTimeout = null) where T : class
{
var type = typeof(T);
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.");
}
string sql;
if (!s_existsQueries.TryGetValue(type.TypeHandle, out sql))
{
s_existsQueries[type.TypeHandle] = sql = TSQLGenerator.BuildDelete(metadata);
}
Log(sql, entity);
var noOfRowsMatching = connection.Execute(
sql,
entity,
transaction: transaction,
commandTimeout: commandTimeout);
return(noOfRowsMatching > 0);
}
/// <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);
}
}
public void BuildColumnList()
{
var tableMD = GetTestMetadata();
var sql = TSQLGenerator.BuildColumnList(tableMD);
Assert.AreEqual("[a],[b],[c]", sql);
}
/// <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);
}));
}
public void BuildInsert_WithSchema()
{
var tableMD = GetTestMetadata();
tableMD.Schema = "testschema";
var sql = TSQLGenerator.BuildInsert(tableMD, withoutKeys: false);
Assert.AreEqual("INSERT INTO [testschema].[testtable] ([a],[b],[c]) VALUES (@aa,@bb,@cc)", sql);
}
public void BuildDelete_WithSchema()
{
var tableMD = GetTestMetadata();
tableMD.Schema = "testschema";
var sql = TSQLGenerator.BuildDelete(tableMD);
Assert.AreEqual("DELETE FROM [testschema].[testtable] WHERE [a]=@aa", sql);
}
/// <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 void BuildParameterList_WithoutKeys()
{
var tableMD = GetTestMetadata();
tableMD.Schema = "testschema";
var sql = TSQLGenerator.BuildParameterList(tableMD, withoutKeys: true);
Assert.AreEqual("@bb,@cc", sql);
}
public void BuildUpdate_WithSchema()
{
var tableMD = GetTestMetadata();
tableMD.Schema = "testschema";
var sql = TSQLGenerator.BuildUpdate(tableMD);
Assert.AreEqual("UPDATE [testschema].[testtable] SET [b]=@bb,[c]=@cc WHERE [a]=@aa", sql);
}
public void BuildDelete_NoSchema()
{
var tableMD = GetTestMetadata();
Assert.IsNull(tableMD.Schema);
var sql = TSQLGenerator.BuildDelete(tableMD);
Assert.AreEqual("DELETE FROM [testtable] WHERE [a]=@aa", sql);
}
public static void CreateTable <T>(this IDbConnection connection, bool onlyIfNotExists, IDbTransaction transaction = null, int?commandTimeout = null) where T : class
{
var type = typeof(T);
var values = new Dictionary <string, object>();
var metadata = MetadataFor(type);
var sql = TSQLGenerator.BuildCreate(metadata, onlyIfNotExists: onlyIfNotExists);
connection.Execute(sql, transaction: transaction, commandTimeout: commandTimeout);
}
public static bool ExistsTable <T>(this IDbConnection connection, IDbTransaction transaction = null, int?commandTimeout = null) where T : class
{
var type = typeof(T);
var values = new Dictionary <string, object>();
var metadata = MetadataFor(type);
var sql = TSQLGenerator.BuildExistsTable(metadata);
return(connection.Query(sql, transaction: transaction, commandTimeout: commandTimeout).Any());
}
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 BuildWhereClause()
{
var tableMD = GetTestMetadata();
var values = new Dictionary <string, object>();
values.Add("aa", 1);
values.Add("cc", "test");
var parameters = new Dictionary <string, object>();
var sql = TSQLGenerator.BuildWhereClause(tableMD, values, ref parameters);
Assert.AreEqual("[a]=@aa AND [c]=@cc", sql);
Assert.AreEqual(2, parameters.Count);
}
public void BuildCreate_NoSchema()
{
var tableMD = GetTestMetadata();
Assert.IsNull(tableMD.Schema);
var sql = TSQLGenerator.BuildCreate(tableMD);
Assert.AreEqual(@"CREATE TABLE [dbo].[testtable](
[a] NOT NULL,
[b] NOT NULL,
[c] NOT NULL,
CONSTRAINT [PK_testtable] PRIMARY KEY CLUSTERED ( [a] ASC )
);
", sql);
}
public static TKey Insert <T, TKey>(this IDbConnection connection,
T entityToInsert,
IDbTransaction transaction = null,
int?commandTimeout = null,
bool letDbGenerateKey = false) where T : class
{
var type = typeof(T);
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.");
}
string sql;
if (!s_insertQueries.TryGetValue(type.TypeHandle, out sql))
{
s_insertQueries[type.TypeHandle] = sql = TSQLGenerator.BuildInsert(metadata, withoutKeys: letDbGenerateKey);
}
Log(sql, entityToInsert);
connection.Execute(
sql,
entityToInsert,
transaction: transaction,
commandTimeout: commandTimeout);
if (letDbGenerateKey)
{
//NOTE: would prefer to use IDENT_CURRENT('tablename') or IDENT_SCOPE but these are not available on SQLCE
var r = connection.Query("select @@IDENTITY id", transaction: transaction,
commandTimeout: commandTimeout);
return((TKey)r.First().id);
}
else
{
return((TKey)keys.First().PropertyInfo.GetValue(entityToInsert, null));
}
}
public void FindUniqueNameInDictionary()
{
var dict = new Dictionary <string, object>();
var subject = "test";
var s1 = TSQLGenerator.FindUniqueNameInDictionary(subject, dict);
Assert.IsTrue(s1.Equals("test"));
dict.Add(s1, null);
var s2 = TSQLGenerator.FindUniqueNameInDictionary(subject, dict);
Assert.IsTrue(s2.Equals("test2"));
dict.Add(s2, null);
var s3 = TSQLGenerator.FindUniqueNameInDictionary(subject, dict);
Assert.IsTrue(s3.Equals("test3"));
}
public void BuildCreate_OneIntKey()
{
var md = new TableMetadata();
MetadataHelper.MetadataForClass(typeof(TableWithOneIntKey), ref md);
var sql = TSQLGenerator.BuildCreate(md, true);
Assert.AreEqual(@"IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TableWithOneIntKey]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[TableWithOneIntKey](
[Key1] [int] IDENTITY(1,1) NOT NULL,
[Other] [NVARCHAR](50) NULL,
CONSTRAINT [PK_TableWithOneIntKey] PRIMARY KEY CLUSTERED ( [Key1] ASC )
);
END
", sql);
}
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 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 static bool Delete <T>(this IDbConnection connection, T entityToDelete, IDbTransaction transaction = null, int?commandTimeout = null) where T : class
{
var type = typeof(T);
var metadata = MetadataFor(type);
string sql;
if (!s_deleteQueries.TryGetValue(type.TypeHandle, out sql))
{
s_deleteQueries[type.TypeHandle] = sql = TSQLGenerator.BuildDelete(metadata);
}
Log(sql, entityToDelete);
var deleted = connection.Execute(sql, entityToDelete, transaction: transaction, commandTimeout: commandTimeout);
return(deleted > 0);
}
public void BuildCreate_WithSchema()
{
var tableMD = GetTestMetadata();
tableMD.Schema = "testschema";
var sql = TSQLGenerator.BuildCreate(tableMD, true);
Assert.AreEqual(@"IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[testschema].[testtable]') AND type in (N'U'))
BEGIN
CREATE TABLE [testschema].[testtable](
[a] NOT NULL,
[b] NOT NULL,
[c] NOT NULL,
CONSTRAINT [PK_testtable] PRIMARY KEY CLUSTERED ( [a] ASC )
);
END
", sql);
}
public void BuildCreate_WithSchemaAndNoKeys()
{
var tableMD = GetTestMetadata();
tableMD.Properties.Where(x => x.IsPK).ToList().ForEach(x => x.IsPK = false);
tableMD.Schema = "testschema";
var sql = TSQLGenerator.BuildCreate(tableMD, true);
Assert.AreEqual(@"IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[testschema].[testtable]') AND type in (N'U'))
BEGIN
CREATE TABLE [testschema].[testtable](
[a] NOT NULL,
[b] NOT NULL,
[c] NOT NULL,
);
END
", sql);
}
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);
}