Asp.net实现简单的图书管理功能,简单用了HttpHandler详细页重定向,数据库存储图像,以及图像在GridView中显示,模糊查询,高级查询

第一次发博客,这是写过的一个作业,学的不是很到位,写的也很简单~

需求如下:

          

网上书店系统中,有书籍表,表结构如下:

书号(主键),书名,价格,出版社和书的封面;

书的封面字段存储的是图片文件的路径。图片的源文件存在web项目的某个子目录中。

用户表中包含的数据是用户名(主键),年龄,密码,Email。

(1).按照要求完成数据库的创建。

(2).以类库方式,读取配置文件,连接数据库。

(3).完成用户注册,注册页面中用户名不能为空,年龄是整数,Email格式正确;(JavaScrpit)

(4).构建用户登录界面,验证用户名和密码,如果登录成功,把用户名存入Session中,跳转到主页面,显示用户名。

(5)用户登录后可以根据书名进行模糊查询,支持高级查询(书名,价格等综合查询)。将查询结果以表格方式显示在界面上,表格中显示的是书的名字和价格。

(6)表格中添加查看按钮列,用户点击查询,可以跳转页面显示这本书的详细信息,包括封面图片。要求用到重定向。(HttpHandler)

(7) 表格中添加下载按钮列,用户点击下载书的封面。

(8) 可以把查询结果保存为excel文件。

详细步骤:

1、创建数据库:

书籍表:

USE [bookshop]
GO

/****** Object:  Table [dbo].[book]    Script Date: 2018/6/8 10:39:09 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[book](
	[bookid] [int] IDENTITY(1,1) NOT NULL,
	[bookname] [nchar](10) NOT NULL,
	[price] [money] NULL,
	[adress] [nvarchar](50) NULL,
	[pic] [nvarchar](50) NULL,
 CONSTRAINT [PK_book] PRIMARY KEY CLUSTERED 
(
	[bookid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO



用户表:

USE [bookshop]
GO

/****** Object:  Table [dbo].[userinfo]    Script Date: 2018/6/8 10:39:57 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[userinfo](
	[userID] [nchar](50) NOT NULL,
	[password] [nchar](50) NOT NULL,
	[userage] [nchar](10) NULL,
	[Email] [nchar](50) NULL,
 CONSTRAINT [PK_userinfo] PRIMARY KEY CLUSTERED 
(
	[userID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO



2、以类库方式,读取配置文件,连接数据库

数据库连接,操作类库

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;


namespace MySqlTool
{
    public class Tool
    {
        /// <summary>
        /// sql执行
        /// </summary>
        /// <param name="ConnectionStrings">连接串</param>
        /// <param name="strSql">sql语句</param>
        /// <param name="paras">SqlParameter集合</param>
        /// <param name="cmdType">CommandType类型</param>
        /// <returns>返回DataSet结果集</returns>
        public DataSet ExecuteSql(string ConnectionStrings,string strSql, SqlParameter[] paras, CommandType cmdType)
        {
            SqlConnection con = new SqlConnection(ConnectionStrings);
            con.Open();
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandType = cmdType;
            cmd.CommandText = strSql;
            foreach (SqlParameter p in paras)
            {
                cmd.Parameters.Add(p);
            }


            SqlDataAdapter data = new SqlDataAdapter(cmd);
             DataSet rs = new DataSet(); //内存的数据库
            data.Fill(rs, "temp");


            con.Close();
            return rs;
           
        }




        /// <summary>
        /// sql插入数据方法
        /// </summary>
        /// <param name="ConnectionStrings">连接串</param>
        /// <param name="strSql">sql语句</param>
        /// <param name="paras">SqlParameter集合</param>
        /// <param name="cmdType">CommandType类型</param>
        /// <returns>返回受影响的行数</returns>
        public int insertSql(string ConnectionStrings, string strSql, SqlParameter[] paras, CommandType cmdType)
        {
            SqlConnection con = new SqlConnection(ConnectionStrings);
            con.Open();
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandType = cmdType;
            cmd.CommandText = strSql;
            foreach (SqlParameter p in paras)
            {
                cmd.Parameters.Add(p);
            }


            int num = cmd.ExecuteNonQuery();//执行


            con.Close();
            return num;


        }  


        public int SqlReturnLine(string connstr, string sql)
        {
            SqlConnection conn = new SqlConnection(connstr);
            conn.Open();
            SqlCommand command = new SqlCommand(sql, conn);
            object Onum = command.ExecuteScalar();
            int num = Convert.ToInt32(Onum);
            return num;
        }




        /// <summary>
        /// 登录验证
        /// </summary>
        /// <param name="username"></param>
        /// <param name="password"></param>
        /// <param name="connectstring"></param>
        /// <returns></returns>
        public bool SqlDataCheck(SqlParameter[]paras, string connectstring,string sql)
    {
        SqlConnection conn = new SqlConnection(connectstring);//where userID=@username
        conn.Open();
        SqlCommand mycom = new SqlCommand();         //创建SQL命令执行对象
        mycom.Connection=conn;
        mycom.CommandText = sql;                           //执行SQL命令
        SqlDataAdapter myDA = new SqlDataAdapter();       //实例化数据适配器
            foreach (SqlParameter p in paras)
            {
                mycom.Parameters.Add(p);
            }


            SqlDataAdapter data = new SqlDataAdapter(mycom);
            DataSet rs = new DataSet();   //实例化结果数据集
            int n = data.Fill(rs, "temp");//将结果放入数据适配器,返回元祖个数          
        if (n != 0)
        {
            return true;
        }
        else return false;


    }
    }
}

3、用户注册功能,注册页面中用户名不能为空,年龄是整数,Email格式正确


验证功能直接用的验证控件。

注册功能代码:

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

public partial class register : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {

    }
    protected void Button1_Click(object sender, EventArgs e)
    {
        string connstr = System.Configuration.ConfigurationManager.ConnectionStrings["myconnstr"].ConnectionString;
        try
        {
        string insertsql = "insert into [userinfo] ([userID],[password],[userage],[Email]) values (@myuserID,@mypassword,@myuserage,@myEmail) ";
        SqlParameter[] parameters = {   
                        new SqlParameter("@myuserID", this.TBusername.Text),
                        new SqlParameter("@mypassword", this.TBpwd.Text),
                         new SqlParameter("@myuserage", this.TBage.Text),
                          new SqlParameter("@myEmail", this.TBemail.Text)
                        
                                    };
        MySqlTool.Tool tool = new MySqlTool.Tool();
        int rs = tool.insertSql(connstr, insertsql, parameters, CommandType.Text);
        this.Label1.Text = "注册成功";
          this.Label1.Visible = true;
          Session["LoginUser"] = this.TBusername.Text.Trim();
        Response.Redirect("Main.aspx");
        }
        catch
        {
            this.Label1.Text = "用户名已经存在!";
            this.Label1.Visible = true;
            //
        }
    }
    protected void LinkButton1_Click(object sender, EventArgs e)
    {
        Response.Redirect("login.aspx");
    }
}

4、构建用户登录界面,验证用户名和密码,如果登录成功,把用户名存入Session中,跳转到主页面,显示用户名。

普通用户、管理员登录,普通用户登录只能查询,管理员可以增加:

登陆成功后进入用户主界面,查询书籍:

管理员登录后跳转到添加图书信息界面:

登录功能代码:

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

public partial class login : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {

    }
  
    protected void LinkButton1_Click(object sender, EventArgs e)
    {
        Response.Redirect("register.aspx");//重定向到注册页面
    }
    protected void Button1_Click(object sender, EventArgs e)
    {
       
        string connectstring = System.Configuration.ConfigurationManager.ConnectionStrings["myconnstr"].ConnectionString;


        string username = TextBox1.Text.Trim();  //取出账号

        string pw = TextBox2.Text.Trim();         //取出密码

        string s1 = "select userID,password from userinfo where userID=@username and password=@password";                                            //编写SQL命令
        MySqlTool.Tool tool = new MySqlTool.Tool();
        SqlParameter[] parameters = {   
                        new SqlParameter("@username",username.Trim()), 
                        new SqlParameter("@password",pw.Trim())
                                        };
        bool temp = tool.SqlDataCheck(parameters, connectstring, s1);
        if (temp == true)
        {
            Label3.Text = "登录成功";
            Label3.Visible = true;
            Session["LoginUser"] = TextBox1.Text.Trim();       
            Response.Redirect("Main.aspx");
        }
        else
        {
            Label3.Text = "用户名或密码有错。请重新输入!";
            Label3.Visible = true;
            TextBox1.Text = "";   //清空账号
            TextBox2.Text = "";    //清空密码?
            TextBox1.Focus();     //光标设置在账号上
        }     
    }
    protected void LinkButton2_Click(object sender, EventArgs e)
    {
        Response.Redirect("loginAd.aspx");
    }
   
}

5、用户登录后可以根据书名进行模糊查询,支持高级查询(书名,价格等综合查询)。将查询结果以表格方式显示在界面上,表格中显示的是书的名字和价格。

上传几组图书数据:


数据库中数据:

上传功能代码:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
//add
using System.Data.SqlClient;
using System.Data; 
public partial class Default2 : System.Web.UI.Page
{
    //实例化tool类
    MySqlTool.Tool tool = new MySqlTool.Tool();
    protected void Page_Load(object sender, EventArgs e)
    {

        if (Session["LoginUser"] == null)  //上面Session名字用LoginUser,这里的名字要和上面的相同,可以随便改,保持一致就行了,注意:Session["LoginUser"]后面,没有ToString()哦,你要是加上的话,就会出错了.
        {
            Response.Redirect("login.aspx"); //如果Session为空,则转到登陆页.
        }
    }
    protected void Button1_Click(object sender, EventArgs e)
    {
     
    }
    protected void Button2_Click(object sender, EventArgs e)
    {   
        int bookid;//定义bookid,用作生成图片序号。
        //定义连接串
        string connstr = System.Configuration.ConfigurationManager.ConnectionStrings["myconnstr"].ConnectionString;
     //定义查询表中行数sql语句
        string numinsertsql = "select count(1) from [dbo].[book]";

        bookid = tool.SqlReturnLine(connstr, numinsertsql) + 1;//调用SqlReturnLine方法获取数据库行数
      
        string path = Server.MapPath("") + "\\upload\\" + bookid.ToString() + ".jpg";//定义图片存储路径
        FileUpload1.SaveAs(path);//图片存储

        //定义sql语句,插入book数据到数据库
        string insertsql = "insert into Book (bookname,price,adress,pic) values (@bookname,@myprice,@myadress,@mypic) ";
        //定义SqlParameter数组,存储参数
        SqlParameter[] parameters = {   
                        new SqlParameter("@bookname",this.TextBox1.Text),
                        new SqlParameter("@myprice",this.TextBox2.Text),
                         new SqlParameter("@myadress",this.TextBox3.Text),
                          new SqlParameter("@mypic","~\\upload\\" + bookid.ToString()+".jpg")
                                    };


        int rs = tool.insertSql(connstr, insertsql, parameters, CommandType.Text);//调用insertSql方法插入数据到数据库
        if (rs != null)
        {
            this.Label1.Text = "上传文件到web子目录成功";
            this.Label1.Visible = true;
        }
    }
    protected void Button3_Click(object sender, EventArgs e)
    {
        //定义连接串
        string connstr = System.Configuration.ConfigurationManager.ConnectionStrings["myconnstr"].ConnectionString;
        string selectSql = "select * from book ";//定义查询sql语句
        SqlParameter[] parameters = { };

        DataSet rs = tool.ExecuteSql(connstr, selectSql, parameters, CommandType.Text);//调用ExecuteSql方法返回Dataset
        //将数据传递到GridView1
        this.GridView1.DataSource = null;
        this.GridView1.DataSourceID = null;
        this.GridView1.DataSource = rs.Tables[0];
        this.DataBind();
      
    }
    protected void GridView1_RowCommand(object sender, GridViewCommandEventArgs e)
    {

      
        //下载封面,按钮事件
        string abc = e.CommandArgument as string;
        string connstr = System.Configuration.ConfigurationManager.ConnectionStrings["myconnstr"].ConnectionString;
        string bd = e.CommandName as string;
        Response.AddHeader("Content-Disposition", "attachment;filename=test.jpg");
        string filename = Server.MapPath("upload/" + abc + ".jpg");
        Response.TransmitFile(filename);  
    }
  
}

跳转到用户查询界面:

(1)不添加查询条件,默认查询全部。

(2)模糊查询,可以查询到包含java的书籍

 

(3)高级查询,可以精确查询到一本书

相关代码:

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Net;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

public partial class Main : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (Session["LoginUser"] == null)  //上面Session名字用LoginUser,这里的名字要和上面的相同,可以随便改,保持一致就行了,注意:Session["LoginUser"]后面,没有ToString()哦,你要是加上的话,就会出错了.
        {
            Response.Redirect("login.aspx"); //如果Session为空,则转到登陆页.
        }
    }
    protected void Button1_Click(object sender, EventArgs e)
    {
        //查询
        string connstr = System.Configuration.ConfigurationManager.ConnectionStrings["myconnstr"].ConnectionString;
        string selectSql = "select * from book where (bookname like '%'+@mybookname2+'%')and(adress like '%'+@myadress+'%')and(price like '%'+@myprice+'%')";
        SqlParameter[] parameters = {   
                        new SqlParameter("@mybookname2", this.TextBox2.Text.Trim()),
                                    new SqlParameter("@myprice", this.TextBox3.Text.Trim()),
                                    new SqlParameter("@myadress", this.TextBox4.Text.Trim()),
                                    };

        MySqlTool.Tool tool = new MySqlTool.Tool();
        DataSet rs = tool.ExecuteSql(connstr, selectSql, parameters, CommandType.Text);

        //bind to view 
        this.GridView1.DataSource = null;
        this.GridView1.DataSourceID = null;
        this.GridView1.DataSource = rs.Tables[0];
        this.GridView1.Visible = true;
        this.DataBind();
    }
    protected void Button2_Click(object sender, EventArgs e)
    {
        //查询
        string connstr = System.Configuration.ConfigurationManager.ConnectionStrings["myconnstr"].ConnectionString;
        string selectSql = "select * from book where 1=1";
        List<SqlParameter> ilistStr = new List<SqlParameter>();
        if (TextBox5.Text.ToString()!=null&&TextBox5.Text.Length!=0)
        {
            ilistStr.Add(new SqlParameter("@mybookname2", this.TextBox5.Text.Trim()));
            selectSql += "and (bookname=@mybookname2)";
        }
        if (TextBox6.Text.ToString() != null && TextBox6.Text.Length != 0)
        {
            ilistStr.Add(new SqlParameter("@myprice", this.TextBox6.Text.Trim()));
            selectSql += "and (price=@myprice)";
        }
        if (TextBox7.Text.ToString() != null && TextBox7.Text.Length != 0)
        {
            ilistStr.Add(new SqlParameter("@myadress", this.TextBox7.Text.Trim()));
            selectSql += "and (adress=@myadress)";
        }

        SqlParameter[] param = ilistStr.ToArray();
        MySqlTool.Tool tool = new MySqlTool.Tool();
        DataSet rs = tool.ExecuteSql(connstr, selectSql, param, CommandType.Text);

        this.GridView1.DataSource = null;
        this.GridView1.DataSourceID = null;
        this.GridView1.DataSource = rs.Tables[0];
        this.GridView1.Visible = true;
        this.DataBind();
 
       
    }
    protected void GridView1_RowCommand(object sender, GridViewCommandEventArgs e)
    {
        

        if (e.CommandName == "LinkButton")
        {
            Response.Write("第一个按钮");
            string abc = e.CommandArgument as string;
            Response.Redirect("~/books/" + abc + ".shtml");


        }
        else
        {
            //下载封面,按钮事件
            string abc = e.CommandArgument as string;
            string connstr = System.Configuration.ConfigurationManager.ConnectionStrings["myconnstr"].ConnectionString;
            string bd = e.CommandName as string;       
            Response.AddHeader("Content-Disposition", "attachment;filename=test.jpg");  
            string filename = Server.MapPath("upload/"+abc+".jpg");  
            Response.TransmitFile(filename);  
            }  
        }
    
    
}



6、表格中添加查看按钮列,用户点击查询,可以跳转页面显示这本书的详细信息,包括封面图片。要求用到重定向。(HttpHandler),根据书籍ID生成重定向页面。

 

相关代码:

webconfig中添加:

  <httpHandlers>
      <!-- verb 请求的方式,post ,get  path对谁进行拦截,预处理;type=类名(httpHandler,完整的类名) -->
      <!--<add verb="*" path="back/*.jpg" type="mytool.StringHandler"/>-->
      <!--<add verb="*" path ="img/*.jpg" type="mytool.LogoPicHandler"/>-->
      <add verb="*" path="books/*.shtml" type="mytool.ReWrite"/>
    </httpHandlers>
建一个rewrite类:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

/// <summary>
/// ReWrite 的摘要说明
/// </summary>
namespace mytool
{
    public class ReWrite : IHttpHandler
    {


        public bool IsReusable
        {
            get { return false; }
        }

        public void ProcessRequest(HttpContext context)
        {
            //throw new NotImplementedException();
            string Url = context.Request.Url.ToString();
            string id = Url.Substring(Url.LastIndexOf("/") + 1);
            string idd = id.Replace(".shtml", "");
            context.Server.Execute("~/DefaultBook.aspx?bid=" + idd);
        }
    }
}


7、表格中添加下载按钮列,用户点击下载书的封面,可以把查询结果保存为excel文件。

GridView1中查看和下载功能代码:

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.IO;
using Microsoft.Office.Interop; 
using Microsoft.Office.Interop.Excel;
public partial class DefaultBook : System.Web.UI.Page
{
    DataSet rs = new DataSet(); //内存的数据库
    MySqlTool.Tool tool = new MySqlTool.Tool();//实例化tool类
    protected void Page_Load(object sender, EventArgs e)
    {
        try
        {

            this.Label1.Text = Request["bid"];
            string bookid = Request["bid"];//获取bookid
            //定义连接串
            string connstr = System.Configuration.ConfigurationManager.ConnectionStrings["myconnstr"].ConnectionString;
            string selectSql = "select * from book where bookid=@mybookid";//定义查询sql语句
            //定义SqlParameter数组,存储参数
            SqlParameter[] parameters = {   
                        new SqlParameter("@mybookid",bookid)  
                                        };
            //调用ExecuteSql方法返回Dataset
            DataSet rs = tool.ExecuteSql(connstr, selectSql, parameters, CommandType.Text);
            this.GridView1.DataSource = null;
            this.GridView1.DataSourceID = null;
            this.GridView1.DataSource = rs.Tables[0];   //将数据传递到GridView1
            this.GridView1.Visible = true;
            this.DataBind();           
        }
        catch {

            Response.Redirect("login.aspx");//捕获异常,重定向到登录页
        
        }
    }

    
    
    protected void Button1_Click(object sender, EventArgs e)
    {
        Export(".xls", "test.xls");//下载excel文件
      
    }
   
    private void Export(string FileType, string FileName)
    {
        //
        Response.Charset = "GB2312";
        Response.ContentEncoding = System.Text.Encoding.UTF8;
        //HTTP协议规定的,PHP,JSP,ASP.NET  web
        Response.AppendHeader("Content-Disposition", "attachment;filename=" + FileName);
        Response.ContentType = FileType;
        //页面的状态
        this.EnableViewState = false;

        StringWriter tw = new StringWriter(); //reader ,writer 字符流         
        HtmlTextWriter hw = new HtmlTextWriter(tw);

         int nHideCols = 0;
         for (int i = 0; i < GridView1.Columns.Count; i++)
         {
             if (GridView1.Columns[i].HeaderText == "封面")
             {
                 GridView1.Columns[i].Visible = false;
                 GridView1.Columns[i].ControlStyle.Width = 0;
                 nHideCols = 1;
                 break;
             }
         }
        GridView1.RenderControl(hw); //error 的原因,runat server 覆盖一个处理
        Response.Write(tw.ToString());
        Response.End();
    }

    public override void VerifyRenderingInServerForm(Control control)
    {
        //base.VerifyRenderingInServerForm(control);
    }
}

目前就写成了这样,感觉还有好多地方需要优化,希望和大佬交流学习。大部分代码都已经粘在这里了,完整代码放在下载页了,地址:点击打开链接





已标记关键词 清除标记
相关推荐
©️2020 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页