第一篇 从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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
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语句
1
2
3
4
5
6
7
|
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 编辑操作
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
|
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语句
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
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 执行删除操作
执行代码:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
|
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语句:
1
2
3
4
5
6
7
8
9
10
11
12
|
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
|
总结:通过以上实践可以看到使用linq to sql执行增改删操作,非常方便,我们甚至不需要学习任何sql相关的知识。
我有两点疑惑,请各位指点:
1. 是否是在执行update和delete时必须先获得实体,然后才能执行操作,我尝试在update时,不去数据库中获取实体,而是自己声明一个实体,然后去删除,但是失败了
2. 在生成的update和delete的条件语句中包含name=@p和hometown=@p的语句,按理说link to sql已经知道id是唯一的主键,为什么还会传这些没有的条件进去的