How to execute multiple queries with one connection

Question

0.00/5 (No votes)

See more:

Hi,

How to execute multiple queries with one connection in vb.net.
I am using windows vb.net with Access DB.

In my project i need to delete the records of 15 tables.
Every time i need to open the connection and close the connection for every delete statement.
Same way i need to insert the records for 15 to 20 tables.
It took lot of time to execute.

is it possible to delete the records from multiple tables with one connections?

Need code for this

Sucharitha

Posted 17-Jul-13 2:04am

RagSuchi

Add a Solution

Comments

Maciej Los 17-Jul-13 8:21am

Who do you said that you need to open/close connection every time when you execute query?

Jean A Brandelero 17-Jul-13 8:22am

Yes, it is possible. Just make this connection public, open it, run all your tasks then close the connection.

Like:

Shared con as SqlConnection;

OpenConnection()
DeleteStuff()
CloseConnection()

Maciej Los 17-Jul-13 8:27am

It's not SQLConnection. It's OLEDBConnection, because of MS Access database! Please, read question carefuly.

Jean A Brandelero 17-Jul-13 8:28am

This was just an example, not a solution. The idea is the same.

Maciej Los 17-Jul-13 8:26am

Improve question and show us your code and example data. Shortly describe why you need to reopen connection...

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

sunil mali · Answer 1 · 2013-07-17T02:18:00

Hey hi

you must be calling sp to achieve this task right
so in once sp also you can delete all your tables..

SQL

USE [LTXFlow]
GO
/****** Object:  StoredProcedure [dbo].[save_Aging]    Script Date: 7/17/2013 5:44:39 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[save_Aging]
@Id int,
@dtTime datetime,
@Status varchar(30),
@UpldMstrId int,
@UpdatedBy int
as
begin
	if @Id=1
	begin


			insert into Tbl1(dtTime,[Status],UpdatedBy,UpldMstrId)values(@dtTime,@Status,@UpdatedBy,@UpldMstrId)
			insert into Tbl2(.....)
insert into Tbl3()
.
.
.
.
	
		
	end
end

instead of calling it 15 times for 15 tables you can add 15 insert statement in one sp.