The best way to run database script file in C# (edit)

SQL Server

Learn SQL: CREATE DATABASE & CREATE TABLE Operations (sqlshack.com)

SSMS Script objects - SQL Server Management Studio (SSMS) | Microsoft Docs

Top 10 Most Common Database Scripts - Simple Talk (red-gate.com)

Run script: Notice with GO keyword

How to execute sql script file in asp.net c# - Codepedia

How to execute an .SQL script file using c# | Newbedev

Running the SQL Script File By Using The System.Diagnostics.Process Class - CodeProject

PostgreSQL

Run script: Notice with ";" character in each line

Oracle

c# - .NET / Oracle: How to execute a script with DDL statements programmatically - Stack Overflow

sql - Execute oracle DDL script with functions from c# code - Stack Overflow

SELECT * 
FROM sometable 
ORDER BY name 
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

• ExecuteNonQuery
• Use both 'BEGIN END' and 'EXECUTE IMMEDIATE'
• Dapper with CRUD

    connection.Execute(dropTableCustomers);
    connection.Execute(createTableCustomers);
    connection.Execute(insertCustomerMark);
    connection.Execute(insertCustomerJohn);

    transaction.Commit();

Run script: Notice with ";" character in each line

• Oracle.ManagedDataAccess.18.3.0
• Oracle.ManagedDataAccess.EntityFramework.18.3.0

using Oracle.ManagedDataAccess.Client;
using System;
using System.Diagnostics;
using System.IO;
using System.Threading.Tasks;

public void ExecuteScript(string connectionString, string script)
{
    using (StringReader sr = new StringReader(script))
    {
        var connection = new OracleConnection(connectionString);
        connection.Open();
        string sqlCommand = "";
        string sqlLine;
        ushort lineNum = 0;
        try
        {
            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);
                        Debug.WriteLine($"Command.CommandText: {sqlCommand}");
                        try
                        {
                            command.ExecuteNonQuery();
                        }
                        catch (OracleException ex)
                        {
                            Debug.WriteLine(ex.ToString());
                            var ex2 = new Exception($"{lineNum} - {sqlCommand} 
 {ex.Message}");
                            Debug.WriteLine(ex2.ToString());
                            //throw ex2;
                        }
                        finally
                        {
                            sqlCommand = string.Empty;
                        }
                    }
                }
            }
        }
        catch (Exception ex)
        {
            Debug.WriteLine("Exception on line: " + lineNum + ", message: " + ex.Message);
        }
        finally
        {
            connection.Close();
        }
        return;
    }
}

DTO: Chạy nhiều tệp .sql theo thứ tự được định nghĩa trước, đặt trong tệp XML

internal class ScriptFileItem
{
    public int OrderNumber { get; set; }
    public string FilePathRelative { get; set; }
    public ScriptFileItem(int orderNumber, string filePathRelative)
    {
        OrderNumber = orderNumber;
        FilePathRelative = filePathRelative;
    }
}

XML: Tệp XML định nghĩa thứ tự chạy các tệp .sql

<?xml version="1.0" encoding="utf-8" ?>
<Scripts>
 <Script>
 <FilePathRelative>01.sql</FilePathRelative>
 <OrderNumber>1</OrderNumber>
 </Script>
 <Script>
 <FilePathRelative>02.sql</FilePathRelative>
 <OrderNumber>2</OrderNumber>
 </Script>
 <Script>
 <FilePathRelative>03.sql</FilePathRelative>
 <OrderNumber>3</OrderNumber>
 </Script>
</Scripts>

Usage: Cách sử dụng

string folder = Environment.CurrentDirectory + @"\Database\Oracle\";
string xmlFilePath = folder + "DataDefinitionLanguage.xml";
XDocument doc = XDocument.Load(xmlFilePath);
//foreach (var delimiter in doc.Descendants("Scripts").Elements())
//    Console.WriteLine(string.Format("{0}: {1}", delimiter.Name, delimiter.Value));
//foreach (var type in doc.Descendants("Script").Elements())
//    Console.WriteLine(string.Format("{0}: {1}", type.Name, type.Value));
var n = doc.Descendants("Script").Elements().Count();
var length = n / 2;
var lst = new ScriptFileItem[length];
byte i = 0, j = 0, key = 0;
string value = string.Empty;
foreach (var type in doc.Descendants("Script").Elements())
{
    if (type.Name.ToString().Equals($"{nameof(ScriptFileItem.FilePathRelative)}"))
    {
        value = type.Value.ToString();
        i++;
    }
    if (type.Name.ToString().Equals($"{nameof(ScriptFileItem.OrderNumber)}"))
    {
        key = int.Parse(type.Value);
        i++;
    }
    if (i % 2 == 0) lst[j++] = new ScriptFileItem(key, value);
}
string connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["OracleDbContext"].ConnectionString;
OracleDataAccessHelper oracleDataAccessHelper = new OracleDataAccessHelper();
var collection = lst.OrderBy(m => m.OrderNumber).ToList();
foreach (var item in collection)
{
    try
    {
        string path = folder + item.FilePathRelative;
        string script = System.IO.File.ReadAllText(path, Encoding.UTF8);
        //oracleDataAccessHelper.ExecuteScriptAsync(connectionString, script).ConfigureAwait(false).GetAwaiter().GetResult();
        oracleDataAccessHelper.ExecuteScript(connectionString, script);
    }
    catch (Exception ex)
    {
        Debug.WriteLine(ex.ToString());
    }
}

MySQL

Run script: Notice with ";" character in each line