数据库接口访问

实验报告课程名称Web程序设计实验名称实验四、ADO.NET数据访问接口日期2014.11.25学生学号B13053215姓名刘婷婷班级B130532实验目的：1.熟悉ADO.NET数据库访问技术。掌握Connection、Command对象的使用。掌握DataReader、DataAdapter对象操作数据库数据的方法。掌握VS2008中创建数据库的方法。实验条件：电脑一台、能上网查阅资料。实验内容与步骤：1.实验内容一(1)要求新建名字为“Accessdatabase_Exercise”的网站。在网站的App_Data文件夹中，建立数据库"MyDatabase_Exercise.mdf”。在该数据库中建立一张职工表，并且添加一些模拟的职工记录。其关系模式如下：Employees(ID,NAME,SEX,AGE,Dateofwork,FilenameofPhoto)在web.config配置文件中，修改"<connectionStrings/>"标记如下。<connectionStrings><addname="ConnectionString"connectionString="DataSource=LTT;InitialCatalog=MyDatabase_Exercise;IntegratedSecurity=True"providerName="System.Data.SqlClient"/></connectionStrings>添加一个网页，利用Command对象实现新职工的录入。添加一个网页，利用Command对象实现删除指定编号的职工记录。添加一个网页，利用Command对象实现修改指定编号的职工信息。添加一个网页，利用DataAdapter对象实现查询职工信息，并显小到网页的Label控件上。(2)源代码和实验结果1.添加一个Command_insert.aspx网页，利用Command对象实现新职工的录入。代码如下：usingSystem;usingSystem.Collections.Generic;usingSystem.Linq;usingSystem.Web;usingSystem.Web.UI;usingSystem.Web.UI.WebControls;usingSystem.Data.SqlClient;usingSystem.Data;usingSystem.Web.Configuration;publicpartialclassCommand_insert:System.Web.UI.Page(protectedvoidSubmitBtn_Click(objectsender,EventArgse)(stringsqlconnstr=WebConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;SqlConnectionsqlconn=newSqlConnection(sqlconnstr);SqlCommandsqlcommand=newSqlCommand();sqlcommand.Connection=sqlconn;sqlcommand.CommandText="insertintoEmployees(ID,NAME,SEX,AGE,Dateofwork,FilenameofPhoto)values(@ID,@NAME,@SEX,@AGE,@Dateofwork,@FilenameofPhoto)”;sqlcommand.Parameters.AddWithValue("@ID”,TextBox1.Text);sqlcommand.Parameters.AddWithValue("@NAME”,TextBox2.Text);sqlcommand.Parameters.AddWithValue("@SEX”,DropDownList1.Text);sqlcommand.Parameters.AddWithValue("@AGE”,TextBox4.Text);sqlcommand.Parameters.AddWithValue("@Dateofwork”,TextBox5.Text);sqlcommand.Parameters.AddWithValue("@FilenameofPhoto”,FileUpload1.FileName);try(sqlconn.Open();sqlcommand.ExecuteNonQuery();if(FileUploadl.HasFile==true)(FileUpload1.SaveAs(Server.MapPath(("~/image/")+FileUploadl.FileName));}Label7.Text="成功添加记录"；}catch(Exceptionex)(Label7.Text="错误原因”+ex.Message;}finally(sqlcommand=null;sqlconn.Close();sqlconn=null;}}}实验结果如下：:寺htLp://localhost:5&8[]iPTlocalhost乂1牛(丹鼎本〔日登看W)岐藏衰|囚iTH(T)ID:1NAME:liuttSEX:女wAGE21DataofiAork:11C:\1.ipa提莪成功舔加记录

usingusingusingusingusingusingusingusingusingusingusingusingusingusingusingusingusingusingSystem;System.Collections.Generic;System.Linq;System.Web;System.Web.UI;System.Web.UI.WebControls;System.Data.SqlClient;System.Data;System.Web.Configuration;publicpartialclassCommand_delete:System.Web.UI.Page(protectedvoidDelBtn_Click(objectsender,EventArgse)(intintDeleteCount;stringsqlconnstr=WebConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;SqlConnectionsqlconn=newSqlConnection(sqlconnstr);SqlCommandsqlcommand=newSqlCommand();sqlcommand.Connection=sqlconn;sqlcommand.CommandText="deletefromEmployeeswhereID=@ID”;sqlcommand.Parameters.AddWithValue("@ID”,TextBox1.Text);try(sqlconn.Open();intDeleteCount=sqlcommand.ExecuteNonQuery();if(intDeleteCount>0)Label2.Text="成功删除记录！";elseLabel2.Text="该记录不存在！"；}catch(Exceptionex)(Label2.Text="错误原因："+ex.Message;}finally(sqlcommand=null;sqlconn.Close();sqlconn=null;}}}实验结果如下：添加一个Command_Update.aspx网页，利用Command对象实现修改指定编号的职工信息。代码如下：usingSystem;usingSystem.Collections.Generic;usingSystem.Linq;usingSystem.Web;usingSystem.Web.UI;usingSystem.Web.UI.WebControls;usingSystem.Data;usingSystem.Data.SqlClient;usingSystem.Web.Configuration;publicpartialclassCommand_Update:System.Web.UI.Page{protectedvoidSubmitBtn_Click(objectsender,EventArgse)(Stringsqlconnstr=WebConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;SqlConnectionsqlconn=newSqlConnection(sqlconnstr);SqlCommandsqlcommand=newSqlCommand();sqlcommand.Connection=sqlconn;sqlcommand.CommandText="updateEmployeessetNAME=@NAME,SEX=@SEX,Dateofwork=@Dateofwork,FilenameofPhoto=@FilenameofPhotowhereID=@ID”;sqlcommand.Parameters.AddWithValue("@ID”,TextBox1.Text);sqlcommand.Parameters.AddWithValue("@NAME”,TextBox2.Text);sqlcommand.Parameters.AddWithValue("@SEX”,DropDownList1.Text);sqlcommand.Parameters.AddWithValue("@AGE”,TextBox3.Text);sqlcommand.Parameters.AddWithValue("@Dateofwork”,TextBox4.Text);sqlcommand.Parameters.AddWithValue("@FilenameofPhoto”,FileUpload1.FileName);try(sqlconn.Open();sqlcommand.ExecuteNonQuery();Label7.Text="成功修改记录"；}catch(Exceptionex)(Labell.Text="错误原因："+ex.Message;}finally{sqlcommand=null;sqlconn.Close();sqlconn=null;}}}实验结果如下:添加一个DataAdapter_update.aspx网页，利用DataAdapter对象实现查询职工信息，并显示到网页的Label控件上。代码如下：usingSystem;usingSystem.Collections.Generic;usingSystem.Linq;usingSystem.Web;usingSystem.Web.UI;usingSystem.Web.UI.WebControls;usingSystem.Data;usingSystem.Data.SqlClient;usingSystem.Web.ConfigurationpublicpartialclassDataAdapter_update:System.Web.UI.Page(protectedvoidPage_Load(objectsender,EventArgse)(stringsqlconnstr=WebConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;SqlConnectionsqlconn=newSqlConnection(sqlconnstr);DataSetds=newDataSet();DataTabledtable;DataRowCollectioncoldrow;DataRowdrow;sqlconn.Open();SqlDataAdaptersqld=newSqlDataAdapter("select*fromEmployees”,sqlconn);sqld.Fill(ds,"Employees");dtable=ds.Tables["Employees"];coldrow=dtable.Rows;for(inti=0;i<coldrow.Count;i++)(drow=coldrow[i];Label1.Text+="ID"+drow[0];Label1.Text+="NAME"+drow[1];Label1.Text+="SEX"+drow[2];Label1.Text+="AGE"