Sql Server数据库多表关联删除
Sql Server数据库多表关联删除数据,在项目开发过程中经常遇到多级关联数据,一般出现分类数据下面子级成员。如果要进行主分类删除操作,同时又需要进行子集成员进行删除。这时和读取关联数据一样,需要依赖表与表的关联主键外键进行关联操作删除。继续 WPF的TreeView从数据库绑定数据 和 WPF的ComboBox关联数据绑定 文章示例中的数据关联进行删除操作,下面图例是本文示例执行效果。
在前文章也介绍了,数据库分别是CityData(一级城市列表)、CityItemData(城市子级分区)、CityChildItem(城市分区的地点),通过数据库视图和查询语句查询结果可以了解三个表的关系,下面图例,通过SELECT语句将三表查询出来,在图中分别用红色和绿色框选了它们之间的关联关系。
关联删除数据Sql脚本编写,通过CityId来删除指定城市以及城市分区的所有字表成员。CityData和CityItemData表可以直接直接用Delete语句WHERE CityId的值条件直接删除,CityChildItem删除需要将CityData和CityItemData两个表关联起来然后再WHERE CityId的值,这样三表三条删除语句,同时WHERE同一个CityId条件。下面是详细存储过程代码。
CREATE PROCEDURE dbo.DeleteCityDataIitemsById
(
@CityId int
)
AS
DELETE FROM CityChildItem
FROM CityItemData INNER JOIN
CityChildItem ON CityItemData.CityItemId = CityChildItem.CityItemId
WHERE (CityItemData.CityId = @CityId)
![]()
DELETE FROM CityItemData WHERE (CityId = @CityId)
![]()
DELETE FROM CityData WHERE (CityId = @CityId)
![]()
RETURN
在三条DELETE语句中可以看到同时WHERE条件都是CityId,定义的@CityId用以接收外部值。建立DeleteCityDataIitemsById存储过程之后,在数据集中添加新的查询,再TableAdapter查询配置导向中点选 使用现有的存储过程(E),选择DeleteCityDataIitemsById,返回方式无任何值。
下面在ControlData.cs类中加入定义DeleteCityItemsById(int id)方法,操作DeleteCityDataIitemsById方法。下面是详细代码。
/////////////Copyright (C) 遗昕 | weisim3.com 05.12.2012/////////////////////
/////////////Sql Server数据库多表关联删除///////////////////////////////////////
/////////////WPF的ComboBox关联数据绑定//////////////////////////////////////////
![]()
/// <summary>
/// DeleteCityItemsById -> 根据Id删除城市城市子集
/// </summary>
/// <param name="id">CityId</param>
/// <returns></returns>
public bool DeleteCityItemsById(int id)
{
try
{
CityData.DeleteCityDataIitemsById(id);
return true;
}
catch
{
return false;
}
}
下面再WebPage中实际应用,在ASP.NET中放入一个GirdView用来绑定一表CityData(一级城市列表),GirdView加入删除列;再放入两个Dropdownlist分别绑定CityData(一级城市列表)、CityItemData(城市子级分区)让两个Dropdownlist关联选取;再放入一个Label用来显示CityItemData的关联CityChildItem(城市分区的地点),下面是详细代码。
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="WebForm1.aspx.cs"
Inherits="CityService.WebForm1" %>
![]()
<%@ Register assembly="System.Web.Extensions, Version=1.0.61025.0,
Culture=neutral,PublicKeyToken=31bf3856ad364e35"
namespace="System.Web.UI" tagprefix="asp" %>
![]()
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
![]()
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>Sql Server数据库多表关联删除</title>
</head>
<body>
<form id="form1" runat="server">
<div>
![]()
<asp:GridView ID="GridView1" runat="server" DataKeyNames="CityId"
EnableModelValidation="True" onrowdeleting="GridView1_RowDeleting">
<Columns>
<asp:TemplateField ShowHeader="False">
<ItemTemplate>
<asp:LinkButton ID="LinkButton1" runat="server"
CausesValidation="False" CssClass="btnlink"
CommandName="Delete" Text="刪除"
OnClientClick="return confirm('是否刪除?无法恢复!!');">
</asp:LinkButton>
</ItemTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>
![]()
<br />
<asp:ScriptManager ID="ScriptManager1" runat="server">
</asp:ScriptManager>
<asp:UpdatePanel ID="UpdatePanel1" runat="server">
<ContentTemplate>
<asp:DropDownList ID="DropDownList1" runat="server"
onselectedindexchanged="DropDownList1_SelectedIndexChanged"
AutoPostBack="True">
</asp:DropDownList>
<asp:DropDownList ID="DropDownList2" runat="server" AutoPostBack="True"
onselectedindexchanged="DropDownList2_SelectedIndexChanged">
</asp:DropDownList>
<asp:Label ID="Label1" runat="server" Text="Label" ForeColor="red" />
</ContentTemplate>
</asp:UpdatePanel>
![]()
<br />
![]()
<br />
![]()
</div>
</form>
</body>
</html>
using System;
using System.Collections.Generic;
![]()
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
![]()
/////////////Copyright (C) 遗昕 | weisim3.com 05.12.2012//////////////////////
/////////////Sql Server数据库多表关联删除//////////////////////////////////////
![]()
namespace CityService
{
/// <summary>
/// Sql Server数据库多表关联删除
/// </summary>
public partial class WebForm1 : System.Web.UI.Page
{
ControlData MyData = new ControlData();
protected void Page_Load(object sender, EventArgs e)
{
GridView1.DataSource = MyData.CityDatas();
GridView1.DataBind();
if (!IsPostBack)
{
DropDownList1.DataSource = MyData.MasterCity(true);
DropDownList1.DataTextField = "CityName";
DropDownList1.DataValueField = "CityId";
DropDownList1.DataBind();
DropDownList1.SelectedValue = "0";
![]()
Label1.Text = "-地点-";
}
![]()
if (Convert.ToInt32(DropDownList1.SelectedValue) == 0)
{
List<CityInfo> list = new List<CityInfo>();
list.Add(new CityInfo() { CityItmeId = 0, CityItmeName = "-城市分区-" });
![]()
DropDownList2.DataSource = list;
DropDownList2.DataTextField = "CityItmeName";
DropDownList2.DataValueField = "CityItmeId";
DropDownList2.DataBind();
DropDownList2.SelectedValue = "0";
}
![]()
![]()
![]()
//foreach (DataRow row in MyData.CityData.GetData())
//{
// Response.Write( MyData.CityData.GetData()[2][0].ToString());
//}
}
![]()
protected void GridView1_RowDeleting(object sender, GridViewDeleteEventArgs e)
{
int Id = Convert.ToInt32(GridView1.DataKeys[e.RowIndex].Value);
if (MyData.DeleteCityItemsById(Id))
{
GridView1.DataSource = MyData.CityDatas();
GridView1.DataBind();
}
}
![]()
protected void DropDownList1_SelectedIndexChanged(object sender, EventArgs e)
{
![]()
if (Convert.ToInt32(DropDownList1.SelectedValue) == 0)
{
List<CityInfo> list = new List<CityInfo>();
list.Add(new CityInfo() { CityItmeId = 0, CityItmeName = "-城市分区-" });
![]()
DropDownList2.DataSource = list;
DropDownList2.DataTextField = "CityItmeName";
DropDownList2.DataValueField = "CityItmeId";
DropDownList2.DataBind();
DropDownList2.SelectedValue = "0";
Label1.Text = "-地点-";
}
else
{
DropDownList2.DataSource =
MyData.CityItem(Convert.ToInt32(DropDownList1.SelectedValue));
DropDownList2.DataTextField = "CityItmeName";
DropDownList2.DataValueField = "CityItmeId";
DropDownList2.DataBind();
}
}
![]()
protected void DropDownList2_SelectedIndexChanged(object sender, EventArgs e)
{
int selectValue = Convert.ToInt32(DropDownList2.SelectedValue);
if (MyData.CityChildItemById(selectValue).Count > 0)
{
foreach (DataRow row in
MyData.CityChildItemById(selectValue))
{
![]()
Label1.Text = row["CityChildItemName"].ToString();
![]()
![]()
}
}
else
{
Label1.Text = Label1.Text = "-未设地点-";
}
![]()
}
}
}
本文示例文件与WPF的TreeView从数据库绑定数据 和 WPF的ComboBox关联数据绑定一致,在数据库中加入了DeleteCityDataIitemsById存储过程,然后再DataSetCity.xsd中间加入DeleteCityDataIitemsById存储过程;然后在ControlData.cs类中封装DeleteCityItemsById的操作方法。