static class exposes methods that take in a
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 =
string sql_script = DataSetToScript.DataSetToSqlScript(ds);
string sql_script = DataSetToScript.DataTableToSqlScript(ds.Tables);
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.
DECLARE @x0 xml = CAST(N'<TABLE>
</TABLE>' as xml);
T.r.value('C1', 'int') as [Column_1_\],
T.r.value('C2', 'float') as [Column_2_\],
T.r.value('C3', 'datetime') as [Column_3_\],
T.r.value('C4', 'nvarchar(max)') as [Column_4_\]
FROM @x0.nodes('/TABLE/R') T(r);
select * from [#TEMP0]
After running, you will have a structure you can use to analyze data as you see fit (in this example
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
DataSet ds =
DataTable dt = ds.Tables;
string tSqlScript1 = ds.AsSqlScript();
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:
- Set the properties of the
DataTable such that they won't conflict with the export process, like altering the
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.
- 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:
- 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
SqlTypeMap in the code for details. Unit test well any time you introduce a non-standard data type (pretty much anything beyond
- Ensuring the output XML will properly import into SQL was a little tricky. The path of least resistance was to (temporarily) rename the
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.
- 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.
- 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
- 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!