Click here to Skip to main content
15,614,943 members
Articles / Desktop Programming / Windows Forms
Posted 23 Jul 2007


33 bookmarked

Generate Insertion Scripts Using .NET 2.0

Rate me:
Please Sign up or sign in to vote.
4.62/5 (4 votes)
23 Jul 2007CPOL2 min read
Using .NET 2.0 to get the SQL Server schema and generate insertion statements.

Screenshot - Preview.jpg


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.


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(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(" ) ").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


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.


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

Written By
Software Developer (Senior)
Hong Kong Hong Kong
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

GeneralA Nice Tool Pin
Yelihs24-Jul-07 22:44
Yelihs24-Jul-07 22:44 
GeneralPerformance Issue Pin
chris338823-Jul-07 23:12
chris338823-Jul-07 23:12 
GeneralRe: Performance Issue Pin
Benedict Chan24-Jul-07 0:06
Benedict Chan24-Jul-07 0:06 

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.