Click here to Skip to main content
14,331,865 members

WPF SQL Replication Tool (Part 2)

Rate this:
5.00 (13 votes)
Please Sign up or sign in to vote.
5.00 (13 votes)
12 Nov 2015CPOL
In this article, we started from Generate Inserts procedure, then go through how generates inserts SQL files with this procedure and runs these inserts SQL files to import data.

1. Introduction

In last article, we go through how replicate database objects with SQL Server Management Objects. Here we will discuss how export and import data.

When working in a development environment, there will almost certainly be a requirement to script and insert standing SQL data from your production database into your development database environment. This can often be a tedious task, a handy Microsoft SQL stored procedure called sp_generate_inserts solves this problem.

The stored procedure (proc) has been created by Narayana Vyas Kondreddi's and a copy of the script can be found at http://vyaskn.tripod.com/code/generate_inserts.txt.

2. Generate Inserts

Run the script in [master] database. Then you can exec the procedure on the database you want to export.

Basically this procedure will create a literally query and run it. The result will be like

Insert [schema].[Table] (Column1, …, Column N) Values ( Value1, …, Value N)

There are a few defects in the original version of generate inserts procedure.

If you run the script directly on SQL Express 2008 R2, it always failed at

EXEC master.dbo.sp_MS_upd_sysobj_category 1

I removed "EXEC master.dbo.sp_MS_upd_sysobj_category 1" at the beginning, then replace

"EXEC master.dbo.sp_MS_upd_sysobj_category 2" with "EXEC sys.sp_MS_marksystemobject sp_generate_inserts" at the bottom. Please note you have to mark sp_generate_inserts as a system object. Otherwise when you run the procedure, you will get an error "User table or view not found".

After make these change, now we run a test,

Go to [AdventureWorks],

exec sp_generate_inserts @table_name='Culture', @owner='Production'

Image 1

You can find the result is single column. It will get truncated for some long values.

So I make a change to separate the result to multiple columns.

Image 2

Then unfortunately, the original version of "generate inserts" convert "xml" data to "char". It will cause another error, "Conversion of one or more characters from XML to target collation impossible", if there is non-ANSI character in xml. I fix this issue by converting "xml" to "nvarchar(max)".

3. WPF SQL Replication Tool

3.1 Visual Studio Solution Structure

Image 3

This solution includes 3 projects, "WPF SQL Replication Tool", "SQLSMO", and "LogUtil".

"SQLSMO" project is for scripting Database and Data with SQL Server Management Objects .

"LogUtil" is a logging component with log4net.

"WPF SQL Replication Tool" uses MVVM pattern to do SQL database and data replication.

3.2 MVVM Design Pattern

In MVVM the code is ViewModel. So it basically focuses on separation of concerns to make the implementation of an application structure a lot simpler to create as well as maintain.

If property values in the ViewModel change, those new values automatically propagate to the view via data binding and via notification. When the user performs some action in the view for example clicking on save button, a command on the ViewModel executes to perform the requested action. In this process, it’s the ViewModel which modifies model data, View never modifies it. The view classes have no idea that the model classes exist, while the ViewModel and model are unaware of the view. In fact, the model doesn’t have any idea about ViewModel and view exists.

The MVVM pattern includes three key parts:

Model (Business rule, data access, model classes)

View (User interface (XAML))

ViewModel (Agent or middle man between view and model)

The ViewModel acts as an interface between Model and View. It provides data binding between View and model data as well as handles all UI actions by using command.

The View binds its control value to properties on a ViewModel, which, in turn, exposes data contained in Model objects.

3.3 Integrate Generate Inserts to Replication Tool

After learn how to use sp_generate_inserts, now we need integrate it to our code.

private List<string> GetInserts(StringBuilder sb, Database db, string outputFolder, string tName, string schema, int maxRows, bool identityOn, int lastSeq = 0)
        {
            List<string> outputFiles = new List<string>();
            var identityOnSql = string.Format("SET IDENTITY_INSERT {0}.{1} ON", schema, tName);
            var identityOffSql = string.Format("SET IDENTITY_INSERT {0}.{1} OFF", schema, tName);

            try
            {
                using (DataSet ds = db.ExecuteWithResults(sb.ToString()))
                {
                    int rows = 0;
                    int seq = lastSeq;
                    string outputfile = Path.Combine(outputFolder, tName + seq.ToString("D5") + ".sql");
                    StreamWriter sw = new StreamWriter(outputfile, false, Encoding.Unicode);
                    if (identityOn)
                    {
                        sw.WriteLine(identityOnSql);
                    }
                    foreach (DataTable dt in ds.Tables)
                    {
                        int cols = dt.Columns.Count;

                        foreach (DataRow dr in dt.Rows)
                        {
                            if (rows > maxRows)
                            {
                                if (identityOn)
                                {
                                    sw.WriteLine(identityOffSql);
                                }
                                sw.Close();
                                sw.Dispose();
                                outputFiles.Add(outputfile);
                                seq++;
                                rows = 0;
                                outputfile = Path.Combine(outputFolder, tName + seq.ToString("D5") + ".sql");
                                sw = new StreamWriter(outputfile, false, Encoding.Unicode);
                                if (identityOn)
                                {
                                    sw.WriteLine(identityOnSql);
                                }
                            }
                            string insertSql = "";
                            for (int i = 0; i < cols; i++)
                                insertSql += dr[i].ToString();
                            sw.WriteLine(insertSql);
                            rows++;
                        }
                    }
                    if (identityOn)
                    {
                        sw.WriteLine(identityOffSql);
                    }
                    sw.Close();
                    sw.Dispose();
                    outputFiles.Add(outputfile);
                }
                return outputFiles;
            }
            catch (Exception ex)
            {
                throw;
            }
        }

The above code call sp_generate_inserts procedure, and save the result to a physical file. Thinking about a large table, the result file is possible to become very big. That’s not what I want. So I introduce maxRows to put a limit of rows of per file.

You can see "Set Identity On" and "Set Identity Off". What’s that mean? When we design a table, it’s very often set Primary Key (int) to identity, thus when you insert data, the value of id will be increase automatically. Because in the result SQL file, we’ve provided values for identity columns. When we run the result SQL file during the import, we have to Set identity insert on, and Set identity insert off when the session finish.

Now we have a look how to generate data script from a database.

public List<string> GenerateDataScript(string connStr, string outputDirectory)
        {
            List<string> outputFiles = new List<string>();
            SqlConnection connection = new SqlConnection(connStr);
            ServerConnection sc = new ServerConnection(connection);
            Server s = new Server(sc);

            Scripter scripter = new Scripter(s);
            Database db = new Database();
            db = s.Databases[connection.Database];

            string outputFolder = Path.Combine(outputDirectory, db.Name + "\\Data");
            if (!Directory.Exists(outputFolder))
                Directory.CreateDirectory(outputFolder);

            ScriptingOptions options = new ScriptingOptions();
            options.DriAll = true;
            options.ClusteredIndexes = true;
            options.Default = true;
            options.DriAll = true;
            options.Indexes = true;
            options.IncludeHeaders = true;
            options.AppendToFile = false;
            options.ToFileOnly = true;
            options.WithDependencies = true;
            options.ContinueScriptingOnError = true;

            scripter.Options = options;
            //Script tables
            if (db.Tables.Count > 0)
            {
                Table[] tbls = new Table[db.Tables.Count];
                db.Tables.CopyTo(tbls, 0);
                DependencyTree tree = scripter.DiscoverDependencies(tbls, true);
                DependencyWalker depwalker = new Microsoft.SqlServer.Management.Smo.DependencyWalker();
                DependencyCollection depcoll = depwalker.WalkDependencies(tree);
                List<string> excludeCols = new List<string>();
                StringBuilder sb = new StringBuilder();
                int maxRows = MaxRows;
                foreach (DependencyCollectionNode dep in depcoll)
                {
                    sb.Clear();
                    if (dep.Urn.Type != "Table")
                        continue;
                    string tName = dep.Urn.GetAttribute("Name");
                    string schema = dep.Urn.GetAttribute("Schema");
                    var tbl = db.Tables[tName, schema];
                    if (tbl == null)
                        continue;
                    if (tbl.IsSystemObject)
                        continue;
                    if (ignoreTables.ToList().Contains(tName))
                        continue;
                    bool identityOn = false;
                   
                    foreach (Column col in tbl.Columns)
                    {
                        if (col.Identity)
                        {
                            identityOn = true;
                        }
                        if (col.Computed)
                        {
                            excludeCols.Add(col.Name);
                        }
                    }
                    string colsExclude = excludeCols.Count > 0 ? ",@cols_to_exclude = \"" : "";
                    int i = 0;
                    foreach (var c  in excludeCols)
                    {
                        colsExclude += i==0 ? string.Format("'{0}'", c) : string.Format(",'{0}'", c);
                        i++;
                    }
                    if (!string.IsNullOrEmpty(colsExclude))
                        colsExclude += "\"";
                    sb.AppendFormat("EXEC sp_generate_inserts @table_name='{0}', @owner='{1}'{2}{3}", tName, schema, colsExclude, Environment.NewLine);

                    outputFiles.AddRange(GetInserts(sb, db, outputFolder, tName, schema, maxRows, identityOn));
                }
            }
            return outputFiles;
        }

One thing I have to mention is we need exclude computed columns. If the computed column value is defined by a deterministic expression, it cannot be the target of an INSERT or UPDATE statement.

3.4 Check Generate Inserts If Exists Before Importing

You can see data importing is fully dependent on "Generate Inserts" procedure. If "Generate Inserts" is not existed, the data importing will be failed straight away.

public static void EnsureGenerateInsertsProcExists(string connectionString, bool forceToUpdate)
        {
            string query = "select * from sysobjects where type='P' and name='sp_generate_inserts'";
            bool spExists = false;
            if (!forceToUpdate)
            {
                using (SqlConnection conn = new SqlConnection(connectionString))
                {
                    conn.Open();
                    using (SqlCommand command = new SqlCommand(query, conn))
                    {
                        using (SqlDataReader reader = command.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                spExists = true;
                                LogUtil.Logger.Instance.Log("sp_generate_inserts procedure exists in master of source server already.");
                                break;
                            }
                        }
                    }
                }
            }
            if (!spExists)
            {
                var file = Path.Combine(Path.GetDirectoryName(System.Reflection.Assembly.GetEntryAssembly().Location), "Scripts\\sp_generate_inserts.sql");
                if (File.Exists(file))
                {
                    LogUtil.Logger.Instance.Log("Create sp_generate_inserts procedure in master of source server already.");
                    string script = File.ReadAllText(file);
                    ExecuteSql(script, connectionString, false);
                }
            }

        }

3.4 Uncheck Constraints and Check Constraints

There is another problem will happens during import data to destination database. That is dependency. It’s very hard to according dependency order to insert data, sometimes nested dependency makes this issue even harder.

We can use an easy way to solve this problem, disable all constraints before import, and enable all constraints after import.

public static void UncheckConstraint(string connectionString, string tableName = null)
        {
            using (var connection = new SqlConnection(connectionString))
            {
                connection.Open();
                string sql = "EXEC sp_MSforeachtable @command1='ALTER TABLE " + (string.IsNullOrEmpty(tableName) ?  "?" : tableName) + " NOCHECK CONSTRAINT ALL'";
                using (var cmd = new SqlCommand(sql, connection))
                {
                    try
                    {
                        cmd.ExecuteNonQuery();
                    }
                    catch (Exception ex)
                    {
                        throw ex;
                    }
                }
            }
        }

        public static void CheckConstraint(string connectionString, string tableName = null)
        {
            using (var connection = new SqlConnection(connectionString))
            {
                connection.Open();
                string sql = "EXEC sp_MSforeachtable @command1='ALTER TABLE " + (string.IsNullOrEmpty(tableName) ? "?" : tableName) +" CHECK CONSTRAINT ALL'";
                using (var cmd = new SqlCommand(sql, connection))
                {
                    try
                    {
                        cmd.ExecuteNonQuery();
                    }
                    catch (Exception ex)
                    {
                        throw ex;
                    }
                }
            }
        }

4. Replicate Database and Data

4.1 Welcome

Image 4

4.2 Choose Source Database

Choose a database which you want to export. Please tick "Import data from source database" option.

We choose "AdventueWorks" as an example.

Image 5

4.3 Generate DB Schema Script and Data Script

First select an output folder which is the place scripts output to.

There are a few options.

  • "Update generate inserts sql procedure if it exists" option is only need tick if you want update "generate inserts" procedure on the source database.

    This option gives you a chance to update generate inserts procedure. As I said before, the original version has a few problems. Even after I change a little bit, it’s still possible to get some issues for some special data. If it happens, you need add some your customization change.

  • "Create Schema Script" generates database schema scripts.
  • "Create Data Script" generates data scripts.

Tick options as you wanted. This tool is very flexible, you don’t have to tick all options if you don’t need.

Tick "Create Schema" option if you want to generate database schema scripts.

Image 6

Ok. Now do the work. Click "Generate script" button.

Image 7

Image 8

When it completes, you can see the SQL files generated in output folder.

Image 9

Image 10

4.4 Choose Destination Server

Choose a database which you want to import. Specify the server name and access authentication. For example, if you want to export to "test" of local SQL Express, the blow is the screenshot. Please note you have to click "Get Default Path" button to get the data file path. This tool doesn’t delete the existing database. Make sure the new database name doesn’t exist on the server if you want to create database.

Image 11

4.5 Run Script

"Create Database" creates the database from scratch, and create all objects.

"Import Data" imports all data to the destination database.

You don’t have to select all options. For exiting database, you don’t need tick "Create Database" option.

Image 12

Click "Run immediately" to start import job.

Image 13

Image 14

When it completes, "AdventureWorks2" is created successfully with all objects.

Image 15

Image 16

4.6 Summary of Tasks

After all works done, you finally get Summary page. The summary page gives you an overview about all tasks you’ve done. Also you can open log file to check.

Image 17

5. Conclusion

In this article, we started from Generate Inserts procedure, then go through how generates inserts SQL files with this procedure and runs these inserts SQL files to import data. The key point is all SQL files generated by this tool can be reused. If you tick "Import Data from existing SQL files" on Source Selection Page, the wizard will skip script generation page to Choose Destination page directly.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Fred Song (Melbourne)
Software Developer (Senior)
Australia Australia
Fred is a senior software developer who lives in Melbourne, Australia. In 1993, he started Programming using Visual C++, Visual Basic, Java, and Oracle Developer Tools. From 2003, He started with .Net using C#, and then expertise .Net development.

Fred is often working with software projects in different business domains based on different Microsoft Technologies like SQL-Server, C#, VC++, ASP.NET, ASP.Net MVC, WCF,WPF, Silverlight, .Net Core and Angular, although he also did some development works on IBM AS400.

Comments and Discussions

 
GeneralMy vote of 5 Pin
Santhakumar M13-Nov-15 4:25
professionalSanthakumar M13-Nov-15 4:25 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Article
Posted 12 Nov 2015

Stats

8.4K views
318 downloads
11 bookmarked