public void Can_select_NotesDto_with_pretty_sql()
{
using (var db = OpenDbConnection())
{
db.DropAndCreateTable <Note>();
var id = db.Insert(new Note
{
SchemaUri = "tcm:0-0-0",
NoteText = "Hello world 5",
LastUpdated = new DateTime(2013, 1, 5),
UpdatedBy = "RC"
}, selectIdentity: true);
var sql = @"
SELECT
Id, {0}, {1}
FROM {2}
WHERE {0}={3}
".Fmt("SchemaUri".SqlColumn(DialectProvider), "NoteText".SqlColumn(DialectProvider), "Note".SqlTable(DialectProvider),
DialectProvider.GetParam("schemaUri"));
var notes = db.Select <NoteDto>(sql, new { schemaUri = "tcm:0-0-0" });
Assert.That(notes[0].Id, Is.EqualTo(id));
Assert.That(notes[0].NoteText, Is.EqualTo("Hello world 5"));
}
}
public void Log_dialect_behavior()
{
Dialect.ToString().Print();
using (var db = OpenDbConnection())
{
db.DropAndCreateTable <DateTimeObject>();
var dateStyles = new[] { DateTimeKind.Local, DateTimeKind.Utc, DateTimeKind.Unspecified };
foreach (var dateStyle in dateStyles)
{
db.DeleteAll <DateTimeObject>();
var dateTime = new DateTime(2012, 1, 1, 1, 1, 1, dateStyle);
"#1 IN: {0} ({1}), UTC: {2}, Local: {3}".Print(
dateTime.Kind,
dateTime,
dateTime.ToUniversalTime(),
dateTime.ToLocalTime());
using (var cmd = db.OpenCommand())
{
cmd.CommandText = "INSERT INTO {0} VALUES({1}, {2})"
.Fmt(typeof(DateTimeObject).Name.SqlTable(DialectProvider),
DialectProvider.GetParam("p1"),
DialectProvider.GetParam("p2"));
cmd.Parameters.Add(cmd.CreateParam("p1", dateTime));
cmd.Parameters.Add(cmd.CreateParam("p2", dateTime));
cmd.ExecuteNonQuery();
}
using (var cmd = db.OpenCommand())
{
cmd.CommandText = "SELECT * FROM {0}".Fmt(typeof(DateTimeObject).Name.SqlTable(DialectProvider));
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
var dbDateTime = reader.GetDateTime(0);
"#1 IN: {0} ({1}), OUT: {2} ({3})".Print(
dateTime.Kind,
dateTime,
dbDateTime.Kind,
dbDateTime);
}
}
}
}
}
}
public void Can_add_DbParam_to_SqlExpression()
{
using (var db = OpenDbConnection())
{
SqlExpressionTests.InitLetters(db);
var q = db.From <LetterFrequency>()
.UnsafeWhere("Letter = {0}".Fmt(DialectProvider.GetParam("p1")));
q.Params.Add(q.CreateParam("p1", "B"));
var results = db.Select(q);
results.PrintDump();
Assert.That(results.Count, Is.EqualTo(2));
}
}
/// <summary>
/// Gets an entity with given primary key.
/// </summary>
/// <param name="connectionString"></param>
/// <param name="id">Primary key of the entity to get</param>
/// <returns>Entity</returns>
public virtual TEntity Single(string connectionString, TPrimaryKey id)
{
string whereExpression = string.Format("{1}={2}", TableName, PrimaryFieldName, DialectProvider.GetParam("Id"));
return(Single(connectionString, whereExpression, new { Id = id }));
}
/// <summary>
///Delete data returns number .
/// </summary>
/// <param name="connectionString"></param>
/// <param name="id"></param>
/// <returns></returns>
public virtual int Delete(string connectionString, TPrimaryKey id)
{
if (id == null)
{
return(-1);
}
if (string.IsNullOrEmpty(PrimaryFieldName))
{
return(-1);
}
string whereExpression = string.Format("WHERE {0}={1}", PrimaryFieldName, DialectProvider.GetParam("Id"));
return(Delete(connectionString, whereExpression, new { Id = id }));
}