5/30 - SQL class-唏嘘老头

今天开撸video conference request form，都是用vb写的，这次用到了sql class，也就是专门的一个class负责与数据库的联络，这个class会调用存在SQL里面的storedprocedure函数，这个函数见后。可以看到他有count，有select，有insert，有update，有delete，带@都是参数名称，参数值是通过外部的sql class用.Parameters.AddWithValue(ArgNames(i), ValueString)输入的。这样一来不用在外部project的code里输入冗长SQL代码，增加reusability，二来SQL参数化防止不良错误语句出现，使用了SQL injection。

最后这个form的问题解决的相当轻松，发现原来是duplicate的原因，那怎么产生duplicate的呢，原来他insert的时候弄错了，输入的empty，这以后一改就全改了。

老头看起来不是很服，于是叫我过去，我就给了他这个面子，把问题说明白，他抱头长叹一声，我也一声唏嘘。后来发现了另一个错误，于是很快我回来改了过来。在测试的过程中，他回来让我告诉安娜把多余的production的DB内容删掉，我就答应了，没想到，安娜听了之后就问为啥你在production测试呢，下次不要这样，要不然啥啥啥问题，被咧了一顿，看来老头还是很滑的，好在这时在学校，算是长了个记性了。

下午帮做了个小documentation，写了个vcr form的test request email，取消了network account的I Accept的checkbox，都是很快就完成的。

USE [Webforms]
GO
/****** Object:  StoredProcedure [dbo].[USP_VidConfSites]    Script Date: 5/30/2014 9:04:53 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:           Joel Montrose
-- Create date: 04/09/2013
-- Description:      Count, Add, Update, Delete or Select conference sites 
--                         for the Video Conference Request form
-- Inputs:           Function
--                                Options include:
--                                       "C"ount,
--                                       "S"elect,
--                                       "I"nsert,
--                                       "U"pdate,
--                                       "D"elete
--                         SiteName - for Add, Update and Delete
--                         NewSiteName - for Update
--                         AlternateName - for Add, Update and Delete
--                         NewAlternateName - for Update
--                         DeliveryMode - Delivery Mode ("DDN" or "GRID")
--                         New DeliveryMode - for Update
-- =============================================
ALTER PROCEDURE [dbo].[USP_VidConfSites]
       -- Add the parameters for the stored procedure here
       (
              @Function varchar(1) = 'S',
              @SiteName varchar(255) = '',
              @AlternateName varchar(255) = '',
              @DeliveryMode varchar(4) = '',
              @NewSiteName varchar(255) = '',
              @NewAlternateName varchar(255) = '',
              @NewDeliveryMode varchar(4) = ''
       )
       
AS
BEGIN
       -- SET NOCOUNT ON added to prevent extra result sets from
       -- interfering with SELECT statements.
       SET NOCOUNT ON;

    -- Insert statements for procedure here
    DECLARE @sSql varchar(MAX)
    DECLARE @sSet varchar(MAX)
    DECLARE @sFunction varchar(1)
    DECLARE @Result bit
    
    DECLARE @SiteNameTrim varchar(255)
    DECLARE   @AlternateNameTrim varchar(255)
       DECLARE       @DeliveryModeTrim varchar(4)
       DECLARE       @NewSiteNameTrim varchar(255)
       DECLARE       @NewAlternateNameTrim varchar(255)
       DECLARE       @NewDeliveryModeTrim varchar(4)
    
    SET @sFunction = UPPER(SUBSTRING(@Function,1,1));
    SET @sSql = ''
    
    SET @SiteNameTrim = LTRIM(RTRIM(@SiteName))
    SET @AlternateNameTrim = LTRIM(RTRIM(@AlternateName))
       SET @DeliveryModeTrim = LTRIM(RTRIM(@DeliveryMode))
       SET @NewSiteNameTrim = LTRIM(RTRIM(@NewSiteName))
       SET @NewAlternateNameTrim = LTRIM(RTRIM(@NewAlternateName))
       SET @NewDeliveryModeTrim = LTRIM(RTRIM(@NewDeliveryMode))
    
    IF @sFunction = 'C'
    BEGIN
              SET @sSql = '' +
              'SELECT COUNT(*) as Count ' +
              'FROM vidConfSites '
              
              SET @sSet = ''
              
              if LEN(@SiteNameTrim) > 0
              BEGIN
                     SET @sSet = 'WHERE SiteName = ''' + @SiteNameTrim + ''' '
              END
              
              if LEN(@AlternateNameTrim) > 0
              BEGIN
                     if LEN(@sSet) = 0
                     BEGIN
                           SET @sSet = 'WHERE '
                     END
                     ELSE
                     BEGIN
                           SET @sSet = @sSet + '   AND '
                     END
                     SET @sSet = @sSet + 'AlternateName = ''' + @AlternateNameTrim + ''' '
              END
              
              if LEN(@DeliveryModeTrim) > 0
              BEGIN
                     IF LEN(@sSet) = 0
                     BEGIN
                           SET @sSet = 'WHERE '
                     END
                     ELSE
                     BEGIN
                           SET @sSet = @sSet + '   AND '
                     END
                     SET @sSet = @sSet + 'DeliveryMode = ''' + @DeliveryModeTrim + ''' '
              END
              
              if LEN(@sSet) > 0
              BEGIN
                     SET @sSql = @sSql + @sSet
              END
              
              EXEC (@sSql);
    END
    
    IF @sFunction = 'S'
    BEGIN
              SET @sSql = '' +
              'SELECT ' +
              '   SiteID, ' +
              '   SiteName, ' +
              '   AlternateName, ' +
              '   DeliveryMode ' +
              'FROM vidConfSites ' 
              
              SET @sSet = ''

              IF LEN(@SiteNameTrim) > 0
              BEGIN
                     SET @sSet = 'WHERE SiteName = ''' + @SiteNameTrim + ''' '
              END
              
              if LEN(@AlternateNameTrim) > 0
              BEGIN
                     IF LEN(@sSet) = 0
                     BEGIN
                           SET @sSet= 'WHERE '
                     END
                     ELSE
                     BEGIN
                           SET @sSet = @sSet + '   AND '
                     END
                     SET @sSet = @sSet + 'AlternateName = ''' + @AlternateNameTrim + ''' '
              END
              
              if LEN(@DeliveryModeTrim) > 0
              BEGIN
                     IF LEN(@sSet) = 0
                     BEGIN
                           SET @sSet = 'WHERE '
                     END
                     ELSE
                     BEGIN
                           SET @sSet = @sSet + '   AND '
                     END
                     SET @sSet = @sSet + 'DeliveryMode = ''' + @DeliveryModeTrim + ''' '
              END
              
              IF LEN(@sSet) > 0
              BEGIN
                     SET @sSql = @sSql + @sSet
              END
              
              SET @sSql = @sSql +
                     'ORDER BY ' + 
                     '   DeliveryMode, ' +
                     '   SiteName'

              EXEC (@sSql);
    END
    
    IF @sFunction = 'I'
              INSERT INTO vidConfSites 
              (
                     SiteName,
                     AlternateName, 
                     DeliveryMode 
              ) 
              VALUES 
              (
                     @SiteNameTrim, 
                     @AlternateNameTrim,
                     @DeliveryModeTrim 
              )
              
              SELECT @@IDENTITY as SiteID
    
    IF @sFunction = 'U'
    BEGIN
              SET @sSql = 'UPDATE vidConfSites '
              SET @sSet = ''
              
              IF LEN(@NewSiteNameTrim) > 0
              BEGIN
                     SET @sSet = 'SET SiteName = ''' + @NewSiteNameTrim + ''' '
              END
              
              IF LEN(@NewAlternateNameTrim) > 0
              BEGIN
                     IF LEN(@sSet) = 0
                     BEGIN
                           SET @sSet = 'SET '
                     END
                     ELSE
                     BEGIN
                           SET @sSet = @sSet + ', '
                     END

                     SET @sSet = @sSet + '      AlternateName = ''' + @NewAlternateNameTrim + ''' '
              END
              
              IF LEN(@NewDeliveryModeTrim) > 0
              BEGIN
                     IF LEN(@sSet) = 0
                     BEGIN
                           SET @sSet = 'SET '
                     END
                     ELSE
                     BEGIN
                           SET @sSet = @sSet + ', '
                     END
                     
                     SET @sSet = @sSet + '      DeliveryMode = ''' + @NewDeliveryModeTrim + ''' '
              END
              
              SET @sSql = @sSql +  @sSet + 
              'WHERE (SiteName = ''' + @SiteNameTrim + ''') ' +
              'AND (AlternateName = ''' + @AlternateNameTrim + ''') ' +
              'AND (DeliveryMode = ''' + @DeliveryModeTrim + ''') '
              
              EXEC (@sSql);
    END
    
    IF @sFunction = 'D'
    BEGIN
              IF LEN(@SiteName) > 0
              BEGIN
                     DELETE FROM vidConfSites 
                     WHERE (SiteName = @SiteNameTrim)
                     
                     IF @@ROWCOUNT > 0
                           SET @Result = 1
                     ELSE
                           SET @Result = 0
                     
                     SELECT @Result [Result]
              END
    END
    
END