Click here to Skip to main content
65,938 articles
CodeProject is changing. Read more.
Articles / desktop / WinForms

Generate Insertion Scripts Using .NET 2.0

4.62/5 (4 votes)
23 Jul 2007CPOL2 min read 1   901  
Using .NET 2.0 to get the SQL Server schema and generate insertion statements.

Screenshot - Preview.jpg

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:

C#
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:

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

To get a list of tables:

C#
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:

C#
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?):

C#
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:

C#
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.

Screenshot - Login.jpg

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

Screenshot - SelectStatement.jpg

Press the "Execute" button, and you will get the result dataset in the gird.

Screenshot - OriginalData.jpg

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.

Screenshot - ModifiedData.jpg

Press the "Preview" button to get the insertion script.

Screenshot - Preview.jpg

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.

License

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