Recently I am writing a Blog program. Because the SQL server space is expensive... Access is used... but I still don't know about C # Access... I 'd like to learn more about Discuz! NT... could not find the transaction processing...
Read the Discuz of Access and SQL! No transactions were found in NT2.0 ~?
Don't transactions need to be processed anymore? How can we ensure data integrity? Is it too natural?
Isn't this irresponsible to users?
Or can't I find the processing code to ensure data integrity? Hope you can help ~
Appendix: Delete the post code of Access and SQL
Access:
/// <Summary>
/// Delete the post with the specified ID
/// </Summary>
/// <Param name = "pid"> post ID </param>
/// <Returns> Delete quantity </returns>
Public static int DeletePost (string posttableid, int pid)
{
// OleDbParameter [] prams = {
// Database. MakeInParam ("@ pid", OleDbType. Integer, 4, pid)
//};
# 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)
{
// -- Delete only one post
// -- Update the total number of replies to the Forum
StrSQL = "UPDATE [" + BaseConfigFactory. GetTablePrefix + "statistics] SET [totalpost] = [totalpost]-1 ";
Database. ExecuteNonQuery (CommandType. Text, strSQL );
// -- Update the total number of replies in the Forum
If (Convert. ToDateTime (postdatetime). tow.datestring () = DateTime. Now. tow.datestring ())
{
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 );
// -- Update the total number of user replies
StrSQL = "UPDATE [" + BaseConfigFactory. GetTablePrefix + "users] SET [posts] = [posts]-1 WHERE [uid] =" + posterid;
Database. ExecuteNonQuery (CommandType. Text, strSQL );
// -- Update the total number of replies to a topic
StrSQL = "UPDATE [" + BaseConfigFactory. GetTablePrefix + "topics] SET [replies] = [replies]-1 WHERE [tid] =" + tid;
Database. ExecuteNonQuery (CommandType. Text, strSQL );
// -- Delete a post
StrSQL = "delete from [" + BaseConfigFactory. GetTablePrefix + "posts" + posttableid + "] WHERE [pid] =" + pid;
Database. ExecuteNonQuery (CommandType. Text, strSQL );
}
Else
{
// -- Delete a topic
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 ());
// -- Update the topic and total number of posts
StrSQL = "UPDATE [" + BaseConfigFactory. GetTablePrefix + "statistics] SET [totaltopic] = [totaltopic]-1, [totalpost] = [totalpost]-" + postcount;
Database. ExecuteNonQuery (CommandType. Text, strSQL );
// -- Update Forum
StrSQL = "UPDATE [" + BaseConfigFactory. getTablePrefix + "forums] SET [posts] = [posts]-" + postcount + ", [topics] = [topics]-1, [todayposts] = [todayposts]-"+ todaycount +" WHERE [fid] in ("+ fidlist + ")";
Database. ExecuteNonQuery (CommandType. Text, strSQL );
// -- Update the total number of user replies
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 ();
// -- Update a user
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 stored procedure:
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
~
Somehow, my computer won't be able to post comments... the company is the same as home. No response is reported ..
@ Strange
Is there any speculation like you?
Is it like switching Forum points? Is it possible that A is allowed to account for B without expenditure?
@ Cat Chen
I personally think that unstable products should not be launched.
@ Stonezhu
Discuz! NT2.0 has arrived early.
Http://www.discuznt.com/opensource.html
@ Playwater
I just think that this problem should be stated in the product description to let users know... otherwise it is a kind of deception... there is a hidden danger.