Run a SQL Script at runtime of a C# application

In my previous post I explained how to run a SQL Script using Command prompt. It is bit difficult. So I found a way to run the SQL script using the application. I used it on button click. Here is how I did it.

1.  Create your SQL Script.

2. Add the references below to your C# application

using Microsoft.SqlServer.Server;

using Microsoft.SqlServer.Management.Smo;

using Microsoft.SqlServer.Management.Common;

using Microsoft.SqlServer.Management.Sdk.Sfc;

3. Add the path to your SQL Script

string Filename = Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments) + "\\Configuration\\", "Create_Database.sql");

4. Add the code below to read your SQL Script

StreamReader StreamReader;

         StreamReader = new StreamReader(Filename);

string CreateDBQuery = StreamReader.ReadToEnd();

5. Create a connection with server & Execute Query

string tempConnString="Data Source=(local);Integrated Security=True";

 

SqlConnection tmpConn = new SqlConnection(tempConnString);

      ServerConnection svrConn=new ServerConnection(tmpConn);

      Server server = new Server(svrConn);

server.ConnectionContext.ExecuteNonQuery(CreateDBQuery);

 

Here is how my application use this

private void btnCreateDB_Click(object sender, EventArgs e)

{   

string tempConnString="Data Source=(local);Integrated Security=True";       

           

string Filename = Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments) + "\\Configuration\\", "Create_Database.sql");

StreamReader StreamReader;

StreamReader = new StreamReader(Filename);

string CreateDBQuery = StreamReader.ReadToEnd();

 

SqlConnection tmpConn = new SqlConnection(tempConnString);

ServerConnection svrConn=new ServerConnection(tmpConn);

Server server = new Server(svrConn);

server.ConnectionContext.ExecuteNonQuery(CreateDBQuery);

}

When i click the CreateDB button it will create the Database