最近在写Blog程序,由于Sql server空间贵...用Access做了..但对于C#操作Access还是有些不了解..想借鉴一下Discuz!NT..没想到找不到事务处理...
看了Access与Sql版的Discuz!NT2.0竟然没有发现事务的身影~?
难道都不需要事务处理了? 如何保证数据的完整性?是不是太想当然了?
这算不算对用户的不负责任?
还是我愚昧找不到保证数据完整性的处理代码? 望高人相助~
附Access与Sql版的删除贴子代码
Access版:
/// <summary>
/// 删除指定ID的帖子
/// </summary>
/// <param name="pid">帖子ID</param>
/// <returns>删除数量</returns>
public static int DeletePost(string posttableid,int pid)
{
// OleDbParameter[] prams = {
// Database.MakeInParam("@pid",OleDbType.Integer,4,pid)
// };
#region 存储过程转sql语句 DeletePost
int fid=0;
int tid=0;
int posterid=0;
int lastforumposterid=0;
int layer=0;
DateTime postdatetime;
string poster="";
int postcount=0;
string title="";
int lasttid=0;
//int postid=0;
int todaycount=0;
string fidlist = "";
string strSQL = "";
DataTable dt=new DataTable();
strSQL = "SELECT [fid], [tid], [posterid],[layer], [postdatetime] FROM [" + BaseConfigFactory.GetTablePrefix + "posts" +posttableid+"] WHERE pid =" + pid;
DataRow dr=Database.ExecuteDataset(CommandType.Text,strSQL).Tables[0].Rows[0];
fid=Convert.ToInt32(dr["fid"].ToString());
tid=Convert.ToInt32(dr["tid"].ToString());
posterid=Convert.ToInt32(dr["posterid"].ToString());
layer=Convert.ToInt32(dr["layer"].ToString());
postdatetime=Convert.ToDateTime(dr["postdatetime"].ToString());
strSQL = "SELECT iif(([parentidlist] is null),'',[parentidlist]) as [fidlist] FROM [" + BaseConfigFactory.GetTablePrefix + "forums] WHERE [fid] =" + fid;
fidlist = Database.ExecuteScalarToStr(CommandType.Text,strSQL);
if (fidlist != "")
{
fidlist = string.Concat(fidlist,",",fid.ToString());
}
else
{
fidlist = fid.ToString();
}
if(layer!=0)
{
//--只删除一个帖子
// --更新论坛总的回帖数
strSQL = "UPDATE [" + BaseConfigFactory.GetTablePrefix + "statistics] SET [totalpost]=[totalpost] - 1";
Database.ExecuteNonQuery(CommandType.Text,strSQL);
// --更新版块内总的回帖数
if(Convert.ToDateTime(postdatetime).ToShortDateString()==DateTime.Now.ToShortDateString())
{
strSQL = "UPDATE [" + BaseConfigFactory.GetTablePrefix + "forums] SET [posts]=[posts] - 1, [todayposts]=[todayposts]-1 WHERE [fid] in ("+fidlist+")";
}
else
{
strSQL = "UPDATE [" + BaseConfigFactory.GetTablePrefix + "forums] SET [posts]=[posts] - 1 WHERE [fid] in ("+fidlist+")";
}
Database.ExecuteNonQuery(CommandType.Text,strSQL);
//--更新用户总的回帖数
strSQL = "UPDATE [" + BaseConfigFactory.GetTablePrefix + "users] SET [posts] = [posts]-1 WHERE [uid] ="+posterid;
Database.ExecuteNonQuery(CommandType.Text, strSQL);
//--更新主题总的回帖数
strSQL = "UPDATE [" + BaseConfigFactory.GetTablePrefix + "topics] SET [replies]=[replies] - 1 WHERE [tid]="+tid;
Database.ExecuteNonQuery(CommandType.Text, strSQL);
//--删除帖子
strSQL = "DELETE FROM [" + BaseConfigFactory.GetTablePrefix + "posts" +posttableid+"] WHERE [pid]=" + pid;
Database.ExecuteNonQuery(CommandType.Text, strSQL);
}
else
{
//--删除主题
strSQL = "SELECT COUNT([pid]) FROM [" + BaseConfigFactory.GetTablePrefix + "posts" +posttableid+"] WHERE [tid] = "+tid;
postcount = Convert.ToInt32(Database.ExecuteDataset(CommandType.Text,strSQL).Tables[0].Rows[0][0].ToString());
strSQL = "SELECT COUNT([pid]) FROM [" + BaseConfigFactory.GetTablePrefix + "posts" +posttableid+"] WHERE [tid] ="+tid+" AND DATEDIFF(\"d\", [postdatetime], now()) = 0";
todaycount = Convert.ToInt32(Database.ExecuteDataset(CommandType.Text,strSQL).Tables[0].Rows[0][0].ToString());
//--更新主题及帖子总数
strSQL = "UPDATE [" + BaseConfigFactory.GetTablePrefix + "statistics] SET [totaltopic]=[totaltopic] - 1, [totalpost]=[totalpost] -"+postcount;
Database.ExecuteNonQuery(CommandType.Text,strSQL);
//--更新版块
strSQL = "UPDATE [" + BaseConfigFactory.GetTablePrefix + "forums] SET [posts]=[posts] -"+postcount+", [topics]=[topics] - 1,[todayposts]=[todayposts] -"+todaycount+" WHERE [fid] in ("+fidlist+")";
Database.ExecuteNonQuery(CommandType.Text,strSQL);
//--更新用户总的回帖数
strSQL = "UPDATE [" + BaseConfigFactory.GetTablePrefix + "users] SET [posts] = [posts] - "+postcount+ " WHERE [uid] = "+posterid;
Database.ExecuteNonQuery(CommandType.Text,strSQL);
strSQL = "DELETE FROM [" + BaseConfigFactory.GetTablePrefix + "posts" +posttableid+"] WHERE [tid] = "+tid;
Database.ExecuteNonQuery(CommandType.Text,strSQL);
strSQL = "DELETE FROM [" + BaseConfigFactory.GetTablePrefix + "topics] WHERE [tid] = "+tid;
Database.ExecuteNonQuery(CommandType.Text,strSQL);
}
if (layer != 0)
{
strSQL = "SELECT TOP 1 [pid], [posterid],[postdatetime], [title], [poster] FROM [" + BaseConfigFactory.GetTablePrefix + "posts" +posttableid+"] WHERE [tid]="+tid+" ORDER BY [pid] DESC";
dt=Database.ExecuteDataset(CommandType.Text,strSQL).Tables[0];
if(dt.Rows.Count>0)
{
dr= dt.Rows[0];
pid=Convert.ToInt32(dr["pid"].ToString());
posterid=Convert.ToInt32(dr["posterid"].ToString());
postdatetime=Convert.ToDateTime(dr["postdatetime"].ToString());
title=dr["title"].ToString();
poster=dr["poster"].ToString();
strSQL = "UPDATE [" + BaseConfigFactory.GetTablePrefix + "topics] SET [lastposter]='"+poster+"',[lastpost]='"+postdatetime.ToString()+"',[lastpostid]="+pid+",[lastposterid]="+posterid+" WHERE [tid]="+tid;
Database.ExecuteNonQuery(CommandType.Text,strSQL);
}
}
strSQL = "SELECT [lasttid] FROM [" + BaseConfigFactory.GetTablePrefix + "forums] WHERE [fid] ="+fid;
lasttid=Convert.ToInt32(Database.ExecuteDataset(CommandType.Text,strSQL).Tables[0].Rows[0][0].ToString());
if(lasttid == tid)
{
strSQL = "SELECT TOP 1 [pid], [tid],[posterid], [title], [poster], [postdatetime] FROM [" + BaseConfigFactory.GetTablePrefix + "posts" +posttableid+"] WHERE [fid] = "+fid+" ORDER BY [pid] DESC";
dt=Database.ExecuteDataset(CommandType.Text,strSQL).Tables[0];
if(dt.Rows.Count>0)
{
dr= dt.Rows[0];
pid=Convert.ToInt32(dr["pid"].ToString());
tid=Convert.ToInt32(dr["tid"].ToString());
if (dr["posterid"] == null)
{
lastforumposterid = 0;
}
else
{
lastforumposterid=Convert.ToInt32(dr["posterid"].ToString());
}
postdatetime=Convert.ToDateTime(dr["postdatetime"].ToString());
if (dr["title"] == null)
{
title = "";
}
else
{
title= dr["title"].ToString();
}
if (dr["poster"] == null)
{
poster = "";
}
else
{
poster=dr["poster"].ToString();
}
strSQL = "UPDATE [" + BaseConfigFactory.GetTablePrefix + "forums] SET [lasttid]="+tid+",[lasttitle]='"+title+"',[lastpost]='"+postdatetime+"',[lastposter]='"+poster+"',[lastposterid]="+lastforumposterid+" WHERE [fid] in ("+fidlist+")";
Database.ExecuteNonQuery(CommandType.Text,strSQL);
strSQL = "SELECT TOP 1 [pid], [tid], [posterid], [postdatetime], [title], [poster] FROM [" + BaseConfigFactory.GetTablePrefix + "posts" +posttableid+"] WHERE [posterid]="+posterid +" ORDER BY [pid] DESC";
dr= Database.ExecuteDataset(CommandType.Text,strSQL).Tables[0].Rows[0];
pid=Convert.ToInt32(dr["pid"].ToString());
//tid=Convert.ToInt32(dr["tid"].ToString());
posterid=Convert.ToInt32(dr["posterid"].ToString());
postdatetime=Convert.ToDateTime(dr["postdatetime"].ToString());
if (dr["title"] == null)
{
title = "";
}
else
{
title= dr["title"].ToString();
}
//poster=dr["poster"].ToString();
//--更新用户
strSQL = "UPDATE [" + BaseConfigFactory.GetTablePrefix + "users] SET [lastpost] = '"+postdatetime+"',[lastpostid] = "+pid+",[lastposttitle] = '"+title+ "' WHERE [uid] = "+posterid;
Database.ExecuteNonQuery(CommandType.Text,strSQL);
}
}
#endregion
return postcount;
//return Database.ExecuteNonQuery(System.Data.CommandType.StoredProcedure,BaseConfigFactory.GetTablePrefix+"deletepost" + posttableid + "bypid",prams);
}
Sql版存储过程:
CREATE PROCEDURE dnt_deletepost1bypid
@pid int
AS
DECLARE @fid int
DECLARE @tid int
DECLARE @posterid int
DECLARE @lastforumposterid int
DECLARE @layer int
DECLARE @postdatetime smalldatetime
DECLARE @poster varchar(50)
DECLARE @postcount int
DECLARE @title nchar(60)
DECLARE @lasttid int
DECLARE @postid int
DECLARE @todaycount int
SELECT @fid = [fid],@tid = [tid],@posterid = [posterid],@layer = [layer], @postdatetime = [postdatetime] FROM [dnt_posts1] WHERE pid = @pid
DECLARE @fidlist AS VARCHAR(1000)
SET @fidlist = '';
SELECT @fidlist = ISNULL([parentidlist],'') FROM [dnt_forums] WHERE [fid] = @fid
IF RTRIM(@fidlist)<>''
BEGIN
SET @fidlist = RTRIM(@fidlist) + ',' + CAST(@fid AS VARCHAR(10))
END
ELSE
BEGIN
SET @fidlist = CAST(@fid AS VARCHAR(10))
END
IF @layer<>0
BEGIN
UPDATE [dnt_statistics] SET [totalpost]=[totalpost] - 1
UPDATE [dnt_forums] SET
[posts]=[posts] - 1,
[todayposts]=CASE
WHEN DATEPART(yyyy, @postdatetime)=DATEPART(yyyy,GETDATE()) AND DATEPART(mm, @postdatetime)=DATEPART(mm,GETDATE()) AND DATEPART(dd, @postdatetime)=DATEPART(dd,GETDATE()) THEN [todayposts] - 1
ELSE [todayposts]
END
WHERE (CHARINDEX(',' + RTRIM([fid]) + ',', ',' +
(SELECT @fidlist AS [fid]) + ',') > 0)
UPDATE [dnt_users] SET
[posts] = [posts] - 1
WHERE [uid] = @posterid
UPDATE [dnt_topics] SET [replies]=[replies] - 1 WHERE [tid]=@tid
DELETE FROM [dnt_posts1] WHERE [pid]=@pid
END
ELSE
BEGIN
SELECT @postcount = COUNT([pid]) FROM [dnt_posts1] WHERE [tid] = @tid
SELECT @todaycount = COUNT([pid]) FROM [dnt_posts1] WHERE [tid] = @tid AND DATEDIFF(d, [postdatetime], GETDATE()) = 0
UPDATE [dnt_statistics] SET [totaltopic]=[totaltopic] - 1, [totalpost]=[totalpost] - @postcount
UPDATE [dnt_forums] SET [posts]=[posts] - @postcount, [topics]=[topics] - 1,[todayposts]=[todayposts] - @todaycount WHERE (CHARINDEX(',' + RTRIM([fid]) + ',', ',' +(SELECT @fidlist AS [fid]) + ',') > 0)
UPDATE [dnt_users] SET
[posts] = [posts] - @postcount
WHERE [uid] = @posterid
DELETE FROM [dnt_posts1] WHERE [tid] = @tid
DELETE FROM [dnt_topics] WHERE [tid] = @tid
END
IF @layer<>0
BEGIN
SELECT TOP 1 @pid = [pid], @posterid = [posterid], @postdatetime = [postdatetime], @title = [title], @poster = [poster] FROM [dnt_posts1] WHERE [tid]=@tid ORDER BY [pid] DESC
UPDATE [dnt_topics] SET [lastposter]=@poster,[lastpost]=@postdatetime,[lastpostid]=@pid,[lastposterid]=@posterid WHERE [tid]=@tid
END
SELECT @lasttid = [lasttid] FROM [dnt_forums] WHERE [fid] = @fid
IF @lasttid = @tid
BEGIN
SELECT TOP 1 @pid = [pid], @tid = [tid],@lastforumposterid = [posterid], @title = [title], @postdatetime = [postdatetime], @poster = [poster] FROM [dnt_posts1] WHERE [fid] = @fid ORDER BY [pid] DESC
UPDATE [dnt_forums] SET
[lasttid]=@tid,
[lasttitle]=ISNULL(@title,''),
[lastpost]=@postdatetime,
[lastposter]=ISNULL(@poster,''),
[lastposterid]=ISNULL(@lastforumposterid,'0')
WHERE (CHARINDEX(',' + RTRIM([fid]) + ',', ',' +
(SELECT @fidlist AS [fid]) + ',') > 0)
SELECT TOP 1 @pid = [pid], @tid = [tid],@posterid = [posterid], @postdatetime = [postdatetime], @title = [title], @poster = [poster] FROM [dnt_posts1] WHERE [posterid]=@posterid ORDER BY [pid] DESC
UPDATE [dnt_users] SET
[lastpost] = @postdatetime,
[lastpostid] = @pid,
[lastposttitle] = ISNULL(@title,'')
WHERE [uid] = @posterid
END
~
不知怎么的,我的电脑无法发表评论..公司和家里都一样.提交没反应..
@怪怪
像你这样的思想是不是有些投机?
就像论坛积分互转之类的?难道允许A入账 B没有支出这种情况出现吗?
@Cat Chen
个人认为不稳定的产品还是不应该推出来
@stonezhu
discuz!NT2.0老早出了.
http://www.discuznt.com/opensource.html
@戏水
只是觉得,这种问题应该在产品说明中写明.让用户知道..不然就是一种欺骗....存在隐患