Can I make this code better using SQLParamater object?

I wrote a class to handle my sqlDataReaders:

Namespace CommonFunctions

PublicClass DataAccess

PublicSubNew()

EndSub

Private ConnStrAsString ="connectionString"

PublicFunction returnDR(ByVal strSQLAsString)As SqlClient.SqlDataReader

Dim drAs SqlClient.SqlDataReader

Dim myCnAsNew SqlClient.SqlConnection(ConnStr)

Dim myCmdAsNew SqlClient.SqlCommand(strSQL, myCn)

myCn.Open()

dr = myCmd.ExecuteReader(CommandBehavior.CloseConnection)

Return dr

EndFunction' returnDR

EndClass' Public Class DataAccess

EndNamespace' Namespace CommonFunction

The way I invoke this is:

Imports CommonFunctions

Dim daAsNew DataAccess

Dim sqlDataReaderAs SqlDataReader

Dim strSQLAsNew StringBuilder

strSQL.Append("Some SQL Query")

sqlDataReader = da.returnDR(strSQL.ToString())

DoWhile sqlDataReader.Read()

strVar = sqlDataReader("columnName")

Loop' Do While sqlDataReader.Read()

sqlDataReader.Close()

What I would like to do, is use the SQL Paramater Object instead of passing my method a String. One so that I dont have to explicitly test for SQL Injection and two, cause I have never worked with the SQL Paramater Object before. :)

J

Sure, put this in your class:

PublicFunction returnDR(ByVal cmd AS SqlCommand)As SqlClient.SqlDataReader

Dim drAs SqlClient.SqlDataReader

Dim myCnAsNew SqlClient.SqlConnection(ConnStr)
mycmd.connection=myCn
myCn.Open()

dr = myCmd.ExecuteReader(CommandBehavior.CloseConnection)

Return dr

EndFunction' returnDR

Then you can pass returnDR either a SQLString, or a SqlCommand object.

like this:

dim cmd as new SqlCommand("SELECT * FROM MyTable WHERE ID=@.ID")
cmd.parameters.add("@.ID",sqdbtype.int).value={Some ID}
sqlDataReader = da.returnDR(cmd)

and of course:
sqlDataReader=da.returnDR("SELECT * FROM MyTable WHERE ID=" & {Some ID})

will continue to work as well. That way you can move forward with using SqlCommand's for new queries, and covert the old queries when you have time.

You can then also change your old function to:

PublicFunction returnDR(ByVal sqlcmd AS String)As SqlClient.SqlDataReader
dim cmd as new sqlCommand(sqlcmd)
return returnDR(cmd)
End Function