简易留言簿
现在,我们就可以使用 Linq to sql 完成简易留言簿了。实现以下功能:
发表留言(增)
查看留言(查)
管理员回复留言(改)
管理员删除留言(删除)
首先,创建一个 Default.aspx,在页面上加入一些控件:
<div>
姓名
<asp:TextBox ID="tb_UserName" runat="server"></asp:TextBox><br />
<br />
留言
<asp:TextBox ID="tb_Message" runat="server" Height="100px"
TextMode="MultiLine" Width="300px"></asp:TextBox><br />
<br />
<asp:Button ID="btn_SendMessage" runat="server" Text="发表留言"
OnClick="btn_SendMessage_Click" /><br />
<br />
<asp:Repeater ID="rpt_Message" runat="server">
<ItemTemplate>
<table width="600px" style="border:solid 1px #666666; font-size:10pt;
background-color:#f0f0f0">
<tr>
<td align="left" width="400px">
<%# Eval("Message")%>
</td>
<td align="right" width="200px">
<%# Eval("PostTime")%> - <%# Eval("UserName")%>
</td>
</tr>
<tr>
<td colspan="2" align="right">
<hr width="300px" />
管理员回复:<%# Eval("IsReplied").ToString() == "False" ? "暂无" :
Eval("Reply")%>
</td>
</tr>
</table>
<br/>
</ItemTemplate>
</asp:Repeater>
</div>
你可能很难想象,使用 Linq to sql 进行数据访问会是这么简单,后台代码:
public partial class _Default : System.Web.UI.Page
{
GuestBookDataContext ctx = new
GuestBookDataContext("server=xxx;database=GuestBook;uid=xxx;pwd=xxx");
protected void Page_Load(object sender, EventArgs e)
Beijing ZJS Express Stock Limited Company
Address: The 11th Floor, Zhaowei Building, Jiangtai Road, Chaoyang District of Beijing.
Postcode: 100016 Name: Liu Xiaohui Email: Xiaohui_liu0406@163.com
Tel: 13488810897 Office: 010-84561144-1816
Page 23 of 113
{
if (!IsPostBack)
DoNet Framework 3.5 系列
{
SetBind();
}
}
protected void btn_SendMessage_Click(object sender, EventArgs e)
{
tbGuestBook gb = new tbGuestBook();
gb.ID = Guid.NewGuid();
gb.UserName = tb_UserName.Text;
gb.Message = tb_Message.Text;
gb.IsReplied = false;
gb.PostTime = DateTime.Now;
ctx.tbGuestBooks.Add(gb);
ctx.SubmitChanges();
SetBind();
}
private void SetBind()
{
Beijing ZJS Express Stock Limited Company
Address: The 11th Floor, Zhaowei Building, Jiangtai Road, Chaoyang District of Beijing.
Postcode: 100016 Name: Liu Xiaohui Email: Xiaohui_liu0406@163.com
Tel: 13488810897 Office: 010-84561144-1816
Page 24 of 113
DoNet Framework 3.5 系列
rpt_Message.DataSource = from gb in ctx.tbGuestBooks orderby gb.PostTime
descending select gb;
rpt_Message.DataBind();
}
}
前面创建 Linq to sql Classes 的时候我们输入名字 GuestBook,系统就为我们自动创建了
GuestBookDataContext(你也可以在 GuestBook.Designer.cs 中找到类定义)。在绑定的
时候我们使用查询句法查询留言表中所有留言,按照发表时间倒序(天哪?这是数据访问
吗?好像仅仅定义了一句 SQL 啊)。在发表留言按钮中,我们为一个 tbGuestBook 赋值,
然后把它加入留言表,再提交更改,就这样完成了记录的插入。
Beijing ZJS Express Stock Limited Company
Address: The 11th Floor, Zhaowei Building, Jiangtai Road, Chaoyang District of Beijing.
Postcode: 100016 Name: Liu Xiaohui Email: Xiaohui_liu0406@163.com
Tel: 13488810897 Office: 010-84561144-1816
Page 25 of 113
OnItemCommand="rpt_Message_ItemCommand">
<ItemTemplate>
DoNet Framework 3.5 系列
<table width="600px" style="border:solid 1px #666666; font-size:10pt;
background-color:#f0f0f0">
<tr>
<td align="left" width="400px">
Beijing ZJS Express Stock Limited Company
Address: The 11th Floor, Zhaowei Building, Jiangtai Road, Chaoyang District of Beijing.
Postcode: 100016 Name: Liu Xiaohui Email: Xiaohui_liu0406@163.com
Tel: 13488810897 Office: 010-84561144-1816
Page 26 of 113
<%# Eval("Message")%>
</td>
<td align="right" width="200px">
DoNet Framework 3.5 系列
<%# Eval("PostTime")%> - <%# Eval("UserName")%>
</td>
</tr>
<tr>
<td colspan="2" align="right">
<hr width="300px" />
<asp:Button ID="btn_DeleteMessage" runat="server" Text="删除留言"
CommandName="DeleteMessage" CommandArgument='<%# Eval("ID")%>'/>
管理员回复:<asp:TextBox runat="server" ID="tb_Reply"
TextMode="MultiLine" Width="300px" Text='<%# Eval("Reply")%>'/>
<asp:Button ID="btn_SendReply" runat="server" Text="发表回复"
CommandName="SendReply" CommandArgument='<%# Eval("ID")%>'/>
</td>
</tr>
</table>
<br/>
</ItemTemplate>
</asp:Repeater>
Beijing ZJS Express Stock Limited Company
Address: The 11th Floor, Zhaowei Building, Jiangtai Road, Chaoyang District of Beijing.
Postcode: 100016 Name: Liu Xiaohui Email: Xiaohui_liu0406@163.com
Tel: 13488810897 Office: 010-84561144-1816
Page 27 of 113
</div>
后台代码:
l class Admin : System.Web.UI.Page
{
GuestBookDataContext ctx = new
GuestBookDataContext("server=xxx;database=GuestBook;uid=xxx;pwd=xxx");
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
SetBind();
}
}
private void SetBind()
{
rpt_Message.DataSource = from gb in ctx.tbGuestBooks orderby gb.PostTime
descending select gb;
rpt_Message.DataBind();
}
protected void rpt_Message_ItemCommand(object source,
RepeaterCommandEventArgs e)
{
if (e.CommandName == "DeleteMessage")
{
StreamWriter sw = new StreamWriter(Server.MapPath("log.txt"), true);
ctx.Log = sw;
tbGuestBook gb = ctx.tbGuestBooks.Single(b => b.ID == new
Guid(e.CommandArgument.ToString()));
ctx.tbGuestBooks.Remove(gb);
ctx.SubmitChanges();
SetBind();
sw.Close();
}
if (e.CommandName == "SendReply")
{
StreamWriter sw = new StreamWriter(Server.MapPath("log.txt"), true);
ctx.Log = sw;
tbGuestBook gb = ctx.tbGuestBooks.Single(b => b.ID == new
Guid(e.CommandArgument.ToString()));
DoNet Framework 3.5 系列
gb.Reply = ((TextBox)e.Item.FindControl("tb_Reply")).Text;
gb.IsReplied = true;
ctx.SubmitChanges();
SetBind();
sw.Close();
}
}
}
在这里,我们通过 Single 方法获取一条记录,也就是一个 tbGuestBook 实例,更新了
一些属性后保存也就完成了改这个操作。删除操作更简单,只需要从表中移除对象。你是不
是觉得好像不是在操作数据库,像在操作内存中的对象。
由于写了日志,看看改和删操作会是怎么样的 SQL?
UPDATE [dbo].[tbGuestBook]
SET [IsReplied] = @p4, [Reply] = @p5
WHERE ([ID] = @p0) AND ([UserName] = @p1) AND ([PostTime] = @p2) AND
([Message] = @p3) AND (NOT ([IsReplied] = 1)) AND ([Reply] IS NULL)
-- @p0: Input Guid (Size = 0; Prec = 0; Scale = 0)
[00000000-0000-0000-0000-000000000000]
-- @p1: Input String (Size = 4; Prec = 0; Scale = 0) [ghgh]
-- @p2: Input DateTime (Size = 0; Prec = 0; Scale = 0) [2007-8-16 10:20:09]
-- @p3: Input String (Size = 3; Prec = 0; Scale = 0) [ghj]
-- @p4: Input Boolean (Size = 0; Prec = 0; Scale = 0) [True]
-- @p5: Input String (Size = 3; Prec = 0; Scale = 0) [qqq]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.20706.1
DELETE FROM [dbo].[tbGuestBook] WHERE ([ID] = @p0) AND ([UserName] = @p1)
AND ([PostTime] = @p2) AND ([Message] = @p3) AND (NOT ([IsReplied] = 1)) AND
([Reply] = @p4)
-- @p0: Input Guid (Size = 0; Prec = 0; Scale = 0)
[158ec941-13ff-4093-bd8b-9fceae152171]
-- @p2: Input DateTime (Size = 0; Prec = 0; Scale = 0) [2007-8-16 9:56:19]
-- @p3: Input String (Size = 2; Prec = 0; Scale = 0) [44]
-- @p4: Input String (Size = 3; Prec = 0; Scale = 0) [222]
现在,我们就可以使用 Linq to sql 完成简易留言簿了。实现以下功能:
发表留言(增)
查看留言(查)
管理员回复留言(改)
管理员删除留言(删除)
首先,创建一个 Default.aspx,在页面上加入一些控件:
<div>
姓名
<asp:TextBox ID="tb_UserName" runat="server"></asp:TextBox><br />
<br />
留言
<asp:TextBox ID="tb_Message" runat="server" Height="100px"
TextMode="MultiLine" Width="300px"></asp:TextBox><br />
<br />
<asp:Button ID="btn_SendMessage" runat="server" Text="发表留言"
OnClick="btn_SendMessage_Click" /><br />
<br />
<asp:Repeater ID="rpt_Message" runat="server">
<ItemTemplate>
<table width="600px" style="border:solid 1px #666666; font-size:10pt;
background-color:#f0f0f0">
<tr>
<td align="left" width="400px">
<%# Eval("Message")%>
</td>
<td align="right" width="200px">
<%# Eval("PostTime")%> - <%# Eval("UserName")%>
</td>
</tr>
<tr>
<td colspan="2" align="right">
<hr width="300px" />
管理员回复:<%# Eval("IsReplied").ToString() == "False" ? "暂无" :
Eval("Reply")%>
</td>
</tr>
</table>
<br/>
</ItemTemplate>
</asp:Repeater>
</div>
你可能很难想象,使用 Linq to sql 进行数据访问会是这么简单,后台代码:
public partial class _Default : System.Web.UI.Page
{
GuestBookDataContext ctx = new
GuestBookDataContext("server=xxx;database=GuestBook;uid=xxx;pwd=xxx");
protected void Page_Load(object sender, EventArgs e)
Beijing ZJS Express Stock Limited Company
Address: The 11th Floor, Zhaowei Building, Jiangtai Road, Chaoyang District of Beijing.
Postcode: 100016 Name: Liu Xiaohui Email: Xiaohui_liu0406@163.com
Tel: 13488810897 Office: 010-84561144-1816
Page 23 of 113
{
if (!IsPostBack)
DoNet Framework 3.5 系列
{
SetBind();
}
}
protected void btn_SendMessage_Click(object sender, EventArgs e)
{
tbGuestBook gb = new tbGuestBook();
gb.ID = Guid.NewGuid();
gb.UserName = tb_UserName.Text;
gb.Message = tb_Message.Text;
gb.IsReplied = false;
gb.PostTime = DateTime.Now;
ctx.tbGuestBooks.Add(gb);
ctx.SubmitChanges();
SetBind();
}
private void SetBind()
{
Beijing ZJS Express Stock Limited Company
Address: The 11th Floor, Zhaowei Building, Jiangtai Road, Chaoyang District of Beijing.
Postcode: 100016 Name: Liu Xiaohui Email: Xiaohui_liu0406@163.com
Tel: 13488810897 Office: 010-84561144-1816
Page 24 of 113
DoNet Framework 3.5 系列
rpt_Message.DataSource = from gb in ctx.tbGuestBooks orderby gb.PostTime
descending select gb;
rpt_Message.DataBind();
}
}
前面创建 Linq to sql Classes 的时候我们输入名字 GuestBook,系统就为我们自动创建了
GuestBookDataContext(你也可以在 GuestBook.Designer.cs 中找到类定义)。在绑定的
时候我们使用查询句法查询留言表中所有留言,按照发表时间倒序(天哪?这是数据访问
吗?好像仅仅定义了一句 SQL 啊)。在发表留言按钮中,我们为一个 tbGuestBook 赋值,
然后把它加入留言表,再提交更改,就这样完成了记录的插入。
Beijing ZJS Express Stock Limited Company
Address: The 11th Floor, Zhaowei Building, Jiangtai Road, Chaoyang District of Beijing.
Postcode: 100016 Name: Liu Xiaohui Email: Xiaohui_liu0406@163.com
Tel: 13488810897 Office: 010-84561144-1816
Page 25 of 113
OnItemCommand="rpt_Message_ItemCommand">
<ItemTemplate>
DoNet Framework 3.5 系列
<table width="600px" style="border:solid 1px #666666; font-size:10pt;
background-color:#f0f0f0">
<tr>
<td align="left" width="400px">
Beijing ZJS Express Stock Limited Company
Address: The 11th Floor, Zhaowei Building, Jiangtai Road, Chaoyang District of Beijing.
Postcode: 100016 Name: Liu Xiaohui Email: Xiaohui_liu0406@163.com
Tel: 13488810897 Office: 010-84561144-1816
Page 26 of 113
<%# Eval("Message")%>
</td>
<td align="right" width="200px">
DoNet Framework 3.5 系列
<%# Eval("PostTime")%> - <%# Eval("UserName")%>
</td>
</tr>
<tr>
<td colspan="2" align="right">
<hr width="300px" />
<asp:Button ID="btn_DeleteMessage" runat="server" Text="删除留言"
CommandName="DeleteMessage" CommandArgument='<%# Eval("ID")%>'/>
管理员回复:<asp:TextBox runat="server" ID="tb_Reply"
TextMode="MultiLine" Width="300px" Text='<%# Eval("Reply")%>'/>
<asp:Button ID="btn_SendReply" runat="server" Text="发表回复"
CommandName="SendReply" CommandArgument='<%# Eval("ID")%>'/>
</td>
</tr>
</table>
<br/>
</ItemTemplate>
</asp:Repeater>
Beijing ZJS Express Stock Limited Company
Address: The 11th Floor, Zhaowei Building, Jiangtai Road, Chaoyang District of Beijing.
Postcode: 100016 Name: Liu Xiaohui Email: Xiaohui_liu0406@163.com
Tel: 13488810897 Office: 010-84561144-1816
Page 27 of 113
</div>
后台代码:
l class Admin : System.Web.UI.Page
{
GuestBookDataContext ctx = new
GuestBookDataContext("server=xxx;database=GuestBook;uid=xxx;pwd=xxx");
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
SetBind();
}
}
private void SetBind()
{
rpt_Message.DataSource = from gb in ctx.tbGuestBooks orderby gb.PostTime
descending select gb;
rpt_Message.DataBind();
}
protected void rpt_Message_ItemCommand(object source,
RepeaterCommandEventArgs e)
{
if (e.CommandName == "DeleteMessage")
{
StreamWriter sw = new StreamWriter(Server.MapPath("log.txt"), true);
ctx.Log = sw;
tbGuestBook gb = ctx.tbGuestBooks.Single(b => b.ID == new
Guid(e.CommandArgument.ToString()));
ctx.tbGuestBooks.Remove(gb);
ctx.SubmitChanges();
SetBind();
sw.Close();
}
if (e.CommandName == "SendReply")
{
StreamWriter sw = new StreamWriter(Server.MapPath("log.txt"), true);
ctx.Log = sw;
tbGuestBook gb = ctx.tbGuestBooks.Single(b => b.ID == new
Guid(e.CommandArgument.ToString()));
DoNet Framework 3.5 系列
gb.Reply = ((TextBox)e.Item.FindControl("tb_Reply")).Text;
gb.IsReplied = true;
ctx.SubmitChanges();
SetBind();
sw.Close();
}
}
}
在这里,我们通过 Single 方法获取一条记录,也就是一个 tbGuestBook 实例,更新了
一些属性后保存也就完成了改这个操作。删除操作更简单,只需要从表中移除对象。你是不
是觉得好像不是在操作数据库,像在操作内存中的对象。
由于写了日志,看看改和删操作会是怎么样的 SQL?
UPDATE [dbo].[tbGuestBook]
SET [IsReplied] = @p4, [Reply] = @p5
WHERE ([ID] = @p0) AND ([UserName] = @p1) AND ([PostTime] = @p2) AND
([Message] = @p3) AND (NOT ([IsReplied] = 1)) AND ([Reply] IS NULL)
-- @p0: Input Guid (Size = 0; Prec = 0; Scale = 0)
[00000000-0000-0000-0000-000000000000]
-- @p1: Input String (Size = 4; Prec = 0; Scale = 0) [ghgh]
-- @p2: Input DateTime (Size = 0; Prec = 0; Scale = 0) [2007-8-16 10:20:09]
-- @p3: Input String (Size = 3; Prec = 0; Scale = 0) [ghj]
-- @p4: Input Boolean (Size = 0; Prec = 0; Scale = 0) [True]
-- @p5: Input String (Size = 3; Prec = 0; Scale = 0) [qqq]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.20706.1
DELETE FROM [dbo].[tbGuestBook] WHERE ([ID] = @p0) AND ([UserName] = @p1)
AND ([PostTime] = @p2) AND ([Message] = @p3) AND (NOT ([IsReplied] = 1)) AND
([Reply] = @p4)
-- @p0: Input Guid (Size = 0; Prec = 0; Scale = 0)
[158ec941-13ff-4093-bd8b-9fceae152171]
-- @p2: Input DateTime (Size = 0; Prec = 0; Scale = 0) [2007-8-16 9:56:19]
-- @p3: Input String (Size = 2; Prec = 0; Scale = 0) [44]
-- @p4: Input String (Size = 3; Prec = 0; Scale = 0) [222]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.20706.1