C# Database.ExecuteScalar方法代码示例

        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 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);
//.........这里部分代码省略.........

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