Pros and Cons of using SqlCommand Prepare in C#?

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()) {

                }
            }
    }
}