Click here to Skip to main content
Click here to Skip to main content

SQL Script Executer or Reader Executes Microsoft SQL Scripts

, 10 Sep 2007 CPOL
Rate this:
Please Sign up or sign in to vote.
SQL Script Executer or Reader executes the scripts which are already generated and you want to execute them. Just change the path where your scripts reside.

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

  • 10th September, 2007: Initial post

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

idreeskhan
Web Developer
Pakistan Pakistan
I'm from Pakistan and have two years of experience in C# and Asp.net in visual stdio 2005 ,2008, 2010 and SQL Server 2000/2005/2008

Comments and Discussions

 
Generalunable to get namespace Pinmemberwinheart14-Feb-08 22:15 
GeneralRe: unable to get namespace Pinmemberidreeskhan2-Apr-08 22:20 
GeneralADO.NET Pinmemberjw12310-Sep-07 3:45 
Hi,
 
just a suggestion: I think you should better use ADO.NET instead of Sql Server SMO. There are classes like SqlConnection and SqlCommand which you could use. These classes are included in the .NET Framework. I'm not sure if the Microsoft.SqlServer.* Assemblies are included in the Framework, so you'll probably need an installed Version of SQL-Server on every client that uses your code.
 
Regards
jan
AnswerRe: ADO.NET Pinmemberidreeskhan10-Sep-07 18:56 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web02 | 2.8.141015.1 | Last Updated 10 Sep 2007
Article Copyright 2007 by idreeskhan
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid