I have a F # program that creates a DataTable , populates it with a single line, and then writes data to SQL Server using bulk insert ( SqlBulkCopy ).
Despite the fact that it works, I cannot figure out how to enable a loop that will generate several list items / rows of data, which I can then insert into one statement, instead of inserting one row into one row at a time (this is the current happening)
here is my code:
open System open System.Data open System.Data.SqlClient let lcpSqlConnection = new SqlConnection("<my-connection-string>") lcpSqlConnection.Open() let bulkLoadEsgData (conn:SqlConnection) (esgTable: list<byte * byte * int * byte * byte * single>) = use sbc = new SqlBulkCopy(conn, SqlBulkCopyOptions.TableLock, null, BatchSize=10000, BulkCopyTimeout=1200, DestinationTableName="dbo.myTable") sbc.WriteToServer( let dt = new DataTable() ["Measure", typeof<byte> "Identifier", typeof<byte> "Simulation", typeof<int> "Time", typeof<byte> "Duration", typeof<byte> "Result", typeof<single>] |> List.iter (dt.Columns.Add>>ignore) for esgData in esgTable do let esg_measure, identifier, simulation, time, duration, result = esgData let dr = dt.NewRow() dr.["Measure"] <- esg_measure dr.["Identifier"] <- identifier dr.["Simulation"] <- simulation dr.["Time"] <- time dr.["Duration"] <- duration dr.["Result"] <- result dt.Rows.Add(dr) dt) let myList: list<byte * byte * int * byte * byte * single> = [(byte)1,(byte)1,1, (byte)1,(byte)1,(single)0.111] // Call method to bulk insert data row bulkLoadEsgData lcpSqlConnection myList lcpSqlConnection.Close()
It seems to me that I need to include a for loop inside the bulkLoadEsgData method to make the code efficient. In addition, I have no idea what to do / where to write, what
source share