东莞职业技术学院asp考试资料2.doc

一、 建数据库BOOK：bookid bookname author publisher pubdate summary price imageCAT：bookid username quantityUSER：username password email question answer role二、 母版与CSS （20分钟） 三、 注册与登录（1）注册（register） protected void Button1_Click(object sender, EventArgs e) 通用部分 sqlCmd.CommandText = insert into user(username,password,email, question,answer,role) values(username,Password,email,question,answer,0); sqlCmd.Parameters.AddWithValue(username, TextBox1.Text); sqlCmd.Parameters.AddWithValue(password, TextBox2.Text); sqlCmd.Parameters.AddWithValue(email, TextBox4.Text); sqlCmd.Parameters.AddWithValue(question, TextBox5.Text); sqlCmd.Parameters.AddWithValue(answer, TextBox6.Text); sqlCmd.Connection = sqlConn; try sqlConn.Open(); sqlCmd.ExecuteNonQuery(); Response.Redirect(registerok.aspx); catch(SqlException ex) Response.Redirect(registerfailure.aspx); finally sqlConn.Close(); （2）登录（login）protected void Button1_Click(object sender, EventArgs e) SqlConnection sqlConn = new SqlConnection(); sqlConn.ConnectionString = Data Source=.SQLEXPRESS; + AttachDbFileName=|DataDirectory|Book.mdf; + Integrated Security=True;User Instance=True; SqlCommand sqlCmd = new SqlCommand(); sqlCmd.CommandText= select*fromuserwhere(username=uname)and(password=pword); sqlCmd.Parameters.AddWithValue(uname, TextBox1.Text); sqlCmd.Parameters.AddWithValue(pword, TextBox2.Text); sqlCmd.Connection = sqlConn; try sqlConn.Open(); SqlDataReader sqlDR = sqlCmd.ExecuteReader(); if (sqlDR.HasRows) Sessionusername = TextBox1.Text; Response.Redirect(loadok.aspx); else Response.Redirect(loadfailure.aspx); catch (SqlException ex) Response.Redirect(registerfailure.aspx?ex=+ex.Message); finally sqlConn.Close(); 四、 数据库操作(1) 搜索（在主页中 page_load事件）public partial class index : System.Web.UI.Page protected void Page_Load(object sender, EventArgs e) if (IsPostBack = false) GridView1.DataSource = GetDataSource();/设置 GridView控件的数据源 GridView1.DataBind();/与数据源绑定 private DataSet GetDataSource() SqlConnection sqlConn = new SqlConnection();/新建数据库连接对象 sqlConn.ConnectionString = Data Source=.SQLEXPRESS; + AttachDbFileName=|DataDirectory|Book.mdf; + Integrated Security=True;User Instance=True; SqlCommand sqlCmd = new SqlCommand();switch (DropDownList1.SelectedItem.Text) case 出版社: sqlCmd.CommandText = select * frombook where publisher Like %+publisher+%;/SQl语句 sqlCmd.Parameters.AddWithValue(publisher, TextBox1.Text);/赋值 break; case 书名: sqlCmd.CommandText = select * frombook where bookname Like %+bookname+%;/SQl语句 sqlCmd.Parameters.AddWithValue(bookname, TextBox1.Text);/赋值 break; case 作者: sqlCmd.CommandText = select * frombook where author Like %+author +%;/SQl语句 sqlCmd.Parameters.AddWithValue(author, TextBox1.Text);/赋值 break; sqlCmd.Connection = sqlConn;SqlDataAdapter sqlDA = new SqlDataAdapter();/新建的桥接器获取查询的结果 sqlDA.SelectCommand = sqlCmd;/设置桥接器使用的数据库的执行对象 DataSet ds = new DataSet();/新建数据集，用于保存查询结果try sqlConn.Open(); /SqlDataReader sqlDR = sqlCmd.ExecuteReader();/使用连线模式，不支持分页 sqlDA.Fill(ds);/将查询结果保存在ds中 catch (SqlException ex) Response.Write(ex.Message); finallysqlConn.Close(); return ds; protected void GridView1_PageIndexChanging(object sender, GridViewPageEventArgs e) GridView1.PageIndex = e.NewPageIndex;/获取当前页码编号 GridView1.DataSource = GetDataSource(); GridView1.DataBind(); protected void Button1_Click(object sender, EventArgs e) GridView1.DataSource = GetDataSource(); GridView1.DataBind();protected void Page_Load(object sender, EventArgs e) if (IsPostBack = false) SqlConnection sqlConn = new SqlConnection(); sqlConn.ConnectionString = Data Source=.SQLEXPRESS; + AttachDbFileName=|DataDirectory|Book.mdf; + Integrated Security=True;User Instance=True; SqlCommand sqlCmd = new SqlCommand(); sqlCmd.Connection = sqlConn; sqlCmd.CommandText = select * from book ;/构造SQL语句 try sqlConn.Open(); SqlDataReader sqlDR = sqlCmd.ExecuteReader();/ GridView1.DataSource = sqlDR;/设置 GridView控件的数据源 GridView1.DataBind();/与数据源绑定 catch (SqlException ex) Response.Write(ex.Message); finally sqlConn.Close(); protected void Button1_Click(object sender, EventArgs e) SqlConnection sqlConn = new SqlConnection();/新建数据库连接对象 sqlConn.ConnectionString = Data Source=.SQLEXPRESS; + AttachDbFileName=|DataDirectory|Book.mdf; + Integrated Security=True;User Instance=True;/连接字符串，设置连接数据库的类型、路径、验证方式 SqlCommand sqlCmd = new SqlCommand(); sqlCmd.Connection = sqlConn; sqlCmd.CommandText = select * from book where bookname like %+bookname+ %;/构造SQL语句 sqlCmd.Parameters.AddWithValue(bookname, TextBox1.Text); try sqlConn.Open(); SqlDataReader sqlDR = sqlCmd.ExecuteReader();/使用连线模式，不支持分页 GridView1.DataSource = sqlDR;/设置 GridView控件的数据源 GridView1.DataBind();/与数据源绑定 catch (SqlException ex) Response.Write(ex.Message); finally sqlConn.Close(); (2) 查询(3) 更新 sqlCmd.CommandText =UPDATE cart SET quantity =quantity WHERE (username = username) AND (bookid = bookid); sqlCmd.Parameters.AddWithValue(username,Sessionusername); sqlCmd.Parameters.AddWithValue(bookid, HiddenField1.Value); sqlCmd.Parameters.AddWithValue(quantity, TextBox1.Text);/赋值 try sqlConn.Open();/打开数据库连接 sqlCmd.ExecuteNonQuery(); Response.Redirect(cart.aspx); (4) 删除protected void Page_Load(object sender, EventArgs e) if (IsPostBack = false) SqlConnection sqlConn = new SqlConnection(); sqlConn.ConnectionString = Data Source=.SQLEXPRESS; + AttachDbFileName=|DataDirectory|Book.mdf; + Integrated Security=True;User Instance=True; SqlCommand sqlCmd = new SqlCommand(); sqlCmd.Connection = sqlConn; sqlCmd.CommandText = select * frombook where bookid=bookid;/SQl语句 sqlCmd.Parameters.AddWithValue(bookid, Request.QueryStringbookid);/赋值 HiddenField1.Value=Request.QueryStringbookid; try sqlConn.Open();/打开数据库连接 SqlDataReader sqlDr = sqlCmd.ExecuteReader();/执行查询功能的SQL语句，使用连线模式，不支持分页 if(sqlDr.Read() Label1.Text=sqlDrbookname.ToString(); Label2.Text = sqlDrauthor.ToString(); Label3.Text = sqlDrpublisher.ToString(); Label4.Text = sqlDrpubdate.ToString(); Label5.Text = sqlDrprice.ToString(); Image3.ImageUrl = sqlDrimage.ToString(); catch(SqlException ex) Response.Write(ex.Message); finally sqlConn.Close();/关闭数据库连接，释放资源 protected void Button1_Click(object sender, EventArgs e) if (Sessionusername !=null) SqlConnection sqlConn = new SqlConnection(); sqlConn.ConnectionString = Data Source=.SQLEXPRESS; + AttachDbFileName=|DataDirectory|Book.mdf; + Integrated Security=True;User Instance=True; SqlCommand sqlCmd = new SqlCommand(); sqlCmd.Connection = sqlConn; sqlCmd.CommandText = delete from cart where (username =username) and (bookid=bookid);/SQl语句 sqlCmd.Parameters.AddWithValue(username,Sessionusername); sqlCmd.Parameters.AddWithValue(bookid, HiddenField1.Value);/赋值 try sqlConn.Open();/打开数据库连接 sqlCmd.ExecuteNonQuery(); Response.Redirect(cart.aspx); catch (SqlException ex) Response.Write(ex.Message); finally sqlConn.Close();/关闭数据库连接，释放资源 详细页 protected void Page_Load(object sender, EventArgs e) if (IsPostBack = false) SqlConnection sqlConn = new SqlConnection(); sqlConn.ConnectionString = Data Source=.SQLEXPRESS; + AttachDbFileName=|DataDirectory|Book.mdf; + Integrated Security=True;User Instance=True; SqlCommand sqlCmd = new SqlCommand(); sqlCmd.Connection = sqlConn; sqlCmd.CommandText = select * frombook where bookid=bookid;/SQl语句 sqlCmd.Parameters.AddWithValue(bookid, Request.QueryStringbookid);/赋值 HiddenField1.Value=Request.QueryStringbookid; try sqlConn.Open();/打开数据库连接 SqlDataReader sqlDr = sqlCmd.ExecuteReader(); if(sqlDr.Read() Label1.Text=sqlDrbookname.ToString(); Label2.Text = sqlDrauthor.ToString(); Label3.Text = sqlDrpublisher.ToString(); Label4.Text = sqlDrpubdate.ToString(); Label5.Text = sqlDrprice.ToString(); Label6.Text = sqlDrsummary.ToString(); Image3.ImageUrl = sqlDrimage.ToString(); catch(SqlException ex) Response.Write(ex.Message); finally sqlConn.Close();/关闭数据库连接，释放资源 protected void Button1_Click(object sender, EventArgs e) if (Sessionusername !=null) SqlConnection sqlConn = new SqlConnection(); sqlConn.ConnectionString = Data Source=.SQLEXPRESS; + AttachDbFileName=|DataDirectory|Book.mdf; + Integrated Security=True;User Instance=True; SqlCommand sqlCmd = new SqlCommand(); sqlCmd.Connection = sqlConn; sqlCmd.CommandText = insert into cart (bookid,username,quantity) values (bookid,username,quantity);/SQl语句 sqlCmd.Parameters.AddWithValue(bookid,HiddenField1.Value);/赋值 sqlCmd.Parameters.AddWithValue(username,Sessionusername); sqlCmd.Parameters.AddWithValue(quantity,1); try sqlConn.Open();/打开数据库连接 sqlCmd.ExecuteNonQuery(); Response.Redirect(cart.aspx); catch (SqlExcepti