Easier way to send parameterised query to database?

Question

Is there a way to write the following code in less lines? It seems like a lot of code to execute such a simple query. No LINQ as I am using VS2005. Answers in either VB or C# are acceptable.

Using cmd As DbCommand = oDB.CreateCommand()
    cmd.CommandText = "SELECT * FROM [Table1] WHERE [Date] BETWEEN @Date1 AND @Date2"
    cmd.CommandTimeout = 30
    cmd.CommandType = CommandType.Text
    cmd.Connection = oDB
    Dim param As DbParameter
    param = cmd.CreateParameter()
    param.Direction = ParameterDirection.Input
    param.DbType = DbType.Date
    param.ParameterName = "@Date1"
    param.Value = Now().Date
    cmd.Parameters.Add(param)
    param = cmd.CreateParameter()
    param.Direction = ParameterDirection.Input
    param.DbType = DbType.Date
    param.ParameterName = "@Date2"
    param.Value = Now().Date.AddDays(intDaysAhead)
    cmd.Parameters.Add(param)
End Using
Dim reader As DbDataReader = cmd.ExecuteReader()

score 2 · Answer 1 · answered Oct 17 '12 at 08:39

2

These are probably the fewest lines you can get:

Using con = New SqlConnection("Connectionstring")
    Using cmd = New SqlCommand("SELECT * FROM [Table1] WHERE [Date] BETWEEN @Date1 AND @Date2", con)
        cmd.CommandTimeout = 30
        cmd.Parameters.AddWithValue("@Date1", Date.Today)
        cmd.Parameters.AddWithValue("@Date2", Date.Today.AddDays(intDaysAhead))
        con.Open()
        Using reader = cmd.ExecuteReader()

        End Using
    End Using
End Using

(assuming SqlClient but similar for other data providers)

answered Oct 17 '12 at 08:39

Tim Schmelter

450,073
74
686
939

I am not getting `AddWithValue` as an available method for `DbCommand.Parameters`. Only `Add`. – CJ7 Oct 17 '12 at 08:52
Then you don't use `SqlClient` as assumed since it's a method of `SqlParameterCollection`. What data provider are you using? it is also available in `OledbParameterCollection` even in .NET 2: http://msdn.microsoft.com/en-US/library/system.data.oledb.oledbparametercollection.addwithvalue(v=vs.80).aspx – Tim Schmelter Oct 17 '12 at 08:57
The code is trying to cater for potentially either `OleDBConnection` or `SQLConnection`, hence the use of `DbCommand`. – CJ7 Oct 17 '12 at 09:00

Easier way to send parameterised query to database?

1 Answers1

Linked