Use a database in webmatrix

Create a database

1. Use an empty website template to create a siteProgram.

2. Click new database in the database workspace.

3. webmatrix uses the site name to create a database with the. SDF file by default. At this time, there is nothing in the database. Create a new table and click the new table button.

Creating a table column is very easy. You can use the new column.

4. Create the first column named ID and specify the column ID and primary key respectively.

The ID indicates that the data in this column is automatically filled with a unique label. By default, each record in the database is incremented by 1 and starts with 0. Therefore, the value allocated for the first record is 1, the second allocation is 2, and so on.

5. Next, add the following three columns: name, nick name, and address.

6. Click Save and the table name is stored as contacts.

7. Double-click the table to view the table content, but there is no database. When you see this interface like a workbook, you will immediately realize that it supports direct editing.

8. You do not need to enter any information in the ID column. It is automatically filled. You can enter other content items. If a table without some human content automatically displays null content, it indicates that there is no data in the data grid.

The preceding figure shows how easy it is to create a database in webmatrix and use the inheritance interface to fill in data.

In Code Database Used in

The most standard way to use databases is to use SQL queries. Webmatrix also supports queries:

1. We use cshtml to render a page for retrieving data from the database. The Code is as follows:

@{
Layout = "~ /_ Layout. cshtml ";
Page. Title = "Database Operations ";
VaR DB = database. Open ("Demo ");
VaR query = "Select name from contacts ";
}
@ Foreach (VAR item in db. Query (query )){
<P> <span> @ item. Name </span> </P>
}

2. After running, the page is roughly as follows, because the content in our _ layout. cshtml may be different:

Add data to a database

1. Add HTML form elements to the page:

<Form method = "Post">
<P> name </P>
<Input type = "text" name = "name"/>
<P> nickname </P>
<Input type = "text" name = "nickname"/>
<P> address </P>
<Input type = "text" name = "Address"/>
<Input type = "Submit" value = "Submit"/>
</Form>

2. Then add the dynamic code:

@{

VaR name = request ["name"];
VaR nickname = request ["nickname"];
VaR address = request ["Address"];

}

3. Because the post submission method is adopted, we add If (ispost) {} to process the page response from post.

4. Use the INSERT command in SQL to add new records to the database:

Insert into contacts (name, nickname, address) values ('older', 'Uncle koala ', 'conservatory beauty concentration camp ')

The placeholder can be used in the code to replace the specific content to be assigned, which is roughly as follows:

Insert into contacts (name, nickname, address) values (@ 0, @ 1, @ 2)

@ 0 indicates the first parameter, @ 1 indicates the second parameter, and @ 2 indicates the third parameter.

So let's take a look at how to set it in the Code:

If (ispost ){
VaR insertq = "insert into contacts (name, nickname, address) values (@ 0, @ 1, @ 2 )";
DB. Execute (insertq, name, nickname, address );
}
5. The execution page shows the following results:

6. Page results after submission:

Oh, we found that a record has been added to the database. This record is exactly from my webpage.

Edit data

We have added a record above. What if we update an existing record?

I want to confirm the next data record to be edited, then tell the database that we want to edit it, and then let the database know the record we want to edit. It is best to give it a clear information, we generally use the form of notifying the database primary key to determine! Here is the ID column!

1. In the previous query code, we only display the name:

VaR query = "Select name from contacts ";

Now we need to retrieve the value of the primary key and modify it as follows:

VaR query = "select ID, name from contacts ";

2. Update the HTML code as follows:

@ Foreach (VAR item in db. Query (query )){
<P> <span> @ item. Name </span> <a href = "Edit. cshtml? Id = @ item. ID "> edit </a> </P>
}

Refresh the browser and you will get the following results:

ViewSource codeIt is roughly as follows:

<P> <span> Meng xiangyuan </span> <a href = "Edit. cshtml? Id = 1 "> edit </a> </P>
<P> <span> Gu Yungang </span> <a href = "Edit. cshtml? Id = 2 "> edit </a> </P>
<P> <span> Chen </span> <a href = "Edit. cshtml? Id = 3 "> edit </a> </P>
<P> <span> old employee </span> <a href = "Edit. cshtml? Id = 4 "> edit </a> </P>

3. Now let's start to edit the page and create an edit. cshtml file.

4. We also add HTML information such as name, nickname, and address on the page:

<Div id = "edititem">
<Form method = "Post">
<P> name </P>
<Input type = "text" name = "name"/>
<P> nickname </P>
<Input type = "text" name = "nickname"/>
<P> address </P>
<Input type = "text" name = "Address"/>
<Input type = "Submit" value = "Submit"/>
</Form>
</Div>

5. Then, you need to make a method to obtain the corresponding ID record in the server code:

@{
Layout = "~ /_ Layout. cshtml ";
VaR id = request ["ID"];
VaR query = "";
VaR DB = database. Open ("Demo ");
VaR name = "";
VaR nickname = "";
VaR address = "";

If (Id. isempty ()){
Response. Redirect ("data. cshtml ");
}
Else {
Query = "select * from contacts where id = @ 0 ";
VaR item = dB. querysingle (query, ID );
Name = item. Name. Trim ();
Nickname = item. Nickname. Trim ();
Address = item. Address. Trim ();
}
}
<Div id = "edititem">
<Form method = "Post">
<P> name </P>
<Input type = "text" name = "name" value = "@ name"/>
<P> nickname </P>
<Input type = "text" name = "nickname" value = "@ nickname"/>
<P> address </P>
<Input type = "text" name = "Address" value = "@ address"/>
<Input type = "Submit" value = "save"/>
</Form>
</Div>

The preceding Code returns an SQL query result returned from the URL ID parameter. You can use the querysingle method to query a single record. The TRIM method removes leading and trailing spaces of the result.

6. Save the page, browse the list, and click the edit link to go to the following page:

We get the above page, which is exactly all the data returned Based on the ID content we provide.

7. At present, there is no way to save the UPDATE function. The next thing will be as simple as submitting new data, so that we can completely complete the edit. cshtml code:

@{
Layout = "~ /_ Layout. cshtml ";
VaR id = request ["ID"];
VaR query = "";
VaR DB = database. Open ("Demo ");
VaR name = "";
VaR nickname = "";
VaR address = "";

If (Id. isempty ()){
Response. Redirect ("data. cshtml ");
}
Else {
Query = "select * from contacts where id = @ 0 ";
VaR item = dB. querysingle (query, ID );
Name = item. Name. Trim ();
Nickname = item. Nickname. Trim ();
Address = item. Address. Trim ();
}

If (ispost ){
Name = request ["name"];
Nickname = request ["nickname"];
Address = request ["Address"];
VaR updateq = "Update contacts set name = @ 0, nickname = @ 1, address = @ 2 where id = @ 3 ";
DB. Execute (updateq, name, nickname, address, ID );
Response. Redirect ("~ /Data. cshtml ");
}
}
<Div id = "edititem">
<Form method = "Post">
<P> name </P>
<Input type = "text" name = "name" value = "@ name"/>
<P> nickname </P>
<Input type = "text" name = "nickname" value = "@ nickname"/>
<P> address </P>
<Input type = "text" name = "Address" value = "@ address"/>
<Input type = "Submit" value = "save"/>
</Form>
</Div>

8. Make some changes on the page and save the changes:

9. Click the Save button and we will find these changes in data. cshtml:

Delete data records

1. Just like the link on the editing page, we need to delete the page and add the link to it:

@ Foreach (VAR item in db. Query (query )){
<P> <span> @ item. Name </span> <a href = "Edit. cshtml? Id = @ item. ID "> edit </a> <a href =" delete. cshtml? Id = @ item. ID "> Delete </a> </P>
}
2. Run this page:

3. Add a page named Delete. cshtml. the HTML code is as follows:

<Div id = "delete">
<Form method = "Post">
<P> are you sure you want to delete the following content? </P>
<P> name </P>
<Input type = "text" name = "name"/>
<P> nickname </P>
<Input type = "text" name = "nickname"/>
<P> address </P>
<Input type = "text" name = "Address"/>
<Input type = "checkbox" name = "delete" value = "Confirm"/> are you sure you want to delete this record.
<Input type = "Submit" value = "Submit"/>
</Form>
</Div>

4. Add the server code as follows:

@{
Layout = "~ /_ Layout. cshtml ";
VaR id = request ["ID"];
VaR query = "";
VaR DB = database. Open ("Demo ");
VaR name = "";
VaR nickname = "";
VaR address = "";
If (Id. isempty ()){
Response. Redirect ("data. cshtml ");
}
Else {
Query = "select * from contacts where id = @ 0 ";
VaR item = dB. querysingle (query, ID );
Name = item. Name. Trim ();
Nickname = item. Nickname. Trim ();
Address = item. Address. Trim ();
}
If (ispost ){
VaR confirm = request ["delete"];
If (confirm = "Confirm "){
VaR deleteq = "delete from contacts where id = @ 0 ";
DB. Execute (deleteq, ID );
}
Response. Redirect (@ href ("~ /Data. cshtml "));
}
}
<Div id = "delete">
<Form method = "Post">
<P> are you sure you want to delete the following content? </P>
<P> name: @ name </P>
<P> nickname: @ nickname </P>
<P> address: @ address </P>
<Input type = "checkbox" name = "delete" value = "Confirm"/> are you sure you want to delete this record.
<Input type = "Submit" value = "Submit"/>
</Form>
</Div>

7. Run page:

Now, the deletion operation is complete!