.NET DataTable to XML to SQL to DataTable

Question

I'm trying to pass a DataTable between C# and SQL Server both ways. I'm currently using XML to facilitate this, but I'm struggling to deserialize the XML both ways (i.e. to deserialize the C# XML in SQL Server, and to deserialize the SQL Server XML in C#). I'm currently using the following procedure to create the XML in C#:

var dt = new DataTable();
//Populate the DataTable...
var set = new DataSet();
set.Tables.Add(dt);
var sb = new StringBuilder();
using (var stringWriter = new StringWriter(sb))
{
  set.WriteXml(stringWriter);
}
string xml = sb.ToString();

I'd then like to be able to parse this XML in SQL Server. Is there a way to automatically parse this as a DataTable in SQL Server (using a schema I would suppose)?

Use [TableAdapter](https://msdn.microsoft.com/en-us/library/7zt3ycf2.aspx). The intermediate XML is not needed. — Alexander Petrov, Nov 16 '15 at 08:21

score 0 · Answer 1 · edited May 23 '17 at 12:03

Since sql server 2008 you can use table valued parameters to pass data tables from .net to sql server.
You will have to create a user defined table type in the database and pass the parameter as readonly to the stored procedure as demonstrated here and there.

To pass data tables from sql server to .net you can use .net's TableAdapter to execute a select statement and fill a DataTable object, as dmonstrated here.

.NET DataTable to XML to SQL to DataTable

1 Answers1