[RESOLVED] Am I Connecting Correctly/Efficiently?

[samuelk]

This is my first "from scratch" database application, so I'm not sure if I'm going about things in the best manner and would appreciate any advice/suggestions, even inor quibbles. I'm using Visual Basic 2010 Pro and our primary database is SQL Server 2000, though it will hopefully be upgraded to 2008 in the near future, and this application will likely end up being used by other branches that already utilize 2008.

Everything works fine, for the most part, I just want to make sure I'm not doing anything "bad".

I have the following variables declared publicly:

    Public Conn As SqlConnection
    Public Reader As SqlDataReader
    Public User As New UserData 'Class containing the username, password and privilege levels
    Public Connect As New ConnectInfo 'Class containing the servername and various database names
    Public SQLQuery As New SqlCommand

I have a public function named GetConnect that sets the connection string specify to the situation (there are a few times when data needs to be pulled from a different database within the same server) in the form of:

Conn = New SqlConnection("Data Source=" & Connect.Server & ";Initial Catalog=" & Connect.AppName & ";User Id=" & User.Username & ";Password=" & User.Password & ";")

And here is one of my several dozen database accessing procedures:

    Public Sub PopulateCustomers(ComboBox As ComboBox)
        Dim daCustomers As New SqlDataAdapter
        Dim dsCustomers As New DataSet

        Try
            Conn = GetConnect()
            SQLQuery = Conn.CreateCommand
            SQLQuery.CommandText = "SELECT Customer_Name, Customer_ID FROM Customer_Information ORDER BY Customer_Name"

            daCustomers.SelectCommand = SQLQuery
            daCustomers.Fill(dsCustomers, "Customer_Information")

            With ComboBox
                .DataSource = dsCustomers.Tables("Customer_Information")
                .DisplayMember = "Customer_Name"
                .ValueMember = "Customer_ID"
                .SelectedIndex = -1
            End With

        Catch ex As Exception
            MessageBox.Show("Error: " & ex.Source & ": " & ex.Message, "Connection Error", MessageBoxButtons.OK)

        Finally
            Conn.Close()

        End Try
    End Sub

Is there anything glaringly wrong with any of this code?

[szlamany]

The USING statement allows you to create objects and "trust" that they will close and dispose properly.

Look at these two examples

Try
    Using dcn As New SqlConnection(System.Web.Configuration.WebConfigurationManager.ConnectionStrings("LocalSQLServerAWC").ToString)
        Using cmd As New SqlCommand
            cmd.CommandType = CommandType.StoredProcedure
            cmd.CommandText = "dbo.awc_ReqUserReq_ReqApp_Save"
            cmd.Connection = dcn
            cmd.CommandTimeout = 0
            DetermineParameters(dcn, cmd)
            cmd.Parameters("@CtrlVal1").Value = ctrlval1
            cmd.Parameters("@CtrlVal2").Value = ctrlval2
            cmd.Parameters("@username").Value = username
            dcn.Open()
            strMessage = cmd.ExecuteScalar().ToString
            If strMessage.Contains("~mail=") Then
                Dim MC As Integer = strMessage.IndexOf("~mail=")
                Dim strArray() As String = strMessage.Substring(MC + 6).Split("~"c)
                Dim wholist As String = strArray(1) & "~" & strArray(3) & "~" & strArray(5)
                Dim emailcontent As String = strArray(7) & "~" & strArray(9)
                strMessage = strMessage.Substring(0, MC)
                Dim emailSuccess As Boolean = SendEMail(wholist, emailcontent)
            End If
            .NewObject("button", "true")
            .Seperate()
        End Using
    End Using
Catch ex As Exception
    strSuccess = ""
    strMessage = ex.Message.Replace("""", "'").Replace("\", "\\")
End Try

and this one

Try
    Using dcn As New SqlConnection(System.Web.Configuration.WebConfigurationManager.ConnectionStrings("LocalSQLServerAWC").ToString)
        Using cmd As New SqlCommand
            cmd.CommandType = CommandType.StoredProcedure
            cmd.CommandText = "dbo.awc_" & fromddtype & toddtype & "_window" ' StudentVisits"
            cmd.Connection = dcn
            cmd.CommandTimeout = 0
            DetermineParameters(dcn, cmd)
            SetParameters(cmd, True, fromwho, objReturn, Nothing, "", "", username)
            dcn.Open()
            Using sdrReader As SqlDataReader = cmd.ExecuteReader
                Do
.
.
.
                    While sdrReader.Read
.
.
.
                    End While
.
.
.
                Loop While sdrReader.NextResult
            End Using
        End Using
    End Using
Catch ex As Exception
    .ResetJson()
    .StartObject()
    .NewObject("%%dalerror%%", ex.Message.Replace("""", "'").Replace("\", "\\"))
    NeedObjEnd = True
    SkipEndArray = True
End Try

[samuelk]

Thank you. 'Using' is something I need to read up on. I suspect I'll be spending a couple of hours inserting them in the very near future.