是否有可能在一个ADO.NET Command.CommandText财产多个SQL指令？ [英] Is it possible to have multiple SQL instructions in a ADO.NET Command.CommandText property?
问题描述
摘要
我目前正在写一个应用程序，我已经找到了我的SQL指令到项目的参数。
在code，我得到我的查询返回的查询本身的价值。让我们举例说，我的SQL查询是像这样：
选择COL1，COL2，COL3从my_table的
此外， COL1 ， COL2 和 COL3 是从不同的表和迁移的外键进入my_table的。因此，当涉及到的插入，我不得不执行多个INSERT语句从其他表用于上述这些列中获取值。假设如下：
BEGIN TRANSACTION
插入first_table（col_x，col_y）值（'col_x'，'col_y'）
插入second_table（col_z，col_a）值（'col_z'，'col_a'）
插入third_table（col_b，col_c）值（'col_b'，'col_c'）
最后：
插入my_table的（COL1，COL2，COL3，col_v）值（@ COL1，COL2 @，@ COL3，col_v）
承诺
认为这些 COL1 ， COL2 ， COL3 列是自动递增的整数为表第一，第二和第三。
问题
-
我可以写一个复杂的SQL语句到IDbCommand.CommandText属性，而每个指令将用分号隔开（
;）？ -
是否有可能包括
BEGIN TRANSACTION ... COMMIT / ROLLBACK这个的CommandText属性？ -
总之，我能写这样的事情？
使用CNX =新的SqlConnection（CONNSTRING） 使用CMD = cnx.CreateCommand（） cmd.CommandText =BEGIN TRANSACTION_ ＆放大器; 插入first_table（col_x，col_y）VALUES（'col_x'，'col_y'）; _ ＆放大器; 插入second_table（col_z，col_a）VALUES（'col_z'，'col_a'）; _ ＆放大器; 插入third_table（col_b，col_c）VALUES（'col_b'，'col_c'）; _ ＆放大器; 插入my_table的（COL1，COL2，COL3，col_v）值（@ COL1，COL2 @，@ COL3，col_v'）;_ ＆放大器; 承诺 cmd.ExecuterNonQuery（） 结束使用 结束使用
编辑＃1
我以前提到它...马克的回答是，我想走的路，但我可以在我的客户的IT部门内部奇怪的政策，不是因为，除非我用自己的自定义组件，我而避免简单起见。请注意，我upvoted马克的回答呢，因为它是一个可行的解决方案，不管是什么。
在此先感谢您的precious的帮助和时间！这是至关重要的我！
如果您不能使用存储过程的话，或许这code可满足您的要求：
的SqlConnection CNX =新的SqlConnection（CONNSTRING）;
的SqlCommand CMD = cnx.CreateCommand（）;
cnx.Open（）;
字符串complexCommand = string.Concat（
DECLARE @first_table AS表（col1中INT IDENTITY，col_x的varchar（20），col_y的varchar（20））
，DECLARE @second_table AS表（COL2 INT IDENTITY，col_z的varchar（20），col_a的varchar（20））
，DECLARE @third_table AS表（INT COL3 IDENTITY，col_b的varchar（20），col_c的varchar（20））
，宣告@my_table如表（COL1 INT，COL2 INT，COL3 INT，col_v VARCHAR（20））
，宣告@ COL1 INT
，宣告@ COL2 INT
，宣告@ COL3 INT
BEGIN TRAN
BEGIN TRY
插入@first_table（col_x，col_y）VALUES（'col_x'，'col_y'）
SET @ COL1 = @@ IDENTITY
插入@second_table（col_z，col_a）VALUES（'col_z'，'col_a'）
SET @ COL2 = @@ IDENTITY
插入@third_table（col_b，col_c）VALUES（'col_b'，'col_c'）
SET @ COL3 = @@ IDENTITY
插入@my_table（COL1，COL2，COL3，col_v）值（@ COL1，COL2 @，@ COL3，col_v'）
， 承诺
END TRY
BEGIN CATCH
回滚
到底抓）;
cmd.CommandText = complexCommand;
cmd.ExecuteNonReader（）;
我已经根据需要添加，以获得例如code运行表变量，很明显，你可以利用你的永久表。
Summary
I'm currently writing an application where I have located my SQL instructions into a project's parameters.
Within the code, I get the value of my query which returns the query itself. Let's for instance say that my SQL query is like so:
select col1, col2, col3 from my_table
Besides, col1, col2 and col3 are from different tables and are migrated as foreign key into my_table. So, when it comes to the insert, I have to perform multiple INSERT statements to get the values from the other tables for these above-mentioned columns. Let's say as follows:
BEGIN TRANSACTION
insert into first_table (col_x, col_y) values ('col_x', 'col_y')
insert into second_table (col_z, col_a) values ('col_z', 'col_a')
insert into third_table (col_b, col_c) values ('col_b', 'col_c')
and finally:
insert into my_table (col1, col2, col3, col_v) values (@col1, @col2, @col3, 'col_v')
COMMIT
Take it that these col1, col2, col3 columns are auto-increment integers for tables first, second and third.
Questions
Could I write a complex SQL statement into the IDbCommand.CommandText property while each instruction would be separated by a semicolon (
;)?Is it possible to include a
BEGIN TRANSACTION...COMMIT/ROLLBACKinto thisCommandTextproperty?In short, could I write something like this?
Using cnx = New SqlConnection(connString) Using cmd = cnx.CreateCommand() cmd.CommandText = "BEGIN TRANSACTION " _ & "insert into first_table (col_x, col_y) values ('col_x', 'col_y');" _ & "insert into second_table (col_z, col_a) values ('col_z', 'col_a');" _ & "insert into third_table (col_b, col_c) values ('col_b', 'col_c');" _ & "insert into my_table (col1, col2, col3, col_v) values (@col1, @col2, @col3, 'col_v'); " _ & "COMMIT" cmd.ExecuterNonQuery() End Using End Using
EDIT #1
I should have mentioned it before... Mack's answer is the way I would like to go, except that I can't because of strange policies within the IT department of my client, except if I use their custom component which I rather avoid for simplicity sake. Notice that I upvoted Mack's answer anyway since it is a viable solution no matter what.
Thanks in advance for your precious help and time! This is crucial for me!
If you can't use stored procedures then perhaps this code may meet your requirements:
SqlConnection cnx = new SqlConnection(connString);
SqlCommand cmd = cnx.CreateCommand();
cnx.Open();
string complexCommand = string.Concat(
"DECLARE @first_table AS TABLE(col1 int IDENTITY, col_x varchar(20), col_y varchar(20))"
, " DECLARE @second_table AS TABLE(col2 int IDENTITY, col_z varchar(20), col_a varchar(20))"
, " DECLARE @third_table AS TABLE(col3 int IDENTITY, col_b varchar(20), col_c varchar(20))"
, " DECLARE @my_table AS TABLE(col1 int, col2 int, col3 int, col_v varchar(20))"
, " DECLARE @col1 int"
, " DECLARE @col2 int"
, " DECLARE @col3 int"
, " BEGIN TRAN"
, " BEGIN TRY"
, " insert into @first_table (col_x, col_y) values ('col_x', 'col_y')"
, " SET @col1=@@IDENTITY"
, " insert into @second_table (col_z, col_a) values ('col_z', 'col_a')"
, " SET @col2=@@IDENTITY"
, " insert into @third_table (col_b, col_c) values ('col_b', 'col_c')"
, " SET @col3=@@IDENTITY"
, " insert into @my_table(col1, col2, col3, col_v) values (@col1, @col2, @col3, 'col_v')"
, " COMMIT"
, " END TRY"
, " BEGIN CATCH"
, " ROLLBACK"
, " END CATCH");
cmd.CommandText = complexCommand;
cmd.ExecuteNonReader();
I have added table variables as necessary to get the example code running, obviously you can utilise your permanent tables.
这篇关于是否有可能在一个ADO.NET Command.CommandText财产多个SQL指令？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
