CODEWITHSUNDEEP
sqlvb.netasp.net-mvc-4
GalloPinto
GalloPinto

Reputation: 677

Better way to execute a lot of SQL commands in Visual Basic?

I'm using MVC4 with Visual Basic to make a web system. But when I have to save my data in my tables and relationships, I need to save in a lot of tables. So, I do this:

conn.Open()
Dim guardador_datos2 As New SqlCommand("INSERT INTO Creadores (ID_Creador,Usuario)  VALUES (" + ultimo_id_creador.ToString() + ", '" + creada_por + "')", conn)
guardador_datos2.ExecuteNonQuery()
conn.Close()

conn.Open()
Dim guardador_datos3 As New SqlCommand("INSERT INTO ReunionCreada_X_Usuario (ID_Reunion,ID_Creador) VALUES (" + ultimo_id.ToString() + "," + ultimo_id_creador.ToString() + ")", conn)
guardador_datos3.ExecuteNonQuery()
conn.Close()

conn.Open()
Dim guardador_datos4 As New SqlCommand("INSERT INTO Reuniones_X_DepartamentoEmpresa (ID_Departamento_X_Empresa,ID_Reunion) VALUES (" + departamento.ToString() + "," + ultimo_id.ToString() + ")", conn)
guardador_datos4.ExecuteNonQuery()
conn.Close()

I'm opening and closing the SqlConnection conn a lot of times (I only put three for example in here, but really there are like 9 conn.Open and Close).

Isn't there a better way to do this? Like only open conn 1 time and execute everything? Actually if I do that, it sends me an error telling me that the connection is actually open

Upvotes: 1

Views: 5839

Answers (4)

cadrell0
cadrell0

Reputation: 17307

Yes, you can open the connection once and execute multiple commands before closing it. I'd only recommend doing this if all of the commands will happen in a relatively close time period, which it sounds like you are doing.

What you don't want to do, is open a connection when your app starts, and leave it open the entire time.

However, if you are using MVC, you may want to consider using EntityFramework (or another ORM if you prefer). Then you don't have to manage connections at all.

Upvotes: 2

tlbignerd
tlbignerd

Reputation: 1114

You don't need to open the connection multiple times, just once for the open and once for the close. We often do something like this:

conn.Open()
Dim guardador_datos2 As New SqlCommand("INSERT INTO Creadores (ID_Creador,Usuario)  VALUES (" + ultimo_id_creador.ToString() + ", '" + creada_por + "')", conn)
guardador_datos2.ExecuteNonQuery()

Dim guardador_datos3 As New SqlCommand("INSERT INTO ReunionCreada_X_Usuario (ID_Reunion,ID_Creador) VALUES (" + ultimo_id.ToString() + "," + ultimo_id_creador.ToString() + ")", conn)
guardador_datos3.ExecuteNonQuery()

Dim guardador_datos4 As New SqlCommand("INSERT INTO Reuniones_X_DepartamentoEmpresa (ID_Departamento_X_Empresa,ID_Reunion) VALUES (" + departamento.ToString() + "," + ultimo_id.ToString() + ")", conn)
guardador_datos4.ExecuteNonQuery()
conn.Close()

You might also want to wrap it in a Try/Catch/Finally block and use transactions, so that if one of your inserts fails, all of them roll back.

Upvotes: 2

twoleggedhorse
twoleggedhorse

Reputation: 5048

I would put it all in a try..catch, open your connection, do all the stuff required then close the connection. The finally part of the try..catch ensures that the connection is closed even if there is an error thrown:

    conn.Open()

try

    Dim guardador_datos2 As New SqlCommand("INSERT INTO Creadores (ID_Creador,Usuario)  VALUES (" + ultimo_id_creador.ToString() + ", '" + creada_por + "')", conn)
    guardador_datos2.ExecuteNonQuery()

    Dim guardador_datos3 As New SqlCommand("INSERT INTO ReunionCreada_X_Usuario (ID_Reunion,ID_Creador) VALUES (" + ultimo_id.ToString() + "," + ultimo_id_creador.ToString() + ")", conn)
    guardador_datos3.ExecuteNonQuery()

    Dim guardador_datos4 As New SqlCommand("INSERT INTO Reuniones_X_DepartamentoEmpresa (ID_Departamento_X_Empresa,ID_Reunion) VALUES (" + departamento.ToString() + "," + ultimo_id.ToString() + ")", conn)
    guardador_datos4.ExecuteNonQuery()

catch ex as exception
    throw(ex)
finally
    conn.Close()
end try

Upvotes: 3

SLaks
SLaks

Reputation: 887365

As the error is trying to tell you, you can't open a connection if it's already open.

You don't need to call Open() multiple times.

Upvotes: 2

Related Questions