Click here to Skip to main content
13,664,555 members
Click here to Skip to main content
Add your own
alternative version

Stats

30.7K views
834 downloads
32 bookmarked
Posted 23 Jul 2007
Licenced CPOL

Generate Insertion Scripts Using .NET 2.0

, 23 Jul 2007
Rate this:
Please Sign up or sign in to vote.
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:

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.

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)

Share

About the Author

Benedict Chan
Software Developer (Senior)
Hong Kong Hong Kong
No Biography provided

You may also be interested in...

Pro
Pro

Comments and Discussions

 
GeneralA Nice Tool Pin
Yelihs24-Jul-07 22:44
memberYelihs24-Jul-07 22:44 
GeneralPerformance Issue Pin
chris338823-Jul-07 23:12
memberchris338823-Jul-07 23:12 
GeneralRe: Performance Issue Pin
Benedict Chan24-Jul-07 0:06
memberBenedict 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.

Permalink | Advertise | Privacy | Cookies | Terms of Use | Mobile
Web06-2016 | 2.8.180810.1 | Last Updated 23 Jul 2007
Article Copyright 2007 by Benedict Chan
Everything else Copyright © CodeProject, 1999-2018
Layout: fixed | fluid