dboperator.cs.svn-base

sms短信发送方面的东东。仅供参考使用。有问题可以和我联系

using System;
using System.Collections;
using System.Data.SqlClient;

namespace PlaneSale
{
    class DBOperator
    {
        Functions func = new Functions();
        SqlCommand cmd = null;
        SqlDataReader reader = null;
        
        /// <summary>
        /// 获取短信代码对应的当前最高价
        /// </summary>
        /// <param name="code"></param>
        /// <returns></returns>
        public string GetHighPriceByCode(string code)
        {
            string highPrice = null;
            try
            {
                string sql = "SELECT TOP 1 * FROM SMS_TICKETSALE WHERE SMSCode = '" + code + "' AND LoopState = 1 AND LoopNum <= " + Convert.ToInt32(func.GetMessageByCode("TicketNum", code)) + " ORDER BY RecTime DESC";
                cmd = new SqlCommand(sql,Global.conn);
                reader = cmd.ExecuteReader();
                while( reader.Read() )
                {
                    highPrice = reader["CurPrice"].ToString();
                }
            }
            catch(Exception ex)
            {
                Global.form.AddMessage(string.Format("获取最高价时出错:{0}",ex));
            }
            finally
            {
                reader.Close();
            }
            return highPrice;
        }
        
        /// <summary>
        /// 获取指定数目的用户
        /// </summary>
        /// <param name="code"></param>
        /// <returns></returns>
        public ArrayList GetNearhighPricePeople(string code)
        {
            int i = 0;
            if(func.GetMessageByCode("RecPeopleNum",code) != null && func.GetMessageByCode("RecPeopleNum",code) != "")
                i = Convert.ToInt32(func.GetMessageByCode("RecPeopleNum", code));
            ArrayList list = new ArrayList();
            try
            {
                string sql = "SELECT TOP " + i.ToString() + " * FROM SMS_TICKETSALE WHERE SMSCode = '" + code + "' AND LoopState = 1 AND LoopNum <= " + Convert.ToInt32(func.GetMessageByCode("TicketNum", code)) + " ORDER BY RecTime DESC";
                cmd = new SqlCommand(sql, Global.conn);
                reader = cmd.ExecuteReader();
                int pos = 0;
                while (reader.Read())
                {
                    if (!list.Contains(reader["UserMobile"].ToString()))
                        list.Add(reader["UserMobile"].ToString());
                }
            }
            catch (Exception ex)
            {
                Global.form.AddMessage(string.Format("获取最高价时出错:{0}", ex));
            }
            finally
            {
                reader.Close();
            }
            return list;
        }
        
        /// <summary>
        /// 
        /// </summary>
        /// <param name="code"></param>
        /// <returns></returns>
        public string GetLoopNum(string code)
        {
            string loopNum = null;
            try
            {
                string sql = "SELECT TOP 1 * FROM SMS_TICKETSALE WHERE SMSCode = '" + code + "' AND LoopState = 1 AND LoopNum <= " + Convert.ToInt32(func.GetMessageByCode("TicketNum", code)) + " ORDER BY RecTime DESC";
                cmd = new SqlCommand(sql, Global.conn);
                reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    loopNum = reader["LoopNum"].ToString();
                }
            }
            catch (Exception ex)
            {
                Global.form.AddMessage(string.Format("获取最高价时出错:{0}", ex));
            }
            finally
            {
                reader.Close();
            }
            return loopNum;
        }
        /// <summary>
        /// 将收到的用户信息写入数据库
        /// </summary>
        /// <param name="UserMobile"></param>
        /// <param name="SMSCode"></param>
        /// <param name="CurPrice"></param>
        public void AddmessageToDB(string UserMobile,string SMSCode,string CurPrice)
        {
            try
            {
                string sql = "INSERT INTO SMS_TICKETSALE (UserMobile,SMSCode,CurPrice,HighestPrice,PlaneInfo,LoopNum) VALUES (";
                sql += "'" + UserMobile + "',";
                sql += "'" + SMSCode + "',";
                sql += "'" + CurPrice + "',";
                sql += "'" + func.GetMessageByCode("HighestPrice",SMSCode) + "',";
                sql += "'" + func.GetMessageByCode("PlaneInfo", SMSCode) + "',";
                if (GetLoopNum(SMSCode) == null)
                    sql += 1;
                else
                    sql += SetLoopNum(SMSCode);
                sql += ")";
                cmd = new SqlCommand(sql,Global.conn);
                cmd.ExecuteNonQuery();
            }
            catch(Exception ex)
            {
                Global.form.AddMessage(string.Format("写入数据库时出错:{0}", ex));
            }
        }


        public string GetLoopNum2(string code)
        {
            string loopNum = null;
            try
            {
                string sql = "SELECT TOP 1 * FROM SMS_TICKETSALE WHERE SMSCode = '" + code + "' ORDER BY RecTime DESC";
                cmd = new SqlCommand(sql, Global.conn);
                reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    loopNum = reader["LoopNum"].ToString();
                }
            }
            catch (Exception ex)
            {
                Global.form.AddMessage(string.Format("获取最高价时出错:{0}", ex));
            }
            finally
            {
                reader.Close();
            }
            return loopNum;
        }
        /// <summary>
        /// 获取当前状态
        /// </summary>
        /// <param name="code"></param>
        /// <returns></returns>
        private string GetLoopState(string code)
        {
            string loopState = "1";
            try
            {
                string sql = "SELECT TOP 1 * FROM SMS_TICKETSALE WHERE SMSCode = '" + code +  "' ORDER BY RecTime DESC";
                cmd = new SqlCommand(sql, Global.conn);
                reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    loopState = reader["LoopState"].ToString();
                }
            }
            catch (Exception ex)
            {
                Global.form.AddMessage(string.Format("获取LoopState时出错:{0}", ex));
            }
            finally
            {
                reader.Close();
            }
            return loopState;
        }
        
        /// <summary>
        /// 获取插入时LoopNum的值
        /// </summary>
        /// <param name="code"></param>
        /// <returns></returns>
        private int SetLoopNum(string code)
        {
            int LoopNum = 0;
            if (GetLoopState(code) == "1")
                LoopNum = Convert.ToInt32(GetLoopNum2(code));
            else if (GetLoopState(code) == "0")
                LoopNum = Convert.ToInt32(GetLoopNum2(code)) + 1;
            return LoopNum;
        }
        
        /// <summary>
        /// 更新LoopState的状态值
        /// </summary>
        /// <param name="code"></param>
        public void UpdateLoopState(string code)
        {
            string LoopNum = GetLoopNum(code);
            try
            {
                string sql = "UPDATE SMS_TICKETSALE SET LoopState = 0 WHERE SMSCode = '" + code + "' AND LoopNum = " + Convert.ToInt32(LoopNum);
                cmd = new SqlCommand(sql,Global.conn);
                cmd.ExecuteNonQuery();
            }
            catch(Exception ex)
            {
                Global.form.AddMessage(string.Format("更新LoopState时出错:{0}",ex));
            }
        }
        
        /// <summary>
        /// 获取当前Loop中的所有用户清单
        /// </summary>
        /// <param name="code"></param>
        /// <returns></returns>
        public ArrayList GetCurLoopPeople(string code)
        {
            ArrayList list = new ArrayList();
            try
            {
                string sql = "SELECT * FROM SMS_TICKETSALE WHERE SMSCode = '" + code + "' AND LoopState = 1  ORDER BY RecTime DESC";
                cmd = new SqlCommand(sql, Global.conn);
                reader = cmd.ExecuteReader();
                int pos = 0;
                while (reader.Read())
                {
                    if (!list.Contains(reader["UserMobile"].ToString()))
                        list.Add(reader["UserMobile"].ToString());
                }
            }
            catch (Exception ex)
            {
                Global.form.AddMessage(string.Format("获取最高价时出错:{0}", ex));
            }
            finally
            {
                reader.Close();
            }
            return list;
        }
    }
}