前言
为了帮助同学们完成痛苦的实验课程设计,本作者将其作出的实验结果及代码贴至CSDN中,供同学们学习参考。如有不足或描述不完善之处,敬请各位指出,欢迎各位的斧正!
一、实验目的
1.了解数据源控件的使用;
2.熟练掌握LINQ表达式的使用;
3.熟练掌握利用LINQ to SQL进行数据访问管理的方法。
二、实验环境
Visual Studio 2019 ASP.Net Framework
三、实验内容
1、创建数据库和数据表。要求如下:
·根据所给的MyPetShop.sql文件建立实验所需的数据库
图1 MyPetShop数据库
2、建立数据管理的导航页面,效果如图2所示。
图2 数据管理导航页
·“显示全部”按钮可以显示Category表的所有内容。
·“模糊查找”按钮出现如图3的页面。当未找到满足条件的数据时显示“没有满足条件的数据!”
图3 模糊查找页面
·“插入”按钮出现如图4的页面。
图4 插入页面
·输入“分类ID”,单击“修改”按钮出现如图5的页面。
图5 修改分类数据的页面
·输入“分类Id”,单击“删除”按钮将删除指定的记录。
3、扩展题2的功能,利用LINQ to SQL查询商品名称中有字符c且价格在30元以上的商品。
4、查找资料,利用LINQ的“标准查询运算符”方法实现题3的功能。
四、代码及截图
1.DataManage.aspx代码
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="DataManage.aspx.cs" Inherits="DataManage" %>
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<meta http-equiv="Content-Type" content="text/html; charset=utf-8"/>
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
分类ID:<asp:TextBox ID="txtCategoryID" runat="server" Text="输入分类ID,只用于“修改”和“删除”" Width="290px" ></asp:TextBox><br/>
<asp:Button ID="btnQueryAll" runat="server" Text="显示全部" OnClick="btnQueryAll_Click" Width ="90px" Height ="30px" BackColor="#3399ff"></asp:Button>
<asp:Button ID="btnFuzzy" runat="server" Text="模糊查找" OnClick="btnFuzzy_Click" Width ="90px" Height ="30px" BackColor="#33cc33"></asp:Button>
<asp:Button ID="btnInsert" runat="server" Text="插入" OnClick="btnInsert_Click" Width ="50px" Height ="30px" BackColor="#3399ff"></asp:Button>
<asp:Button ID="btnUpdate" runat="server" Text="修改" OnClick="btnUpdate_Click" Width ="50px" Height ="30px" BackColor="#3399ff"></asp:Button>
<asp:Button ID="btnDelete" runat="server" Text="删除" OnClick="btnDelete_Click" Width ="50px" Height ="30px" BackColor="Red"></asp:Button>
<asp:Button ID="Button1" runat="server" Text="Linq查找" Height="30px" OnClick="Button1_Click" />
<br/>
<asp:GridView ID="gvCategory" runat="server"></asp:GridView>
</div>
</form>
</body>
</html>
设计结构图:
DataManage.aspx.cs代码:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text.RegularExpressions;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
public partial class DataManage : System.Web.UI.Page
{
MyPetShopDataContext db = new MyPetShopDataContext();
protected void Page_Load(object sender, EventArgs e)
{
}
protected void Bind()
{
var results = from c in db.Category select c;
gvCategory.DataSource = results;
gvCategory.DataBind();
}
protected void btnQueryAll_Click(object sender, EventArgs e)
{
Bind();
}
protected void btnFuzzy_Click(object sender, EventArgs e)
{
Response.Redirect("FuzzyQuery.aspx");
}
protected void btnInsert_Click(object sender, EventArgs e)
{
Response.Redirect("Insert.aspx");
}
protected int checknumber()
{
Regex numRegex = new Regex(@"^\d+$");
if (numRegex.IsMatch(txtCategoryID.Text.Trim())) return 1; else return 2;
}
protected void btnUpdate_Click(object sender, EventArgs e)
{
if (checknumber()==1)
Response.Redirect("Update.aspx?CategoryID=" + txtCategoryID.Text);
else Response.Write("<script>alert('请正确输入要修改行的序号!');</script>");
}
protected void btnDelete_Click(object sender, EventArgs e)
{
if (checknumber()==1)
{
var results = from c in db.Category where c.CategoryId == int.Parse(txtCategoryID.Text) select c;
db.Category.DeleteAllOnSubmit(results);
db.SubmitChanges();
Bind();
Response.Write("<script>alert('删除成功!');</script>");
}
else Response.Write("<script>alert('请正确输入要删除行的序号!');</script>");
}
protected void Button1_Click(object sender, EventArgs e)
{
Response.Redirect("LinqEdit.aspx");
}
}
FuzzyQuery.aspx源代码:
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="FuzzyQuery.aspx.cs" Inherits="FuzzyQuery" %>
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<meta http-equiv="Content-Type" content="text/html; charset=utf-8"/>
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
分类名:<asp:TextBox ID="txtSearch" runat="server"></asp:TextBox>
<asp:Button ID ="btnSearch" runat="server" Text="搜索" OnClick="btnSearch_Click"></asp:Button>
<asp:Button ID="btnReturn" runat="server" Text="返回" OnClick="btnReturn_Click"></asp:Button><br/>
<asp:GridView ID="gvCategory" runat="server"></asp:GridView>
<asp:Label ID="lblMsg" runat="server" ></asp:Label>
</div>
</form>
</body>
</html>
设计结构图:
FuzzyQuery.aspx.cs代码:
using System;
using System.Collections.Generic;
using System.Data.Linq.SqlClient;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
public partial class FuzzyQuery : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void btnSearch_Click(object sender, EventArgs e)
{
MyPetShopDataContext db = new MyPetShopDataContext();
var results = from c in db.Category where SqlMethods.Like(c.Name, "%" + txtSearch.Text + "%") select c;
if (results.Count() != 0)
{
gvCategory.DataSource = results;
gvCategory.DataBind();
}
else
{
lblMsg.Text = "很抱歉,本数据表中没有满足条件的数据!";
}
}
protected void btnReturn_Click(object sender, EventArgs e)
{
Response.Redirect("DataManage.aspx");
}
}
Insert.aspx源代码:
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Insert.aspx.cs" Inherits="Insert" %>
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<meta http-equiv="Content-Type" content="text/html; charset=utf-8"/>
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div style="text-align:left">
<table><tr><td>
<asp:Label ID="Label1" runat="server" Text="分类名:" Width="70px"></asp:Label></td><td><asp:TextBox ID="txtName" runat="server" Width="300px"></asp:TextBox></td></tr>
<tr><td>
<asp:Label ID="Label2" runat="server" Text="描述:" Width="70px"></asp:Label></td></tr>
</table>
<asp:TextBox ID="txtDescn" runat="server" Width="300px" TextMode="Multiline" Length="600px" Height="144px"></asp:TextBox><br/>
<asp:Button ID="btnInsert" runat="server" Text="插入" OnClick="btnInsert_Click" Width="50px" Height="30px"></asp:Button>
<asp:Button ID="btnReturn" runat="server" Text="返回" OnClick="btnReturn_Click" Width="50px" Height="30px"></asp:Button>
</div>
</form>
</body>
</html>
设计结构图:
Insert.aspx.cs代码:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
public partial class Insert : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void btnInsert_Click(object sender, EventArgs e)
{
try
{
MyPetShopDataContext db = new MyPetShopDataContext();
Category category = new Category();
category.Name = txtName.Text;
category.Descn = txtDescn.Text;
db.Category.InsertOnSubmit(category);
db.SubmitChanges();
Response.Write("<script>alert('插入成功!');location.href='DataManage.aspx';</script>");
}
catch (Exception ex)
{
Response.Write("<script>alert('您插入的数据有误,请重试!');</script>");
}
}
protected void btnReturn_Click(object sender, EventArgs e)
{
Response.Redirect("DataManage.aspx");
}
}
Update.aspx源代码:
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Update.aspx.cs" Inherits="Update" %>
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<meta http-equiv="Content-Type" content="text/html; charset=utf-8"/>
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<table><tr><td>
<asp:Label ID="Label1" runat="server" Text="分类ID:" Width="70px"></asp:Label></td><td><asp:TextBox ID="txtCategoryId" runat="server" Width="192px"></asp:TextBox></td></tr>
<tr><td>
<asp:Label ID="Label2" runat="server" Text="分类名:" Width="70px"></asp:Label></td><td><asp:TextBox ID="txtName" runat="server" Width="192px"></asp:TextBox></td></tr>
<tr><td>
<asp:Label ID="Label3" runat="server" Text="描述:" Width="70px"></asp:Label>
</td></tr>
</table>
<br />
<asp:TextBox ID="txtDescn" runat="server" TextMode="MultiLine" Height="100px" Width="192px"></asp:TextBox>
<table><tr><td><asp:Button ID="btnUpdate" runat="server" Text="修改" OnClick="btnUpdate_Click" Width="50px" Height="30px"></asp:Button></td><td><asp:Button ID="btnReturn" runat="server" Text="返回" OnClick="btnReturn_Click" Width="50px" Height="30px"></asp:Button></td></tr></table>
</div>
</form>
</body>
</html>
设计结构图:
Update.aspx.cs代码:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
public partial class Update : System.Web.UI.Page
{
MyPetShopDataContext db = new MyPetShopDataContext();
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
string categoryId = Request.QueryString["CategoryId"];
var category = (from c in db.Category where c.CategoryId == int.Parse(categoryId) select c).First();
txtCategoryId.Text = categoryId;
txtCategoryId.ReadOnly = true;
txtName.Text = category.Name;
txtDescn.Text = category.Descn;
}
}
protected void btnUpdate_Click(object sender, EventArgs e)
{
try
{
var category = (from c in db.Category where c.CategoryId == int.Parse(txtCategoryId.Text) select c).First();
category.Name = txtName.Text;
category.Descn = txtDescn.Text;
db.SubmitChanges();
Response.Write("<script>alert('修改成功!');location.href='DataManage.aspx';</script>");
}
catch(Exception ex)
{
Response.Write("<script>alert('您修改的方式有误,请重试!');</script>");
}
}
protected void btnReturn_Click(object sender, EventArgs e)
{
Response.Redirect("DataManage.aspx");
}
}
LinqEdit.aspx源代码:
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="LinqEdit.aspx.cs" Inherits="LinqEdit" %>
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<meta http-equiv="Content-Type" content="text/html; charset=utf-8"/>
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<table><tr><td>
<asp:Label ID="Label1" runat="server" Text="含有字符:"></asp:Label></td><td>
<asp:TextBox ID="txtchar" runat="server"></asp:TextBox></td></tr>
<tr><td>
<asp:Label ID="Label2" runat="server" Text="价格区间:(无穷为-1)"></asp:Label></td><td>
<asp:TextBox ID="txtPrice1" runat="server"></asp:TextBox></td><td>到</td><td>
<asp:TextBox ID="txtPrice2" runat="server"></asp:TextBox></td></tr>
<tr><td>
<asp:Button ID="btSearch" runat="server" Text="查询" Width="50px" Height="30px" OnClick="btSearch_Click" /></td><td>
<asp:Button ID="btCancle" runat="server" Text="取消" Width="50px" Height="30px" OnClick="btCancle_Click"/></td></tr>
</table>
</div>
<asp:GridView ID="gvCategory" runat="server"></asp:GridView>
<asp:Label ID="lblMsg" runat="server" ></asp:Label>
</form>
</body>
</html>
设计结构图:
LinqEdit.aspx.cs代码:
using System;
using System.Collections.Generic;
using System.Data.Linq.SqlClient;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
public partial class LinqEdit : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void btSearch_Click(object sender, EventArgs e)
{
MyPetShopDataContext db = new MyPetShopDataContext();
var results1 = db.CartItem.Where(r => r.ProName.Contains(txtchar.Text.Trim()));
var results2=results1;
if(txtPrice1.Text.Trim()!="-1"&&txtPrice2.Text.Trim()!="-1")
results2 = db.CartItem.Where(r => r.ListPrice > int.Parse(txtPrice1.Text.Trim())&&r.ListPrice<int.Parse(txtPrice2.Text.Trim()));
else if(txtPrice1.Text.Trim() == "-1"&& txtPrice2.Text.Trim() != "-1")
results2 = db.CartItem.Where(r => r.ListPrice < int.Parse(txtPrice1.Text.Trim()));
else if (txtPrice2.Text.Trim() == "-1" && txtPrice1.Text.Trim() != "-1")
results2= db.CartItem.Where(r => r.ListPrice > int.Parse(txtPrice1.Text.Trim()));
var results = results1.Intersect(results2);
//var results = from c in db.CartItem where (SqlMethods.Like(db.CartItem.ProName, "%" + txtchar.Text + "%")&& db.CartItem.ListPrice>int.Parse(txtPrice.Text.Trim())) select c;
if (results.Count() != 0)
{
gvCategory.DataSource = results;
gvCategory.DataBind();
}
else
{
lblMsg.Text = "很抱歉,本数据表中没有满足条件的数据!";
}
}
protected void btCancle_Click(object sender, EventArgs e)
{
Response.Redirect("DataManage.aspx");
}
}
附:增加数据后的CartItem表
运行界面截图:
单击显示全部:
单击模糊查找,输入En:
单击搜索得到结果:
单击返回回到主界面:
单击插入,输入分类名和描述:
单击插入,提示插入成功,并返回主界面:
不输入分类ID,直接点击修改,提示错误并返回主界面:
输入分类ID,点击修改:
修改描述及分类名,单击修改,提示修改成功并返回主界面:
不输入删除行号ID,直接点击删除,提示错误并返回主界面:
输入删除行ID,单击修改:
提示删除成功并返回主界面:
单击Linq查找:
输入题目中要求,单击查询:
注:题目中所要求范围是30以上(即不包括30)
附:增加数据后的CartItem表
单击取消回到主界面
五、实验总结
在实验三的编程过程中,我通过在网络中大量搜索关于LINQ to SQL的语句,结合数据库系统概论所学知识,对实验进行了反复尝试与修改,并最终对模糊查询、联合查询等数据库查找方式在ASP.NET环境下的实践有了更深的理解。整个实验最终达到了令人较为满意的效果。