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

        private void Form1_Load(object sender, EventArgs e)
        {
            var db = new PetaPoco.Database("tencentcloud");

            //To query a scalar
            foreach(var a in db.Query<article>("select * from articles"))
            {
                listBox1.Items.Add(string.Format("{0}-{1}", a.article_id, a.title));
            }

            listBox1.Items.Add("\r\n");
            long count = db.ExecuteScalar<long>("select Count(*) from articles");
            listBox1.Items.Add(string.Format("count: {0}",count ));
            listBox1.Items.Add("\r\n");
            //@0  代表占位符  SingleOrDefault
            var abc = db.SingleOrDefault<article>("select * from articles where [email protected]",1);
            listBox1.Items.Add(abc);
            listBox1.Items.Add("\r\n");

            //Paged Fetches 分页
            var result = db.Page<article>(1, 3, "select * from articles where draft=1 order by date_created ");

            foreach (var temp in result.Items)
            {
                listBox1.Items.Add(string.Format("title: {0}", temp.title));
            }

            listBox1.Items.Add("\r\n");
            listBox1.Items.Add("结束");
        }

        private void button3_Click(object sender, EventArgs e)
        {
            try
            {
                // Create a PetaPoco database object
                var db = new PetaPoco.Database("sqlite");

                // find the (presumably) most recently created foo
                int id = db.ExecuteScalar<int>("SELECT max(id) from foo");

                // Get a record
                var foo = db.SingleOrDefault<foo>("SELECT * FROM foo WHERE [email protected]", id);

                // Change it
                foo.name = "PetaPoco changed your name!";

                // Save it
                db.Update("foo", "Id", foo);
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message + Environment.NewLine + ex.StackTrace);
            }

            this.fooQuery1.Refresh();
        }

        public static PetaPoco.Database GetDatabase(bool ValidateDatabase = true)
        {
            dbMutex.WaitOne();

            if (!System.IO.File.Exists(DbFile))
                CreateDatabase(DbFile);

            try
            {
                var db = new PetaPoco.Database(@"Data Source={0};Version=3;".FormatStr(DbFile), "System.Data.SQLite");
                if (ValidateDatabase)
                {
                    int version = 0;
                    try
                    {
                        version = db.ExecuteScalar<int>("select databaseversion from [version]");
            #if(DEBUG)
                        if (version != Globals.DB_VERSION)
                        {
                            db.Dispose();
                            // recreate db
                            System.IO.File.Delete(DbFile);
                            CreateDatabase(DbFile);
                            db = new PetaPoco.Database(@"Data Source={0};Version=3;".FormatStr(DbFile), "System.Data.SQLite");
                        }
            #endif
                    }
                    catch (Exception)
                    {
                        throw new SetupException();
                    }
                }

                return db;
            }
            finally
            {
                dbMutex.ReleaseMutex();
            }
        }

        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);
            }
        }

 private static int CountTest(string connectionString)
 {
     var db = new PetaPoco.Database(connectionString, "System.Data.SqlClient");
     long count = db.ExecuteScalar<long>("SELECT Count(*) FROM Home");
     return 1;
 }

 public static int TotalForum()
 {
     using (PetaPoco.Database db = new PetaPoco.Database("sqlconnection"))
     {
         return db.ExecuteScalar<int>("SELECT COUNT(1) FROM jexus_forums ");
     }
 }

 public static int TotalComment(int fid)
 {
     using (PetaPoco.Database db = new PetaPoco.Database("sqlconnection"))
     {
         return db.ExecuteScalar<int>("SELECT COUNT(1) FROM jexus_comments  WHERE [email protected]", fid);
     }
 }

        public SettingsProvider(string scope)
        {
            _currentSettings = new Hashtable();
            _scopelessSettings = new Hashtable();

            string p = System.IO.Path.Combine(new string[] { System.Environment.GetFolderPath(Environment.SpecialFolder.CommonApplicationData), "GAPP" });
            if (!Directory.Exists(p))
            {
                Directory.CreateDirectory(p);
            }
            string fn = System.IO.Path.Combine(p, "settings.db3");

            //create backup first
            List<string> availableBackups = new List<string>();
            if (File.Exists(fn))
            {
                File.Copy(fn, string.Format("{0}.{1}.bak", fn, DateTime.Now.ToString("yyyyMMddHHmmss")));

                //keep maximum of X backups
                availableBackups = Directory.GetFiles(p, "settings.db3.*.bak").OrderBy(x => x).ToList();
                while (availableBackups.Count > 20)
                {
                    File.Delete(availableBackups[0]);
                    availableBackups.RemoveAt(0);
                }
            }

            bool dbOK;
            bool backupUsed = false;
            do
            {
                try
                {
                    _database = new PetaPoco.Database(string.Format("data source=file:{0}", fn), Community.CsharpSqlite.SQLiteClient.SqliteClientFactory.Instance);
                    dbOK = _database.ExecuteScalar<string>("PRAGMA integrity_check") == "ok";
                    //if (availableBackups.Count > 2) dbOK = false; //test
                }
                catch
                {
                    dbOK = false;
                }
                if (!dbOK)
                {
                    backupUsed = true;
                    try
                    {
                        _database.Dispose();
                    }
                    catch
                    {
                    }
                    _database = null;

                    //delete settings and move to latest
                    File.Delete(fn);
                    if (availableBackups.Count > 0)
                    {
                        File.Move(availableBackups[availableBackups.Count - 1], fn);
                        availableBackups.RemoveAt(availableBackups.Count - 1);
                    }
                }

            } while (!dbOK);

            if (backupUsed)
            {
                System.Windows.Forms.MessageBox.Show("The settings file was corrupt and a backup file is restored.", "Settings");
            }

            if (!TableExists("Settings"))
            {
                _database.Execute("create table 'Settings' (Name text, Value text)");
                _database.Execute("insert into Settings (Name, Value) values ('Scope', 'default')");
            }

            if (!TableExists("SettingsScope"))
            {
                _database.Execute("create table 'SettingsScope' (ID integer primary key autoincrement, Name text)");
            }

            if (string.IsNullOrEmpty(scope))
            {
                scope = _database.ExecuteScalar<string>("select Value from Settings where [email protected]", "Scope");
                if (string.IsNullOrEmpty(scope))
                {
                    scope = "default";
                }
            }

            _scope = _database.FirstOrDefault<SettingsScope>("where [email protected]", scope);
            if (_scope == null)
            {
                _scope = new SettingsScope();
                _scope.Name = scope;
                _database.Save(_scope);
            }

            if (!TableExists(string.Format("Settings_{0}",_scope.ID)))
            {
                _database.Execute(string.Format("create table 'Settings_{0}' (Name text, Value text)", _scope.ID));
//.........这里部分代码省略.........

        /// <summary>
        /// Creates tables if they don't already exist in the database
        /// </summary>
        /// <param name="tableName">Name of the table to create</param>
        /// <param name="sql">SQL statement for table creation</param>
        /// <param name="connectionString">SQL connection string</param>
        private static void CreateTable(string tableName, string sql, string connectionString)
        {
            using (var db = new PetaPoco.Database(connectionString))
            {
                var count = db.ExecuteScalar<int>(TableExists, tableName);

                if (count > 0)
                {
                    return;
                }

                db.Execute(sql);
            }
        }

        private void button4_Click(object sender, EventArgs e)
        {
            try
            {
                // Create a PetaPoco database object
                var db = new PetaPoco.Database("sqlite");

                // find the (presumably) most recently created foo
                int id = db.ExecuteScalar<int>("SELECT max(id) from foo");

                // Delete it
                db.Delete("foo", "Id", null, id);
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message + Environment.NewLine + ex.StackTrace);
            }
            this.fooQuery1.Refresh();
        }