C# Database.SingleOrDefault方法代码示例

 public static void Save(Database db, Purchase purchase)
 {
     var existing = db.SingleOrDefault<Purchase>("SELECT * FROM purchases WHERE [email protected] ", 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 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 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 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 [email protected] 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;
            }
        }

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

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

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

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

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