Run Sql Script file in Asp.net C#

EXECUTE SQL Script file in Asp.net C#

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

• 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 databasescript contain "GO" if not using smo.dll it will throw error.