Creating a general SQL module in VB.NET
Hey everyone. I'm fairly new to VB.NET and I'm looking to create a module that will contain all general SQL functionality like connect, disconnect and execute a sql query etc.
I think I'm almost there but the code keeps bombing in one place.
Can anyone see what is wrong with the following code?
It bombs here, setting the command object to a connection object. The opening and closing of the connection works fine.
cmdSystem.Connection = cnSystem
Or maybe I'm just thinking old VB and am going about this all wrong.
Public Module modGeneral
Private cnSystem As New SqlClient.SqlConnection
Private cmdSystem As SqlClient.SqlCommand
Public Sub ConnectToSQL()
Dim sConnectionString As String = "Data Source=SQL;Initial Catalog=XXXX;User ID=XXXX;Password=XXXX"
Try
cnSystem.ConnectionString = sConnectionString
cnSystem.Open()
Catch ex As Exception
End Try
End Sub
Public Sub DisconnectFromSQL()
Try
cnSystem.Close()
cnSystem.Dispose()
Catch ex As Exception
End Try
End Sub
Public Function lExecuteSQL(ByVal sSQL As String) As Long
Dim lRecordsAffected As Long = 0
Try
cmdSystem.Connection = cn开发者_StackOverflow中文版System
cmdSystem.CommandText = sSQL
lRecordsAffected = cmdSystem.ExecuteNonQuery()
cmdSystem.Dispose()
Catch ex As Exception
End Try
Return lRecordsAffected
End Function
End Module
Thanks in advance.
at some point, you need to instantiate your command object like you did the connection.
have you considered having these functions in a class instead of a module?
精彩评论