PetaPoco 基础操作

7/27/2021 Auther:cdeng View:316

var db=new PetaPoco.Database("connectionStringName");

long count=db.ExecuteScalar<long>("SELECT Count(*) FROM articles");

var a = db.SingleOrDefault<article>("SELECT * FROM articles WHERE article_id=@0", 123));

var a=db.SingleOrDefault<article>("WHERE article_id=@0", 123);

var a = db.SingleOrDefault<article>("FROM whatever WHERE id=@0", 123);

var a = db.SingleOrDefault<article>(some_id);

var result=db.Page<article>(1, 20,"SELECT * FROM articles WHERE category=@0 ORDER BY date_posted DESC", "coolstuff");

// 逐行查询每条记录,一次只从数据库表中取一条数据
foreach (var a in db.Query<article>("SELECT * FROM articles"))
    Console.WriteLine("{0} - {1}", a.article_id, a.title);

foreach (var a in db.Fetch<dynamic>("SELECT * FROM articles"))
    Console.WriteLine("{0} - {1}", a.article_id, a.title);
foreach (var x in db.Query<long>("SELECT article_id FROM articles"))
    Console.WriteLine("Article ID: {0}", x);

if (db.Exists<article>(23)) 
    db.Delete <article>(23);

db.Execute("DELETE FROM articles WHERE draft<>0");

// Create the article
var a=new article();
a.title="My new article";
a.content="PetaPoco was here";
// Insert it

// Get a record
var a=db.SingleOrDefault<article>("SELECT * FROM articles WHERE article_id=@0", 123);
// Change it
a.content="PetaPoco was here again";
// Save it

db.Update("articles", "article_id", new { title="New title" }, 123);

db.Update<article>("SET title=@0 WHERE article_id=@1", "New Title", 123);

a.Update(new string[] { "title" });
db.Update<user>(u, new string[] { "title" });


db.Delete<article>("WHERE article_id=@0", 123);

using(var scope = db.GetTransaction())
    //todo: Do transacted updates here

    // Commit


//支持存储过程,还不支持out 参数?
db.Query<type>("CALL storedproc")     // MySQL stored proc
db.Query<type>("EXECUTE stmt")        // MySQL prepared statement
db.Query<type>("EXECUTE storedproc")  // SQL Server

databaseQuery.Execute("insert into temp1 (t) values (@0)", 
                new SqlParameter() { SqlDbType = SqlDbType.VarBinary, Value = DbNull.Value });

var id=123;
var a=db.Query<article>(PetaPoco.Sql.Builder
    .Append("SELECT * FROM articles")
    .Append("WHERE article_id=@0", id)
    .Append("AND date_created<@0", DateTime.UtcNow)

var id=123;
var sql=PetaPoco.Sql.Builder
    .Append("SELECT * FROM articles")
    .Append("WHERE article_id=@0", id);

if (start_date.HasValue)
    sql.Append("AND date_created>=@0", start_date.Value);

if (end_date.HasValue)
    sql.Append("AND date_created<=@0", end_date.Value);

var a=db.Query<article>(sql)

sql.Append("AND date_created>=@start AND date_created<=@end", 

var sql=PetaPoco.Sql.Builder()
            .Where("date_created < @0", DateTime.UtcNow)
            .OrderBy("date_created DESC");

var sql = Sql.Builder