1

I have a method in my C# code that executes an SQL script. If all the values in the script are hard coded, it works fine. But if I try to call a script that uses variables in the form $(varName), I have no idea how to substitute those in my code.

How would I go about executing a sql script while passing it all of its variables that are in the above form?

I am trying to replicate the -v flag for the SQLCMD Batch Command if anyone has experience with that.

Thank you,

Code:

public static bool executeSqlScript(string scriptPath, string serverName, string databaseName)
{
    SqlConnection connection = null;
    try
    {
        string connectionString = @"Server=" + serverName + ";Database=" + databaseName + ";Integrated Security=true";
        string scriptText = System.IO.File.ReadAllText(scriptPath);

        connection = new SqlConnection(connectionString); //was declared above
        SqlCommand command = new SqlCommand(scriptText, connection);
        connection.Open();
        command.ExecuteNonQuery();
        connection.Close();
        return true;
    }
    catch (Exception e)
    {
        connection.Close();
        Console.WriteLine(e);
        return false;
    }
}
Arobbins530
  • 51
  • 7
  • 2
    See [this question](http://stackoverflow.com/a/26617517/111266) – Joe Enos Jul 26 '16 at 20:18
  • So basically there is no good way to do it other than just running SQLCMD in a new cmd process? – Arobbins530 Jul 26 '16 at 20:25
  • maybe I misunderstood the question but isn't it simple parameterized sql?? If it is then you haven't google hard enough – Steve Jul 26 '16 at 20:30
  • I can't think of anything good, but...You could change your scripts to use dynamic SQL. You could use a templating engine like [T4](https://msdn.microsoft.com/en-us/library/bb126445.aspx) or [Handlebars](https://github.com/rexm/Handlebars.Net) to build a final script. Or depending on what you're actually using the variables for, try to find a solution that doesn't require injecting any variables. – Joe Enos Jul 26 '16 at 20:44

0 Answers0