2015-10-29 ADO.NET 第一次课 （增删改查代码）

1、登陆页面前端部分

//demo.aspx 登陆页面前端部分
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="demo.aspx.cs" Inherits="web20150811.demo" %>

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<meta http-equiv="Content-Type" content="text/html; charset=utf-8"/>
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <table>
        <tr><td>用户名：</td><td>
            <asp:TextBox ID="txtUserName" runat="server"></asp:TextBox></td></tr>
        <tr><td>密码：</td><td>
            <asp:TextBox ID="txtPwd" runat="server" TextMode="Password"></asp:TextBox></td></tr>
        <tr><td>
            <asp:Button ID="btnLogin" runat="server" Text="登录" OnClick="btnLogin_Click" /></td></tr>
    </table>
    </form>
</body>
</html>
//demo.aspx.cs登陆页面代码
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
namespace web20150811
{
    public partial class demo : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {

        }

        protected void btnLogin_Click(object sender, EventArgs e)
        {
            string username = txtUserName.Text.Trim();//获取到用户输入的用户名
            string pwd = txtPwd.Text.Trim();//获取到用户输入的密码
            if (string.IsNullOrEmpty(username) || string.IsNullOrEmpty(pwd))
            {
                Response.Write("<script>alert('用户名或密码不为空');</script>");
            }
            else
            {
                //string connStr = "Database=TestDB;Server=.;Integrated Security=false;Uid=sa;Password=123;";//数据库的而连接信息，相当于电话号码
                string connStr = ConfigurationManager.ConnectionStrings["sq_ruanmou"].ToString();
                SqlConnection con = new SqlConnection(connStr);//相当于是电话
                con.Open();//和数据库建立起连接了
                string strSql = string.Format("select * from UserInfor where UserName='{0}' and Pwd='{1}'", username, pwd);//得当sql语句
                using (SqlCommand cmd = new SqlCommand(strSql, con))//cmd去执行sql语句
                {
                    using (SqlDataReader read = cmd.ExecuteReader())//执行sql语句，并将执行之后的结果给read
                    {
                        if (read.Read())
                        {
                            //Response.Write("<script>alert('登录成功');</script>");
                            Response.Redirect("StuAskM.aspx");
                        }
                        else
                        {
                            Response.Write("<script>alert('用户名或密码错误');</script>");
                        }
                        //read.Dispose();
                        //read.Close();
                        //con.Dispose();
                        //con.Close();
                    }
                }
            }
        }


    }
}

2、增删改查StuAskM.aspx前端部分

//增删改查StuAskM.aspx前端部分
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="StuAskM.aspx.cs" Inherits="web20150811.StuAskM" %>

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<meta http-equiv="Content-Type" content="text/html; charset=utf-8"/>
    <title></title>
    <link href="css/StuAskM.css" rel="stylesheet" />
</head>
<body>
    <form id="form1" runat="server">
        <div>
            <table><tr><td>帖子ID：</td><td>
                <asp:TextBox ID="txtSAskId" runat="server" Width="50px"></asp:TextBox></td>
                <td>标题：</td><td>
                    <asp:TextBox ID="txtSTitle" runat="server"></asp:TextBox></td>
                <td>分类：</td><td>
                    <asp:DropDownList ID="ddlSCate" runat="server">
                        <asp:ListItem>---请选择---</asp:ListItem>
                        <asp:ListItem Value="1">网页前端</asp:ListItem>
                        <asp:ListItem Value="2">数据库</asp:ListItem>
                        <asp:ListItem Value="3">.NET</asp:ListItem>
                        <asp:ListItem Value="4">PS</asp:ListItem>
                    </asp:DropDownList></td>
                <td>
                    <asp:Button ID="btnSel" runat="server" Text="查询" OnClick="btnSel_Click" /></td>
                   </tr></table>
        </div>

        <div>
            <table>
                <tr><td>标题：</td><td>
                    <asp:TextBox ID="txtATitle" runat="server"></asp:TextBox></td></tr>
                <tr><td>内容：</td><td>
                    <asp:TextBox ID="txtAText" runat="server" TextMode="MultiLine" Width="200px" Height="100px"></asp:TextBox></td></tr>
                <tr><td>分类：</td><td>  <asp:DropDownList ID="ddlACate" runat="server">
                        <asp:ListItem>---请选择---</asp:ListItem>
                        <asp:ListItem Value="1">网页前端</asp:ListItem>
                        <asp:ListItem Value="2">数据库</asp:ListItem>
                        <asp:ListItem Value="3">.NET</asp:ListItem>
                        <asp:ListItem Value="4">PS</asp:ListItem>
                    </asp:DropDownList></td></tr>
                <tr><td>用户ID：</td><td>
                    <asp:TextBox ID="txtAUserIds" runat="server" Width="50px"></asp:TextBox></td></tr>
                <tr><td colspan="2">
                    <asp:Button ID="btnAdd" runat="server" Text="添加" OnClick="btnAdd_Click" /></td></tr>
            </table>
        </div>

        <div><table>
            <tr><td>帖子ID：</td><td>
                <asp:TextBox ID="txtDAskId" runat="server" style="width: 148px"></asp:TextBox></td><td>
                    <asp:Button ID="btnDel" runat="server" Text="删除" OnClick="btnDel_Click" /></td></tr>
             </table></div>

        <div>
            <table>
                <tr><td>帖子ID：</td><td> <asp:TextBox ID="txtUAskId" runat="server" style="width: 148px"></asp:TextBox></td></tr>
              <tr><td>标题：</td><td>
                  <asp:TextBox ID="txtUTitle" runat="server"></asp:TextBox></td></tr>
                <tr><td>
                    <asp:Button ID="btnUpdate" runat="server" Text="更新" OnClick="btnUpdate_Click" /></td></tr>
            </table>
        </div>
    <div>
        <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" OnRowDataBound="GridView1_RowDataBound">
            <Columns>
                <asp:BoundField DataField="AskId" HeaderText="ID" />
                <asp:BoundField DataField="Title" HeaderText="标题" />
                <asp:BoundField DataField="AskCategory" HeaderText="分类" />
                <asp:BoundField DataField="CreateTime" HeaderText="创建时间" />
                <asp:BoundField DataField="UserId" HeaderText="用户id" />
            </Columns>
        </asp:GridView>
    </div>
    </form>
</body>
</html>
//增删改查代码部份StuAskM.aspx.cs
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Text;
namespace web20150811
{
    public partial class StuAskM : System.Web.UI.Page
    {
        string connStr = ConfigurationManager.ConnectionStrings["sq_ruanmou"].ToString();
        SqlConnection con;
        SqlCommand cmd;
        SqlDataReader read;
        /// <summary>
        /// 连接数据库
        /// </summary>
        private void OpenDB()
        {
            con = new SqlConnection(connStr);//相当于是电话
            con.Open();
        }

        /// <summary>
        /// 得到sql语句
        /// </summary>
        /// <returns></returns>
        private string GetSql()
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("select AskId,Title,AskCategory,CreateTime,UserId from  StuAsk where 1=1 ");
            if (!string.IsNullOrEmpty(txtSAskId.Text.Trim()))
            {
                sb.Append(string.Format(" and AskId={0}", Convert.ToInt32(txtSAskId.Text.Trim())));
            }
            if (!string.IsNullOrEmpty(txtSTitle.Text.Trim()))
            {
                sb.Append(string.Format(" and Title like  '%{0}%'", txtSTitle.Text.Trim()));
            }
            if (ddlSCate.SelectedIndex > 0)
            {
                sb.Append(string.Format(" and AskCategory={0}", Convert.ToInt32(ddlSCate.SelectedValue)));
            }
            return sb.ToString();
        }
        /// <summary>
        /// 数据绑定
        /// </summary>
        private void BindAsk()
        {
            OpenDB();
            string strSql = GetSql();
            using (cmd = new SqlCommand(strSql, con))
            {
                using (read = cmd.ExecuteReader())//对数据库做查询ExecuteReader()
                {
                    GridView1.DataSource = read;
                    GridView1.DataBind();
                }
            }
        }
        protected void Page_Load(object sender, EventArgs e)
        {
            BindAsk();
        }

        /// <summary>
        /// 绑定每一行数据
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
        {
            if (e.Row.RowType == DataControlRowType.DataRow)//判断是数据行
            {
                string cate = e.Row.Cells[2].Text;
                switch (cate)
                {
                    case "1":
                        e.Row.Cells[2].Text = "网页前端";
                        break;
                    case "2":
                        e.Row.Cells[2].Text = "数据库";
                        break;
                    case "3":
                        e.Row.Cells[2].Text = ".NET";
                        break;
                    case "4":
                        e.Row.Cells[2].Text = "PS";
                        break;
                    default:
                        break;
                }
                DateTime cdate = Convert.ToDateTime(e.Row.Cells[3].Text);
                e.Row.Cells[3].Text = cdate.ToLongDateString().ToString();
            }
        }

        /// <summary>
        /// 查询
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        protected void btnSel_Click(object sender, EventArgs e)
        {
            BindAsk();
        }

        /// <summary>
        /// 添加
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        protected void btnAdd_Click(object sender, EventArgs e)
        {
            try
            {
                string title = txtATitle.Text.Trim();
                string text = txtAText.Text.Trim();
                int cate = ddlACate.SelectedIndex > 0 ? Convert.ToInt32(ddlACate.SelectedValue) : 1;
                int userid = string.IsNullOrEmpty(txtAUserIds.Text.Trim()) ? 0 : Convert.ToInt32(txtAUserIds.Text.Trim());
                OpenDB();
                string strSql = string.Format("insert into StuAsk(Title,Text,AskCategory,CreateTime,UserId) values('{0}','{1}',{2},'{3}',{4})",
                    title, text, cate, DateTime.Now.ToString(), userid);
                using (cmd = new SqlCommand(strSql, con))
                {
                    cmd.ExecuteNonQuery();//对数据库进行增加、删除、修改ExecuteNonQuery
                }
                BindAsk();
                Response.Write("<script>alert('添加成功');</script>");
            }
            catch (Exception ex)
            {
                Response.Write("网站正在维修中...");
            }
        }

        /// <summary>
        /// 删除
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        protected void btnDel_Click(object sender, EventArgs e)
        {
            int askid = Convert.ToInt32(txtDAskId.Text.Trim());
            //string strSql = string.Format("select AskId from StuAsk where AskId={0} ", askid);
            //using (cmd = new SqlCommand(strSql, con))
            //{
            //    using (read = cmd.ExecuteReader())
            //    {
            //        if (!read.Read())
            //        {
            //            Response.Write("<script>alert('这个帖子不存在');</script>");
            //        }
            //        else { 
                    
            //        }
            //    }
            //}

            string strSql = string.Format("select COUNT(*) from StuAsk where AskId={0}", askid);
            using (cmd = new SqlCommand(strSql, con))
            {
                if (Convert.ToInt32(cmd.ExecuteScalar().ToString()) == 0)
                {
                    Response.Write("<script>alert('这个帖子不存在');</script>");
                }
                else
                {
                    string s = string.Format("delete StuAsk where AskId={0}", askid);
                    using (cmd = new SqlCommand(s, con))
                    {
                        cmd.ExecuteNonQuery();
                    }
                }
            }
            BindAsk();
            Response.Write("<script>alert('删除成功');</script>");
        }

        /// <summary>
        /// 更新
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        protected void btnUpdate_Click(object sender, EventArgs e)
        {
            int askid = Convert.ToInt32(txtUAskId.Text.Trim());
            string title = txtUTitle.Text.Trim();
            string strSql = string.Format("update StuAsk set Title='{0}' where AskId={1}", title, askid);
            using (cmd = new SqlCommand(strSql, con))
            {
                cmd.ExecuteNonQuery();
            }
            BindAsk();
            Response.Write("<script>alert('更新成功');</script>");
        }
    }
}

3、数据库连接部分Web.config

//数据库连接部分Web.config
<?xml version="1.0" encoding="utf-8"?>
<!--
  有关如何配置 ASP.NET 应用程序的详细信息，请访问
  http://go.microsoft.com/fwlink/?LinkId=169433
  -->
<configuration>
  <connectionStrings>
    <add name="sq_ruanmou" connectionString="Database=sq_ruanmou;Server=.;Integrated Security=false;Uid=sa;Password=123;"    providerName="System.Data.SqlClient"/>
  </connectionStrings>

  <system.web>
    <compilation debug="true" targetFramework="4.5" />
    <httpRuntime targetFramework="4.5" />
  </system.web>
</configuration>
//CSS部份
body {
    font-size: 14px;
    font-family: "微软雅黑";
}
div {
margin-bottom:10px;
}
table {
border-collapse:collapse;
}
td {
    text-align: center;
    border:solid 1px #cccccc;
}
posted @ 2015-11-03 11:38  75-逢场作戏  阅读(204)  评论(0)  编辑  收藏  举报