DataSet to T-SQL Script

• Download source - 9.8 KB

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!