短消息数据库

MessageTB

ID      OleDbType.Integer   
m_SendUser     OleDbType.VarWChar   发送者名称
m_SendID    OleDbType.Integer    发送者ID
m_ReceiveID     OleDbType.Integer    接收者ID
m_title     OleDbType.VarWChar   标题
m_content     OleDbType.VarWChar   内容
m_sendTime     OleDbType.Date    发送时间
m_readed     OleDbType.Bit    是否已读

 

根据用户ID获取消息统计数
GetCountIdOnUserID_MessageTB
SQL:
SELECT count(ID)
FROM MessageTB
WHERE m_ReceiveID = [@m_ReceiveID];

添加短消息
AddData_MessageTB
SQL:
INSERT INTO MessageTB ( m_SendUser, m_SendID, m_ReceiveID, m_title, m_content )
SELECT @m_SendUser AS 表达式1, @m_SendID AS 表达式2, @m_ReceiveID AS 表达式3, @m_title AS 表达式4, @m_content AS 表达式5;

根据ID和用户ID获取消息内容
GetDataOnIdRid_MessageTB
AS
SELECT  m_SendUser,m_title,m_content,m_sendTime
FROM MessageTB
WHERE ID = @ID AND
m_ReceiveID = @m_ReceiveID 

将消息状态改为已读
ReadMessage
SQL:
UPDATE MessageTB SET m_readed = 1
WHERE ID=[@ID];

根据ID接收者ID获取数据
DelOnIDm_ReceiveID_MessageTB
SQL:
DELETE MessageTB.ID, MessageTB.m_ReceiveID, *
FROM MessageTB
WHERE MessageTB.ID = @ID
AND MessageTB.m_ReceiveID = @m_ReceiveID;