C# PetaPoco.Database.Fetch方法代码示例

        /// <summary>
        /// Gets an event stream from SQL for a given ID
        /// </summary>
        /// <param name="aggregateRootId">Aggregate root ID</param>
        /// <param name="startVersion">Starting version of the event stream</param>
        /// <returns>List of events</returns>
        public List<EventStream> GetEventStream(Guid aggregateRootId, long startVersion)
        {
            CreateEventTable();

            using (var db = new PetaPoco.Database(_eventConnectionStringName))
            {
                var events = db.Fetch<EventStream>(GetEventsForId, aggregateRootId, startVersion);

                return events;
            }
        }

        public ActionResult Search(DateTime? d = null)
        {
            using (var db = new PetaPoco.Database("DefaultConnection"))
            {
                var sql = PetaPoco.Sql.Builder.Select("eaj.ahdm,eaj.ah,eaj_ftsy.ktrq,eaj_ftsy.kssj,eaj.dsr,eaj.ayms");
                sql.From("eaj");
                sql.InnerJoin("eaj_ftsy").On("eaj.ahdm = eaj_ftsy.ahdm");
                sql.Where("eaj_ftsy.ktrq = @0", (d ?? DateTime.Now).ToString("yyyyMMdd"));
                sql.OrderBy("eaj_ftsy.kssj,eaj.ahdm");

                return View(db.Fetch<dynamic>(1, 200, sql));
            }
        }

        public ActionResult CaseSearch(string xm, string fy, string start_time_year, string start_time_month,
            string end_time_year, string end_time_month)
        {
            using (var db = new PetaPoco.Database("HssfConnection"))
            {
                var data =
                    db.Fetch<dynamic>(
                        "SELECT a.ahdm,a.ah,b.ktrq,b.kssj,a.dsr,a.ayms from EAJ a,EAJ_FTSY b where a.ahdm = b.ahdm and b.ktrq = @0 order by b.kssj,a.ahdm",
                        DateTime.Now.ToString("yyyyMMdd"));

                if (!string.IsNullOrEmpty(start_time_year))
                {
                    var startTime = new DateTime(int.Parse(start_time_year), int.Parse(start_time_month), 1);
                    var endTime = new DateTime(int.Parse(end_time_year), int.Parse(end_time_month), 1).AddMonths(1).AddDays(-1);

                    var sql = PetaPoco.Sql.Builder.Select("COUNT(*)");
                    sql.From("EAJ");
                    sql.LeftJoin("EAJ_FTSY").On("EAJ.ahdm = EAJ_FTSY.ahdm");

                    if (!fy.IsEmpty())
                    {
                        sql.Where("EAJ_FTSY.sly = @0", fy);
                    }

                    if (!xm.IsEmpty())
                    {
                        sql.Where("EAJ.ayms LIKE @0", "'%" + xm + "%'");
                    }

                    sql.Where("EAJ_FTSY.ktrq >= @0 AND EAJ_FTSY.ktrq <= @1", startTime.ToString("yyyyMMdd"), endTime.ToString("yyyyMMdd"));
                    ViewData["TotalCount"] = db.ExecuteScalar<int>(sql);
                }
                


                return View(data);
            }
        }

 public static List<Models.Forums> GetUserForums(int uid, int top)
 {
     using (PetaPoco.Database db = new PetaPoco.Database("sqlconnection"))
     {
         return db.Fetch<Models.Forums>(1, top, "SELECT \"fid\",cid,uid,ruid,title,keywords,'' as \"content\",addtime,updatetime,lastreply,views,comments,favorites,closecomment,is_top,is_hidden,ord  FROM   jexus_forums WHERE [email protected] ORDER BY addtime DESC ", uid).ToList();
     }
 }

 public static List<Models.Comment> GetUserComment(int uid, int top)
 {
     using (PetaPoco.Database db = new PetaPoco.Database("sqlconnection"))
     {
         return db.Fetch<Models.Comment>(1, top, "SELECT \"id\",  fid  ,uid   ,   \"content\"      ,replytime FROM jexus_comments WHERE [email protected]  ORDER BY replytime DESC ", uid).ToList();
     }
 }

 /// <summary>
 /// 获取最新的帖子
 /// </summary>
 /// <returns></returns>
 public static List<Models.Forums> GetLastForum(int top)
 {
     using (PetaPoco.Database db = new PetaPoco.Database("sqlconnection"))
     {
         return db.Fetch<Models.Forums>(1, top, " SELECT  * FROM jexus_forums ORDER BY  addtime DESC").ToList();
     }
 }

      private IList<HistoryEntry> FetchInternal(QueryParameters parameters, BonusCalculationType bonusCalculation)
      {
         Debug.Assert(TableExists(SqlTable.WuHistory));

         var select = new PetaPoco.Sql(SqlTableCommandDictionary[SqlTable.WuHistory].SelectSql);
         select.Append(WhereBuilder.Execute(parameters));
         GetProduction.BonusCalculation = bonusCalculation;
         using (var connection = new SQLiteConnection(ConnectionString))
         {
            connection.Open();
            using (var database = new PetaPoco.Database(connection))
            {
               List<HistoryEntry> query = database.Fetch<HistoryEntry>(select);
               return query;
            }
         }
      }