CommandType.Text 与 CommandType.StoredProcedure [英] CommandType.Text vs CommandType.StoredProcedure

问题描述

与仅使用文本命令相比，显式使用 StoredProcedure CommandType 有什么好处吗?换句话说，是

Is there any benefit to explicitly using the StoredProcedure CommandType as opposed to just using a Text Command? In other words, is

cmd = new SqlCommand("EXEC StoredProc(@p1, @p2)");
cmd.CommandType = CommandType.Text;
cmd.Parameters.Add("@p1", 1);
cmd.Parameters.Add("@p2", 2);

比不上

cmd = new SqlCommand("StoredProc");
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@p1", 1);
cmd.Parameters.Add("@p2", 2);

修复了错误的复制粘贴作业(再次).此外，问题的重点在于数据访问类.我宁愿能够在一行中传递存储的 proc 名称和参数，而不是每个参数的额外行.

Fixed bad copy paste job (again). Also, the whole point of the question is for a data access class. I'd much rather be able to pass the stored proc name and parameters in one line as opposed to extra lines for each parameter.

推荐答案

一个区别是消息泵的发生方式.

One difference is how message pumping happens.

在我以前工作的地方，我们有许多批处理流程需要通宵运行.其中许多只涉及运行存储过程.我们曾经使用 sql server 作业来安排这些工作，但从它移到了 .Net 程序中调用这些过程.这使我们能够将所有我们的计划任务保存在一个地方，即使是那些与 Sql Server 无关的任务.

Where I used to work we had a number of batch processes that ran over night. Many of them simply involved running a stored procedure. We used to schedule these using sql server jobs, but moved away from it to instead call the procedures from a .Net program. This allowed us to keep all our scheduled tasks in one place, even the ones that had nothing to do with Sql Server.

它还允许我们在调用过程的 .Net 程序中构建更好的日志记录功能，以便所有通宵进程的日志记录保持一致.存储过程将使用 sql print 和 raiserror 函数，.Net 程序将接收并记录这些函数.我们了解到，CommandType.StoredProcedure 将始终将这些消息缓冲成大约 50 条的批次..Net 代码在过程完成或刷新之前不会看到任何日志事件缓冲区，无论您在连接上设置了什么选项或在 sql 中做了什么.CommandType.Text 为我们解决了这个问题.

It also allowed us to build better logging functionality into the .Net program that calls the procedures, so that the logging from all of the overnight processes was consistent. The stored procedures would use the sql print and raiserror functions, and the .Net program will receive and log those. What we learned was that CommandType.StoredProcedure would always buffer these messages into batches of about 50. The .Net code wouldn't see any log events until the procedure finished or flushed the buffer, no matter what options you set on the connection or what you did in your sql. CommandType.Text fixed this for us.

作为一个附带问题，我会在您的查询参数中使用显式类型.在某些情况下，让 .Net 尝试推断您的参数类型可能会导致问题.

As a side issue, I'd use explicit types with your query parameters. Letting .Net try to infer your parameter types can cause issues in some situations.

这篇关于CommandType.Text 与 CommandType.StoredProcedure的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！