Click here to Skip to main content
14,243,297 members

DataSet to T-SQL Script

Rate this:
4.81 (10 votes)
Please Sign up or sign in to vote.
4.81 (10 votes)
17 Dec 2014CPOL
Export a System.Data.DataTable/DataSet into a T-SQL script that when run creates a #TEMP table of the same definition filled with data.

Introduction

This static class exposes methods that take in a System.Data.DataTable or DataSet and outputs a Transact SQL (T-SQL) script. When that script is run against a SQL Server instance, it will create a logically identical structure in the form of a #TEMP table. This can be especially useful when debugging (both in development and in production) to analyze the data in the DataTable(s). Our team has found this particularly helpful when working with large DataTables and using SqlBulkImport, as well as debugging issues with constraints (Primary Key, Foreign Key and Check).

While this code was intended to be used in debugging, it could easily be wired into a logging mechanism to capture detailed logging reports to show how data is moving though your system.

Using the Code

To create the T-SQL script, call one of the methods depending on your needs. Currently, the code provides 3 methods for outputting results to the Debug.Output window, a System.String variable, or to a file. Feel free to extend to meet your own needs.

DataSet ds = // A DataSet filled with data....

// Write the script results to the Debug.Output window
DataSetToScript.DataSetToSqlScriptOutput(ds);

// Write the script to a string variable
string sql_script = DataSetToScript.DataSetToSqlScript(ds);

// Write the script to a string variable for a single table
string sql_script = DataSetToScript.DataTableToSqlScript(ds.Tables[1]);

// Write the script to a file and return the path to the output file
string full_file_path = DataSetToScript.DataSetToSqlScriptFile(ds);

After the T-SQL script code is generated, copy and paste (or open the output file) in SQL Server Management Studio (or your favorite SQL client) and run the script.

-- Create a structure to hold the import data --
DECLARE @x0 xml = CAST(N'<TABLE>
  <R>
    <C1>0</C1>
    <C2>0</C2>
    <C3>2014-12-15T11:40:45.9329535-08:00</C3>
    <C4>abcdefghilkmnopqrstuvwxyz1234567890 ~!@#$%^&*()_+`{}[]|\\:;"''<>,.?/®£¥</C4>
  </R>
  <R>
    <C1>1</C1>
    <C2>0.33333333333333331</C2>
    <C3>2014-12-15T11:40:55.9329535-08:00</C3>
    <C4>abcdefghilkmnopqrstuvwxyz1234567890 ~!@#$%^&*()_+`{}[]|\\:;"''<>,.?/®£¥</C4>
  </R>
</TABLE>' as xml);

-- Process that structure --
SELECT 
    T.r.value('C1[1]', 'int') as [Column_1_\], 
    T.r.value('C2[1]', 'float') as [Column_2_\], 
    T.r.value('C3[1]', 'datetime') as [Column_3_\], 
    T.r.value('C4[1]', 'nvarchar(max)') as [Column_4_\] 
INTO [#TEMP0]
FROM @x0.nodes('/TABLE/R') T(r);
GO

select * from [#TEMP0]
GO

After running, you will have a structure you can use to analyze data as you see fit (in this example #TEMP0).

Extension Method Update

After the initial posting, it occurred to me that most people would find it convenient to have this functionality exposed as extension methods, so I have included an extension method class to do just that. The extension class is in the System.Data namespace so if this downloaded assembly is present, it will be available for all DataSets and DataTables.

DataSet ds = // DataSet filled with data
DataTable dt = ds.Tables[0]; // Some DataTable filled with data

// Get the entire DataSet as a T-SQL Script
string tSqlScript1 = ds.AsSqlScript();

// Get a single DataTable as a T-SQL Script
string tSqlScript2 = dt.AsSqlScript();

Additionally, I have added a Reset() method to reset static variables used in DataSetToScript. This was to aid in unit testing and should not likely be used in production-worthy code.

Points of Interest

From a high-level perspective, the process in pretty straight forward:

  1. Set the properties of the DataSet and DataTable such that they won't conflict with the export process, like altering the TableName and DataSetName.
  2. Use DataTable.WriteXML() to create a giant SQL XML variable and escape (like replacing single ticks with double ticks) the results so they will be suitable for running as a T-SQL script.
  3. Add some T-SQL script code to parse that variable using XQuery to return the results as a standard SQL result set.

Some of the more low-level details that needed special attention in the code:

  1. Data type mapping was awkward since we started with a .NET data type, transition to an XML data type, and finally to a SQL data type. Finding the common ground of all 3 is tricky and will likely cause problems if you use complex data types in your DataTables. See SqlTypeMap in the code for details. Unit test well any time you introduce a non-standard data type (pretty much anything beyond int/float/string/date/etc.)
  2. Ensuring the output XML will properly import into SQL was a little tricky. The path of least resistance was to (temporarily) rename the DataTable and DataSet to names that would not conflict, and undo the rename after the export. This also helps keep the size of the exported XML smaller. See DataSetToSqlScript in the code for details.
  3. At the top of the output script, details are attached in a comment block. Things like export machine name and export date/time. This is just a bell and whistle to aid in debugging. See GetHeaderDetails in the code for more details.
  4. To try and keep the XML as small as possible, the row tag is set to R and the column tag is set to Cn (where n = 1,2,3,...). The original (but properly escaped) column name is restored in the final select.

History

  • 15 Dec 2014 - Initial public draft
  • 16 Dec 2014 - Clarified a few points and included .sln/.csproj in zip file to make running the included unit tests easier
  • 16 Dec 2014 - Extension methods, Reset() method to aid in unit testing
  • 23 Dec 2014 - Minor article updates for clarity. Code is unchanged.
  • 09 Jan 2015 - Included alegn's well-made suggestion to omit columns that have been flagged as hidden for XML export from the other parts of the script generation. Thanks!

License

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

Share

About the Author

Brad Joss
Software Developer (Senior)
United States United States
No Biography provided

Comments and Discussions

 
Questionsuggest a slight correction in the method DataSetToScript.DataSetToSqlScript Pin
alegn6-Jan-15 21:43
memberalegn6-Jan-15 21:43 
AnswerRe: suggest a slight correction in the method DataSetToScript.DataSetToSqlScript Pin
Brad Joss9-Jan-15 13:16
professionalBrad Joss9-Jan-15 13:16 
AnswerRe: suggest a slight correction in the method DataSetToScript.DataSetToSqlScript Pin
Brad Joss10-Jan-15 6:54
professionalBrad Joss10-Jan-15 6:54 
QuestionThanks this one is handy! Pin
devvvy17-Dec-14 10:57
memberdevvvy17-Dec-14 10:57 
SuggestionRe: Thanks this one is handy! Pin
Brad Joss17-Dec-14 14:58
professionalBrad Joss17-Dec-14 14:58 

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.

Tip/Trick
Posted 15 Dec 2014

Stats

30.7K views
343 downloads
19 bookmarked