第一:创建表留言表tb_GuestBook,代码如下:
CREATE TABLE dbo.tb_GuestBook(
[ID] uniqueidentifier primary key,
[UserName] [nvarchar](50),
[PostTime] [datetime] NULL,
[Message] [nvarchar](500),
[IsReplied] [bit] NULL,
[Reply] [nvarchar],
)
第二,利用VS2008新建一个网站解决方案,命名为:LinqCRUD。
(关键)建立实体类方法:
1.添加一个新项,DataClasses1.dbml,命名为:GuestBook.dbml。
2.在VS2008左上角添加数据库db_Test,然后将表 tb_GuestBook 拖入到 GuestBook.dbml的设计页面中,保存。
打开GuestBook.designer.cs可以发现系统自动创建了GuestBook数据库中tbGuestBook表的映射。
第三,
简易留言簿
现在,我们就可以使用Linq to sql完成简易留言簿了。实现以下功能:
l 发表留言(增)
l 查看留言(查)
l 管理员回复留言(改)
l 管理员删除留言(删除)
首先,创建一个Default.aspx,在页面上加入一些控件:
- <div>
- 姓名:<asp:TextBox ID="tb_UserName" runat="server"></asp:TextBox>
- <br />
- <br />
- 留言:<asp:TextBox ID="tb_Message" runat="server" TextMode="MultiLine"
- Width="435px"></asp:TextBox>
- <br />
- <br />
- <asp:Button ID="btn_SendMessage" runat="server" onclick="btn_SendMessage_Click"
- Text="发表留言" />
- <br />
- <br />
- <asp:Repeater ID="Repeater1" runat="server" >
- <ItemTemplate >
- <table width="600px" style="border:solid 1px #666666; font-size:10pt; background-color:#f0f0f0">
- <tr>
- <td align="left" style ="width :400px">
- <%# Eval("Message")%>
- </td>
- <td align="right" style ="width :200px">
- <%# Eval("PostTime")%> - <%# Eval("UserName")%>
- </td>
- </tr>
- <tr>
- <td colspan="2" align="right">
- <hr style ="width :300px" />
- 管理员回复:<%# Eval("IsReplied").ToString() == "False" ? "暂无" : Eval("Reply")%>
- </td>
- </tr>
- </table>
- <br/>
- </ItemTemplate>
- </asp:Repeater>
- <br />
- <br />
- </div>
很难想像,使用Linq to sql进行数据访问会是这么简单,Default.aspx后台代码如下:
- using System.Xml.Linq;
- using System.Data.Linq;
- namespace LinqCRUD
- {
- public partial class _Default : System.Web.UI.Page
- {
- private const string connStr = "Data Source=(local);User Id=sa;Password=server;Initial Catalog=db_Test;";
- GuestBookDataContext ctx = new GuestBookDataContext(connStr);
- protected void Page_Load(object sender, EventArgs e)
- {
- SetBind();
- }
- /// <summary>
- /// 绑定Repeater的数据
- /// </summary>
- private void SetBind()
- {
- Repeater1.DataSource = from gb in ctx.tb_GuestBooks orderby gb.PostTime descending select gb;
- Repeater1.DataBind();
- }
- protected void btn_SendMessage_Click(object sender, EventArgs e)
- {
- //创建表的实例
- tb_GuestBook gb = new tb_GuestBook();
- gb.ID = Guid.NewGuid();
- gb.UserName = tb_UserName.Text;
- gb.Message = tb_Message.Text;
- gb.IsReplied = false;
- gb.PostTime = DateTime.Now;
- ctx.tb_GuestBooks.InsertOnSubmit(gb);
- ctx.SubmitChanges();
- SetBind();
- }
- }
- }
运行效果如下图:
- <div>
- <asp:Repeater ID="Repeater1" runat="server"
- onitemcommand="Repeater1_ItemCommand">
- <ItemTemplate >
- <table style =" width :600px; border :solid 1px #666666; font-size :10pt; background-color :#f0f0f0;">
- <tr>
- <td align ="left" style ="width :400px">
- <%#Eval("Message") %>
- </td>
- <td align ="right" style ="width :200px">
- <%#Eval ("PostTime") %>-<%#Eval("UserName") %>
- </td>
- </tr>
- <tr>
- <td colspan ="2" align ="right" >
- <hr style ="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 runat ="server" ID ="btn_SendReply" Text ="发表回复" CommandName ="SendReply" CommandArgument ='<%#Eval("ID") %>' />
- </td>
- </tr>
- </table>
- <br />
- </ItemTemplate>
- </asp:Repeater>
- </div>
后台代码:
- using System.Data.Linq;
- using System.IO;
- namespace LinqCRUD
- {
- public partial class Admin : System.Web.UI.Page
- {
- private const string connStr = "Data Source=(local);User Id=sa;Password=server;Initial Catalog=db_Test;";
- //创建强类型DataContext对象
- GuestBookDataContext ctx = new GuestBookDataContext(connStr);
- protected void Page_Load(object sender, EventArgs e)
- {
- if (!Page.IsPostBack)
- {
- SetBind();
- }
- }
- /// <summary>
- /// 绑定数据
- /// </summary>
- private void SetBind()
- {
- Repeater1.DataSource = from gb in ctx.tb_GuestBooks orderby gb.PostTime descending select gb;
- Repeater1.DataBind();
- }
- protected void Repeater1_ItemCommand(object source, RepeaterCommandEventArgs e)
- {
- if (e.CommandName == "DeleteMessage")
- {
- //记录日志
- StreamWriter sw = new StreamWriter(Server.MapPath("log.txt"), true);
- ctx.Log = sw;
- //删除回复
- tb_GuestBook gb = ctx.tb_GuestBooks.Single(b => b.ID == new Guid(e.CommandArgument .ToString ()));
- ctx.tb_GuestBooks.DeleteOnSubmit(gb);
- ctx.SubmitChanges();
- SetBind();
- sw.Close();
- }
- if (e.CommandName == "SendReply")
- {
- //记录日志
- StreamWriter sw = new StreamWriter(Server.MapPath("log.txt"), true);
- ctx.Log = sw;
- //管理员回复
- tb_GuestBook gb = ctx.tb_GuestBooks.Single(b => b.ID == new Guid(e.CommandArgument.ToString()));
- gb.Reply = ((TextBox)e.Item.FindControl("tb_Reply")).Text;
- gb.IsReplied = true;
- ctx.SubmitChanges();
- SetBind();
- Response.Write("<script>alert('回复成功')</script>");
- 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] -- @p1: Input String (Size = 2; Prec = 0; Scale = 0) [44] -- @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 |