温习ADO

2013-03-10

复习ADOSelectNewsClass.aspx（查询新闻分类）前台bodyform idform1 runatserverdivtabletr

复习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 WHERET1.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 whereId=@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;
        }

热点排行

Web前端

温习ADO