Setting up a database class and parameters

  • Thread starter Thread starter tshad
  • Start date Start date
T

tshad

I am trying to setup a database class and curious how you would deal with
parameters?

Part of the class looks like:
***********************************************************************
'
' This Constructor accepts a connection string as input
' and makes a connection to that SQL Server.
'
Public Sub New(ByVal ConnectionString As String)
m_cnDB = New SqlConnection(ConnectionString)
m_cnDB.Open()
End Sub

'
' In case there are other objects that need the live connection,
' make it available through a read-only property.
'
Public ReadOnly Property Connection() As SqlConnection
Get
Return m_cnDB
End Get
End Property

'
' Run a query that does not return records
'

Public Function Execute(ByVal SQL As String) As Integer
Dim lngRecords As Integer
Dim cmdQuery As New SqlCommand()
cmdQuery.Connection = m_cnDB
cmdQuery.CommandText = SQL
cmdQuery.CommandType = CommandType.Text
lngRecords = cmdQuery.ExecuteNonQuery()
End Function

'
' Run a Stored Procedure that does not return records
'
Public Function ExecuteStoredProc(ByVal SQL As String) As Integer
Dim lngRecords As Integer
Dim cmdQuery As New SqlCommand()
cmdQuery.Connection = m_cnDB
cmdQuery.CommandText = SQL
cmdQuery.CommandType = CommandType.StoredProcedure
lngRecords = cmdQuery.ExecuteNonQuery()
End Function
*******************************************************

How do you handle parameters or can you?

Normally you would do something like:
****************************************************
Dim sQuery as String = "Update Vendor set FullName = @CompanyName where
VendorID = @VendorID"
Dim objCmd as New SqlCommand(sQuery,objConn)

with objcmd.Parameters
.Add("@VendorID",SqlDbType.bigInt).value = session("VendorID")
.Add("@CompanyName",SqlDbType.VarChar,30).value = CompanyName.text
end with
***************************************************************************

In my class, I would send sQuery to the class - but I can't do that here.

Thanks,

Tom
 
An interesting exercise, but the connection can be used by only one instance
at a time.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
 
William (Bill) Vaughn said:
An interesting exercise, but the connection can be used by only one
instance at a time.
Click to expand...

In my New function, I am setting up a new connection, aren't I?

Tom
 
The following is open to SQL injection attacks if you aren't careful:

Public Function ExecuteStoredProc(ByVal SQL As String) As Integer
Dim lngRecords As Integer
Dim cmdQuery As New SqlCommand()
cmdQuery.Connection = m_cnDB
cmdQuery.CommandText = SQL
cmdQuery.CommandType = CommandType.StoredProcedure
lngRecords = cmdQuery.ExecuteNonQuery()
End Function

It is also much better to used parameterized command objects
for performance reasons with sql server.

read:

http://www.eggheadcafe.com/articles/adonet_exec_stored_procedures_sqlhelper.asp



--
2005 Microsoft MVP C#
Robbe Morris
http://www.robbemorris.com
http://www.mastervb.net/home/ng/forumvbcode/post10017013.aspx
http://www.eggheadcafe.com/articles/adonet_source_code_generator.asp
 
Back
Top