Tuesday, May 29, 2012

Writing Dynamic SQL in .Net code behind

Hack The Gibson

Although it is highly against best practices to put your SQL commands in your .Net code, sometimes there is simply no way around it.  The worst thing we want to do is loose the flexibility that SQL affords us by being dynamic.  So lets take a look at Dynamic SQL and then we can look at writing it in our code behind.  In the tier, this would exist at our DATA level, where we can pass back the returned table set to the Business Layer to manipulate and massage into something meaningful.
If you have not already done so, you might want to download the Northwind database and attach it to your development server as this is the database this article uses.  It is not necessary to understand the article, however.

Lets take a look at the dbo.Sales by Year (no underbars in the name?? Shame, shame Microsoft for using spaces).  This is the code that Microsoft gives you -

USE [Northwind]



GO



/****** Object:  StoredProcedure [dbo].[Sales by Year]    Script Date: 05/29/2012 15:04:52 ******/



SET ANSI_NULLS ON



GO



SET QUOTED_IDENTIFIER ON



GO



 



ALTER procedure [dbo].[Sales by Year] 



    @Beginning_Date DateTime, @Ending_Date DateTime AS



SELECT Orders.ShippedDate, Orders.OrderID, "Order Subtotals".Subtotal, DATENAME(yy,ShippedDate) AS Year



FROM Orders INNER JOIN "Order Subtotals" ON Orders.OrderID = "Order Subtotals".OrderID



WHERE Orders.ShippedDate Between @Beginning_Date And @Ending_Date




This is good, but it is setup so that you MUST provide a beginning date and ending date.  What if we want a little more flexibility?  What if we want it to where we ONLY have to provide a beginning date that it will fetch every record after that beginning date?  For instance, I want every record after 22 May 1980 – the date NAMCO released PAC-MAN (or Puck-Man, as it was originally named).



Due to the fact that Microsoft can’t seem to follow its own naming conventions, I have decided to completely rewrite the SP as so-





Use [Northwind]



SET ANSI_NULLS ON



GO



SET QUOTED_IDENTIFIER ON



GO



 



CREATE PROCEDURE dbo.SalesByYear 



    -- Add the parameters for the stored procedure here



    @BeginningDT DateTime = NULL,



    @EndingDT DateTime = NULL



AS



BEGIN



    -- SET NOCOUNT ON added to prevent extra result sets from



    -- interfering with SELECT statements.



    SET NOCOUNT ON;



 



    -- Insert statements for procedure here



    SELECT Orders.ShippedDate, Orders.OrderID, "Order Subtotals".Subtotal, DATENAME(yy,ShippedDate) AS Year



    FROM Orders INNER JOIN "Order Subtotals" ON Orders.OrderID = "Order Subtotals".OrderID



    WHERE Orders.ShippedDate Between @BeginningDT And @EndingDT



END



GO



 



-- dbo.SalesByYear '05/22/1980', '5/29/2012'




Obviously, the use command tells us which database to use initially for security reasons.  You will not that I removed the spaces from the procedure name and used camel-casing.  We use the same select statement and where clause from the original SP and the final line is commented out.  It is something that I put into all my SP’s so that all I have to do is highlight the name with some default values to invoke the SP.  Notice that I gave our two variables default values.  It is important to give them values that they would not hold normally so we can discern whether it is a true value or one that we gave them.



So for our third iteration, lets make it DYNAMIC -





Use [Northwind]



SET ANSI_NULLS ON



GO



SET QUOTED_IDENTIFIER ON



GO



 



ALTER PROCEDURE dbo.SalesByYear 



    -- Add the parameters for the stored procedure here



    @BeginningDT    varchar(12) = '1/1/1900',



    @EndingDT        varchar(12) = '1/1/1900'



AS



BEGIN



    Declare 



            @sql_0            nvarchar(max)



        ,    @sql_1            nvarchar(max)



        ,    @sql_statement    nvarchar(max)



        



    SET NOCOUNT ON;



 



    -- Insert statements for procedure here



    Select @sql_0 = '



            SELECT 



                Orders.ShippedDate            , 



                Orders.OrderID                , 



                "Order Subtotals".Subtotal    , 



                DATENAME(yy,ShippedDate) AS Year



            FROM Orders 



            INNER JOIN 



                "Order Subtotals" ON Orders.OrderID = "Order Subtotals".OrderID



                    '



    IF @EndingDT != '1/1/1900'



        BEGIN



            Select @sql_1 = '



                WHERE Orders.ShippedDate Between ''' + @BeginningDT + ''' And 



                ''' + @EndingDT + '''



                            '



        End



    Else



        Begin



            Select @sql_1 = '



                Where Orders.ShippedDate > ''' + @BeginningDT + '''



                            '



        End



        



    Select @sql_statement = @sql_0 + @sql_1



    



    -- SELECT @sql_statement



    



    EXECUTE sp_executesql @statement = @sql_statement



END



GO



 



-- dbo.SalesByYear '05/22/1980', '5/29/2012'




Now this makes the query dynamic, where you do not have to enter an ending date, however, if you do not you will get every record from the beginning date and greater.  To prove this theory, lets highlight just the beginning date and invoke the SP -

image



By hightlighting just the first the SP Name and first value (beginning date – due to ordinal positioning) and then press the Execute button, we get the following return -

image



Good stuff!!



Healthy TidBit: If you do not want to be reliant on ordinal positioning when passing variables to your SP, you can specifically name them in the call, Like so -

image


OR

image


They both will give you the same results because we are specifically naming our variables.



Now, to rewrite this procedure in VB.Net.  So that it is just as flexible to make it easier to read, we are simply going to use an adapter and fill it -







Dim beginningDT As DateTime = DateTime.MinValue



Dim endingDT As DateTime = DateTime.MinValue



Dim sbSQL As Stringbuilder = New StringBuilder



Dim strWhere As String = " Where "



 



'Start building our SQL Query



sbSQL.Add(" Select Orders.ShippedDate, Orders.OrderID, [Order Subtotals].Subtotal, ")



sbSQL.Add(" DateName(yy,ShippedDate) As Year")



sbSQL.Add(" From Orders ")



sbSQL.Add(" Inner Join [Order Subtotals] On Orders.OrderID = [Order Subtotals].OrderID ")



 



'Check our Where Clause



If (endingDT = DateTime.MinValue) Then



    sbSQL.Add(strWhere & " Orders.ShippedDate > '" & beginningDT & "'")



    strWhere = " And "



Else 



    sbSQL.Add(strWhere & " Orders.ShippedDate Between '" & beginningDT & "' AND '" & endingDT & "'")



    strWhere = " And "



End If



 



Dim cn As SqlConnection = New SqlConnection(ConnectionStrings("Northwind").ToString())



Dim adapt As SqlDataAdapter = New SqlDataAdapter(sbSQL.Text, cn)



    Try



            cn.Open()



            adapt.Fill(ds, "Orders")



        Catch ex As Exception



        Finally



            cn.Close()



        End Try






So here we dimension two date variables and initialize them with DateTime.MinValue.  This is because under no normal circumstances will these variables hold these values, so if they do hold the value- it’s because we set them that way, thus they did not inherit the value.  We also dimension a new StringBuilder and a String which we initialize to WHERE to fulfill our Where Statement in the SQL.  Then we build our SQL statement.  You will notice that at the beginning of each statement line and at the end, I add a space.  TSQL is a scripting language and ignores white spaces so you can add as many as you want, I do this to ensure that I do have spaces between my statements when it is sent to SQL.

Next we need to check our ending date to see if we have a value.  Since we initialized it with MinValue, if it equals MinValue, then we know no value was passed in and we can assume that the user supplied no value.  We write the appropriate where clause to the stringbuilder.


Finally, an interesting piece of code – we change our strWhere to AND.  This is incase we either have or might add additional Where conditions.  You can not have two (or more) where clauses, thus the correct verbiage is AND.


Finally, through the laborious method known as ADO.Net, we make our connection and send the command which, fills our adapter under table 0 – otherwise known as Orders (which we named during the fill command).  Now we can use the adapter like any other DataSet.



We have taken a normal SQL query, made it dynamic, and then moved it to ASP.Net code behind. 



Happy .Netting

Hack the Gibson

No comments:

Post a Comment