c# generating INSERT script with data from database

Question

I try to generate a script which is contains datas in INSERT script forms, I try to use a reference (see below) to do it

using Microsoft.SqlServer.Management.Smo;

with this code draft

Server serv = new Server(db);
Database simba = serv.Databases[dbname];
string script = "";

ScriptingOptions so = new ScriptingOptions()
{
    ScriptData = true,
    ScriptSchema = false,
    ScriptDrops = false
};

foreach (Table tb in simba.Tables)
{
    if (tables.Contains(tb.Name))
    {
        var sc = tb.Script(so);
        foreach (var s in sc)
            script += s;

    }
    using (StreamWriter writer = new StreamWriter(file))
    {
        foreach (string tab in tables)
        writer.WriteLine(script);
    }
}

but this code get an error on

var sc = tb.Script(so);

which is

Microsoft.SqlServer.Management.Smo.FailedOperationException

thanks for all reply

@bugfinder I think they are trying to generate a string with scripts, not run them. Have you tried explicitly typing the string? — Jacob H
– Jacob H, Commented Mar 12, 2018 at 14:07
@Crowcoder Microsoft.SqlServer.Management.Smo.FailedOperationException: Cette méthode ne prend pas en charge le script de données. à Microsoft.SqlServer.Management.Smo.SqlSmoObject.ScriptImpl(ScriptingOptions so) à Microsoft.SqlServer.Management.Smo.TableViewTableTypeBase.Script(ScriptingOptions scriptingOptions) à generateScriptSQL.Program.Main(String[] args) dans c:\users\cad\Source\Repos\generateScriptSQL\generateScriptSQL\Program.cs:ligne 84 — christopher achard
– christopher achard, Commented Mar 12, 2018 at 14:12
@JacobH do you mean without generating? i can use the publisher wizard directly on mssql but i try to automatisate my task — christopher achard
– christopher achard, Commented Mar 12, 2018 at 14:15

Zaki Mohammed · Accepted Answer · 2018-03-12 14:59:39Z

I have this code and it is working fine try to use it

var report   = string.Empty;
var fileName = Server.MapPath(Constants.BACKUP_FILE_NAME);

var server      = new Server(new ServerConnection(new SqlConnection(Constants.BACKUP_CONNECTION_STRING)));
var options     = new ScriptingOptions();
var databases   = server.Databases[Constants.BACKUP_DATABASE_NAME];                    

options.FileName                = fileName;
options.EnforceScriptingOptions = true;
options.WithDependencies        = true;
options.IncludeHeaders          = true;
options.ScriptDrops             = false;
options.AppendToFile            = true;
options.ScriptSchema            = true;
options.ScriptData              = true;
options.Indexes                 = true;

report = "<h4>Table Scripts</h4>";
foreach (var table in Constants.BACKUP_TABLES)
{
    databases.Tables[table, Constants.BACKUP_SCHEMA_NAME].EnumScript(options);                        
    report += "Script Generated: " + table + "<br>";
}

The "Constants" is my class to hold the constant values like file name, db etc and I am generating script for limited tables so for that reason not doing "simba.Tables" like you have done in your code; you can surely do that if you want every table scripts to be generated. So this code generates script and store it to specified file.

Hope it helps

christopher achard · Accepted Answer · 2018-03-12 16:26:50Z

3

Thanks @Zaki Mohammed,

Your code helped me a lot,

I just modify a bit for my case and it works perfectly,

            Server serv = new Server(db);
            Database simba = serv.Databases[dbname];
            Scripter scripter = new Scripter(serv);
            scripter.Options.FileName = "InsertIntoScript.sql";
            scripter.Options.EnforceScriptingOptions = true;
            scripter.Options.WithDependencies = false;
            scripter.Options.IncludeHeaders = true;
            scripter.Options.ScriptDrops = false;
            scripter.Options.AppendToFile = true;
            scripter.Options.ScriptSchema = false;
            scripter.Options.ScriptData = true;
            scripter.Options.Indexes = false;

            string script = "";
            foreach (Table tb in simba.Tables)
            {
                if (tables.Contains(tb.Name))
                {
                    IEnumerable<string> sc = scripter.EnumScript(new Urn[] { tb.Urn });
                    foreach (var s in sc)
                        script += s;
                }
            }

answered Mar 12, 2018 at 16:26

christopher achard

731 silver badge8 bronze badges

1 Comment

Derek Over a year ago

NOTE: the script += stuff can be very slow with large data. Using the following got me from over three minutes to about 7 seconds. StringBuilder script = new StringBuilder(); ... script.Append(s);

Punit Poshiya · Accepted Answer · 2024-01-13 07:05:48Z

    static void Main(string[] args)
    {
        string serverName = @"xxxxx\SQLEXPRESS";
        string databaseName = "databaseName";
        string outputPath = "C:\\ScriptFile1.sql";
        string connectionString = $"Server={serverName};Database={databaseName};Integrated Security=true;";

        try
        {
            GenerateInsertScript(connectionString, outputPath);
            Console.WriteLine($"Insert script generated successfully: {outputPath}");
            Console.ReadKey();
        }
        catch (Exception ex)
        {
            Console.WriteLine($"Error: {ex.Message}");
        }
    }
    static void GenerateInsertScript(string connectionString, string outputPath)
    {
        SqlConnection connection = new SqlConnection(connectionString + ";MultipleActiveResultSets=True");
        connection.Open();

        using (StreamWriter writer = new StreamWriter(outputPath))
        {
            SqlCommand command = new SqlCommand("SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'", connection);
            SqlDataReader reader = command.ExecuteReader();

            while (reader.Read())
            {
                string tableName = reader.GetString(0);
                GenerateInsertStatements(connection, tableName, writer);
            }
        }
    }
    static void GenerateInsertStatements(SqlConnection connection, string tableName, StreamWriter writer)
    {
        SqlCommand selectCommand = new SqlCommand($"SELECT * FROM {tableName}", connection);
        SqlDataReader dataReader = selectCommand.ExecuteReader();



        if (dataReader.HasRows)
        {
            StringBuilder columns = new StringBuilder();
            StringBuilder values = new StringBuilder();
            for (int i = 0; i < dataReader.FieldCount; i++)
            {
                columns.Append($"{dataReader.GetName(i)}, ");
                values.Append($"@{dataReader.GetName(i)}, ");
            }

            columns.Length -= 2; // Remove the trailing comma and space
            values.Length -= 2; // Remove the trailing comma and space


            writer.WriteLine($"-- Insert statements for {tableName}");
            writer.WriteLine($"INSERT INTO {tableName} ({columns.ToString()}) VALUES");

            while (dataReader.Read())
            {
                writer.Write("(");
                for (int i = 0; i < dataReader.FieldCount; i++)
                {
                    if (dataReader.FieldCount - 1 == i)
                    {
                        writer.Write($"'{EscapeSqlString(dataReader[i].ToString())}' ");
                    }
                    else
                    {
                        writer.Write($"'{EscapeSqlString(dataReader[i].ToString())}', ");
                    }
                }

                writer.WriteLine("),");
            }

        }
        writer.WriteLine();

    }
    static string EscapeSqlString(string input)
    {
        // Implement any necessary SQL string escaping logic here
        return input.Replace("'", "''");
    }

Collectives™ on Stack Overflow

c# generating INSERT script with data from database

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related