整理一下级联更新和删除 c#调用返回值
use master
go
IF exists(select 1 from sysdatabases where name='temp')
BEGIN DROP DATABASE temp
END
create database temp
go
use temp
go
--drop table ProductInfo
create table ProductInfo
(ProductId int primary key ,ProductName varchar(20),
)create table ProductDetails
(id int identity(1,1) primary key,num varchar(100) , ProductId int,foreign key (ProductId) references ProductInfo(ProductId) on delete cascade on update cascade
)insert ProductInfo values (1,'Think')
insert ProductInfo values(2,'TCL')
insert ProductInfo values(3,'HTC')insert ProductDetails values('T420',1)
insert ProductDetails values('Xo1',1)
insert ProductDetails values('TVoo1',2)
insert ProductDetails values('TPhone',2)
insert ProductDetails values('One',3)
insert ProductDetails values('Buffer',3)alter table 表名
add constraint 外键名
foreign key(字段名) references 主表名(字段名)
on delete cascade --删除
on update cascade --更新--查看现有数据
select * from ProductInfo
select * from ProductDetails--更改
update ProductInfo set ProductId=5 where ProductName='Think'
select * from ProductInfo
select * from ProductDetails--删除
delete from ProductInfo where ProductId=5
select * from ProductInfo
select * from ProductDetails
第一种方法:
C#代码:
protected void btnBack_Click(object sender, EventArgs e)
{//调用存储过程stringconStr=ConfigurationManager.ConnectionStrings["NorthwindConnectionString"].ToString();SqlConnection conn = new SqlConnection(conStr);SqlCommand cmd = new SqlCommand();cmd.CommandText = "MyProc";cmd.CommandType = CommandType.StoredProcedure;cmd.Connection=conn;conn.Open();SqlParameter sp = new SqlParameter("@ID", SqlDbType.Int);sp.Value = int.Parse("3");cmd.Parameters.Add(sp);//定义输出参数SqlParameter returnValue = new SqlParameter("@returnValue", SqlDbType.Int);returnValue.Direction = ParameterDirection.ReturnValue;cmd.Parameters.Add(returnValue);cmd.ExecuteNonQuery(); conn.Close();}
存储过程如下:
create procedure MyProc
(@ID int
)
asreturn 1go
注意,(return)这种方式 只能返加数值类型第二种方法:
protected void btnBack_Click(object sender, EventArgs e)
{//调用存储过程string conStr = System.Configuration.ConfigurationManager.ConnectionStrings["NorthwindConnectionString"].ToString();SqlConnection conn = new SqlConnection(conStr);SqlCommand cmd = new SqlCommand();cmd.CommandText = "MyProc";cmd.CommandType = CommandType.StoredProcedure;cmd.Connection=conn;conn.Open();SqlParameter sp = new SqlParameter("@ID", SqlDbType.Int);sp.Value = int.Parse("3");cmd.Parameters.Add(sp);//定义输出参数sp = new SqlParameter("@outputValue", SqlDbType.NVarChar,50);sp.Direction = ParameterDirection.Output;cmd.Parameters.Add(sp);cmd.ExecuteNonQuery();conn.Close();}存储过程如下:
alter procedure MyProc
(@ID int,@outputValue nvarchar(50) output)
asSelect @outputValue='aa'
go
