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

Source: Internet
Author: User

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)

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.