I have an F# program that creates a DataTable, populates it with one row and then writes the data to SQL Server using bulk insert (SqlBulkCopy).
Although it's working, I can't really figure out how to include a loop that will generate a number of list items / data rows which I can then insert in one statement, rather than having to bulk insert a single row at a time (which is the current case)
here's 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()
I think I need to include a for loop inside the bulkLoadEsgData method, to make the code run efficiently. Except I've no idea what to do / where to write that
ildjarn
63.3k9 gold badges135 silver badges219 bronze badges
asked Dec 27, 2012 at 15:16
Casual Pundit
2792 silver badges10 bronze badges
-
3I don't know why you're being downvoted either -- nothing wrong with this question, and you at least provided code for your attempts (unlike many other askers).Jack P.– Jack P.2012年12月27日 16:03:36 +00:00Commented Dec 27, 2012 at 16:03
-
1If you rephrase the text to be more clear what you are looking for you will be okHarald Scheirich– Harald Scheirich2012年12月28日 13:14:00 +00:00Commented Dec 28, 2012 at 13:14
1 Answer 1
I managed to resolve this
sbc.WriteToServer(
let dt = new DataTable()
dt.Columns.Add("Measure", typeof<byte>) |> ignore
dt.Columns.Add("Identifier", typeof<byte>) |> ignore
dt.Columns.Add("Simulation", typeof<int>) |> ignore
dt.Columns.Add("Time", typeof<byte>) |> ignore
dt.Columns.Add("Duration", typeof<byte>) |> ignore
dt.Columns.Add("Result", typeof<single>) |> ignore
for i= 1 to 100 do
dt.Rows.Add(i, i, i, i, i, (float)i*0.11) |> ignore
dt)
ildjarn
63.3k9 gold badges135 silver badges219 bronze badges
answered Dec 27, 2012 at 15:55
Casual Pundit
2792 silver badges10 bronze badges
Sign up to request clarification or add additional context in comments.
Comments
default