How to Execute an .SQL Script File Using C#

How to execute an .SQL script file using c#

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);
    }
}

Execute sql file on SQL Server using C#

This depends on what sort of files they are. If, for example, they only contain actual T-SQL commands (and aren't batch files that you'd run in, say, SSMS, which would contain a batch separator like GO), then you just need to create a connection, a command, then read the contents of the file and use that to populate the CommandText property of the command.

For example:

void ExecuteFile(string connectionString, string fileName)
{
    using(SqlConnection conn = new SqlConnection(connectionString))
    {
        string data = System.IO.File.ReadAllText(fileName);

        conn.Open();

        using(SqlCommand cmd = conn.CreateCommand())
        {
            cmd.CommandText = data;
            cmd.ExecuteNonQuery();
        }
    }
}

If it's a batch file, you'll need to split the file into individual batches and process those individually. The simplest method is just to use string.Split, but bear in mind that it won't respect SQL parsing rules when it splits (for example, if GO appears within a SQL statement, it's going to split the command up into two batches, which will obviously fail).

More generally, you can see what you'd need to do here by modifying the code in this way:

string[] batches = SplitBatches(System.IO.File.ReadAllText(fileName));

conn.Open();

using(SqlCommand cmd = conn.CreateCommand())
{
    foreach(string batch in batches)
    {
        cmd.CommandText = batch;
        cmd.ExecuteNonQuery();
    }
}

The implementation of a function called SplitBatches is up to you.

How to run a sql script using C#

Have a look at

• Run a .sql script file in C#
• How to execute an .SQL script file
  using c#

Execute sql script on SQL server using C#

You likely are, you need the assemblies that are included with the SDK that comes with SQL server. Be sure you installed the SDK when you installed SQL server.

(screenshot taken from a random google image search, the highlighted item is what you need)

By default they are located at a path similar to C:\Program Files (x86)\Microsoft SQL Server\100\SDK\Assemblies The version number may be different for the version of SQL server you have installed.

How to run .sql script using C#, also need to pass values for parameters that are declared in .sql file?

I'm assuming there is some reason you're not just using a stored procedure for this and instead loading this from a file each time? That's really the way to do this if you're going to keep calling the same bit of code since you can pass the city as a parameter.

Anyway, you could just modify the SQL file and replace 'Berlin' with '{0}', then just do this:

string value = "Berlin"
script = string.Format(script, value); 

Or, just use the .Replace method:

script = script.Replace("Berlin", "New Value");

To add/use it as a stored procedure, you'd run a script in something like SQL Server Management Studio that looks like this:

CREATE PROCEDURE AddStuffIfCityExists
   @city char(10)
AS
BEGIN
   SET NOCOUNT ON;
   IF EXISTS( SELECT * FROM Customer where city = @city)
   BEGIN
       --some stuff to do with the record---
   END
END
GO

You can call it from your code like this:

using (SqlConnection conn = new SqlConnection(sqlConnectionString))
{
    using (var cmd = new SqlCommand("AddStuffIfCityExists", conn))
    {
        cmd.CommandType = System.Data.CommandType.StoredProcedure;
        cmd.Parameters.Add(new SqlParameter("@city", "Berlin"));
        cmd.ExecuteNonQuery();
    }
}

Execute sql file on a SQL Server using C#

This is how we do it:

    protected virtual void ExecuteScript(SqlConnection connection, string script)
    {
        string[] commandTextArray = System.Text.RegularExpressions.Regex.Split(script, "\r\n[\t ]*GO");

        SqlCommand _cmd = new SqlCommand(String.Empty, connection);

        foreach (string commandText in commandTextArray)
        {
            if (commandText.Trim() == string.Empty) continue;
            if ((commandText.Length >= 3) && (commandText.Substring(0, 3).ToUpper() == "USE"))
            {
                throw new Exception("Create-script contains USE-statement. Please provide non-database specific create-scripts!");
            }

            _cmd.CommandText = commandText;
            _cmd.ExecuteNonQuery();
        }

    }

Load the contents of your script using some file-reading function.

---

Related Topics