我现在有两个表,Corp(CorpID,CorpName)和Car(CarID,CorpID,CarName),其中CorpID和CarID分别是两个表的主键。
假如我现在想删除Corp表里的某条数据,但是在Car表中该CorpID对应的还有数据。这时我想给出一个提示,不能删除,该怎么实现。
假如我现在想删除Corp表里的某条数据,但是在Car表中该CorpID对应的还有数据。这时我想给出一个提示,不能删除,该怎么实现。
@auto_Id int
as
if not exists(select 1 from [Corp],[Car] where Corp.CorpID = Car.CorpID and Corp.[CorpID]=@CorpID)
print '0'
else
print '1'如果存在在UI提示将子项移至别的大项或者连同删除子项都可
Where name = 'proc_abc' AND type = 'P')
Drop PROCEDURE proc_abc
GO
create proc proc_abc
@CorpID int
as
if not exists(select 1 from [Corp],[Car] where Corp.CorpID = Car.CorpID and Corp.[CorpID]=@CorpID)
print '0'
else
print '1'sql稍微修改下
if(null == um.GetCarByCorpID(CorpID))
{
rm.DeleteCorp();
}
else
{
ScriptManager.RegisterStartupScript(this,GetType(),"DeleteRole","alert('该Corp有引用,不能删除!')",true);
}
SqlConnection myConn = new SqlConnection(System.Configuration.ConfigurationSettings.AppSettings["connectionString"]);
string mySel="SELECT count(*) as iCount from chanpin where cp_lx='"+aaa+“‘”;
SqlCommand myCmd1=new SqlCommand(mySel,myConn);
myCmd1.Connection.Open();
SqlDataReader Dr1=myCmd1.ExecuteReader();
Dr1.Read();
string Count=Dr1["iCount"].ToString();
Dr1.Close();
myCmd1.Connection.Close();
if(Count!="0")
{
Response.Write("<script>alert(\"该类型还有产品,不能删除,要删除请先清空此分类下的所有产品!\");</script>");
}
else
{
String deleteCmd2 = "DELETE from chanpin where cp_lx =@xiao+“'";
SqlCommand myCommand2 = new SqlCommand(deleteCmd2, MyConnection);
myCommand2.Parameters.Add(new SqlParameter("@xiao", 115));
myCommand2.Parameters["@xiao"].Value = aaa;
myCommand2.Connection.Open();
myCommand2.ExecuteNonQuery();
myCommand2.Connection.Close();
//
String deleteCmd = "DELETE from chanpin_lx where lx_id = @Id";
SqlCommand myCommand = new SqlCommand(deleteCmd, MyConnection);
myCommand.Parameters.Add(new SqlParameter("@Id", 115));
myCommand.Parameters["@Id"].Value = DataGrid1.DataKeys[(int)e.Item.ItemIndex];
myCommand.Connection.Open();
myCommand.ExecuteNonQuery();
myCommand.Connection.Close();
//
DataGridDataBind();
}
你删除Corp表中的一条数据肯定有 Corp_id吧,在执行删除方法之前做如下判断,你根据Corp_id 先在Car 中查询(select * from Car where Corp_id=@p0),
如果查询结果为空说明Car表中没有Corp_id的关联数据,允许执行删除方法;如果结果不为空,则说明有关联数据,给出提示不允许删除。
数据访问层
public Car GetCarByCorpID(int CorpID)
{
string sql = string.Format("select * from Car where CorpID='{0}'",CorpID); SqlConnection conn = new SqlConnection(“数据库连接字符串”);
SqlCommand cmd = new SqlCommand(sql,conn); try
{
conn.Open();
SqlDataReader reader = cmd.ExecuteReader();
if(reader.Read())
{
Car car= new Car ();
car.CarID= Convert.ToInt32(reader["CarID"].ToString());
car.CorpID= Convert.ToInt32(reader["CorpID"].ToString());
car.CarName= reader["CarName"].ToString();
reader.Close();
}
}
catch(Exception ex)
{
throw ex;
}
finally
{
conn.Close();
conn.Dispose();
}
return role;
}
BLL层方法:
//先要添加DAL层的引用,实例DAL层
CarService cs = new CarService();
public Car GetCarByCorpID(int CorpID)
{
return cs.GetCarByCorpID(CorpID);
}页面判断:
//判断另一个表是否有引用 ,如果是空就是没有引用,可以删,不为空,给一个提示
if(null == um.GetCarByCorpID(CorpID))
{
rm.DeleteCorp();
}
else
{
ScriptManager.RegisterStartupScript(this,GetType(),"DeleteRole","alert('该Corp有引用,不能删除!')",true);
}
select
count(b.cropId)
from Corp a right join Car b on a.cropId=b.cropId
where a.cropId=删除的Id