看过了关于LINQ TO SQL 的介绍以及基本的原理之后,我们本次内容主要是做一个关于LINQ TO SQL 对数据库的增删改查,来加深我们对它的理解。
在创建项目之前,我们需要准备一个数据库表InStore.
(有关于次表的基础数据,请参考在前面的这篇博客 《sql T_sql 通用的动态按月份统计输出 》 )
备注:我们在做LINQ to SQL 之前,需要注意一个问题,就是创建的数据库表必须要有一个主键,本数据表InStore的主键是id
①,我们新建一个web应用程序项目,LinqDemo.
并添加新建项 LINQ to SQL 类,叫InStore.dbml ,如下图:
点击添加,接下来我们往该文件上添加一个数据库表:
此时我们会发现该文件的InStore.designer.cs 自动生成了很多相关的代码,有该数据表的实体类,操作类等。
②,为了方便我们后面做查询的需要,所以我们在这里顺便建一个PredicateBuilder.cs类,
代码如下:
using System;
using System.Linq;
using System.Linq.Expressions;
using System.Collections.Generic;
public static class PredicateBuilder
{
public static Expression<Funcbool>> True()
{
return f => true;
}
public static Expression<Funcbool>> False()
{
return f => false;
}
public static Expression<Funcbool>> Or(this Expression<Funcbool>> expr1, Expression<Funcbool>> expr2)
{
var invokedExpr = Expression.Invoke(expr2, expr1.Parameters.Cast<Expression>());
return Expression.Lambda<Funcbool>>(Expression.Or(expr1.Body, invokedExpr), expr1.Parameters);
}
public static Expression<Funcbool>> And(this Expression<Funcbool>> expr1, Expression<Funcbool>> expr2)
{
var invokedExpr = Expression.Invoke(expr2, expr1.Parameters.Cast<Expression>());
return Expression.Lambda<Funcbool>>(Expression.And(expr1.Body, invokedExpr), expr1.Parameters);
}
}
③,我们添加一个List.aspx页面,该页面的代码如下,
DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>无标题页title>
</head>
<body>
<form id="form1" runat="server">
<div>
<table width="60%" border="1">
<tr>
<td>
id:<asp:TextBox ID="txt_id" runat="server">asp:TextBox>
</td>
<td>
名称<asp:TextBox ID="txt_proname" runat="server">asp:TextBox>
</td>
<td>
数量:<asp:TextBox ID="txt_insertcount" runat="server">asp:TextBox>
</td>
<td>
<asp:Button ID="btnQuery" runat="server" OnClick="btnQuery_Click" Text="查询" />
<asp:Button ID="btndelete" runat="server" Text="删除" OnClick="btndelete_Click" />
<asp:Button ID="btnAdd" runat="server" Text="添加" OnClick="btnAdd_Click" />
<asp:Button ID="btnUpdate" runat="server" Text="修改" OnClick="btnUpdate_Click" />
</td>
</tr>
<tr>
<td colspan="4">
<asp:GridView ID="GridView1" runat="server" Width="100%" AutoGenerateColumns="False">
<Columns>
<asp:TemplateField>
<ItemTemplate>
<input type="checkbox" id="chk" runat="server" value=' ' />
ItemTemplate>
<ItemStyle Width="1px" />
asp:TemplateField>
<asp:BoundField DataField="id" HeaderText="id" />
<asp:BoundField DataField="proname" HeaderText="名称" />
<asp:BoundField DataField="insertday" HeaderText="入货时间" />
<asp:BoundField DataField="insertcount" HeaderText="入货数量" />
</Columns>
<RowStyle HorizontalAlign="Center" />
</asp:GridView>
</td>
</tr>
</table>
</div>
</form>
</body>
</html>
List.aspx.cs后台代码如下:
using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
namespace LinqDemo
{
public partial class List : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
btndelete.Attributes.Add("onclick", "javascript:return window.confirm('确定删除吗?')");
btnAdd.Attributes.Add("onclick", "javascript:return window.confirm('确定添加吗?')");
if (!IsPostBack)
{
Bind();
}
}
protected void btnQuery_Click(object sender, EventArgs e)
{
Bind();
}
/// <summary>
/// 查询函数
/// </summary>
private void Bind()
{
var predicate = PredicateBuilder.True<InStore>();
if (!string.IsNullOrEmpty(txt_proname.Text))
{
predicate = predicate.And(p => p.ProName.Contains(txt_proname.Text));
}
if (!string.IsNullOrEmpty(txt_insertcount.Text))
{
predicate = predicate.And(p => p.InsertCount.Equals(txt_insertcount.Text));
}
InStoreDataContext db = new InStoreDataContext();
GridView1.DataSource = db.InStore.Where(predicate);
GridView1.DataBind();
}
/// <summary>
/// 添加函数
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void btnAdd_Click(object sender, EventArgs e)
{
InStoreDataContext db = new InStoreDataContext();
InStore store = new InStore();
store.Id = int.Parse(txt_id.Text);
store.InsertCount = int.Parse(txt_insertcount.Text);
store.InsertDay = DateTime.Now;
store.ProName = txt_proname.Text;
db.InStore.InsertOnSubmit(store);
db.SubmitChanges();
txt_insertcount.Text = "";
txt_proname.Text = "";
txt_id.Text = "";
Bind();
}
/// <summary>
/// 删除函数
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void btndelete_Click(object sender, EventArgs e)
{
InStoreDataContext db = new InStoreDataContext();
for (int i = 0; i < GridView1.Rows.Count; i++)
{
HtmlInputCheckBox chk = (HtmlInputCheckBox)GridView1.Rows[i].FindControl("chk");
if (chk.Checked)
{
InStore store = db.InStore.First(p => p.Id.Equals(int.Parse(chk.Value)));
db.InStore.DeleteOnSubmit(store);
db.SubmitChanges();
}
}
Bind();
}
/// <summary>
/// 修改函数
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void btnUpdate_Click(object sender, EventArgs e)
{
InStoreDataContext db = new InStoreDataContext();
for (int i = 0; i < GridView1.Rows.Count; i++)
{
HtmlInputCheckBox chk = (HtmlInputCheckBox)GridView1.Rows[i].FindControl("chk");
if (chk.Checked)
{
InStore store = db.InStore.First(p => p.Id.Equals(int.Parse(chk.Value)));
Response.Redirect("edit.aspx?id=" + chk.Value + "");
}
}
}
}
}
④,我们再添加一个Edit.aspx页面,代码如下:
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Edit.aspx.cs" Inherits="LinqDemo.Edit" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>无标题页</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<table width="60%" border="1">
<tr>
<td>
id:<asp:TextBox ID="txt_id" runat="server" Enabled="false"></asp:TextBox>
</td>
<td>
名称<asp:TextBox ID="txt_proname" runat="server"></asp:TextBox>
</td>
<td>
数量:<asp:TextBox ID="txt_insertcount" runat="server"></asp:TextBox>
</td>
<td>
<asp:Button ID="btnSummit" runat="server" OnClick="btnSummit_Click" Text="确定" />
</td>
</tr>
</table>
</div>
</form>
</body>
</html>
Edit.aspx.cs代码如下:
using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
namespace LinqDemo
{
public partial class Edit : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
btnSummit.Attributes.Add("onclick", "javascript:return window.confirm('确定修改吗?')");
if (!IsPostBack)
{
InStoreDataContext db = new InStoreDataContext();
InStore store = db.InStore.First(p => p.Id.Equals(Request["id"].ToString()));
txt_id.Text = store.Id.ToString();
txt_insertcount.Text = store.InsertCount.ToString();
txt_proname.Text = store.ProName;
}
}
protected void btnSummit_Click(object sender, EventArgs e)
{
InStoreDataContext db = new InStoreDataContext();
//获取当前instore表的数据行
InStore store = db.InStore.First(p => p.Id.Equals(Request["id"].ToString()));
//对需要修改的数据进行替换
store.InsertCount = int.Parse(txt_insertcount.Text);
store.ProName = txt_proname.Text;
//最后提交修改
db.SubmitChanges();
Response.Redirect("List.aspx");
}
}
}
⑤,我们编译后,运行List.aspx页面,如图所示:
我们来添加一条数据,如下图:
点击确定后,我们发现,该数据已经提交到表InStore中了。
其它的删除,查询,修改等操作,我这里就不一一介绍了。