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

/ / 0 Comments

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.

 [ads]

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

Thank you for reading, pls keep visiting this blog and share this in your network. Also, I would love to hear your opinions down in the comments.

PS: If you found this content valuable and want to do a favor, then Buy me a coffee

Subscribe to our newsletter

Get the latest and greatest from Codepedia delivered straight to your inbox.


Post Comment

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

0 Comments

  1. Free Email Marketing 06/14/2012 03:10:18
    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...
  2. sri 03/21/2013 13:28:52
    how could be this done if we use oracleclient instead of sqlclient. Your information is very much appreciated. Thanks in advance!!