機房合作我負責了最簡單的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 ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- 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)=null AS 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 here SET @Sql ='SELECT * From '+@tablename +' where '+@ziduan1+@fuhao1+CHAR(39)+@tiaojian1 +CHAR(39) if @zuhe1 !='' SET @Sql=@Sql+@zuhe1 +CHAR(32)+@ziduan2+@fuhao2+CHAR(39)+@tiaojian2+CHAR(39) else execute(@Sql) if @zuhe2 !='' SET @Sql=@Sql+@zuhe2 +CHAR(32)+@ziduan3+@fuhao3+CHAR(39)+@tiaojian3+CHAR(39) else execute(@Sql) return 1 END
D層調用(以返回Line類型為例):
#Region "操作員--查看上機記錄--組合查詢" '''''' 操作員--查看上機記錄--組合查詢 ''' ''' '''''' 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 ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- 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, UserID FROM dbo.T_Recharge WHERE (Rechargedate = CONVERT(varchar(10), GETDATE(), 120))
本想用重構時候的數據庫,後來看到一篇新浪微博,是.NET程序員面試的一組SQL試題,簡直看傻我眼!!!才發現,我們做的是多麼小兒科的東西。。才費盡心思的在數據庫上面創新的。總之,不要因為事情簡單就不去做!就像導數據一樣,以為是體力活,但是真的可以學到很多東西的,要看你站在什麼樣的高度上去審視這件事情!不僅僅是Excel表的技術體現,當初我的理解是錯的。