SQL Server 删除关联数据库表

heyingss 2021-04-22 10:56:19
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
...全文
81 2 收藏 3
写回复
3 条回复
吉普赛的歌 04月22日
回复 点赞
heyingss 04月22日
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
回复 点赞
唐诗三百首 04月22日
感谢分享, 请问sqlHelper类的代码能否分享一下.
回复 点赞
发动态
发帖子
应用实例
创建于2007-09-28

1.1w+

社区成员

6.8w+

社区内容

MS-SQL Server 应用实例
社区公告
暂无公告