Making multiple requests
I am using OleDB to do my queries in C #,
Is it possible in any way to execute multiple queries in a single command statement?
I tried to highlight them with colon (;) but it gives the error "Characters found at the end"
I need to execute several hundred requests at the same time.
Edit: I am doing inserts.
a source to share
It is not possible to combine requests within one OleDbCommand
. Make a stored procedure if possible, otherwise you'll have to stick with firing many OleDbCommands on the server.
However, it's worth noting that pooling is enabled OleDbConnection
by default:
When using the .NET Framework Data Provider for OLE DB, you do not have to enable connection pooling because the provider handles 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 are connecting with, you can use it. But beware, it can be as slow as inserting records one at a time anyway.
a source to share
Just run them using GO (groups the package) and colons to separate the requests within the package. Remember to surround the colon with spaces. You need to send 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 from MSDN .
a source to share
Use sp_executesql
.
See my answer in another question where I include a use case sp_executesql
for sending SQL queries in batch.
a source to share
I wanted to execute multiple SQL statements in an Access database using OleDB
for a project I am working on and I couldn't find anything good for my situation, so I came up with this solution which basically splits a 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 this helps someone.
a source to share