C# PetaPoco.Database类代码示例

        private void btnOK_Click(object sender, EventArgs ea)
        {
            var k = roleGridEdit1.GetSelectedValue();
            if (null == k) return;
            List<MenuCtlData> lst = new List<MenuCtlData>(100);
            foreach (TreeListNode td in treeCtl1.Nodes)
            {
                var mnugrp = td.GetValue(0).ToString();
                foreach (TreeListNode tds in td.Nodes)
                {
                    var mnuname = tds.GetValue(0).ToString();
                    foreach (TreeListNode tmd in tds.Nodes)
                    {
                        var mcd = tmd.Tag as MenuCtlData;
                        if (null != mcd)
                        {
                            mcd.IsEnabled = tmd.Checked;
                            mcd.MenuGrpName = mnugrp;
                            mcd.MenuName = mnuname;
                            lst.Add(mcd);
                        }
                        foreach (TreeListNode tkd in tmd.Nodes)
                        {
                            var md = tkd.Tag as MenuCtlData;
                            if (null == md) continue;
                            md.IsEnabled = tkd.Checked;
                            md.MenuGrpName = mnugrp; md.MenuName = mnuname;
                            lst.Add(md);
                        }
                    }
                }
            }
            var usr = GetCurUser<t_user>();
            using (var db = new Database())
            {

                var et = db.FirstOrDefault<t_roleset>("select * from t_roleset where [email protected]", k.Id);

                var roleset = et;
                if (et != null)
                {
                    roleset.RoleSetTree = Ultra.Web.Core.Common.ObjectHelper.SerializeJson(lst);
                }
                else
                {
                    roleset = new t_roleset
                    {
                        RoleSetTree = Ultra.Web.Core.Common.ObjectHelper.SerializeJson(lst),
                        RoleId = k.Id,
                        IsUsing = true,
                        RoleName = k.Name,
                        Guid = Guid.NewGuid(),
                        Creator =this.CurUser,
                        CreateDate = TimeSync.Default.CurrentSyncTime
                    };
                }
                db.Save(roleset);
            }
            MsgBox.ShowMessage(null, "保存成功!");
        }

        public FrmNewRegist()
        {
            InitializeComponent();
            //string sPwd = Des.Decrypt(ClsFrmMng.KEY, ConfigurationManager.AppSettings["DbPwd"]);
            db = new Database("XE");

            //db.OpenSharedConnection();
            lstOutpatientCategory = db.Fetch<OUTPATIENT_CATEGORY>("");
            OUTPATIENT_CATEGORYTextEdit.Properties.TextEditStyle = DevExpress.XtraEditors.Controls.TextEditStyles.DisableTextEditor;
            OUTPATIENT_CATEGORYTextEdit.Properties.Columns.Add(new DevExpress.XtraEditors.Controls.LookUpColumnInfo("CATEGORY_ID"));
            OUTPATIENT_CATEGORYTextEdit.Properties.Columns.Add(new DevExpress.XtraEditors.Controls.LookUpColumnInfo("CATEGORY_NAME"));
            OUTPATIENT_CATEGORYTextEdit.Properties.DataSource = lstOutpatientCategory;

            OUTPATIENT_CATEGORYTextEdit.Properties.ValueMember = "CATEGORY_ID";         // 对应ID
            OUTPATIENT_CATEGORYTextEdit.Properties.DisplayMember = "CATEGORY_NAME";     // 显示内容

            patientReg.STATUS = 0;
            patientReg.OPERATOR = ClsFrmMng.WorkerID;

            pATIENTREGISTBindingSource.DataSource = patientReg;
            //db.CloseSharedConnection();

            ConditionValidationRule ruleNoEmpty = new ConditionValidationRule();
            ruleNoEmpty.ConditionOperator = ConditionOperator.IsNotBlank;
            ruleNoEmpty.ErrorText = "该项不能为空。";
            dxValidationProvider1.SetValidationRule(NAMETextEdit, ruleNoEmpty);
            dxValidationProvider1.SetValidationRule(OUTPATIENT_CATEGORYTextEdit, ruleNoEmpty);

            pATIENTBASEINFOBindingSource.DataSource = db.Fetch<PATIENT_BASEINFO>("");
            NAMETextEdit.Properties.EditValueChanged += Properties_EditValueChanged;
        }

        public FrmDefaultDocAdvice_Cfg()
        {
            InitializeComponent();

            //string sPwd = Des.Decrypt(ClsFrmMng.KEY, ConfigurationManager.AppSettings["DbPwd"]);
            db = new Database("XE");
        }

 private static List<Comment> GetAllComments()
 {
     using (var db = new Database("umbracoDbDSN"))
     {
         return db.Fetch<Comment>("SELECT * FROM forumComments WHERE isSpam = 1 ORDER BY id DESC");
     }
 }

        //[RequireBasicAuthentication("AppHarbor")]
        public ActionResult Create(ProvisioningRequest provisionRequest)
        {
            if (provisionRequest == null || string.IsNullOrEmpty(provisionRequest.plan)) provisionRequest = new ProvisioningRequest() { callback_url = "/Response", heroku_id = Guid.NewGuid().ToString(), plan = "Free" };
            var db = new Database("DefaultConnection");
            Plan plan;
            if (!Enum.TryParse<Plan>(provisionRequest.plan, true, out plan))
            {
                throw new ArgumentException(string.Format("Plan \"{0}\" is not a valid plan", provisionRequest.plan));
            }

            var purchase = new Purchase()
            {
                CreatedBy = string.Format("{0};{1}", Request.GetForwardedHostAddress(), User.Identity.Name),
                UniqueId = Guid.NewGuid().ToString(),
                Plan = plan,
                ProviderId = provisionRequest.heroku_id,
                ProvisionStatus = ProvisionStatus.Provisioning,
                ApiKey = Guid.NewGuid().ToString(),
                ApiSecretKey = Guid.NewGuid().ToString()
            };
            // TODO: Provision the resource
            purchase.ProvisionStatus = ProvisionStatus.Provisioned;
            Purchase.Save(db, purchase);
            var output = new
            {
                id = purchase.UniqueId,
                config = new
                {
                    CONFIG_ApiKey = purchase.ApiKey,
                    CONFIG_ApiSecretKey = purchase.ApiSecretKey
                }
            };

            return Json(output, JsonRequestBehavior.AllowGet);
        }

 private void InitializeDatabase()
 {
     _db = new Database(_connectionStringName);
     _db.EnableAutoSelect = false;
     _db.EnableNamedParams = false;
     _db.ForceDateTimesToUtc = false;
 }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="base_id"></param>
        /// <param name="reg_id"></param>
        /// <param name="isTemp">0:长期, 1:临时</param>
        public FrmNewDav(Int64 base_id, Int64 reg_id, int isTemp)
        {
            InitializeComponent();
            if (isTemp == 0)
                this.Text = "新建长期医嘱";
            else
                this.Text = "新建临时医嘱";

            //string sPwd = Des.Decrypt(ClsFrmMng.KEY, ConfigurationManager.AppSettings["DbPwd"]);
            db = new Database("XE");

            _regID = reg_id;
            _baseID = base_id;
            _isTemp = isTemp;

            docAdv.REG_ID = _regID;
            docAdv.BASE_INFO_ID = _baseID;
            docAdv.OPERATOR = ClsFrmMng.WorkerID;
            docAdv.ADVICE_TYPE = _isTemp;
            docAdv.IS_DEL = 0;
            dOCADVICEBindingSource.DataSource = docAdv;

            vALUECODEBindingSource.DataSource = db.Fetch<VALUE_CODE>("");

            M_UNITLookUpEdit.Properties.DataSource = db.Fetch<VALUE_CODE>("where GROUPNAME = @0", new object[] { 14 });
            M_UNITLookUpEdit.Properties.DisplayMember = "DSP_MEMBER";
            M_UNITLookUpEdit.Properties.ValueMember = "VALUE_MEMBER";

            M_ACTIONLookUpEdit.Properties.DataSource = db.Fetch<VALUE_CODE>("where GROUPNAME = @0", new object[] { 13 });
            M_ACTIONLookUpEdit.Properties.DisplayMember = "DSP_MEMBER";
            M_ACTIONLookUpEdit.Properties.ValueMember = "VALUE_MEMBER";
        }

 public void CreateTables()
 {
     using (var db = new Database("DataSource=\"test.sdf\"; Password=\"chrissiespassword\"", "System.Data.SqlServerCe.4.0")) {
         db.Execute("CREATE TABLE Person (Id int IDENTITY(1,1) PRIMARY KEY, LastName nvarchar (40) NOT NULL, FirstName nvarchar (40), AddressId int NOT NULL);");
         db.Execute("CREATE TABLE Address (Id int IDENTITY(1,1) PRIMARY KEY, Street nvarchar (40) NOT NULL, HouseNumber nvarchar (10));");
     }
 }

        ////utility function to build the sql statement to perform the join
        //private static Sql sql()
        //{
        //    return Sql
        //        .Builder
        //        .Append("SELECT a.id, a.bike_id, a.rider_id, a.route_id, a.ride_date, a.notes,")
        //        .Append("a.reward, a.distance, a.paid, a.payment_id,")
        //        .Append("b.name AS route, c.name AS rider, d.name AS bike")
        //        .Append("FROM ride a, route b, rider c, bike d")
        //        .Append("WHERE a.route_id = b.id AND a.rider_id = c.id AND a.bike_id = d.id");
        //}

        public static List<Ride> getRides()
        {
            using (Database db = new PetaPoco.Database(ModelConfig.connectionStringName("bikes")))
            {
                return db.Fetch<Ride>("");
            }
        }

        public FrmRptPatientInfo()
        {
            InitializeComponent();

            //string sPwd = Des.Decrypt(ClsFrmMng.KEY, ConfigurationManager.AppSettings["DbPwd"]);
            db = new Database("XE");
        }

 public static Route getRoute(int id)
 {
     using (Database db = new PetaPoco.Database(ModelConfig.connectionStringName("bikes")))
     {
         return db.FirstOrDefault<Route>("WHERE id = @0", id);
     }
 }

        public void Delete(long id)
        {
            if (id <= 0)
            {
                return;
            }

            ApiAccessPolicy policy = new ApiAccessPolicy(typeof(MixERP.Net.Entities.Core.Account), "DELETE");
            policy.Authorize();

            if (!policy.IsAuthorized)
            {
                throw new HttpResponseException(HttpStatusCode.Forbidden);
            }

            try
            {
                using (Database db = new Database(Factory.GetConnectionString(), "Npgsql"))
                {
                    db.Delete("core.accounts", "account_id", null, id);
                }
            }
            catch
            {
                throw new HttpResponseException(HttpStatusCode.InternalServerError);
            }
        }

     public static List<Route> getRoutes()
     {
         using (Database db = new PetaPoco.Database(ModelConfig.connectionStringName("bikes")))
         {
             return db.Fetch<Route>("WHERE deleted = FALSE");
         }
 }

        public FrmNewCaseHis(bool isAdd, Int64 base_id, Int64 reg_id)
        {
            InitializeComponent();

            //string sPwd = Des.Decrypt(ClsFrmMng.KEY, ConfigurationManager.AppSettings["DbPwd"]);
            db = new Database("XE");

            _reg_id = reg_id;
            _isAdd = isAdd;
            _baseID = base_id;

            //ConditionValidationRule ruleNoEmpty = new ConditionValidationRule();
            //ruleNoEmpty.ConditionOperator = ConditionOperator.IsNotBlank;
            //ruleNoEmpty.ErrorText = "该项不能为空。";
            //dxValidationProvider1.SetValidationRule(MEMOMemoEdit, ruleNoEmpty);
            //dxValidationProvider1.SetValidationRule(DOSE_MEMOMemoEdit, ruleNoEmpty);

            caseHis.REG_ID = _reg_id;
            caseHis.BASE_INFO_ID = _baseID;
            caseHis.OPERATOR = ClsFrmMng.WorkerID;
            cASEHISTORYBindingSource.DataSource = caseHis;

            lookUpEdit1.Properties.DataSource = db.Fetch<VALUE_CODE>("where GROUPNAME = @0", new object[] { 11 });
            lookUpEdit1.Properties.DisplayMember = "DSP_MEMBER";
            lookUpEdit1.Properties.ValueMember = "VALUE_MEMBER";

            vALUECODEBindingSource.DataSource = db.Fetch<VALUE_CODE>("where GROUPNAME = @0", new object[] { 181 });
        }

        public void SetPoints(int trackId, TrackPoint[] points)
        {
            var sb = new StringBuilder();
            for (int i = 0; i < points.Length; i++)
            {
                if (i % 1000 == 0)
                {
                    if(sb.Length>0) sb.Remove(sb.Length - 1, 1);
                    sb.AppendLine(";").AppendLine("insert into MR.tTrackPoint (TrackId, Idx, Lat, Lon, Elevation, PointTime) values ");
                }

                var p = points[i];
                sb.AppendLine().Append("(")
                    .Append(trackId).Append(",")
                    .Append(i).Append(",")
                    .Append(p.Lat.ToString(CultureInfo.InvariantCulture)).Append(",")
                    .Append(p.Lon.ToString(CultureInfo.InvariantCulture)).Append(",")
                    .Append(p.Elevation.ToString(CultureInfo.InvariantCulture)).Append(",")
                    .Append("'").Append(p.PointTime.ToString("o",CultureInfo.InvariantCulture)).Append("'")
                    .Append("),");

            }
            var sql = sb.ToString().TrimEnd(',');
            var db = new Database(_config.ConnectionString, "System.Data.SqlClient");

            try
            {
                db.Execute(sql);
            }
            catch (SqlException ex)
            {
                throw new Exception(sql, ex);
            }
        }