C# Database.Query方法代码示例

        static void Main(string[] args)
        {
            var db = new PetaPoco.Database("Data Source=localhost;Initial Catalog=AdventureWorks;Integrated Security=True", "System.Data.SqlClient");

            var records = db.Query<Person>("select FirstName,MiddleName,LastName,Demographics,Suffix from Person.Person");
            var record = records.First();

            Console.WriteLine("Suffix: " + record.Suffix);
            Console.WriteLine("FName: " + record.FirstName);
            Console.WriteLine("Middle: " + record.MiddleName);
            Console.WriteLine("LName: " + record.LastName);
            Console.WriteLine("Demographics: " + record.Demographics);


            Console.ReadLine();
        }

        static void Main(string[] args)
        {
            var db = new Database("Postgres");

            // Show all articles
            foreach (var member in db.Query<Members>("SELECT * FROM cd.members"))
            {
                Console.WriteLine("{0} - {1}, {2}",
                    member.MemberId,
                    member.Surname,
                    member.FirstName);
            }

            db.Dispose();
            Console.WriteLine("Done.");
            Console.ReadLine();
        }

        public List<Table> GetAllTables()
        {
            var db = new Database("document");
            var tables =
                db.Query<Table>(
                    "select [table].name as Name, [table].[extract_batch_size] as ExtractBatchSize, [table].[lookup_descriptor] as LookupDescriptor,  [table].[parent_table] as ParentTableName, [table].[key_columns] as KeyColumns, [table].connection_string as ExtractConnectionString, [table].environment_name as Environment, [table].extract_version as MaxExtractedVersion, [table].prefetch as Prefetch, [table].custom_sql as CustomSql from [table]")
                    .ToList();

            foreach (var table in tables)
            {
                if (table.ParentTableName != null)
                {
                    table.Parent =
                        tables.FirstOrDefault(t => t.Name == table.ParentTableName && t.Environment == table.Environment);
                }

            }

            return tables;
        }

        private static void TestMARS(Database db)
        {
            Console.WriteLine("mapping");
            Console.WriteLine();

            var herdId = 1505147;

            var sql =
                Sql.Builder.Append("execute hepsis");

            var res = db.Query<IdkarBovine>(sql);
        }

        private static void TestMapping(Database db)
        {
            Console.WriteLine("mapping");
            Console.WriteLine();

            var herdId = 1505147;

            var sql =
                Sql.Builder.Append("select * from bovine b with(nolock)")
                   .Append("join idkarbovine ib with(nolock) on b.BovineId = ib.BovineId")
                   .Append("where [email protected]", herdId);

            var res = db.Query<Bovine, IdkarBovine, Bovine>(
                                                            (p, a) =>
                                                            {
                                                                p.IdkarBovine = a;
                                                                return p;
                                                            }, sql);
        }

        private static void DynamicSql(Database db, int herd, DateTime? date)
        {
            // Kysely dynaamisella sql:llä

            var sql = PetaPoco.Sql.Builder
                //.Append("SELECT * FROM idkarbovine with(nolock) sadfdsf") // Eri näppärä virhekäsittely, breakpoint petapoco.cs:n OnException-metodiin.
                .Append("SELECT * FROM idkarbovine with(nolock)")
                .Append("WHERE [email protected]", herd);

            if (date.HasValue)
                sql.Append("AND ValidFromDate<[email protected]", date.Value);

            if (date.HasValue)
                sql.Append("AND ValidDueDate>[email protected]", date.Value);

            sql.Append("ORDER BY EarNr");

            var results = db.Query<Bovine>(sql);

            foreach (var a in results)
            {
                Console.WriteLine("{0} - {1}, {2}", a.BovineId, a.EarNr, a.NameShort);
            }
            Console.WriteLine();
        }

        private static void BasicQuery(Database db)
        {
            // Peruskysely
            var customers = db.Query<Customer>("SELECT TOP 10 * FROM Customer WITH(NOLOCK)");

            foreach (var a in customers)
            {
                Console.WriteLine("{0} - {1}", a.CustId, a.CustName);
            }
            Console.WriteLine();
        }

 public IEnumerable<Route> RoutesForUser(int userId, long ts)
 {
     var db = new Database(_config.ConnectionString, "System.Data.SqlClient");
     return db.Query<Route>(@"select v.* from MR.vRoute v inner join MR.tRoute r on r.RouteId = v.RouteId where v.CreatorUserId = @0 and r.timestamp > @1", userId, ts);
 }

        public bool Archive()
        {
            try
            {
                Database db = new Database("Database");

                foreach(var archiveMultimedia in db.Query<Multimedia>("SELECT ID, ProductID, MultimediaTypeID, Name, FileName, FilePath, Version FROM Multimedia WHERE Name = @0", this.Name))
                {
                   archiveMultimedia.IsActive = false;
                   new Database("Database").Update(archiveMultimedia);
                }

                return true;
            }
            catch (Exception ex)
            {
                Logger.WriteLine("Failed to archive file {0}: {1}", this.Name, ex);
                return false;
            }
        }

 private void roleGridEdit2_EditValueChanged(object sender, EventArgs e)
 {
     if (roleGridEdit2.EditValue == null) {
         gridunalloc.DataSource = gridalloc.DataSource = null;
         gridunalloc.RefreshDataSource();
         gridalloc.RefreshDataSource();
         return;
     }
     var re = roleGridEdit2.GetSelectedValue();
     if (re == null)
         return;
     using (var db = new Database()) {
         var ur = db.Query<t_user>("select * from t_user")
             .Join(db.Query<t_roleuser>("select * from t_roleuser where [email protected]", re.Id),
             o => o.Id, k => k.UserId, (o, k) => new { o, k }
             ).Select(z => z.o).OrderBy(k => k.UserName).ToList();
         this.gridalloc.DataSource = ur;
         this.gridunalloc.DataSource = AllUser.Where(k => !ur.Any(j => j.UserName == k.UserName)).Where(k => k.UserName != "admin").OrderBy(k => k.UserName).ToList();
         return;
     }
 }

        private void gridView1_FocusedRowChanged(object sender, DevExpress.XtraGrid.Views.Base.FocusedRowChangedEventArgs e)
        {
            var et = this.gridControlEx1.GetFocusedDataSource<t_role>();
            if (null == et) {
                this.gridControlEx2.DataSource = null;
                return;
            }
            using (var db = new Database()) {
                var users = db.Query<t_user>("Select * from t_user");
                var rus = db.Query<t_roleuser>("Select * from t_roleuser");
                var op = (from o in users
                          join k in rus.Where(t => t.RoleId == et.Id)
                              on o.Id equals k.UserId

                          select o);

                this.gridControlEx2.DataSource = op.ToList();
            }
        }

    private string WinFlagColumn = "sWin"; // 中獎識別欄位

    #endregion Fields

    #region Methods

    /// <summary>
    /// 抽獎方法
    /// </summary>
    /// <param name="n">欲抽出數量</param>
    /// <param name="PKColumn">主索引鍵</param>
    /// <param name="MaxDrawQuota">中獎名額</param>
    /// <param name="WinFlagColumn">中獎識別欄位</param>
    /// <param name="DistinctColumn">排除重複的欄位（判斷使用者身份的唯一值，例如：E-mail、Facebook UID 等）</param>
    /// <param name="TableName">資料表</param>
    /// <param name="BasicCondition">基本 SQL 條件</param>
    /// <param name="IsGroup">若為真，則每個人中獎機率相同；若為假，則名單越多者中獎機率越高。</param>
    /// <returns>回傳 DrawResult 類別，其下有 Result（是否成功，布林值）與 Msg（回傳訊息，若成功，則為不重複的欄位值）</returns>
    public static DrawResult MakeDraw(int n, int MaxDrawQuota, string PKColumn, string WinFlagColumn, string DistinctColumn, string TableName, string BasicCondition, bool IsGroup)
    {
        PetaPoco.Database db = new PetaPoco.Database("conn");

        int counter = 0;
        DrawResult result = new DrawResult();

        PetaPoco.Sql sql = PetaPoco.Sql.Builder;
        sql.Append(String.Format("SELECT MAX({0}) FROM {1} WHERE 1=1", PKColumn, TableName));
        sql.Append(BasicCondition);
        if (IsGroup)
        {
            sql.Append("GROUP BY [" + DistinctColumn + "]");
        }

        var data = db.Query<DataModel_a12SupauCheckin>(sql);
        counter = data.Count();

        if (counter < n)
        {
            result.Result = false;
            result.Msg = "名單不足以抽出這樣的數量喔！";

            return result;
        }

        if (n < 1)
        {
            result.Result = false;
            result.Msg = "數量請至少為 1。";

            return result;
        }

        if (n > MaxDrawQuota)
        {
            result.Result = false;
            result.Msg = "抽出名額不得大於中獎名額 " + MaxDrawQuota + " 名 喔！";

            return result;
        }

        #region 檢查剩餘名額

        sql = PetaPoco.Sql.Builder;
        sql.Append(String.Format("SELECT {0} FROM {1} WHERE {2}='1'", PKColumn, TableName, WinFlagColumn));
        sql.Append(BasicCondition);
        var r = db.Query<DataModel_a12SupauCheckin>(sql);

        // 若目前中獎人數大於等於中獎名額
        if (r.Count() >= MaxDrawQuota)
        {
            result.Result = false;
            result.Msg = "名額已滿";

            return result;
        }

        #endregion

        if (!IsGroup)
        {
            if (n == 1)
            {
                sql = PetaPoco.Sql.Builder;
                sql.Append(String.Format("SELECT TOP 1 {0} FROM {1} WHERE 1=1", DistinctColumn, TableName));
                sql.Append(BasicCondition);
                sql.Append("ORDER BY NEWID()");

                var a = db.SingleOrDefault<DataModel_a12SupauCheckin>(sql);
                result.Result = true;
                result.Msg = "'" + a.sFBUID.ToString() + "'";

                return result;
            }
            else
            {
                string list_column = MakeDraw(n - 1, MaxDrawQuota, PKColumn, WinFlagColumn, DistinctColumn, TableName, BasicCondition, IsGroup).Msg;

                sql = PetaPoco.Sql.Builder;
                sql.Append(String.Format("SELECT TOP 1 * FROM {0} WHERE 1=1", TableName));
                sql.Append(String.Format("{0} AND [{1}] NOT IN ({2})", BasicCondition, DistinctColumn, list_column));
//.........这里部分代码省略.........

        private void loadComments()
        {
            if (_comments == null)
            {
                _comments = new List<Comment>();
            }

            var db = new Database("umbracoDbDSN");
            foreach (var comment in db.Query<Comment>("Select * from forumComments where topicId = @0", Id))
            {
                _comments.Add(comment);
            }
        }

 private static IEnumerable<string> GetTableNames(Database database)
 {
     return
         database.Query<string>(
             string.Format("SELECT name FROM sqlite_master " + "WHERE type = 'table'" + "ORDER BY 1"));
 }

 private static IEnumerable<dynamic> GetItems(string targetTableName, Database database)
 {
     return database.Query<dynamic>(string.Format("Select * from {0}  order by 1", targetTableName));
 }