VB.NET & SQL Server Connection Example

[Chris Moore]

I am new to .NET & SQL Server and am having fits trying to connect to a SQL
Server Database/table. Can someone please please please send me a copy of
their connection process (connection, dataadapater, fill, whatever is
needed to actually connect to the database & table) including querying a
table and looping through it? I don't need the code during the loop, I
just would like to see how you reference the fields and such.

Thanks for the help.

 

[Samuel Shulman]

Below is a sample class, that should do the job

If you need a connection string sample please reply with a request

hth,
Samuel

Public Class clsDataSQL





Public Shared Function GetConnectObj() As SqlClient.SqlConnection

Return New SqlClient.SqlConnection(objProgGlobals.ConnectionString)'The
connection string should be assigned



End Function





'Return a dataTable that was filled by the Adaptor.Fill method

Public Shared Function FillData(ByVal sSQL As String) As DataTable

Dim objDataAdapter As New System.Data.SqlClient.SqlDataAdapter(sSQL,
GetConnectObj)

Dim ObjTable As New DataTable("")



Try

objDataAdapter.Fill(ObjTable)

Catch Ex As System.data.SqlClient.SqlException

'Log the error

clsErrorLog.Log(Ex)



MsgBox(Ex.Message)

End Try



Return ObjTable

End Function





'Returns the ID of the new record

Shared Function DataSetAdaptor(ByVal sCommandText As String, _

ByRef iErrorNum As Integer, _

ByRef sErrorDesc As String, _

ByVal bNotLog As Boolean, _

ByVal bNotifyError As Boolean) As Integer



Dim objCmd As New System.Data.SqlClient.SqlCommand

Dim objReader As System.Data.SqlClient.SqlDataReader



With objCmd

.Connection = GetConnectObj()

.CommandText = sCommandText

.CommandType = CommandType.Text

End With



Try

objCmd.Connection.Open()

Catch myException As System.Exception

If myException.GetType.ToString =
"System.Data.SqlClient.SqlException" Then

iErrorNum = CType(myException,
System.Data.SqlClient.SqlException).Number

Exit Function

End If



If Not bNotLog Then

clsErrorLog.Log(myException)

iErrorNum = -1

sErrorDesc = myException.Message

End If

'Notify the error to the user

If bNotifyError Then

MsgBox(myException.Message)

End If

End Try



If objCmd.Connection.State = ConnectionState.Open Then



Try

objReader =
objCmd.ExecuteReader(CommandBehavior.CloseConnection)



Do While objReader.Read()

DataSetAdaptor = objReader(0)

Loop



objReader.Close()



Catch myException As System.Data.SqlClient.SqlException



If Not bNotLog Then



'Log the error

clsErrorLog.Log(myException)

End If



iErrorNum = myException.Number



'Notify the error to the user

If bNotifyError Then

MsgBox(myException.Message)

End If



Try

'Close the object reader that will cause the connection
to close

objReader.Close()

Catch

End Try



End Try



objCmd.Connection.Close()



End If

End Function







End Class

 

[drolaw]

This is extremely rough, and it will only work for SQL server (You'll
need to add data for each argument in the connection string):

Imports System.Data
Imports System.Data.SqlClient

Public Class DataConnection
Dim mIsSQLServer As Boolean
Dim mCn As SqlConnection


Public Function GetData(ByVal SQLstr As String) As SqlDataReader
Dim MyCommand As SqlCommand
Dim MyDataReader As SqlDataReader

' Create a Command object with the SQL statement.
MyCommand = New SqlCommand(SQLstr, mCn)

' Fill a DataSet with data returned from the database.
MyDataReader =
MyCommand.ExecuteReader(CommandBehavior.CloseConnection)

GetData = MyDataReader

End Function

Public Sub ExecuteQuery(ByVal Query As String)
'This Sub does not return a data set it just executes a query.
Dim myCmd As OleDbCommand



End Sub


Public Sub New(Optional ByVal IsSqlServer As Boolean = False)
'initialize the connection string
Dim ConnStr As String

ConnStr = "Server=;DataBase=;uid=;pwd="

mCn = New SqlConnection(ConnStr)

mCn.Open()

End Sub

End Class


Private Sub PopulateDropDown(ByRef Combo As DropDownList)
Dim myConnection As New DataConnection
Dim myDataSet As SqlDataReader
Dim SQLStr As String

SQLStr = "select kbsiseq from kbsi"

'get the data
myDataSet = myConnection.GetData(SQLStr)

'iterate through the data and populate the combo box
While myDataSet.Read
Combo.Items.Add(myDataSet("kbsiseq"))
End While

End Sub

There are of course, other ways to do it, and there is a lot more to a
real connection class than that, but that should get you through the
basics.