Wanted a generic way to pass a DataTable as a parameter to SqlCommand as a TVP (Table Value Parameter). It automatically creates the, IMHO, superfluous User-Defined Table Types. Here's what I have so far:

          
            
              <Runtime.CompilerServices.Extension()>
_ Sub AddTableParameter(ByVal cmd As SqlCommand, ByVal n AsString, ByVal dt As DataTable) Static typeXlat As Dictionary(Of
System.Type, SqlDbType) If typeXlat IsNothingThen typeXlat =New Dictionary(Of
System.Type, SqlDbType) typeXlat.Add(GetType(Int64),
SqlDbType.BigInt) typeXlat.Add(GetType(Int32),
SqlDbType.Int)
typeXlat.Add(GetType(Int16),
SqlDbType.SmallInt) typeXlat.Add(GetType(Byte),
SqlDbType.TinyInt) typeXlat.Add(GetType(SByte),
SqlDbType.TinyInt) typeXlat.Add(GetType(Boolean),
SqlDbType.Bit) typeXlat.Add(GetType(String),
SqlDbType.VarChar) typeXlat.Add(GetType(Byte()),
SqlDbType.VarBinary) typeXlat.Add(GetType(DateTime),
SqlDbType.DateTime) typeXlat.Add(GetType(DateTimeOffset),
SqlDbType.DateTimeOffset) typeXlat.Add(GetType(Char),
SqlDbType.Char)
typeXlat.Add(GetType(Single),
SqlDbType.Real) typeXlat.Add(GetType(Double),
SqlDbType.Float) typeXlat.Add(GetType(Decimal),
SqlDbType.Decimal)
typeXlat.Add(GetType(Guid),
SqlDbType.UniqueIdentifier) typeXlat.Add(GetType(Xml.XmlDocument),
SqlDbType.Xml) EndIf'assembly
the typename and specDim tabletypedef AsNew List(Of String) Dim parm AsNew SqlParameter(n,
SqlDbType.Structured) parm.Value = dt ForEach c As DataColumn In dt.Columns Dim s As SqlDbType = SqlDbType.Variant If typeXlat.ContainsKey(c.DataType) Then s = typeXlat(c.DataType)
tabletypedef.Add(c.ColumnName +"
"+ s.ToString) NextDim tabledef =String.Join(",",
tabletypedef.ToArray) Dimtypename="autotype_"+ tabledef
parm.TypeName="["+typename+"]"'create
the type if needed Using cmd2 =New SqlCommand("select
count(*) from sys.types where is_table_type=1 and name='"+typename+"'",
cmd.Connection) If cmd2.ExecuteScalar() = 0 Then cmd2.CommandText ="create
type ["+typename+"]
as table("+ tabledef +")" cmd2.ExecuteNonQuery() EndIfEnd Using 'add
the parm referencing the typename cmd.Parameters.Add(parm) EndSub