SQL Server delete relational database table, you can use transaction implementation associative table delete,

Table 1 and table 2 associated fields is Id,

# region delete
///& lt; Summary>
///delete
///& lt;/summary>
///& lt; Param name="Id" & gt; The primary key & lt;/param>
///& lt; Returns>
[HttpPost]
Public JsonResult DeleteData (string Id)
{
Int result=0;
String MSG=string. The Empty;
Var state=string. The Empty;
IDictionary
//delete table 1
String sqlUsage=@ "delete Table1 WHERE ID=@ ID";

SqlParameter [] the parameters={
New SqlParameter (" ID ", ID)

};
HsTable. Add (sqlUsage, parameters);

//delete table 2
String sqlEuE=@ "delete Table2 WHERE UID=@ UID";

SqlParameter [] parameters2={
New SqlParameter (" UID ", Id)

};
HsTable. Add (sqlEuE parameters2);

Result=sqlHelper. ExecTran (hsTable);//added to the transaction

If (result & gt; 0 {MSG="delete" success; State="1"; } else {MSG="delete failed"; State="0"; }
Return Json (new {result=result, MSG=MSG, state=the state that id=id}, JsonRequestBehavior. AllowGet);
}

# endregion

CodePudding user response:

Thanks for sharing, can you tell me the sqlHelper class code can share.

CodePudding user response:

using System;
using System.Collections.Generic;
Using System. The Configuration;
Using System. The Data;
Using System. The Data. SqlClient.
Using System. Linq;
Using System. The Web;

The namespace Common
{
Public class SQLHelper
{
Public SqlConnection conn=null;
Public SqlCommand CMD=null;
Public SqlDataReader SDR=null;
Public SQLHelper ()
{
String strconn=ConfigurationManager. ConnectionStrings [r]. "strconn" ConnectionString;
Conn=new SqlConnection (strconn);
}

Private SqlConnection OpenConn ()
{
If (conn. State==ConnectionState. Closed)
{
conn.Open();
}
Return the conn.
}

# region to close the database connection
///& lt; Summary>
///close the database connection
///& lt;/summary>
Private void CloseConn ()
{
If (conn. State==ConnectionState. Open)
{
Conn. Close ();
}
}
# endregion
# region to commit the transaction
///& lt; Summary>
///perform transactions, returns 0 or 1
///& lt;/summary>
///& lt; Param name="hsTable & gt;"
///& lt; Returns>
Public int ExecTran (IDictionary{
OpenConn ();
SqlTransaction tran=conn. BeginTransaction ();//instance SqlTransaction first class, the use of this transaction is used con this link, use the BeginTransaction this way to start to carry out the transaction
SqlCommand CMD=new SqlCommand ();
CMD. Connection=conn;
CMD. Transaction=tran;
Cmd.Com mandType=ct;
Try
{
The foreach (KeyValuePair{
String cmdText=keyValuePair. Key. The ToString ();
SqlParameter [] cmdParms=keyValuePair. Value;

Cmd.Com mandText=cmdText;
CMD. The Parameters. AddRange (cmdParms);
Int num=CMD. ExecuteNonQuery ();
CMD. The Parameters. The Clear ();
}

Tran.Com MIT ();
return 1;
}
The catch (Exception ex)
{
Tran. The Rollback ();
return 0;
}
The finally
{
CloseConn ();
Tran. The Dispose ();
}
}

///& lt; Summary>
///perform transactions, returns 0 or 1
///& lt;/summary>
///& lt; Param name="sqlList & gt;" SQL statement list & lt;/param>
///& lt; Param name="cmdParms & gt;" The parameters of the SQL statement general & lt;/param>
///& lt; Returns>
Public int ExecTran (List SqlList SqlParameter [] cmdParms, CommandType ct=CommandType. Text)
{
OpenConn ();
SqlTransaction tran=conn. BeginTransaction ();//instance SqlTransaction first class, the use of this transaction is used con this link, use the BeginTransaction this way to start to carry out the transaction
SqlCommand CMD=new SqlCommand ();
CMD. Connection=conn;
CMD. Transaction=tran;
Cmd.Com mandType=ct;
Try
{
The foreach (string item in sqlList)
{
String cmdText=item;

Cmd.Com mandText=cmdText;
CMD. The Parameters. AddRange (cmdParms);
CMD. ExecuteNonQuery ();
CMD. The Parameters. The Clear ();
}

Tran.Com MIT ();
return 1;
}
The catch (Exception ex)
{
Tran. The Rollback ();
return 0;
}
The finally
{
CloseConn ();
Tran. The Dispose ();
}
}
# endregion

CodePudding user response: