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]

Re: VB.NET & SQL Server Connection Example

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.SqlCo nnection

Return New SqlClient.SqlCo nnection(objPro gGlobals.Connec tionString)'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.Sql Client.SqlDataA dapter(sSQL,
GetConnectObj)

Dim ObjTable As New DataTable("")



Try

objDataAdapter. Fill(ObjTable)

Catch Ex As System.data.Sql Client.SqlExcep tion

'Log the error

clsErrorLog.Log (Ex)



MsgBox(Ex.Messa ge)

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.Sql Client.SqlComma nd

Dim objReader As System.Data.Sql Client.SqlDataR eader



With objCmd

.Connection = GetConnectObj()

.CommandText = sCommandText

.CommandType = CommandType.Tex t

End With



Try

objCmd.Connecti on.Open()

Catch myException As System.Exceptio n

If myException.Get Type.ToString =
"System.Data.Sq lClient.SqlExce ption" Then

iErrorNum = CType(myExcepti on,
System.Data.Sql Client.SqlExcep tion).Number

Exit Function

End If



If Not bNotLog Then

clsErrorLog.Log (myException)

iErrorNum = -1

sErrorDesc = myException.Mes sage

End If

'Notify the error to the user

If bNotifyError Then

MsgBox(myExcept ion.Message)

End If

End Try



If objCmd.Connecti on.State = ConnectionState .Open Then



Try

objReader =
objCmd.ExecuteR eader(CommandBe havior.CloseCon nection)



Do While objReader.Read( )

DataSetAdaptor = objReader(0)

Loop



objReader.Close ()



Catch myException As System.Data.Sql Client.SqlExcep tion



If Not bNotLog Then



'Log the error

clsErrorLog.Log (myException)

End If



iErrorNum = myException.Num ber



'Notify the error to the user

If bNotifyError Then

MsgBox(myExcept ion.Message)

End If



Try

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

objReader.Close ()

Catch

End Try



End Try



objCmd.Connecti on.Close()



End If

End Function







End Class









"Chris Moore" <chris@dblayout dotcom> wrote in message
news:Xns97D3786 F1CC6Ccabubba@2 07.46.248.16...[color=blue]
>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.[/color]

[drolaw]

Re: VB.NET &amp; SQL Server Connection Example

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.Sql Client

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(SQLs tr, mCn)

' Fill a DataSet with data returned from the database.
MyDataReader =
MyCommand.Execu teReader(Comman dBehavior.Close Connection)

GetData = MyDataReader

End Function

Public Sub ExecuteQuery(By Val 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=;DataBa se=;uid=;pwd="

mCn = New SqlConnection(C onnStr)

mCn.Open()

End Sub

End Class


Private Sub PopulateDropDow n(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.Ge tData(SQLStr)

'iterate through the data and populate the combo box
While myDataSet.Read
Combo.Items.Add (myDataSet("kbs iseq"))
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.