Passing table value params to Sql Server from WPF app

Christopher Duncan

For those familiar with them, TVPs are a great way to reduce the number of calls to the database and improve performance. I use them to great effect in the MVC web app using the general technique below. In the stored procedure I use a cursor to iterate through the rows and do all the db stuff in one whack. I copied the same routines to a WPF app I'm doing, however, and it flames out calling the proc. I've since deleted it and gone the multiple calls route so I don't remember the precise error message but in general it was barking about not liking the parameters. I'm on the same box, same version of VS, etc. and literally copied and pasted my routines. Works in MVC, no joy in WPF. Have any of you successfully used table value parameters in a WPF app, and if so, is there a trick I'm missing?

DataTable tvp = new DataTable();
tvp.Columns.Add(new DataColumn("Id", Type.GetType("System.Int32")));
tvp.Columns.Add(new DataColumn("RowIdx", Type.GetType("System.Int32")));
tvp.Columns.Add(new DataColumn("ParentId", Type.GetType("System.Int32")));
tvp.Columns.Add(new DataColumn("MemberId", Type.GetType("System.Int32")));

DataRow row = null;
int iRowIdx = 0;

foreach (int iId in Ids)
{
row = tvp.NewRow();
row["Id"] = 0;
row["RowIdx"] = iRowIdx++;
row["ParentId"] = iListId;
row["MemberId"] = iId;
tvp.Rows.Add(row);
}
...
Command.Parameters.AddWithValue("@Entries", tvp);
...
ExecuteNonQuery(), etc.

Thanks!

Christopher Duncan Author of Unite the Tribes: Leadership Skills for Technology Managers Have Fun, Get Paid The Career Programmer

Richard Deeming

ADO.NET is ADO.NET; there's no difference whether you're calling it from ASP.NET, WPF, or a console application. If it works in your ASP.NET application, but not in your WPF application, then there's a difference in the code which you haven't shown. For example, verify that Command is a SqlCommand instance, and not some other type. NB: You can replace your Type.GetType calls with the typeof keyword[^]:

tvp.Columns.Add(new DataColumn("Id", typeof(int)));

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer

Christopher Duncan

Hey, Richard. Appreciate the feedback. Yeah, that was my take on it as well - ADO.NET should be ADO.NET. However, after a few decades of dealing with MS technologies, I never take that as gospel. When I get some time I'm going to try it again as the performance difference is significant, but I eyeballed the code pretty intensely and am as sure as I ever am about such things that it was the same in both environments. I hope to be wrong about that because otherwise I'm out of ideas. Do you use TVPs in your ADO.NET apps?

Christopher Duncan Author of Unite the Tribes: Leadership Skills for Technology Managers Have Fun, Get Paid The Career Programmer

Richard Deeming

Christopher Duncan wrote:

Do you use TVPs in your ADO.NET apps?

Yes, although I tend to use the IEnumerable<SqlDataRecord> approach, rather than a DataTable.

private static IEnumerable<SqlDataRecord> AsTableValuedParameter(IEnumerable<string> value)
{
var record = new SqlDataRecord(new SqlMetaData("Value", SqlDbType.NVarChar, 0x100));
foreach (string item in value)
{
record.SetValue(0, item);
yield return record;
}
}

...

IEnumerable<string> roleNames = ...;
var parameter = command.Parameters.AddWithValue("@RoleNames", AsTableValuedParameter(roleNames));
parameter.SqlDbType = SqlDbType.Structured;

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer

Christopher Duncan

That's a nice approach. The DataTable was from an example I found on TVPs. I was focused on making the db interaction worked and never thought to convert it to something more elegant. Deadlines and all that. I'm going to take another swing at this today. Hopefully I just fat fingered something when I cloned the MVC code I was using. Appreciate all the help.

Christopher Duncan Author of Unite the Tribes: Leadership Skills for Technology Managers Have Fun, Get Paid The Career Programmer

Christopher Duncan

Well, apparently the fingers are indeed fat. Tried again this morning and it worked without a fuss. Still using DataTable approach at this point. MS says SqlDataRecord is resource abusive and recommends not creating a new one but reusing a single instance. That aside, are there any benefits to the enumerable approach over populating a data table?

Christopher Duncan Author of Unite the Tribes: Leadership Skills for Technology Managers Have Fun, Get Paid The Career Programmer

Richard Deeming

Christopher Duncan wrote:

MS says SqlDataRecord is resource abusive and recommends not creating a new one but reusing a single instance.

Which is why I was doing that in the sample I posted. :)

Christopher Duncan wrote:

are there any benefits to the enumerable approach over populating a data table?

Not that I'm aware of.

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer

Christopher Duncan

My bad. Looked like you were allocating a new SqlDataRecord each time but I'm in the US and thus still trying to get my eyes to focus this morning. Need. More. Coffee. :)

Christopher Duncan Author of Unite the Tribes: Leadership Skills for Technology Managers Have Fun, Get Paid The Career Programmer