Esempio n. 1
0
        /// <summary>
        /// 获取待发送的短信列表
        /// </summary>
        /// <param name="topCnts">获取记录数</param>
        /// <returns></returns>
        public List<SMSEntity> GetWaitSendSMSList(int topCnts)
        {
            List<SMSEntity> result = new List<SMSEntity>();

            string sql = @"DECLARE @TblSysNoList TABLE (GetSysNo INT);
            INSERT INTO @TblSysNoList
            SELECT TOP " + topCnts.ToString() + @" [SysNo]
            FROM [SohoEmailAndSMS].[dbo].[SMS](NOLOCK)
            WHERE [Status] = 200 OR ([Status] = 300 AND [SendTime] < GETDATE())

            UPDATE [SohoEmailAndSMS].[dbo].[SMS] SET Status = 301
            WHERE [SysNo] IN (SELECT GetSysNo FROM @TblSysNoList)

            SELECT [SysNo]
              ,[UserSysNo]
              ,[ReceiveName]
              ,[ReceivePhoneNumber]
              ,[SMSBody]
              ,[Status]
              ,[SendTime]
              ,[InDate]
              ,[LastUpdateTime]
              ,[Note]
              FROM [SohoEmailAndSMS].[dbo].[SMS](NOLOCK)
              WHERE [SysNo] IN (SELECT GetSysNo FROM @TblSysNoList)";
            SqlServerDBHelper db = new SqlServerDBHelper();
            try
            {
                DataTable dt = db.ExeSqlDataAdapter(CommandType.Text, sql);
                if (dt != null && dt.Rows.Count > 0)
                {
                    foreach (DataRow row in dt.Rows)
                    {
                        result.Add(new SMSEntity()
                        {
                            SysNo = int.Parse(row["SysNo"].ToString()),
                            UserSysNo = string.IsNullOrWhiteSpace(row["UserSysNo"].ToString()) ? 0 : int.Parse(row["UserSysNo"].ToString()),
                            ReceiveName = row["ReceiveName"].ToString(),
                            ReceivePhoneNumber = row["ReceivePhoneNumber"].ToString(),
                            SMSBody = row["SMSBody"].ToString(),
                            Status = (SMSStatus)int.Parse(row["Status"].ToString()),
                            SendTime = row["SendTime"].ToString(),
                            InDate = row["InDate"].ToString(),
                            LastUpdateTime = row["LastUpdateTime"].ToString(),
                            Note = row["Note"].ToString(),
                        });
                    }
                }
            }
            catch (Exception ex)
            {
                Logger.WriteException(ex.ToString());
            }
            finally
            {
                db.Dispose();
            }

            return result;
        }
Esempio n. 2
0
 /// <summary>
 /// 加载配置
 /// </summary>
 /// <returns></returns>
 public DataTable LoadConfig()
 {
     DataTable dt = null;
     string sql = @"SELECT SysNo, Category, ConfigKey, ConfigValue
                         FROM [SohoEmailAndSMS].[dbo].[Configs](NOLOCK)
                         WHERE [Category] = N'Email'";
     SqlServerDBHelper db = new SqlServerDBHelper();
     try
     {
         dt = db.ExeSqlDataAdapter(CommandType.Text, sql);
     }
     catch(Exception ex)
     {
         Logger.WriteException(ex.ToString());
     }
     finally
     {
         db.Dispose();
     }
     return dt;
 }