public Response Count(dynamic parameters) { using (var db = new PetaPoco.Database("SWATDB")) { var count = db.ExecuteScalar <long>("select count(distinct _id) from [user]"); return(Helper.ResponseHelper(ResponseContent: count)); } }
private void btnOK_Click(object sender, EventArgs e) { if (!dxValidationProvider1.Validate()) return; if (spnOrder.EditValue != null) { using (var db = new Database(this.ConnString)) { var count = db.ExecuteScalar<int>(" select count(1) from T_ERP_Procedure where OrderNo = @0 and Guid <> @1 ", Convert.ToInt32(spnOrder.EditValue), Entity==null?Guid.Empty:Entity.Guid); if (count > 0) { MsgBox.ShowMessage("当前序号已经存在"); return; } } } if (EditMode == Business.Core.Define.EnViewEditMode.New) { var r = new UltraDbEntity.T_ERP_Procedure { Guid = Guid.NewGuid(), Creator = CurUser, Updator = CurUser, Remark = string.Empty, Reserved1 = 0, Reserved2 = string.Empty, Reserved3 = false, ProcedureName = txtName.Text, LabourCost = 0, OrderNo = Convert.ToInt32(spnOrder.EditValue), BatchNo = Convert.ToInt32(spnBatch.EditValue), IsUsing = chkusing.Checked }; using (var db = new Database(this.ConnString)) { db.Insert(r); Entity = r; DialogResult = System.Windows.Forms.DialogResult.OK; Close(); } } else if (EditMode == Business.Core.Define.EnViewEditMode.Edit) { Entity.IsUsing = chkusing.Checked; Entity.Updator = CurUser; Entity.ProcedureName = txtName.Text; Entity.LabourCost = 0; Entity.OrderNo = Convert.ToInt32(spnOrder.EditValue); Entity.BatchNo = Convert.ToInt32(spnBatch.EditValue); using (var db = new Database(this.ConnString)) { db.Update(Entity); DialogResult = System.Windows.Forms.DialogResult.OK; Close(); } } }
static void Main(string[] args) { Application.EnableVisualStyles(); Application.SetCompatibleTextRenderingDefault(false); Application.SetUnhandledExceptionMode(UnhandledExceptionMode.CatchException); Application.ThreadException += new ThreadExceptionEventHandler(Application_ThreadException); AppDomain.CurrentDomain.UnhandledException += new UnhandledExceptionEventHandler(CurrentDomain_UnhandledException); Application.ApplicationExit += Application_ApplicationExit; AppLog = new Ultra.Log.ApplicationLog(); __start: string con = string.Empty; BaseSurface vw; vw = Lanucher.Start("Ultra.Login.LoginView");//.ShowDialog(); con = Lanucher.ConnectonString; if (!CanConnDb(con)) { MsgBox.ShowMessage("无法连接至服务器", "无法连接至服务器!"); } using (var db = new Database()) { var dte = db.ExecuteScalar<DateTime>("select getdate()"); TimeSync.Default.StartSync(dte); } var dt = vw.ShowDialog(); if (dt == DialogResult.OK)//登录成功 { if (null == args || args.Length < 1) vw = Lanucher.Start("Ultra.Login.MainView"); else vw = Lanucher.Start(args[0]); } else if (dt == DialogResult.Cancel)//退出 { return; } dt = vw.ShowDialog(); if (dt == DialogResult.No) { Lanucher.Clean("OfficeSkins.Register()"); goto __start; } }
public static BoolMessage PpGetScalar <T>(string query) { _checkVal.Data = null; try { if (string.IsNullOrEmpty(_cstring)) { throw new Exception("No connection string provided"); } _checkVal.Data = _pp.ExecuteScalar <T>(query); _checkVal.Success = true; _checkVal.Message = string.Format("Successfully retrieved scalar"); } catch (Exception ex) { _checkVal.Data = null; _checkVal.Success = false; _checkVal.Message = ex.Message; } return(_checkVal); }
public int CreatePlt(int userId, string name, string line) { var db = new Database(_config.ConnectionString, "System.Data.SqlClient"); return db.ExecuteScalar<int>("insert into [MR].[tTrack] ([UserId],[Name], LineString) VALUES(@0, @1, @2); select SCOPE_IDENTITY();", userId, name, line); }
public bool ApplyRule(Multimedia multimedia, out string ruleMessage) { ruleMessage = "ProductNotExists"; Database db = new Database("Database"); return db.ExecuteScalar<int?>("SELECT TOP 1 ID FROM Product WHERE code = @0", multimedia.Name.Substring(0, 14).Replace("_", "/")) != null; }
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 })); }
/// <summary> /// 隐式转换 /// </summary> /// <param name="pageSize"></param> /// <param name="pageIndex"></param> /// <param name="connName"></param> /// <returns></returns> public static PetaPoco.Page<dynamic> ImplicitConvert(int pageSize, int pageIndex, string connName) { PetaPoco.Sql sbRs = new PetaPoco.Sql(); StringBuilder sb = new StringBuilder(); sb.Append("SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ;WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan') select * from ( SELECT row_number()over(order by id )as RowNum, * from ( select NEWID() as id, stmt.value('(@@StatementText)[1]', 'varchar(max)') as sqltxt, t.value('(ScalarOperator/Identifier/ColumnReference/@@Schema)[1]', 'varchar(128)') as schemaname, t.value('(ScalarOperator/Identifier/ColumnReference/@@Table)[1]', 'varchar(128)') as tablename, t.value('(ScalarOperator/Identifier/ColumnReference/@@Column)[1]', 'varchar(128)') as columnname, ic.DATA_TYPE AS ConvertFrom, ic.CHARACTER_MAXIMUM_LENGTH AS ConvertFromLength, t.value('(@@DataType)[1]', 'varchar(128)') AS ConvertTo, t.value('(@@Length)[1]', 'int') AS ConvertToLength FROM sys.dm_exec_cached_plans AS cp CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qp CROSS APPLY query_plan.nodes('/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple') AS batch(stmt) CROSS APPLY stmt.nodes('.//Convert[@@Implicit=\"1\"]') AS n(t) JOIN INFORMATION_SCHEMA.COLUMNS AS ic ON QUOTENAME(ic.TABLE_SCHEMA) = t.value('(ScalarOperator/Identifier/ColumnReference/@@Schema)[1]', 'varchar(128)') AND QUOTENAME(ic.TABLE_NAME) = t.value('(ScalarOperator/Identifier/ColumnReference/@@Table)[1]', 'varchar(128)') AND ic.COLUMN_NAME = t.value('(ScalarOperator/Identifier/ColumnReference/@@Column)[1]', 'varchar(128)') ) a) b where rownum between " + ((pageIndex - 1) * pageSize + 1).ToString() + " and " + (pageIndex * pageSize).ToString()); PetaPoco.Page<dynamic> result = new Page<dynamic>(); var db = new PetaPoco.Database(connName); try { result.CurrentPage = pageIndex; result.ItemsPerPage = pageSize; sbRs.Append(sb.ToString()); result.Items = db.Query<dynamic>(sbRs).ToList(); PetaPoco.Sql sbCount = new PetaPoco.Sql(); sb.Length = 0; sb.Append( "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ;WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan') select count(1) FROM sys.dm_exec_cached_plans AS cp CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qp CROSS APPLY query_plan.nodes('/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple') AS batch(stmt) CROSS APPLY stmt.nodes('.//Convert[@@Implicit=\"1\"]') AS n(t) JOIN INFORMATION_SCHEMA.COLUMNS AS ic ON QUOTENAME(ic.TABLE_SCHEMA) = t.value('(ScalarOperator/Identifier/ColumnReference/@@Schema)[1]', 'varchar(128)') AND QUOTENAME(ic.TABLE_NAME) = t.value('(ScalarOperator/Identifier/ColumnReference/@@Table)[1]', 'varchar(128)') AND ic.COLUMN_NAME = t.value('(ScalarOperator/Identifier/ColumnReference/@@Column)[1]', 'varchar(128)') "); sbCount.Append(sb.ToString()); result.TotalItems = db.ExecuteScalar<int>(sbCount); // result = db.Page<dynamic>(pageIndex, pageSize, sb.ToString()); } catch (Exception ex) { var mm = ex.Message; } return result; }
private void populateVersionNumber() { Database db = new Database("Database"); int versionCount = db.ExecuteScalar<int>("SELECT (SELECT COUNT(ID) FROM Multimedia WHERE name = @0)", this.Name); this.Version = (versionCount != 0) ? versionCount : 0; }
private void populateProductID() { Database db = new Database("Database"); int? productID = db.ExecuteScalar<int?>("SELECT TOP 1 ID FROM Product WHERE code = @0", this.Name.Substring(0, 14).Replace("_", "/")); this.ProductID = productID; }
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 IEnumerable<Point> RoutesPoints(int routeId) { var db = new Database(_config.ConnectionString, "System.Data.SqlClient"); var routeline = db.ExecuteScalar<string>(@"select r.LineString from MR.vRoute r where r.RouteId = @0", routeId); foreach (var s in routeline.Split(new [] {','}, StringSplitOptions.RemoveEmptyEntries)) { var parts = s.Split(new[] {' '}, StringSplitOptions.RemoveEmptyEntries); yield return new Point() { Lat = double.Parse(parts[1], CultureInfo.InvariantCulture), Lon = double.Parse(parts[0], CultureInfo.InvariantCulture) }; } }