How to run sql script in c#

Question

I've searching for this and I thought I found the answer on here. this is the code I found to run a sql script through c#:

using System.Data.SqlClient;
using System.IO;
using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Smo;

namespace SeleniumTest2
{
    class CreateSchema
    {
        public void Schema_Create()
        {
            string sqlConnectionString = "connection string here";
            FileInfo file = new FileInfo(@"filepath to script.sql");
            string script = file.OpenText().ReadToEnd();
            SqlConnection conn = new SqlConnection(sqlConnectionString);
            Server server = new Server(new ServerConnection(conn));

            //DOESNTLIKE
            server.ConnectionContext.ExecuteNonQuery(script);


            file.OpenText().Close();
            conn.Close();


        }
    }
}

But I keep getting the following error:

An unhandled exception of type 'Microsoft.SqlServer.Management.Common.ExecutionFailureException' occurred in Microsoft.SqlServer.ConnectionInfo.dll

Additional information: An exception occurred while executing a Transact-SQL statement or batch.

Can anyone tell me how to overcome this error? THANKS!!

How about you enclose that line in a try/catch block and see the exception details? — shree.pat18, Aug 07 '14 at 11:02
Are you sure your SQL account is valid and has all necessary rights? AFAIR this exception is often trigger by privileges errors. — Kamil T, Aug 07 '14 at 11:11
possible duplicate of [How to execute an .SQL script file using c#](http://stackoverflow.com/questions/650098/how-to-execute-an-sql-script-file-using-c-sharp) — Alberto Solano, Aug 07 '14 at 11:14
Another question... Could you show us what the content of your SQL Script file contains? Maybe the query is indeed not valid? — Belogix, Aug 07 '14 at 11:17
No the script is fine. I am able to run it to create the tables in the database. It's saying that one of the tables can't be found or I don't have permissions but the script is being run to create the tables in the first place so obviously it can't be found and I have full permissions — DaBradley, Aug 07 '14 at 13:30

score 3 · Answer 1 · answered Aug 26 '15 at 19:36

This happened to me a couple times. After debugging, there were some errors in my script file itself. The following worked for me:

Try running your script file directly using SQL Management Studio. This can pinpoint errors in your script itself.
Break down the SQL script into smaller files. For some reason this worked for me. Split the file into smaller scripts accordingly. For my particular database creation script, I separated it into a create tables script, a populate tables script, and an add primary and foreign keys script.

My code:

    /// <summary>
    /// Process SQL script with "GO" statements 
    /// </summary>
    /// <param name="script"></param>
    public static void ProcessSQLScriptFile(string script)
    {
        try
        {
            SqlConnection con = new SqlConnection(Properties.Settings.Default.SQLConDefault); // your connection string 
            con.Open(); 
            Server server = new Server(new ServerConnection(con));
            server.ConnectionContext.ExecuteNonQuery(script);
            con.Close();
        }
        catch (SqlException e)
        {
            Console.WriteLine("SQL Exception: " + e.Message);
        }
        catch (Exception e)
        {
            Console.WriteLine("Exception: " + e.Message);
        }
    }

Hope this helps.

score 1 · Answer 2 · answered Aug 07 '14 at 11:21

You may try this method to execute sql (from msdn):

private static void ExecuteCommand(string queryString,
    string connectionString)
{
    using (SqlConnection connection = new SqlConnection(
               connectionString))
    {
        SqlCommand command = new SqlCommand(queryString, connection);
        command.Connection.Open();
        command.ExecuteNonQuery();
    }
}

If you will get an error, check exception details, check if your connection string is valid.

score 1 · Answer 3 · answered May 05 '16 at 18:12

I had this exact same issue. What fixed it for me was to find out the actual error:

public void Schema_Create()
{
    string sqlConnectionString = "connection string here";
    FileInfo file = new FileInfo(@"filepath to script.sql");
    string script = file.OpenText().ReadToEnd();
    SqlConnection conn = new SqlConnection(sqlConnectionString);
    Server server = new Server(new ServerConnection(conn));

    try
    {
        server.ConnectionContext.ExecuteNonQuery(script);           
    }
    catch (Exception ex)
    {
        Console.WriteLine("Error: " + ex.InnerException.Message);
    }

    file.OpenText().Close();
    conn.Close();
}

My issue presented itself in the ex.InnerException.Message, which in my case happened to be that my script was attempting to write a column that already existed on the table (column names must be unique for a given table).

score 0 · Answer 4 · answered Feb 06 '15 at 21:39

0

Change the database path to another drive Because in c drive or in windows drive you don't have permission to create data base

If change the path , your solution is work successful.

answered Feb 06 '15 at 21:39

Novin.Kh

129
2
6

How to run sql script in c#

4 Answers4