亲宝软件园·资讯

展开

SQL Server存储过程在C#调用 SQL Server存储过程在C#中调用的简单实现方法

shanzm 人气:0

0. 简介

【定义】：存储过程（Stored Procedure） 是在大型数据库系统中，一组为了完成特定功能的SQL 语句集，它存储在数据库中，一次编译后永久有效，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。

【优缺点】：存储过程优缺点都非常的明显！几乎每一篇讨论存储过程的文字，都是会说其他优点是balabala,缺点是balabala，然而最后作者的结论都是：“我不推荐使用存储过程”。
具体的存储过程的优缺点这里就不详述了！

公司旧项目使用存储过程实现业务逻辑，没办法只能研究了一下 🙃！

闲言碎语不要讲，书归正传，下面就开始存储过程！

1. 语法细节

变量与变量之间使用逗号隔开，语句结尾无标点符号

声明变量:declare @variate_name variate_type,例如声明并赋值：declare @name nvarchar(50) ='shanzm'

变量赋值:set @variate_name =value

打印变量：print @variate_name

begin……end 之间的SQL语句称之为一个代码块

可以使用if……else实现逻辑判断

创建存储过程：create procedure pro_name

执行存储过程：execute pro_name

输出参数:存储过程返回的是SQL语句查阅结果，在定义参数后，添加output,设置为一个输出参数（和C#中输出参数类似），相当于多了一个返回值！

创建存储过程的基本形式：

create procedure pro_name_tableName
@param1 param1_type,
@param2 param2_type,
as
begin 
  --sql语句
end
go

2. 示例1：模拟转账

①示例背景：使用存储过程，模拟在一张存款表中实现用户与用户之间的转账

②准备工作1：在数据库中创建表szmBank

CREATE TABLE [dbo].[szmBank](
 [Id] [bigint] IDENTITY(1,1) NOT NULL,
 [Balance] [decimal](18, 0) NOT NULL

添加一些测试数据：

Id                   Balance
---------------      ----------------
1                    1000
2                    2000
3                    3000

③准备工作2：封装C#代码中的SQL辅助类SqlHelper

注意封装的时候要有一个CommandType参数，决定是执行SQL语句还是存储过程，
CommandType是一个枚举类型，其中Text值为执行SQL语句，StoreProcedure为执行存储过程
具体封装细节这里就不详述了。

找到了2年前我封装的一个SqlHelper.cs,常规使用没有任何问题，仅供参考：

#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">执行类型</param>
    /// <param name="param">参数</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>
    /// 返回影响行数
    /// </summary>
    /// <param name="sql">SQL语句或存储过程</param>
    /// <param name="type">执行类型</param>
    /// <param name="param">参数</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>
    /// 返回查询结果的第一行第一个单元格的数据
    /// </summary>
    /// <param name="sql">SQL语句或存储过程</param>
    /// <param name="type">执行类型</param>
    /// <param name="param">参数</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();
        }
      }
    }

  }
}

④编写存储过程：

在数据库中：指定数据库-->可编程性-->存储过程-->右键：新建-->存储过程：

SQL Server中编写的SQL语句没有默认的格式化，所有代码排版按照我自己习惯进行Tab缩进

建议放到编辑器中查看下面的存储过程，会好看一些！

SQL大小写不敏感，我习惯小写，方便阅读！

-- =============================================
-- Author: shanzm
-- Create date: 2020年5月2日 19:56:51
-- Description: 模拟账户之间转账
-- =============================================
create procedure pro_transfer_szmbank
@from bigint,
@to bigint,
@balance decimal(18,0),
@returnNum int output--(1表示转账成功，2表示失败，3表示余额不足)
as
begin
 --判断转出账户是否有足够的金额
 declare @money decimal(18,0)
 select @money=Balance from dbo.szmBank where Id=@from; 
 if @money-@balance>=0.1 
   --开始转账
  begin 
  begin transaction
   declare @sum int =0
   --转出账户扣钱
   update szmBank set balance=balance-@balance where id=@from
   set @sum=@sum+@@error
   --转入账户加钱
   update szmBank set balance=balance+@balance where id=@to
   set @sum=@sum+@@error
   --判断是否成功
   if @sum<>0
   begin
    set @returnNum=2--转账失败
    rollback
   end
   else
   begin
    set @returnNum=1--转账成功
    commit
   end
  end
 else
 begin
  set @returnNum=3--余额不足
 end
end
go

在数据库中执行测试（F5）：

--执行测试：
declare @ret int
execute pro_transfer_szmbank 
@from='1',
@to='2',
@balance='10',
@returnNum=@ret output--注意输出参数在执行语句中也是要表明"output"
print @ret --结果是打印:1,即存储过程实现成功

【注意】：

⑤控制台中测试

新建一个控制台项目，在配置文件中添加连接字符串

因为封装的SqlHelper中需要从配置文件中读取数据库连接字符串，所以添加引用：System.Configuration

static void Main(string[] args)
{
  //转出账户的Id
  int from = 1;
  //转入账户的Id
  int to = 2;
  //转账金额
  decimal balance = 10;

  SqlParameter[] param =
  {
    new SqlParameter ("@from",from),
    new SqlParameter("@to",to),
    new SqlParameter ("@balance",balance),
    //-------------------------------注意：这里设置为输出参数
    new SqlParameter ("@returnNum",System.Data.SqlDbType.Int{Direction=System.Data.ParameterDirection.Output }
  };

  //------------------------设置CommonType为StorProcedure类型
  SqlHelper.ExecuteNonquery("pro_transfer_szmbank",System.Data.CommandType.StoredProcedure, param);

  //------------------------获取输出参数
  //根据输出参数判断转账结果
  int outPutparam = Convert.ToInt16(param[3].Value);

  switch (outPutparam)
  {
    case 1: Console.WriteLine($"success:从Id:{from}转账{balance}元到Id：{to}");break;
    case 2: Console.WriteLine("error"); break;
    case 3: Console.WriteLine("余额不足"); break;
  }

  Console.ReadKey();
}

测试结果：

success：从Id:1转账10元到Id:2

3. 示例2：测试返回DataTable

①存储过程：

create procedure [dbo].[pro_ReturnDataTable]
as
begin
 select Id as 用户ID ,Balance as 余额 from szmBank;
end
go

②数据库中测试：

execute pro_ReturnDataTable

测试结果：即显示szmBank中的所有数据

③控制台中测试：

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中的所有数据

4. 源代码下载

C#中使用存储过程-源代码下载

所需要的数据库表在示例中已说明，可以直接使用建表语句创建！

存储过程的SQL语句在示例中完整的展示了，可以直接复制！

总结

加载全部内容
相关教程
猜你喜欢
用户评论