F#LINQ向SQL Server添加新行 [英] F# LINQ add new rows to SQL Server

问题描述

我正在用F#3.0学习LINQ.我想知道如何重写旧代码以在F#3.0中使用LINQ的新功能.例如，我在SQL Server 2008 R2中创建了一个简单的数据表，数据库名称为myDatabase.

I am learning LINQ with F# 3.0. I want to know how to re-write my old code to use new LINQ features in F# 3.0 For example, I have created a simple data table in SQL Server 2008 R2, the database name is myDatabase.

-- Create the Table1 table.
CREATE TABLE [dbo].[Table1] (
    [Id]        INT        NOT NULL,
    [TestData1] INT        NOT NULL,
    [TestData2] FLOAT (53) NOT NULL,
    [Name]      NTEXT      NOT NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC)
);

对于F#2.0，我可以使用数据集添加新行，如下所示:

For F# 2.0, I can use Dataset to add new rows, like this:

#light
open System
open System.Collections.Generic 
open System.Data
open System.Data.SqlClient

let sqlConn = "server=(local); Integrated Security=True; Database=MyDatabase"
let connDB = new SqlConnection(sqlConn)
let sql = "SELECT * FROM Table1"
let da = new SqlDataAdapter(sql, connDB)
let ds  = new DataSet()
da.Fill(ds) |> ignore
let commandBuilder: SqlCommandBuilder = new SqlCommandBuilder(da)

for i = 0 to 1 do
    let newDR: DataRow = ds.Tables.Item(0).NewRow()
    newDR.Item(0) <- (i + 1)
    newDR.Item(1) <- (i * 10)
    newDR.Item(2) <- ( decimal i ) * 5.0M
    newDR.Item(3) <- "Testing" + (i + 1).ToString()
    ds.Tables.Item(0).Rows.Add(newDR)
da.Update(ds) |> ignore

现在使用F#3.0，如何更好地重新编写添加新行的代码? 我想我可以写一些像这样的代码:

Now with F# 3.0, how I can re-write the add new rows code better? I think I can wrtie some code like:

#light
open System
open System.Data.Linq
open Microsoft.FSharp.Data.TypeProviders
open Microsoft.FSharp.Linq

[<Generate>]
type dbSchema = SqlDataConnection<"Data Source=.;Initial Catalog=MyDatabase;Integrated Security=True">
let db = dbSchema.GetDataContext()

try
   db.DataContext.ExecuteCommand("INSERT INTO Table1 (Id, TestData1, TestData2, Name) VALUES (1, 10, 0.0, 'Testing1')") |> ignore
with
   | exn -> printfn "Exception:\n%s" exn.Message

try
   db.DataContext.ExecuteCommand("INSERT INTO Table1 (Id, TestData1, TestData2, Name) VALUES (2, 20, 5.0, 'Testing2')") |> ignore
with
   | exn -> printfn "Exception:\n%s" exn.Message

但是我不认为新方法更好，实际上，我认为它甚至更糟.在F#2.0中，我可以使用代码生成数据表的值，但是如果我必须编写带有预定义值的静态SQL语句(例如"INSERT INTO Table1 VALUES(")，那么我想我希望通过以下方式插入数据记录:我可以立即看到结果，这是SQL Server Management Studio提供的帮助. 有人对此有更好的主意吗? 谢谢， 约翰

But I don't think the new way is better, in fact, I think it is even worse. In F# 2.0, I can use code to generate the values for data table, but if I have to write the static SQL statement, like "INSERT INTO Table1 VALUES(" with prefined values, then I think I would prefer to insert data records by hand from SQL Server Management Studio, as I can see the results immediately. Anyone has a better idea on this? Thanks, John

推荐答案

您可以使用 InsertOnSubmit 和 InsertAllOnSubmit 进行漂亮的，静态类型的记录插入.像这样:

You can use InsertOnSubmit and InsertAllOnSubmit for nice, statically typed record insertion. Something like:

let newRecords =
    [for i in [0 .. 1] ->
        new Table1(Id=(i + 1), TestData1=(i * 10), TestData2=( decimal i ) * 5.0M, Name="Testing" + (i + 1).ToString())]

db.Table1.InsertAllOnSubmit(newRecords)
db.SubmitChanges()

另请参见 http://msdn.microsoft.com/zh- us/library/hh361033(v = vs.110).aspx ，然后在页面中搜索"InsertOnSubmit"(实际上，该示例是您的Table1演示表的示例).

Also see http://msdn.microsoft.com/en-us/library/hh361033(v=vs.110).aspx and search within the page for "InsertOnSubmit" (in fact, the example there is made for your Table1 demo table).

这篇关于F#LINQ向SQL Server添加新行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！