further discussions about parameterized queries (PQ)

[netnewb]

There are times when parameter queries won't work. They are not very flexible.

Let's say you make a custom query dialog, for flexible reporting. User enters one/more parameters: one/more customer names, a dollar amount, less than or greater than (e.g. order amounts <$100, or >$100), and one of several possible sort orders. You can build that complex query easily and briefly by building up a SQL string, but unless I'm mistaken, parameters won't do it. (Well, I suppose you could have many different parameterized queries, depending on which parameters the user chooses to supply, but that grows huge fast with permutations)

Also, the example you give is ugly code; bad primarily for being a classic jumble. The embedded functions ought to be separated out, assigned to temporary variables; this would ease both reading and debugging. It seems to me that although parameters are more technically correct, and will avoid a few types of problems, they're not nearly as elegant (quick and tidy and self-contained) as building the sql string. It doesn't take much to build the sql string correctly and safely, anyway.

 

[JohnH]

Parameters are the the input values (and types) of a query and only that. What you are talking about is dynamically build a query, which may be valid in some cases. But never (never ever!) mix the value in that build, add it as a parameter.

[SIZE=2][COLOR=#008000]'default command[/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] cmd [/SIZE][SIZE=2][COLOR=#0000ff]As [/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] SqlCommand([/SIZE][SIZE=2][COLOR=#a31515]"SELECT * FROM something"[/COLOR][/SIZE][SIZE=2])[/SIZE]
[SIZE=2][COLOR=#008000]'add condition[/COLOR][/SIZE]
[SIZE=2]cmd.CommandText &= [/SIZE][SIZE=2][COLOR=#a31515]" WHERE part = @part"[/COLOR][/SIZE]
[SIZE=2][COLOR=#008000]'add parameter[/COLOR][/SIZE]
[SIZE=2]cmd.Parameters.Add([/SIZE][SIZE=2][COLOR=#a31515]"@part"[/COLOR][/SIZE][SIZE=2], SqlDbType.NText)[/SIZE]
[SIZE=2][COLOR=#008000]'set parameter value[/COLOR][/SIZE]
[SIZE=2]cmd.Parameters([/SIZE][SIZE=2][COLOR=#a31515]"@part"[/COLOR][/SIZE][SIZE=2]).Value = SomeTextBox.Text[/SIZE]

 

[netnewb]

John,
"Never (never ever!)" are very strong words. You give no reason or explanation. Passed parameters are a feature in ADO, which talk to a feature in (generic) SQL server. Just because the features exist doesn't make an imperative to use them. As I see it, SQL is an ancient language which was updated a couple decades ago to include parameter-passing. But again, just because the feature is there doesn't give it any advantage over more traditional (and less verbose, less cryptic) ways of using parameters.

I could be wrong, but I don't know of any compelling reason to avoid old-fashioned string building.

I think that IF the failure to use formal parameter passing caused:

• Big performance hits
• Unpredictable bugs
• Frequent incompatibility
• Sub-standard results

...then it would be good to avoid the simpler way, at least in some cases. But it appears to me that this edict of style generates from a devotion to Microsoft's database technology of the week, not from any compelling reason.

For example, in Access (JET) it was advisable to use formal parameters, because there was a performance hit with freeform queries. But if it's a rare routine that does it, or a small database, it doesn't matter. I think the base imperative is do whatever is simpler and more brief (if it won't cause problems).

I'm not saying your way is bad. It's probably widely preferred for enterprise projects. But I don't see any reason why it's an issue for "never", much less "never (never ever!)".

PS: If you get a chance, check out Cache.
See the demo videos. Here's a technology that makes a joke of SQL. I think it's pretty important to remember that the whole massive architecture of ADO is built around ancient and outdated technology; important to keep perspective on ideas of "correct".

 

[cjard]

There are times when parameter queries won't work. They are not very flexible.

Let's say you make a custom query dialog, for flexible reporting. User enters one/more parameters: one/more customer names, a dollar amount, less than or greater than (e.g. order amounts <$100, or >$100), and one of several possible sort orders.

There is still no excuse not to use a paramaterized query. Build the parameterised query dynamically if you really cannot think of anotehr way. Most searches can be made simple:

SELECT * FROM orders WHERE
(customerName = CustomerName OR CustomerName IS NULL) AND
(orderAmount < MinOrderAmount OR MinOrderAmount IS NULL) AND
(orderAmount > MaxOrderAmount OR MaxOrderAmount IS NULL)

Sorting is done in the client grid. To "wildcard" a parameter in this query, set it to null


If you have some good reason why this isnt possible, there is nothing stopping you using string concatenation to build up a parameterised query, and I do this in some of my apps. I have the user type in one or more fields on a form and their edits are put in a datatable. I then scan through the datarow, looking at the type of the column and the value the user typed:

Dim oc as New OracleCommand("SELECT * FROM orders WHERE 1=1 ");
For Each[I] [column in the datatable][/I]
  [I][parameter name] [/I]= ":p" + [I][column name][/I]
  If [I][column type is string][/I] Then
    oc.CommandText += "[I][column name] = [parameter name][/I]"
    oc.Parameters.Add([I][parameter name][/I], VARCHAR type, [I][column value][/I])
  Else If [column type is date] Then
    oc.CommandText += "[I][column name] = [parameter name][/I]"
    oc.Parameters.Add([I][parameter name][/I], DATETIME type, [I][column value][/I])

...

THere is nothing stopping you offering huge amounts of flexibility to the user, but the crucial difference youre MISSING here is that by not using parameters youre shoeing everything through a string represenation and back. Precision loss, time zones, date formats.. Everything I talk about as to why putting a SQL together with straight concat is retarded and parameters are great STILL HOLDS

Also, the example you give is ugly code; bad primarily for being a classic jumble. The embedded functions ought to be separated out, assigned to temporary variables;

tempVars in an SQL? We arent writing stored procedures here, we are running an SQL..

they're not nearly as elegant (quick and tidy and self-contained) as building the sql string.

Nonsense. Show me some of your vb code.. Go on.. I want to see that all your programs are written as one huge function and never use anything other than string variables and when you want to do something like add 2 numbers, you convert from string to number, add, and store the result back in a string.

Do you do this? Nope.. So please.. dont even try to tell me that storing everything in a string is simple, elegant, quick, tidy and selfcontained..

It doesn't take much to build the sql string correctly and safely, anyway.

Again, rubbish. If string was perfect for representing everything, why do we even have other datatypes?

I appreciate your arguemnt, I just dont think you appreciate parameters..

 

[cjard]

Just because the features exist doesn't make an imperative to use them.

Go on.. show me that app of yours that uses nothing but strings.. "Just because DateTime exists, doesnt mean I have to use it" - true, but try convincing anyone here that you know what youre talking about in the programming arena if you constantly make such a short-sighted claim!

As I see it, SQL is an ancient language which was updated a couple decades ago to include parameter-passing.

Er.. paramerterisation of SQL has been around as a notion, nearly as long as programming languages with typed variables have existed. Taking your stated timescale of "a couple of decades" - if you turned up to a job interview and said "Oh.. I havent updated my computer knowledge in more than 20 years; i still write SQLs like they did in the early '80s" do you think you'd get the job? Come on.. Quit with the fallacious argument, or seek out that one company out there whose payroll system is still programmed by conencting wires on a breadboard and go work for them..

But again, just because the feature is there doesn't give it any advantage over more traditional (and less verbose, less cryptic)

I've given pretty much every advantage possible. I cant state the disadvantages because there arent any.

ways of using parameters.

You arent using parameters! Your " '80s way" is representing everything as a string and then having the database either:
a) convert your value from string to native datatype and do the relevant comparison
b) convert every value in the table from native type to string, then do a string comparison

Do you even know what it's doing?


Another way to present this as daft, is to tell me, what date is this:
"030201"

Cmon.. what is it? 1st Feb 2003? 1st Feb 1903? 2nd Mar 2001? 2nd Mar 1901?

Here's an even more stupid one:
"191211"
11th Dec 1919? 11th Dec 2019? If the format was "yyyyMd" that could even be the 1st Jan 1912.

Representing a Date as a String is silly.. it leads to your "unpredictable bugs" list entry. Parsing it leads to your performance hit. Converting all dates in a million row table to strings really leads to a performance hit. Take your app to america where the users date format is different leads to your incompatibility.

I could be wrong,

Indeed.. try and think of a single advantage that extends beyond programmer laziness, for building SQLs out of concatted strings, and I'll shoot it down..

I don't know of any compelling reason to avoid old-fashioned string building.

It's crap, for every reason I have listed in the original post, and the subsequent ones.. Hopefully the debate is proving highly educational to all who read this topic though

it appears to me that this edict of style generates from a devotion to Microsoft's database technology of the week, not from any compelling reason.

Parameterised queries have probably been around longer than Microsoft have had a database engine ; there is no slaving to microsoft here. Every vendor of every programming language makes the facility available becaise it is universally recognised and a good, proper way of getting data between systems. Essentially here youre performing Interprocess Communication, and you do not want to do that by relying on one systems parsing of another system's strings. We wont even talk about string encoding at the moment.

if it's a rare routine that does it, or a small database, it doesn't matter.

Thinking like that never raises the bar of quality; this is a personal pride issue for some..

do whatever is simpler and more brief

..or a laziness issue for others

I'm not saying your way is bad.

Thanks.. if you did, you wouldnt succeed

I don't see any reason why it's an issue for "never", much less "never (never ever!)".

Umm.. well, youre free to do as you choose. When youre writig your next app all in one class, in the Sub Main, using only strings, I'll for sure not have any problem with you concatting your SQLs together in any old way that is easiest for you.. but dont ask me to hire you to direct my programming team

PS: If you get a chance, check out Cache.

I'm aware that they are having a go at solving the O-R Impedance Mismatch but I dont feel it is particular relevant to this discussion, which is aimed at enabling better using of SQL within systems that are backed by an RDBMS

important to keep perspective on ideas of "correct".

Important to keep perspective on the notions of scope and context w.r.t forum threads

 

[netnewb]

"Just because DateTime exists, doesnt mean I have to use it"

That doesn't follow at all from what I said.

if you turned up to a job interview and said "Oh.. I havent updated my computer knowledge in more than 20 years; i still write SQLs like they did in the early '80s" do you think you'd get the job?

Total non-sequitar. Doesn't even make sense, much less answer my argument. Rants are no substitute for reason.

I've given pretty much every advantage possible.

Yep, and it doesn't amount to much in most cases. Certainly not enough to evoke an edict (much less ranting).

what date is this: "030201"

Indeterminate. You need to use YYYYMMDD format. Or a parameter would be fine also (as I said before). I'm not a coding bigot, I'm just pointing out that simpler is often just as good, if not better.

try and think of a single advantage that extends beyond programmer laziness, for building SQLs out of concatted strings, and I'll shoot it down..

There's a big difference between laziness and keeping things simple. If you like typing half a kilobyte to code every little query, for no particular reason, go ahead. There are all kinds of things one can spend time doing/typing/nit-picking. I prefer to get things accomplished rather than inventing edicts for unnecessarily doing things the tedious way.


I don't envy you for putting your pride in intense conformity to excessive structure.

I'm not interested in fighting with you. I said what I had to say, I stand by it, and perhaps it will be educational for others, as you said.

 

[cjard]

That doesn't follow at all from what I said.

Sure it does; youre asserting that String suits every need and specialist data types are unnecessary.. You also didnt actually respond to the point

Total non-sequitar. Doesn't even make sense, much less answer my argument. Rants are no substitute for reason.

Again, youre offering a boilerplate "um. no" without qualifying your reasoning - if youre going to present an opposing opinion, you need to back it up with something

Yep, and it doesn't amount to much in most cases. Certainly not enough to evoke an edict (much less ranting).

Your chosen name gives away a little; perhaps concepts like type safety and code re-use arent something you considered in whatever prior programming experience you had before .net - i'm amazed that you'd gloss over performance benefits though; it's normally newer programmers accustomed to lots of computing resource that dont bother to code in optimal ways

Indeterminate. You need to use YYYYMMDD format.

Who says? Now youre laying down rules just like I'm saying "you need to use a DateTime parameter if youre dealing with dates/times. By mandating a specific string format to represent a date time, youre effectively enforcing a similar standardisation as using a type specific parameter, but youre incurring the conversion overhead. Youre also losing precision and location awareness. In some contexts this might not be important, but then you'll find yourself adding other rules and other formats to cope with situations where milliseconds or timezones are needed? Youre starting to clutter your code with an excess of representations when one sensible one exists, and your argument becomes increasingly non sensical.

Or a parameter would be fine also (as I said before). I'm not a coding bigot, I'm just pointing out that simpler is often just as good, if not better.

Make everything as simple as possible, but no simpler. What's simpler:
Converting between strings and datetimes
Leaving datetimes as they are

?

There's a big difference between laziness and keeping things simple. If you like typing half a kilobyte to code every little query, for no particular reason, go ahead.

I dont actually type most of the code; because parameterisation is regular, type specific and sensible, it is something that a computer can do very well. I write the statement, it picks out the parameters, typecasts them, sets them up and we're done. You unwittingly use a computer to write code everytime you drop a button on a form.. That code runs into hundreds of kilobytes, but you dont see it or realise it because it's hidden away, it's regular, strict and it works. It also outperforms (in the case of SQL statements) inline SQLs.

Addiitonally, though the concept may be new to you, it's is well encapsulated too. In your strive to "mkae things simpler" you might actually be making things more complex because youre filling your button handler code full of SQL statements - statements that may look pretty in your eyes, but they are in the wrong place, unsafe, slow and inefficient.

I prefer to get things accomplished rather than inventing edicts for unnecessarily doing things the tedious way.

Do you drink instant coffee? Do you wash your clothes by hand? Do you find an ipod easier to carry than a symphony orchestra? Your life is filled with instances of machines and processes making it easier and simpler, and you have a choice not to employ those machines or processes and instead do it the long, hard way. Do you? Are you even aware that Visual Studio can write nearly all the code surrounding an SQL; all you have to do is write the SQL? Do you know that in cases where true dynamics are needed, that you can write code that will write the SQL?

Stick around the forums and read a little - you might come around to liking this parameterisation lark

I'm not interested in fighting with you. I said what I had to say, I stand by it

You used the word "rant" before; just because I can think of more thinks to say and I put more words into the counter argument [in order to better educate other readers, including yourself] doesnt make it a rant, nor a fight. Standing by what you said is admirable, but if you dont actually back it up with some sensible logical argument, and spend your time nitpicking over my analogys and context descriptors then that stand wont actually count for much in the eyes of someone reading. I'm genuinely thankful for your input though, as may others be because it's evoked a wider discussion around the topic than I would ever have been bothered to write initially

 

[Stonkie]

I do agree that parametrized queries are the best way... Most of the time... But I don't think programming has any invariable truth that no one must stray from. Here are a few examples I can think of where I would consider the alternative.

You want to use the filter from a DataView as the WHERE clause for your query. If you do control the filter on the Dataview, you can insure it always has a syntax that won't break the SQL and use it when it is simpler to do so.

You need to parse a single WHERE clause out of a whole form's worth of filters, some empty, some not.

You want to use a JOIN on a few other tables only when the additional data is actually used by a filter (I can only guess there might be a notable performance gain from this, but performance issues are difficult to guess right).

Obviously, this is bad :

Dim oc as New OracleCommand( _
  "SELECT a || '" & TextBox1.Text & "' as Thing FROM table1 " & _
  "WHERE col1 = '" & TextBox2.Text.Replace("'"c, "''") & "' OR c" & _
  "ol3 LIKE '" & TextBox3.Text & "%' AND col4date = #" & _
  dateTimePicker1.Value.ToString("MM/dd/yyyy HH:mm:ss") & "#" & _
  " AND someListCol5 NOT IN('" & TextBox6.Text.Split(","c)(0) & "'," & _
  "'" & s & "','" & CInt(iText).ToString("000") &"')")

but this looks fine :

dim conditionBuilder as new StringBuilder()

if (txtFilterA.Text.Length > 0) then
    AddCondition(conditionBuilder, "a = '" + txtFilterA.Text.replace("'", "''") + "'")
endif

if (txtFilterB.Text.Length > 0) then
    AddCondition(conditionBuilder, "b = '" + txtFilterB.Text.replace("'", "''") + "'")
endif

' Lots of conditions

return "SELECT a FROM SomeTable " + conditionBuilder.ToString()

The AddCondition method would be something like this :

private sub AddCondition(conditionBuilder as StringBuilder, newCondition as String)
    if (conditionBuilder.Length > 0) then
        conditionBuilder.Append(" AND (")
    else
        conditionBuilder.Append("WHERE (")
    endif

    conditionBuilder.Append(newCondition)
    conditionBuilder.Appent(")");

end sub

I would actually think about creating a ConditionBuilder class to hide all this or maybe just SqlBuilder that would create the whole SQL query including the columns to return and the table for the FROM clause. It would be even simpler than to pass nulls when a criteria is not needed and could be used to generate the Filter value of a DataView independently from any database command.

Don't think I actually recommend anyone to start concatenating Strings for fun, but the parametrized query might force you to build a gigantic SQL Query that can accommodate the various null values for non existent filters out of the 20 filters you may have. That means a single String that's defined over 10 - 20 lines. Did you forget to put a space on the line break? Lots of things can go wrong. Less that the String concatenation, obviously, but still.

On the other hand, this method allows you to set the conditions you need in bits of 30 - 40 characters each. You can step through each line and see when the condition went wrong which makes it easier to debug than the parametrized query.

I admit it lacks typed parameters which would avoid your having to think about the " ' " character around and within strings and I have yet to work with dates that way. I haven't had the time to look at how the current parametrized queries do that yet, maybe you can use a Microsoft provided method somewhere. Otherwise you can just add value formatting static methods in the SqlBuilder class.

 

[cjard]

The AddCondition method would be something like this :

private sub AddCondition(conditionBuilder as StringBuilder, newCondition as String)
    if (conditionBuilder.Length > 0) then
        conditionBuilder.Append(" AND (")
    else
        conditionBuilder.Append("WHERE (")
    endif

    conditionBuilder.Append(newCondition)
    conditionBuilder.Appent(")");

end sub

Mmhhhh, but answer me (i.e. defy) this:

If youre capable of concatenating a string on

strSql &= "colName = '" & txtCol.Text & "'"


Youre certainly capable of concatenating a parameter on:

strSql &= "colName = @colname"
cmd.Parameters.Add("@colName", txtCol.Text


-


I wont wind on about datatypes and the foolishness of trying to represent everything as a string yet again, but that's a major consideration for parameters.

You say there are no invariable truths, and for the most part, I agree, but one thing you should never do, because there is no good reason for it, and no situation that requires it, is use string-value concatenation to build a query SQL. String-parameter concatenation fine, but string-value concat; just dont do it! I'll give you that as an invariable truth. the only time it will actually fail is when something is not parameterisable, and in that case, there are a raft of reasons why we typically dont do that kind of thing (we prepare queries that the db can plan ahead)

(Are you sure you thought your argument through?)

ps; when youre concatting your where clauses on, its far more trick to just to this:

strSql = "SELECT * FROM table WHERE 1=1"

strSql &= " AND col = @col"
..
strSql &= " AND col10 = @col10"

 

[Stonkie]

Mmhhhh, but answer me (i.e. defy) this:

If you're capable of concatenating a string on

strSql &= "colName = '" & txtCol.Text & "'"


Youre certainly capable of concatenating a parameter on:

strSql &= "colName = @colname"
cmd.Parameters.Add("@colName", txtCol.Text)

Of course... But it isn't actually just that. You have to add the OR @colName IS NULL, put parenthesis around this, etc. That gives a pretty complex SQL expression. Even in the event that you just search in ColA from the A to Z columns, you have 26 * 2 conditions and you have to put parenthesis and OR to make a non existent field work correctly.

And you still cannot see the values in the SQL query to debug it. Say you use a combo box to tell which color to look for and the default value "unknown" should be removed from the search because it is a special data (somehow). Now, you forgot to put the code for that says "Unknown" is actually null and you accidentally run the query with the value whatever the other search criteria are. The only info you have is that the search always return zero records.

You run the search with no filter and look at the query in the SqlCommand just before it is run. Would you rather see :

SELECT Name FROM MyTable WHERE (Name = @Name OR @Name IS NULL) AND (Color = @Color OR @Color IS NULL) AND (SomethingElse = @SomethingElse OR @SomethingElse IS NULL) ... ORDER BY Name

Then you will look at the parameters from the SqlCommand and look for one that is not null.

Or isn't easier to look at this :

SELECT Name FROM MyTable WHERE (Color = 1)

If you don't understand what is wrong at that point (when the problem is still too complex), you copy paste this in a SQL editor and run it straight on your database until you pinpoint the problem.

It is true that parametrized query produce clean code to use and create, but you reach a similar point of simplicity by encapsulating the query creation in an object. Add a few static methods to convert the DateTime object or raw String to a String ready for adding to the query and you got something that is nearly as clean as the parametrized query. Without the complexity in debugging and the limitations.

--
One more situation I would think about this is when you want the user to use a CheckedListBox to check the categories he want to search for. Parametrizing a query like this :

SELECT name FROM MyTable WHERE CategoryId IN {@CategoryId1, @CategoryId2, ... @CategoryIdN}

is not really an option if the number of categories is variable (the categories are in a table). It may be possible to write a parametrized query like this :

SELECT name FROM MyTable WHERE CategoryId IN @CategoryIdList

and give it an array or collection in parameter, I must admit I never tried but I doubt it and the SqlCommand class is sealed so there is no way to add this with inheritance. The number of sealed classes and not virtual methods and properties is one of the thing I miss from Java btw...

--

ps; when youre concatting your where clauses on, its far more trick to just to this:

strSql = "SELECT * FROM table WHERE 1=1"

strSql &= " AND col = @col"
..
strSql &= " AND col10 = @col10"

Thanks, I didn't think of that!

 

[cjard]

Of course... But it isn't actually just that. You have to add the OR @colName IS NULL,

Um.. no you dont, you jsut simply dont concatenate the parameter on

I think you may be missing the point i'm making. Youre saying:
"I have 26 options, my user selects 2 of them (suppose item 7 and 13) and concatenate those 2 on as values:
SELECT * FROM table WHERE 1=1
CONCAT: AND col7 = 'hello'
CONCAT: AND col13 = 'world'

Your SQL ends up as SELECT * FROM table WHERE col7 = 'hello' AND col13 = 'world'

I'm saying
"I have 26 options, my user selects 2 of them (suppose item 7 and 13) and concatenate those 2 on as parameters then set the values:
SELECT * FROM table WHERE 1=1
CONCAT: AND col7 = @col7
PARAMETERS.ADD('@col7', 'hello')
CONCAT: AND col13 = @col13
PARAMETERS.ADD('@col13', 'world')

My SQL ends up as: SELECT * FROM table WHERE col7 = @col7 AND col13 = @col13
AND THE PARAMETERS COLLECTION has 2 entries, one for the @col7 and one for the @col13


This is more work on the client side, but it can be done. I'm also pointing out that it can be done in SQL (via the col = @param OR @param IS NULL method), it depends how much work you want to put into doing it on the client side.. Imagine if you made a tableadapter partial clas sthat had a sub that took an array of values and an array of column names, and then iterated the arrays and built a parameterised string with them and ran it.. yep, you can do that.. but theres a faff somewhere along the line with making the array.. All i'm saying is that with the (col = x OR x IS NULL) method, you write the SQL once, properly (because cmon, we are pro developers if we are doing this, not some newbie; we can get this right, and we can tell what the SQL is doing simply by looking at the parameters collection..) and not have to fuss over it..


Upshot, I dont care how you write the SQL, whether concatted or "col = x OR x IS NULL" style, SO LONG AS YOU USE PARAMETERS

You can use parameters in 99% of situations, and the situations you cant use tham, you should question whether you should be doing the data access that way..
Parameters:
Stop weird characters screwing up your SQL string
Stop SQL injection
Allow the DB to better plan the query
Allow reuse without recompilation

All upsides, no downsides. There is nothing about concatting a bunch of values together to form an SQL that has ANY merit, in 99% of situations (and as noted, the 1% are cases like when you want to parameterise what the table name is.. You cant do SELECT * FROM @table_name, and good job too.. because parameterising something like that means your data structure you interact with is not consistent or fixed, and that makes it hard to code. Its a step beyond what is required in 99% of situations. Trust me.

That gives a pretty complex SQL expression. Even in the event that you just search in ColA from the A to Z columns, you have 26 * 2 conditions and you have to put parenthesis and OR to make a non existent field work correctly.

I think we have different definitions of complex. I'll email you the last SQL i wrote (it's 32 kilobytes long) and you can check out something I regard as complex
The database doesnt particularly care about 52 checks; most of them are instantly discountable as true in your cases anyway (cause the user doesnt pick all 26). Remember that there are 52 predicates but only 26 parameters.. that promotes another plus point of parameters. if you have a 4 meg parameter to upload, into 3 different columns, you can either upload it 3 times (your way) by having a 12 meg long SQL or once, using a parameter (4 meg)..

And you still cannot see the values in the SQL query to debug it.

Okay, this might be as a result of using a basic programming language like VB.NET because it doesnt show you lists, or private members of objects. Upgrade to C# and your deugger experience will improve considerably. Point to a collection in C# and you see the members, not a "to access an indexed property you must provide..." tooltip. The values are there for your inspection, you have only to look!

SELECT Name FROM MyTable WHERE (Name = @Name OR @Name IS NULL) AND (Color = @Color OR @Color IS NULL) AND (SomethingElse = @SomethingElse OR @SomethingElse IS NULL) ... ORDER BY Name

I dont know about anyone else, but i'm quite particular about indenting and pretty printing my SQLs because i find nothing more unreadable than the gash mess that most people (and even the .net wizard) makes of an sql all on one line

Then you will look at the parameters from the SqlCommand and look for one that is not null.

Yes, but I wrote the query, so I should know what I'm doing! I've looked at other developer's codes, and run their queries in VS2005's analyzers and they come out just fine, and I can pause and look and check. I also write queries in test, with fixed values, and then parameterize them later when I know they work. Maybe its how we differ in an approach to the problem

SELECT Name FROM MyTable WHERE (Color = 1)

I take your point, but it's really only one that an inexperienced or SQL hating programmer would make. If there's a problem with an SQL as critically simple as SELECT * FROM person WHERE name = 'smith' then your company needs to hire a database nerd

I take your point of running queries direct, yes it saves you some time.. but then, how hard can it be to knock up a class that simply replaces all the @parameters in your sql text with the value (using the parameters collection) so you can TEMPORARILY run it in an analyzer

It is true that parametrized query produce clean code to use and create, but you reach a similar point of simplicity by encapsulating the query creation in an object.

Indeed. It's called the VS2005 table adapter wizard.. and isnt it strange, that Microsoft chose the parameterised route too? Are you telling them they do it the wrong way round too?

Add a few static methods to convert the DateTime object or raw String to a String ready for adding to the query and you got something that is nearly as clean as the parametrized query. Without the complexity in debugging and the limitations.

No, you've got something that is a mess or coercion, and in full form might be megabytes long because of all the values stuffed in there. You seem to be trying your hardest to convince me that when you write a VB program, you Dim everything as a String, and when you need to do some date math, for example, you parse your strings to DateTimes, do the math, and store the result in another string..

Show me some of your VB code. If it contains so much as one Dim x as Integer, Dim y as DateTime, Dim z as (any Type other than String) then youre effectively being a hypocrite, because you use non-string variables throughout your VB, but then advocate that every variable in SQL should be of type string, and conversion should take place when appropriate

SELECT name FROM MyTable WHERE CategoryId IN {@CategoryId1, @CategoryId2, ... @CategoryIdN}

is not really an option if the number of categories is variable (the categories are in a table). It may be possible to write a parametrized query like this :

SELECT name FROM MyTable WHERE CategoryId IN @CategoryIdList

In is something that most pro SQL developers avoid tenuously.. I agree it makes some things very convenient and easy, and it's a syntactic convenience for writing out hundreds of ORs (not that you should use IN on lists longer than you'd write by hand) yourself that most databases cannot parameterize, so we have other tricks to work around those situations.. and I'll readily concede that those situations come up far more often than the 1% (or less) that parameterization for a Quirky Reason is impossible..

and give it an array or collection in parameter, I must admit I never tried but I doubt it and the SqlCommand class is sealed so there is no way to add this with inheritance. The number of sealed classes and not virtual methods and properties is one of the thing I miss from Java btw...

Mmm.. it doesnt work, typically. In Oracle we tend to provide a list of values delimited by something that doesnt occur naturally in any of the values and bust it out using a WITH clause, effectively forming a temporary in-memory table that we can join to. It performs a lot better than IN too, in most of the cases where I use it.

It does tend to make things a bit more tricky, which is why i devolve to stored functions that return cursors when I have to do something like that.. Fortunately, it's exceptionally rare that a user demands something I cannot otherwise assemble simply on the client side

 

[Stonkie]

Upgrade to C# and your deugger experience will improve considerably.

The only VB I've done recently is maintenance on older applications. I've been doing C# for the better part of a year now... It's closer to the Java which I'm (was) used to and I'm the only coder here.

I take your point, but it's really only one that an inexperienced or SQL hating programmer would make. If there's a problem with an SQL as critically simple as SELECT * FROM person WHERE name = 'smith' then your company needs to hire a database nerd

I'm the only coder... Along with a network/database guy who has me write the queries he can't write...

In is something that most pro SQL developers avoid tenuously.. I agree it makes some things very convenient and easy, and it's a syntactic convenience for writing out hundreds of ORs (not that you should use IN on lists longer than you'd write by hand) yourself that most databases cannot parameterize, so we have other tricks to work around those situations.. and I'll readily concede that those situations come up far more often than the 1% (or less) that parameterization for a Quirky Reason is impossible..

I like parametrized queries and I do use them when they work, but what I want is a no trick query creation process. I don't want to work around the limitations of the tool I use, I want the tool to work whenever I need it! Be it that I use parametrized queries when they fit my need, I concat a single String to do the job or I combine both practices, hide the thing in a CommandFactory of some kind and live happily ever after : it has to work.

99% of the queries are actually those you write in the dataset designer. Whenever you do have to instantiate a SqlCommand object in code, it's because parametrization was insufficient and that's the 1% we're talking about.

 

[Invisionsoft]

Some of the best technologies are 20 years old and it shows that SQL really has stood the test of time. I have no problem constructing strings by attaching strings, but I make them much more efficiently than in your first example. I put all my data into an array (or collection) and loop and build. That way, you never see that *HORRIBLE* string you had in your first post.

I'll be honest, too. I never knew SQL had parameter thingies, they look very useful but I really don't feel like changing what I've learnt. I use SQL with PHP, mostly. I have VERY low diskspace so uninstalled SQL Server and all that silly stuff that comes with Visual Studio. My IDE works fine and I still have 200mb for docs and stuff! (FYI, I have a 4GB drive with an nlited install of XP. The drive is a solid state disk in my Asus EEE PC (tiny netbook), resolution less than 800x600). I really don't use SQL with .NET, I know I should, but I saw tutorials and it looks *SO* hard! Seriously. All that dataAccessProviders stuff goes over my head. I'm not a n00b though. I might be 15, but I sell my VB.NET online and make ~$20 a day.


- James

 

[JuggaloBrotha]

Some of the best technologies are 20 years old and it shows that SQL really has stood the test of time. I have no problem constructing strings by attaching strings, but I make them much more efficiently than in your first example. I put all my data into an array (or collection) and loop and build. That way, you never see that *HORRIBLE* string you had in your first post.

I'll be honest, too. I never knew SQL had parameter thingies, they look very useful but I really don't feel like changing what I've learnt. I use SQL with PHP, mostly. I have VERY low diskspace so uninstalled SQL Server and all that silly stuff that comes with Visual Studio. My IDE works fine and I still have 200mb for docs and stuff! (FYI, I have a 4GB drive with an nlited install of XP. The drive is a solid state disk in my Asus EEE PC (tiny netbook), resolution less than 800x600). I really don't use SQL with .NET, I know I should, but I saw tutorials and it looks *SO* hard! Seriously. All that dataAccessProviders stuff goes over my head. I'm not a n00b though. I might be 15, but I sell my VB.NET online and make ~$20 a day.


- James

SQL itself doesn't have parameterized queries, it's the .Net controls that has it and fills in the blanks (replaces your parameters with the actual value(s))

If you want to get into manually sanitizing the user's input that's fine, MS's .Net's parameter's are there to do most of it for you as well as making it easier to follow the code.

One thing you can do is use a loop to build the initial SQL command then pass the results to a DataAccess object and loop again to assign the values to the parameters that the first loop put in.

Also, a 4gb drive for your development stuff and the OS, you need a serious upgrade, those micro mini laptops are horrible dev comps.

 

[Stonkie]

Well, using Typed Dataset in the designer is the best way to be productive. I mean, even with the learning curve, it's the best tool out there to get data to/from your database. We were actually talking about when the designer can't understand your query (because you need something too dynamic for it to figure it out).

It is just an encapsulation of the query creation. I'm really not sure of this, but I think the parameters are sent separately (and transparently) from the query and the database uses it for optimization and index usages...

4GB hard drive? That's my amount of RAM...