SQL Script Executer or Reader Executes Microsoft SQL Scripts

Introduction

This article is about an SQL Script Reader or Executer that executes the scripts which are already generated and you want to execute them. Just change the path where your scripts reside.

Background

If you have some generated scripts and you want to execute them, then modify those scripts.

Using the Code

Execute the scripts which are already generated and you want to execute them. Just change the path where your scripts reside. If you have multiple scripts and want to execute them all at the same time, then use array which contains the path of your directory and name of the scripts:

using System;
using System.Data.SqlClient;
using System.IO;
using System.Data;
using System.Collections.Generic;
using System.Text;
using System.ComponentModel;
using System.Drawing;
using System.Windows.Forms;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Nmo;
using Microsoft.SqlServer.Management.Smo.Agent;

namespace SQL_ScriptReader
{
    public class scriptReader
    {
	public scriptReader()
	{ 
   	    //SQL script reader default constructor
	}
	// the below method requires only connection string 
	// this is understood that script has no error and has same column 
         // name and data type
	public void ReadScript(string ConnectionString)
	{
	    string sqlConnectionString = ConnectionString;
	    //Directory where the script reside
		
	    FileInfo file = new FileInfo(@"d:\script.txt");
	    // this string opens and reads the script from start to end
	    // if your script is too long then use any collection or any other thing 
             //which u want and use method of .Tostring()
	    string script = file.OpenText().ReadToEnd();
	    //below code reside in following name spaces be default Microsoft just 
             //include them
	
	    /* using Microsoft.SqlServer.Management.Smo;
	    using Microsoft.SqlServer.Management.Nmo;
	    using Microsoft.SqlServer.Management.Smo.Agent;
	    */
	    Server server = new Server();
	    server.ConnectionContext.ConnectionString = sqlConnectionString;
			
	    server.ConnectionContext.ExecuteNonQuery(script);
	    // 
	    // if your script has key word like \n and 
             // you want to not add in your data base 
             // then use ExecutionTypes.QuotedIdentifierOn
	    // otherwise off

	    /*server.ConnectionContext.ExecuteNonQuery
               (script,Microsoft.SqlServer.Management.Common.ExecutionTypes.
               QuotedIdentifierOn); 
             */ 
	}
    }
}

I have not used try catch block. You can use it for your code efficiency.

History

• 10^th September, 2007: Initial post