Running SQL scripts in C#


csharp dotnet sql

How to run a bunch of SQL scripts inside a C# program.

A simple problem

I needed to run a directory full of SQL scripts in a SQL Server instance. Here’s a bit of code that:

  1. Reads in any script called *.sql from a directory that does not have the word ’test’ somewhere in it
  2. Uses the Microsoft.SqlServer.Management.Common and Microsoft.SqlServer.Management.Smo
  3. Executes each script in turn using name-based sorting for the order of execution.

Get it done!

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

public bool RestoreFieldDatabaseFromBackup(string connectionString)
{
 List scripts = this._sqlDirectory.GetFiles("*.sql", SearchOption.AllDirectories)
  .Where(s => !s.Name.Contains("test"))
  .Select(s => s.FullName).ToList();

 if (scripts.Count() == 0) return false;

 var connection = new SqlConnection(connectionString);

 try
 {
  for (int i = 0; i < scripts.Count; i++)
  {
   using (var reader = new StreamReader(scripts[i]))
   {
    var server = new Server(new ServerConnection(connection));
    var db = new Database(server, connection.Database);
    string scriptText = reader.ReadToEnd();
    Log.For(this).Debug("Running: " + scripts[i]);
    db.ExecuteNonQuery(scriptText);
   }
  }
 }
 catch (Exception ex)
 {
  Log.For(this).Error(ex);
  return false;
 }
 return true;
}

See Also

🔗 Share on LinkedIn or visit me on Mastodon