王 紅，陳功平

(六安職業(yè)技術(shù)學院信息與電子工程學院，安徽 六安 237158)

.NET中調(diào)用數(shù)據(jù)庫存儲過程方法的研究與實現(xiàn)*

王 紅，陳功平

(六安職業(yè)技術(shù)學院信息與電子工程學院，安徽 六安 237158)

應用程序經(jīng)常從數(shù)據(jù)庫讀寫數(shù)據(jù)，有的讀寫過程會被經(jīng)常使用，數(shù)據(jù)的讀寫會增加服務器端壓力，方法不當還容易泄露數(shù)據(jù)庫設計細節(jié)，危害數(shù)據(jù)庫安全.存儲過程是SQL Server數(shù)據(jù)庫中用于完成特定功能的一系列T-SQL語句的集合，開發(fā)人員在數(shù)據(jù)庫中預先定義存儲過程，當需要完成功能時可直接調(diào)用存儲過程，可以提高執(zhí)行速度，隱藏數(shù)據(jù)庫設計細節(jié).面向?qū)ο蟪绦蛟O計中，常采用類封裝的形式與數(shù)據(jù)庫交換數(shù)據(jù)，先使用ADO.NET技術(shù)中的Connection類連接數(shù)據(jù)庫再使用Command類執(zhí)行存儲過程.

存儲過程;數(shù)據(jù)庫;類封裝;數(shù)據(jù)庫應用系統(tǒng)

SQL Server數(shù)據(jù)庫是實際應用最廣泛、管理能力最強的數(shù)據(jù)庫管理系統(tǒng)[1].用戶都是通過應用程序中的頁面和數(shù)據(jù)庫進行交互，開發(fā)人員為了提高數(shù)據(jù)交互速率，隱藏數(shù)據(jù)庫設計細節(jié)，經(jīng)常使用存儲過程包裝數(shù)據(jù)查詢和數(shù)據(jù)操縱過程[2].應用程序訪問SQL Server數(shù)據(jù)庫的存儲過程要借助ADO.NET技術(shù)，一般流程為:

1)數(shù)據(jù)庫中使用T-SQL語言定義存儲過程.

2)應用程序中使用ADO.NET中的Connection類連接數(shù)據(jù)庫，使用Command類執(zhí)行存儲過程.

3)應用程序頁面中使用開發(fā)語言調(diào)用執(zhí)行存儲過程的方法.

1 存儲過程

1.1存儲過程的概念

存儲過程是大型數(shù)據(jù)庫中用于完成某項功能所要用到的一組命令集合，在SQL Server數(shù)據(jù)庫中是若干條T-SQL語句的集合，用于完成某項功能[3].在數(shù)據(jù)庫應用系統(tǒng)開發(fā)中通常將那些經(jīng)常用到的數(shù)據(jù)操縱過程在數(shù)據(jù)庫中包裝成存儲過程，再在應用系統(tǒng)開發(fā)環(huán)境中定義調(diào)用存儲過程的方法，最后在要用到數(shù)據(jù)操縱過程的控件中運行調(diào)用存儲過程的方法.存儲過程在數(shù)據(jù)庫和用戶應用頁面之間的調(diào)用關(guān)系如圖1所示.

圖1 存儲過程的調(diào)用關(guān)系

開發(fā)人員設計存儲過程時先在數(shù)據(jù)庫中定義，再定義方法調(diào)用數(shù)據(jù)庫中的存儲過程，最后調(diào)用方法；用戶使用時方向正好相反.

存儲過程將一組預編譯的T-SQL語句作為數(shù)據(jù)庫對象保存，為重復調(diào)用執(zhí)行該組語句提供方便；而且存儲過程是預編譯的，第一次執(zhí)行時SQL Server為其產(chǎn)生查詢計劃并保留在內(nèi)存中，以后再調(diào)用就無須再編譯，所以執(zhí)行速度快；存儲過程可以提供輸入、輸出參數(shù)，避免數(shù)據(jù)庫細節(jié)暴露，是數(shù)據(jù)庫安全設計的一種方法.

1.2存儲過程的創(chuàng)建

用戶存儲過程可分成有參數(shù)和無參數(shù)兩種類型，在SQL Server數(shù)據(jù)庫中使用T-SQL命令創(chuàng)建存儲過程，命令格式如下.

CREATE PROC[EDURE] 存儲過程名 [參數(shù)名 類型 [=默認值]，……]

AS

T-SQL語句 [T-SQL語句……]

其中存儲過程名不能與現(xiàn)有數(shù)據(jù)庫對象名重復，參數(shù)名的起始字符必須為@符號，定義多個參數(shù)時用逗號隔開，AS關(guān)鍵字后的T-SQL語句實現(xiàn)了存儲過程的功能.

2 相關(guān)技術(shù)

2.1ADO.NET

ADO.NET是ADO的升級版在NET編程環(huán)境中首選數(shù)據(jù)訪問技術(shù)[4]，主要包含Connection、Command、DataReader、DataSet、DataAdapter、DataTable幾種常用類.

Connection類用于連接數(shù)據(jù)庫，是其它類與數(shù)據(jù)庫交互的基礎，連接SQL Server數(shù)據(jù)庫的類名為SqlConnection；Command類用于執(zhí)行查詢、修改、添加、刪除等數(shù)據(jù)操縱命令，在執(zhí)行命令之前要連接到數(shù)據(jù)庫，Command類對象的常用方法是ExecuteNonQuery，經(jīng)常用于執(zhí)行不帶返回值的命令，執(zhí)行SQL Server數(shù)據(jù)庫命令的類名為SqlCommand；DataReader類對象可存儲Command類對象的查詢結(jié)果，由于只能按照一定的順序讀取數(shù)據(jù)，所以速度快，但要操作數(shù)據(jù)就十分不方便；DataSet類對象中的數(shù)據(jù)存儲在內(nèi)存中，讀取速度快，數(shù)據(jù)操縱方便，還可以保存多個查詢結(jié)果，DataSet對象能被所有DataProviders對象使用；DataTable類對象可用表格形式臨時保存數(shù)據(jù)的一個虛擬表格，是ADO.NET技術(shù)中最常用的類之一.

2.2SQLServer數(shù)據(jù)庫中的流程控制語句

SQL Server數(shù)據(jù)庫除了強大的數(shù)據(jù)存儲和數(shù)據(jù)管理能力外，還具備使用T-SQL語言編寫程序完成特殊功能的能力，T-SQL語言同樣可以實現(xiàn)順序、選擇和循環(huán)三種程序結(jié)構(gòu)[5]，選擇結(jié)構(gòu)可使用if…else、case…when流程控制語句實現(xiàn)，循環(huán)結(jié)構(gòu)可使用while流程控制語句實現(xiàn).

2.2.1 基本語句

程序設計中的第一步都是定義變量來保存中間量，SQL Server數(shù)據(jù)庫中的變量有局部變量和全局變量兩種，全局變量由開發(fā)人員定義，定義變量的語法格式如下.

DECLARE {@變量名 數(shù)據(jù)類型}[,…]

例如：DECLARE @a INT ,@b CHAR(6),@c FLOAT—同時定義了3個類型不同的變量.

成功定義局部變量后，就可以為變量賦值，T-SQL語言中的賦值語句有兩種:

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

SET 變量名=表達式

可見SET一次只可以給一個變量賦值，SELECT一次可以給多個變量賦值；SELECT 可以將數(shù)據(jù)表中的字段值賦給變量，SET不可以.

2.2.2 IF…ELSE分支語句

IF…ELSE分支結(jié)構(gòu)的語法格式如下.

IF 邏輯表達式

語句塊1

ELSE

語句塊2

當邏輯表達式成立時，執(zhí)行語句塊1，否則執(zhí)行語句塊2，多分支結(jié)構(gòu)可以使用IF…ELSE IF…ELSE流程控制語句實現(xiàn).語句塊如果由多條T-SQL語句組成，可用BEGIN…END界定起來，相當于C語言中的大括號.

2.2.3 循環(huán)語句

WHILE循環(huán)語句的語法格式如下.

WHILE 邏輯表達式

循環(huán)體語句

WHILE語句的執(zhí)行流程如圖2所示.

圖2 WHILE語句執(zhí)行流程

3 存儲過程在用戶登錄中的應用

3.1數(shù)據(jù)表介紹

無論是基于手機還是基于Web的應用程序，都會提供用戶登錄接口[6]，用戶登錄過程使用率高且登錄時要與后臺數(shù)據(jù)庫打交道，記錄當前登錄用戶的一些基本數(shù)據(jù)，比如登錄的IP地址，登錄時間等.用于存儲用戶基本信息的表結(jié)構(gòu)中部分字段名及含義，見表1.

表1 users表數(shù)據(jù)結(jié)構(gòu)

3.2存儲過程的創(chuàng)建

用戶登錄時要向數(shù)據(jù)庫寫入當前登錄的IP地址、登錄時間和上次登錄時間，每天登錄增加1個用戶積分，同一天內(nèi)登錄只增加一次.使用存儲過程的方法和數(shù)據(jù)庫交換數(shù)據(jù)，先在數(shù)據(jù)庫中創(chuàng)建存儲，創(chuàng)建語句如下：

--存儲過程名字是UserLogin，@username和@userip是參數(shù)

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

--當用戶不在同一天內(nèi)登錄時，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數(shù)據(jù)庫的存儲過程存放在當前數(shù)據(jù)庫“可編程性”的子文件夾“存儲過程中”.

3.3定義調(diào)用方法

在數(shù)據(jù)庫中定義存儲過程之后，就可以在應用程序中直接調(diào)用或使用類封裝的形式調(diào)用數(shù)據(jù)庫中的存儲過程.以Visual Studio開發(fā)環(huán)境的Web網(wǎng)站應用程序為例，常用類封裝的形式調(diào)用存儲過程，下列程序段使用C#語言定義名為ConnSql的類、RunProc()方法實現(xiàn)存儲過程調(diào)用.

public class ConnSql

{

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

private SqlConnection con=new SqlConnection(constr);//定義連接類的實例

private SqlCommand com;//定義用于執(zhí)行T-SQL命令的實例

private SqlParameter param;//定義參數(shù)

public int RunProc(string procName)//調(diào)用無參數(shù)的存儲過程

{

com = CreateCommand(procName, null);

com.ExecuteNonQuery();

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

}

public int RunProc(string procName, SqlParameter[] prams)//調(diào)用有參數(shù)的存儲過程

{

com = CreateCommand(procName, prams);

int r = com.ExecuteNonQuery();

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

}

}

用戶登錄頁面如圖3所示.當用戶輸入數(shù)據(jù)單擊“登錄”按鈕后,就會和數(shù)據(jù)庫進行數(shù)據(jù)交換.

圖3 用戶登錄頁面

將當前用戶的登錄IP、登錄時間等數(shù)據(jù)寫入users表的C#代碼如下:

ConnSql con = new ConnSql();

string user_ip = Request.UserHostAddress;//獲取用戶IP地址信息

//生成參數(shù)，用戶名參數(shù)值來自TextBox1控件

SqlParameter[] prams ={

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

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

};

//調(diào)用存儲過程，第一個參數(shù)是存儲過程名

con.RunProc("UserLogin", prams);

4 存儲過程在發(fā)表留言中的應用

4.1數(shù)據(jù)表介紹

發(fā)表留言也是應用程序中經(jīng)常調(diào)用的過程，需要向數(shù)據(jù)庫寫入留言內(nèi)容、留言者、留言時間等數(shù)據(jù)，留言表的字段名和含義見表2.

表2 ly表數(shù)據(jù)結(jié)構(gòu)

4.2存儲過程的創(chuàng)建

添加留言的存儲過程創(chuàng)建語句如下:

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存儲過程的調(diào)用

發(fā)表留言的常見形式如圖4所示.單擊“發(fā)表”按鈕通過RunProc()方法調(diào)用存儲AddLy，將用戶的輸入保存到數(shù)據(jù)表.

圖4 發(fā)表留言頁面

實現(xiàn)留言頁面的代碼如下:

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 結(jié)語

存儲過程經(jīng)過編譯后存儲在數(shù)據(jù)庫中[7]，在數(shù)據(jù)庫內(nèi)外通過存儲過程的名字和參數(shù)來執(zhí)行存儲過程，完成存儲過程的功能，因存儲過程是一組預編譯的語句組合，執(zhí)行速度快，對于那些頻繁與數(shù)據(jù)庫交換數(shù)據(jù)的過程可組合為存儲過程，可有效提高數(shù)據(jù)交互速度，同時有利于保護數(shù)據(jù)設計細節(jié).

[1]王紅,陳功平. 數(shù)據(jù)完整性機制的研究與實現(xiàn)[J].佛山科學技術(shù)學院學報:自然科學版,2015, 33(1):81-87.

[2]崔躍生,張勇,曾春,等. 數(shù)據(jù)庫物理結(jié)構(gòu)優(yōu)化技術(shù)[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]郭紹忠,甄濤,賈琦.基于存儲過程的海量郵件數(shù)據(jù)挖掘[J].計算機工程, 2010, 36(1):40-42.

[6]王紅,陳功平.數(shù)據(jù)庫安全機制的探討與實現(xiàn)[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年高校優(yōu)秀青年人才支持計劃重點項目(gxyqZD2016570);安徽省2014年高校優(yōu)秀青年人才支持計劃項目；教育部高職教育創(chuàng)新發(fā)展行動計劃精品在線開放課程項目(XM-06)

王紅(1983-)，女，安徽霍邱人，副教授，碩士，研究方向：人工智能，數(shù)據(jù)庫技術(shù).

TP311.131

A