本文整理汇总了C#中SqlBuilder.AddTemplate方法的典型用法代码示例。如果您正苦于以下问题：C# SqlBuilder.AddTemplate方法的具体用法？C# SqlBuilder.AddTemplate怎么用？C# SqlBuilder.AddTemplate使用的例子？那么恭喜您, 这里精选的方法代码示例或许可以为您提供帮助。您也可以进一步了解该方法所在类SqlBuilder的用法示例。
在下文中一共展示了SqlBuilder.AddTemplate方法的15个代码示例，这些例子默认根据受欢迎程度排序。您可以为喜欢或者感觉有用的代码点赞，您的评价将有助于系统推荐出更棒的C#代码示例。
示例1: BuilderSelectClause
public void BuilderSelectClause()
{
using (var dbConn = dbFactory.OpenDbConnection())
using (var dbCmd = dbConn.CreateCommand())
{
var rand = new Random(8675309);
var data = new List<User>();
for (var i = 0; i < 100; i++)
{
var nU = new User { Age = rand.Next(70), Id = i, Name = Guid.NewGuid().ToString() };
data.Add(nU);
dbCmd.Insert(nU);
nU.Id = (int) dbCmd.GetLastInsertId();
}
var builder = new SqlBuilder();
var justId = builder.AddTemplate("SELECT /**select**/ FROM Users");
var all = builder.AddTemplate("SELECT /**select**/, Name, Age FROM Users");
builder.Select("Id");
var ids = dbCmd.Query<int>(justId.RawSql, justId.Parameters);
var users = dbCmd.Query<User>(all.RawSql, all.Parameters);
foreach (var u in data)
{
Assert.That(ids.Any(i => u.Id == i), "Missing ids in select");
Assert.That(users.Any(a => a.Id == u.Id && a.Name == u.Name && a.Age == u.Age), "Missing users in select");
}
}
}示例2: Test5
public void Test5()
{
var sqlBuilder = new SqlBuilder();
var temp = sqlBuilder.AddTemplate("select * from test where /**where**/ and id = @0", 1);
var temp2 = sqlBuilder.AddTemplate("select * from test2 where /**where**/ and id = @0", 1);
Assert.AreEqual(1, temp.Parameters.Length);
Assert.AreEqual("select * from test where 1=1 and id = @0", temp.RawSql);
sqlBuilder.Where("id2 = @0", 2);
Assert.AreEqual(2, temp2.Parameters.Length);
Assert.AreEqual("select * from test2 where ( id2 = @1 )\n and id = @0", temp2.RawSql);
}示例3: List
/// <summary>
/// The list of results.
/// </summary>
/// <param name="maxResults">The max results.</param>
/// <param name="start">The start</param>
/// <param name="finish">The finish</param>
/// <param name="orderBy">The order by.</param>
/// <returns>The result set</returns>
public override IEnumerable<Guid> List(
int maxResults,
DateTime? start = null,
DateTime? finish = null,
ListResultsOrder orderBy = ListResultsOrder.Descending)
{
var builder = new SqlBuilder();
var t = builder.AddTemplate("select Id from MiniProfilers /**where**/ /**orderby**/ LIMIT(" + maxResults + ")");
if (start != null)
{
builder.Where("Started > @start", new { start });
}
if (finish != null)
{
builder.Where("Started < @finish", new { finish });
}
builder.OrderBy(orderBy == ListResultsOrder.Descending ? "Started desc" : "Started asc");
using (var conn = GetOpenConnection())
{
return conn.Query<Guid>(t.RawSql, t.Parameters).ToList();
}
}示例4: Test6
public void Test6()
{
var sqlBuilder = new SqlBuilder();
var temp = sqlBuilder.AddTemplate("select * from test where /**where**/ and id = @0", 1);
var temp2 = sqlBuilder.AddTemplate("select * from test2 where /**where**/ and id = @0", 1);
sqlBuilder.Where("id2 = @0", 2);
Sql sql1 = temp;
Assert.AreEqual(2, sql1.Arguments.Length);
Assert.AreEqual("select * from test where ( id2 = @1 )\n and id = @0", sql1.SQL);
sqlBuilder.Where("id3 = @0", 3);
Sql sql2 = temp2;
Assert.AreEqual(3, sql2.Arguments.Length);
Assert.AreEqual("select * from test2 where ( id2 = @1 AND id3 = @2 )\n and id = @0", sql2.SQL);
}示例5: GetAlls
public static IEnumerable<BusOperator> GetAlls()
{
var builder = new SqlBuilder();
var template = builder.AddTemplate(@"
select *
from BusOperator bo
order by bo.Name");
return Current.DB.Query<BusOperator>(template.RawSql, template.Parameters);
}示例6: Index
/// <summary>
/// GET: /search
/// GET: /search/index
/// </summary>
/// <returns></returns>
public ActionResult Index(string input, int? page, int? pageSize)
{
ViewBag.Input = input;
page = Math.Max(page ?? 1, 1); // Adjust.
pageSize = Math.Max(Math.Min(pageSize ?? 10, 100), 10); // Adjust.
int start = ((page.Value - 1) * pageSize.Value) + 1;
int finish = page.Value * pageSize.Value;
var builder = new SqlBuilder();
SqlBuilder.Template pager = null, counter = null;
pager = builder.AddTemplate("SELECT * FROM (SELECT /**select**/, ROW_NUMBER() OVER (/**orderby**/) AS RowNumber FROM Questions Q /**where**/) AS Results WHERE RowNumber BETWEEN @start AND @finish ORDER BY RowNumber",
new { start = start, finish = finish });
counter = builder.AddTemplate("SELECT COUNT(*) FROM Questions Q /**where**/");
builder.Select("Q.*");
builder.OrderBy("Q.Time DESC");
builder.Where("Q.Title LIKE @searchInput OR Q.Answer LIKE @searchInput", new { searchInput = "%" + input + "%" });
var results = Current.DB.QueryMultiple(pager.RawSql, pager.Parameters).Read<Question>();
var totals = Current.DB.Query<int>(counter.RawSql, counter.Parameters).First();
ViewData["Href"] = "/search?";
return View(new PagedList<Question>(results, page.Value, pageSize.Value, false, totals));
}示例7: SqlPaging
public void SqlPaging()
{
var b = new SqlBuilder()
.Select("*")
.From("Orders o")
.Where("o.ShipCountry = @country ", new {country = "USA"})
.OrderBy("CustomerID DESC");
var paging = new
{
Page = 1,
PageSize = 25
};
var t = b.AddTemplate(
Templates.Combine(Templates.SqlServer.PagedSelection, Templates.Count),
paging);
using (var db = new SqlConnection(ConnectionStrings.Northwind))
{
Console.WriteLine(t.RawSql);
var results = db.QueryResultsSql<dynamic, int>(t.RawSql, t.Parameters);
results.Set1.Should().HaveCount(paging.PageSize, "because thats how many are in the page");
results.Set2.Single().Should().Be(122, "because thats how many there are");
}
}示例8: Test4
public void Test4()
{
var sqlBuilder = new SqlBuilder();
var temp = sqlBuilder.AddTemplate("select * from test where id = @0", 1);
Assert.AreEqual(1, temp.Parameters.Length);
Assert.AreEqual("select * from test where id = @0", temp.RawSql);
}示例9: Test10
public void Test10()
{
var sqlBuilder = new SqlBuilder();
var temp = sqlBuilder.AddTemplate("select * from test where /**where**/ and id = @0", 1);
sqlBuilder.Where("id2 = 2 OR id2 = 3");
sqlBuilder.Where("id2 = 4");
Assert.AreEqual(1, temp.Parameters.Length);
Assert.AreEqual("select * from test where ( id2 = 2 OR id2 = 3 ) AND ( id2 = 4 )\n and id = @0", temp.RawSql);
}示例10: Test13
public void Test13()
{
var sqlBuilder = new SqlBuilder(new Dictionary<string, string>()
{
{"where", "1<>2"}
});
var temp = sqlBuilder.AddTemplate("select * from test where /**where**/ and id = @0", 1);
Assert.AreEqual(1, temp.Parameters.Length);
Assert.AreEqual("select * from test where 1<>2 and id = @0", temp.RawSql);
}示例11: Test1
public void Test1()
{
var b = new SqlBuilder();
var p = new {a = 123};
var t = b.AddTemplate("select @a {{WHERE}}", p);
b.Where("@b = @c", new {b = "abc", c = "abc"});
int res;
using (var db = new SqlConnection(ConnectionStrings.Default))
res = db.Query<int>(t.RawSql, t.Parameters).Single();
Assert.AreEqual(p.a, res);
}示例12: Test3
public void Test3()
{
var sqlBuilder = new SqlBuilder();
var temp = sqlBuilder.AddTemplate("select * from test where /**where**/ and id = @0", 1);
var test = new[] {1, 2};
sqlBuilder.Where("id2 in (@test)", new { test });
Assert.AreEqual("select * from test where ( id2 in (@0,@1) )\n and id = @0", temp.RawSql);
Assert.AreEqual(2, temp.Parameters.Length);
Console.WriteLine(temp.RawSql);
}示例13: Test11
public void Test11()
{
var sqlBuilder = new SqlBuilder(new Dictionary<string, string>()
{
{"where(test)", null}
});
var temp = sqlBuilder.AddTemplate("select * from test where /**where(test)**/ and id = @0", 1);
sqlBuilder.Where("id2 = 4");
Assert.AreEqual(1, temp.Parameters.Length);
Assert.AreEqual("select * from test where /**where(test)**/ and id = @0", temp.RawSql);
}示例14: BuilderTemplateWOComposition
public void BuilderTemplateWOComposition()
{
var builder = new SqlBuilder();
var template = builder.AddTemplate("SELECT COUNT(*) FROM Users WHERE Age = @age", new {age = 5});
if (template.RawSql == null) throw new Exception("RawSql null");
if (template.Parameters == null) throw new Exception("Parameters null");
using (var db = dbFactory.OpenDbConnection())
{
db.Insert(new User {Age = 5, Name = "Testy McTestington"});
Assert.That(db.QueryScalar<int>(template.RawSql, template.Parameters), Is.EqualTo(1));
}
}示例15: BuildSQL
private static void BuildSQL(string firstName = null, string lastName = null)
{
var builder = new SqlBuilder();
var template = builder.AddTemplate("Select * from Contacts /**where**/ ");
if (firstName != null)
builder.Where("FirstName LIKE '%' + @FirstName + '%'", new { FirstName = firstName });
if (lastName != null)
builder.Where("LastName LIKE '%' + @LastName + '%'", new { LastName = lastName });
using (var cn = new SqlConnection(CONNECTION_STRING))
{
cn.Open();
var c = cn.Query<Contact>(template.RawSql, template.Parameters);
Console.WriteLine("Found {0} contacts", c.Count());
}
Console.ReadKey();
}