c# - 如何将多个 SQL 命令合二为一?
问题描述
是否可以通过将以下查询合并到单个 SQLCommand 中来优化它们?
SqlCommand cmd = new SqlCommand
{
CommandType = CommandType.Text,
CommandText = "DELETE FROM cbu_naslovi WHERE [ID]='" + CurrentID + "'",
Connection = con
};
SqlCommand cmd1 = new SqlCommand
{
CommandType = CommandType.Text,
CommandText = "DELETE FROM cbu_deli WHERE [IDX]='" + CurrentID + "'",
Connection = con
};
cmd.ExecuteNonQuery();
cmd1.ExecuteNonQuery();
编辑:工作解决方案,正如社区答案所建议的那样
SqlCommand cmd = new SqlCommand
{
CommandType = CommandType.Text,
CommandText = "DELETE FROM cbu_naslovi WHERE [ID] = @CurrentID; DELETE FROM cbu_deli WHERE [IDX] = @CurrentID",
Connection = con
};
cmd.Parameters.AddWithValue("@CurrentID", CurrentID);
cmd.ExecuteNonQuery();
解决方案
是的,您可以用分号分隔它们。例如,我有在一次调用中执行以下代码的代码
SET NOCOUNT ON;
DECLARE @decimalDate DECIMAL(12,0);
DECLARE @charDate CHAR(12);
DECLARE @utcDate DATETIMEOFFSET;
DECLARE date_cursor CURSOR FOR SELECT {1} FROM {0} WHERE ISNULL({1},0)!=0;
OPEN date_cursor;
FETCH NEXT FROM date_cursor INTO @decimalDate;
WHILE @@FETCH_STATUS=0
BEGIN
BEGIN TRY SET @charDate=CONVERT(CHAR(12),@decimalDate);
SET @utcDate=SwitchOffset(
CONVERT(DATETIME,'20'
+SUBSTRING(@charDate,1,2)+'-'+SUBSTRING(@charDate,3,2)+'-'
+SUBSTRING(@charDate,5,2)+' '+SUBSTRING(@charDate,7,2)+':'
+SUBSTRING(@charDate,9,2)+':'+SUBSTRING(@charDate,11,2)
,121) AT TIME ZONE '{3}',0);
END
TRY BEGIN CATCH
SET @utcDate=SysUtcDateTime();
END CATCH;
BEGIN
TRY UPDATE {0} SET {2}=@utcDate WHERE CURRENT OF date_cursor;
END TRY
BEGIN CATCH END CATCH;
FETCH NEXT FROM date_cursor INTO @decimalDate;
END;
CLOSE date_cursor;
DEALLOCATE date_cursor;
也有例外。例如,“创建过程”语句必须是块的第一条语句。但是大多数 DML 都可以像这样进行批处理。
推荐阅读
- amazon-web-services - Amazon S3 视频控制栏问题
- docker - 找不到与 scipy docker 匹配的发行版
- javascript - nodejs应用程序中的内存泄漏,关于全局变量的建议
- reactjs - 用计时器反应 useEffect 和 useState 表现得很奇怪
- laravel - Laravel Rest API 使用 Google 登录
- flask - 无法将烧瓶 tesseract 应用程序部署到 heroku
- kotlin - admob 改写广告不显示 kotlin
- spring - 图像未显示在 Spring Boot 应用程序的网页上
- ssas - 在 SSAS 表格模型中编辑 SQL 查询
- python - Conda 安装 python -v 返回回溯