Generate Insertion Scripts Using .NET 2.0

• Download demo - 81.7 KB
• Download source - 106 KB

Introduction

Recently, I had 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 Insert statements from the the gird for later use.

Background

The features for getting the 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

Here is how we get the 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"; 

To get a list of SQL Server databases:

DataTable table = conn.GetSchema(SqlClientMetaDataCollectionNames.Databases);
cbx_DatabaseList.DataSource = table;
cbx_DatabaseList.DisplayMember = "database_name";

To get a 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";

To get a list of Views:

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

To get a 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')";  
DataTable columnsTable = conn.GetSchema(SqlClientMetaDataCollectionNames.Columns, 
                                        new string[] { null, null, tableName });
DataView columnsView = new DataView(columnsTable, FILTER_COLUMN_TYPE, 
                           "ordinal_position", DataViewRowState.Added); 

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();
}

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 clause after that as well.

Press the "Execute" button, and you will get the result dataset 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.

Note that since I didn't capture what you have input in the Select Script box, and I don't know what the table name is by just looking at the dataset returned, you need to choose the correct table name before generating the insertion scripts.