sql bulkcopy

sql bulk copy from datatable into sql table(with transaction)


 Dim con As New  SqlConnection("connection string")
 con.Open()

 Dim myTransaction As SqlTransaction = con.BeginTransaction()
 Dim bulkCopy As New SqlBulkCopy(con, SqlBulkCopyOptions.Default,   myTransaction)

bulkCopy.DestinationTableName = "table name"
bulkCopy.WriteToServer("datatable")
myTransaction.Commit()
bulkCopy.Close()

Note:
you can use column mapping
bulkCopy.ColumnMappings.Add("column name in datatable", "column name in sql table")

LINQ query to DataTable

Converting Anonymous type generated by LINQ to a DataTable type(here LINQ left outer join)

  customer                                                      city

id   name  city_id                                     cid       city_name                         

1     xxx        2                                             1           aaa

2     yyy         3                                            2           bbb

                                                                     3           ccc

First create temporary datatable with columns you need

Dim tbTemp As New DataTable
        tbTemp.Columns.Add("cust_name")
        tbTemp.Columns.Add("city_name")
     

Using left outer join  

 Dim JoinedResult As IEnumerable(Of DataRow) = From t1 In                                                customer.AsEnumerable()
         Group Join t2 In city.AsEnumerable()
         On t1.Field(Of String)("city_id") Equals t2.Field(Of String)("cid")
        Into RightTableResults = Group
        From t In RightTableResults.DefaultIfEmpty
        Select tbTemp.LoadDataRow(New Object() {t1.Item("cust_name"),      t.Item("city_name")})

 Dim tbResult As New DataTable

tbResult=JoinedResult.CopyToDataTable() 

Reference

http://social.msdn.microsoft.com/Forums/en/csharplanguage/thread/6d4e2579-f4b5-497c-ad00-7c347eb1f902