LINQ to SQL 系列如何使用LINQ to SQL插入、修改、删除数据-优快云博客

本文详细介绍了如何使用LINQ to SQL在Visual Studio 2010中进行数据库操作，包括添加、修改和删除数据的步骤及生成的SQL语句。通过实践演示，展示了LINQ to SQL简化了数据库操作过程，无需深入学习SQL知识。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

第一篇从CUD开始，如何使用LINQ to SQL插入、修改、删除数据

准备工作，现在数据库中建好测试表Student，这个表只有三个字段ID,Name,Hometown，其中ID为int类型的自增长字段，Name和Howmtown是nvarchar类型

1. 打开VS2010新建控制台应用程序，然后添加LINQ to SQL Class，命名为DbApp.dbml，新建dbml文件之后，可以打开server explorer，建立数据库连接，并将我们新建的表拖到dbml文件中，结果如下图

2．可以通过点击dbml文件空白处，按F4显示dbml属性，可以修改Context和生成实体的命名空间

3．到现在为止VS2010通过工具为我们创建好了数据表对应实体类和数据表操作添，改，删的方法，现在开始实践

1）添加 Add

static void Add()
{
    //添加一个Student
    Student aStudent = new Student
    {
        Name = "张小二",
        Hometown = "南海观音院"
    };
    Console.WriteLine("----------begin Add a student");
    using (DbAppDataContext db = new DbAppDataContext())
    {
        db.Log = Console.Out;
        db.Students.InsertOnSubmit(aStudent);
        db.SubmitChanges();
    }
 
    Console.WriteLine("----------End Add a student");
}

输出的sql语句

INSERT INTO [dbo].[Student]([Name], [Hometown])
VALUES (@p0, @p1)
 
SELECT CONVERT(Int,SCOPE_IDENTITY()) AS [value]
-- @p0: Input NVarChar (Size = 4000; Prec = 0; Scale = 0) [张小二]
-- @p1: Input NVarChar (Size = 4000; Prec = 0; Scale = 0) [南海观音院]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 4.0.30319.1

2) 使用linq to sql执行Edit 编辑操作

private static void Edit(int id)
{
    Console.WriteLine("----------begin edit");
    using (DbAppDataContext db = new DbAppDataContext())
    {
        db.Log = Console.Out;
 
    //取出student
    var editStudent = db.Students.SingleOrDefault<Student>(s=>s.ID == id);
 
    if (editStudent == null)
    {
        Console.WriteLine("id错误");
        return;
    }
 
    //修改student的属性
    editStudent.Name = "张小三";
    editStudent.Hometown = "张家口张家寨张家营";
 
    //执行更新操作
    db.SubmitChanges();
 
    }
    Console.WriteLine("---------end edit Student");
}

输出的sql语句

SELECT [t0].[ID], [t0].[Name], [t0].[Hometown]
FROM [dbo].[Student] AS [t0]
WHERE [t0].[ID] = @p0
-- @p0: Input Int (Size = -1; Prec = 0; Scale = 0) [6]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 4.0.30319.1
 
UPDATE [dbo].[Student]
SET [Name] = @p3, [Hometown] = @p4
WHERE ([ID] = @p0) AND ([Name] = @p1) AND ([Hometown] = @p2)
-- @p0: Input Int (Size = -1; Prec = 0; Scale = 0) [6]
-- @p1: Input NVarChar (Size = 4000; Prec = 0; Scale = 0) [张小二]
-- @p2: Input NVarChar (Size = 4000; Prec = 0; Scale = 0) [南海观音院]
-- @p3: Input NVarChar (Size = 4000; Prec = 0; Scale = 0) [张小三]
-- @p4: Input NVarChar (Size = 4000; Prec = 0; Scale = 0) [张家口张家寨张家营]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 4.0.30319.1

3）使用linq to sql 执行删除操作

执行代码：

static void Delete(int id)
{
    Console.WriteLine("-----------begin delete a student");
    using (DbAppDataContext db = new DbAppDataContext())
    {
        db.Log = Console.Out;
        //取出student
        var student = db.Students.SingleOrDefault<Student>(s => s.ID == id);
 
        if (student == null)
        {
            Console.WriteLine("student is null");
            return;
        }
 
        db.Students.DeleteOnSubmit(student);
 
        db.SubmitChanges();
    }
    Console.WriteLine("------------end Delete student");
}

生成的sql语句：

SELECT [t0].[ID], [t0].[Name], [t0].[Hometown]
FROM [dbo].[Student] AS [t0]
WHERE [t0].[ID] = @p0
-- @p0: Input Int (Size = -1; Prec = 0; Scale = 0) [6]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 4.0.30319.1
 
DELETE FROM [dbo].[Student] WHERE ([ID] = @p0) AND ([Name] = @p1) AND ([Hometown
] = @p2)
-- @p0: Input Int (Size = -1; Prec = 0; Scale = 0) [6]
-- @p1: Input NVarChar (Size = 4000; Prec = 0; Scale = 0) [张小三]
-- @p2: Input NVarChar (Size = 4000; Prec = 0; Scale = 0) [张家口张家寨张家营]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 4.0.30319.1