.NET / Oracle：如何以编程方式使用 DDL 语句执行脚本

.NET / Oracle: How to execute a script with DDL statements programmatically

提问by user2516186

I want to do some programmatical schema manipulation against an oracle database in C#. Therefore, I struggle with some basic issues.

我想对 C# 中的 oracle 数据库进行一些编程模式操作。因此，我在一些基本问题上挣扎。

The ddl sql statements are located in a script file. I do not want to use sqlplus.exe, but I want to use OracleCommand out of the ODP.NET assemblies (System.Oracle.DataAccess). Here's an example of my script file:

ddl sql 语句位于脚本文件中。我不想使用 sqlplus.exe，但我想使用 ODP.NET 程序集 (System.Oracle.DataAccess) 中的 OracleCommand。这是我的脚本文件的示例：

script.sql:

脚本.sql：

DROP TABLE ABCDEF; 

DROP TABLE GHIJKL;

I want to point out:

我想指出：

• The script contains DDL statements (data definition language)
• The script contains empty lines
• The script contains more than one statement

• 脚本包含 DDL 语句（数据定义语言）
• 脚本包含空行
• 该脚本包含多个语句

The following code should execute my script:

以下代码应该执行我的脚本：

var content = File.ReadAllText("script.sql");

using (var oracleConnection = new OracleConnection(_connectionString))
{
     oracleConnection.Open();

     using (var command = new OracleCommand(content) { Connection = oracleConnection })
     {
          command.CommandType = CommandType.Text;
          command.ExecuteNonQuery();
     }
}

Executing this code, I do get an oracle error:

执行此代码，我确实收到一个 oracle 错误：

Oracle.DataAccess.Client.OracleException: ORA-00911: invalid character

Oracle.DataAccess.Client.OracleException: ORA-00911: 无效字符

Maybe there is some issue with the formatting of the statements, I think. Any hint is appreciated. Thank you.

我想，也许语句的格式存在一些问题。任何提示表示赞赏。谢谢你。

---EDIT---

- -编辑 - -

To summarize my needs in a simple way: I search for an approach to execute any sql/ddl script, that is executable by SQL Plus, programmatically with C#.

以一种简单的方式总结我的需求：我寻找一种方法来执行任何 sql/ddl 脚本，该脚本可由 SQL Plus 以编程方式使用 C# 执行。

采纳答案by GriffeyDog

As @Steve said, the semicolons are causing your error. And you can't wrap the entire file into a single execute immediatecommand, since that can only execute one statement at a time. You will need to parse your file and execute each command on its own, removing the semicolon that delinates commands. Your parsing will have to deal with string literals, as you noted, which in addition to containing semicolons may also contain doubled single quotes ('') within the single quotes (') that begin and end the string literal.

正如@Steve 所说，分号导致了您的错误。而且您不能将整个文件包装成一个execute immediate命令，因为它一次只能执行一个语句。您将需要解析您的文件并单独执行每个命令，删除分隔命令的分号。正如您所指出的，您的解析必须处理字符串文字，除了包含分号外，还可能在开始和结束字符串文字的单引号 (') 中包含双引号 ('')。

回答by Anwer Matter

Simply wrap it inside BEGIN and END and it will work smoothly

只需将它包裹在 BEGIN 和 END 中，它就会顺利运行

var content =string.Format("BEGIN {0} END;", File.ReadAllText("script.sql"));
using (var oracleConnection = new OracleConnection(_connectionString))            
{
  oracleConnection.Open();
  using (var command = new OracleCommand(content) { Connection = oracleConnection })
  {
       command.CommandType = CommandType.Text;
       command.ExecuteNonQuery();
  }
}

回答by Marco Rodrigues

Thanks for the semicolon tip!

感谢您的分号提示！

My final code to run oracle scripts!

我运行 oracle 脚本的最终代码！

1) It accepts: - empty lines / comment ( -- ) lines - Multiline DDl / DML commands ending with ;

1) 它接受： - 空行/注释 ( -- ) 行 - 以 ; 结尾的多行 DDl / DML 命令

2) In case of error it throws an exception with the line number and sql command!

2）如果出现错误，它会抛出带有行号和sql命令的异常！

    public async Task ExecuteScript(string _connectionString, string script)
    {
        using (StringReader sr = new StringReader(script))
        {
            var connection = new OracleConnection(_connectionString);
            connection.Open();

            string sqlCommand = "";
            string sqlLine; byte lineNum = 0;

            while ((sqlLine = sr.ReadLine()) != null)
            {
                sqlLine = sqlLine.Trim(); ++lineNum;

                if (sqlLine.Length > 0 && !sqlLine.StartsWith("--"))
                {
                     sqlCommand += (sqlCommand.Length > 0 ? Environment.NewLine : "") + sqlLine;  // Accept multiline SQL

                    if (sqlCommand.EndsWith(";"))
                    {
                        sqlCommand = sqlCommand.Substring(0, sqlCommand.Length - 1);

                        var command = new OracleCommand(sqlCommand, connection);

                        try
                        {
                            await command.ExecuteNonQueryAsync(); 
                        }
                        catch (OracleException ex)
                        {
                            connection.Close();
                            var e2 = new Exception($"{lineNum} - {sqlCommand} <br/> {ex.Message}");
                            throw e2;
                        }
                    }
                }
            }

            connection.Close();

            return;
        }
    }

回答by Steve

I will try to execute one line at time to see if you have any weird character that blocks the execution. (I am not sure also if you could send all of your commands together on just one call).

我将尝试一次执行一行，看看您是否有任何阻止执行的奇怪字符。（我也不确定您是否可以通过一个电话将所有命令一起发送）。

Also you should remove the semicolonat the end of the lines

您还应该删除行尾的分号

int lineNum = 0;
try
{
    string[] cmdTexts = File.ReadAllLines("script.sql");

    using (var oracleConnection = new OracleConnection(_connectionString))
    {
         oracleConnection.Open();
         OracleCommand command = new OracleCommand();
         command.Connection = oracleConnection;
         foreach(string cmd in cmdTexts)
         {
              lineNum++;
              if(cmd.Trim().Length > 0)
              {
                  if(cmd.EndsWith(";"))
                      cmd = cmd.Substring(0, cmd.Length - 1);

                  command.CommandText = cmd;
                  command.ExecuteNonQuery();
              }
         }
    }
}
catch(Exception ex)
{
    MessageBox.Show("Exception on line: " + lineNum + " message: " + ex.Message);
}