Step by step to learn about LINQ to SQL (3): Add, query, modify, and delete

Preface

Today, I want to learn how to perform database operations, add, delete, and modify through LINQ. Preparations: first, a web project of ASP. net mvc 3.0 is established,

Create a database and table

 
Use [yqblog] gocreate table [DBO]. [tbguestbook] ([ID] [uniqueidentifier] not null primary key, [username] [varchar] (50) not null, [posttime] [datetime] not null, [Message] [varchar] (400) null, [isrequired] [bit] not null, [reply] [varchar] (400) null
)
 

Generate object class

Right-click the website project, select Add new item, and then select "LINQ to SQL classes", and name it guestbook. Open guestbook. dbml in app_code. The text in the design view shows that you can drag an item from the server resource manager or attack box to the design interface to create an object class.

In the server resource manager, create Guestbook Database data connection, and then Tbguestbook Drag a table Guestbook. dbml In the design view CTRL + S Save. Open Guestbook. Designer. CS The system automatically creates Guestbook Database Tbguestbook Table ing. Remember to set the primary key.

Homepage load list

First, create a global variable in homecontroller for simplicity.

 
Guestbookdatacontext CTX = new guestbookdatacontext (configurationmanager. connectionstrings ["yqblogconnectionstring"]. connectionstring );
 

Then, the background of the list to be loadedCodeAs follows:

 
Public actionresult index () {viewbag. Message = "Welcome to ASP. net mvc! "; List <tbguestbook> List = (from GB in CTX. tbguestbook orderby GB. posttime descending select GB). tolist (); Return view (" Index ", list );}
 

Let's take a look at the list view code.

@ {Viewbag. title = "Homepage" ;}@ using mvcoperation; @ model list <tbguestbook> <H2> @ viewbag. message </H2> <p> for more information about ASP. for more information about net MVC, visit <a href = "http://asp.net/mvc" Title = "ASP. net MVC Website "> http://asp.net/mvc </a>. </P> <div> <p> @ HTML. actionlink ("add", "addbook", "home ") </P> </div> <Table> <tr> <TH> name </Th> <TH> message </Th> <TH> posting time </ th> <TH> whether to reply </Th> <TH> operation </Th> </tr> @ {foreach (tbguestbook in Model) {<tr> <TD> @ book. username </TD> <TD> @ book. message </TD> <TD> @ book. posttime </TD> @ if (book. isrequired = true) {<TD> Yes </TD>} else {<TD> NO </TD >}< TD> @ HTML. actionlink ("delete", "delbook", "home", new {id = book. id}, null) @ HTML. actionlink ("modify", "updatebook", "home", new {id = book. id}, null) </TD> </TR >}</table> </div>

The list is displayed in this way.

Add

See the operation column above, delete and modify.

 
Public actionresult savebook (tbguestbook TB) {TB. id = guid. newguid (); TB. isrequired = false; TB. posttime = datetime. now; CTX. tbguestbook. insertonsubmit (TB); CTX. submitchanges (); Return Index ();}
 

Add the stored code and assign a simple value. You can submit it to the database using the special method of LINQ.

Let's take a look at the code of the added view.

<H2> addbook </H2> <div> @ using (HTML. beginform ("savebook", "home", forw.hod. post) {<Table> <tr> <TD> @ HTML. label ("name") </TD> <TD> @ HTML. textboxfor (C => C. username, new {@ style = "width: 300px;"}) </TD> </tr> <TD> @ HTML. label ("message") </TD> <TD> @ HTML. textareafor (C => C. message, new {@ style = "width: 300px; Height: 50px;"}) </TD> </tr> <TD> @ HTML. label ("whether to reply") </TD> <TD> Yes @ HTML. radiobuttonfor (C => C. isrequired, 1, true) No @ HTML. radiobuttonfor (C => C. isrequired, 0, false) </TD> </tr> <TD> <input type = "Submit" value = "add"/> </TD> <input type =" button "value =" return "/> </TD> </tr> </table> @ HTML. hiddenfor (C => C. ID)} </div>

Of course, this is also the modified view code.

Modify

 
Public actionresult updatebook (string ID) {tbguestbook GB = CTX. tbguestbook. single (B => B. id = new GUID (ID); Return view ("addbook", GB );}
 

When you click Modify in the list, you can find the data by passing the ID and bind it to the addbook view.

Public actionresult savebook (tbguestbook TB) {tbguestbook GB = NULL; If (Tb. Id. tostring ()! = "00000000-0000-0000-0000-000000000000") {GB = CTX. tbguestbook. single (B => B. id = new GUID (TB. id. tostring (); GB. posttime = datetime. now; GB. username = TB. username; GB. message = TB. message; CTX. submitchanges (); Return Index ();} else {TB. id = guid. newguid (); TB. isrequired = false; TB. posttime = datetime. now; CTX. tbguestbook. insertonsubmit (TB); CTX. submitchanges ();} Return Index ();}
 

This is the Save code for adding and modifying

Delete

Public actionresult delbook (string ID) {tbguestbook GB = CTX. tbguestbook. single (B => B. id = new GUID (ID); CTX. tbguestbook. deleteonsubmit (GB); CTX. submitchanges (); Return Index ();}
 

Summary

Simple instance code is like this, and it is quite simple on the surface.

Sample Code http://files.cnblogs.com/aehyok/LinqOperation.rar