017-新闻表分页增删改查

1-NewsList.aspx（查）

 1 <%@ Page Language="C#" AutoEventWireup="true" CodeBehind="NewsList.aspx.cs" Inherits="t3_NewsInfo.NewsList" %>
 2 
 3 <%@ Import Namespace="System.Data" %>
 4 
 5 <!DOCTYPE html>
 6 
 7 <html xmlns="http://www.w3.org/1999/xhtml">
 8 <head runat="server">
 9     <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
10     <title></title>
11     <script>
12         function RemoveConfirm(id) {
13             if (confirm('确定要删除吗？小龙包')) {
14                 location.href = "NewsDelete.ashx?id=" + id;
15             }
16         }
17     </script>
18 </head>
19 <body>
20     <a href="NewsAdd.aspx">添加</a>
21     <hr />
22     <table border="1">
23         <tr>
24             <th>编号</th>
25             <th>标题</th>
26             <th>分类</th>
27             <th>时间</th>
28             <th>修改</th>
29             <th>删除</th>
30         </tr>
31         <%
32             foreach (DataRow row in DtNews.Rows)
33             {%>
34         <tr>
35             <td><%=row["NewsId"] %></td>
36             <td><%=row["NewsTitle"] %></td>
37             <td><%=row["TypeTitle"] %></td>
38             <td><%=Convert.ToDateTime(row["SubTime"]).ToString("yyyy-MM-dd") %></td>
39             <td><a href="NewsEdit.aspx?id=<%=row["NewsId"] %>">修改</a></td>
40             <td><a href="javascript:RemoveConfirm(<%=row["NewsId"] %>);">删除</a></td>
41         </tr>
42         <%}
43 
44         %>
45         <tr>
46             <td colspan="6" align="center"><%=PagerBar %></td>
47         </tr>
48     </table>
49 </body>
50 </html>

 1     public partial class NewsList : System.Web.UI.Page
 2     {
 3         protected DataTable DtNews { get; set; }
 4         protected string PagerBar { get; set; }
 5         protected void Page_Load(object sender, EventArgs e)
 6         {
 7             int pageSize = 10;
 8             int pageIndex = 1;
 9 
10             //接收页索引
11             if (!string.IsNullOrEmpty(Request["pIndex"]))
12             {
13                 pageIndex = int.Parse(Request["pIndex"]);
14             }
15 
16 
17             GetPagerBar(ref pageIndex, pageSize);
18             //分页查询·SQL语句
19             //string sql = "select * from NewsInfo "+
20             //    "inner join typeinfo on NewsInfo.typeid=typeinfo.TypeId "+
21             //    "order by newsid desc";
22             string sql = "select * from newsinfolist where rowIndex between @startIndex and @endIndex";
23             SqlParameter[] ps =
24             {
25                 new SqlParameter("@startIndex",(pageIndex-1)*pageSize+1),
26                 new SqlParameter("@endIndex",pageSize*pageIndex)
27             };
28             DtNews = SqlHelper.GetList(sql, ps);
29 
30 
31         }
32 
33         private void GetPagerBar(ref int pageIndex, int pageSize)
34         {
35             //首页 上一页 下一页 末页
36             StringBuilder sb = new StringBuilder("");
37 
38             if (pageIndex < 1)
39             {
40                 pageIndex = 1;
41             }
42 
43             //首页 上一页 
44             if (pageIndex == 1)
45             {
46                 //当前是第1页，则首页 上一页不需要链接
47                 sb.Append("首页 上一页 ");
48             }
49             else
50             {
51                 sb.Append("<a href='?pIndex=1'>首页</a> ");
52                 sb.Append("<a href='?pIndex=" + (pageIndex - 1) + "'>上一页</a> ");
53             }
54 
55             //查询总条数，计算总页数
56             string sql = "select count(*) from newsinfo";
57             int rowsCount = Convert.ToInt32(SqlHelper.ExecuteScalar(sql));
58             int pageCount = Convert.ToInt32(Math.Ceiling(rowsCount * 1.0 / pageSize));//5.2->6.0->6
59 
60             if (pageIndex > pageCount)
61             {
62                 pageIndex = pageCount;
63             }
64 
65             //下一页 末页
66             if (pageIndex == pageCount)
67             {
68                 sb.Append("下一页 末页");
69             }
70             else
71             {
72                 sb.Append("<a href='?pIndex=" + (pageIndex + 1) + "'>下一页</a> ");
73                 sb.Append("<a href='?pIndex=" + pageCount + "'>末页</a>");
74             }
75 
76             PagerBar = sb.ToString();
77         }
78     }

2-NewsAdd.aspx（增）

 1 <%@ Page Language="C#" AutoEventWireup="true" ValidateRequest="false" CodeBehind="NewsAdd.aspx.cs" Inherits="t3_NewsInfo.NewsAdd" %>
 2 
 3 <%@ Import Namespace="System.Activities.Statements" %>
 4 <%@ Import Namespace="System.Data" %>
 5 
 6 <!DOCTYPE html>
 7 
 8 <html xmlns="http://www.w3.org/1999/xhtml">
 9 <head runat="server">
10     <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
11     <title></title>
12     <link href="UMeditor/themes/default/css/umeditor.css" rel="stylesheet" />
13     <script src="js/jquery.min.js"></script>
14     <script src="js/umeditor.config.js"></script>
15     <script src="js/umeditor.min.js"></script>
16     <script src="js/lang/zh-cn/zh-cn.js"></script>
17 
18 </head>
19 <body>
20     <form id="form1" runat="server">
21         标题：<input type="text" name="title" />
22         <br />
23         分类：<select name="typeid">
24             <% foreach (DataRow row in DtType.Rows)
25                 {%>
26             <option value="<%=row["TypeId"] %>">
27                 <%=row["TypeTitle"] %>
28             </option>
29             <% } %>
30         </select>
31         <br />
32         内容：
33         <script type="text/plain" id="myEditor" style=" 500px; height: 240px; max-height: 240px;">
34         </script>
35         <script type="text/javascript">
36             //实例化编辑器
37             var um = UM.getEditor('myEditor');
38         </script>
39         <br />
40         <input type="submit" value="添加" />
41         <%=Msg %>
42     </form>
43 </body>
44 </html>

 1     public partial class NewsAdd : System.Web.UI.Page
 2     {
 3         protected DataTable DtType { get; set; }
 4         protected string Msg { get; set; }
 5         protected void Page_Load(object sender, EventArgs e)
 6         {
 7             if (!IsPostBack)
 8             {
 9                 LoadTypeList();
10             }
11             else
12             {
13                 //用户提交，进行添加操作
14                 string title = Request["title"];
15                 string typeid = Request["typeid"];
16                 string content = Request["editorValue"];
17 
18                 string sql = "insert into newsinfo values(@title,getdate(),@typeid,@content)";
19                 SqlParameter[] ps =
20                 {
21                     new SqlParameter("@title", title),
22                     new SqlParameter("@typeid", typeid),
23                     new SqlParameter("@content", content)
24                 };
25 
26                 if (SqlHelper.ExecuteNonQuery(sql, ps) > 0)
27                 {
28                     //添加成功，转到列表页
29                     Response.Redirect("NewsList.aspx");
30                 }
31                 else
32                 {
33                     Msg = "添加失败";
34                     LoadTypeList();
35                 }
36             }
37         }
38 
39         private void LoadTypeList()
40         {
41             //在页面初次加载时，获取所有的分类信息
42             string sql = "select * from typeinfo";
43             DtType = SqlHelper.GetList(sql);
44         }
45     }

3-NewsEdit.aspx（改）

 1 <%@ Page Language="C#" AutoEventWireup="true" ValidateRequest="false" CodeBehind="NewsEdit.aspx.cs" Inherits="t3_NewsInfo.NewsEdit" %>
 2 
 3 <%@ Import Namespace="System.Data" %>
 4 <%@ Import Namespace="t3_NewsInfo" %>
 5 
 6 <!DOCTYPE html>
 7 
 8 <html xmlns="http://www.w3.org/1999/xhtml">
 9 <head runat="server">
10     <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
11     <title></title>
12     <link href="UMeditor/themes/default/css/umeditor.css" rel="stylesheet" />
13     <script src="js/jquery.min.js"></script>
14     <script src="js/umeditor.config.js"></script>
15     <script src="js/umeditor.min.js"></script>
16     <script src="js/lang/zh-cn/zh-cn.js"></script>
17 </head>
18 <body>
19     <form id="form1" runat="server">
20         编号：<%=NewsInfo1.NewsId %>
21         <br />
22         标题：<input type="text" name="title" value="<%=NewsInfo1.NewsTitle %>" />
23         <br />
24         分类：<select name="typeid">
25             <%
26                 foreach (DataRow row in DtType.Rows)
27                 {%>
28             <option value="<%=row["TypeId"] %>"
29                 <%
30                 if (Convert.ToInt32(row["TypeId"]) == NewsInfo1.TypeId)
31                 {
32                     Response.Write(" selected='selected'");
33                 }
34                             %>>
35                 <%=row["TypeTitle"] %>
36             </option>
37             <% }
38             %>
39         </select>
40         <br />
41         内容：
42         <script type="text/plain" id="myEditor" style=" 500px; height: 240px; max-height: 240px;">
43             <%=NewsInfo1.NewsContent %>
44         </script>
45         <script type="text/javascript">
46             //实例化编辑器
47             var um = UM.getEditor('myEditor');
48         </script>
49         <br />
50         <input type="submit" value="修改" />
51         <br />
52         <%=Msg %>
53     </form>
54 </body>
55 </html>

 1     public partial class NewsEdit : System.Web.UI.Page
 2     {
 3         protected NewsInfo NewsInfo1 { get; set; }
 4         protected DataTable DtType { get; set; }
 5         protected string Msg { get; set; }
 6         protected void Page_Load(object sender, EventArgs e)
 7         {
 8             if (!IsPostBack)
 9             {
10                 LoadData();
11             }
12             else
13             {
14                 int id = int.Parse(Request["id"]);
15                 string title = Request["title"];
16                 string typeid = Request["typeid"];
17                 string content = Request["editorValue"];
18 
19                 string sql =
20                     "update newsinfo set newstitle=@title,subtime=getdate(),typeid=@tid,newscontent=@content where newsid=@id";
21                 SqlParameter[] ps =
22                 {
23                     new SqlParameter("@id",id),
24                     new SqlParameter("@title",title),
25                     new SqlParameter("@tid",typeid),
26                     new SqlParameter("@content",content)
27                 };
28 
29                 if (SqlHelper.ExecuteNonQuery(sql, ps) > 0)
30                 {
31                     Response.Redirect("NewsList.aspx");
32                 }
33                 else
34                 {
35                     Msg = "修改失败";
36                     LoadData();
37                 }
38             }
39         }
40 
41         private void LoadData()
42         {
43             string sql = "select * from newsinfo where newsid=@id";
44             int id = int.Parse(Request["id"]);
45             SqlParameter p = new SqlParameter("@id", id);
46 
47             DataTable dt = SqlHelper.GetList(sql, p);
48             NewsInfo1 = new NewsInfo();
49             NewsInfo1.NewsId = Convert.ToInt32(dt.Rows[0]["NewsId"]);
50             NewsInfo1.NewsTitle = dt.Rows[0]["NewsTitle"].ToString();
51             NewsInfo1.TypeId = Convert.ToInt32(dt.Rows[0]["TypeId"]);
52             NewsInfo1.NewsContent = dt.Rows[0]["NewsContent"].ToString();
53 
54             sql = "select * from typeinfo";
55             DtType = SqlHelper.GetList(sql);
56         }
57     }

4-NewsDelete.ashx（删）

 1     public class NewsDelete : IHttpHandler
 2     {
 3 
 4         public void ProcessRequest(HttpContext context)
 5         {
 6             context.Response.ContentType = "text/plain";
 7 
 8             int id = int.Parse(context.Request["id"]);
 9 
10             string sql = "delete from newsinfo where newsid=@id";
11             SqlParameter p = new SqlParameter("@id", id);
12 
13             if (SqlHelper.ExecuteNonQuery(sql, p) > 0)
14             {
15                 context.Response.Redirect("NewsList.aspx");
16             }
17             else
18             {
19                 context.Response.Write("删除失败");
20             }
21         }
22 
23         public bool IsReusable
24         {
25             get
26             {
27                 return false;
28             }
29         }
30     }

5-SqlHelper.cs/Web.config（NewsInfo.cs）

 1     public static class SqlHelper
 2     {
 3         private static string connStr =
 4             System.Configuration.ConfigurationManager.ConnectionStrings["conn"].ConnectionString;
 5 
 6         public static DataTable GetList(string sql, params SqlParameter[] ps)
 7         {
 8             using (SqlConnection conn = new SqlConnection(connStr))
 9             {
10                 SqlDataAdapter sda = new SqlDataAdapter(sql, conn);
11                 sda.SelectCommand.Parameters.AddRange(ps);
12 
13                 DataTable dt = new DataTable();
14                 sda.Fill(dt);
15 
16                 return dt;
17             }
18         }
19 
20         public static int ExecuteNonQuery(string sql, params SqlParameter[] ps)
21         {
22             using (SqlConnection conn = new SqlConnection(connStr))
23             {
24                 SqlCommand cmd = new SqlCommand(sql, conn);
25                 cmd.Parameters.AddRange(ps);
26 
27                 conn.Open();
28                 return cmd.ExecuteNonQuery();
29             }
30         }
31 
32         public static object ExecuteScalar(string sql, params SqlParameter[] ps)
33         {
34             using (SqlConnection conn = new SqlConnection(connStr))
35             {
36                 SqlCommand cmd = new SqlCommand(sql, conn);
37                 cmd.Parameters.AddRange(ps);
38 
39                 conn.Open();
40                 return cmd.ExecuteScalar();
41             }
42         }
43 
44     }

 1 <?xml version="1.0" encoding="utf-8"?>
 2 
 3 <!--
 4   有关如何配置 ASP.NET 应用程序的详细信息，请访问
 5   http://go.microsoft.com/fwlink/?LinkId=169433
 6   -->
 7 
 8 <configuration>
 9   <system.web>
10     <compilation debug="true" targetFramework="4.5" />
11     <httpRuntime requestValidationMode="2.0" targetFramework="4.5" />
12   </system.web>
13   <connectionStrings>
14     <add name="conn" connectionString="server=.;database=web1;uid=sa;pwd=123"/>
15   </connectionStrings>
16 </configuration>

1     public class NewsInfo
2     {
3         public int NewsId { get; set; }
4         public string NewsTitle { get; set; }
5         public int TypeId { get; set; }
6         public string NewsContent { get; set; }
7     }