当前位置：文江博客话题详情

执行多个查询

发布于 2024-08-30 15:07:43 字数 147 浏览 3 评论 0原文

我正在使用 OleDB 在 C# 中执行查询，

有什么方法可以在一个命令语句中执行多个查询吗？

我尝试用分号 (;) 将它们分开，但它给出错误“在末尾找到字符”

我必须一次执行数百个查询。

编辑：我正在执行插入语句。

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

怕倦 2024-09-06 15:07:43

无法在一个 OleDbCommand。如果可能，请创建一个存储过程，否则您将不得不坚持在服务器上触发许多 OleDbCommand。

但值得注意的是，连接池已启用默认情况下OleDbConnection：

当您使用 .NET Framework 数据时
OLE DB 提供程序，您没有
启用连接池，因为
提供商管理此
自动。

编辑：

尝试这样的操作：

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'

根据您连接到的 OleDb 提供程序，您也许可以使用它。但要注意，无论如何，它可能会像一条一条插入记录一样慢。

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.

回复收藏 0 原文

〆一缕阳光ご 2024-09-06 15:07:43

只需使用 GO（对批次进行分组）和冒号来对它们进行批处理即可分隔批次内的查询。确保冒号周围有空格。您需要将此 SQL 提交到 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

示例取自 MSDN。

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.

回复收藏 0 原文

沦落红尘 2024-09-06 15:07:43

使用sp_executesql。

请参阅我在另一个问题中的回答，其中包含使用 sp_executesql 批量发送 SQL 查询的示例。

回复收藏 0 原文

时间你老了 2024-09-06 15:07:43

我想在我正在处理的项目中使用 OleDB 在 Access 数据库中执行多个 SQL 语句，但我找不到任何适合我的情况的东西，所以我想出了这个解决方案它基本上将 SQL 字符串分解为多个 SQL 语句并在一个事务中执行它们：

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();
}

希望它对某人有帮助。

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.

回复收藏 0 原文

~没有更多了~