1、表的T-Sql创建代码 create table student ( studentid int identity not null, name varchar(50) not null,
sex varchar(50) default '女', height float check(height between 100 and 300), idcardnumber varchar(50) unique not null, addtime datetime default GetDate() )
2、存储过程的T-Sql创建代码（至少四个，分别体现增、删、改、查） （1）创建增加语句存储过程
CREATE PROCEDURE [dbo].[pro_insert] @studentid int, @name varchar(50), @sex varchar(50), @height float,
@idcardnumber varchar(50) AS
set IDENTITY_INSERT student on
insert into student (studentid,name,sex,idcardnumber,height) values(@studentid,@name,@sex,@idcardnumber,@height) GO
（2）创建删除语句存储过程
CREATE PROCEDURE [dbo].[pro_delete] @studentid int as
Delete student
where studentid=@studentid GO
（3）创建更新语句存储过程
CREATE PROCEDURE [dbo].[pro_update] @studentid int, @name varchar(50), @sex varchar(50), @height float,
实验二·涉及数据库操作的网络程序设计
@idcardnumber varchar(50)
as update student set name=@name ,sex =@sex ,height=@height,idcardnumber=@idcardnumber where studentid=@studentid GO
（4）创建查询单条信息语句存储过程 CREATE PROCEDURE [dbo].[pro_select] @studentid int as
select * from student
where studentid=@studentid GO
（5）创建获取所有信息语句存储过程 CREATE PROCEDURE [dbo].[pro_get] as
select * from student GO
3、Web主页面截图 ○1查询学号为23 的学生的信息
○2增加编号为20的学生的信息
实验二·涉及数据库操作的网络程序设计
○3删除学号为34的学生的信息
○4更新学号为33的学生的信息
4、页面元素交互关键代码
namespace lala
{
public partial class Wela : System.Web.UI.Page {
string nectionString;
protected void Page_Load(object sender, EventArgs e) { }
protected void Button1_Click(object sender, EventArgs e) {
SqlConnection con = new SqlConnection(conString); SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.StoredProcedure; cmd.Connection = con; cmd.CommandText = \"pro_get\";
SqlDataAdapter sda = new SqlDataAdapter(); sda.SelectCommand = cmd; DataSet ds = new DataSet();
conString
=
System.Configuration.ConfigurationManager.ConnectionStrings[\"lalaConnectionString\"].Con
实验二·涉及数据库操作的网络程序设计
con.Open();
sda.Fill(ds, \"StudentInfo\");
this.GridView1.DataSource = ds.Tables[0]; this.GridView1.DataBind();//数据绑定 con.Close(); }
protected void Button2_Click(object sender, EventArgs e) {
SqlConnection con = new SqlConnection(conString); SqlCommand cmd = new SqlCommand(); cmd.Connection = con;
cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = \"pro_select\";
SqlParameter para = new SqlParameter(\"@studentid\", this.txt_studentid.Text); cmd.Parameters.Add(para);
SqlDataAdapter sda = new SqlDataAdapter(); sda.SelectCommand = cmd; DataSet ds = new DataSet(); con.Open();
sda.Fill(ds, \"StudentInfo\");
this.GridView2.DataSource = ds.Tables[0]; this.GridView2.DataBind();//数据绑定 con.Close();
this.Button1_Click(null, null);//单击“获取”按钮一次 }
protected void Button3_Click(object sender, EventArgs e) {
SqlConnection con = new SqlConnection(conString); SqlCommand cmd = new SqlCommand(); cmd.Connection = con;
cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = \"pro_insert\"; SqlParameter[] para = { new this.txt_studentid.Text),
new SqlParameter(\"@name\", this.txt_name.Text), new SqlParameter(\"@sex\", this.txt_sex.Text), new SqlParameter(\"@height\", this.txt_height.Text), new this.txt_idcardnumber.Text), }; cmd.Parameters.Add(para[0]); cmd.Parameters.Add(para[1]); cmd.Parameters.Add(para[2]);
SqlParameter(\"@idcardnumber\",
SqlParameter(\"@studentid\",
实验二·涉及数据库操作的网络程序设计
cmd.Parameters.Add(para[3]); cmd.Parameters.Add(para[4]); con.Open();
int i = cmd.ExecuteNonQuery(); con.Close(); if (i >= 1) {
this.txt_shuoming.Text = \"成功插入编号为\" + this.txt_studentid.Text + \"学生的信息\";
this.Button1_Click(null, null);//单击“获取”按钮一次 } else
this.txt_shuoming.Text = \"插入失败\"; this.txt_studentid.Text = \"\"; this.txt_name.Text = \"\"; this.txt_sex.Text = \"\"; this.txt_height.Text = \"\"; this.txt_idcardnumber.Text = \"\"; }
protected void Button4_Click(object sender, EventArgs e) {
SqlConnection con = new SqlConnection(conString); SqlCommand cmd = new SqlCommand(); cmd.Connection = con;
cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = \"pro_delete\";
SqlParameter para = new SqlParameter(\"@studentid\", this.txt_studentid.Text); cmd.Parameters.Add(para); con.Open();
int i = cmd.ExecuteNonQuery(); con.Close(); if (i >= 1)
this.txt_shuoming.Text = \"成功删除编号为\" + this.txt_studentid.Text + \"学生的记录！\"; else
this.txt_shuoming.Text = \"删除编号为\" + this.txt_studentid.Text + \"学生的记录失败！\";
this.Button1_Click(null, null);//单击“获取”按钮一次 }
protected void Button5_Click(object sender, EventArgs e) {
if (this.txt_name.Text == \"\" || this.txt_sex.Text == \"\" || this.txt_height.Text == \"\" || this.txt_idcardnumber.Text == \"\") {
实验二·涉及数据库操作的网络程序设计
this.txt_shuoming.Text = \"***********\"; } else {
SqlConnection con = new SqlConnection(conString); SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.StoredProcedure;//存储过程 cmd.CommandText = \"pro_update\"; cmd.Connection = con; SqlParameter[] paras = { new this.txt_studentid.Text),
new SqlParameter(\"@name\",this.txt_name.Text),
new SqlParameter(\"@sex\",this.txt_sex.Text), new SqlParameter(\"@height\",this.txt_height.Text), new
SqlParameter(\"@idcardnumber\",this.txt_idcardnumber.Text) }; cmd.Parameters.Add(paras[0]); cmd.Parameters.Add(paras[1]); cmd.Parameters.Add(paras[2]); cmd.Parameters.Add(paras[3]); cmd.Parameters.Add(paras[4]); con.Open();
int i = cmd.ExecuteNonQuery(); con.Close(); if (i >= 1) {
this.txt_shuoming.Text = \"成功更新编号为\" + this.txt_studentid.Text + \"学生的信息\";
this.Button1_Click(null, null); } else
this.txt_shuoming.Text = \"\"; this.txt_name.Text = \"\"; this.txt_sex.Text = \"\"; this.txt_height.Text = \"\"; this.txt_idcardnumber.Text = \"\"; } } } }
SqlParameter(\"@studentid\",