ASP. NET 4.0 and Entity Framework 4-Article 2-Use Entity Framework for crud operations

This article describes how to use entiity framework to add, delete, modify, and query data in SQL Server. The database used in this article is the previous one.ArticleCreated in model-first development mode. Of course, you also need to download Visual Studio 2010 to open the project file of the previous article. Download link of the project file: Click to download.

 

Step 1: Modify the database in the first article

In the first article, we used EF4 to create two entities. They are useraccount and address. Each object has a primary key and multiple fields named ID. However, the article did not tell you how to set the primary key to the identity field. The advantage of setting it to the identity field is that the primary key can be auto-incrementing.

 

1. Open the project file used in the first article.

2. Double-click the orderdb. edmx file of Solution Explorer to view the Entity Framework designer.

3. Click the ID field of useraccount to view its attributes (Alt + enter ).

4. Set storegeneratedpattern to identity, and the address entity performs the same operation.

5. The database must be updated synchronously when the entity is updated. Right-click the view designer and select "generate database from model". At this time, Vs will prompt you whether to overwrite the orderdb. edmx. SQL file and click "yes ".

6. After overwriting, Vs will automatically open the orderdb. edmx. SQL file, right-click the menu and select "Execute SQL ".

At this time, Vs will prompt you to enter the data connection information, and then click "Connect.

 

Step 2: create a web form

The followingProgramCreate a form to manage useraccount data.

1. Right-click the project file and choose add-> new item ..

2. Select the web form template, change the name to users. aspx, and click "add ".

3. Add the following between the user. aspx Div:Code:

<Table> <tr> <TD> select a user: </TD> <asp: dropdownlist runat = server id = "ddlusers" autopostback = "true"> </ASP: dropdownlist> </TD> </tr> <TD> First name: </TD> <asp: textbox runat = "server" id = "txtfirstname"> </ASP: textbox> </TD> </tr> <TD> last name: </TD> <asp: textbox runat = "server" id = "txtlastname"> </ASP: textbox> </TD> </tr> <TD> inserted: </TD> <asp: Label runat = "server" id = "lblinserted"> </ASP: label> </TD> </tr> <TD> updated: </TD> <asp: label runat = "server" id = "lblupdated"> </ASP: Label> </TD> </tr> </table> <asp: button runat = server id = "btnsave" text = "save"/> <asp: button id = "btndelete" runat = "server" text = "delete"/>

Here, the table is used to make a simple layout for the form. Go to the design view and you will see the form as follows:

 

Step 3: load data to the drop down list

To load a page, load the useraccount name and ID data to the drop down list. Load more detailed information when selecting a specific item.

1. Double-click degsin view (F7) and add the page_load event in the background code.

2. The code for processing the page_load event is as follows:

 
Protected void page_load (Object sender, eventargs e) {If (! Ispostback) {loaduserdropdownlist ();}}
 

3. The code for the loaduserdropdownlist method in the page_load method is as follows:

 private void loaduserdropdownlist () {using (orderdbcontainer DB = new orderdbcontainer ()) {ddlusers. datasource = from u in dB. useraccounts orderby U. lastname select new {name = u. lastname + "," + U. firstname, id = u. id}; ddlusers. datatextfield = "name"; ddlusers. datavaluefield = "ID"; ddlusers. databind (); ddlusers. items. insert (0, new listitem ("create new user", "") ;}

This method creates an orderdbcontainer class instance, which is automatically generated when orderdb. edmx is created. Its function is similar to connction In ADO. net. We use orderdbcontainer to connect to the database and operate the objects in the database. The data source of the drop down list is a LINQ query. Of course, EF will translate it into SQL statements. As if the SQL statements are written in reverse mode, the from statement is in the front, while the SELECT statement is in the back. In this example, all useraccount data is queried and sorted by last name. The name and ID columns are selected at the same time. Names are separated by commas.

The datatextfield attribute is set to name and the datavaluefield is set to ID, which are created in the LINQ query. After the configuration is complete, it is bound. When binding, you can call the database query operation. Finally, add "crate new user." To the drop down list to differentiate between update and add operations.

Currently, there is no data in the database, and the drop down list contains only "Crete new user. If you open SQL Server Profiler, the SQL statement generated by EF4 is as follows:

Select [project1]. [ID] as [ID], [project1]. [C1] as [C1] From (select [extent1]. [ID] as [ID], [extent1]. [lastname] as [lastname], [extent1]. [lastname] + N', '+ [extent1]. [firstname] as [C1] from [DBO]. [useraccounts] as [extent1]) as [project1] Order by [project1]. [lastname] ASC
 

Step 4: Add and update data

The following describes how to add and update data in a table.

1. Go to the design view and double-click the Save button to create the click event for this button.

2. The code for handling click events is as follows:

Using (orderdbcontainer DB = new orderdbcontainer () {useraccount = new useraccount (); useraccount. firstname = txtfirstname. text; useraccount. lastname = txtlastname. text; useraccount. auditfields. updatedate = datetime. now; If (ddlusers. selecteditem. value = "") {// Adding useraccount. auditfields. insertdate = datetime. now; dB. useraccounts. addobject (useraccount);} else {// updating useraccount. id = convert. toint32 (ddlusers. selectedvalue); useraccount. auditfields. insertdate = convert. todatetime (lblinserted. text); dB. useraccounts. attach (useraccount); dB. objectstatemanager. changeobjectstate (useraccount, system. data. entitystate. modified);} dB. savechanges (); lblinserted. TEXT = useraccount. auditfields. insertdate. tostring (); lblupdated. TEXT = useraccount. auditfields. updatedate. tostring (); // reload the drop down list loaduserdropdownlist (); // select the one the user just saved. ddlusers. items. findbyvalue (useraccount. id. tostring ()). selected = true ;}}

The code first creates the orderdbcontainer object, then creates the useraccount object, and fills in the attributes of the useraccount object with the input value. The update date uses the current system time, and then determines whether the update operation or add operation is performed. The last step is to update the value of the drop down list and select the useraccout in the previous operation. Here dB. savechanges () is actually executed in the database to add or update the statement.

 

Step 5. query data

The following describes how to display detailed information when you select a drop down list item.

1. Double-click the drop down list in the view designer to create the selectedindexchanged method for the drop down list.

2. The code for compiling the selectedindexchanged method is as follows:

If (ddlusers. selectedvalue = "") {txtfirstname. TEXT = ""; txtlastname. TEXT = ""; lblinserted. TEXT = ""; lblupdated. TEXT = "";} else {// get the user from the DB using (orderdbcontainer DB = new orderdbcontainer () {int useraccountid = convert. toint32 (ddlusers. selectedvalue); List <useraccount> useraccounts = (from u in dB. useraccounts where u. id = useraccountid select U ). tolist (); If (useraccounts. count ()> 0) {useraccount = useraccounts [0]; txtfirstname. TEXT = useraccount. firstname; txtlastname. TEXT = useraccount. lastname; lblinserted. TEXT = useraccount. auditfields. insertdate. tostring (); lblupdated. TEXT = useraccount. auditfields. updatedate. tostring ();} else {// error: Didn't find user. txtfirstname. TEXT = ""; txtlastname. TEXT = ""; lblinserted. TEXT = ""; lblupdated. TEXT = "";}}}

The Code queries a piece of data from the database and displays it according to the ID selected by the drop down list.

Step 6: delete data

The last step is to delete data. 1. Go to the view designer and double-click "delete. 2. Add the following code:

 
If (ddlusers. selecteditem. value! = "") {Using (orderdbcontainer DB = new orderdbcontainer () {useraccount = new useraccount (); useraccount. id = convert. toint32 (ddlusers. selectedvalue); dB. useraccounts. attach (useraccount); dB. objectstatemanager. changeobjectstate (useraccount, system. data. entitystate. deleted); dB. savechanges (); loaduserdropdownlist (); txtfirstname. TEXT = ""; txtlastname. TEXT = ""; lblinserted. TEXT = ""; lblupdated. TEXT = "";}}
 

The code first creates a useraccount object, sets its ID as the ID of the selected item, attaches the useraccount to the useraccount collection, and sets its status to delete. Call the savechanges operation to delete the data and refresh the data source in the drop down list!

Summary

It can be found that EF4 can easily deal with the general data addition, deletion, modification, and query operations.

In the next article, I will show you how to use the Stored Procedure for addition, deletion, modification, and query operations without using the SQL statements generated by EF.

Related project files: Click to download

Independent blog links: http://lloydsheng.com/2010/04/aspnet40-entityframework4-how-to-crud-using-entityframework.html