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.
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 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
Post Comment
Your email address will not be published. Required fields are marked *