SQL Server存儲過程在C#中調(diào)用的簡單實現(xiàn)方法
0. 簡介
【定義】：存儲過程（Stored Procedure） 是在大型數(shù)據(jù)庫系統(tǒng)中，一組為了完成特定功能的SQL 語句集，它存儲在數(shù)據(jù)庫中，一次編譯后永久有效，用戶通過指定存儲過程的名字并給出參數(shù)（如果該存儲過程帶有參數(shù)）來執(zhí)行它。
【優(yōu)缺點】：存儲過程優(yōu)缺點都非常的明顯！幾乎每一篇討論存儲過程的文字，都是會說其他優(yōu)點是balabala,缺點是balabala，然而最后作者的結論都是：“我不推薦使用存儲過程”。
具體的存儲過程的優(yōu)缺點這里就不詳述了！
公司舊項目使用存儲過程實現(xiàn)業(yè)務邏輯，沒辦法只能研究了一下 🙃！
閑言碎語不要講，書歸正傳，下面就開始存儲過程！
1. 語法細節(jié)
變量與變量之間使用逗號隔開，語句結尾無標點符號
聲明變量:declare @variate_name variate_type,例如聲明并賦值：declare @name nvarchar(50) ='shanzm'
變量賦值:set @variate_name =value
打印變量：print @variate_name
begin……end 之間的SQL語句稱之為一個代碼塊
可以使用if……else實現(xiàn)邏輯判斷
創(chuàng)建存儲過程：create procedure pro_name
執(zhí)行存儲過程：execute pro_name
輸出參數(shù):存儲過程返回的是SQL語句查閱結果，在定義參數(shù)后，添加output,設置為一個輸出參數(shù)（和C#中輸出參數(shù)類似），相當于多了一個返回值！
創(chuàng)建存儲過程的基本形式：
create procedure pro_name_tableName @param1 param1_type, @param2 param2_type, as begin --sql語句 end go
2. 示例1：模擬轉(zhuǎn)賬
①示例背景：使用存儲過程，模擬在一張存款表中實現(xiàn)用戶與用戶之間的轉(zhuǎn)賬
②準備工作1：在數(shù)據(jù)庫中創(chuàng)建表szmBank
CREATE TABLE [dbo].[szmBank]( [Id] [bigint] IDENTITY(1,1) NOT NULL, [Balance] [decimal](18, 0) NOT NULL
添加一些測試數(shù)據(jù)：
Id Balance
--------------- ----------------
1 1000
2 2000
3 3000
③準備工作2：封裝C#代碼中的SQL輔助類SqlHelper
注意封裝的時候要有一個CommandType參數(shù)，決定是執(zhí)行SQL語句還是存儲過程，
CommandType是一個枚舉類型，其中Text值為執(zhí)行SQL語句，StoreProcedure為執(zhí)行存儲過程
具體封裝細節(jié)這里就不詳述了。
找到了2年前我封裝的一個SqlHelper.cs,常規(guī)使用沒有任何問題，僅供參考：
#region
// ===============================================================================
// Project Name :
// Project Description :
// ===============================================================================
// Class Name : SqlHelper
// Class Version : v1.0.0.0
// Class Description : SQL語句輔助類
// CLR : 4.0.30319.18408
// Author : shanzm
// Create Time : 2018-8-14 18:22:59
// Update Time : 2018-8-14 18:22:59
// ===============================================================================
// Copyright © SHANZM-PC 2018 . All rights reserved.
// ===============================================================================
#endregion
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
namespace _16StoreProcedure
{
public class SqlHelper
{
private static readonly string connStr =
ConfigurationManager.ConnectionStrings["connStr"].ConnectionString;
/// <summary>
/// 返回查詢結果的的表
/// </summary>
/// <param name="sql">SQL語句或存儲過程</param>
/// <param name="type">執(zhí)行類型</param>
/// <param name="param">參數(shù)</param>
/// <returns></returns>
public static DataTable GetDataTable(string sql, CommandType type, params SqlParameter[] param)
{
using (SqlConnection conn = new SqlConnection(connStr))
{
using (SqlDataAdapter adapter = new SqlDataAdapter(sql, conn))
{
if (param != null)
{
adapter.SelectCommand.Parameters.AddRange(param);
}
adapter.SelectCommand.CommandType = type;
DataTable da = new DataTable();
adapter.Fill(da);
return da;
}
}
}
/// <summary>
/// 返回影響行數(shù)
/// </summary>
/// <param name="sql">SQL語句或存儲過程</param>
/// <param name="type">執(zhí)行類型</param>
/// <param name="param">參數(shù)</param>
/// <returns></returns>
public static int ExecuteNonquery(string sql, CommandType type, params SqlParameter[] param)
{
using (SqlConnection conn = new SqlConnection(connStr))
{
using (SqlCommand cmd = new SqlCommand(sql, conn))
{
if (param != null)
{
cmd.Parameters.AddRange(param);
}
cmd.CommandType = type;
conn.Open();
return cmd.ExecuteNonQuery();
}
}
}
/// <summary>
/// 返回查詢結果的第一行第一個單元格的數(shù)據(jù)
/// </summary>
/// <param name="sql">SQL語句或存儲過程</param>
/// <param name="type">執(zhí)行類型</param>
/// <param name="param">參數(shù)</param>
/// <returns></returns>
public static object ExecuteScalar(string sql, CommandType type, params SqlParameter[] param)
{
using (SqlConnection conn=new SqlConnection (connStr ))
{
using (SqlCommand cmd=new SqlCommand (sql,conn))
{
if (param !=null )
{
cmd.Parameters.AddRange(param);
}
cmd.CommandType = type ;
conn.Open();
return cmd.ExecuteScalar();
}
}
}
}
}
④編寫存儲過程：
在數(shù)據(jù)庫中：指定數(shù)據(jù)庫-->可編程性-->存儲過程-->右鍵：新建-->存儲過程：
SQL Server中編寫的SQL語句沒有默認的格式化，所有代碼排版按照我自己習慣進行Tab縮進
建議放到編輯器中查看下面的存儲過程，會好看一些！
SQL大小寫不敏感，我習慣小寫，方便閱讀！
-- =============================================
-- Author: shanzm
-- Create date: 2020年5月2日 19:56:51
-- Description: 模擬賬戶之間轉(zhuǎn)賬
-- =============================================
create procedure pro_transfer_szmbank
@from bigint,
@to bigint,
@balance decimal(18,0),
@returnNum int output--(1表示轉(zhuǎn)賬成功，2表示失敗，3表示余額不足)
as
begin
--判斷轉(zhuǎn)出賬戶是否有足夠的金額
declare @money decimal(18,0)
select @money=Balance from dbo.szmBank where Id=@from;
if @money-@balance>=0.1
--開始轉(zhuǎn)賬
begin
begin transaction
declare @sum int =0
--轉(zhuǎn)出賬戶扣錢
update szmBank set balance=balance-@balance where id=@from
set @sum=@sum+@@error
--轉(zhuǎn)入賬戶加錢
update szmBank set balance=balance+@balance where id=@to
set @sum=@sum+@@error
--判斷是否成功
if @sum<>0
begin
set @returnNum=2--轉(zhuǎn)賬失敗
rollback
end
else
begin
set @returnNum=1--轉(zhuǎn)賬成功
commit
end
end
else
begin
set @returnNum=3--余額不足
end
end
go
在數(shù)據(jù)庫中執(zhí)行測試（F5）：
--執(zhí)行測試： declare @ret int execute pro_transfer_szmbank @from='1', @to='2', @balance='10', @returnNum=@ret output--注意輸出參數(shù)在執(zhí)行語句中也是要表明"output" print @ret --結果是打印:1,即存儲過程實現(xiàn)成功
【注意】：
- 我們需要查看某個存儲過程，則可以使用數(shù)據(jù)中自帶的存儲過程查看：
- sp_helptext pro_transfer_szmBank
- 修改現(xiàn)有的存儲過程，右鍵存儲過程-->修改：顯示的存儲過程只是把創(chuàng)建存儲過程中的create變?yōu)榱薬lert
- 可以在SQL Server的SQL窗口選中某些SQL語句，點擊執(zhí)行，即執(zhí)行選中的SQL語句
⑤控制臺中測試
新建一個控制臺項目，在配置文件中添加連接字符串
因為封裝的SqlHelper中需要從配置文件中讀取數(shù)據(jù)庫連接字符串，所以添加引用：System.Configuration
static void Main(string[] args)
{
//轉(zhuǎn)出賬戶的Id
int from = 1;
//轉(zhuǎn)入賬戶的Id
int to = 2;
//轉(zhuǎn)賬金額
decimal balance = 10;
SqlParameter[] param =
{
new SqlParameter ("@from",from),
new SqlParameter("@to",to),
new SqlParameter ("@balance",balance),
//-------------------------------注意：這里設置為輸出參數(shù)
new SqlParameter ("@returnNum",System.Data.SqlDbType.Int{Direction=System.Data.ParameterDirection.Output }
};
//------------------------設置CommonType為StorProcedure類型
SqlHelper.ExecuteNonquery("pro_transfer_szmbank",System.Data.CommandType.StoredProcedure, param);
//------------------------獲取輸出參數(shù)
//根據(jù)輸出參數(shù)判斷轉(zhuǎn)賬結果
int outPutparam = Convert.ToInt16(param[3].Value);
switch (outPutparam)
{
case 1: Console.WriteLine($"success:從Id:{from}轉(zhuǎn)賬{balance}元到Id：{to}");break;
case 2: Console.WriteLine("error"); break;
case 3: Console.WriteLine("余額不足"); break;
}
Console.ReadKey();
}
測試結果：
success：從Id:1轉(zhuǎn)賬10元到Id:2
3. 示例2：測試返回DataTable
①存儲過程：
create procedure [dbo].[pro_ReturnDataTable] as begin select Id as 用戶ID ,Balance as 余額 from szmBank; end go
②數(shù)據(jù)庫中測試：
execute pro_ReturnDataTable
測試結果：即顯示szmBank中的所有數(shù)據(jù)
③控制臺中測試：
static void Main(string[] args)
{
DataTable dt = SqlHelper.GetDataTable("pro_ReturnDataTable", CommandType.StoredProcedure);
foreach (DataRow row in dt.Rows)
{
Console.WriteLine(row["用戶ID"].ToString() + ":" + row["余額"].ToString());
}
Console.ReadKey();
//TransferAccounts();
ReturnDataTable();
}
測試結果：即打印szmBank中的所有數(shù)據(jù)
4. 源代碼下載
所需要的數(shù)據(jù)庫表在示例中已說明，可以直接使用建表語句創(chuàng)建！
存儲過程的SQL語句在示例中完整的展示了，可以直接復制！
總結
到此這篇關于SQL Server存儲過程在C#中調(diào)用的文章就介紹到這了,更多相關SQL Server存儲過程在C#調(diào)用內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！
相關文章
這篇文章主要介紹了C# 調(diào)用exe傳參,并獲取打印值的實例，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2021-04-04
這篇文章主要介紹了C#線程間不能調(diào)用剪切板的解決方法,需要的朋友可以參考下2014-07-07
這篇文章主要介紹了C#中緩存的基本用法,對C#初學者來說是很有必要熟練掌握的內(nèi)容,需要的朋友可以參考下2014-08-08
下面小編就為大家分享一篇對int array進行排序的實例講解，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2017-12-12
C#幾種截取字符串的方法小結，需要的朋友可以參考一下2013-04-04
這篇文章主要介紹了c# 切片語法糖的相關資料，幫助大家更好的理解和學習c#，感興趣的朋友可以了解下2020-09-09
本文主要介紹了C# Bitmap圖像處理(含增強對比度的三種方法)，文中通過示例代碼介紹的非常詳細，具有一定的參考價值，感興趣的小伙伴們可以參考一下2021-11-11
這篇文章主要介紹了VS2015 C#生成dll文件的方法(32/64),需要的朋友可以參考下2016-12-12
這篇文章主要為大家詳細介紹了Unity shader實現(xiàn)消融效果，具有一定的參考價值，感興趣的小伙伴們可以參考一下2019-02-02
單例模式防止在應用程序中實例化多個對象。這就節(jié)約了開銷，每個實例都要占用一定的內(nèi)存，創(chuàng)建對象時需要時間和空間。2016-06-06