下面是数据库的实现代码：
using System;
using System.Collections.Generic;
using System.Text;
using System.Data.OleDb;
using System.Data;
namespace CMPP
{
class SMSData
{
private string errMessage;
private OleDbConnection oleConn;
private OleDbCommand oleGetSynOrderRelation;
private OleDbCommand oleGetWillSendSM_CM;
private OleDbCommand oleGetWillSendSM_UC;
private OleDbCommand oleRegistSPNumber;
private OleDbCommand oleSaveRecievedSM;
private OleDbCommand oleSaveSyncOrderRelation;
private OleDbCommand oleSetSendSMDoneTime;
private OleDbCommand oleSetSendSMFlag;
private OleDbCommand oleSetSendSMMsgId;
private OleDbCommand oleUpdateSynOrderRelationFlag;
public SMSData()
{
this.InitializeComponent();
}
public int GetWillSendSM_CM(ref long id, ref CMPP_SUBMIT mess)
{
int num1 = 0;
try
{
OleDbDataAdapter adapter1 = new OleDbDataAdapter(this.oleGetWillSendSM_CM);
DataTable table1 = new DataTable("tbl_SendSM");
if (adapter1.Fill(table1) > 0)
{
id = Convert.ToInt64(table1.Rows[0]["id"].ToString());
mess.Pk_total = 0;
mess.Pk_number = 0;
if (table1.Rows[0]["Registered_Delivery"].ToString().Trim().Length > 0)
{
mess.Registered_Delivery = Convert.ToByte(table1.Rows[0]["Registered_Delivery"].ToString());
}
else
{
mess.Registered_Delivery = 1;
}
if (table1.Rows[0]["Msg_level"].ToString().Trim().Length > 0)
{
mess.Msg_level = Convert.ToByte(table1.Rows[0]["Msg_level"].ToString());
}
else
{
mess.Msg_level = 0;
}
if (table1.Rows[0]["service"].ToString().Trim().Length > 0)
{
mess.Service_Id = table1.Rows[0]["service"].ToString();
}
else
{
mess.Service_Id = "Test";
}
if (table1.Rows[0]["Fee_UserType"].ToString().Trim().Length > 0)
{
mess.Fee_UserType = Convert.ToByte(table1.Rows[0]["Fee_UserType"].ToString());
}
else
{
mess.Fee_UserType = 0;
}
if (mess.Fee_UserType == 3)
{
mess.Fee_terminal_Id = table1.Rows[0]["Fee_terminal_Id"].ToString();
}
if (table1.Rows[0]["Fee_terminal_type"].ToString().Trim().Length > 0)
{
mess.Fee_terminal_type = Convert.ToByte(table1.Rows[0]["Fee_terminal_type"].ToString());
}
else
{
mess.Fee_terminal_type = 0;
}
if (table1.Rows[0]["TP_pid"].ToString().Trim().Length > 0)
{
mess.TP_pId = Convert.ToByte(table1.Rows[0]["TP_pid"].ToString());
}
else
{
mess.TP_pId = 0;
}
if (table1.Rows[0]["TP_udhi"].ToString().Trim().Length > 0)
{
mess.TP_udhi = Convert.ToByte(table1.Rows[0]["TP_udhi"].ToString());
}
else
{
mess.TP_udhi = 0;
}
if (table1.Rows[0]["Msg_Fmt"].ToString().Trim().Length > 0)
{
mess.Msg_Fmt = Convert.ToByte(table1.Rows[0]["Msg_Fmt"].ToString());
}
else
{
mess.Msg_Fmt = 15;
}
if (table1.Rows[0]["FeeType"].ToString().Trim().Length > 0)
{
mess.FeeType = table1.Rows[0]["FeeType"].ToString();
}
else
{
mess.FeeType = "02";
}
if (table1.Rows[0]["FeeCode"].ToString().Trim().Length > 0)
{
int num3 = Convert.ToInt32(table1.Rows[0]["FeeCode"].ToString());
mess.FeeCode = num3.ToString("000000");
}
else
{
mess.FeeCode = "000010";
}
mess.ValId_Time = table1.Rows[0]["ExpireTime"].ToString();
mess.At_Time = table1.Rows[0]["ScheduleTime"].ToString();
mess.Src_Id = table1.Rows[0]["spid"].ToString();
mess.DestUsr_tl = 0;
mess.Dest_terminal_Id = new string[] {table1.Rows[0]["mobile"].ToString()};
if (table1.Rows[0]["Dest_terminal_type"].ToString().Trim().Length > 0)
{
mess.Dest_terminal_type = Convert.ToByte(table1.Rows[0]["Dest_terminal_type"].ToString());
}
else
{
mess.Dest_terminal_type = 0;
}
mess.Msg_Content = table1.Rows[0]["content"].ToString();
mess.LinkId = table1.Rows[0]["LinkID"].ToString();
return 0;
}
num1 = -1;
}
catch (Exception exception1)
{
this.errMessage = exception1.Message;
Log.WriteLog("DASmsCenter.GetWillSendSM_CM:" + exception1.Message);
num1 = -2;
}
return num1;
}
public int OpenDBConnection()
{
int num1;
try
{
if (this.oleConn.State == ConnectionState.Closed)
{
this.oleConn.Open();
}
num1 = 0;
}
catch (Exception exception1)
{
this.errMessage = exception1.Message;
Log.WriteLog("OpenDBConnection:" + exception1.Message);
num1 = -1;
}
return num1;
}
public int OpenDBConnection(string ConnectionString)
{
if (this.oleConn.State == ConnectionState.Closed)
{
this.oleConn.ConnectionString = ConnectionString;
return this.OpenDBConnection();
}
return 0;
}
/// <summary>
/// 保存接收的短信
/// </summary>
/// <param name="mobile"></param>
/// <param name="spid"></param>
/// <param name="content"></param>
/// <param name="service"></param>
/// <param name="msgId"></param>
/// <param name="sequence"></param>
/// <param name="LinkID"></param>
/// <returns></returns>
public int SaveRecievedSM(string mobile, string spid, string content, string service, long msgId, int sequence, string LinkID)
{
int num1 = 0;
this.oleSaveRecievedSM.Parameters["mobile"].Value = mobile;
this.oleSaveRecievedSM.Parameters["spid"].Value = spid;
this.oleSaveRecievedSM.Parameters["content"].Value = content;
this.oleSaveRecievedSM.Parameters["service"].Value = service;
this.oleSaveRecievedSM.Parameters["msgId"].Value = msgId;
this.oleSaveRecievedSM.Parameters["sequence"].Value = sequence;
this.oleSaveRecievedSM.Parameters["LinkID"].Value = LinkID;
try
{
this.oleSaveRecievedSM.ExecuteNonQuery();
}
catch (Exception exception1)
{
this.errMessage = exception1.Message;
Log.WriteLog("DASmsCenter.SaveRecievedSM:" + exception1.Message);
num1 = -1;
}
return num1;
}
public int SetSendSMDoneTime(long msgId, int flag, string sendstat)
{
int num1 = 0;
this.oleSetSendSMDoneTime.Parameters["Original_msgId"].Value = msgId;
this.oleSetSendSMDoneTime.Parameters["flag"].Value = flag;
this.oleSetSendSMDoneTime.Parameters["sendstat"].Value = sendstat;
this.oleSetSendSMDoneTime.Parameters["doneTime"].Value = DateTime.Now;
try
{
this.oleSetSendSMDoneTime.ExecuteNonQuery();
}
catch (Exception exception1)
{
this.errMessage = exception1.Message;
Log.WriteLog("DASmsCenter.SetSendSMDoneTime:" + exception1.Message);
num1 = -1;
}
return num1;
}
public int SetSendSMFlag(long id, int flag, int sequence, int MessageCount)
{
int num1 = 0;
this.oleSetSendSMFlag.Parameters["Original_id"].Value = id;
this.oleSetSendSMFlag.Parameters["flag"].Value = flag;
this.oleSetSendSMFlag.Parameters["sentTime"].Value = DateTime.Now;
this.oleSetSendSMFlag.Parameters["sequence"].Value = sequence;
this.oleSetSendSMFlag.Parameters["MessageCount"].Value = MessageCount;
try
{
this.oleSetSendSMFlag.ExecuteNonQuery();
}
catch (Exception exception1)
{
this.errMessage = exception1.Message;
Log.WriteLog("DASmsCenter.SetSendSMFlag:" + exception1.Message);
num1 = -1;
}
return num1;
}
public int SetSendSMMsgId(int sequence, long msgId, int flag)
{
int num1 = 0;
this.oleSetSendSMMsgId.Parameters["Original_sequence"].Value = sequence;
this.oleSetSendSMMsgId.Parameters["msgId"].Value = msgId;
this.oleSetSendSMMsgId.Parameters["flag"].Value = flag;
try
{
this.oleSetSendSMMsgId.ExecuteNonQuery();
}
catch (Exception exception1)
{
this.errMessage = exception1.Message;
Log.WriteLog("DASmsCenter.SetSendSMMsgId:" + exception1.Message);
num1 = -1;
}
return num1;
}
public void CloseDBConnection()
{
this.oleConn.Close();
}
public string DBConnectionString
{
get
{
return this.oleConn.ConnectionString;
}
set
{
this.oleConn.ConnectionString = value;
}
}
public string ErrorMessage
{
get
{
return this.errMessage;
}
}
private void InitializeComponent()
{
this.oleConn = new OleDbConnection();
this.oleSetSendSMFlag = new OleDbCommand();
this.oleSetSendSMMsgId = new OleDbCommand();
this.oleSetSendSMDoneTime = new OleDbCommand();
this.oleGetWillSendSM_CM = new OleDbCommand();
this.oleSaveRecievedSM = new OleDbCommand();
this.oleRegistSPNumber = new OleDbCommand();
this.oleGetWillSendSM_UC = new OleDbCommand();
this.oleSaveSyncOrderRelation = new OleDbCommand();
this.oleGetSynOrderRelation = new OleDbCommand();
this.oleUpdateSynOrderRelationFlag = new OleDbCommand();
this.oleConn.ConnectionString = "Provider=SQLOLEDB.1;Data Source=127.0.0.1;Initial Catalog=SMSCenter;User ID=sa;Password=11";
this.oleSetSendSMFlag.CommandText = "UPDATE tbl_SendSM SET flag = ?, sentTime = ?, sequence = ?, MessageCount = ? WHERE (id = ?)";
this.oleSetSendSMFlag.Connection = this.oleConn;
this.oleSetSendSMFlag.Parameters.Add(new OleDbParameter("flag", OleDbType.Integer, 4, "flag"));
this.oleSetSendSMFlag.Parameters.Add(new OleDbParameter("sentTime", OleDbType.DBTimeStamp, 8, "sentTime"));
this.oleSetSendSMFlag.Parameters.Add(new OleDbParameter("sequence", OleDbType.Integer, 4, "sequence"));
this.oleSetSendSMFlag.Parameters.Add(new OleDbParameter("MessageCount", OleDbType.Integer, 4, "MessageCount"));
this.oleSetSendSMFlag.Parameters.Add(new OleDbParameter("Original_id", OleDbType.BigInt, 8, ParameterDirection.Input, false, 0, 0, "id", DataRowVersion.Original, null));
this.oleSetSendSMMsgId.CommandText = "UPDATE tbl_SendSM SET flag = ?, msgId = ? WHERE (flag = 1) AND (sequence = ?)";
this.oleSetSendSMMsgId.Connection = this.oleConn;
this.oleSetSendSMMsgId.Parameters.Add(new OleDbParameter("flag", OleDbType.Integer, 4, "flag"));
this.oleSetSendSMMsgId.Parameters.Add(new OleDbParameter("msgId", OleDbType.BigInt, 8, "msgId"));
this.oleSetSendSMMsgId.Parameters.Add(new OleDbParameter("Original_sequence", OleDbType.Integer, 4, ParameterDirection.Input, false, 0, 0, "sequence", DataRowVersion.Original, null));
this.oleSetSendSMDoneTime.CommandText = "UPDATE tbl_SendSM SET flag = ?, doneTime = ?, sendstat = ? WHERE (flag = 2) AND (msgId = ?) ";
this.oleSetSendSMDoneTime.Connection = this.oleConn;
this.oleSetSendSMDoneTime.Parameters.Add(new OleDbParameter("flag", OleDbType.Integer, 4, "flag"));
this.oleSetSendSMDoneTime.Parameters.Add(new OleDbParameter("doneTime", OleDbType.DBTimeStamp, 8, "doneTime"));
this.oleSetSendSMDoneTime.Parameters.Add(new OleDbParameter("sendstat", OleDbType.VarChar, 10, "sendstat"));
this.oleSetSendSMDoneTime.Parameters.Add(new OleDbParameter("Original_msgId", OleDbType.BigInt, 8, ParameterDirection.Input, false, 0, 0, "msgId", DataRowVersion.Original, null));
this.oleGetWillSendSM_CM.CommandText = "SELECT id, mobile, spid, content, Registered_Delivery, Msg_level, Service, Fee_UserType, Fee_terminal_Id, Fee_terminal_type, TP_pid, TP_udhi, Msg_Fmt, FeeType, FeeCode, ExpireTime, ScheduleTime, Dest_terminal_type, LinkID FROM tbl_SendSM WHERE (flag = 0) AND (preSendTime <= GETDATE()) AND (mobile NOT LIKE '130%') AND (mobile NOT LIKE '131%') AND (mobile NOT LIKE '133%') ORDER BY id";
this.oleGetWillSendSM_CM.Connection = this.oleConn;
this.oleSaveRecievedSM.CommandText = "INSERT INTO tbl_RecieveSM (mobile, spid, content, msgId, sequence, service, LinkID, recieveTime, flag) VALUES (?, ?, ?, ?, ?, ?, ?, GETDATE(), 0)";
this.oleSaveRecievedSM.Connection = this.oleConn;
this.oleSaveRecievedSM.Parameters.Add(new OleDbParameter("mobile", OleDbType.VarChar, 20, "mobile"));
this.oleSaveRecievedSM.Parameters.Add(new OleDbParameter("spid", OleDbType.VarChar, 0x15, "spid"));
this.oleSaveRecievedSM.Parameters.Add(new OleDbParameter("content", OleDbType.VarWChar, 200, "content"));
this.oleSaveRecievedSM.Parameters.Add(new OleDbParameter("msgId", OleDbType.BigInt, 8, "msgId"));
this.oleSaveRecievedSM.Parameters.Add(new OleDbParameter("sequence", OleDbType.Integer, 4, "sequence"));
this.oleSaveRecievedSM.Parameters.Add(new OleDbParameter("service", OleDbType.VarChar, 20, "service"));
this.oleSaveRecievedSM.Parameters.Add(new OleDbParameter("LinkID", OleDbType.VarWChar, 50, "LinkID"));
this.oleRegistSPNumber.CommandText = "SELECT * FROM tbl_SPInfo WHERE (spid = ?) ";
this.oleRegistSPNumber.Connection = this.oleConn;
this.oleRegistSPNumber.Parameters.Add(new OleDbParameter("spid", OleDbType.VarChar, 0x15, "spid"));
this.oleGetWillSendSM_UC.CommandText = "SELECT id, mobile, spid, content, Service, FeeType, FeeCode, UC_GivenValue, UC_AgentFlag, UC_MorelatetoMTFlag, Msg_level, ExpireTime, ScheduleTime, Registered_Delivery, TP_pid, TP_udhi, Msg_Fmt, UC_MessageType, UC_ChargeNumber, LinkID FROM tbl_SendSM WHERE (flag = 0) AND (preSendTime <= GETDATE()) AND (mobile LIKE '130%' OR mobile LIKE '131%' OR mobile LIKE '133%') ORDER BY id";
this.oleGetWillSendSM_UC.Connection = this.oleConn;
this.oleSaveSyncOrderRelation.CommandText = "INSERT INTO tbl_SyncOrderRelation (TransactionID, MsgType, Version, Send_Address_DeviceType, Send_Address_DeviceID, Dest_Address_DeviceType, Dest_Address_DeviceID, FeeUser_ID_UserIDType, FeeUser_ID_MSISDN, FeeUser_ID_PseudoCode, DestUser_ID_UserIDType, DestUser_ID_MSISDN, DestUser_ID_PseudoCode, LinkID, ActionID, ActionReasonID, SPID, SPServiceID, AccessMode, FeatureStr, hRet, RecieveTime, Flag) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 0, GETDATE(), 0)";
this.oleSaveSyncOrderRelation.Connection = this.oleConn;
this.oleSaveSyncOrderRelation.Parameters.Add(new OleDbParameter("TransactionID", OleDbType.VarChar, 50, "TransactionID"));
this.oleSaveSyncOrderRelation.Parameters.Add(new OleDbParameter("MsgType", OleDbType.VarChar, 50, "MsgType"));
this.oleSaveSyncOrderRelation.Parameters.Add(new OleDbParameter("Version", OleDbType.VarChar, 50, "Version"));
this.oleSaveSyncOrderRelation.Parameters.Add(new OleDbParameter("Send_Address_DeviceType", OleDbType.Integer, 4, "Send_Address_DeviceType"));
this.oleSaveSyncOrderRelation.Parameters.Add(new OleDbParameter("Send_Address_DeviceID", OleDbType.VarChar, 50, "Send_Address_DeviceID"));
this.oleSaveSyncOrderRelation.Parameters.Add(new OleDbParameter("Dest_Address_DeviceType", OleDbType.Integer, 4, "Dest_Address_DeviceType"));
this.oleSaveSyncOrderRelation.Parameters.Add(new OleDbParameter("Dest_Address_DeviceID", OleDbType.VarChar, 50, "Dest_Address_DeviceID"));
this.oleSaveSyncOrderRelation.Parameters.Add(new OleDbParameter("FeeUser_ID_UserIDType", OleDbType.Integer, 4, "FeeUser_ID_UserIDType"));
this.oleSaveSyncOrderRelation.Parameters.Add(new OleDbParameter("FeeUser_ID_MSISDN", OleDbType.VarChar, 50, "FeeUser_ID_MSISDN"));
this.oleSaveSyncOrderRelation.Parameters.Add(new OleDbParameter("FeeUser_ID_PseudoCode", OleDbType.VarChar, 50, "FeeUser_ID_PseudoCode"));
this.oleSaveSyncOrderRelation.Parameters.Add(new OleDbParameter("DestUser_ID_UserIDType", OleDbType.Integer, 4, "DestUser_ID_UserIDType"));
this.oleSaveSyncOrderRelation.Parameters.Add(new OleDbParameter("DestUser_ID_MSISDN", OleDbType.VarChar, 50, "DestUser_ID_MSISDN"));
this.oleSaveSyncOrderRelation.Parameters.Add(new OleDbParameter("DestUser_ID_PseudoCode", OleDbType.VarChar, 50, "DestUser_ID_PseudoCode"));
this.oleSaveSyncOrderRelation.Parameters.Add(new OleDbParameter("LinkID", OleDbType.VarChar, 50, "LinkID"));
this.oleSaveSyncOrderRelation.Parameters.Add(new OleDbParameter("ActionID", OleDbType.Integer, 4, "ActionID"));
this.oleSaveSyncOrderRelation.Parameters.Add(new OleDbParameter("ActionReasonID", OleDbType.Integer, 4, "ActionReasonID"));
this.oleSaveSyncOrderRelation.Parameters.Add(new OleDbParameter("SPID", OleDbType.VarChar, 50, "SPID"));
this.oleSaveSyncOrderRelation.Parameters.Add(new OleDbParameter("SPServiceID", OleDbType.VarChar, 50, "SPServiceID"));
this.oleSaveSyncOrderRelation.Parameters.Add(new OleDbParameter("AccessMode", OleDbType.Integer, 4, "AccessMode"));
this.oleSaveSyncOrderRelation.Parameters.Add(new OleDbParameter("FeatureStr", OleDbType.VarChar, 50, "FeatureStr"));
this.oleGetSynOrderRelation.CommandText = "SELECT TOP 1 TransactionID, MsgType, Version, hRet FROM tbl_SyncOrderRelation WHERE (Flag = 0) AND (DATEADD(second, 2, RecieveTime) < GETDATE()) OR (Flag = 1) ORDER BY TransactionID";
this.oleGetSynOrderRelation.Connection = this.oleConn;
this.oleUpdateSynOrderRelationFlag.CommandText = "UPDATE tbl_SyncOrderRelation SET Flag = ? WHERE (TransactionID = ?)";
this.oleUpdateSynOrderRelationFlag.Connection = this.oleConn;
this.oleUpdateSynOrderRelationFlag.Parameters.Add(new OleDbParameter("Flag", OleDbType.Integer, 4, "Flag"));
this.oleUpdateSynOrderRelationFlag.Parameters.Add(new OleDbParameter("Original_TransactionID", OleDbType.VarChar, 50, ParameterDirection.Input, false, 0, 0, "TransactionID", DataRowVersion.Original, null));
}
}
}
