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

/ / 1 Comments

Execute Database SQL script in C#: 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. If our script contain DDL, DML then all those DLL, DML gets executed i.e Tables, Views, Inserting data into out MS SQL server.

To create Database from .SQL file or to execute SQL script file in asp.net c#, we need to use some dll. That is First we need to import below mentioned DLL in our Asp.net C# Application.

DLL Required:

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

These two DLL we have to import for executing SQL script file from our Web Application. So that it iterates SQL scripts in a directory and executes them with `ConnectionContext.ExecuteNonQuery`.

C# Code : To run database script

//*
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();
}
//*

Conclusion:  Using Microsoft.SqlServer.Smo.dll, Microsoft.SqlServer.ConnectionInfo.dll we can able to execute .SQL file inside our Asp.net WebApplication. 

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.

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 thank me? 👳 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 *

1 Comments

  1. Anonymous 02/11/2013 06:39:52
    post more nice

    prasanas