Internet_应用开发【实验报告】.doc

Internet 应用开发实验报告学 号：XXX姓 名：XXX指导老师：XXX目录一、报告题目- 2 -二、实验目标- 2 -三、前期准备- 2 -a)、ASP.NET网站创建- 2 -b)、数据库设计- 3 -c)、Web界面设计- 4 -四、三步曲- 6 -a)、第一步ADO.NET之基本数据操作- 6 -b)、第二步数据操作封装- 9 -c)、第三步三层架构设计及实现- 13 -一、报告题目ASP.NET数据操作三步曲新闻发布系统的设计开发二、实验目标1、 创建ASP.NET网站开发环境；2、 设计一个具有基本样式的Web界面；3、 创建一个简单的数据库，其中包含至少一张5个字段以上的表；4、 分别采用三种数据库操作方式，进行对网站进行开发；5、 用户体验的设计实现；三、 前期准备a) 、ASP.NET网站创建打开visual studio 2010 ，新建项目中选择其他项目，选择visual studio 2010 空白解决方案，在方案下新建ASP.NET Web应用程序，如下：b)、数据库设计1、新闻信息表的设计：2、用户信息表的设计：3、数据库代码实现：use mastergo/*-建库-*/if exists(select * from sysdatabases where name=testDB) drop database testDBgo-创建数据库testDBcreate database testDB/*-建表-*/use testDBgo-判断表是否存在if exists(select * from sysobjects where name=UserInfo) drop table UserInfogo-用户信息表create table userInfo ( userID int identity(1,1) primary key, Name varchar(20)not null, Age int not null, Sex char(1),-0 男，女 Email varchar(20), Pwd varchar(10),)go-判断表是否存在if exists(select * from sysobjects where name=news) drop table newsgo-新闻表create table news( nID int identity(1,1)primary key, nTitle varchar(50)not null, nContent text not null, nTime datetime,)/*存储过程*/-用户登陆create proc proc_loginname char(19),pwd char(6)asif not exists(select * from UserInfo where Name=name)beginraiserror(用户不存在,16,1)returnendif not exists(select * from UserInfo where Name=name and Pwd = pwd)beginraiserror(密码错误,16,1)returnendc)、Web界面设计1、登陆界面设计：2、主界面设计：3、点击编辑按钮，进行新闻编辑：4、 输入新闻内容，点击添加新闻：5、点击删除按钮，删除新闻内容：四、三步曲a)、第一步ADO.NET之基本数据操作private static string strConn = Data Source=.;Initial Catalog=testDB;Integrated Security=true; protected void Page_Load(object sender, EventArgs e) public static SqlConnection GetConn() return new SqlConnection(strConn); protected void btnLogin_Click(object sender, EventArgs e) string name1 = txtName.Text.Trim(); string pwd1 = txtPwd.Text.Trim(); string sql = select * from UserInfo where Name= + name1 + and Pwd= + pwd1 + ; bool flag; SqlConnection conn = GetConn(); conn.Open(); SqlCommand comm = new SqlCommand(sql, conn); SqlDataReader dr = comm.ExecuteReader(); flag = dr.Read(); if (flag) SessionuserName = name1; Response.Redirect(WebSite/Default.aspx); else ClientScript.RegisterClientScriptBlock(this.GetType(), msfg, alert(登陆失败！);); private static string strConn = Data Source=.;Initial Catalog=testDB;Integrated Security=true; public static SqlConnection GetConn() return new SqlConnection(strConn); protected void Page_Load(object sender, EventArgs e) if (!IsPostBack) BindDataNews(); protected void BindDataNews() string sql = select * from news; SqlConnection conn = GetConn(); conn.Open(); SqlDataAdapter adapter = new SqlDataAdapter(sql, conn); DataSet ds = new DataSet(); adapter.Fill(ds); conn.Close(); DataTable dt = new DataTable(); dt.Columns.Add(nID, typeof(int); dt.Columns.Add(nTitle, typeof(string); dt.Columns.Add(nContent, typeof(string); dt.Columns.Add(nTime, typeof(DateTime); foreach (DataRow Rtest in ds.Tables0.Rows) DataRow dr = dt.NewRow(); drnID = RtestnID.ToString(); drnTitle = RtestnTitle.ToString().Substring(0, 3) + .; drnContent = RtestnContent.ToString() + ; drnTime = RtestnTime.ToString(); dt.Rows.Add(dr); GridView1.DataSource = dt; GridView1.DataBind(); protected void btnEdit_Click(object sender, EventArgs e) Button edit = sender as Button; SessionnID = int.Parse(edit.CommandArgument.ToString(); string sql = string.Format(select nTitle,nContent,nTime from news where nID=0, SessionnID.ToString(); SqlConnection conn = GetConn(); conn.Open(); SqlDataAdapter adapter = new SqlDataAdapter(sql, conn); DataSet ds = new DataSet(); adapter.Fill(ds); conn.Close(); DataTable dt = ds.Tables0; EditTitle.Text = dt.Rows0nTitle.ToString(); EditContent.Text = dt.Rows0nContent.ToString(); protected void btnDel_Click(object sender, EventArgs e) Button delete = sender as Button; int id = int.Parse(delete.CommandArgument.ToString(); string sql = string.Format(delete from news where nID=0, id); int j; SqlConnection conn = GetConn(); conn.Open(); SqlCommand comm = new SqlCommand(sql, conn); j = comm.ExecuteNonQuery(); conn.Close(); if (j 0) ClientScript.RegisterClientScriptBlock(this.GetType(), msfg, alert(删除成功！);); BindDataNews(); else ClientScript.RegisterClientScriptBlock(this.GetType(), msfg, alert(删除失败！);); protected void btnAdd_Click(object sender, EventArgs e) string title = txtTitle.Text.Trim(); string content = txtContent.Text.Trim(); string sql = string.Format(insert into news values(0,1,2), title, content, DateTime.Now); int j; SqlConnection conn = GetConn(); conn.Open(); SqlCommand comm = new SqlCommand(sql, conn); j = comm.ExecuteNonQuery(); conn.Close(); if (j 0) ClientScript.RegisterClientScriptBlock(this.GetType(), msfg, alert(添加成功！);); BindDataNews(); b)、第二步数据操作封装private static string strConn = Data Source=.;Initial Catalog=testDB;Integrated Security=true; public DBHelper() public static SqlConnection GetConn() return new SqlConnection(strConn); public static bool IfExist(string sql) bool flag; SqlConnection conn = GetConn(); conn.Open(); SqlCommand comm = new SqlCommand(sql, conn); SqlDataReader dr = comm.ExecuteReader(); flag = dr.Read(); return flag; public static int ExecuteSql(string sql) int i; SqlConnection conn = GetConn(); conn.Open(); SqlCommand comm = new SqlCommand(sql, conn); i = comm.ExecuteNonQuery(); conn.Close(); return i; public static DataSet GetDataSet(string querySql) SqlConnection conn = GetConn(); conn.Open(); SqlDataAdapter adapter = new SqlDataAdapter(querySql, conn); DataSet ds = new DataSet(); adapter.Fill(ds); conn.Close(); return ds;protected void btnLogin_Click(object sender, EventArgs e) string name1 = txtName.Text.Trim(); string pwd1 = txtPwd.Text.Trim(); string sql = select * from UserInfo where uName= + name1 + and Pwd= + pwd1 + ; if (DBHelper.IfExist(sql) SessionuserName = name1; Response.Redirect(WebSite/Default.aspx); else ClientScript.RegisterClientScriptBlock(this.GetType(), msfg, alert(登陆失败！);); protected void Page_Load(object sender, EventArgs e) if (!IsPostBack) BindDataNews(); protected void BindDataNews() string sql = select * from news; DataSet ds = DBHelper.GetDataSet(sql); DataTable dt = new DataTable(); dt.Columns.Add(nID, typeof(int); dt.Columns.Add(nTitle, typeof(string); dt.Columns.Add(nContent, typeof(string); dt.Columns.Add(nTime, typeof(DateTime); foreach (DataRow Rtest in ds.Tables0.Rows) DataRow dr = dt.NewRow(); drnID = RtestnID.ToString(); drnTitle = RtestnTitle.ToString().Substring(0, 3) + .; if (RtestnContent.ToString().Length 25) drnContent = RtestnContent.ToString().Substring(0, 24) + .; else drnContent = RtestnContent.ToString() + ; drnTime = RtestnTime.ToString(); dt.Rows.Add(dr); GridView1.DataSource = dt; GridView1.DataBind(); protected void btnEdit_Click(object sender, EventArgs e) Button edit = sender as Button; string sql = string.Format(select nTitle,nContent,nTime from news where nID=0, SessionnID.ToString(); DataTable dt = DBHelper.GetDataSet(sql).Tables0; EditTitle.Text = dt.Rows0nTitle.ToString(); EditContent.Text = dt.Rows0nContent.ToString(); protected void btnDel_Click(object sender, EventArgs e) Button delete = sender as Button; int id = int.Parse(delete.CommandArgument.ToString(); string sql = string.Format(delete from news where nID=0, id); if (DBHelper.ExecuteSql(sql) 0) ClientScript.RegisterClientScriptBlock(this.GetType(), msfg, alert(删除成功！);); BindDataNews(); else ClientScript.RegisterClientScriptBlock(this.GetType(), msfg, alert(删除失败！);); protected void btnAdd_Click(object sender, EventArgs e) string title = txtTitle.Text.Trim(); string content = txtContent.Text.Trim(); string sql = string.Format(insert into news values(0,1,2), title, content, DateTime.Now); if (DBHelper.ExecuteSql(sql) 0) ClientScript.RegisterClientScriptBlock(this.GetType(), msfg, alert(添加成功！);); BindDataNews(); c)、第三步三层架构设计及实现如图建立三层架构：DAL层，对数据库的操作：public class News public DataTable GetAllNews() string sql = select * from news; return SQLHelper.Helper.Exec(sql); public DataTable Query(string sql) return SQLHelper.Helper.Exec(sql); public bool Delete(int id) string sql = string.Format(delete from news where nID=0, id); return SQLHelper.Helper.ExecNonQuery(sql)0?true:false; public bool Insert(string sql) return SQLHelper.Helper.ExecNonQuery(sql)0?true:false; SQLHelper数据库帮助类： / / 执行SQL语句，返回影响的记录数 / / SQL语句 / 影响的记录数 public static int ExecuteSql(string SQLString) using (SqlConnection connection = new SqlConnection(connectionString) using (SqlCommand cmd = new SqlCommand(SQLString, connection) try connection.Open(); int rows = cmd.ExecuteNonQuery(); return rows; catch (System.Data.SqlClient.SqlException e) connection.Close(); throw e; finally cmd.Dispose(); connection.Close(); / / 执行查询语句，返回SqlDataReader ( 注意：调用该方法后，一定要对SqlDataReader进行Close ) / / 查询语句 / SqlDataReader public static SqlDataReader ExecuteReader(string strSQL) SqlConnection connection = new SqlConnection(connectionString); SqlCommand cmd = new SqlCommand(strSQL, connection); try connection.Open(); SqlDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection); return myReader; catch (System.Data.SqlClient.SqlException e) throw e; / / 执行查询语句，返回DataSet / / 查询语句 / DataSet public static DataSet Query(string SQLString) using (SqlConnection connection = new SqlConnection(connectionString) DataSet ds = new DataSet(); try connection.Open(); SqlDataAdapter command = new SqlDataAdapter(SQLString, connection); command.Fill(ds, ds); catch (System.Data.SqlClient.SqlException ex) throw new Exception(ex.Message); finally connection.Close(); return ds; / / 执行SQL语句，返回影响的记录数 / / SQL语句 / 影响的记录数 public static int ExecuteSql(string SQLString, params SqlParameter cmdParms) using (SqlConnection connection = new SqlConnection(connectionString) using (SqlCommand cmd = new SqlCommand() try PrepareCommand(cmd, connection, null, SQLString, cmdParms); int rows = cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); return rows; catch (System.Data.SqlClient.SqlException e) throw e; finally cmd.Dispose(); connection.Close(); / / 执行查询语句，返回DataSet / / 查询语句 / DataSet public static DataSet Query(string SQLString, params SqlParameter cmdParms) using (SqlConnection connection = new SqlConnection(connectionString) SqlCommand cmd = new SqlCommand(); PrepareCommand(cmd, connection, null, SQLStr