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.
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
2 comments on “How to execute sql script file in asp.net c#”
how could be this done if we use oracleclient instead of sqlclient. Your information is very much appreciated. Thanks in advance!!
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…