資料表及資料準備：
create table Member
(
MemberId int primary key identity(1,1),
MemberAccount nvarchar(20) unique,
MemberPwd nvarchar(20),
MemberName nvarchar(20),
MemberPhone nvarchar(20)
)
truncate table Member
insert into Member(MemberAccount,MemberPwd,MemberName,MemberPhone)
values('liubei','123456','劉備','4659874564')
insert into Member(MemberAccount,MemberPwd,MemberName,MemberPhone)
values('guanyu','123456','關羽','42354234124')
insert into Member(MemberAccount,MemberPwd,MemberName,MemberPhone)
values('zhangfei','123456','張飛','41253445')
insert into Member(MemberAccount,MemberPwd,MemberName,MemberPhone)
values('zhangyun','123456','趙雲','75675676547')
insert into Member(MemberAccount,MemberPwd,MemberName,MemberPhone)
values('machao','123456','馬超','532523523')
本文以下內容都藉助於前面封裝的DBHelper類
一、呼叫exec語句執行儲存過程
由於在SQL SERVER內部呼叫儲存過程使用的方式是:
exec 儲存過程名 引數1,引數2,引數3...
所以我們可以在C#中呼叫exec的sql語句，讓此sql語句去呼叫儲存過程，嚴格來說，此種方式並不能稱之為C#呼叫儲存過程，本質上仍然是呼叫的sql語句。
示例：
需求：採用呼叫儲存過程的方式實現資料的顯示以及資料的新增。
主要程式碼：
SQL儲存過程程式碼：
--查詢Member表所有資料的儲存(沒有引數)
create proc procSelectMember
as
select * from Member
go
exec procSelectMember
--新增會員資訊（有輸入引數）
create proc procInsertMember
@acc nvarchar(20),
@pwd nvarchar(20),
@memName nvarchar(20),
@memPhone nvarchar(20)
as
insert into Member(MemberAccount,MemberPwd,MemberName,MemberPhone)
values(@acc,@pwd,@memName,@memPhone)
go
exec procInsertMember 'sunwukong','123456','孫悟空','13554856985'
資料顯示C#程式碼：
private void BindData()
{
DBHelper.PrepareSql("exec procSelectMember");
this.dataGridView1.DataSource = DBHelper.ExecQuery();
}
private void Form1_Load(object sender, EventArgs e)
{
BindData();
}
資料新增C#程式碼：
private void btAdd_Click(object sender, EventArgs e)
{
DBHelper.PrepareSql(string.Format("exec procInsertMember '{0}','{1}','{2}','{3}'"
,this.txtAccount.Text,this.txtPwd.Text,this.txtNickName.Text,this.txtPhone.Text));
DBHelper.ExecNonQuery();
}
二、直接呼叫儲存過程
呼叫儲存過程需要將CommandType執行命令型別設定為CommandType.StoredProcedure儲存過程。
（1）呼叫沒有引數的儲存過程
需求：實現資料的顯示。
主要程式碼：
SQL儲存過程程式碼：
--查詢Member表所有資料的儲存(沒有引數)
create proc procSelectMember
as
select * from Member
go
--呼叫
exec procSelectMember
為了支援儲存過程，給DBHelper新增方法：
public static void PrepareProc(string sql)
{
OpenConn(); //開啟資料庫連線
adp = new SqlDataAdapter(sql, conn);
adp.SelectCommand.CommandType = CommandType.StoredProcedure;
}
窗體程式碼：
private void BindData()
{
DBHelper.PrepareProc("procSelectMember");
this.dataGridView1.DataSource = DBHelper.ExecQuery();
}
private void Form1_Load(object sender, EventArgs e)
{
BindData();
}
（2）呼叫有輸入引數的儲存過程
需求：實現資料的新增。
主要程式碼：
SQL儲存過程程式碼：
--新增會員資訊（有輸入引數）
create proc procInsertMember
@acc nvarchar(20),
@pwd nvarchar(20),
@memName nvarchar(20),
@memPhone nvarchar(20)
as
insert into Member(MemberAccount,MemberPwd,MemberName,MemberPhone)
values(@acc,@pwd,@memName,@memPhone)
go
--呼叫
exec procInsertMember 'sunwukong','123456','孫悟空','13554856985'
窗體程式碼：
private void btAdd_Click(object sender, EventArgs e)
{
DBHelper.PrepareProc("procInsertMember");
DBHelper.SetParameter("acc", this.txtAccount.Text);
DBHelper.SetParameter("pwd",this.txtPwd.Text);
DBHelper.SetParameter("memName", this.txtNickName.Text);
DBHelper.SetParameter("memPhone", this.txtPhone.Text);
DBHelper.ExecNonQuery();
}
（3）呼叫有輸入和輸出引數的儲存過程
需求：輸入使用者名稱，點選"查詢電話"按鈕，在下面顯示姓名和號碼。
主要程式碼：
SQL儲存過程：
--根據賬號查詢姓名和電話（有輸入引數，有輸出引數）
create proc procGetInfoByAcc
@acc nvarchar(20),
@memName nvarchar(20) output,
@phone nvarchar(20) output
as
select @memName = (select MemberName from Member where MemberAccount=@acc)
select @phone = (select MemberPhone from Member where MemberAccount=@acc)
go
--呼叫
declare @name nvarchar(20)
declare @phone nvarchar(20)
exec procGetInfoByAcc 'machao',@name output,@phone output
select @name,@phone
為了支援輸出引數，給DBHelper新增方法：
/// <summary>
/// 設定輸出引數（不指定長度，適合非字串）
/// </summary>
/// <param name="parameterName">引數名稱</param>
/// <param name="dbType">引數型別</param>
public static void SetOutParameter(string parameterName, SqlDbType dbType)
{
parameterName = "@" + parameterName.Trim();
SqlParameter parameter = new SqlParameter(parameterName, dbType);
parameter.Direction = ParameterDirection.Output;
adp.SelectCommand.Parameters.Add(parameter);
}
/// <summary>
/// 設定輸出引數（指定長度，適合字串）
/// </summary>
/// <param name="parameterName">引數名稱</param>
/// <param name="dbType">引數型別</param>
/// <param name="size">引數長度</param>
public static void SetOutParameter(string parameterName, SqlDbType dbType, int size)
{
parameterName = "@" + parameterName.Trim();
SqlParameter parameter = new SqlParameter(parameterName, dbType, size);
parameter.Direction = ParameterDirection.Output;
adp.SelectCommand.Parameters.Add(parameter);
}
/// <summary>
/// 獲取引數內容值
/// </summary>
/// <param name="parameterName">引數名稱</param>
/// <returns>引數值</returns>
public static object GetParameter(string parameterName)
{
parameterName = "@" + parameterName.Trim();
return adp.SelectCommand.Parameters[parameterName].Value;
}
窗體程式碼：
private void btSearch_Click(object sender, EventArgs e)
{
DBHelper.PrepareProc("procGetInfoByAcc");
DBHelper.SetParameter("acc", this.txtAccount.Text);
DBHelper.SetOutParameter("memName", SqlDbType.NVarChar, 20);
DBHelper.SetOutParameter("phone", SqlDbType.NVarChar, 20);
DBHelper.ExecNonQuery();
this.lblName.Text = "姓名：" + DBHelper.GetParameter("memName").ToString();
this.lblPhone.Text = "電話：" + DBHelper.GetParameter("phone").ToString();
}
（4）呼叫有輸入輸出引數的儲存過程
需求：密碼升級，傳入使用者名稱和密碼；如果使用者名稱密碼正確，並且密碼長度<8，自動升級成8位密碼。
主要程式碼：
SQL儲存過程（SQLSERVER中output引數直接傳入值即可以做輸入引數，也可以做輸出引數）：
--密碼升級，傳入使用者名稱和密碼，如果使用者名稱密碼正確，並且密碼長度<8，自動升級成8位密碼
--有輸入輸出引數（密碼作為輸入引數也作為輸出引數）
select FLOOR(RAND()*10) --0-9之間隨機數
create proc procPwdUpgrade
@acc nvarchar(20),
@pwd nvarchar(20) output
as
if not exists(select * from Member where MemberAccount=@acc and MemberPwd=@pwd)
set @pwd = ''
else
begin
if len(@pwd) < 8
begin
declare @len int = 8- len(@pwd)
declare @i int = 1
while @i <= @len
begin
set @pwd = @pwd + cast(FLOOR(RAND()*10) as varchar(1))
set @i = @i+1
end
update Member set MemberPwd = @pwd where MemberAccount=@acc
end
end
go
--呼叫
declare @pwd nvarchar(20) = '123456'
exec procPwdUpgrade 'liubei',@pwd output
select @pwd
為了支援輸入輸出引數，給DBHelper新增方法：
/// <summary>
/// 設定輸入輸出引數（不指定長度，適合非字串）
/// </summary>
/// <param name="parameterName">引數名稱</param>
/// <param name="dbType">引數型別</param>
public static void SetInOutParameter(string parameterName, SqlDbType dbType, object parameterValue)
{
parameterName = "@" + parameterName.Trim();
SqlParameter parameter = new SqlParameter(parameterName, dbType);
parameter.Value = parameterValue;
parameter.Direction = ParameterDirection.InputOutput;
adp.SelectCommand.Parameters.Add(parameter);
}
/// <summary>
/// 設定輸入輸出引數（指定長度，適合字串）
/// </summary>
/// <param name="parameterName">引數名稱</param>
/// <param name="dbType">引數型別</param>
/// <param name="size">引數長度</param>
public static void SetInOutParameter(string parameterName, SqlDbType dbType, int size, object parameterValue)
{
parameterName = "@" + parameterName.Trim();
SqlParameter parameter = new SqlParameter(parameterName, dbType, size);
parameter.Value = parameterValue;
parameter.Direction = ParameterDirection.InputOutput;
adp.SelectCommand.Parameters.Add(parameter);
}
窗體程式碼：
//密碼升級，傳入使用者名稱和密碼，
//如果使用者名稱密碼正確，並且密碼長度<8，自動升級成8位密碼
private void btUpgrade_Click(object sender, EventArgs e)
{
DBHelper.PrepareProc("procPwdUpgrade");
DBHelper.SetParameter("acc", this.txtAccount.Text);
DBHelper.SetInOutParameter("pwd", SqlDbType.NVarChar, 20, this.txtPwd.Text);
DBHelper.ExecNonQuery();
this.lblNewPwd.Text = DBHelper.GetParameter("pwd").ToString();
}
（5）呼叫有返回值的儲存過程
SQLSERVER儲存過程返回值只能是整數。
需求：實現資料的新增，由SQLSERVER返回執行的狀態。
主要程式碼：
SQL儲存過程程式碼：
--新增會員資訊（有返回值）
create proc procInsertMember
@acc nvarchar(20),
@pwd nvarchar(20),
@memName nvarchar(20),
@memPhone nvarchar(20)
as
insert into Member(MemberAccount,MemberPwd,MemberName,MemberPhone)
values(@acc,@pwd,@memName,@memPhone)
declare @myErr int = @@error
if @myErr = 0
return 1
else if @myErr = 2627 --唯一約束
return -1
else
return -100
go
--呼叫
declare @return int
exec @return = procInsertMember 'sunwukong','123456','孫悟空','13554854785'
print @return
為了支援返回值，給DBHelper新增方法：
/// <summary>
/// 設定返回值引數
/// </summary>
/// <param name="parameterName">引數名稱</param>
public static void SetReturnParameter(string parameterName)
{
parameterName = "@" + parameterName.Trim();
SqlParameter parameter = new SqlParameter();
parameter.ParameterName = parameterName;
parameter.Direction = ParameterDirection.ReturnValue;
adp.SelectCommand.Parameters.Add(parameter);
}
窗體程式碼：
private void btAdd_Click(object sender, EventArgs e)
{
try
{
DBHelper.PrepareProc("procInsertMember");
DBHelper.SetParameter("acc", this.txtAccount.Text);
DBHelper.SetParameter("pwd", this.txtPwd.Text);
DBHelper.SetParameter("memName", this.txtNickName.Text);
DBHelper.SetParameter("memPhone", this.txtPhone.Text);
DBHelper.SetReturnParameter("returnValue");
DBHelper.ExecNonQuery();
int result = (int)DBHelper.GetParameter("returnValue");
if (result == 1)
MessageBox.Show("新增成功!");
}
catch (Exception ex)
{
int result = (int)DBHelper.GetParameter("returnValue");
if (result == -1)
MessageBox.Show("使用者名稱重名了，違反了唯一約束!");
if (result == -100)
MessageBox.Show(ex.Message);
}
}