SQL Parameters and Question Marks

Question

I am making the switch from classic ASP to ASP.NET. And I am having some trouble doing some basic stuff that I used to do easily with the old method. Below is a handy ASP function that I used to execute scalar queries with a few lines.

FUNCTION ExecuteScalarParams(SQLQuery, Parameter_Array)
Set cmd1 = Server.CreateObject("ADODB.Command") 
cmd1.ActiveConnection = con 
cmd1.CommandText = SQLQuery 
cmd1.CommandType = 1 

FOR ParamCount = 0 TO UBOUND(Parameter_Array)
    cmd1.Parameters(ParamCount) = Parameter_Array(ParamCount)
NEXT 'ParamCount

Set rstScalar = cmd1.Execute()

IF NOT rstScalar.EOF THEN
    arrScalar = rstScalar.GetRows()
    IF UBOUND(arrScalar,2) = 0 THEN
        ExecuteScalarParams = arrScalar(0,0)
    ELSE
        ExecuteScalarParams = NULL
    END IF
ELSE
    ExecuteScalarParams = NULL
END IF

rstScalar.Close
Set rstScalar = Nothing
Set cmd1 = Nothing
END FUNCTION

I used to pass a SQL query with question marks as place holders for the parameters like this:

SELECT TOP 1 UserName FROM Members WHERE (Created>?) AND (AdminLevel=?);

I would then set up a parameters array and pass it on to the function:

MyArray = ARRAY("1-JAN-2012",1)

The parameters in the array would replace the question marks in the query string in the order they appear.

I am trying to mimic this function in C# but I am stuck in the part where I have to pass the parameters. So far I got to the point where I have to used named place holders such as @Created and @AdminLevel instead of the question marks and then I have to set up parameter objects like this:

SqlParameter param = new SqlParameter();
param.ParameterName = "@AdminLevel";
param.Value = 1;

Is there a way to pass the parameters without having to set the parameter names and simply use question marks and the order in which they appear to specify which parameter goes where?

Answer 1

edit: as pointed out by Dana the MSDN Docs for Parameters shows you need to use named parameters for SqlClient but can use positional parameters for OleDb/ODBC.

You can make adding parameters a lot easier by using the code below; it's the skeleton I use but I'm sure there's a better way of doing it.

You still need to used named parameters, but you can simulate your question marks to an extent by naming them @a, @b, @c.. - positional parameters are fine until you get more than a handful of parameters and you have to constantly count the number of question marks to figure out which parameter value is being applied where, often resulting in mistakes.

using (var con = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString))
{
    con.Open();
    {
        using (var command = con.CreateCommand())
        {
            command.Connection = conn;
            command.CommandText = "SELECT * FROM [dbo].[Table] WHERE [c1] = @a AND [c2] = @b";
            command.Parameters.AddWithValue("@a", aVal);
            command.Parameters.AddWithValue("@b", bVal);
            command.CommandType = CommandType.Text;

            using (var reader = command.ExecuteReader())
            {
                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        ///
                    }
                }
                else
                {
                    ///
                }
            }
        }
    }
}