Pros and Cons of using SqlCommand Prepare in C#?

by

From the MSDN Documentation:

“Before you call Prepare, specify the
data type of each parameter in the
statement to be prepared. For each
parameter that has a variable length
data type, you must set the Size
property to the maximum size needed.
Prepare returns an error if these
conditions are not met.

If you call an Execute method after
calling Prepare, any parameter value
that is larger than the value
specified by the Size property is
automatically truncated to the
original specified size of the
parameter, and no truncation errors
are returned.

Output parameters (whether prepared or
not) must have a user-specified data
type. If you specify a variable length
data type, you must also specify the
maximum Size.”

Furthermore, “If the CommandType
property is set to TableDirect,
Prepare does nothing. If CommandType
is set to StoredProcedure, the call to
Prepare should succeed, …”

This in general is used to make sure that the end user is not using a SQL Injection technique to add or remove information you do not want them too from the database.

I looked into it and check out this article http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.prepare.aspx. Your issue is you need to define your parameters before you run .Prepare() and then set your parameters after you run .Prepare(). Right now you are doing both before. I would try something like this (Note I didn’t test it so my syntax might be a bit off).

public static decimal pobierzBenchmarkKolejny(string varPortfelID, DateTime data, decimal varBenchmarkPoprzedni, decimal varStopaOdniesienia) {
    const string preparedCommand = @"SELECT [dbo].[ufn_BenchmarkKolejny](@varPortfelID, @data, @varBenchmarkPoprzedni,  @varStopaOdniesienia) AS 'Benchmark'";
    using (var varConnection = Locale.sqlConnectOneTime(Locale.sqlDataConnectionDetailsDZP)) //if (varConnection != null) {
    using (var sqlQuery = new SqlCommand(preparedCommand, varConnection)) {

        sqlQuery.Parameters.Add("@varPortfelID");
        sqlQuery.Parameters.Add("@varStopaOdniesienia");
        sqlQuery.Parameters.Add("@data");
        sqlQuery.Parameters.Add("@varBenchmarkPoprzedni");

        sqlQuery.Prepare();
        sqlQuery.ExecuteNonQuery();//This might need to be ExecuteReader()

        sqlQuery.Parameters[0].Value = varPortfelID;
        sqlQuery.Parameters[1].Value = varStopaOdniesienia;
        sqlQuery.Parameters[2].Value = data;
        sqlQuery.Parameters[3].Value = varBenchmarkPoprzedni;

        using (var sqlQueryResult = sqlQuery.ExecuteReader())
            if (sqlQueryResult != null) {
                while (sqlQueryResult.Read()) {

                }
            }
    }
}

More Related Contents:

  1. SQL Query slow in .NET application but instantaneous in SQL Server Management Studio
  2. Any way to SQLBulkCopy “insert or update if exists”?
  3. Is it better to execute many sql commands with one connection, or reconnect every time?
  4. SQL Server: Dynamic where-clause
  5. Procedure or function xxxxx has too many arguments specified [duplicate]
  6. An unhandled exception of type ‘System.InvalidOperationException’ occurred in System.Data.dll?
  7. \d less efficient than [0-9]
  8. What’s the fastest way to read a text file line-by-line?
  9. Is “else if” faster than “switch() case”? [duplicate]
  10. How can I set an SQL Server connection string?
  11. Performance surprise with “as” and nullable types
  12. C# Sort and OrderBy comparison
  13. Stopwatch vs. using System.DateTime.Now for timing events [duplicate]
  14. Insert entire DataTable into database at once instead of row by row?
  15. LINQ Ring: Any() vs Contains() for Huge Collections
  16. What is more efficient: Dictionary TryGetValue or ContainsKey+Item?
  17. How to execute an SSIS package from .NET?
  18. Passing List to SQL Stored Procedure
  19. How to retrieve data from a SQL Server database in C#?
  20. Bulk Update in C#
  21. EF 6 – How to correctly perform parallel queries
  22. Are protected members/fields really that bad?
  23. How to keep single SQL Server connection instance open for multiple request in C#?
  24. How to use SqlCommand to CREATE DATABASE with parameterized db name?
  25. ExecuteNonQuery returning -1 when using sql COUNT despite the query string
  26. Why is processing a sorted array slower than an unsorted array?
  27. HTTPWebResponse + StreamReader Very Slow
  28. Cyrillic encoding in C#
  29. Generating sql code programmatically
  30. Accessing System Databases/Tables using LINQ to SQL?

Leave a Comment