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