What is the fastest way to create VB.NET Datatables from SQL TVP

Question

I am in the process of revising code to use TVP to send data from our VB.NET app to the SQL 2008 DB and try to keep all the writes atomic.

Using this page as a general guide: http://www.sqlteam.com/article/sql-server-2008-table-valued-parameters

I am in the process of creating all the in-code datatables to be sent to the SQL stored procedure

However, I will have to create these datatables and datacolumns (then add the columns to the tables) repeatedly for multiple tables. In my case, each of these Datatables will have layouts identical to the SQL Server table (with the exception on the autonumber PK and GUID)....

Is there a way to auto-generate this datatable creation code? Or is there a faster way in general?

Thanks

Answer 1

Honestly, the simplest method that I use is by using Excel. I copy the SQL Table Definition from the Design View and paste it into the first 3 rows of Excel. Then I use functions, one column for each part of the class, and build my code that way. For example,

D1="Dim prm" & A1 & " As SQLParameter = tmpSqlCommand.Paramaters.Add('@" & A1 & "', SQLDbType." & B1 & ")"

It may not be a code generator, but the only thing I need to do is copy the table layout and I have my classes all designed for a copy and paste. Definitely cuts down on coding.