[RESOLVED] Which queries is looks better?

[AceDuk]

1.

'Insert
            Try
                Dim strSql As String = "INSERT INTO Users ([Username], [Password]) VALUES (@Username,@Password)"
                con.Open()
                Using cmd As New OleDbCommand(strSql, con)
                    cmd.Parameters.AddWithValue("@Username", txtUsername.Text)
                    cmd.Parameters.AddWithValue("@Password", txtPassword.Text)
                    cmd.ExecuteNonQuery()
                End Using
                con.Close()
                MsgBox("Successfully saved", vbInformation, "")
            Catch ex As Exception
                MsgBox(ex.Message)
                con.Close()
            End Try
 
'Updating
            Try
                con.Open()
                Dim strSql As String = "UPDATE Users SET [Username]= @Username, [Password]= @Password WHERE ID = @ID"
                Using cmd As New OleDbCommand(strSql, con)
                    cmd.Parameters.AddWithValue("@Username", txtUsername.Text)
                    cmd.Parameters.AddWithValue("@Password", txtPassword.Text)
                    cmd.Parameters.AddWithValue("@ID", labelID.Text)
                    cmd.ExecuteNonQuery()
                End Using
                con.Close()
                MsgBox("Successfully saved", vbInformation, "")
            Catch ex As Exception
                MsgBox(ex.Message)
                con.Close()
            End Try
 
  'Removing
            Try
                Dim strSql As String = "DELETE from Users WHERE ID = @ID"
                con.Open()
                Using cmd As New OleDbCommand(strSql, con)
                    cmd.Parameters.AddWithValue("@ID", labelID.Text)
                    cmd.ExecuteNonQuery()
                End Using
                con.Close()
                MsgBox("Successfully removed", vbInformation, "")
            Catch ex As Exception
                MsgBox(ex.Message)
                con.Close()
            End Try

2.

'Insert
            Try
                con.Open()
                Dim insert_user As OleDbCommand = New OleDbCommand("INSERT INTO Users ([Username], [Password]) VALUES (@Username,@Password)", con)
                insert_user.Parameters.AddWithValue("@Username", txtUsername.Text)
                insert_user.Parameters.AddWithValue("@Password", txtPassword.Text)
                insert_user.ExecuteNonQuery()
                insert_user.Dispose()
                con.Close()
                MsgBox("Successfully saved", vbInformation, "")
            Catch ex As Exception
                MsgBox(ex.Message)
                con.Close()
            End Try
 
'Updating
            Try
                con.Open()
                Dim update_user As New Data.OleDb.OleDbCommand("UPDATE Users SET [Username]= @Username, [Password]= @Password WHERE ID = @ID;", con)
                update_user.Parameters.AddWithValue("@Username", txtUsername.Text)
                update_user.Parameters.AddWithValue("@Password", txtPassword.Text)
                update_user.Parameters.AddWithValue("@ID", labelID.Text)
                update_user.ExecuteNonQuery()
                update_user.Dispose()
                con.Close()
                MsgBox("Successfully saved", vbInformation, "")
            Catch ex As Exception
                MsgBox(ex.Message)
                con.Close()
            End Try
 
 'Removing
            Try
                con.Open()
                Dim delete As New OleDbCommand("DELETE from Users WHERE ID = @ID;", con)
                delete.Parameters.AddWithValue("@ID", labelID.Text)
                delete.ExecuteNonQuery()
                con.Close()
                MsgBox("Successfully removed", vbInformation, "")
            Catch ex As Exception
                MsgBox(ex.Message)
                con.Close()
            End Try

Thanks

[Shaggy Hiker]

The former. However, I'd use the Using statement for the Connection object, not just the Command object. After all, if con.Open throws an exception, are you sure that con.Close even makes sense? The Using construct will take care of closing and cleaning up the connection, whether an exception is thrown or not, so you don't have to worry about it.

[AceDuk]

how i understand you say me to remove con.open, con.close?
If i remove that lines of code i always get errors

[Shaggy Hiker]

You have to have con.open, anyways. What I was saying was something like this:

Using con As New OleDB.OleDBConnection(yourConnectionStringHere)
 Try
                Dim strSql As String = "INSERT INTO Users ([Username], [Password]) VALUES (@Username,@Password)"
                con.Open()
                Using cmd As New OleDbCommand(strSql, con)
                    cmd.Parameters.AddWithValue("@Username", txtUsername.Text)
                    cmd.Parameters.AddWithValue("@Password", txtPassword.Text)
                    cmd.ExecuteNonQuery()
                End Using
                
                MsgBox("Successfully saved", vbInformation, "")
            Catch ex As Exception
                MsgBox(ex.Message)
                
            End Try
   End Using

So, the Open is still there, but the Close has been removed, because EndUsing will deal with that correctly, whether an exception is thrown, or not.

[AceDuk]

I forgot to say that i always use connection string in module

Imports System.Data.OleDb
Module Module1
    Public con As New OleDbConnection("Provider=MICROSOFT.ACE.OLEDB.12.0; Data Source=|DataDirectory|/database.accdb")
End Module

[Shaggy Hiker]

Ah, in that case, the Using wouldn't work right, but then again, there's no good reason to handle a connection that way. You may think that sharing a connection around is fine, and it probably is as long as you need only one, but connections are a limited resource. As a general rule, get one, use it, get rid of it, all as fast as you can. With an Access DB, it often doesn't matter much, because Access doesn't deal well with multiple concurrent users, so it tends to do best with just a single user, and that's how it is generally used. Once you have multiple concurrent users, then creating and disposing of the connection as I showed with the Using statement will work better.

You may think that it's wasteful to create new connections all the time like that, and you'd have been right at one point (I don't know how old you are), but not in .NET, not any longer. Even when you dispose of a connection (like the End Using will do), it doesn't really go away. It is just released back into a connection pool. When you create a connection, if there is one available in the pool, you get that one. Only if there are none available in the pool will a new one be created.

For example, I have a program which may create then dispose of, a couple dozen connections during startup. However, if I look at SQL Server, I see that there are only three or four actual connections that have been created. Those three or four have just been re-used over and over by the different parts of the program. It's all pretty efficient.

[AceDuk]

so you suggest me to get rid of Module, and how you say: get one, use it, get rid of it, all as fast as you can.

[Shaggy Hiker]

I've done it both ways. The way you have it is perfectly fine if there will only be one program connecting to the database at a time, which is often the case. If there could be multiple users, then changing it would be advisable.

[AceDuk]

so if one program use that database it's better my suggestion (With Module connection)
also your suggest is look good if i delete module - maybe i should use your suggestion because i avoid con.close

[Shaggy Hiker]

There is that benefit.

[AceDuk]

Ok thanks for help