機房收費系統合作——再看資料庫設計

標籤：

    機房合作我負責了最簡單的D層，介面層，工廠層。反正D層是我來寫，於是資料庫索性也就順便設計了。已經是第三次敲機房收費系統了，每次都是相隔半年左右吧。需求搞得透透的了，資料庫也就好設計了。基本跟第二次沒什麼大的區別，就是把Student表和Card表分開了。

    重構的時候，我的資料庫幾乎什麼都用到了：事務，預存程序，觸發器，視圖，聯集查詢等等。所以，這次設計資料庫還是SO Easy的。。並且，為了讓嬋嬋和牛遷遷師哥寫的方便，我把組合查詢都寫成了預存程序！！！！費了一番功夫，但是D層簡單了不少。還記得，上次重構的時候，用觸發器用多了。。出現了一個神奇的事情：當我註冊一張卡的時候，充值10塊錢，會在Card表加1倍的價錢。。這是賠本買賣啊。。後來才發現，觸發器用過頭了。。就改成了事務+預存程序。這次再看資料庫設計，就輕車熟路了~

     下面舉幾個例子~跟大家討論一下~

一、 巧用預存程序解決組合查詢：

    在組合查詢的時候需要用到12個參數：一個表名稱，三個欄位，三個操作符，三個條件，兩個與或關係。這是他們的共性。個性的地方就是，分為三個不同的表，欄位也不同，條件也不同。這樣，我就把共同的SQL查詢語句封裝起來，寫成一個預存程序，當執行組合查詢時段的時候，只有從UI層傳入不同的參數即可，當然泛型集合需要返回不同的類型。預存程序如下：

USE [HezuoCharge]GO/****** Object:  StoredProcedure [dbo].[Proc_GroupQuery]    Script Date: 05/27/2015 21:50:17 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO-- =============================================-- Author:周洲-- Create date:   2015-5-5-- Description: 組合查詢-- =============================================ALTER PROCEDURE [dbo].[Proc_GroupQuery]-- Add the parameters for the stored procedure here@tablename varchar(40),--第一組參數@ziduan1 varchar(40)=null,@fuhao1 varchar(10)=null,@tiaojian1 char(50)=null,@zuhe1 char(10)=null,--第二行參數@ziduan2 varchar(40)=null,@fuhao2 varchar(10)=null,@tiaojian2 char(50)=null,@zuhe2 char(10)=null,--第三行參數@ziduan3 varchar(40)=null,@fuhao3 varchar(10)=null,@tiaojian3 char(50)=nullAS   declare @Sql varchar(400)BEGIN-- SET NOCOUNT ON added to prevent extra result sets from-- interfering with SELECT statements.SET NOCOUNT ON;    -- Insert statements for procedure hereSET @Sql ='SELECT * From '[email protected] +' where '[email protected][email protected]+CHAR(39)[email protected] +CHAR(39)if @zuhe1 !=''SET @[email protected][email protected] +CHAR(32)[email protected][email protected]+CHAR(39)[email protected]+CHAR(39)else execute(@Sql)if @zuhe2 !=''SET @[email protected][email protected] +CHAR(32)[email protected][email protected]+CHAR(39)[email protected]+CHAR(39)elseexecute(@Sql)return 1END

D層調用（以返回Line類型為例）：

#Region "操作員--查看上機記錄--組合查詢"    ''' <summary>    ''' 操作員--查看上機記錄--組合查詢    ''' </summary>    ''' <param name="enGroup"></param>    ''' <returns></returns>    ''' <remarks></remarks>    Public Function Groupline(enGroup As GroupEntity) As List(Of LineEntity) Implements ILine.Groupline        Dim sql As String = "Proc_GroupQuery"        Dim para As SqlParameter() =        {            New SqlParameter("@tablename", enGroup.tablename),            New SqlParameter("@fuhao1", enGroup.fuhao1),            New SqlParameter("@ziduan1", enGroup.ziduan1),            New SqlParameter("@tiaojian1", enGroup.tiaojian1),            New SqlParameter("@tiaojian2", enGroup.tiaojian2),            New SqlParameter("@fuhao2", enGroup.fuhao2),            New SqlParameter("@fuhao3", enGroup.fuhao3),            New SqlParameter("@zuhe1", enGroup.zuhe1),            New SqlParameter("@zuhe2", enGroup.zuhe2),            New SqlParameter("@ziduan2", enGroup.ziduan2),            New SqlParameter("@ziduan3", enGroup.ziduan3),            New SqlParameter("@tiaojian3", enGroup.tiaojian3)}        Dim helper As New SqlHelper        Dim dt As DataTable  '定義dt        Dim mylist As New List(Of LineEntity) '定義泛型        dt = helper.ExecSelect(sql, CommandType.StoredProcedure, para)        If (dt.Rows.Count > 0) Then            mylist = CType(EntityHelper.ConvertToList(dt, mylist), Global.System.Collections.Generic.List(Of Global.Entity.LineEntity))            Return mylist        Else            Throw New Exception("當前條件下沒有記錄！")            Return Nothing        End If    End Function#End Region

二、觸發器幫你結賬：

    結賬的時候需要在三張表上邊更新IScheck欄位的資料，既然設計到三張表，就寫了觸發器。Insert資料的時候，就觸發它的執行。建議大家：觸發器要慎用，因為太靈活了，一旦滿足Insert，update，delete這個觸發條件，它就會執行。相比執行傾向於預存程序的使用，但是靈活也是它的優點，各有千秋吧。

USE [HezuoCharge]GO/****** Object:  Trigger [dbo].[Check]    Script Date: 05/27/2015 21:57:11 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO-- =============================================-- Author:周洲-- Create date: 2015-5-5-- Description:結賬的時候，都要寫上已結賬-- =============================================ALTER TRIGGER [dbo].[Check]    on [dbo].[T_Check]    for Insert   AS    BEGIN      update T_Card set IsCheck ='已結賬' where Carddate =CONVERT (varchar(10),getdate(),120)update T_Recharge set IsCheck ='已結賬' where Rechargedate =CONVERT (varchar(10),getdate(),120)update T_Cancelcard set IsCheck ='已結賬' where Canceldate =CONVERT (varchar(10),getdate(),120)  END

三、巧用函數，幫你算賬

     SQLSERVER提供好多函數，可以幫我們輕輕鬆鬆解決排序，最大值，最小值，平均值，求和等等一些事情。下面是我寫的算總金額的語句。

select table1.remaincash + table2.alladdmoney -table3.allcancelcash from (select top 1 convert(int,Allcash)remaincash from T_Check Order By CheckID desc)table1, (select sum(convert(int,Addmoney  )) alladdmoney from V_TodayRecharge )table2,(select sum(convert(int,Cancelcash  )) allcancelcash from V_TodayCancel)table3

四、當日結賬，用視圖列出資料

以充值表資料列出為例：

SELECT     CardID, Addmoney, Rechargedate, Rechargetime, UserIDFROM         dbo.T_RechargeWHERE     (Rechargedate = CONVERT(varchar(10), GETDATE(), 120))

五、小結：

    本想用重構時候的資料庫，後來看到一篇新浪微博，是.NET程式員面試的一組SQL試題，簡直看傻我眼！！！才發現，我們做的是多麼小兒科的東西。。才費盡心思的在資料庫上面創新的。總之，不要因為事情簡單就不去做！就像導資料一樣，以為是體力活，但是真的可以學到很多東西的，要看你站在什麼樣的高度上去審視這件事情！不僅僅是Excel表的技術體現，當初我的理解是錯的。

    就想起李社河師哥在三合班說過的一句話：別人不願意做的事情，我去做，會發現，簡單的小事情也會學到很多的東西！！（激勵我，恩！！）

    說多了。。END...

機房收費系統合作——再看資料庫設計