0

My goal is trying to install stored procedures that are in .sql files. After doing some research, everyone recommended that my program reads in the .sql file into a string and create a command object from the string. 

using System.Data.SqlClient;

public partial class ExecuteScript 
{
    static void Main(string[] args)
    {
        string sqlConnectionString = [connection string];
        string script = File.ReadAllText([.sql file path]);
        SqlConnection conn = new SqlConnection(sqlConnectionString);
        Server server = new Server(new ServerConnection(conn));
        server.ConnectionContext.ExecuteNonQuery(script);
    }
}

Here are some references:

  1. How to execute an .SQL script file using c#
  2. https://social.msdn.microsoft.com/Forums/en-US/43e8bc3a-1132-453b-b950-09427e970f31/run-a-sql-script-file-in-c?forum=adodotnetdataproviders

It is interesting that sqlcmd.exe takes in .sql file but SqlClient in .NET does not. Does anyone know why or how to? The reason I do not want to read in .sql file as a string is that all my stored procedures have comments at the top and reading them into a string makes the entire procedure into a sql comment. I understand I can fix this various ways but I was hoping to see if there is a easier way. I ended up doing the following but please enlighten me! 

ProcessStartInfo startInfo = new ProcessStartInfo();
startInfo.Arguments = "-E -d [database] -S [server\\instance] -i " + storedProcFullPath; 
startInfo.FileName = "sqlcmd.exe";
Process.Start(startInfo);

Thanks!

Community
  • 1
  • 1
Chris Hong
  • 51
  • 5
  • 1
    Q: sqlcmd.exe takes in .sql file but SqlClient in .NET does not. A: Apples and Oranges - they're two completely separate things! It's also worth noting that "GO" is a "sqlcmd.exe thing" - it is *not* understood in SQL you might invoke from .Net (or Java JDBC, for that matter). – paulsm4 Aug 18 '15 at 17:12
  • Q: Were you able to read your .sql file into a string, and successfully use the string to create your stored procedure? As I mentioned below, the problem was likely "ReadAllText()". You should be able to accomplish *exactly* what you want - *without* spawning an external program - by using *different* APIs (like ReadAllBytes()/GetString()", for example). – paulsm4 Aug 18 '15 at 19:23
  • I was hoping to just specify file path if that was possible. I was surprised that I couldn't in .NET. – Chris Hong Aug 18 '15 at 19:39
  • I apologize if my question is misleading. I am hoping somebody can answer why can't it be like server.ConnectionContext.ExecuteNonQuery(path to the .sql file)? – Chris Hong Aug 18 '15 at 19:45
  • 1
    Q: Why didn't Microsoft write the API in a way I think would be really convenient for me? A: 1) Because: they didn't :). 2) Because you can easily write a 2-line function to accomplish exactly the same thing. – paulsm4 Aug 18 '15 at 19:50
  • Thanks. It would have been more intuitive if Microsoft built it that way.. – Chris Hong Aug 18 '15 at 19:55

1 Answers1

1

Of course you can read your SQL file in .Net.

The problem you're experiencing is that File.ReadAllText strips your CR/LF when you read it into a string: How to read a file into a string with CR/LF preserved?

SOLUTION:

  1. File.ReadAllBytes() to get all the bytes

  2. Encoding.GetString() to convert the bytes to a string.

EXAMPLE:

byte[] data = File.ReadAllBytes("myfile.sql");
string text = Encoding.UTF8.GetString(data);
Community
  • 1
  • 1
paulsm4
  • 114,292
  • 17
  • 138
  • 190
  • Sure I understand that there is a solution to fix my problem by how to read my file but what I was hoping was to just specify file path. I am surprised that is not an option in .NET. – Chris Hong Aug 18 '15 at 19:40
  • why can't it be like server.ConnectionContext.ExecuteNonQuery(path to the .sql file)? – Chris Hong Aug 18 '15 at 19:42