CODEWITHSUNDEEP
c#sql-server-2008
Veer
Veer

Reputation: 1593

How to run SQL File having large number of lines?

I am having a SQL file(filenameScript) having more than 10k lines of code. Each block of SQL starts with GO and ends with GO. While executing the file from C#, I am getting exception near GO statements. But when I am running the same file in SQL server it is working fine.

con.ConnectionString = sqlconn;
FileInfo file = new FileInfo(filenameScript);
string script = file.OpenText().ReadToEnd();
SqlCommand command = new SqlCommand(script, con);
con.Open();
command.ExecuteNonQuery();
Close();

I think ExecuteNonQuerry is not able to handle so many \n,\r,and \t as the file read is stored in single line with many \n and \r. Is there any other method to do the same? Thanks in advance.

Upvotes: 1

Views: 1542

Answers (1)

Jamiec
Jamiec

Reputation: 136104

No, the issue is not the length of the file, nor is it the existence of \r and/or \n characters. It is because executing SQL using that method can only run a single batch, and the script having GO statements causes multiple batches.

One possibility is to split the text on the keyword GO and execute each individual part:

con.ConnectionString = sqlconn;
var commands = File.ReadAllText(filenameScript).Split(new []{"GO"},StringSplitOption.RemoveEmptyEntries);
con.Open();
foreach(var batch in commands)
{
    SqlCommand command = new SqlCommand(batch, con);
    command.ExecuteNonQuery();
}
con.Close()

Additionally, you could wrap that in a Transaction to ensure all batches are executed atomically.

An alternative is also provided in this SO Question: How do I execute a large SQL script (with GO commands) from c#

Upvotes: 3

Related Questions