[C# 2.0] SqlCommand resultaat is vaag

[_Thanatos_]

Ik heb een SqlCommand met een tweetal parameters, die een stored procedure aanroept. De code ziet er ongeveer zo uit:

C#:

SqlCommand cmd = new SqlCommand("get_articles");
cmd.Parameters.AddWithValue("@StartDate", master.StartDate);
cmd.Parameters.AddWithValue("@EndDate", master.EndDate);

Als ik dan met de SQL Profiler ga kijken, kom ik tot de grote verbazing dat er een draak van een SQL-statement naar de server wordt geslingerd:

SQL:

exec sp_executesql N'get_articles', N'@StartDate datetime,@EndDate datetime', @StartDate = 'Sep  7 2005 11:57:16:540PM', @EndDate = 'Sep  7 2005 11:57:16:540PM'

Dus ten eerste geeft dit een syntaxfout (logisch, want het klopt gewoon niet) en ten tweede is get_articles al een stored procedure, dus waarom in godsnaam nog es in een sp_executesql gieten

Is er een manier om dit vreemde gedrag goed te krijgen?

[ Voor 33% gewijzigd door _Thanatos_ op 07-09-2005 23:57 ]

[pjvandesande]

Heb je de SqlCommand.CommandType al geset?

[ Voor 7% gewijzigd door pjvandesande op 08-09-2005 07:26 ]

[whoami]

Check eens in de MSDN hoe je een SP moet uitvoeren.
zoals questa al zegt moet je de CommandType op CommandType.StoredProcedure zetten.

[pjvandesande]

whoami schreef op donderdag 08 september 2005 @ 08:43:
Check eens in de MSDN hoe je een SP moet uitvoeren.
zoals questa al zegt moet je de CommandType op CommandType.StoredProcedure zetten.

MSDN is voor .NET 2.0 nog niet echt je van het, maar hier is iig de link:
SqlCommand Members (System.Data.SqlClient)

[_Thanatos_]

Aha, CommandType.StoredProcedure dus... maar dan wekt weer een andere vraag op. Standaard staat ie dus op Text, maar hij maakt er toch een sp_executesql van. Waarom bij een normale (niet SP) query niet gewoon de parameters invullen en zo naar de server sturen? nu moet de server 2 commando's parsen (de sp_executesql én whatever daarin staat)...

[ Voor 6% gewijzigd door _Thanatos_ op 09-09-2005 22:11 ]

[Verwijderd]

_Thanatos_ schreef op vrijdag 09 september 2005 @ 22:10:
Aha, CommandType.StoredProcedure dus... maar dan wekt weer een andere vraag op. Standaard staat ie dus op Text, maar hij maakt er toch een sp_executesql van. Waarom bij een normale (niet SP) query niet gewoon de parameters invullen en zo naar de server sturen? nu moet de server 2 commando's parsen (de sp_executesql én whatever daarin staat)...

uh,
parameterized queries

1. eerst de query over de lijn sturen.
2. eventueel query compileren
3. daarna de parameters over de lijn sturen
4. query en parameters koppelen en uitvoeren


Volgens mij moet je ook met CommandType.Text een stored procedure aan kunnen roepen.

[ Voor 13% gewijzigd door Verwijderd op 10-09-2005 12:42 . Reden: url ]

[_Thanatos_]

Klinkt leuk, maar het is relatief zeer traag...

Je stopt dit in een SqlCommand:

SQL:

SELECT * FROM tabelletje WHERE param1=@param1 AND param2=@param2

je vult vervolgens die parameters in de SqlCommand in en wat wordt er naar de server gestuurd:

SQL:

EXEC sp_executesql
   N'SELECT * FROM tabelletje WHERE param1=@param1 AND param2=@param2',
   N'@param1 nvarchar(4000), @param2 nvarchar(4000)',
   @param1='aap',
   @param2='koe'

Niet echt motiverend om parameterized queries te gebruiken

En wat is hier dan mis mee:

SQL:

SELECT * FROM tabelletje WHERE param1='aap' AND param2='koe'

[ Voor 15% gewijzigd door _Thanatos_ op 11-09-2005 19:20 ]

[pjvandesande]

* pjvandesande wijst naar url

[Verwijderd]

Er zijn ook veel discussies te vinden op het internet van de voors en tegens van parameterized queries vs. stored procedures, met aanhangers voor beide kanten. EfBe heeft daar in het verleden op GoT ook wel zinvolle dingen over gezegd, kan ik me vaag herinneren ....

[pjvandesande]

Verwijderd schreef op maandag 12 september 2005 @ 02:42:
EfBe heeft daar in het verleden op GoT ook wel zinvolle dingen over gezegd, kan ik me vaag herinneren ....

Frans Bouma (EfBe) heeft in iedergeval op zijn blog een mooie post gemaakt hierover.

Verder is er echt hele hoop te vinden hierover, een intressante discussie overrigens want er lijken evenveel fanatieke tegenstanders als voorstanders te zijn.
Het is denk ik ook een kwestie van een persoonlijke voorkeur.

[gorgi_19]

questa schreef op maandag 12 september 2005 @ 07:44:
[...]


Frans Bouma (EfBe) heeft in iedergeval op zijn blog een mooie post gemaakt hierover.

Vooral de discussie met Rob Howard maakt het leuk * klik *

[gorgi_19]

_Thanatos_ schreef op zondag 11 september 2005 @ 19:19:
Klinkt leuk, maar het is relatief zeer traag...

Je stopt dit in een SqlCommand:

SQL:

SELECT * FROM tabelletje WHERE param1=@param1 AND param2=@param2

je vult vervolgens die parameters in de SqlCommand in en wat wordt er naar de server gestuurd:

SQL:

EXEC sp_executesql
   N'SELECT * FROM tabelletje WHERE param1=@param1 AND param2=@param2',
   N'@param1 nvarchar(4000), @param2 nvarchar(4000)',
   @param1='aap',
   @param2='koe'

Niet echt motiverend om parameterized queries te gebruiken

En wat is hier dan mis mee:

SQL:

SELECT * FROM tabelletje WHERE param1='aap' AND param2='koe'

In het eerste geval kan hij het execution plan cachen voor meerdere situaties; het tweede geval moet SQL Server hem toch omzetten naar een parametrized query om het execution plan te kunnen cachen.

quote: http://weblogs.asp.net/fbouma/archive/2003/11/18/38178.aspx

Isn't that a little wrong, Rob? There is no precompilation of stored procedure code in SqlServer. It caches execution plans for each query, also ad-hoc queries. Even better: it will parametrize queries which don't even have parameters to keep the execution plan in the cache!

[_Thanatos_]

Met andere woorden, het is sneller op de "korte" manier, zoals ik het verwacht. M.I. is het gewoon de luiheid ofzo vande programmeurs van SqlCommand dat de parameters zo dom over de lijn gestuurd worden. Ik begrijp écht niet waarom de parameters gewoon invullen zo slecht is.

Ja, als er nou een quote in de parameter zit? Nou dan kan toch escaped worden? Met parameterized queries kan een quote in een parameter ook voorkomen dus die moet óók escaped worden. Daar dus alvast geen verschil.

Dan nog het verschil in performance. SQL server gaat vgs mij niet het execution plan van de query binnenin een sp_executesql cachen, maar de sp_executesql call zelf wordt wel gecached. Nou lekker boeiend als die elke keer hetzelfde is met een ander stringetje.

Echt, totdat iemand me kan vertellen waarom parameterized queries op de .NET manier beter zijn t.o.v. parameterized queries op de normale (verwachte) manier, zal ik het alleen gebruiken voor stored procedures. Voor ad-hoc queries zie ik het (excuseer taalgebruik) als complete crap.

[Verwijderd]

_Thanatos_ schreef op maandag 12 september 2005 @ 19:19:
Met andere woorden, het is sneller op de "korte" manier, zoals ik het verwacht. M.I. is het gewoon de luiheid ofzo vande programmeurs van SqlCommand dat de parameters zo dom over de lijn gestuurd worden. Ik begrijp écht niet waarom de parameters gewoon invullen zo slecht is.

Is op zich ook niet slecht. Het uitvoeren van een eenmalige statische query is zelfs zelfs sneller.
Er is wel nagedacht over SqlCommand. Je vergeet de SqlCommand.Prepare en dat is waar zich het voordeel in bevindt.

"If your application runs the same set of SQL queries multiple times, preparing those queries by using the Command.Prepare method may give you better performance. In ADO.NET, the SqlCommand.Prepare method calls the sp_prepare stored procedure for SQL Server 7. The SqlCommand.Prepare method calls sp_prepexec for SQL Server 2000 and later. SqlCommand.Prepare makes these calls instead of running a regular batch remote procedure call (RPC)."
"Using the Prepare method does not yield a benefit if you are only going to run the statement one or two times. The next version of SQL Server will better leverage how plans are cached, so using it would not make a difference. You should only use the Prepare method for those statements that you run multiple times."
Bron: http://msdn.microsoft.com.../html/ScaleNetChapt12.asp

_Thanatos_ schreef op maandag 12 september 2005 @ 19:19:
Ja, als er nou een quote in de parameter zit? Nou dan kan toch escaped worden? Met parameterized queries kan een quote in een parameter ook voorkomen dus die moet óók escaped worden. Daar dus alvast geen verschil.

Dat klopt dus niet. Je hoeft helemaal niets te escapen in een parameter.

_Thanatos_ schreef op maandag 12 september 2005 @ 19:19:
Dan nog het verschil in performance. SQL server gaat vgs mij niet het execution plan van de query binnenin een sp_executesql cachen, maar de sp_executesql call zelf wordt wel gecached. Nou lekker boeiend als die elke keer hetzelfde is met een ander stringetje.

De execution plan wordt wel degelijk gecached. Idem voor parameterized queries.

"SQL optimizations. Some databases provide optimizations to stored procedures that do not apply to dynamic SQL. For example, Microsoft SQL Server™ versions prior to SQL Server 2000 kept a cached execution plan for stored procedures. The cached execution plan for stored procedures reduced the need to compile each stored procedure request. SQL Server 2000 is optimized to cache query plans for both stored procedure and for dynamic SQL query plans."
Bron: http://msdn.microsoft.com.../html/ScaleNetChapt12.asp

_Thanatos_ schreef op maandag 12 september 2005 @ 19:19:
Echt, totdat iemand me kan vertellen waarom parameterized queries op de .NET manier beter zijn t.o.v. parameterized queries op de normale (verwachte) manier, zal ik het alleen gebruiken voor stored procedures. Voor ad-hoc queries zie ik het (excuseer taalgebruik) als complete crap.

"SQL Injection. Avoid using dynamically generated SQL with user input. SQL injection occurs when input from a malicious user is used to perform unauthorized actions, such as retrieving too much data or destructively modifying data. Parameterized stored procedures and parameterized SQL statements can help reduce the likelihood of SQL injection. Parameter collections force parameters to be treated as literal values so that the parameters are not treated as executable code. You should also constrain all user input to reduce the likelihood that a malicious user could use SQL injection to perform unauthorized actions."
Bron: http://msdn.microsoft.com.../html/ScaleNetChapt12.asp


Daarnaast kan een dynamische c.q. parameterized query programmeersrompslomp besparen
Als je een statische query samenstelt zit je met conversie van datatypes naar tekst. Dit zou wellicht problemen op kunnen leveren.
Datum -> string
Integer -> string
Double -> string
Boolean -> string
Lange tekst (Memo) -> string
quotes in string.
binaire data
etc.