How to execute an .SQL script file using c#

c# execute sql script file with parameters
execute sql script from c# without smo
c# execute sql script with go statements
how to create sql file in c#
c# execute sql batch
how to read data from sql file in c#
c# run sql query
use sql file in c#

I'm sure this question has been answered already, however I was unable to find an answer using the search tool.

Using c# I'd like to run a .sql file. The sql file contains multiple sql statements, some of which are broken over multiple lines. I tried reading in the file and tried executing the file using ODP.NET ... however I don't think ExecuteNonQuery is really designed to do this.

So I tried using sqlplus via spawning a process ... however unless I spawned the process with UseShellExecute set to true sqlplus would hang and never exit. Here's the code that DOESN'T WORK.

Process p = new Process();
p.StartInfo.UseShellExecute = false;
p.StartInfo.RedirectStandardOutput = true;
p.StartInfo.FileName = "sqlplus";
p.StartInfo.Arguments = string.Format("xx/xx@{0} @{1}", in_database, s);
p.StartInfo.CreateNoWindow = true;

bool started = p.Start();
p.WaitForExit();

WaitForExit never returns .... Unless I set UseShellExecute to true. A side effect of UseShellExecute is that you can no capture the redirected output.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Common;
using System.IO;
using System.Data.SqlClient;

public partial class ExcuteScript : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
    string sqlConnectionString = @"Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=ccwebgrity;Data Source=SURAJIT\SQLEXPRESS";

    string script = File.ReadAllText(@"E:\Project Docs\MX462-PD\MX756_ModMappings1.sql");

    SqlConnection conn = new SqlConnection(sqlConnectionString);

    Server server = new Server(new ServerConnection(conn));

    server.ConnectionContext.ExecuteNonQuery(script);
    }
}

How to run sql script using SQL Server Management Studio?, typically contains queries to modify the structure of a relational database -- to insert, delete, update or extract data. you can use SMO (Sql server Management Object) by C#. using the following library to execute this : using Microsoft.SqlServer.Management.Common; using Microsoft.SqlServer.Management.Smo; Sample Code like this : FileInfo file = new FileInfo("ScriptFile.sql"); string script = file.OpenText().ReadToEnd();

I tried this solution with Microsoft.SqlServer.Management but it didn't work well with .NET 4.0 so I wrote another solution using .NET libs framework only.

string script = File.ReadAllText(@"E:\someSqlScript.sql");

// split script on GO command
IEnumerable<string> commandStrings = Regex.Split(script, @"^\s*GO\s*$", RegexOptions.Multiline | RegexOptions.IgnoreCase);

Connection.Open();
foreach (string commandString in commandStrings)
{
    if (commandString.Trim() != "")
    {
        using(var command = new SqlCommand(commandString, Connection))
        {
            command.ExecuteNonQuery();
        }
    }
}     
Connection.Close();

What is SQL? What Opens a SQL? File Format List from WhatIs.com, 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) How to execute an sql script file using C#. This entry was posted in .Net and tagged execute sql script file using C#. Bookmark the permalink.

This Works on Framework 4.0 or Higher. Supports "GO". Also show the error message, line, and sql command.

using System.Data.SqlClient;

        private bool runSqlScriptFile(string pathStoreProceduresFile, string connectionString)
    {
        try
        {
            string script = File.ReadAllText(pathStoreProceduresFile);

            // split script on GO command
            System.Collections.Generic.IEnumerable<string> commandStrings = Regex.Split(script, @"^\s*GO\s*$",
                                     RegexOptions.Multiline | RegexOptions.IgnoreCase);
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();
                foreach (string commandString in commandStrings)
                {
                    if (commandString.Trim() != "")
                    {
                        using (var command = new SqlCommand(commandString, connection))
                        {
                        try
                        {
                            command.ExecuteNonQuery();
                        }
                        catch (SqlException ex)
                        {
                            string spError = commandString.Length > 100 ? commandString.Substring(0, 100) + " ...\n..." : commandString;
                            MessageBox.Show(string.Format("Please check the SqlServer script.\nFile: {0} \nLine: {1} \nError: {2} \nSQL Command: \n{3}", pathStoreProceduresFile, ex.LineNumber, ex.Message, spError), "Warning", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                            return false;
                        }
                    }
                    }
                }
                connection.Close();
            }
        return true;
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message, "Warning", MessageBoxButtons.OK, MessageBoxIcon.Warning);
            return false;
        }
    }

Executing SQL Script file from C# ASP.Net, now you can execute your sql script file using your application c#. script file : https​://www Duration: 7:08 Posted: Nov 21, 2014 GO is not a SQL Command. GO is a keyword used in SQL Server stored procedures that tells the server to execute the SQL that you gave it. You don't need to "GO" when you are executing SQL using SQLCommand; you just need to Execute() it.

Put the command to execute the sql script into a batch file then run the below code

string batchFileName = @"c:\batosql.bat";
string sqlFileName = @"c:\MySqlScripts.sql";
Process proc = new Process();
proc.StartInfo.FileName = batchFileName;
proc.StartInfo.Arguments = sqlFileName;
proc.StartInfo.WindowStyle = ProcessWindowStyle.Hidden;
proc.StartInfo.ErrorDialog = false;
proc.StartInfo.WorkingDirectory = Path.GetDirectoryName(batchFileName);
proc.Start();
proc.WaitForExit();
if ( proc.ExitCode!= 0 )

in the batch file write something like this (sample for sql server)

osql -E -i %1

Run a .sql script file in C - MSDN, Load the script from the file into a string, and set it as the cmdText to an SqlCommand object. Then call the appropriate command method:  Hi I want to know whether in c#.net there is any way to execute a .sql file consisting of multiple insert and update statements separated with keyword GO without needing to add microsoft.sqlserver.management.smo. Need this alternative approach because I am getting some version issues when using microsoft.sqlserver.management.smo dll in SQL2k8 R2.

This works for me:

public void updatedatabase()
{

    SqlConnection conn = new SqlConnection("Data Source=" + txtserver.Text.Trim() + ";Initial Catalog=" + txtdatabase.Text.Trim() + ";User ID=" + txtuserid.Text.Trim() + ";Password=" + txtpwd.Text.Trim() + "");
    try
    {

        conn.Open();

        string script = File.ReadAllText(Server.MapPath("~/Script/DatingDemo.sql"));

        // split script on GO command
        IEnumerable<string> commandStrings = Regex.Split(script, @"^\s*GO\s*$", RegexOptions.Multiline | RegexOptions.IgnoreCase);
        foreach (string commandString in commandStrings)
        {
            if (commandString.Trim() != "")
            {
                new SqlCommand(commandString, conn).ExecuteNonQuery();
            }
        }
        lblmsg.Text = "Database updated successfully.";

    }
    catch (SqlException er)
    {
        lblmsg.Text = er.Message;
        lblmsg.ForeColor = Color.Red;
    }
    finally
    {
        conn.Close();
    }
}

C# - Execute Sql Script Using Csharp Application, Execute SQL Script file in asp.net , Using SQL Server Management Objects SMO execute SQL script file, sql script file with Go cmd, run sql  Execute a large SQL script (with GO commands) I need to execute a large set of SQL statements (creating a bunch of tables, views and stored procedures) from within a C# program. These statements need to be separated by GO statements, but SqlCommand.ExecuteNonQuery() does not like GO statements.

I want to execute sql script in c# how can i do this., I have this code to execute a sql script in C#. It works great Smo;. using Microsoft.SqlServer.Management.Common;. if (File.Exists(filePath)). Sometimes we need to run *.sql script from our application for installing data base. SqlCommand is not right for running installation script because installation script consists of DDL and GO command. Here we use smo library for executing *.sql script. For doing above operation we need to add following references Microsoft.SqlServer.ConnectionInfo

Execute SQL Script file Asp.net C#, SqlException with the message "Incorrect syntax near 'GO'" is started. The idea is to split the script with each "GO" and execute the commands  Additionally, I investigated deploying and using C# code-behind files and custom C# assemblies. For more information related to Using C# with U-SQL, see Microsoft's article Extending U-SQL Expressions with User-Code, which contains additional detailed examples.

How to execute sql script from C# – SQLServerCentral, In it we can write C# code and run the code on the fly with all C# language features do a database backup #r "C:\Program Files\Microsoft SQL  SQL Server sqlcmd Examples. Now I will show you a few examples on how to run script files with sqlcmd for different scenarios. I am using a script file with the AdventureWorksDW database which you can download from Microsoft for free at this link: AdventureWorks Databases and Scripts for SQL Server 2016.

Comments
  • Hello Mr. Rich, your question was about Oracle and you accepted a solution that was for sql server ? You changed your DB to sql server ?
  • Great! This solution worked for me for being able to drop and recreate a database, and add tables (via the referenced SQL script file).
  • This method doesn't allow using the "GO" command in your script which is allowed when you run a script from SQL Management Studio or the osql command. msdn.microsoft.com/en-us/library/ms188037.aspx
  • Rn222: I think you've confused ExecuteNonQuery methods, SqlCommand.ExecuteNonQuery won't allow using "GO" commands, however Server.ConnectionContext.ExecuteNonQuery definitely does (I'm using it right now).
  • Note that you need to add references to the project, to Microsoft.SqlServer.ConnectionInfo, Microsoft.SqlServer.Management.Sdk and Microsoft.SqlServer.Smo for this answer to work.
  • To me it didn't work when using .net 4.0/4.5, when referencing 110\SDK\Assemblies The solution I found was changing the app.Config to <startup useLegacyV2RuntimeActivationPolicy="true"> <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5"/> </startup>
  • Exactly. This solution won't even close the file after it's done using it. That could be critical.
  • Use "RegexOptions.Multiline | RegexOptions.IgnoreCase" to match "Go" or "go" cases too.
  • I think the RegexOptions.CultureInvariant flag should be used as well.
  • This is not 100% working: 'GO' may accept numerical parameter.