本周做的事新闻的管理模块，包括新闻标题的增添、新闻内容的增加、创建者和新闻类型以及修改和添加 ，
（思路：在Demo3.aspx页面中进行查询并用表格显示出来（动态生成表格），然后再每条数据的最后加上一个有
修改工能的按钮或者超链接，点击修改后，跳转到update.aspx(修改页)进行修改，修改完成后在把修改完成的数据
回传到Demo3.aspx页面中，这时页面显示的内容是修改后的内容，添加也是这样）
1. 首先在VS中建立一个Web窗体，我这里是Demo3.aspx，在网页上添加了一个新闻标题：后面加上一个文本框，再加上一个button
按钮，后面再加上添加的超链接。
<form id="form1" runat="server">
<div>
新闻标题：
<asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
<asp:Button ID="Button1" runat="server" onclick="Button1_Click" Text="查询" /><a href="insert.aspx">添加</a>
<br />
<br />
<div id="divResult" runat="server">
</div>
</div>
</form>
------------后台部分---------------------------------------------------
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Data;
using System.Text;
namespace _2013_3_4sql复习
{
public partial class Demo3 : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
if (!string.IsNullOrEmpty(Request.QueryString["key"]))//接受传过来的值
{
TextBox1.Text = Request.QueryString["key"].ToString();
DataLoad();
}
if (Request.QueryString["ispostback"] == "1")
{
DataLoad();
}
}
}
//连接到数据库
string connstr = "Data Source=QZC-PC;database=News;integrated security=true";
protected void Button1_Click(object sender, EventArgs e)
{
#region 验证输入是否为空
if (string.IsNullOrEmpty(TextBox1.Text.Trim()))
{
TextBox1.Text = "请输入您要查询的数据";
return;
}
DataLoad();
#endregion
}
private void DataLoad()
{
#region 根据用户的输入获取数据
SqlConnection conn = new SqlConnection(connstr);
conn.Open();//打开数据库连接
SqlCommand cmd = new SqlCommand();
cmd.CommandText = "SELECT T1.Id,T1.NewsTitle,T1.NewsContent,T2.ClassName,T3.RealName,T4.Department_Name FROM T_News T1 INNER JOIN T_NewsClass T2 ON T1.ClassId=T2.ClassId JOIN T_User T3 ON T1.NewsCreator=T3.UserId INNER JOIN Department T4 ON T3.Department=T4.Department_Id where (T1.NewsTitle like '%" + TextBox1.Text.Trim() + "%') order by T1.Id DESC";
cmd.Connection = conn;
DataTable dt = new DataTable();
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
adapter.Fill(dt);
cmd.Dispose();
conn.Dispose();
StringBuilder sb1 = new StringBuilder();
#endregion
#region 将数据拼接成字符串发送到前台
string newid = string.Empty;
string newstitle = string.Empty;
string newscontent = string.Empty;
string classname = string.Empty;
string realname = string.Empty;
string dpname = string.Empty;
sb1.Append("<table>");
foreach (DataRow row in dt.Rows)
{
sb1.Append("<tr>");
newstitle = row["NewsTitle"].ToString();
newscontent = row["NewsContent"].ToString();
classname = row["ClassName"].ToString();
realname = row["RealName"].ToString();
dpname=row["Department_Name"].ToString();
sb1.Append("<td>" + newstitle + "</td>");
sb1.Append("<td>" + newscontent + "</td>");
sb1.Append("<td>" + classname+ "</td>");
sb1.Append("<td>" + realname + "</td>");
sb1.Append("<td>" + dpname + "</td>");
//下面这种方式是通过将要传递的参数放置到地址栏中传递的，
sb1.Append("<td><a href=update.aspx?id=" + row["Id"].ToString() + "&key=" + TextBox1 .Text+ ">修改</a></td>");
sb1.Append("</tr>");
}
sb1.Append("</table>");
divResult.InnerHtml = sb1.ToString();
#endregion
}
}
}
2.就是跳转到修改页update.aspx
<form id="form1" runat="server">
<div>
新闻标题：<asp:TextBox ID="txtnewstitle" runat="server"
Width="228px" Height="36px" TextMode="MultiLine"></asp:TextBox>
<br />
新闻内容：<asp:TextBox ID="txtnewscontent" runat="server" Width="233px"
Height="73px" TextMode="MultiLine"></asp:TextBox>
<br />
类别名称：<asp:DropDownList ID="ddlclassname" runat="server" AutoPostBack="True">
<asp:ListItem>军事</asp:ListItem>
<asp:ListItem>游戏</asp:ListItem>
<asp:ListItem>娱乐</asp:ListItem>
<asp:ListItem>房产</asp:ListItem>
<asp:ListItem>科技</asp:ListItem>
<asp:ListItem>教育</asp:ListItem>
<asp:ListItem>体育</asp:ListItem>
</asp:DropDownList>
<br />
创建者：<asp:DropDownList ID="ddlcreator" runat="server" AutoPostBack="True">
<asp:ListItem>姚宏波</asp:ListItem>
<asp:ListItem>肖唯哲</asp:ListItem>
<asp:ListItem>张东</asp:ListItem>
<asp:ListItem>刘晓飞</asp:ListItem>
<asp:ListItem>王瑞</asp:ListItem>
</asp:DropDownList>
<br />
<br />
<asp:Button ID="Button1" runat="server" onclick="Button1_Click" Text="保存" />
</div>
</form>
------------后台部分-------------------------
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Text;
using System.Data;
namespace _2013_3_4sql复习
{
public partial class update : System.Web.UI.Page
{
string newid = string.Empty;
string key = string.Empty;
protected void Page_Load(object sender, EventArgs e)
{
newid = Request.QueryString["id"];
key=Request.QueryString["key"];
if(!IsPostBack)
{
DataLoad(newid);
}
}
#region 根据传递过来的新闻ID获取数据
string connstr = "data source=QZC-PC;database=News;integrated security=true";
SqlConnection conn = new SqlConnection(connstr);
conn.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandText = "SELECT T1.Id,T1.NewsTitle,T1.NewsContent,T2.ClassId,T2.ClassName,T3.UserId,T3.RealName,T4.Department_Name FROM T_News T1 INNER JOIN T_NewsClass T2 ON T1.ClassId=T2.ClassId INNER JOIN T_User T3 ON T1.NewsCreator=T3.UserId INNER JOIN Department T4 ON T3.Department=T4.Department_Id WHERE T1.Id=@id";
cmd.Parameters.AddWithValue("@id", newsid);
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
adapter.Fill(dt);
//Response.Write(dt.Rows.Count);
#endregion
#region 将查询出来的数据绑定到界面控件上
txtnewstitle.Text = dt.Rows[0]["NewsTitle"].ToString();
txtnewscontent.Text = dt.Rows[0]["NewsContent"].ToString();
#region 将新闻类别绑定到新闻类别下拉列表中
cmd.Parameters.Clear();
cmd.CommandText = "SELECT ClassId,ClassName FROM T_NewsClass";
DataTable dtNewsClass = new DataTable();
adapter.Fill(dtNewsClass);
//Response.Write(dtNewsClass.Rows.Count);
/*绑定到下拉*/
this.ddlclassname.DataSource = dtNewsClass;
this.ddlclassname.DataTextField = "ClassName";
this.ddlclassname.DataValueField = "ClassId";
this.ddlclassname.DataBind();
ddlclassname.Items.FindByValue(dt.Rows[0]["ClassId"].ToString()).Selected = true;
#endregion
#region 将创建者绑定到创建者列表下
cmd.Parameters.Clear();
cmd.CommandText = "SELECT UserId,RealName FROM T_User";
DataTable dtUser = new DataTable();
adapter.Fill(dtUser);
/*绑定到下拉*/
this.ddlcreator.DataSource = dtUser;
this.ddlcreator.DataTextField = "RealName";
this.ddlcreator.DataValueField = "UserId";
this.ddlcreator.DataBind();
//在下拉列表中找到value值为dt.Rows[0]["ClassId"].ToString()的选项，然后将其设置为默认选项
ddlcreator.Items.FindByValue(dt.Rows[0]["UserId"].ToString()).Selected = true;
#endregion
//Dispose就是将对象彻底销毁了
cmd.Dispose();
conn.Dispose();
#endregion
}
protected void Button1_Click(object sender, EventArgs e)
{
#region 获取用户在新界面输入的更改后的数据
string newstitle = txtnewstitle.Text;
string newscontent = txtnewscontent.Text;
string classid = ddlclassname.SelectedItem.Value;
string userid = ddlcreator.SelectedItem.Value;
#endregion
//建立数据库连接
string connstr1 = "data source=QZC-PC;database=News;integrated security=true";
SqlConnection conn = new SqlConnection(connstr1);
conn.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandText = "UPDATE T_News SET NewsTitle=@newstitle,NewsContent=@newscontent,ClassId=@classid,NewsCreator=@newscreator WHEREId=@id";
cmd.Parameters.AddWithValue("@newstitle",newstitle);
cmd.Parameters.AddWithValue("@newscontent", newscontent);
cmd.Parameters.AddWithValue("@classid", classid);
cmd.Parameters.AddWithValue("@NewsCreator", userid);
cmd.Parameters.AddWithValue("@id", newid);
if (cmd.ExecuteNonQuery()>0)
{
Response.Redirect("Demo3.aspx?key="+key);
}
}
}
}
修改后的结果：
3.就是进入到添加页面
这一部分的前台和修改部分的前台代码是一样的，我这里就不再粘贴了
--------后台部分--------------------------------
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Data;
namespace _2013_3_4sql复习
{
public partial class insert : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if(!IsPostBack)
{
LoadClassName();
LoadUser();
}
}
private void LoadClassName()
{
string connstr = "data source=QZC-PC;DATABASE=News;INTEGRATED SECURITY=TRUE";
SqlConnection conn = new SqlConnection(connstr);
conn.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandText = "SELECT ClassId,ClassName FROM T_NewsClass";
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
adapter.Fill(dt);
cmd.Dispose();
conn.Close();
conn.Dispose();
this.DropDownList1.DataSource = dt;
this.DropDownList1.DataTextField = "ClassName";
this.DropDownList1.DataValueField = "ClassId";
this.DropDownList1.DataBind();
}
private void LoadUser()
{
string connstr = "data source=QZC-PC;DATABASE=News;INTEGRATED SECURITY=TRUE";
SqlConnection conn = new SqlConnection(connstr);
conn.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandText = "SELECT UserId,RealName FROM T_User";
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
adapter.Fill(dt);
this.DropDownList2.DataSource = dt;
this.DropDownList2.DataTextField = "RealName";
this.DropDownList2.DataValueField = "UserId";
this.DropDownList2.DataBind();
cmd.Dispose();
conn.Close();
conn.Dispose();
}
protected void Button1_Click(object sender, EventArgs e)
{
string classid = DropDownList1.SelectedItem.Value;
string userid = DropDownList2.SelectedItem.Value;
string connstr = "data source=QZC-PC;DATABASE=News;INTEGRATED SECURITY=TRUE";
SqlConnection conn = new SqlConnection(connstr);
conn.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandText = "insert into T_News(ClassId,NewsCreator,NewsTitle,NewsContent,CreateTime)values(@classid,@newscreator,@newstitle,@newscontent,GETDATE())";
cmd.Parameters.AddWithValue("@classid",classid);
cmd.Parameters.AddWithValue("@newscreator",userid);
cmd.Parameters.AddWithValue("@newstitle",TextBox1.Text);
cmd.Parameters.AddWithValue("@newscontent",TextBox2.Text);
if (cmd.ExecuteNonQuery()>0)
{
Response.Redirect("Demo3.aspx?ispostback=1");
}
}
}
}
最后的结果如下：
