Executing SQL Script file from C# ASP.Net

Vishal agrawal

11y24.3k.NET

SIR,

I WANT TO SEND SMS THROUGH MY ASP.NET C# WEB PAGE.

I hAVE CREATED A .SQL SCRIPT FILE FOR SENDING SMS.

I HAVE TO RUN A THAT FILE BY GIVING FOLLOWING PARAMETER

sqlplus me/me@database @c:\script.sql 9574000589 'HELLO'

SO CAN I GET AND EXCUTE THE FILE.

REGARDS,

vISHAL

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Upendra Pratap Shahi11y ago
Accepted answer
Hello Vishal,

Kindly use below code snippt-

These two DLL you have to import for executing SQL script file from your application
Microsoft.SqlServer.ConnectionInfo.dll
Microsoft.SqlServer.Smo.dll
So that it iterates SQL scripts in a directory and executes them with ConnectionContext.ExecuteNonQuery.

1-
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;
class test
{
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();
}

}

2-

string sqlConnectionString = ConfigurationManager.ConnectionStrings["myconnectionstring"].ToString();
FileInfo file = new FileInfo("C:\\script.sql");
string script = file.OpenText().ReadToEnd();
SqlConnection conn = new SqlConnection(sqlConnectionString);
SqlCommand command = new SqlCommand(script, conn);
command.Connection.Open();
command.ExecuteNonQuery();
command.Connection.Close();

and also read below article-

http://www.c-sharpcorner.com/Blogs/4419/

Marked the answer as Accepted Answer if this work for you, it helps in best discussion.
0
Nanhe Siddique11y ago
hi Vishal

read this link

https://social.msdn.microsoft.com/Forums/en-US/43e8bc3a-1132-453b-b950-09427e970f31/run-a-sql-script-file-in-c?forum=adodotnetdataproviders

it may help you
0

Manoj Bhoir11y ago

Hi,

You need to specify file name and then first read it's content in a string and then execute CommandText command. Here is the sample code :

using System.Data.SqlClient;
using System.IO; 
using Microsoft.SqlServer.Management.Common; 
using Microsoft.SqlServer.Management.Smo;   
namespace ConsoleApplication1 
{     
   class Program     
   {         
      static void Main(string[] args)         
      {             
         string sqlConnectionString = "Data Source=(local);                  
         Initial Catalog=AdventureWorks;Integrated Security=True";             
         FileInfo file = new FileInfo("C:\\myscript.sql");              
         string script = file.OpenText().ReadToEnd();              
         SqlConnection conn = new SqlConnection(sqlConnectionString);              
         Server server = new Server(new ServerConnection(conn));              
         server.ConnectionContext.ExecuteNonQuery(script);         
      }     
   } 
}

For more details check these links :

http://stackoverflow.com/questions/1190809/how-do-i-run-my-sql-script-file-through-ado-net

http://forums.asp.net/t/1619985.aspx?Running+SQL+script+using+c+