SQL options and question marks

Question

I am making the transition from classic ASP to ASP.NET. And I have some problems doing some basic things that I usually did with the old method. The following is a handy ASP function that I used to execute scalar queries with multiple 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 an SQL query with question marks as place holders for parameters like this:

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

Then I would set up an array of parameters and pass it to the function:

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

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

I am trying to reproduce this function in C #, but I am stuck in the part where I need to pass parameters. So far, I have come to the point that I need to use nominal names such as @Created and @AdminLevel instead of question marks, and then I need to configure parameter objects such as this:

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

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

Answer 1

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

You can simplify adding parameters using the code below; this is the skeleton that I use, but I'm sure there is a better way to do this.

You still need to use named parameters, but you can mimic your question marks to the extent by calling them @a, @b, @c .. - positional parameters are fine until you get more than a few parameters, and you have to keep counting the number of question marks to find out what parameter value is applied where errors often occur.

 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 { /// } } } } }