在哪里摔倒
就在哪里自己爬起来
asp.net里用存储过程搞东西真的好快，越来越喜欢，短消息就是一个表的维护，很容易的，本来不用用到存储过程，就是因为我把未读短消息数写进了用户表里，所以在短消息表的维护后，还要动一下用户表
这些连带的表的数据更新，我最开始的时候是用代码做的，有时候代码执行了一半出错，数据库就乱了，后来我喜欢用触发器来搞，现在我用存储过程搞，因为触发器的维护不好弄
先看短消息表的结构
CREATE TABLE [T_SMS] (
[sid] [int] IDENTITY (1, 1) NOT NULL ,
[fromTo] [int] NULL CONSTRAINT [DF_T_SMS_fromTo] DEFAULT (0),
[sendTo] [int] NULL CONSTRAINT [DF_T_SMS_sendTo] DEFAULT (0),
[title] [varchar] (200) COLLATE Chinese_PRC_CI_AS NULL CONSTRAINT [DF_T_SMS_title] DEFAULT (''),
[stime] [datetime] NULL CONSTRAINT [DF_T_SMS_stime] DEFAULT (getdate()),
[isRead] [bit] NULL CONSTRAINT [DF_T_SMS_isRead] DEFAULT (0),
[isDel] [bit] NULL CONSTRAINT [DF_T_SMS_isDel] DEFAULT (0),
[sms] [varchar] (2000) COLLATE Chinese_PRC_CI_AS NULL CONSTRAINT [DF_T_SMS_sms] DEFAULT (''),
CONSTRAINT [PK_T_SMS] PRIMARY KEY CLUSTERED
(
[sid]
) ON [PRIMARY]
) ON [PRIMARY]
GO
各字段的意思是：fromTo发消息的人，sendTo接消息的人，title标题，sms内容，stime发送时间，isRead是否已读，isDel是否已删除
我一共用了五个存储过程，a_SMS_List短消息列表，a_SMS_read显示单个短消息的内容，a_SMS_insert添加短消息(控制群发)，a_SMS_oneUser给一个用户添加短消息，a_SMS_delete删除短消息
在界面上的处理很简单，用一个GridView，使用a_SMS_List可以显示出收件箱、发件箱、废件箱
用一个FormView，select方法用a_SMS_read，insert方法用a_SMS_insert，delete方法用a_SMS_delete，可以阅读、添加和删除短消息，短消息是不需要修改的，所以不用修改的方法了
下面是这五个存储过程
CREATE PROCEDURE [dbo].[a_SMS_List] ( @uid int, @type int ) AS
declare @sql varchar(600)
select @sql='select SMS.sid, SMS.fromTo, SMS.sendTo, F.truename AS fromName, S.truename AS sendName, SMS.title, SMS.stime, SMS.isRead, SMS.sms '
select @sql=@sql + 'from T_SMS SMS '
select @sql=@sql + 'inner JOIN T_User F on SMS.fromTo = F.uid '
select @sql=@sql + 'inner JOIN T_User S on SMS.sendTo = S.uid '
if @type=1
begin
--get box
select @sql=@sql + 'where sendTo=' + convert(varchar(6),@uid)
end
else if @type=2
begin
--send box
select @sql=@sql + 'where fromTo=' + convert(varchar(6),@uid)
end
else if @type=3
begin
--delete box
select @sql=@sql + 'where SMS.isDel=1 and (fromTo=' + convert(varchar(6),@uid) + ' or sendTo=' + convert(varchar(6),@uid) + ')'
end
select @sql=@sql + ' order by stime desc'
--print @sql
execute(@sql)
CREATE PROCEDURE [dbo].[a_SMS_read] ( @uid int, @sid int )AS
declare @isRead int
select @isRead=isRead from T_SMS where sid=@sid and sendTo=@uid
if @isRead=0
begin
update T_SMS set isRead=1 where sid=@sid
update T_User set SmsCount=SmsCount-1 where uid=@uid
end
--detail
select SMS.sid, SMS.fromTo, SMS.sendTo, F.truename AS fromName, S.truename AS sendName, SMS.title, SMS.stime, SMS.isRead, SMS.sms
from T_SMS SMS
inner join T_User F on SMS.fromTo = F.uid
inner join T_User S on SMS.sendTo = S.uid
where sid=@sid and (fromTo=@uid or sendTo=@uid)
CREATE PROCEDURE [dbo].[a_SMS_insert] (@fromTo int, @sendTo int, @title varchar(200), @sms varchar(2000)) AS
if @sendTo<>0
begin
exec a_SMS_insert_oneUser @fromTo, @sendTo, @title, @sms
end
else
begin
declare user_cursor scroll cursor for select uid from T_User order by uid
open user_cursor
fetch next from user_cursor into @sendTo
while @@fetch_status=0
begin
exec a_SMS_insert_oneUser @fromTo, @sendTo, @title, @sms
fetch next from user_cursor into @sendTo
end
close user_cursor
deallocate user_cursor
end
CREATE PROCEDURE [dbo].[a_SMS_insert_oneUser] (@fromTo int, @sendTo int, @title varchar(200), @sms varchar(2000)) AS
insert into T_SMS
(fromTo, sendTo, title, stime, isRead, isDel, sms)
values
(@fromTo, @sendTo, @title, getdate(), 0, 0, @sms)
update T_User set SmsCount=SmsCount+1 where uid=@sendTo
CREATE PROCEDURE [dbo].[a_SMS_delete] ( @uid int, @sid int ) AS
declare @isRead int, @isDel int
select @isRead=isRead, @isDel=isDel from T_SMS where sid=@sid and sendTo=@uid
if @isRead=0
begin
update T_User set SmsCount=SmsCount-1 where uid=@uid
end
--detail
if @isDel=1
begin
delete from T_SMS where sid=@sid and sendTo=@uid
end
else
begin
update T_SMS set isDel=1 where sid=@sid and sendTo=@uid
end
