Execute multiple queries

Question

I am using OleDB for executing my queries in C#,

Is there any way I can execute multiple queries in one command statement?

I tried to separate them with semi-colon (;) but it gives error "Characters found at the end"

I have to execute a few hundreds of queries at once.

Edit: I am executing Insert Statements.

The related question is basically for stored procedures. Didn't helped. — smartali89, May 01 '10 at 15:49
Well written question. I expected it to me a nightmare trying to google for this specific problem, but there it was as the first result. Thanks! — Michael Lang, Apr 13 '11 at 22:43

score 2 · Accepted Answer · answered May 01 '10 at 16:14

It's not possible to combine queries within one OleDbCommand. If possible, make a stored procedure, otherwise you'll have to stick to firing many OleDbCommands at the server.

It's worth noting, though, that connection pooling is enabled for OleDbConnection by default:

When you use the .NET Framework Data Provider for OLE DB, you do not have to enable connection pooling because the provider manages this automatically.

EDIT:

Try something like this:

INSERT INTO myTable ( Column1, Column2, Column3 )
SELECT 'Value1', 1, 'Value3'
UNION
SELECT 'Value1', 2, 'Value3'
UNION
SELECT 'Value1', 3, 'Value3'
UNION
SELECT 'Value1', 4, 'Value3'

Depending on which OleDb provider you're connecting to, you might be able to use this. But beware, it might be as slow as inserting records one by one anyway.

Example SQL does not work with Access database: *"Syntax error (missing operator) in query expression"* (Provider=Microsoft.Jet.OLEDB.4.0) — JohnB, Aug 14 '12 at 23:15

score 1 · Answer 2 · edited Jul 07 '10 at 14:37

Simply Batch them up using GO (groups a batch) and colons to separate queries inside a batch. Make sure to surround your colon with spaces. You need to submit this SQL to sp_executesql.

BEGIN TRANSACTION
GO
USE AdventureWorks;
GO
CREATE TABLE dbo.mycompanies
(
 id_num int IDENTITY(100, 5),
 company_name nvarchar(100)
)
GO
INSERT mycompanies (company_name)
   VALUES (N'A Bike Store');
INSERT mycompanies (company_name)
   VALUES (N'Progressive Sports');
INSERT mycompanies (company_name)
   VALUES (N'Modular Cycle Systems');
INSERT mycompanies (company_name)
   VALUES (N'Advanced Bike Components');
INSERT mycompanies (company_name)
   VALUES (N'Metropolitan Sports Supply');
INSERT mycompanies (company_name)
   VALUES (N'Aerobic Exercise Company');
INSERT mycompanies (company_name)
   VALUES (N'Associated Bikes');
INSERT mycompanies (company_name)
   VALUES (N'Exemplary Cycles');
GO

SELECT id_num, company_name
FROM dbo.mycompanies
ORDER BY company_name ASC;
GO
COMMIT;
GO

Example taken from MSDN.

`GO` only applies to Microsoft SQL Server Management Studio. It is used to break statements up, and hence will not work when using `OleDbCommand` — Codesleuth, May 01 '10 at 16:10
GO can be used when executing your statement via sp_executesql. — Johannes Rudolph, May 01 '10 at 16:27

score 0 · Answer 3 · edited May 23 '17 at 11:52

0

Use sp_executesql.

Do see my answer in another question where I include a sample usage of sp_executesql to send SQL queries in a batch.

edited May 23 '17 at 11:52

Community

1
1

answered May 01 '10 at 16:14

Amry

4,791
2
23
24

41686d6564 stands w. Palestine · Answer 4 · 2018-04-04T13:08:24.290

I wanted to execute multiple SQL statements in an Access database using OleDB for a project I'm working on, and I couldn't find anything that's good enough for my situation, so I came up with this solution which basically breaks down the SQL string into multiple SQL statements and executes them in one transaction:

string sql = GetMultiStatementSqlString();
string[] sqlStatements = sql.Split(new[] { ';' }, StringSplitOptions.RemoveEmptyEntries);
using (OleDbConnection conn = new OleDbConnection(connStr))
{
    conn.Open();
    OleDbTransaction transaction = conn.BeginTransaction();
    foreach (string statement in sqlStatements)
    {
        using (OleDbCommand cmd = new OleDbCommand(statement, conn, transaction))
        {
            cmd.ExecuteNonQuery();
        }
    }
    transaction.Commit();
}

Hope it helps someone.

Execute multiple queries

4 Answers4

Linked