SQL Server 洗掉關聯資料庫表，可以用事務實作關聯表洗掉。
表1 和 表2 的關聯欄位是 Id。
#region 洗掉
/// <summary>
/// 洗掉
/// </summary>
/// <param name="Id">主鍵</param>
/// <returns></returns>
[HttpPost]
public JsonResult DeleteData(string Id)
{
int result = 0;
string msg = string.Empty;
var state = string.Empty;
IDictionary<string, SqlParameter[]> hsTable = new Dictionary<string, SqlParameter[]>();
//洗掉表1
string sqlUsage = @"delete Table1 WHERE ID = @ID";
SqlParameter[] parameters = {
new SqlParameter("ID", Id)
};
hsTable.Add(sqlUsage, parameters);
//洗掉表2
string sqlEuE = @"delete Table2 WHERE UID = @UID";
SqlParameter[] parameters2 = {
new SqlParameter("UID", Id)
};
hsTable.Add(sqlEuE, parameters2);
result = sqlHelper.ExecTran(hsTable); //添加到事務
if (result > 0) { msg = "洗掉成功"; state = "1"; } else { msg = "洗掉失敗"; state = "0"; }
return Json(new { result = result, msg = msg, state = state, id = Id }, JsonRequestBehavior.AllowGet);
}
#endregion
uj5u.com熱心網友回復：
感謝分享, 請問sqlHelper類的代碼能否分享一下.uj5u.com熱心網友回復：
using System;using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
namespace Common
{
public class SQLHelper
{
public SqlConnection conn = null;
public SqlCommand cmd = null;
public SqlDataReader sdr = null;
public SQLHelper()
{
string strconn = ConfigurationManager.ConnectionStrings["strconn"].ConnectionString;
conn = new SqlConnection(strconn);
}
private SqlConnection OpenConn()
{
if (conn.State == ConnectionState.Closed)
{
conn.Open();
}
return conn;
}
#region 關閉資料庫連接
/// <summary>
/// 關閉資料庫連接
/// </summary>
private void CloseConn()
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
}
#endregion
#region 提交事務
/// <summary>
/// 執行事務，回傳0或者1
/// </summary>
/// <param name="hsTable"></param>
/// <returns></returns>
public int ExecTran(IDictionary<string, SqlParameter[]> hsTable, CommandType ct = CommandType.Text)
{
OpenConn();
SqlTransaction tran = conn.BeginTransaction();//先實體SqlTransaction類，使用這個事務使用的是con 這個連接，使用BeginTransaction這個方法來開始執行這個事務
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.Transaction = tran;
cmd.CommandType = ct;
try
{
foreach (KeyValuePair<string, SqlParameter[]> keyValuePair in hsTable)
{
string cmdText = keyValuePair.Key.ToString();
SqlParameter[] cmdParms = keyValuePair.Value;
cmd.CommandText = cmdText;
cmd.Parameters.AddRange(cmdParms);
int num = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
}
tran.Commit();
return 1;
}
catch (Exception ex)
{
tran.Rollback();
return 0;
}
finally
{
CloseConn();
tran.Dispose();
}
}
/// <summary>
/// 執行事務，回傳0或者1
/// </summary>
/// <param name="sqlList">SQL陳述句串列</param>
/// <param name="cmdParms">SQL陳述句通用的引數</param>
/// <returns></returns>
public int ExecTran(List<string> sqlList, SqlParameter[] cmdParms, CommandType ct = CommandType.Text)
{
OpenConn();
SqlTransaction tran = conn.BeginTransaction();//先實體SqlTransaction類，使用這個事務使用的是con 這個連接，使用BeginTransaction這個方法來開始執行這個事務
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.Transaction = tran;
cmd.CommandType = ct;
try
{
foreach (string item in sqlList)
{
string cmdText = item;
cmd.CommandText = cmdText;
cmd.Parameters.AddRange(cmdParms);
cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
}
tran.Commit();
return 1;
}
catch (Exception ex)
{
tran.Rollback();
return 0;
}
finally
{
CloseConn();
tran.Dispose();
}
}
#endregion
uj5u.com熱心網友回復：
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/279116.html
標籤：應用實例
下一篇：K ettle