单个命令对象，用于执行多个SQL查询 [英] Single Command Object for execute multiple SQL Query

问题描述

我有一种方法，其中我使用不同的Command对象来执行不同的SQL语句.目前，我正在使用以下代码块-

I have a method where i am using different Command objects to execute different SQL statements. Currently i am using the following code block-

private static Voucher CreateEntity(Voucher voucher)
    {
        OleDbConnection Connection = new OleDbConnection(voucher.ConnectionString);

        OleDbCommand cmdInsert1 = new OleDbCommand("INSERT INTO table1(Field1, Field2,..) VALUES(Value1,Value2,..)", Connection);

        OleDbCommand cmdInsert2 = new OleDbCommand("INSERT INTO table2(Field1, Field2,..) VALUES(Value1,Value2,..)", Connection);

        OleDbCommand cmdLastId = new OleDbCommand("SELECT MAX(Field1) AS LastId FROM Table1", Connection);

        OleDbTransaction transaction = null;

        try
        {
            Connection.Open();
            transaction = Connection.BeginTransaction();

            cmdInsert1.Transaction = transaction;
            cmdInsert2.Transaction = transaction;
            cmdLastId.Transaction = transaction;

            cmdInsert1.ExecuteNonQuery();
            cmdInsert2.ExecuteNonQuery();

            object LastId = cmdLastId.ExecuteScalar();

            transaction.Commit();
            Connection.Close();
            //voucher.MarkOld();
            //return voucher;
        }
        catch (Exception ex)
        {
            if (transaction != null)
            {
                transaction.Rollback();
            }
            throw ex;
        }
    }

我的问题是，我可以在不声明多个Command对象的情况下在此处使用单个Command对象吗?特别是在使用Transaction对象时?

例如，cmdInsert.CommandText = sqlString1，cmdInsert.CommandText = sqlString2，..如果这样做，是否存在性能问题?

问候
Saumitra Kumar Paul

My question is, can i use single Command object here without declaring multiple Command objects? Specially, when using Transaction object?

For example, cmdInsert.CommandText=sqlString1, cmdInsert.CommandText=sqlString2,.. If I do so, is there any performance problem?

Regards
Saumitra Kumar Paul

推荐答案

Saumitra，

您可以使用分号;"分隔来执行多个查询.

如下更新代码.

Hi Saumitra,

You can execute multiple query by separating with semicolon ";".

Update your code as below.

private static Voucher CreateEntity(Voucher voucher)
    {
        OleDbConnection Connection = new OleDbConnection(voucher.ConnectionString);
        string query = "INSERT INTO table1(Field1, Field2,..) VALUES(Value1,Value2,..);INSERT INTO table2(Field1, Field2,..) VALUES(Value1,Value2,..);SELECT MAX(Field1) AS LastId FROM Table1;";        
        OleDbCommand cmd = new OleDbCommand(query, Connection);
 
        OleDbTransaction transaction = null;
 
        try
        {
            Connection.Open();
            transaction = Connection.BeginTransaction();            
            cmd.Transaction = transaction;             
            object LastId = cmd.ExecuteScalar(); 
            transaction.Commit();
            Connection.Close();
            //voucher.MarkOld();
            //return voucher;
        }
        catch (Exception ex)
        {
            if (transaction != null)
            {
                transaction.Rollback();
            }
            throw ex;
        }
    }

希望能为您提供帮助.

Hope thi will help you.

这篇关于单个命令对象，用于执行多个SQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！