今天开撸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