.NET中调用数据库存储过程方法的研究与实现*

王 红，陈功平

(六安职业技术学院信息与电子工程学院，安徽 六安 237158)

.NET中调用数据库存储过程方法的研究与实现*

王 红，陈功平

(六安职业技术学院信息与电子工程学院，安徽 六安 237158)

应用程序经常从数据库读写数据，有的读写过程会被经常使用，数据的读写会增加服务器端压力，方法不当还容易泄露数据库设计细节，危害数据库安全.存储过程是SQL Server数据库中用于完成特定功能的一系列T-SQL语句的集合，开发人员在数据库中预先定义存储过程，当需要完成功能时可直接调用存储过程，可以提高执行速度，隐藏数据库设计细节.面向对象程序设计中，常采用类封装的形式与数据库交换数据，先使用ADO.NET技术中的Connection类连接数据库再使用Command类执行存储过程.

存储过程;数据库;类封装;数据库应用系统

SQL Server数据库是实际应用最广泛、管理能力最强的数据库管理系统[1].用户都是通过应用程序中的页面和数据库进行交互，开发人员为了提高数据交互速率，隐藏数据库设计细节，经常使用存储过程包装数据查询和数据操纵过程[2].应用程序访问SQL Server数据库的存储过程要借助ADO.NET技术，一般流程为:

1)数据库中使用T-SQL语言定义存储过程.

2)应用程序中使用ADO.NET中的Connection类连接数据库，使用Command类执行存储过程.

3)应用程序页面中使用开发语言调用执行存储过程的方法.

1 存储过程

1.1存储过程的概念

存储过程是大型数据库中用于完成某项功能所要用到的一组命令集合，在SQL Server数据库中是若干条T-SQL语句的集合，用于完成某项功能[3].在数据库应用系统开发中通常将那些经常用到的数据操纵过程在数据库中包装成存储过程，再在应用系统开发环境中定义调用存储过程的方法，最后在要用到数据操纵过程的控件中运行调用存储过程的方法.存储过程在数据库和用户应用页面之间的调用关系如图1所示.

图1 存储过程的调用关系

开发人员设计存储过程时先在数据库中定义，再定义方法调用数据库中的存储过程，最后调用方法；用户使用时方向正好相反.

存储过程将一组预编译的T-SQL语句作为数据库对象保存，为重复调用执行该组语句提供方便；而且存储过程是预编译的，第一次执行时SQL Server为其产生查询计划并保留在内存中，以后再调用就无须再编译，所以执行速度快；存储过程可以提供输入、输出参数，避免数据库细节暴露，是数据库安全设计的一种方法.

1.2存储过程的创建

用户存储过程可分成有参数和无参数两种类型，在SQL Server数据库中使用T-SQL命令创建存储过程，命令格式如下.

CREATE PROC[EDURE] 存储过程名 [参数名 类型 [=默认值]，……]

AS

T-SQL语句 [T-SQL语句……]

其中存储过程名不能与现有数据库对象名重复，参数名的起始字符必须为@符号，定义多个参数时用逗号隔开，AS关键字后的T-SQL语句实现了存储过程的功能.

2 相关技术

2.1ADO.NET

ADO.NET是ADO的升级版在NET编程环境中首选数据访问技术[4]，主要包含Connection、Command、DataReader、DataSet、DataAdapter、DataTable几种常用类.

Connection类用于连接数据库，是其它类与数据库交互的基础，连接SQL Server数据库的类名为SqlConnection；Command类用于执行查询、修改、添加、删除等数据操纵命令，在执行命令之前要连接到数据库，Command类对象的常用方法是ExecuteNonQuery，经常用于执行不带返回值的命令，执行SQL Server数据库命令的类名为SqlCommand；DataReader类对象可存储Command类对象的查询结果，由于只能按照一定的顺序读取数据，所以速度快，但要操作数据就十分不方便；DataSet类对象中的数据存储在内存中，读取速度快，数据操纵方便，还可以保存多个查询结果，DataSet对象能被所有DataProviders对象使用；DataTable类对象可用表格形式临时保存数据的一个虚拟表格，是ADO.NET技术中最常用的类之一.

2.2SQLServer数据库中的流程控制语句

SQL Server数据库除了强大的数据存储和数据管理能力外，还具备使用T-SQL语言编写程序完成特殊功能的能力，T-SQL语言同样可以实现顺序、选择和循环三种程序结构[5]，选择结构可使用if…else、case…when流程控制语句实现，循环结构可使用while流程控制语句实现.

2.2.1 基本语句

程序设计中的第一步都是定义变量来保存中间量，SQL Server数据库中的变量有局部变量和全局变量两种，全局变量由开发人员定义，定义变量的语法格式如下.

DECLARE {@变量名 数据类型}[,…]

例如：DECLARE @a INT ,@b CHAR(6),@c FLOAT—同时定义了3个类型不同的变量.

成功定义局部变量后，就可以为变量赋值，T-SQL语言中的赋值语句有两种:

SELECT 变量名1=表达式/字段名 [FROM 子句][WHERE 子句]……,变量名2=……

SET 变量名=表达式

可见SET一次只可以给一个变量赋值，SELECT一次可以给多个变量赋值；SELECT 可以将数据表中的字段值赋给变量，SET不可以.

2.2.2 IF…ELSE分支语句

IF…ELSE分支结构的语法格式如下.

IF 逻辑表达式

语句块1

ELSE

语句块2

当逻辑表达式成立时，执行语句块1，否则执行语句块2，多分支结构可以使用IF…ELSE IF…ELSE流程控制语句实现.语句块如果由多条T-SQL语句组成，可用BEGIN…END界定起来，相当于C语言中的大括号.

2.2.3 循环语句

WHILE循环语句的语法格式如下.

WHILE 逻辑表达式

循环体语句

WHILE语句的执行流程如图2所示.

图2 WHILE语句执行流程

3 存储过程在用户登录中的应用

3.1数据表介绍

无论是基于手机还是基于Web的应用程序，都会提供用户登录接口[6]，用户登录过程使用率高且登录时要与后台数据库打交道，记录当前登录用户的一些基本数据，比如登录的IP地址，登录时间等.用于存储用户基本信息的表结构中部分字段名及含义，见表1.

表1 users表数据结构

3.2存储过程的创建

用户登录时要向数据库写入当前登录的IP地址、登录时间和上次登录时间，每天登录增加1个用户积分，同一天内登录只增加一次.使用存储过程的方法和数据库交换数据，先在数据库中创建存储，创建语句如下：

--存储过程名字是UserLogin，@username和@userip是参数

CREATE PROCEDURE UserLogin @username VARCHAR(16),@userip VARCHAR(50)

AS

--定义局部变量@last

DECLARE @last DATETIME

--将当前登录用户的上次登录时间赋值给@last变量

SELECT @last =(SELECT last_time FROM users WHERE username =@username)

--修改当前用户的上次登录时间

UPDATE users SET last_time =login_time WHERE username =@username

--修改当前用户的登录IP、登录时间

UPDATE users SET IP=@userip,login_time =GETDATE() WHERE username=@username

--当用户不在同一天内登录时，jf加1

if(YEAR(@last)!=year(GETDATE()) or MONTH (@last)!=month(GETDATE()) or day(@last)!=day(GETDATE()))

UPDATE users SET jf=jf+1 WHERE username =@username

SQL Server数据库的存储过程存放在当前数据库“可编程性”的子文件夹“存储过程中”.

3.3定义调用方法

在数据库中定义存储过程之后，就可以在应用程序中直接调用或使用类封装的形式调用数据库中的存储过程.以Visual Studio开发环境的Web网站应用程序为例，常用类封装的形式调用存储过程，下列程序段使用C#语言定义名为ConnSql的类、RunProc()方法实现存储过程调用.

public class ConnSql

{

private string constr ="server=.;initial catalog=weblt;uid=sa;pwd=123456";

private SqlConnection con=new SqlConnection(constr);//定义连接类的实例

private SqlCommand com;//定义用于执行T-SQL命令的实例

private SqlParameter param;//定义参数

public int RunProc(string procName)//调用无参数的存储过程

{

com = CreateCommand(procName, null);

com.ExecuteNonQuery();

return (int)com.Parameters["ReturnValue"].Value;

}

public int RunProc(string procName, SqlParameter[] prams)//调用有参数的存储过程

{

com = CreateCommand(procName, prams);

int r = com.ExecuteNonQuery();

return (int)com.Parameters["ReturnValue"].Value;

}

}

用户登录页面如图3所示.当用户输入数据单击“登录”按钮后,就会和数据库进行数据交换.

图3 用户登录页面

将当前用户的登录IP、登录时间等数据写入users表的C#代码如下:

ConnSql con = new ConnSql();

string user_ip = Request.UserHostAddress;//获取用户IP地址信息

//生成参数，用户名参数值来自TextBox1控件

SqlParameter[] prams ={

con.MakeInParam("@username",SqlDbType.VarChar,16,TextBox1.Text.Trim()),

con.MakeInParam("@userip", SqlDbType.VarChar ,50,user_ip),

};

//调用存储过程，第一个参数是存储过程名

con.RunProc("UserLogin", prams);

4 存储过程在发表留言中的应用

4.1数据表介绍

发表留言也是应用程序中经常调用的过程，需要向数据库写入留言内容、留言者、留言时间等数据，留言表的字段名和含义见表2.

表2 ly表数据结构

4.2存储过程的创建

添加留言的存储过程创建语句如下:

CREATE PROC AddLy @title VARCHAR(50),@ly_content VARCHAR(800),@username VARCHAR(16)

AS

INSERT ly(title,ly_content,username,btime) VALUES (@title ,@ly_content,@username, GETDATE())

4.3存储过程的调用

发表留言的常见形式如图4所示.单击“发表”按钮通过RunProc()方法调用存储AddLy，将用户的输入保存到数据表.

图4 发表留言页面

实现留言页面的代码如下:

ConnSql con = new ConnSql();

SqlParameter[] prams ={

con.MakeInParam("@title",SqlDbType.VarChar ,50, TextBox1.Text.Trim() ),

con.MakeInParam("@ly_content" ,SqlDbType.VarChar ,800, TextBox2.Text.Trim()),

con.MakeInParam("@username" ,SqlDbType.VarChar,16,username ),

};

con.RunProc("Add_bbs_ly", prams);

5 结语

存储过程经过编译后存储在数据库中[7]，在数据库内外通过存储过程的名字和参数来执行存储过程，完成存储过程的功能，因存储过程是一组预编译的语句组合，执行速度快，对于那些频繁与数据库交换数据的过程可组合为存储过程，可有效提高数据交互速度，同时有利于保护数据设计细节.

[1]王红,陈功平. 数据完整性机制的研究与实现[J].佛山科学技术学院学报:自然科学版,2015, 33(1):81-87.

[2]崔跃生,张勇,曾春,等. 数据库物理结构优化技术[J].软件学报, 2013, 24(4):761-780.

[3]Carmen Martínez-Cruz, José M. Noguera, M. Amparo Vila. Flexible queries on relational databases using fuzzy logic and ontologies[J]. Information Sciences,2016,366(4):150-164.

[4]张小波,成良玉. vs.net中存储过程使用方法研究[J]. 计算机应用,2004, 24(2):138-140.

[5]郭绍忠,甄涛,贾琦.基于存储过程的海量邮件数据挖掘[J].计算机工程, 2010, 36(1):40-42.

[6]王红,陈功平.数据库安全机制的探讨与实现[J].河北省科学院学报, 2014, 31(3):15-24.

[7]Ion M, Russello G, Crispo B. Enforcing multi-user access policies to encrypted cloud databases. In: Proc. of the IEEE Int’l Symp. on Policies for Distributed Systems and Networks (POLICY 2011). New York: IEEE Computer Society Press, 2011:175-177.

ResearchandImplementationofCallingDatabaseStoredProcedureMethodinNET

WANG Hong, CHEN Gong-ping

(Institute of Information and Electronic Engineering, Lu’an Vocational Technical College, Lu’an Anhui 237158, China)

Applications often read and write data from the database and some of the reading and writing process will be used frequently. Reading and writing data will increase the server pressure and improper method will easily disclose the database design details and endanger the database. Stored procedures are collections of a series of T-SQL statements that are used to perform specific functions in the SQL Server database. Developers can define the stored procedures in advance in the database and call the stored procedure directly when they need, which can increase execution speed and hide database design details. Class encapsulation is often used to exchange data with the database, during which connection class is used first in the ADO.NET technology to connect the database, and then the command class is used to execute the stored procedure.

stored procedure; database; class encapsulation; database application system

1673-2103(2017)05-0029-05

2017-06-07

安徽省高校自然科学研究重点项目(KJ2015A435);安徽省2016年高校优秀青年人才支持计划重点项目(gxyqZD2016570);安徽省2014年高校优秀青年人才支持计划项目；教育部高职教育创新发展行动计划精品在线开放课程项目(XM-06)

王红(1983-)，女，安徽霍邱人，副教授，硕士，研究方向：人工智能，数据库技术.

TP311.131

A