Generate Insertion Scripts using .Net 2.0

• Download SqlScriptGenerator_Demo.zip - 81.7 KB
• Download SqlScriptGenerator_Source.zip - 119.0 KB

Introduction

Recently I have to modify some data in SQL Server and insert them into other databases. The approach is to get the data from the source database. Modify them in a grid and generate Insertion Statements from the the gird for later use.

Background

The features for getting SQL Server schema in .Net 2.0 is very useful and easy to use. And the task to generate scripts becomes very easy if you can get the schema.

Using the code

To get SQL Server Instance

            DataTable table = System.Data.Sql.SqlDataSourceEnumerator.Instance.GetDataSources();
            DataView view = new DataView(table, string.Empty, "ServerName", DataViewRowState.Added);
            cbx_ServerList.DataSource = view;
            cbx_ServerList.DisplayMember = "ServerName";<o:p /> 

To get list of SQL Server Databases

            DataTable table = conn.GetSchema(SqlClientMetaDataCollectionNames.Databases);
            cbx_DatabaseList.DataSource = table;
            cbx_DatabaseList.DisplayMember = "database_name";<o:p /> 

To get list of Tables

            DataTable table = conn.GetSchema(SqlClientMetaDataCollectionNames.Tables, new string[] { null, null, null, "BASE TABLE" });
            cbx_TableList.DataSource = table;
            cbx_TableList.DisplayMember = "TABLE_NAME";<o:p /> 

To get list of Views

            DataTable table = conn.GetSchema(SqlClientMetaDataCollectionNames.Tables, new string[] { null, null, null, "VIEW" });
            cbx_ViewList.DataSource = table;
            cbx_ViewList.DisplayMember = "TABLE_NAME";<o:p /> 

To get list of Columns

Some types are ignored because I don't really need this to be exported. And, it looks strange to export an image to a text file right?

            private const string FILTER_COLUMN_TYPE = "data_type NOT IN ('uniqueidentifier', 'timestamp', 'image')"; <o:p /> 
            DataTable columnsTable = conn.GetSchema(SqlClientMetaDataCollectionNames.Columns, new string[] { null, null, tableName });
            DataView columnsView = new DataView(columnsTable, FILTER_COLUMN_TYPE, "ordinal_position", DataViewRowState.Added);<o:p /> 

Script Generation

        private static string GenerateDataRowInsertSQLScript(string SQLTableName, DataRow row, ArrayList includedColumnNames)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append(" INSERT INTO [").Append(SQLTableName).Append("] ( ");
            for (int cnt = 0; cnt < includedColumnNames.Count; cnt++)
            {
                if (cnt != 0)
                    builder.Append(", ");
                builder.Append("[").Append(includedColumnNames[cnt].ToString()).Append("]");
            }
            builder.Append(" ) ").Append(Environment.NewLine);

            builder.Append(" VALUES ( ");
            for (int cnt = 0; cnt < includedColumnNames.Count; cnt++)
            {
                if (cnt != 0)
                    builder.Append(", ");

                string columnName = includedColumnNames[cnt].ToString();
                builder.Append(SqlHelper.ToSQLString(row[columnName], row.Table.Columns[columnName].DataType));
            }
            builder.Append(" ) ").Append(Environment.NewLine).Append(Environment.NewLine);

            return builder.ToString();
        } <o:p /> 

The Demo

Input the connection details, and choose the corresponding Table.

The "Select Script" button will generate a SELECT statement, it is just for convenience. You can add a WHERE CAUSE after that as well.

Press "Execute" button and you will get the result data set in the gird.

You can then modify the data as you like.
By choosing the "Columns", you can do a Re-Ordering or a Replacement function in the grid if you like.

Press the "Preview" button to get the Insertion Script.

Conclusion

This is just a simple project to show you how to get the schema from SQL Server using .Net 2.0.

A note is that since I didn't capture what you have input in the Select Script Box. And I don't know what is the TABLE NAME by just the Data Set returned. Therefore you need to choose the correct TABLE NAME before generating the Insertion Scripts.