.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