public static void Save(Database db, Purchase purchase) { var existing = db.SingleOrDefault<Purchase>("SELECT * FROM purchases WHERE UniqueId=@0 ", purchase.UniqueId); if (existing != null) db.Update("purchases", "PurchaseId", existing); else db.Insert("purchases", "PurchaseId", purchase); }
public static Tag GetTag(int id) { var db = new Database("umbracoDbDSN"); var tag = db.SingleOrDefault<Tag>("where id = @0", id); if (tag != null) return tag; throw new ArgumentNullException(string.Format("No tag found with id = {0}", id)); }
public IHttpActionResult GetStory(string id) { var db = new PetaPoco.Database("AGSoftware"); Entities.Storytime storytime = db.SingleOrDefault<Entities.Storytime>("Select * From StoryTime Where StorytimeId = @0", id); if (storytime != null) return Ok(storytime); else return NotFound(); }
public IEnumerable<Tuple<string, string>> GetPolygon(int segmentId) { var db = new Database(_config.ConnectionString, "System.Data.SqlClient"); var str = db.SingleOrDefault<string>("select Polylines.ToString() from MR.tSegment where SegmentId = @0 ", segmentId); return str.Substring("MULTIPOINT (".Length).Replace("(", "").Replace(")", "").Split(',') .Select(s => { var p = s.Trim().Split(' '); return Tuple.Create(p[1], p[0]); }); }
public FrmNewBloodCleanupProcess(Int64 cleanup_id) { InitializeComponent(); //string sPwd = Des.Decrypt(ClsFrmMng.KEY, ConfigurationManager.AppSettings["DbPwd"]); db = new Database("XE"); _cleanupID = cleanup_id; process.BLOODCLEANUP_ID = _cleanupID; process.OPERATOR = ClsFrmMng.WorkerID; bLOODCLEANUPPROCESSBindingSource.DataSource = process; BLOODCLEANUP_TEMP bt = db.SingleOrDefault<BLOODCLEANUP_TEMP>("where BLOOD_CLEANUP_ID = @0", _cleanupID); DEVICECOMMUNICATION_LOG log = db.SingleOrDefault<DEVICECOMMUNICATION_LOG>("where REMOTE_IP=@0 and rownum = 1 order by ID DESC", bt.SERIAL_PORT_NUM); if (log != null) { string sDataTmp = log.MSG.Substring(0, log.MSG.Length - 4); string sTmp = sDataTmp.Substring(log.MSG.LastIndexOf('F') + 1, 5); string sVp = sDataTmp.Substring(log.MSG.LastIndexOf('H') + 1, 5); string sBf = sDataTmp.Substring(log.MSG.LastIndexOf('D') + 1, 5); string sMaxBp = sDataTmp.Substring(log.MSG.LastIndexOf('N') + 1, 5); string sMinBp = sDataTmp.Substring(log.MSG.LastIndexOf('O') + 1, 5); string sPulse = sDataTmp.Substring(log.MSG.LastIndexOf('P') + 1, 5); string sTotalUFAmount = sDataTmp.Substring(log.MSG.LastIndexOf('B') + 1, 5); string sDC = sDataTmp.Substring(log.MSG.LastIndexOf('G') + 1, 5); string sTMP = sDataTmp.Substring(log.MSG.LastIndexOf('J') + 1, 5); process.ANA_TIME = log.RECEIVE_TIME; process.TEMP = decimal.Parse(sTmp); process.VENOUS_PRESSURE = decimal.Parse(sVp); process.BLOOD_FLOW = decimal.Parse(sBf); process.BP = decimal.Parse(sMaxBp).ToString() + "~" + decimal.Parse(sMinBp); process.P = decimal.Parse(sPulse); process.ULTRAFILTRATION = decimal.Parse(sTotalUFAmount); process.CONDUCTIVITY = sDC; process.ARTERIAL_PRESSURE = decimal.Parse(sTMP); } }
public static string CrearCiudad(Ciudad ciudad) { try { using (var db = new Database("riesgos")) { var cuenta = db.SingleOrDefault<int>("select count(id) from ciudad where nombre = @0", ciudad.Nombre); if (cuenta > 0) return "Ya existe una ciudad con el mismo nombre"; db.Save("ciudad", "id", ciudad); return ""; } } catch (Exception ex) { return "Error de ejecución: " + ex.Message; } }
public async Task <bool> Demo(string id) { var data = Db.SingleOrDefault <DatabaseInfo>($"select DemoStatus from DemoData where id = {id}"); await Task.Delay(10); if (data.DemoStatus == 0) { return(true); } else { return(false); } }
public IHttpActionResult GetStory(string id) { var db = new PetaPoco.Database("AGSoftware"); Entities.Storytime storytime = db.SingleOrDefault <Entities.Storytime>("Select * From StoryTime Where StorytimeId = @0", id); if (storytime != null) { return(Ok(storytime)); } else { return(NotFound()); } }
/// <summary> /// 获取用户全部信息 /// </summary> /// <param name="UserCode"></param> /// <param name="UserPass"></param> /// <returns></returns> public Models.User UserInfo(string UserCode, string UserPass) { Models.User MUser; using (SqlConnection sqlConnection = new SqlConnection(SystemSqlConn)) { sqlConnection.Open(); using (Database db = new PetaPoco.Database(sqlConnection)) { MUser = db.SingleOrDefault <Models.User>(@" SELECT * FROM Tb_User WHERE UserCode = @0 AND ISNULL(UserIsLocked,0) = 0 AND UserPass = @1", UserCode, UserPass); } return(MUser); } }
/// <summary> /// 每次进入窗体时,验证Token /// </summary> /// <param name="msg"></param> /// <returns></returns> public Token ValidateToken(string UserGuid, string ParamSqlConn) { Token token; using (SqlConnection sqlConnection = new SqlConnection(ParamSqlConn)) { sqlConnection.Open(); using (Database db = new PetaPoco.Database(sqlConnection)) { token = db.SingleOrDefault <Token>(@"SELECT TokenCode, TokenLastDate, TokenFalseDate, UserGuid FROM Tb_Token WHERE UserGuid = @0", UserGuid); } return(token); } }
public static BoolMessage PpRetrieveOne <T>(string tableName, string condition) { _checkVal.Data = null; try { if (string.IsNullOrEmpty(_cstring)) { throw new Exception("No connection string provided"); } _checkVal.Data = _pp.SingleOrDefault <T>(string.Format("SELECT * FROM [{0}] WHERE {1}", tableName, condition)); _checkVal.Success = true; _checkVal.Message = string.Format("Successfully returned one item from table {0}", tableName); } catch (Exception ex) { _checkVal.Data = null; _checkVal.Success = false; _checkVal.Message = ex.Message; } return(_checkVal); }
protected string DelUniqueId(string UniqueId) { try { // Create a PetaPoco database object var db = new PetaPoco.Database("Shell_AutoArchDBConnectionString"); // Get a record var a = db.SingleOrDefault <planning>("SELECT * FROM shell_Planning WHERE UniqueId=@0", UniqueId); //delete db.Delete(a); return("OK"); } catch (Exception eg) { divMessage.InnerHtml = "<span id='msg' style='color:#FF3300;font-size:Smaller;font-weight:bold;'>" + "Error Message: " + eg.Message + " </span>"; return("Error"); } }
public Guid AuthUser(string email, string passwordHash) { var db = new Database(_config.ConnectionString, "System.Data.SqlClient"); return db.SingleOrDefault<Guid>("select AuthId from MR.tUser where Email = @0 AND Passwd = @1", email, passwordHash); }
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)); sql.Append("ORDER BY NEWID()"); var a = db.SingleOrDefault<DataModel_a12SupauCheckin>(sql); result.Result = true; result.Msg = list_column + ",'" + a.sFBUID.ToString() + "'"; return result; } } else { sql = PetaPoco.Sql.Builder; sql.Append(String.Format("SELECT TOP {0} {1} FROM {2} WHERE 1=1", n, DistinctColumn, TableName)); sql.Append(BasicCondition); sql.Append(String.Format("GROUP BY [{0}] ORDER BY NEWID()", DistinctColumn)); var a = db.Query<DataModel_a12SupauCheckin>(sql); string return_data = ""; foreach (var item in a) { return_data += ",'" + item.sFBUID + "'"; } return_data = return_data.Substring(1, return_data.Length - 1); result.Result = true; result.Msg = return_data; return result; } }
public Guid GetAuthId(string email) { var db = new Database(_config.ConnectionString, "System.Data.SqlClient"); return db.SingleOrDefault<Guid>("select AuthId from MR.tUser where Email = @0", email); }
public User GetUser(string email) { var db = new Database(_config.ConnectionString, "System.Data.SqlClient"); return db.SingleOrDefault<User>("select * from MR.tUser where Email = @0", email); }
protected void SaveNewUpd(bool flgChkAuto) { bool flgNew = false; bool flgValid = false; try { //Validate first... flgValid = validateFields(); // if (!flgValid) { return; } // Create a PetaPoco database object var db = new PetaPoco.Database("Shell_AutoArchDBConnectionString"); // Get a record var a = db.SingleOrDefault <planning>("SELECT * FROM shell_Planning WHERE UniqueId=@0", txt_uniqueid.Text); //Test if record exists... if (null == a) { a = new planning(); flgNew = true; } //if update, check if status is not equal to CVR... or Closed... else if (flgChkAuto) { if (txt_status.Text.IndexOf("Archival Order Closed") == -1) { if ((a.Status.IndexOf("Rejected") >= 0) || (a.Status.IndexOf("CVR Created") >= 0)) //Rejected { ClrFields(false); return; } } } // Change it // if (txt_actaoenddate.Text != a.ActAOEndDate.ToString("yyyy-MM-dd HH:mm:ss.ttt")) { a.ActAOEndDate = DateTime.Parse(txt_actaoenddate.Text); } // if (txt_actaoreceivedate.Text != a.ActAOReceiveDate.ToString("yyyy-MM-dd HH:mm:ss.ttt")) { a.ActAOReceiveDate = DateTime.Parse(txt_actaoreceivedate.Text); } // if (txt_actaostartdate.Text != a.ActAOStartDate.ToString("yyyy-MM-dd HH:mm:ss.ttt")) { a.ActAOStartDate = DateTime.Parse(txt_actaostartdate.Text); } // if (txt_colsitenum.Text != "") { if (Convert.ToInt16(txt_colsitenum.Text) != a.ColSiteNum) { a.ColSiteNum = Convert.ToInt16(txt_colsitenum.Text); } } // if (txt_comment.Text != a.Comment) { a.Comment = txt_comment.Text; } // if (chk_dataexpctrchk.Checked) { a.DataExpCtrChk = 1; } else { a.DataExpCtrChk = 0; } // if (txt_duname.Text != a.DUName) { a.DUName = txt_duname.Text; } // // if ((txt_actaostartdate.Text != "") && (txt_actaoenddate.Text != "")) { DateTime dtf = DateTime.Parse(txt_actaostartdate.Text); DateTime dtt = DateTime.Parse(txt_actaoenddate.Text); a.DurationWorkDays = GetWorkingDays(dtf, dtt); } else if (Convert.ToInt16(txt_durationworkdays.Text) != a.DurationWorkDays) { a.DurationWorkDays = Convert.ToInt16(txt_durationworkdays.Text); } // // if (txt_operatorname.Text != a.OperatorName) { a.OperatorName = txt_operatorname.Text; } // if (txt_planaoenddate.Text != a.PlanAOEndDate.ToString("yyyy-MM-dd HH:mm:ss.ttt")) { a.PlanAOEndDate = DateTime.Parse(txt_planaoenddate.Text); } // if (txt_planaoreceivedate.Text != a.PlanAOReceiveDate.ToString("yyyy-MM-dd HH:mm:ss.ttt")) { a.PlanAOReceiveDate = DateTime.Parse(txt_planaoreceivedate.Text); } // if (txt_planaostartdate.Text != a.PlanAOStartDate.ToString("yyyy-MM-dd HH:mm:ss.ttt")) { a.PlanAOStartDate = DateTime.Parse(txt_planaostartdate.Text); } // if (txt_status.Text != a.Status) { a.Status = txt_status.Text; } // if (txt_instanceid.Text != a.InstanceId) { a.InstanceId = txt_instanceid.Text; } // if ((float)Convert.ToDouble(txt_volumegb.Text) != a.VolumeGB) { a.VolumeGB = (float)Convert.ToDouble(txt_volumegb.Text); } // if (chk_waiverchk.Checked) { a.WaiverChk = 1; } else { a.WaiverChk = 0; } // string msg = "Archiving Order updated! - "; if (flgNew) { a.UniqueId = txt_uniqueid.Text; msg = "New Archiving Order inserted! - "; db.Save(a); } else { db.Update(a); } if (flgChkAuto) { divMessage.InnerHtml += "<span id='msg' style='color:#FF3300;font-size:Smaller;font-weight:bold;'>" + msg + txt_uniqueid.Text + " </span><br>"; } else { divMessage.InnerHtml = "<span id='msg' style='color:#FF3300;font-size:Smaller;font-weight:bold;'>" + msg + txt_uniqueid.Text + " </span>"; } } catch (Exception eg) { if (flgChkAuto) { divMessage.InnerHtml += "<span id='msg' style='color:#FF3300;font-size:Smaller;font-weight:bold;'>" + "Error Message: " + eg.Message + " </span>"; } else { divMessage.InnerHtml = "<span id='msg' style='color:#FF3300;font-size:Smaller;font-weight:bold;'>" + "Error Message: " + eg.Message + " </span>"; } } }
public FrmNewBloodCleanBase(Int64 base_id, Int64 reg_id, Int64 machineCheckID, decimal pt_path_type) { InitializeComponent(); //string sPwd = Des.Decrypt(ClsFrmMng.KEY, ConfigurationManager.AppSettings["DbPwd"]); db = new Database("XE"); _regID = reg_id; _baseID = base_id; blookCaeanup.REG_ID = _regID; blookCaeanup.BASE_INFO_ID = _baseID; blookCaeanup.OPERATOR = ClsFrmMng.WorkerID; blookCaeanup.ANA_DATE = DateTime.Now; // 查询该患者所签到的透析机机位, 透析机型号 ms = db.SingleOrDefault<MACHINE_SCHEDULE>(machineCheckID); string sFloor = db.ExecuteScalar<string>("select DSP_MEMBER from VALUE_CODE where VALUE_MEMBER = @0", ms.FLOOR_ID); string sArea = db.ExecuteScalar<string>("select DSP_MEMBER from VALUE_CODE where VALUE_MEMBER = @0", ms.AREA_ID); blookCaeanup.MACH_POS = sFloor + " " + sArea + " " + ms.BED_NO + "#"; MACHINE_INFO info = db.SingleOrDefault<MACHINE_INFO>(ms.MACHINE_INFO_ID); if (info != null && info.MODEL != null) blookCaeanup.MACH_TYP = info.MODEL.ToString(); // 血管通路类型 blookCaeanup.FISTULA_TYPE = pt_path_type; if (pt_path_type == 519 || pt_path_type == 520) { ItemForAPPLICATOR.Visibility = DevExpress.XtraLayout.Utils.LayoutVisibility.Never; ItemForAPPLICATOR_NUM.Visibility = DevExpress.XtraLayout.Utils.LayoutVisibility.Never; ItemForHEPARIN_CAP.Visibility = DevExpress.XtraLayout.Utils.LayoutVisibility.Never; ItemForHEPARIN_CAP_NUM.Visibility = DevExpress.XtraLayout.Utils.LayoutVisibility.Never; // 获取该患者最近一次, 当通路类型与目前匹配时使用的耗材型号与数据 BLOODCLEANUP bc1 = db.SingleOrDefault<BLOODCLEANUP>("where FISTULA_TYPE = @0 and BASE_INFO_ID = @1 AND rownum = 1 order by ID DESC", new object[] { pt_path_type, base_id }); if (bc1 != null) { blookCaeanup.FISTULA_NEEDLE = bc1.FISTULA_NEEDLE; blookCaeanup.FISTULA_NEEDLE_NUM = bc1.FISTULA_NEEDLE_NUM; blookCaeanup.FISTULA_CARE_PACKAGES = bc1.FISTULA_CARE_PACKAGES; blookCaeanup.FISTULA_CARE_PACKAGES_NUM = bc1.FISTULA_CARE_PACKAGES_NUM; } } else if (pt_path_type == 704 || pt_path_type == 705) { ItemForFISTULA_NEEDLE.Visibility = DevExpress.XtraLayout.Utils.LayoutVisibility.Never; ItemForFISTULA_NEEDLE_NUM.Visibility = DevExpress.XtraLayout.Utils.LayoutVisibility.Never; ItemForFISTULA_CARE_PACKAGES.Visibility = DevExpress.XtraLayout.Utils.LayoutVisibility.Never; ItemForFISTULA_CARE_PACKAGES_NUM.Visibility = DevExpress.XtraLayout.Utils.LayoutVisibility.Never; // 获取该患者最近一次, 当通路类型与目前匹配时使用的耗材型号与数据 BLOODCLEANUP bc1 = db.SingleOrDefault<BLOODCLEANUP>("where FISTULA_TYPE = @0 and BASE_INFO_ID = @1 AND rownum = 1 order by ID DESC", new object[] { pt_path_type, base_id }); if (bc1 != null) { blookCaeanup.APPLICATOR = bc1.APPLICATOR; blookCaeanup.APPLICATOR_NUM = bc1.APPLICATOR_NUM; blookCaeanup.HEPARIN_CAP = bc1.HEPARIN_CAP; blookCaeanup.HEPARIN_CAP_NUM = bc1.HEPARIN_CAP_NUM; } } // 查询该患者上一次血液净信息 BLOODCLEANUP bc = db.SingleOrDefault<BLOODCLEANUP>("where BASE_INFO_ID = @0 AND rownum = 1 order by ID DESC", base_id); if (bc != null) { blookCaeanup.WEIGHT = bc.WEIGHT; blookCaeanup.MACH = bc.MACH; // 透析器 blookCaeanup.PIPELINE = bc.PIPELINE; // 管路 } // 查询该患者医嘱(长期及临时医嘱)中的药品 List<DOC_ADVICE> lstAdv = db.Fetch<DOC_ADVICE>("where BASE_INFO_ID = @0 and (ADVICE_TYPE = 0 or ADVICE_TYPE = 9) AND IS_DEL = 0 order by log_time desc", base_id); string sDose = string.Empty; for (int i = 0; i < lstAdv.Count; i++) { sDose += db.ExecuteScalar<string>("select DSP_MEMBER from VALUE_CODE where VALUE_MEMBER = @0", lstAdv[i].M_NAME) +", "; } lstAdv = db.Fetch<DOC_ADVICE>("where reg_id = @0 and ADVICE_TYPE = 1 AND IS_DEL = 0 order by log_time desc", reg_id); for (int i = 0; i < lstAdv.Count; i++) { sDose += db.ExecuteScalar<string>("select DSP_MEMBER from VALUE_CODE where VALUE_MEMBER = @0", lstAdv[i].M_NAME) + ", "; } sDose = sDose.TrimEnd(new char[] { ',', ' ' }); blookCaeanup.EPO = sDose; bLOODCLEANUPBindingSource.DataSource = blookCaeanup; //EPOTextEdit.Properties.TextEditStyle = DevExpress.XtraEditors.Controls.TextEditStyles.Standard; EPOTextEdit.Properties.DataSource = db.Fetch<VALUE_CODE>(""); EPOTextEdit.Properties.DisplayMember = "DSP_MEMBER"; EPOTextEdit.Properties.ValueMember = "VALUE_MEMBER"; MACH_TYPTextEdit.Properties.DataSource = db.Fetch<VALUE_CODE>("where GROUPNAME = @0 or GROUPNAME = @1", new object[] { 3, 162 }); MACH_TYPTextEdit.Properties.DisplayMember = "DSP_MEMBER"; MACH_TYPTextEdit.Properties.ValueMember = "VALUE_MEMBER"; vALUECODEBindingSource.DataSource = db.Fetch<VALUE_CODE>("where GROUPNAME = @0", 101); vALUECODEBindingSource1.DataSource = db.Fetch<VALUE_CODE>("where GROUPNAME = @0", 44); vALUECODEBindingSource2.DataSource = db.Fetch<VALUE_CODE>("where GROUPNAME = @0", 102); vALUECODEBindingSource3.DataSource = db.Fetch<VALUE_CODE>("where GROUPNAME = @0", 161); aCCOUNTBindingSource.DataSource = db.Fetch<ACCOUNT>(""); bindingSource1.DataSource = db.Fetch<VALUE_CODE>("where GROUPNAME = @0", 193); // 通路类型 bindingSource2.DataSource = db.Fetch<VALUE_CODE>("where GROUPNAME = @0", 47); // 管路 bindingSource3.DataSource = db.Fetch<VALUE_CODE>("where GROUPNAME = @0", 43); // 穿刺针 bindingSource4.DataSource = db.Fetch<VALUE_CODE>("where GROUPNAME = @0", 171); // 敷贴 bindingSource5.DataSource = db.Fetch<VALUE_CODE>("where GROUPNAME = @0", 166); // 护理包 bindingSource6.DataSource = db.Fetch<VALUE_CODE>("where GROUPNAME = @0", 169); // 肝素帽 }
//public static string GetPhrase(string language, string phrase) public string GetPhrase(string language, string phrase) { // TODO: Fetch the translatedtext for the specified term with the specified language string[] param = {phrase, language}; Database db = new Database("Translation"); Translation t = new Translation(); t=db.SingleOrDefault<Translation>("select * from Translation where Phrase=@0 and LanguageCode=@1", param); return t.TranslatedText; //throw new NotImplementedException(); }
public static sanpham ChiTietSanPham(string id) { var db = new PetaPoco.Database("DienThoaiShopConnection"); return(db.SingleOrDefault <sanpham>("Select * from sanpham WHERE MaSanPham=@0", id)); }
public User GetUserByApiKey(string apiKey) { var db = new Database(_config.ConnectionString, "System.Data.SqlClient"); return db.SingleOrDefault<User>("select * from MR.tUser where ApiKey = @0", apiKey); }
static void Main(string[] args) { //var table = new PowerPlants(); //var plants = table.All(); var point = SqlGeography.Point(47.8315, -121.626, 4326); //var param = new SqlParameter("@point", point); //param.UdtTypeName = "geography"; //param.SqlDbType = SqlDbType.Udt; ////var plantsNearBy = table.All(where: "WHERE geom.STDistance(geom.STDistance(@point) < 25", args: param); //using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["Spatial"].ConnectionString)) //using (SqlCommand cmd = new SqlCommand("Select * from PowerPlants where geom.STDistance(@point) < 25 * 1609.344", conn)) //{ // SqlParameter p = cmd.Parameters.Add("@point", sqlDbType: SqlDbType.Udt); // p.UdtTypeName = "geography"; // SqlGeography geog = SqlGeography.Point(47.8315, -121.626, 4326); // p.Value = geog; // conn.Open(); // using (var reader = cmd.ExecuteReader(CommandBehavior.CloseConnection)) // { // var result = reader.ToExpandoList(); // } //} var db = new PetaPoco.Database("Spatial"); long count = db.ExecuteScalar<long>("SELECT Count(*) FROM PowerPlants"); var a = db.SingleOrDefault<PowerPlant>("WHERE PLANT_ID = @0", 72712); var b = db.Fetch<PowerPlant>(@"SELECT * FROM PowerPlants where geom.STDistance(@0) < 25 * 1609.344", point); var x = db.Fetch<PowerPlant>(PetaPoco.Sql.Builder.Append("WHERE geom.STDistance(@point) < @miles * 1609.344", new { point = point, miles = 50 })); }
private void backgroundWorker1_DoWork(object sender, DoWorkEventArgs e) { Int64 iTicks = 1; byte[] by = new byte[] { 0x4b, 0x0d, 0x0a }; // 打开所有串口 foreach (KeyValuePair<string, SerialPortManager> item in dicSerialPort) { try { item.Value.StartListening(item.Key, 9600, System.IO.Ports.Parity.None, 8, System.IO.Ports.StopBits.One); } catch (Exception err) { tbData.SafeCall(delegate() { tbData.AppendText("初始化串口" + item.Key + "发生异常: " + err.Message + Environment.NewLine); }); } } while (isRun) { foreach (KeyValuePair<string, SerialPortManager> item in dicSerialPort) { try { item.Value.SendMsg(by); } catch (Exception err) { tbData.SafeCall(delegate() { tbData.AppendText("向串口" + item.Key + "发送数据失败: " + err.Message); }); } } tbData.SafeCall(delegate() { tbData.Clear(); }); foreach (KeyValuePair<string, SerialPortManager> item in dicSerialPort) { try { string sData = item.Value.ReadData(); tbData.SafeCall(delegate() { tbData.AppendText(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss ") + item.Key + ": " + sData + Environment.NewLine); }); if (sData.Length > 20) { try { string sDataTmp = sData.Substring(0, sData.Length - 4); // 写入数据库 PetaPoco.Database db = new Database("XE"); DEVICECOMMUNICATION_LOG log = new DEVICECOMMUNICATION_LOG(); log.MSG = sData.Length > 3999 ? sData.Substring(0, 3999) : sData; log.REMOTE_IP = toolStripTextBox1.Text + item.Key; //log.REMOTE_PORT = remotePort; log.RECEIVE_TIME = DateTime.Now; db.Insert(log); // 写入到台湾人要的表 var log1 = GetLog1Entity(sDataTmp, item.Key); db.Insert(log1); if (iTicks % 60 == 0) { BLOODCLEANUP_TEMP bt = new BLOODCLEANUP_TEMP(); bt = db.SingleOrDefault<BLOODCLEANUP_TEMP>("where to_char( ANA_DATE, 'yyyy-mm-dd') = @0 and SERIAL_PORT_NUM = @1", new object[] { DateTime.Now.ToString("yyyy-MM-dd"), toolStripTextBox1.Text + item.Key }); if (bt != null) { string sTmp = sDataTmp.Substring(log.MSG.LastIndexOf('F') + 1, 5); string sVp = sDataTmp.Substring(log.MSG.LastIndexOf('H') + 1, 5); string sBf = sDataTmp.Substring(log.MSG.LastIndexOf('D') + 1, 5); string sMaxBp = sDataTmp.Substring(log.MSG.LastIndexOf('N') + 1, 5); string sMinBp = sDataTmp.Substring(log.MSG.LastIndexOf('O') + 1, 5); string sPulse = sDataTmp.Substring(log.MSG.LastIndexOf('P') + 1, 5); string sTotalUFAmount = sDataTmp.Substring(log.MSG.LastIndexOf('B') + 1, 5); string sDC = sDataTmp.Substring(log.MSG.LastIndexOf('G') + 1, 5); string sTMP = sDataTmp.Substring(log.MSG.LastIndexOf('J') + 1, 5); BLOODCLEANUP_PROCESS proc = new BLOODCLEANUP_PROCESS(); proc.ANA_TIME = log.RECEIVE_TIME; proc.TEMP = decimal.Parse(sTmp); proc.VENOUS_PRESSURE = decimal.Parse(sVp); proc.BLOOD_FLOW = decimal.Parse(sBf); proc.BP = decimal.Parse(sMaxBp).ToString() + "~" + decimal.Parse(sMinBp); proc.P = decimal.Parse(sPulse); proc.ULTRAFILTRATION = decimal.Parse(sTotalUFAmount); proc.CONDUCTIVITY = sDC; proc.ARTERIAL_PRESSURE = decimal.Parse(sTMP); proc.LOG_TIME = DateTime.Now; proc.BLOODCLEANUP_ID = bt.BLOOD_CLEANUP_ID; db.Insert(proc); //proc.OPERATOR = ClsFrmMng.WorkerID; } } } catch (Exception err) { tbData.SafeCall(delegate() { tbData.AppendText("写数据失败: " + err.Message + Environment.NewLine); }); } } } catch (Exception err) { tbData.SafeCall(delegate() { tbData.AppendText("读取串口" + item.Key + "数据失败: " + err.Message + Environment.NewLine); }); } } Thread.Sleep(3000); iTicks++; } }
public static T GetById(int id) { using (var connection = new PetaPoco.Database("myConnectionString")) //connection.Delete<T>(id); return(connection.SingleOrDefault <T>(id)); }
/// <summary> /// Gets the user by their ID /// </summary> public RegisteredUser GetById(int id) { return(_database.SingleOrDefault <RegisteredUser>("where userId=@0", id)); }
private static void TransactionTest(Database db) { // Transaktio using (var tx = new TransactionScope()) { // Lähderivi var source = db.SingleOrDefault<Customer>("SELECT * FROM Customer WITH(NOLOCK) WHERE CustId = 287350"); // Kohderivi var target = db.SingleOrDefault<Customer>("SELECT * FROM Customer WITH(NOLOCK) WHERE CustId = 287348"); target.CustName = source.CustName; // Vars. muutos: "taulu", "avain", data db.Update("customer", "custid", target); // Luetaan testimielessä likainen rivi var dirty = db.SingleOrDefault<Customer>("SELECT * FROM Customer WITH(NOLOCK) WHERE CustId = 287348"); Console.WriteLine("dirty: {0} - {1}", dirty.CustId, dirty.CustName); Console.WriteLine(); } // Ei committia, eli ei tallentunut var rolledback = db.SingleOrDefault<Customer>("SELECT * FROM Customer WITH(NOLOCK) WHERE CustId = 287348"); Console.WriteLine("eiku: {0} - {1}", rolledback.CustId, rolledback.CustName); Console.WriteLine(); }
private static void addTagToTopic(int topicId, Tag tag) { var db = new Database("umbracoDbDSN"); if (tag.Id == 0) { db.Insert(tag); } var topicTag = db.SingleOrDefault<TopicTag>("WHERE tagId = @0 and topicId = @1", tag.Id, topicId); if (topicTag == null) { topicTag = new TopicTag(tag.Id, topicId, tag.Weight); db.Insert(topicTag); } }
/// <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)); sql.Append("ORDER BY NEWID()"); var a = db.SingleOrDefault <DataModel_a12SupauCheckin>(sql); result.Result = true; result.Msg = list_column + ",'" + a.sFBUID.ToString() + "'"; return(result); } } else { sql = PetaPoco.Sql.Builder; sql.Append(String.Format("SELECT TOP {0} {1} FROM {2} WHERE 1=1", n, DistinctColumn, TableName)); sql.Append(BasicCondition); sql.Append(String.Format("GROUP BY [{0}] ORDER BY NEWID()", DistinctColumn)); var a = db.Query <DataModel_a12SupauCheckin>(sql); string return_data = ""; foreach (var item in a) { return_data += ",'" + item.sFBUID + "'"; } return_data = return_data.Substring(1, return_data.Length - 1); result.Result = true; result.Msg = return_data; return(result); } }
public User GetUser(Guid authId) { var db = new Database(_config.ConnectionString, "System.Data.SqlClient"); return db.SingleOrDefault<User>("select * from MR.tUser where AuthId = @0", authId); }
public RetreaveIndex GetById(int id) { var result = _database.SingleOrDefault <RetreaveIndex>("where indexId=@0", id); return(FillResult(result)); }
private static void UspQuery(Database db) { // Kysely uspilla var customerId = 287350; var viaUsp = db.SingleOrDefault<Customer>("execute [usp_MLYLdaGetCustomer] @0", customerId); Console.WriteLine("{0} - {1}", viaUsp.CustId, viaUsp.CustName); Console.WriteLine(); }
public static Topic GetTopic(int topicId) { var db = new Database("umbracoDbDSN"); var topic = db.SingleOrDefault<Topic>("SELECT * FROM forumTopics WHERE id = @0", topicId); return topic; }
public static Topic GetTopic(int topicId, bool getSpamTopic) { var db = new Database("umbracoDbDSN"); var topic = db.SingleOrDefault<Topic>(string.Format("SELECT * FROM forumTopics WHERE {0} id = @0", getSpamTopic ? "" : " (forumTopics.isSpam IS NULL OR forumTopics.isSpam != 1) AND "), topicId); return topic; }