整理一下级联更新和删除 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
)
as
return
1
go
注意,(
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
)
as
Select @outputValue=
'aa'
go
|
本文转自lpxxn博客园博客，原文链接：http://www.cnblogs.com/li-peng/p/3254982.html
，如需转载请自行联系原作者
![[MySQL]存储过程](https://ucc.alicdn.com/3mfxe2r26qbaq/developer-article1632621/20241101/f9e47d1cdc9947d492b09f397a02c7b6.jpeg?x-oss-process=image/resize,h_160,m_lfit)
![2024Mysql And Redis基础与进阶操作系列（9）作者——LJS[含MySQL存储过程之局部、系统变量、参数传递、流程控制-判断/case具体详步骤；注意点及常见报错问题所对应的解决方法]](https://ucc.alicdn.com/pic/developer-ecology/56pfyutwi4tv4_c53df19098d2427391fe36eeb35f84c8.png?x-oss-process=image/resize,h_160,m_lfit)