复习ADO

SelectNewsClass.aspx（查询新闻分类）前台

</tr> </table> <div id="divResult" runat="server"></div> </div> </form> </body>

SelectNewsClass.aspx.cs（查询新闻分类）后台

string conStr = @"Data Source=.;Initial Catalog=News;Persist Security Info=True;User ID=sa;Password=111111";

string ispostback = string.Empty; string a = string.Empty;

protected void Page_Load(object sender, EventArgs e) { ispostback = Request.QueryString["ispostback"]; a = Request.QueryString["a"];

if (!IsPostBack) { if (!string.IsNullOrEmpty(Request.QueryString["key"])) { txtClassName.Text = Request.QueryString["key"]; ClassLoad(); } if (ispostback == "1") { ClassLoad(); } if (a == "3") { ClassLoad(); } } }

protected void btnSelectClass_Click(object sender, EventArgs e) { if (string.IsNullOrEmpty(txtClassName.Text)) { txtClassName.Text = "请输入您要查询的数据"; return; }

ClassLoad();

}

private void ClassLoad() { SqlConnection conn = new SqlConnection(conStr); conn.Open(); SqlCommand cmd = new SqlCommand(); cmd.Connection = conn; if (ispostback == "1") { cmd.CommandText = "select T1.Id,T1.ClassName,T2.RealName,T1.CreateTime from T_NewsClass T1 inner join T_User T2 on T1.ClassCreator=T2.UserId Order by T1.Id DESC "; } else { cmd.CommandText = "select T1.Id,T1.ClassName,T2.RealName,T1.CreateTime from T_NewsClass T1 inner join T_User T2 on T1.ClassCreator=T2.UserId where ClassName like '%'+@classname+'%' Order by T1.Id DESC "; cmd.Parameters.AddWithValue("@classname", txtClassName.Text); } SqlDataAdapter adapter = new SqlDataAdapter(cmd); DataTable dt = new DataTable(); adapter.Fill(dt);

StringBuilder sb1 = new StringBuilder(); sb1.Append("<table style='border:2px solid black;' bgcolor='#3399ff'>"); foreach (DataRow row in dt.Rows) { sb1.Append("<tr>"); sb1.Append("<td>" + row["ClassName"].ToString() + "</td>"); sb1.Append("<td>" + row["RealName"].ToString() + "</td>"); sb1.Append("<td>" + row["CreateTime"].ToString() + "</td>"); sb1.Append("<td><a href='EditNewsClass.aspx?id=" + row["Id"].ToString() + "&key=" + txtClassName.Text + "&ChaNews=news1'>编辑</a></td>"); sb1.Append("</tr>"); }

sb1.Append("</table>"); divResult.InnerHtml = sb1.ToString();

cmd.Dispose(); conn.Dispose(); }

protected void btnAssClass_Click(object sender, EventArgs e) { Response.Redirect("AddNewsClass.aspx"); }

EditNewsClass.aspx（修改新闻分类）前台

EditNewsClass.aspx.cs（修改新闻分类）后台

string conStr = @"Data Source=.;Initial Catalog=News;Persist Security Info=True;User ID=sa;Password=111111";

string classid = string.Empty; string key = string.Empty;

protected void Page_Load(object sender, EventArgs e) { classid = Request.QueryString["id"]; key = Request.QueryString["key"]; if (!IsPostBack) { //将数据渲染到界面控件中 ClassLoad(classid);

} }

private void ClassLoad(string classid) { SqlConnection conn = new SqlConnection(conStr); conn.Open(); SqlCommand cmd = new SqlCommand(); cmd.Connection = conn; cmd.CommandText = "select T1.Id,T1.ClassName,T2.RealName,T1.CreateTime from T_NewsClass T1 inner join T_User T2 on T1.ClassCreator=T2.UserId WHERE T1.Id=@id"; cmd.Parameters.AddWithValue("@id", classid);

SqlDataAdapter adapter = new SqlDataAdapter(cmd); DataTable dt = new DataTable(); adapter.Fill(dt); foreach (DataRow row in dt.Rows) { txtClassName.Text = Convert.ToString(row["ClassName"]); //DDLcreator.Text = Convert.ToString(row["RealName"]); }

#region 将创建者绑定到下拉列表

//cmd.Parameters.Clear(); cmd.CommandText = "select UserId,RealName from T_User"; DataTable dtNewsReal = new DataTable(); adapter.Fill(dtNewsReal); this.DLSRealName.DataSource = dtNewsReal; this.DLSRealName.DataTextField = "RealName"; this.DLSRealName.DataValueField = "UserId"; this.DLSRealName.DataBind();

DLSRealName.Items.FindByText(dt.Rows[0]["RealName"].ToString()).Selected = true;

//DLSRealName.Items.FindByValue(dt.Rows[0]["UserId"].ToString()).Selected = true; #endregion

cmd.Dispose(); conn.Dispose(); } protected void btnUpdate_Click(object sender, EventArgs e) { #region 获取用户在新界面输入的数据

string classname = txtClassName.Text; string userid = DLSRealName.SelectedItem.Value; #endregion

#region 建立数据库连接，更改数据

SqlConnection conn = new SqlConnection(conStr); conn.Open(); SqlCommand cmd = new SqlCommand(); cmd.Connection = conn; cmd.CommandText = "UPDATE T_NewsClass SET ClassName=@classname,ClassCreator=@classcreator WHERE Id=@id"; cmd.Parameters.AddWithValue("@classname", classname); cmd.Parameters.AddWithValue("@classcreator", userid); cmd.Parameters.AddWithValue("@id", classid); if (cmd.ExecuteNonQuery() > 0) { Response.Redirect("SelectNewsClass.aspx?key=" + key + "&a=3"); }

#endregion }

AddNewsClass.aspx(添加新闻分类)前台

AddNewsClass.aspx.cs（修改新闻分类）后台

string conStr = @"Data Source=.;Initial Catalog=News;Persist Security Info=True;User ID=sa;Password=111111";

protected void Page_Load(object sender, EventArgs e) { if (!IsPostBack) { LoadUsers(); } }

private void LoadUsers() { SqlConnection conn = new SqlConnection(conStr); 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.DLSRealName.DataSource = dt; this.DLSRealName.DataTextField = "RealName"; this.DLSRealName.DataValueField = "UserId"; this.DLSRealName.DataBind(); cmd.Dispose(); conn.Close(); conn.Dispose(); }

protected void btnAddClass_Click(object sender, EventArgs e) { string classname = txtClassName.Text; string realname = DLSRealName.SelectedItem.Value;

#region 建立数据库连接，插入数据 SqlConnection conn = new SqlConnection(conStr); conn.Open(); SqlCommand cmd = new SqlCommand(); cmd.Connection = conn; cmd.CommandText = "INSERT INTO T_NewsClass(ClassId,ClassName,ClassCreator,CreateTime) VALUES(NEWID(),@classname,@classcreator,GETDATE())"; cmd.Parameters.AddWithValue("@classname", classname); cmd.Parameters.AddWithValue("@classcreator", realname); if (cmd.ExecuteNonQuery() > 0) { Response.Redirect("SelectNewsClass.aspx?ispostback=1"); } cmd.Dispose(); conn.Close(); conn.Dispose(); #endregion }

查询用户管理分类

查询页前台 <div> 查看用户: <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>   <asp:Button ID="Button1" runat="server" OnClick="Button1_Click" Text="查询" />   <asp:Button ID="Button2" runat="server" Text="添加" OnClick="Button2_Click" /> <div id="divmain" runat="server"></div> </div>

查询页后台

string constr = @"data source=.;initial catalog=News;user id=sa;password=111111;";

protected void Page_Load(object sender, EventArgs e) { //判断Request是否为空 if (!string.IsNullOrEmpty(Request.QueryString["RealName"])) { TextBox1.Text = Request.QueryString["RealName"]; showData(); } if (!string.IsNullOrEmpty(Request.QueryString["ispostback"])) { showData(); } }

protected void Button1_Click(object sender, EventArgs e) { //单击倒出相应的数据信息 showData(); }

private void showData() { #region 根据textbox1中的内容查询信息,文本框中如果为空,则查询全部信息 SqlConnection conn = new SqlConnection(constr); conn.Open(); SqlCommand cmd = conn.CreateCommand(); string sql = "";

//判断文本框中是否有内容 if (TextBox1.Text.Trim() == "") { sql = "select * from T_User order by Id desc"; } else { sql = "select * from T_User where RealName like '%'+@name+'%'"; cmd.Parameters.AddWithValue("@name", TextBox1.Text.Trim()); }

cmd.CommandText = sql;

//在内存中开辟一块空间,存储查询出来的信息 SqlDataAdapter adapter = new SqlDataAdapter(cmd); DataTable dt = new DataTable(); adapter.Fill(dt);

conn.Close(); conn.Dispose();

//定义一个字符串 StringBuilder sb1 = new StringBuilder(); sb1.Append("<table>"); foreach (DataRow row in dt.Rows) { sb1.Append("<tr> <td>" + row["UserId"].ToString() + "</td>"); sb1.Append("<td>" + row["UserName"].ToString() + "</td>"); sb1.Append("<td>" + row["Password"].ToString() + "</td>"); sb1.Append("<td>" + row["RealName"].ToString() + "</td>"); sb1.Append("<td>" + row["Mobile"].ToString() + "</td>"); sb1.Append("<td>" + row["Department"].ToString() + "</td>"); sb1.Append("<td> <a href='WebForm2.aspx?Id=" + row["Id"].ToString() + "'>编辑</a></td></tr>"); } sb1.Append("</table>");

//将生成的table写到网页上 divmain.InnerHtml = sb1.ToString();

#endregion }

protected void Button2_Click(object sender, EventArgs e) { //单击跳转添加页 Response.Redirect("AddUser.aspx"); }

编辑页前台------------------------------------------------

编辑页后台------------------------------------------------

string constr = @"data source=.;initial catalog=News;user id=sa;password=111111;";

protected void Page_Load(object sender, EventArgs e) { if (!IsPostBack) { //第一次加载网页时,动态邦定dropdownlist中的内容,以及其它文本框中的内容 dataLoadDepartment(); } }

private void dataLoadDepartment() { #region 加载其它文本框的内容

SqlConnection conn = new SqlConnection(constr); conn.Open(); SqlCommand cmd = conn.CreateCommand();

string sql = "select * from T_User where Id=@Userd"; cmd.Parameters.AddWithValue("@Userd",Request.QueryString["Id"]); cmd.CommandText = sql; SqlDataAdapter adapter = new SqlDataAdapter(cmd); DataTable dt = new DataTable(); adapter.Fill(dt);

userId.Text = dt.Rows[0]["UserId"].ToString(); userName.Text = dt.Rows[0]["UserName"].ToString(); passwords.Text = dt.Rows[0]["Password"].ToString(); realName.Text = dt.Rows[0]["RealName"].ToString(); phoneNumber.Text = dt.Rows[0]["Mobile"].ToString(); #endregion

#region 动态邦定dropdownlist中的内容 string sql1 = "select * from Department"; cmd.CommandText = sql1; DataTable dt1 = new DataTable(); adapter.Fill(dt1); conn.Close(); conn.Dispose();

Deartment.DataSource = dt1; Deartment.DataTextField = "Department_Name"; Deartment.DataValueField = "Department_Id"; Deartment.DataBind();

Deartment.Items.FindByValue(dt.Rows[0]["Department"].ToString()).Selected = true;

#endregion }

protected void Button1_Click(object sender, EventArgs e) { #region 更改数据库中的数据

SqlConnection conn = new SqlConnection(constr); conn.Open(); SqlCommand cmd = conn.CreateCommand(); string sql = "update T_User set UserId=@userId,UserName=@username,Password=@password,RealName=@realname,Mobile=@mobile,Department=@department where Id=@id"; cmd.Parameters.AddWithValue("@userId", userId.Text.Trim()); cmd.Parameters.AddWithValue("@username", userName.Text.Trim()); cmd.Parameters.AddWithValue("@password", passwords.Text.Trim()); cmd.Parameters.AddWithValue("@realname", realName.Text.Trim()); cmd.Parameters.AddWithValue("@mobile", phoneNumber.Text.Trim()); cmd.Parameters.AddWithValue("@department",Deartment.SelectedValue); cmd.Parameters.AddWithValue("@id",Request.QueryString["Id"]); cmd.CommandText = sql; cmd.ExecuteNonQuery(); conn.Close(); conn.Dispose(); Response.Redirect("WebForm1.aspx?RealName="+realName.Text.Trim()); #endregion }

添加用户页前台----------------------------------

</table> </div>

添加用户页后台----------------------------------

string conStr = @"Data Source=.;Initial Catalog=News;Persist Security Info=True;User ID=sa;Password=111111"; bool t = false; protected void Page_Load(object sender, EventArgs e) { if (!IsPostBack) { //网页加载时动态绑定dropdownlist的值 LoadDepartment(); } }

private void LoadDepartment() { //动态绑定dropdownlist SqlConnection conn = new SqlConnection(conStr); conn.Open(); SqlCommand cmd = new SqlCommand(); cmd.Connection = conn; cmd.CommandText = "SELECT Department_Id,Department_Name From Department"; SqlDataAdapter adapter = new SqlDataAdapter(cmd); DataTable dt = new DataTable(); adapter.Fill(dt); this.DDLdepartment.DataSource = dt; this.DDLdepartment.DataTextField = "Department_Name"; this.DDLdepartment.DataValueField = "Department_Id"; this.DDLdepartment.DataBind(); cmd.Dispose(); conn.Close(); conn.Dispose(); }

protected void btnAdd_Click(object sender, EventArgs e) { if (t == false) {

#region 建立数据库连接，向网页文本框中插入数据 SqlConnection conn = new SqlConnection(conStr); conn.Open(); SqlCommand cmd = new SqlCommand(); cmd.Connection = conn; cmd.CommandText = "INSERT INTO T_User(UserId,UserName,Password,RealName,Mobile,Department) VALUES(@userid,@username,@password,@realname,@mobile,@department)";

cmd.Parameters.AddWithValue("@userid", txtUserId.Text.Trim()); cmd.Parameters.AddWithValue("@username", txtUserName.Text.Trim()); cmd.Parameters.AddWithValue("@password", txtRealName.Text.Trim()); cmd.Parameters.AddWithValue("@realname", txtPassword.Text.Trim()); cmd.Parameters.AddWithValue("@mobile", txtMobile.Text.Trim()); cmd.Parameters.AddWithValue("@department", DDLdepartment.SelectedItem.Value);

if (cmd.ExecuteNonQuery() > 0) { Response.Redirect("WebForm1.aspx?ispostback=1"); } cmd.Dispose(); conn.Close(); conn.Dispose(); #endregion } }

protected void txtUserId_TextChanged(object sender, EventArgs e) { //连接数据库判断用户ID是否存在,如果存在则不能进行添加 SqlConnection conn = new SqlConnection(conStr); conn.Open(); SqlCommand cmd = new SqlCommand(); cmd.Connection = conn; cmd.CommandText = "select UserId from T_User where UserId=@userid"; cmd.Parameters.AddWithValue("@userid", txtUserId.Text); SqlDataReader reader = cmd.ExecuteReader(); if (reader.Read()) { Label2.Text = "用户ID已经存在，请重新输入！"; txtUserId.Text = ""; } else { Label2.Text = "此用户ID可用！"; } reader.Dispose(); cmd.Dispose(); conn.Dispose();

}

protected void txtUserName_TextChanged(object sender, EventArgs e) { //连接数据库判断用户名是否存在,如果存在则不能进行添加 SqlConnection conn = new SqlConnection(conStr); conn.Open(); SqlCommand cmd = new SqlCommand(); cmd.Connection = conn; cmd.CommandText = "select UserName from T_User where UserName=@username"; cmd.Parameters.AddWithValue("@username", txtUserName.Text); SqlDataReader reader = cmd.ExecuteReader(); if (reader.Read()) { Label1.Text = "用户名已经存在，请重新输入！"; txtUserName.Text = ""; } else { Label1.Text = "此用户名可以添加！"; }

reader.Dispose(); cmd.Dispose(); conn.Dispose(); //定义一个全局BOOL型变量,来判断是否能添加此条信息 t = true; }