September 26, 2008 at 11:53 am
The method(s) for setting the command text of a SqlCommand in CLR would be either in the constructor
myCommand = new SqlClient.SqlCommand(strSQL, myConnection)
or by setting the CommandText property of a command object
myCommand.CommandText = "SELECT * FROM USERS"
My current issue is that I want to write a relatively complex sql statement but VB (and any other language) make it hard to do so in an easily readable way. For example:
strSQL = "SELECT CASE WHEN d2.Ident_Value IS NULL OR d2.Ident_Value = '' " _
& " THEN (h2.ident_value + ISNULL('*' + h.ident_value, '') " _
& " + ISNULL('*' + h3.ident_value, '')) " _
& " ELSE (ISNULL(d2.Ident_Value, '') + ISNULL('*' + d.Ident_Value, '') " _
& " + ISNULL('*' + d1.Ident_Value, '') + ISNULL('*' + d3.Ident_Value,'')) " _
& " END AS insured_name, " _
& " (ISNULL(c.Street_1,'') + ISNULL(' ' + c.street_2, '')) AS insured_address, c.City,c.State_Province, c.Postal_Code, " _
& " CASE WHEN b.Ident_Type_ID = 1046 " _
& " THEN CASE " _
& " WHEN CONVERT(CHAR(8), CAST(e.Ident_Value AS DATETIME), 112 ) IS NULL " _
& " THEN CONVERT(CHAR(8), CAST(e1.Ident_Value AS DATETIME), 112) " _
& " ELSE CONVERT(CHAR(8), CAST(e1.Ident_Value AS DATETIME), 112) " _
& " End " _
& "ELSE '' " _
& " END AS insured_dob, " _
& " CASE WHEN b.Ident_Type_ID = 1046 " _
& " THENCASE " _
& " WHEN f.Ident_Value IS NULL OR f.ident_value = '' " _
& " THEN h8.Ident_Value " _
& " ELSE f.Ident_Value " _
Is there is an easy way to do this so that a query remains legible but doesn't require a timely manual conversion process when going from SSMS to VB? I know in any other VB project I would have a designer that would build my command objects but it doesn't appear to be available when building a SQL Server Project. Is there a way to make it available?
September 26, 2008 at 12:52 pm
I don't know, if you do a little corrective formatting, it doesn't seem to bad to me:
strSQL = "SELECT " _
& " CASE WHEN d2.Ident_Value IS NULL OR d2.Ident_Value = '' " _
& " THEN (h2.ident_value + ISNULL('*' + h.ident_value, '') " _
& " + ISNULL('*' + h3.ident_value, '')) " _
& " ELSE (ISNULL(d2.Ident_Value, '') + ISNULL('*' + d.Ident_Value, '') " _
& " + ISNULL('*' + d1.Ident_Value, '') + ISNULL('*' + d3.Ident_Value,'')) " _
& " END AS insured_name, " _
& " (ISNULL(c.Street_1,'') + ISNULL(' ' + c.street_2, '')) AS insured_address," _
& " c.City,c.State_Province, c.Postal_Code, " _
& " CASE WHEN b.Ident_Type_ID = 1046 THEN " _
& " CASE " _
& " WHEN CONVERT(CHAR(8), CAST(e.Ident_Value AS DATETIME), 112 ) IS NULL " _
& " THEN CONVERT(CHAR(8), CAST(e1.Ident_Value AS DATETIME), 112) " _
& " ELSE CONVERT(CHAR(8), CAST(e1.Ident_Value AS DATETIME), 112) " _
& " End " _
& " ELSE '' " _
& " END AS insured_dob, " _
& " CASE WHEN b.Ident_Type_ID = 1046 " _
& " THEN CASE " _
& " WHEN f.Ident_Value IS NULL OR f.ident_value = '' " _
& " THEN h8.Ident_Value " _
& " ELSE f.Ident_Value "
There's not much else that you can do in VB up through 2005, except some formatting by hand. IMHO, however, this kind of thing should be in stored procedures anyway, unless it is a DBA utility.
In C# you can embed the line breaks in a quoted string, which can help quite a bit (I wish that VB had this feature).
And, in VS2008 (both VB & C#) you could use Linq to SQL, which adds some native query syntax capability to the languages.
But still, a stored procedure is preferred for many other reasons and that would easily solve your code formatting problems.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
September 28, 2008 at 3:30 pm
I agree completely. A stored procedure is much easier to maintain for what you are doing.
Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
My Blog | Twitter | MVP Profile
Training | Consulting | Become a SQLskills Insider
Troubleshooting SQL Server: A Guide for Accidental DBAs[/url]
September 29, 2008 at 8:35 am
I agree with the previous suggestions regarding the use of stored procedures, and I have also found the following utility to be very useful when stored procedures are not used. You can store your original code in .sql for example, and just copy it from SSMS into the wrapper utility to quickly format it for your VB/VBA application.
http://www.artisolve.com/cgi-bin/SQL_Wrap_for_VB_Explanation.html
September 30, 2008 at 1:19 pm
I had a feeling that was going to be the answer. I wasn't looking forward to telling my team that they would have to transfer all of their sql code back to stored procedures. But we've done it now and it works well.
The part that clinched it for me was when I started thinking about injection. I know it's usually considered a web-style attack but it could just as easily happen if a nasty string gets inserted from somewhere else and then one of our functions picks it up and concatenates it into a sql string. It's a real possibility for us and I couldn't justify letting it be there. I just kept thinking "Little Bobby Tables"[/url].
Additionally, this is going to end up being a rather extensive project. The performance benefit of stored procedures starts to look tempting when you start thinking about running the same query 5000 - 10000 times for a single job every night.
So, short version - Thanks for setting me straight.
Kent
September 30, 2008 at 1:27 pm
kent.kester (9/30/2008)
The performance benefit of stored procedures starts to look tempting when you start thinking about running the same query 5000 - 10000 times for a single job every night.
As long as you use parameterized commands against SQL Server, they will perform just as good as a stored procedure, and will have plan caching that will be reused. They also protect against SQL Injection. My preference as a DBA is that all TSQL be in the database layer for a number of reasons. First I control that environment, so if a new procedure gets introduced in new development that drags the server down, I can find it and fix the problem much faster than I can if it is embedded in some C# class in the application, and I can do so without having to recompile any component of the application. It also allows for a certain level of abstraction of the database structure from the application itself. If I decide that I need to split a table into 3 tables for normalization purposes, and the application uses stored procedures, then I can edit the stored procedures, to use my newer normalized structures, and as long as I honor the inputs and output format, the app doesn't know or care. I think that for any enterprise level application, stored procedures are the only way to go.
Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
My Blog | Twitter | MVP Profile
Training | Consulting | Become a SQLskills Insider
Troubleshooting SQL Server: A Guide for Accidental DBAs[/url]
September 30, 2008 at 1:41 pm
kent.kester (9/30/2008)
The part that clinched it for me was when I started thinking about injection. I know it's usually considered a web-style attack but it could just as easily happen if a nasty string gets inserted from somewhere else and then one of our functions picks it up and concatenates it into a sql string. It's a real possibility for us and I couldn't justify letting it be there. I just kept thinking "Little Bobby Tables"[/url].
Excellent point Kent. I don't know how many times I have had to explain to developers that "Dynamic SQL" does in fact include SQL commands composed in the client code and executed as a string.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
September 30, 2008 at 2:22 pm
Jonathan Kehayias (9/30/2008)
kent.kester (9/30/2008)
The performance benefit of stored procedures starts to look tempting when you start thinking about running the same query 5000 - 10000 times for a single job every night.As long as you use parameterized commands against SQL Server
Unfortunately, my devs are very junior coders. They write some decent SQL but they haven't been brought up around actual VB/C++/C# code. As a result I created some utility functions for them that they use to execute their code. This limited what they could do but sped up their dev time. So they were writing dynamic SQL rather than parameterized commands.
To be fair, I haven't done much beyond TSQL in over 4 years and my last experience with VB was VB6. That has been enough to get me going on VB.net/CLR, though.
The new way we're doing this, they pass the name of the stored procedure and one integer value to my helper function. The helper function deals with database connections, creating the parameter, and executing the command. It then returns a datareader object to them. The end result is they don't have to think in terms of VB very much. They just have to know what stored proc to run and remember to close their datareader.
This really has cleaned up the code. And I do like the idea of having the data access abstracted from the functional code to allow for changes if I need to make them. I hadn't thought of it in those terms.
Kent
September 30, 2008 at 3:51 pm
I'm curious what functions you need to implement that cause you to have a bunch of junior coders writing in CLR. Datareaders force row-by-row operations, if they can be written in set-based T-SQL they probably should not be CLR.
October 1, 2008 at 6:42 am
It probably could be written in TSQL. The majority of it anyway. There are some pieces that really aren't good for TSQL though. There's some FTP functionality, some VPN functionality, and some lie system work that has to be done. Yes, I could write those pieces as stand alone assemblies but I also wanted a chance to see what the data capabilities of CLR were. I'll admit I maybe chose the wrong project to learn from. It is rather large. So far, however, performance has been better than I expected it would be.
October 12, 2008 at 5:36 am
Thanks. This was very helpful.
October 13, 2008 at 6:09 am
Turns out that I chose well. There's a lot of logic in what we're doing that would have been very difficult to write in TSQL.
Also, this is a re-visit of a previous app that I wrote several years ago in VB6. Oddly, I'm using the same hardware so the only difference is the software. I'm getting a nearly 500X improvement over the VB6 app by using VB.net CLR. I queued up a list of 2500 items to process and fired it off. The VB6 app processed 5 per second so I pushed away from my desk to go get a drink while I waited. By the time I got out of my chair it was done. Insane performance increase. Needless to say, I am very happy.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply