Best Practice... One Simple Question...

[soulcode]

Hi Guys,

I have a simple question about veriables like SQLConnection.


Do you guys generally create muiltiple veriables of the same type even if you use them one after the other in a procedure... or ... would you just create the one veriable then clear it (or dispose it) then use them again.

I'm opening two databases in a procedure one after the other. I dont know if its good practice to use the same veriable like SQLConnection or if its cleaner to create and use two.

What do you guys think??

Let me know if more clarification is needed...



soulcode

[Edneeis]

Depends on the code give us an example.

[soulcode]

hi guys,

Hope this example illustrates what I mean in this thread.

    Private Sub frmSelectDB_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'check that initial application database(MIS_InitialDB) exists, if not, create it with one table

       'first set of variable dealing with the first database
        Dim sqlDBConn As New SqlConnection
        Dim adpListDB As New SqlDataAdapter
        Dim dsetListDB As New DataSet
        Dim shLoopCnt As Short

        'open MIS_InitialDB database to check if it exists
        Try
            sqlDBConn = New SqlConnection("Initial Catalog=MIS_InitialDB; Data Source=localhost; uid=sa")
            sqlDBConn.Open()

        Catch ex As Exception
            'open database cause an error. create database because it doesnt exist

 
            'Second set of variable dealing with the second database
            Dim sqlDBCommand As SqlCommand
            Dim sqlNewConn As SqlConnection


            MsgBox("MIS Initial Database was not found.  One will be created now.", MsgBoxStyle.OKOnly, "Attention...")
            sqlNewConn = New SqlConnection("Data Source=vcomp; uid=sa")
            sqlNewConn.Open()

            'create database called MIS_InitialDB
            sqlDBCommand = New SqlCommand("CREATE DATABASE MIS_InitialDB", sqlNewConn)
            sqlDBCommand.ExecuteNonQuery()

            'clean code
            sqlDBCommand.Dispose()
            sqlNewConn.Close()
            sqlNewConn.Dispose()

            'create Installed_DB table in newly created database
            Dim sqlTableComm As SqlCommand
            Dim sqlTableConnection As New SqlConnection

            sqlTableConnection = New SqlConnection("Initial Catalog=MIS_InitialDB; Data Source=localhost; uid=sa;")
            sqlTableConnection.Open()

            'create table code
            sqlTableComm = New SqlCommand("CREATE TABLE Installed_DB(DB_Name VARCHAR(15) NOT NULL PRIMARY KEY, Created DATETIME NOT NULL, Deleted BIT NOT NULL)", sqlTableConnection)
            sqlTableComm.ExecuteNonQuery()

            'clean code
            sqlTableComm.Dispose()
            sqlTableConnection.Close()
            sqlTableConnection.Dispose()

            'Pause applicationto give PC time to phicially create database before opening database 
            Call Please_Wait_Form("Please Wait...", "Please wait while MIS Initial Database gets created.", 5000)
            sqlDBConn = New SqlConnection("Initial Catalog=MIS_InitialDB; Data Source=localhost; uid=sa")
            sqlDBConn.Open()
        End Try


        'Select all entries in Installed_DB table and display in ListView control
        adpListDB = New SqlDataAdapter("SELECT * FROM Installed_DB", sqlDBConn)
        adpListDB.Fill(dsetListDB)

        For shLoopCnt = 0 To dsetListDB.Tables(0).Rows.Count - 1
            lvSelection.Items.Add(dsetListDB.Tables(0).Rows(shLoopCnt).Item("DB_Name") + " " + dsetListDB.Tables(0).Rows(shLoopCnt).Item("Created"), 0)
        Next

    End Sub

See how i'm opening and closing databases and tables. Do you guys think its safe and clean enough to only declare one set of variables and use them throughout the procedure or is it best that I declare seperate variables for each database like the code above??


soulcode

[Edneeis]

Yes or at least that is how I would and/or do it.

[Pirate]

And to add small hint : when local variables go out of scope , GC takes care of clearing and claiming memory allocations for them .

[soulcode]

Cool... thanks for your help guys...


I have one more question :-) When updating, adding or deleting data from a table, do you guys think its best to use SQL code or do you think using DataRows and DataSets is best??



SQL Code example....

Dim SQL As String

sSQL = "INSERT INTO Suppliers (SupplierName, SupplierID) VALUES ('Rotten Potatoes, Inc.', 1)"

objCommand.CommandText = sSQL
objCommand.ExecuteNonQuery()
objCommand.CommandText = "UPDATE Suppliers SET SupplierName = 'Rotten Tomatoes, Inc.' WHERE SupplierID=1"
objCommand.ExecuteNonQuery()
objCommand.CommandText = "DELETE FROM Suppliers WHERE SupplierID=1"
objCommand.ExecuteNonQuery()

Or Dataset example...

Dim aRow As DataRow

aRow = objDS.Tables("Suppliers").NewRow()
aRow("SupplierID") = 1
aRow("SupplierName") = "Rotten Eggs, Ltd."
objDS.Tables("Suppliers").Rows.Add(aRow)

------------

Dim aRow As DataRow

aRow = objDS.Tables("Suppliers").Rows(0)
objDS.Tables("Suppliers").Remove(aRow)

--------------

objDA.Update(objDS)
objDS.AcceptChanges()

I'm sure each method (SQL or Dataset) has its strengths and weaknesses, but in general, which method would be best to stick with? ... or is it just to hard to call?


soulcode

[Edneeis]

It depends if I already have the data in a dataset or not. Also how often the data needs to be updated or get changed. I don't think there is a real default answer for that one. If you are using a dataset already then definately use the datarows I would say.

[soulcode]

Yeah, thats true... in most cases, my data is already in a dataset, it seems to be easier to work with that way.


Thanks for your input.



soulcode