c# - Running multiple SQL scripts

Question

I know my question seems a bit broad but I'll try to be as specific and brief as possible. So I am currently working on a program in c# about data archiving. Basically, I'll have a button and when I click it, it should generate new tables into my SQL server database.

My issue is the approach on executing the queries. I already have a list of sql script files (written in SSMS) and I would like to execute them(in sequence) in c#. Should I hardcode the script into my c# program to create the table? It would look something like this:

  private void btnCreateTables_Click(object sender, EventArgs e){
    string query = "IF OBJECT_ID('dbo.AuditCardTypeBenefit_TEST','U') IS NULL ";
    query += "BEGIN ";
    query += "CREATE TABLE[dbo].[AuditCardTypeBenefit_TEST]( "; 
    query += "[AuditID] [decimal](18, 0) IDENTITY(1,1) NOT NULL, ";
    query += "[AuditType] [char](5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, ";
    query += "[CardTypeBenefitID] [decimal](18, 0) NOT NULL, ";

    query += "[EventCode] [varchar](8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, ";
    query += "[CardTypeGroupID] [varchar](5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, ";
    query += "[AgeFrom] [int] NULL, ";
    query += "[AgeTo] [int] NULL, ";

    query += "[Gender] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, ";
    query += "[CreateBy] [nvarchar](128) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, ";
    query += "[CreateDate] [datetime] NOT NULL, ";
    query += "[Status] [char](2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, ";
    query += "[CancelReason] [varchar](250) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, ";
    query += "[LastChangeBy] [nvarchar](128) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, ";
    query += "[LastChangeDate] [datetime] NOT NULL, ";
    query += "[RecordVersion] [timestamp] NOT NULL ";
    query += ") ON [PRIMARY] ";
    query += "END "; }

Or should I execute the script files by using reading the files(calling them) in c# ? I was wondering if it is possible to read them in specific sequence. Also, some SQL statement in the script files are not compatible by reading them in c# such as GO statement for example.

I was thinking of hardcoding in it like the code I just posted above since it can execute the tables generation in the order I want. Problem is imagine if I want to create say,50 tables, then the line of code is going to be really long.

As my idea is to click on the button and then it shall create all the tables in the sql server.

What suggestion would be the best?

EDIT: I tried executing sql script files instead and see if it would work. Here is it:

private void btnCreateTables_Click(object sender, EventArgs e)
        {
 string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;

            try
            {
                using (SqlConnection con = new SqlConnection(constr))
                {
                    FileInfo file = new FileInfo("C:\\Users\\88106221\\Documents\\PromotionEvent_Test.sql");
                    string script = file.OpenText().ReadToEnd();
                    Server server = new Server(new ServerConnection(con));
                    server.ConnectionContext.ExecuteNonQuery(script);
   }

            }
            catch(Exception ex)
            {

                MessageBox.Show(ex.Message);
                textBox1.AppendText(string.Format("{0}", Environment.NewLine));
                textBox1.AppendText(string.Format("{0} MainPage_Load() exception - {1}{2}", _strThisAppName, ex.Message, Environment.NewLine));
                Debug.WriteLine(string.Format("{0} MainPage_Load() exception - {1}", _strThisAppName, ex.Message));

            }

Now it says it could not locate my directory for PromotionEvent_Test.sql. I checked the directory and it's correct. What is the cause?

Answer 1

To run Script Files use this C# Method.

public void CreateDataBase(string FileNameWithPath)
{
    List<string> cmds = new List<string>();
    if (File.Exists(_pFileNameWithPath))
    {
        TextReader tr = new StreamReader(FileNameWithPath);
        string line = "";
        string cmd = "";
        while ((line = tr.ReadLine()) != null)
        {
            if (line.Trim().ToUpper() == "GO")
            {
                cmds.Add(cmd);
                cmd = "";
            }
            else
            {
                cmd += line + "\r\n";
            }
        }
        if (cmd.Length > 0)
        {
            cmds.Add(cmd);
            cmd = "";
        }
        tr.Close();
    }
    if (cmds.Count > 0)
    {
        try
        {
            using (SqlCommand command = new SqlCommand())
            {
                command.Connection = new SqlConnection("ConnectionString To Master");
                command.CommandType = CommandType.Text;
                if (command.Connection.State == System.Data.ConnectionState.Closed)
                {
                    command.Connection.Open();
                }
                for (int i = 0; i < cmds.Count; i++)
                {
                    command.CommandText = cmds[i];
                    command.ExecuteNonQuery();
                }
            }
        }
        catch (Exception exp)
        {
            MessageBox(exp.Message);
        }
    }
}

and use it

CreateDataBase ("C:\1.SQL");
CreateDataBase ("C:\2.SQL");
CreateDataBase ("C:\3.SQL");
CreateDataBase ("C:\4.SQL");
CreateDataBase ("C:\5.SQL");
CreateDataBase ("C:\6.SQL");
CreateDataBase ("C:\7.SQL");
CreateDataBase ("C:\8.SQL");
CreateDataBase ("C:\9.SQL");