機房收費系統（VB.NET）——預存程序實戰，收費系統vb.net

機房收費系統（VB.NET）——預存程序實戰，收費系統vb.net

       
          最初接觸預存程序是在耿建玲老師的視頻裡，當初只是草草過了一遍，只是有了個印象，知道了這個名詞；大二時也有SqlServer資料庫這門課，不過老師沒講，自己也沒看；真正對預存程序的瞭解來自於自學考試中的《資料庫系統原理》，在考試中，知道預存程序是幹嘛的，在紙上怎麼寫，但從來沒有在DBMS中親手敲過。於是機房收費系統給了我這個機會。
       在這裡不再過多敘述關於預存程序德基本知識，唯寫一下在機房收費系統這個小項目中是如何用到預存程序的。

       背景：
       機房收費系統中有一個註冊的功能，原型圖如下：

       
        
        註冊時，需要對資料庫中的三個表進行更新（向卡表T_Card、學生表T_Student、充值表T_Register中分別新增一條記錄），所以，執行時，如果用執行SQL語句的方式，那就需要執行三次SQL語句：

        1：向卡表T_Card添加記錄

 insert into T_Card(cardNumber ,balance ,type ,stuNumber ,status ,isChecked ) values(@cardNumber ,@balance, @type ,@stuNumber ,@status ,@isChecked )

        2：向學生表T_Student添加記錄

 insert into T_Student (stuNumber,stuName,stuSex,stuMajor,stuGrade,stuClass  ,comment ) values (@stuNumber ,@stuName ,@stuSex ,@stuMajor ,@stuGrade ,@stuClass,@comment )

        3：向充值表T_Register添加記錄

insert into chargesystem.dbo.T_Recharge (userID,cardNumber,rechargeCash ,rechargeDate ,rechargeTime ,isChecked )values(@userID,@cardNumber ,@balance, CONVERT(varchar,getdate(),120),CONVERT(varchar,GETDATE(),108),'未結賬')

        預存程序的使用        對於上面的需求，如果用預存程序，在資料庫裡建立預存程序之後，在代碼裡只需負責直接執行這個預存程序即可，而不用連續多次串連、操作資料庫。

        一、建立預存程序        建立預存程序有兩種方法（因為系統用的資料庫為SqlServer2008，所以這裡以此為例）：
        （1）、手動建立預存程序：        物件總管中：資料庫→ChargeSystem(資料庫名稱)→可程式化性→右鍵“預存程序”→建立預存程序

         
        建立的預存程序可以說是一個已經成型的預存程序德模板，我們只需在上面修改一下預存程序名稱、參數、執行語句等代碼就OK了。     

       （2）、SQL語句添加預存程序        直接建立查詢，在代碼編輯視窗編寫預存程序SQL代碼，基本文法為：
         CREATE  PROCEDURE  PROC_NAME
               @[參數名] [類型],@[參數名] [類型]……
         AS
         BEGIN
               [過程體].........
         END

         用第一種方法建立的預存程序基本上也是這個結構，加入相應的參數和過程體之後，完整的預存程序為：

        

CREATE PROCEDURE PROC_Register-- 定義參數@cardNumber varchar(6),@balance decimal(5,1),@type nvarchar(20),@status nvarchar(50),@isChecked nvarchar(10),@stuNumber varchar(18),@stuName nvarchar(10),@stuSex varchar(6),@stuMajor nvarchar(30),@stuGrade nvarchar(20),@stuClass nvarchar(20),@comment nvarchar(100),@userID varchar(18)ASBEGIN        --向表中插入資料insert into ChargeSystem .dbo.T_Card(cardNumber ,balance ,type ,stuNumber ,status ,isChecked ) values(@cardNumber ,@balance, @type ,@stuNumber ,@status ,@isChecked )insert into ChargeSystem .dbo.T_Student (stuNumber,stuName,stuSex,stuMajor,stuGrade,stuClass  ,comment ) values (@stuNumber ,@stuName ,@stuSex ,@stuMajor ,@stuGrade ,@stuClass,@comment )insert into chargesystem.dbo.T_Recharge (userID,cardNumber,rechargeCash ,rechargeDate ,rechargeTime ,isChecked )values(@userID,@cardNumber ,@balance, CONVERT(char(10),getdate(),120),CONVERT(varchar,GETDATE(),108),'未結賬')END

            注意：無論是用哪種方式建立的預存程序，都需要點擊執行來存到伺服器裡，才可以通過以後的調用來執行，單純地儲存（ctrl+C）只是把這個預存程序檔案儲存在本地，而不會存入資料庫伺服器中。

       當建立好預存程序時，可以建立查詢執行“exec 預存程序名 @參數1=值1,@參數2=值2……”來驗證預存程序是否正確。

        二、代碼中調用預存程序        調用預存程序跟執行SQL語句的方式大同小異，需要注意的是，執行SQL語句時，命令對象Command的CommandType的值為CommandType.Text，而執行預存程序時，CommandType的值為CommandType.StoredProcedure。
        在本例中具體代碼為：

        D層代碼：       

Public Class SqlServerRegisterDAL : Implements IDAL.IRegister    Public Function Insert(ByVal enCard As Entity.CardEntity, ByVal enStudent As Entity.StudentEntity, ByVal userID As String) As Boolean Implements IDAL.IRegister.Insert        Dim sqlHelper As New SqlHelper                                 '定義SqlHelper執行個體        Dim cmdType As CommandType = CommandType.StoredProcedure       '定義資料庫命令類型        Dim cmdText As String = "PROC_Register"                        '資料庫執行字串        Dim parameters As SqlParameter()                               '定義參數數組，負責向預存程序中的變數傳值        '為參數數組中的參數一一賦值        parameters = {New SqlParameter("@cardNumber", enCard.CardNumber), New SqlParameter("@balance", enCard.Balance),                      New SqlParameter("@type ", enCard.CardType), New SqlParameter("@status", enCard.Status),                      New SqlParameter("@isChecked", enCard.IsChecked), New SqlParameter("@stuNumber", enStudent.StuNumber),                      New SqlParameter("@stuName", enStudent.StuName), New SqlParameter("@stuSex", enStudent.StuSex),                      New SqlParameter("@stuMajor", enStudent.StuMajor), New SqlParameter("@stuGrade", enStudent.StuGrade),                      New SqlParameter("@stuClass", enStudent.StuClass), New SqlParameter("@comment", enStudent.StuComment),                      New SqlParameter("@userID", userID)}        '判斷是否有查詢結果        If sqlHelper.ExecuteNonQuery(cmdText, cmdType, parameters) Then            Return True        Else            Return False        End If    End FunctionEnd Class

        SqlHelper代碼：

Public Class SqlHelper    '從設定檔中擷取連接字串的值    Dim strConnection As String = ConfigurationSettings.AppSettings("strConnection")    '建立資料庫連接對象conn    Dim conn As SqlConnection    '建立資料庫操作類cmd    Dim cmd As New SqlCommand    ''' <summary>    ''' 建構函式，執行個體化類時就初始化資料庫連接對象    ''' </summary>    ''' <remarks></remarks>    Public Sub New()        conn = New SqlConnection(strConnection)    End Sub    ''' <summary>    ''' 關閉釋放SqlCommand對象    ''' </summary>    ''' <param name="cmd">需要關閉的SqlCommand對象</param>    ''' <remarks>cmd.Dispose()直接釋放command資源，不知這麼做對系統效能怎麼樣，先這麼著，以後再繼續最佳化</remarks>    Public Sub CloseCommand(ByVal cmd As SqlCommand)        If Not IsNothing(cmd) Then            cmd.Dispose()            cmd = Nothing        End If    End Sub    ''' <summary>    ''' 關閉資料庫連接    ''' </summary>    ''' <param name="conn">需要關閉的SqlConnection對象</param>    ''' <remarks>關閉資料庫連接，但並沒有釋放，而是儲存在串連池中，需要的時候還可以通過Open()方法開啟串連</remarks>    Public Sub CloseConnection(ByVal conn As SqlConnection)        If Not IsNothing(conn) Then            conn.Close()        End If    End Sub    ''' <summary>    ''' 有參數的 增 刪 改 操作    ''' </summary>    ''' <param name="cmdText">需要執行的SQL命令</param>    ''' <param name="cmdType">所執行命令的，一般是sql語句、預存程序或表</param>    ''' <param name="sqlParameters">參數數組</param>    ''' <returns>返回受影響的行數 類型為整型</returns>    ''' <remarks></remarks>    Public Function ExecuteNonQuery(ByVal cmdText As String, ByVal cmdType As String, ByVal sqlParameters As SqlParameter()) As Integer       Try           conn.Open()                                 '開啟資料庫連接           cmd.CommandText = cmdText                   '設定查詢語句           cmd.CommandType = cmdType                   '設定一個值，解釋cmdType(如果值為StoredProcedure時，調用的是預存程序)           cmd.Connection = conn                       '設定串連           cmd.Parameters.AddRange(sqlParameters)      '傳入參數               Dim affectedRows As Integer           affectedRows = cmd.ExecuteNonQuery           Return affectedRows                         '返回執行所受影響行數        Catch ex As Exception           MsgBox(ex.Message, MsgBoxStyle.OkOnly, "溫馨提示")           Return 0                                    '如果出錯，則返回0        Finally            cmd.Parameters.Clear()                      '清楚參數           Call CloseCommand(cmd)                      '關閉並釋放Command           Call CloseConnection(conn)                  '關閉串連conn        End Try    End Function   End Class

      為什麼要使用預存程序      

       註冊學生卡號在這個系統中不算個大模組，但這個小小的需求，需要與資料庫中三個表的資料打交道，而在以往的操作，竟然連了三次資料庫，執行了三次SQL語句。

       這樣頻繁地開啟與關閉與資料庫的串連，需要消耗大量系統資源，降低執行速度。這時就需要考慮用預存程序來代替執行如此之多的SQL語句。

       1、一般SQL語句每執行一次就需要編譯一次，而預存程序只是在創造時進行編譯，以後每次執行都不需要再進行編譯。

       2、預存程序就是相當於把多個需要執行的SQL語句集合起來，變成一條SQL語句，當然就只需串連和執行一次就可以得到結果。

       3、安全性高。可以指定預存程序的使用權，防止SQL注入。

       4、系統升級、維護比較方便。

       總結：

       ★ 當涉及到多個SQL語句執行，需要多次串連資料庫，或者需要對多張表進行處理時，可以將這些操作封裝在一起，即建立預存程序，以後每次需要的時候直接調用執行，即可執行所有的操作，避免了多次開啟、關閉資料庫連接。

       ★ 當涉及到比較複雜的需求時（比如機房收費系統中的下機結算消費金額就可以採用預存程序），比如排序、計算等等，可以把資料直接傳到預存程序,一系列操作在資料庫伺服器裡進行，減小了用戶端與伺服器之間的資料流量，同時還保證了系統的安全性。

  

       到了這裡大家腦子裡可能會有個問題：既然讓一個預存程序執行多個任務，那麼萬一在執行的過程中，這些任務中的一個或者幾個任務沒有完成，該如何是好？這時事務就派上用場了…快去實踐吧…

       

        

vbnet怎使用預存程序？

預存程序把一個或多個T-SQL語句組合到一個邏輯單元中，在SQL Server資料庫中儲存為一個對象。在預存程序建立後，它的T-SQL定義儲存在sys.sql_module系統目錄檢視中。當預存程序首次執行的時候，SQL Server建立執行計畫並把它儲存在計劃記憶體緩衝中。然後SQL Server就能對這個預存程序的後續執行重用計劃。和沒有編譯且沒有準備好的等價即時查詢相比，預存程序更加快速可靠。還可以建立使用.net通用語言執行平台（CLR）程式集的預存程序。使用預存程序有很多好處，而沒什麼壞處：協助在資料層聚集T-SQL代碼。協助大的即時查詢減少網路流量。促進代碼的可複用性。淡化資料擷取的方法。與視圖不同，預存程序可以利用劉控制技術、暫存資料表、表變數等。預存程序對查詢回應時間的影響比較穩定。預存程序能作為控制層，排除了直接存取SQL Server執行個體和它的資料庫表可能引起的安全隱患。預存程序能用於多種不同的活動，包括簡單的SELECT、INSERT、UPDATE、DELETE等。T-SQL活動能混合在單個預存程序中，或者以模組形式建立預存程序，為每一個或一組任務建立多個預存程序。沒有參數的預存程序的基本文法如下：CREATE PROCEDURE [Schema_name.] procedure_nameAS { <sql_statement> [...n ] }帶參數的預存程序可以接收外部傳入參數多達2100個。文法如下：CREATE {PROC|PROCEDURE} [Schema_name.] procedure_name [; number][ { @parameter [ type_schema_name.] data_type } [VARYING ] [=default] [OUT | OUTPUT ] [READONLY] ] [ , ... n][ WITH <procedure_option> [ , ... n] ][FOR REPLICATION ]AS {sql_statement> [;] [ ...n] | <method_specifier> }
 
vbnet 怎獲得預存程序的傳回值

Dim cmd As New SqlCommand() cmd.CommandType = CommandType.StoredProcedure cmd.CommandText = "proc name" '參數名可以任意，但最好與預存程序的return一致 '預設值隨便寫 Dim ret As New SqlParameter("ret", 0) '傳回值類型才有下面一句 ret.Direction = ParameterDirection.ReturnValue cmd.Parameters.Add(ret) '其他的參數和一般的sql語句沒區別 '... cmd.ExecuteNonQuery() '得到了ret