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

Execute Sql Script file in Asp.net C# [Database generate]

Overview: This article explains how to execute SQL script file in C# .i.e here in our Asp.net Webform on button click event will execute the SQL Script file and generate database .i.e Tables, Data in MS SQL server.

To create SQL Database in Asp.net is quite difficult, Here will see how to create SQL Database or how to  execute SQL Script File in asp.net c#.

First we need to import some DLL

  1. Microsoft.SqlServer.ConnectionInfo.dll
  2. Microsoft.SqlServer.Smo.dll

These two DLL we have to import for executing SQL script file from our Asp.net Web Application.

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 dbName=txtdbname.text;
  string strCreatecmd = "create database " + dbName + "";
  SqlCommand cmd = new SqlCommand(strCreatecmd, con1);
  con1.Open();
  cmd.ExecuteNonQuery();
  con1.Close();
//  Code to execute SQL script file .i.e.(create tables / storedprocedure /views on Ms-SQL)
 
// generatescript.sql is sql script generated
// placed under Add_data folder in my application
 
    FileInfo file = new FileInfo(Server.MapPath("App_Datageneratescript.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 your windows i.e. at C/programfiles/sqlserver/assembly/…
  • Your Database Script contains `GO` statement, if not using smo.dll it will throw an error.

Hope you enjoyed this tutorial. If you have any recommendations, please let us know what you think in the comment section below! See you again next time!

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++ ; }


One comment on “Execute Sql Script file in Asp.net C# [Database generate]”


Post Comment


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