C#二十六使用Ado.Net调用存储过程

teayear

已于 2022-05-05 11:54:56 修改

阅读量5.4k

点赞数

分类专栏： C# 道本自然文章标签： .net C#存储过程系统安全 ado.net winform

于 2016-05-07 19:05:41 首次发布

本文链接：https://blog.csdn.net/zhangchen124/article/details/51339456

版权

道本自然同时被 2 个专栏收录

742 篇文章 41 订阅

订阅专栏

134 篇文章 21 订阅

订阅专栏

存储过程是连接式访问数据库的一种延伸，主要是通过命令对象调用数据库系统中的存储过程来完成的。存储过程可以带参数，也可以不带参数，可以返回结果页可以没有返回结果。存储过程执行速度快、允许模块化程序设计并且提高系统安全性，所以是最常用的操作数据库的技术。使用Ado.Net中的SqlCommand可以调用并执行 Sql Server数据库的存储过程。

视频课堂https://edu.csdn.net/course/play/7621

C#程序设计及宿舍管理系统实战

C#程序设计与宿舍管理系统实战-c#宿舍管理系统,c#学生宿舍管理系统-C#视频教程-后端开发-CSDN程序员研修院

重点：

Ø 如何定义与使用存储过程以及存储过程的重要性

Ø 参数对象

Ø 调用有返回值的存储过程

预习功课：

Ø SqlCommand如何才能调用存储过程

Ø SqlParameter类及其使用规则

Ø 参数化对象的使用

Ø 如何才能调用有返回值的存储过程

3.1 参数化对象

参数对象(Parameter)表示命令对象（Command）中的一个参数，我们前面用过的命令对象都是不带参数的，我们也可以使用带参数的命令对象，这将给程序带来更大的灵活性。我们知道存储过程中存在输入输出参数，所以在介绍C#调用存储过程前，需要来看看系统为我们提供的参数参数对象到底有何用处以及如何使用。

测试表：Person

Create table Person

{

   psnNo Nchar(6) PrimaryKey,

   psnName Nvarchar(5) NotNull,

   psnSex  Nchar(1) not null,

   psnAge smallint notnull,

   psnAddress Nvarchar(50)not null

}

go

该表可建立于我们的测试数据库Test下：

在向数据库里插入记录，我们可以使用下面Sql语句：

Insert into Person values(＇001，＇心酸果冻＇，＇女＇，25，＇北京宣武区＇)

Insert into Person values(＇002，＇芬理希梦＇，＇女＇，24，＇上海浦东区＇)

很显然这两个语句非常相似，有没有一种方法可以使我们避免这种无谓的重复呢？有，就是使用参数化Sql语句。在实现上面的插入操作时，可以使用参数化Sql语句将两个Sql语句中不同的部分用参数来表示，然后在使用的时候给参数赋予一个具体的值即可，这样就不用每次都将Sql语句重新写一遍了。我们使用参数化Sql语句重写了上面的插入操作如下：

//通过连接字符串建立数据库连接

SqlConnection cn=new SqlConnection(＂server=.;database=Test;uid=sa;pwd=123456＂);

try

{

  cn.Open();

//创建带参数的Sql语句

  string sql=＂Insert into Personvalues(@Id,@Name,@Sex,@Age,@Address) ＂;

  SqlCommand cmd=newSqlCommand(sql,cn);

//创建参数并加入到Parameters集合里

  cmd.Parameters.Add(＂@Id＂,SqlDbType.NChar,6);

  cmd.Parameters.Add(＂@Name＂,SqlDbType.Nvarchar,5);

  cmd.Parameters.Add(＂@Sex＂,SqlDbType.Nchar,1);

  cmd.Parameters.Add(＂@Age＂,SqlDbType.SmallInt);

  cmd.Parameters.Add(＂@Address＂,SqlDbType.NvarChar,50);


//设置参数的值，并执行插入

  cmd.Parameters[＂@Id＂].Value=＂004＂;

  cmd.Parameters[＂@Name＂].Value=＂帕瓦罗蒂＂;

  cmd.Parameters[＂@Sex＂].Value=＂男＂;

  cmd.Parameters[＂@Age＂].Value=＂32＂;

  cmd.Parameters[＂@Address＂].Value=＂东部部落＂;

  cmd.ExecuteNonQuery();


//设置带参数的值，并执行插入

cmd.Parameters[＂@Id＂].Value=＂005＂;

cmd.Parameters[＂@Name＂].Value=＂天使毛毛＂;

cmd.Parameters[＂@Age＂].Value=＂18＂;

cmd.Parameters[＂@Address＂].Value=＂东部部落＂;

//此处设置的值可以为对应的Winform、Webform的文本框、下拉框等用户输入的值；

//如：ccmd.Parameters[＂@Id＂].Value=txtId.Text;

cmd.ExecuteNonQuery();

}

catch(SqlExecption ex)

{

  //数据库出错报错信息

}

finally

{

  cn.Close();

}

这段代码执行后数据库里会增加两条记录。这里就是使用参数化的Sql语句的方式进行的，要注意参数化Sql语句是和命令对象配合使用的。上面的参数化Sql语句中使用的＂@Id＂等就是参数（参数以@开头可随便定义名称）,在SqlCommand中需要为这些参数创建对应的参数对象，具体说来参数化Sql语句的使用有三步：

1. 构造参数Sql语句，可以是任何Sql语句

2. 为每一个Sql语句中出现的参数定义一个参数对象，并将这些参数加入到命令对象中

3. 给参数设置值，并执行查询

构造参数Sql语句我们就不说了，定义参数对象比较复杂，上面我们看到的是使用cmd.Parameters.Add方法创建参数对象，实际上我们也可以自己定义参数对象，定义完后要加入到命令对象里面：

SqlParameter parId=newSqlParameter();

parId.ParameterName=＂@Id＂； //设置参数的名称

parId.Size=6; //设置参数数据的最大值

cmd.Parameters.Add(parId); //将参数对象加入到命令对象中

这段代码和我们上面使用的＂cmd.Parameters.Add(＂@Id＂,SqlDbType.Nchar,6)效果是相同的。像这样使用自己定义的参数可以更灵活的定制参数，因为SqlParameter类为我们提供许多有用的属性。

属性	说明
ParameterName	参数的名称，在与参数化Sql中出现的参数名要对应
SqlDbType	参数的数据类型
IsNullable	该值指示参数是否接受空值
Size	获取或设置参数数据的最大大小，设置Size仅影响输入的参数值
SourceColumn	获取或设置源列的名称
SourceVersion	确定参数值使用的是原始值还是当前值
Value	参数的值
Direction	指示参数是只可输入、只可输出，双向还是存储过程返回值

3.2 调用无返回值的存储过程

无返回值的存储过程可以执行增加记录、删除记录、修改记录等数据库操作。使用命令对象执行无返回值存储过程和无返回值的Sql语句执行方式基本相同，都是使用ExecuteQuery()。下面这个例子，是调用存储过程修改存储过程修改Person表中我们刚刚插入的“帕瓦罗蒂”的地址，我们首先要在数据库建立如下存储过程：

Create Proc ch_Person

as

    Update Person

        Set psnAddress=’乞力马扎罗’

    Where psnName=’帕瓦罗蒂’

Go

要通过C#执行该存储过程，需要创建一个SqlCommand类的命令对象，然后修改命令对象的类型属性CommandType为存储过程类型，并设置命令对象的CommandText为存储过程的名字，然后通过ExecuteNonQuery()方法执行存储过程即可：

核心代码:

SqlConnection cn=new SqlConnection(＂server=.;database=test;uid=sa;pwd=123456＂);

try

{

    cn.Open();

    SqlCommandcmd=cn.CreateCommand();

    //设置命令类型为存储过程

    cmd.CommandType=CommandType.StoreProcedure;

    //设置存储过程的名字

    cmd.CommandText=＂ch_Person＂;

    //执行存储过程

    cmd.ExecuteNonQuery();

}

catch(SqlExecption ex)

{

    //数据库出错信息提示

}

finally
    {

    cn.Close();

}

无返回值的存储过程还可以包含传入参数，比如下面这个存储过程，可以按照姓名更新地址。姓名和地址都是存储过程传入的参数：

Create Proc ch_Person

    @p_psnName NvarChar(5),

    @p_psnAddress NvarChar(50)

As

   Update Person

       Set psnAddress=@p_psnAddress

   Where psnName=@p_psnName

Go

调用这个存储过程又需要用到命令对象中的参数属性Parameters,只需要在这个参数集合里加入存储过程的参数定义并设置其值就可以了，代码如下：

SqlConnection cn=new SqlConnection(＂server=.;database=test;uid=sa;pwd=123456＂);

try

{

  cn.Open();

  SqlCommandcmd=new SqlCommand(＂ch_Person＂,cn);

  cmd.CommandType=CommandType.StoredProcedure;

//加入参数对象，并设置其值

  cmd.Parameters.Add(＂@p_psnName＂,SqlDbType.NVarChar).Value=＂帕瓦罗蒂＂;

  cmd.Parameters.Add(＂@p_psnAddress＂,SqlDbType.NVarChar).Value=＂日本广岛＂;

//后面的具体的值，在设置的时候可以变为具体的文本框控件等的值；

//如：cmd.Parameters.Add(＂@p_psnName＂,SqlDbType.NVarChar).Value=txtName.Text

//执行存储过程

  cmd.ExecuteNonQuery();

}

catch(SqlException ex)

{

  //操作数据库出错信息处理

}

finally

{

  cn.Close();

}

3.3 调用带返回值的存储过程

上面讨论了对于没有返回值的存储过程的调用，那么对于带有返回值的存储过程我们能调用并获得返回数据么？当然可以，实际上和上面无返回值的实现方式差别不大。我们在介绍SqlParameter的时候曾经提到SqlParameter的一个Direction属性，这个属性就可以指定参数是输入还是输出，指定了Direction属性为输出类型的参数对象，就可以调用存储过程时获得存储过程的返回值。Direction属性是通过ParameterDirection枚举指定的，如：

l Input:表示该参数为输入参数

l InputOutput:表示该参数既能输入，也能输出

l Output：表示该参数为输出参数

l ReturnValue:获取存储过程的返回值

另外，我们知道存储过程的返回值有两种方式，一种是通过设置参数为Output属性而返回，一种是直接在存储过程里使用Return关键字来返回值。下面这个存储过程可以通过姓名查找其地址，上面说的两种返回方式都使用了：

Create Proc gt_Address

   @p_psnName NvarChar(5)  //请输入姓名

   @g_psnAddress NvarChar(5)OutPut  //返回此人的地址

As

   Select@g_psnAddress=psnAddress From Person

   WherepsnName=@p_psnName

   If@@Error<>0

       Return-1   //如果查询语句出错返回-1

   Else

       Return 0

Go

我们调用这个存储过程的部分代码如下：

SqlConnection cn=new SqlConnection(＂server=.;database=test;uid=sa;pwd=123456＂);

try

{

   cn.Open();

   SqlCommandcmd=new SqlCommand(＂gt_Address＂,cn);

   cmd.CommandType=CommandType.StoredProcedure;

   //加入参数，并设置参数的值和Direction属性

   cmd.Parameters.Add(＂@p_psnName＂,SqlDbType.NvarChar).Value=＂帕瓦罗蒂＂;

   SqlParametercpar=cmd.Parameters.Add(＂@g_psnAddress＂,SqlDbType.NvarChar,50);

   cpar.Direction=ParameterDirection.Output;

   SqlParametercres=cmd.Parameters.Add(＂@return＂,SqlDbType.Int);

   cres.Direction=ParameterDirection.ReturnValue;

   //执行存储过程

   cmd.ExecuteNonQuery();

   //获得参数的值

   intres=(int)cres.Value;

   stringaddress=(string)cpar.Value;

   Console.WriteLine（＂返回值：{0},地址：{1}＂，res，address）;  

}

catch(SqlException ex)

{

   //数据库出错信息报告

}

finally

{

   cn.Close();

}



运行结果：

返回值：0,地址：日本广岛

在上面的这段代码中，我们给命令对象定制了三个参数，其中有一个输入参数：@p_psnName(参数的Direction属性如果没有指定，那么默认是输入参数)，两个返回参数：@g_psnAddress和@return。如果查看存储过程你就会发现存储过程里并没有@return参数，事实上在这里,@return是一个临时参数，我们通过这个参数来获取存储过程的返回值，这个参数可以是任何名称，如@Re、@R等。存储过程的返回值是整型的，所以@return类型也应该是整型。另外，注意对于要返回的参数，如果是字符串型(NvarChar、Nchar或Char)则必须指定长度，如上面的@g_psnAddress参数我们指定了长度为50。

对于带参数的存储过程，不管是输入参数还是输出参数，实际上有一种简单的方式创建参数，就是使用系统类SqlCommandBuilder的静态方法DeriveParameters自动生成参数。使用DeriveParameters方法可以从SqlCommand中指定的存储过程中检索参数信息并填充到该SqlCommand对象的Parameters集合里。我们使用DeriveParameters方法重新实现上面的存储过程调用如下：

SqlConnection cn=new SqlConnection(＂server=.;database=test;uid=sa;pwd=123456＂);

try

{

   cn.Open();

   SqlCommandcmd=new SqlCommand(＂gt_Address＂,cn);

   cmd.CommandType=CommandType.StoredProcedure;

   //为命令对象生成参数

   SqlCommandBuilder.DeriveParameters(cmd);

   //设置输入参数的值

   cmd.Parameters.Add(＂@p_psnName＂,SqlDbType.NvarChar).Value=＂帕瓦罗蒂＂;

   //执行存储过程

   cmd.ExecuteNonQuery();



   intres=(int)cmd.Parameters[＂@Return＂].Value;

   string address=(string)cmd.Parameters[＂@p_psnAddress＂].Value;

   Console.WriteLine（＂返回值：{0},地址：{1}＂，res，address）;  

}

catch(SqlException ex)

{

   //数据库出错信息报告

}

finally

{

   cn.Close();

}

这个程序的执行方式和运行结果和上面那个完全一样。不难看出，使用DeriveParameters只是将参数创建的过程交给了系统，我们省略了参数创建的过程，但给参数传值还有获得参数的值还是需要自己操作，即使如此，这样还是使程序简单了很多。