How LINQ to SQL series inserts, modifies, and deletes data using LINQ to SQL

Http://www.cnblogs.com/yukaizhao/archive/2010/05/13/linq_to_sql_1.html

LINQ and LINQ to SQL are no longer a new thing, but I am in touch with the relatively late, in the spirit of knowing this matter to preach attitude, decided to write this series.

The test environment that this article uses is vs 2010, and the SQL Server 2005 database.

1. Starting with cud, how to insert, modify, and delete data using LINQ to SQL

2. Query using LINQ to SQL for simple queries

3. Query lazy load with immediate load, using LoadWith and AssociateWith

4. Query Inner join,left outer JOIN

5. Aggregation grouping in Linq to SQL have

6. LINQ to SQL query optimization, do you need to worry about performance?

First article from cud, how to insert, modify, and delete data using LINQ to SQL

Preparation, now the database is built in the test table student, the table has only three fields Id,name,hometown, where the ID is an int type of self-growing field, Name and Howmtown is the nvarchar type

1. Open VS2010 new Console Application and add LINQ to SQL Class, named Dbapp.dbml, after creating a new dbml file, you can open the Server Explorer, establish a database connection, and drag our newly created table to the dbml file. Results such as

2. You can modify the context and the namespace of the generated entity by clicking the dbml file blank, pressing F4 to display the DBML property.

3. So far, VS2010 has created a data table for us by tool. Add, change, and delete the method of adding, changing, and deleting the tables, and now start practicing

1) Adding add

?

123456789101112131415161718

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 statements for output

?

1234567

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) [Zhang Xiao ii] --@p1: Input NVarChar (Size = 4000; Prec = 0; Scale = 0) [Nanhai Kannon temple] --Context:sqlprovider (Sql2005) Model:attributedmetamodel Build: 4.0.30319.1

2) perform edit edits using LINQ to SQL

?

1234567891011121314151617181920212223242526

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 statements for output

?

123456789101112131415

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.1UPDATE [dbo].[Student]SET [Name] = @p3, [Hometown] = @p4WHERE ([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) Perform a delete operation using LINQ to SQL

Execute code:

?

123456789101112131415161718192021

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");}

Generated SQL statement:

?

123456789101112

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) [Zhangxiao] --@p2: Input NVarChar (Size = 4000; Prec = 0; Scale = 0) [Zhangjiakou Zhang Jia Zhai Zhang Jia Ying] --context:sqlprovider (Sql2005) Model:attributedmetamodel build:4.0.30319.1

Summary: It is very convenient to use LINQ to SQL in the above practice to perform the increment and delete operations, and we do not even need to learn any SQL-related knowledge.

How LINQ to SQL series inserts, modifies, and deletes data using LINQ to SQL (GO)