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

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.

I have two doubts, please advise:

1. If the entity must be obtained at the time of update and delete, and then the operation can be performed, I try to get the entity in the database without going to the databases at the time of update, but declare an entity myself and then delete it, but it fails.

2. In the generated update and delete conditional statements contain [email protected] and [email protected] statements, supposedly link to SQL already know the ID is the only primary key, why also pass these no conditions in the

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