学习笔记：存储过程在asp.net中的应用

今天的主要收获是研究了存储过程在asp.net中的应用。主要包括了两个方面：在代码中通过编程的方式使用存储过程和在DataSorce控件使用存储过程。

第一部分：存储过程简要介绍：

1、什么是存储过程？
存储过程（PROCEDURE），是SQL数据库里面的一个术语。通过SQL数据库提供的编程能力，可以将一系列的SQL命令、函数、编程语句组合起来，连续执行，可以说就是数据库的批命令。

2、存储过程的组成：
存储过程包括：名称、参数、语句体、返回值四部分组成。
3、存储过程的简要语法结构是：

■■■■■■代码开始■■■■■■
CREATE PROCEDURE [存储过程的名称]
 [参数1],[参数2],....
AS
 [语句体]
 RETURN [返回值]
■■■■■■代码结束■■■■■■ 

例如：

■■■■■■代码开始■■■■■■

--修改日志分类，集中修改[lc_blogClass]和[BlogClass]

CREATE PROCEDURE dbo.BlogClassUpdate
 /*这是MS SQL2005提供的注释，我觉得很有代表性就留了下来。
 (
 @parameter1 int = 5, --这是指定默认值的范例
 @parameter2 datatype OUTPUT --这是“输出”、“返回值”参数的范例
 )
 */
 
 /*
 *参数说明：
 *编号
 *分类名称
 *分类图片
 *分类排序
 */
 
 @Id int,
 @ClassName nvarchar(50),
 @ClassPic nvarchar(100),
 @sort int
 
AS
 /* SET NOCOUNT ON */
 IF @Id IS NULL
  RETURN -1
  
 --Insert into   [log] ([log]) values ('BlogClassUpdate !!!!')
 Update [lc_BlogClass] Set [sort] = @sort WHERE Id = @Id
 
 Update [BlogClass] Set [ClassName] = @ClassName,[ClassPic] = @ClassPic  WHERE ClassId = @Id
 
 RETURN 0
■■■■■■代码结束■■■■■■ 

4、存储过程的用途
4.1、多条语句连续执行。
有一些连续而且固定的操作，可以封装成一个存储过程，为工作提供方便。
4.2、多个操作合并
在应用程序中使用数据库，有时候会遇到一定的限制，最常见的就是，在某个位置，只允许执行一条语句。在这种情况下，有时候偏偏要连续完成多个操作。比如说上面的范例，程序要求同时修改两个表，但是只能执行一次修改动作。
4.3、预定义的流程
触发器实在删除、更新、插入等操作中触发的预定义动作，触发器也可以同时执行多条语句，触发器的语句体结构和存储过程是一样的。所以有人说触发器就是存储过程的一种特殊应用。

5、存储过程和函数

大多数情况下存储过程和函数都是一样的。但是也有一些不同。
在SQL SERVER的联机丛书里有详细的内容，这里列出部分内容：

存储过程与函数不同，因为存储过程不返回取代其名称的值，也不能直接用在表达式中。
 ——联机丛书
函数中不能使用临时表,和动态SQL
 ——CSDN：http://topic.csdn.net/t/20051027/17/4354930.html
另外还可以看看这两篇文章：

http://www.51testing.com/?141432/action_viewspace_itemid_81171.html
http://topic.csdn.net/t/20051027/17/4354930.html
http://www.cnsdn.com.cn/blog/article.asp?id=2035
http://hi.baidu.com/kevinliu/blog/item/fc9d57da11c215d9b6fd4800.html

第二部分：在代码中通过编程的方式使用存储过程
1、基本要求：通过SqlCommand对象执行。
        System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection();
        conn.ConnectionString = this.ConnStr;
        SqlCommand cmd = new SqlCommand();
        conn.Open();
        cmd.Connection = conn;
2、主要步骤：
2.1、指定存储过程的名称和command的类型为存储过程。
        cmd.CommandText = "AddBlogClass";
        cmd.CommandType = CommandType.StoredProcedure;
2.2、添加参数：
        SqlParameter Para1 = new SqlParameter("@ClassName",SqlDbType.NVarChar);
        Para1.Value = TextBoxName.Text;
        cmd.Parameters.Add(Para1);
2.3、执行存储过程
 int result = cmd.ExecuteNonQuery();
3、完整代码：

■■■■■■代码开始■■■■■■

 

   protected void Button2_Click(object sender, EventArgs e)
    {
        System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection();
        conn.ConnectionString = this.ConnStr;
        SqlCommand cmd = new SqlCommand();
        conn.Open();
        cmd.Connection = conn;
   
        cmd.CommandText = "AddBlogClass";
        cmd.CommandType = CommandType.StoredProcedure;
        SqlParameter Para1 = new SqlParameter("@ClassName",SqlDbType.NVarChar);
        Para1.Value = TextBoxName.Text;


        SqlParameter Para2 = new SqlParameter("@ClassPic", SqlDbType.NVarChar);
        if (String.IsNullOrEmpty(TextBoxPic.Text))
            Para2.Value = DBNull.Value;
        else
            Para2.Value = TextBoxPic.Text;
        SqlParameter Para3 = new SqlParameter("@ClassSort", SqlDbType.Int);
        if (String.IsNullOrEmpty(TextBoxSort.Text))
            Para3.Value = DBNull.Value;
        else
            Para3.Value = Convert.ToInt32(TextBoxSort.Text);
        cmd.Parameters.Add(Para1);
        cmd.Parameters.Add(Para2);
        cmd.Parameters.Add(Para3);
        int result = cmd.ExecuteNonQuery();
        switch (result)
        {
            case 2:
                Label1.Text = "添加成功。";
                GridView1.DataBind();
                break;
            case 1:
                Label1.Text = "添加出错，只有部分数据保存。";
                break;
            case 0:
                Label1.Text = "添加出错，没有数据被保存。";
                break;
            default:
                break;
        }

    }

 

■■■■■■代码结束■■■■■■ 

第三部分：在DataSorce控件使用存储过程
1、没有参数
对于没有参数的存储过程，直接调用就可以了。
1.1、配置数据源
如果是“配置数据源”就要在“自定义语句和存储过程”中，选择“存储过程”那一选项，然后在下拉列表中选择要使用的存储过程就行了。

1.2、属性面板
如果是通过属性面板设置的，就需要下面这两个步骤：

A：命令类型中选择“StoredProcedure”
注：命令类型包括“SelectCommandType”、“UpdateCommandType”等。

B：在命令类型下面相应的查询（例如SelectCommandType对应的就是SelectQuery）中，单击“查询”框里的按钮。弹出“命令和参数编辑器”，在“Select命令”输入存储过程的名称。然后单击确定。

2、有参数
2.1、添加参数
有参数的存储过程，除了要像上面1.1和1.2中那样添加存储过程的名称之外还需要在1.2.B打开的“命令和参数编辑器”中添加参数。
可以在“命令和参数编辑器”的界面上使用“刷新参数”按钮，也可以使用下面的“添加参数”按钮。

2.2、添加参数的注意事项

2.2.1
对于Select和Delete命令，不要添加“RETURN_VALUE”参数，如果是全用“刷新参数”产生的，那就手工删掉。

2.2.2
对于Delete命令，需要指定参数源。一般情况下就是“Control”（控件），然后在下面选择一个控件（根据控件名）

2.2.3
对于Update命令，【ConflicDetection】不要使用【CompareAllValues】方式的参数，我测试的情况是在这种模式下，无法更新数据。

2.2.4
经常出现的错误“为过程或函数 ### 指定了过多的参数。”
在我测试的过程有，有如下几个原因。
A：对有有参数的存储过程添加了存储过程名称，没有添加参数
B：有多余的“RETURN_VALUE”参数，参见2.2.1
C：如果GridView控件的字段数量和存储过程的参数不一致
D：属性面板的参数名和存储过程的参数名不一致。