CODEWITHSUNDEEP
c#sql
DaBradley
DaBradley

Reputation: 43

How to run sql script in c#

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!!

Upvotes: 0

Views: 6556

Answers (4)

NocFenix
NocFenix

Reputation: 701

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).

Upvotes: 1

keshav.bahadoor
keshav.bahadoor

Reputation: 1896

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

  1. Try running your script file directly using SQL Management Studio. This can pinpoint errors in your script itself.
  2. 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.

Upvotes: 3

Novin.Kh
Novin.Kh

Reputation: 129

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.

Upvotes: 0

ntl
ntl

Reputation: 1299

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.

Upvotes: 1

Related Questions