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

Question

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

Are they all related with FKs? Entity Framework might really make a difference if that is the case, assuming changing your underlying tech is an option — Lotok
– Lotok, Commented Jul 15, 2013 at 14:41
Look at a how-to article about using parameterized SQL statements rather than string concatenation to assemble your statements -- this is a best practice to avoid SQL injection attacks as well as format issues. — Cortright
– Cortright, Commented Jul 15, 2013 at 14:43

twoleggedhorse · Accepted Answer · 2013-07-15 14:40:41Z

3

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

answered Jul 15, 2013 at 14:40

twoleggedhorse

5,0484 gold badges26 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

cadrell0 Over a year ago

Or just use a Using. msdn.microsoft.com/en-us/library/htd05whh(v=vs.110).aspx

tlbignerd · Accepted Answer · 2013-07-15 14:41:34Z

2

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.

answered Jul 15, 2013 at 14:41

tlbignerd

1,13410 silver badges22 bronze badges

3 Comments

GalloPinto Over a year ago

Thanks a lot! It works for the ExecuteNonQuery, but for the ExecuteReader it doesn't work. But now I only have 3 conn.open and close, not 9, jaja! Thanks!

tlbignerd Over a year ago

ExecuteReader should work the same way. The only issue here is that you should need to fill a dataTable with the reader results. Do you get some sort of error with the ExecuteReader?

marc_s Over a year ago

@GalloPinto: in the SqlDataReader case, you need to iterate over the reader and store the data you get back, and then close the SqlDataReader since by default you can only ever have a single one of those open. The SqlDataReader - not the SqlConnection is the issue

SLaks · Accepted Answer · 2013-07-15 14:38:55Z

2

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.

answered Jul 15, 2013 at 14:38

SLaks

891k182 gold badges1.9k silver badges2k bronze badges

Comments

cadrell0 · Accepted Answer · 2013-07-15 14:42:00Z

2

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.

answered Jul 15, 2013 at 14:42

cadrell0

17.4k5 gold badges55 silver badges70 bronze badges

Collectives™ on Stack Overflow

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

4 Answers 4

1 Comment

3 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related