Update database with Linq to SQL

  • 添加引用 System.Data.Linq

  • 定义表的结构

[Table(Name = "Customers")]
class Customer
{
    [Column(IsPrimaryKey = true)]
    public string CustomerID { get; set; }
 
    [Column]
    public string CompanyName { get; set; }
 
    [Column]
    public string ContactName { get; set; }
}
  • 编写测试用例
using System;
using System.Linq;
using System.Data.Linq;
using System.Data.Linq.Mapping;
using System.Data.SqlClient;

class Program
{
    public static void Main()
    {
        var conn = new SqlConnection("Server=(local);Database=NORTHWND;Trusted_Connection=True;");
        conn.Open();
        var ctx = new DataContext(conn);
        ctx.Log = Console.Out;
        var table = ctx.GetTable<Customer>();
        var q = from x in table where x.CustomerID == "ANTON" select x;
        var customer = q.Single();

        if (customer != null) {
            customer.CompanyName = $"<<New Name>>";
            try {
                ctx.SubmitChanges(ConflictMode.ContinueOnConflict);
            }
            catch (Exception ex) {
                Console.WriteLine(ex.Message);
            }
        }
    }
}
  • 运行结果
SELECT [t0].[CustomerID], [t0].[CompanyName], [t0].[ContactName]
FROM [Customers] AS [t0]
WHERE [t0].[CustomerID] = @p0
-- @p0: Input NVarChar (Size = 4000; Prec = 0; Scale = 0) [ANTON]
-- Context: SqlProvider(Sql2008) Model: AttributedMetaModel Build: 4.8.3752.0

UPDATE [Customers]
SET [CompanyName] = @p3
WHERE ([CustomerID] = @p0) AND ([CompanyName] = @p1) AND ([ContactName] = @p2)
-- @p0: Input NVarChar (Size = 4000; Prec = 0; Scale = 0) [ANTON]
-- @p1: Input NVarChar (Size = 4000; Prec = 0; Scale = 0) [Antonio Moreno Taquería]
-- @p2: Input NVarChar (Size = 4000; Prec = 0; Scale = 0) [Antonio Moreno]
-- @p3: Input NVarChar (Size = 4000; Prec = 0; Scale = 0) [<<New Name>>]
-- Context: SqlProvider(Sql2008) Model: AttributedMetaModel Build: 4.8.3752.0
  • 提示
经过测试Access数据库在使用LINQ TO SQL的过程中,查询可以,但是更新提交调用SubmitChanges时会报错(System.Data.Linq.ChangeConflictException: Row not found or changed),但是同样的代码连接到SQL Server就没有问题。