Want a free Ebook & latest Update Subscribe Now
  [email protected]      beta
Donate

How to execute sql script file in asp.net c#

EXECUTE SQL SCRIPT FROM C#

To create SQL Database using Asp.net is quite difficult, Here I this article am writing how to create SQL Database and how to Run the SQL Script File in asp.net. These two DLL you have to import to perform these tasks.

  • Microsoft.SqlServer.ConnectionInfo.dll 
  • Microsoft.SqlServer.Smo.dll.

 

So that it iterates SQL scripts in a directory and executes them with ConnectionContext.ExecuteNonQuery.
Code :

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Data;
using System.Web.Configuration;
using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Smo;
using System.IO;
using System.Xml.Linq;

protected void Page_Load(object sender, EventArgs e)
{
   SqlConnection con1 = new SqlConnection("YOUR CONNECTIONSTRING");
}

protected void btn_Click(object sender, EventArgs e)
{
  string strdbname=txtdbname.text;
  string strCreatecmd = "create database " + strdbname + "";
  SqlCommand cmd = new SqlCommand(strCreatecmd, con1);
  con1.Open();
  cmd.ExecuteNonQuery();
  con1.Close();
  //  Code to execute sql script ie(create tables/storedprocedure/views on ms sqlserver)

 //generatescript.sql is sql script generated and placed under Add_data folder in my application
    FileInfo file = new FileInfo(Server.MapPath("App_Data\generatescript.sql"));
    string strscript = file.OpenText().ReadToEnd();
    string strupdatescript = strscript.Replace("[databaseOldnameWhileSriptgenerate]", strdbname);
    Server server = new Server(new ServerConnection(con1));
    server.ConnectionContext.ExecuteNonQuery(strupdatescript);
    con1.Close();
}

Note:

  • Microsoft.SqlServer.Smo.dll,Microsoft.SqlServer.ConnectionInfo.dll is need
  • You will find these online or check in ur windows ie In c/programfiles/sqlserver/assembly/…
  • Your database script contains “GO” if not using smo.dll it will throw error.


Thanks for reading

Share:    
Satinder Singh

Hi all, am Founder and Author of Codepedia.info, also atypical polyglot programmer from Bombay, India. I have a passion to create, solve, and deploy software applications.
       ForEach ( minute in MyLife ) { myExperience++ ; }


2 comments on “How to execute sql script file in asp.net c#”

  1. sri

    how could be this done if we use oracleclient instead of sqlclient. Your information is very much appreciated. Thanks in advance!!

  2. Free Email Marketing

    Hello,

    This is really interesting take on the concept. I never thought of it that way. I came across this site recently which I think it will be a great use of new ideas and informations. Thanks a lot…


Post Comment


Your email address will not be published. Required fields are marked *