失眠网 > vs实现asp.net对SQL Server完整的增删改查

vs实现asp.net对SQL Server完整的增删改查

时间：2018-09-29 01:34:17

vs实现对SQL Server完整的增删改查

项目文件目录

img文件夹存放项目需要的图片，本人的数据表是student，当然，完成以下操作还需要成功连接数据库。

数据库连接详解

index.aspx

<style>body {background:url("./img/01.jpg");background-size:100% 100%;}#iframe {width:100%;height:480px;margin-top:30px;}#div1 {width:100%;height:110px;text-align:center;}#h_1 {font-size:40px;color:aqua;text-shadow: 0 0 10px blue,0 0 20px blue,0 0 30px blue,0 0 40px blue;}button {width:100px;height:50px;font-size:30px;border-radius:5px;background-color:aqua;}</style>

<body><div id="div1"><h1 id="h_1">学生信息管理系统</h1><button onclick="btnClick1()">查询</button><button onclick="btnClick2()">添加</button><button onclick="btnClick3()">修改</button><button onclick="btnClick4()">删除</button></div><iframe id="iframe" src="query.aspx"></iframe><script>function btnClick1() {var str = "query.aspx";document.getElementById("iframe").src = str;};function btnClick2() {var str = "add.aspx";document.getElementById("iframe").src = str;};function btnClick3() {var str = "update.aspx";document.getElementById("iframe").src = str;};function btnClick4() {var str = "delete.aspx";document.getElementById("iframe").src = str;}</script></body>

查询 query.aspx

<style>.div1 {width:100%;margin:auto;}.div2 {width:400px;margin:auto;margin-bottom:30px;}input {font-size:20px;height:40px;border-radius:5px;font-weight:900;}</style>

<body><form id="form1" runat="server"><div class="div1"><div class="div2"><input type="text" name="number" id="number" placeholder="请输入查询学生的学号" runat="server"/> <asp:Button ID="Button1" runat="server" Text="查询" height="45px" Width="100px" OnClick="Button1_Click" /></div><asp:GridView ID="GridView1" runat="server" text-align="center" style="margin: 0px auto;Font-Size:20px; border:3px;" ></asp:GridView></div></form></body>

查询 query.aspx.cs

添加 add.aspx

<style>.div1 {width:100%;margin:auto;}.div2 {width:1200px;margin:auto;margin-bottom:30px;}input {font-size:15px;height:30px;width:105px;border-radius:5px;font-weight:600;}</style>

添加 add.aspx.cs

protected void Page_Load(object sender, EventArgs e){string strcon = ConfigurationManager.ConnectionStrings["Test"].ConnectionString;//从web.config文件中读取连接字符串SqlConnection con = new SqlConnection(strcon);//定义连接对象SqlCommand cmd = new SqlCommand();//创建命令对象cmd.Connection = con;//设置命令对象的数据库连接属性mandText = "select * from student";//把SQL语句赋给命令对象con.Open();//打开数据库连接//Response.Write("连接数据库查询成功");SqlDataReader sdr = cmd.ExecuteReader();//执行SQL命令，并获取查询结果GridView1.DataSource = sdr;//将查询结果连接到GridView1中GridView1.DataBind();//将结果与GridView1绑定}protected void Button1_Click(object sender, EventArgs e){//获取input标签输入的值var number = Request.Form["number"].ToString();var StdFirstName = Request.Form["firstname"].ToString();var StdLastName = Request.Form["lastname"].ToString();var StdCity = Request.Form["stdcity"].ToString();var StdState = Request.Form["stdstate"].ToString();var StdMajor = Request.Form["stdmajor"].ToString();var StdClass = Request.Form["stdclass"].ToString();var StdGPA = Request.Form["stdgpa"].ToString();var StdZip = Request.Form["stdzip"].ToString();//Console.log(number);string strcon = ConfigurationManager.ConnectionStrings["Test"].ConnectionString;//从web.config文件中读取连接字符串SqlConnection con = new SqlConnection(strcon);//定义连接对象SqlCommand cmd = new SqlCommand();//创建命令对象cmd.Connection = con;//设置命令对象的数据库连接属性mandText = "insert into student(StdSSN,StdFirstName,StdLastName,StdCity,StdState,StdMajor,StdClass,StdGPA,StdZip) values('" + number+"','" + StdFirstName + "','" + StdLastName + "','"+ StdCity + "','" + StdState + "','" + StdMajor + "','" + StdClass + "','" + StdGPA + "','" + StdZip + "')";//把SQL语句赋给命令对象try{con.Open();//打开数据库连接//Response.Write("连接数据库查询成功");SqlDataReader sdr = cmd.ExecuteReader();//执行SQL命令，并获取查询结果cmd.Connection = con;//设置命令对象的数据库连接属性mandText = "select * from student";//把SQL语句赋给命令对象con.Close();//关闭数据库连接con.Open();//打开数据库连接//Response.Write("连接数据库查询成功");sdr = cmd.ExecuteReader();//执行SQL命令，并获取查询结果GridView1.DataSource = sdr;//将查询结果连接到GridView1中GridView1.DataBind();//将结果与GridView1绑定//Button1.Attributes.Add("Button1_Click", "{javascript:form1.reset();return false;}");}catch (Exception ex){Response.Write("连接失败，原因是" + ex.Message);}finally{if (con.State == System.Data.ConnectionState.Open) //判断数据库连接是否关闭con.Close();//关闭数据库连接}}

修改update.aspx

<style>.div1 {width:100%;margin:auto;}.div2 {width:1200px;margin:auto;margin-bottom:30px;}input {font-size:15px;height:30px;width:105px;border-radius:5px;font-weight:600;}</style>

修改update.aspx.cs

protected void Page_Load(object sender, EventArgs e){string strcon = ConfigurationManager.ConnectionStrings["Test"].ConnectionString;//从web.config文件中读取连接字符串SqlConnection con = new SqlConnection(strcon);//定义连接对象SqlCommand cmd = new SqlCommand();//创建命令对象cmd.Connection = con;//设置命令对象的数据库连接属性mandText = "select * from student";//把SQL语句赋给命令对象con.Open();//打开数据库连接//Response.Write("连接数据库查询成功");SqlDataReader sdr = cmd.ExecuteReader();//执行SQL命令，并获取查询结果GridView1.DataSource = sdr;//将查询结果连接到GridView1中GridView1.DataBind();//将结果与GridView1绑定}protected void Button1_Click(object sender, EventArgs e){//var name = ConfigXmlDocument.getElementById("number").value;var number = Request.Form["number"].ToString();var StdFirstName = Request.Form["firstname"].ToString();var StdLastName = Request.Form["lastname"].ToString();var StdCity = Request.Form["stdcity"].ToString();var StdState= Request.Form["stdstate"].ToString();var StdMajor= Request.Form["stdmajor"].ToString();var StdClass= Request.Form["stdclass"].ToString();var StdGPA= Request.Form["stdgpa"].ToString();var StdZip = Request.Form["stdzip"].ToString();//Console.log(number);string strcon = ConfigurationManager.ConnectionStrings["Test"].ConnectionString;//从web.config文件中读取连接字符串SqlConnection con = new SqlConnection(strcon);//定义连接对象SqlCommand cmd = new SqlCommand();//创建命令对象cmd.Connection = con;//设置命令对象的数据库连接属性mandText = "update student set StdFirstName='" + StdFirstName + "',StdLastName='" + StdLastName+ "',StdCity='" + StdCity + "',StdState='" +StdState + "',StdMajor='" + StdMajor + "',StdClass='" +StdClass + "',StdGPA='" + StdGPA + "',StdZip='" +StdZip + "' where StdSSN= '" + number+"'";//把SQL语句赋给命令对象try{con.Open();//打开数据库连接//Response.Write("连接数据库查询成功");SqlDataReader sdr = cmd.ExecuteReader();//执行SQL命令，并获取查询结果cmd.Connection = con;//设置命令对象的数据库连接属性mandText = "select * from student";//把SQL语句赋给命令对象con.Close();//关闭数据库连接con.Open();//打开数据库连接//Response.Write("连接数据库查询成功");sdr = cmd.ExecuteReader();//执行SQL命令，并获取查询结果GridView1.DataSource = sdr;//将查询结果连接到GridView1中GridView1.DataBind();//将结果与GridView1绑定//Button1.Attributes.Add("Button1_Click", "{javascript:form1.reset();return false;}");}catch (Exception ex){Response.Write("连接失败，原因是" + ex.Message);}finally{if (con.State == System.Data.ConnectionState.Open) //判断数据库连接是否关闭con.Close();//关闭数据库连接}}

删除delete.aspx

<style>.div1 {width:100%;margin:auto;}.div2 {width:400px;margin:auto;margin-bottom:30px;}input {font-size:20px;height:40px;border-radius:5px;font-weight:900;}</style>

<body><form id="form1" runat="server"><div class="div1"><div class="div2"><input type="text" name="number" id="number" placeholder="请输入删除学生的学号" runat="server"/> <asp:Button ID="Button1" runat="server" Text="删除" height="45px" Width="100px" OnClick="Button1_Click"/></div><asp:GridView ID="GridView1" runat="server" text-align="center" style="margin: 0px auto;Font-Size:20px;border:3px;"></asp:GridView></div></form></body>

删除delete.aspx.cs

protected void Page_Load(object sender, EventArgs e){string strcon = ConfigurationManager.ConnectionStrings["Test"].ConnectionString;//从web.config文件中读取连接字符串SqlConnection con = new SqlConnection(strcon);//定义连接对象SqlCommand cmd = new SqlCommand();//创建命令对象cmd.Connection = con;//设置命令对象的数据库连接属性mandText = "select * from student";//把SQL语句赋给命令对象con.Open();//打开数据库连接//Response.Write("连接数据库查询成功");SqlDataReader sdr = cmd.ExecuteReader();//执行SQL命令，并获取查询结果GridView1.DataSource = sdr;//将查询结果连接到GridView1中GridView1.DataBind();//将结果与GridView1绑定}protected void Button1_Click(object sender, EventArgs e){//var name = ConfigXmlDocument.getElementById("number").value;var number = Request.Form["number"].ToString();//Console.log(number);string strcon = ConfigurationManager.ConnectionStrings["Test"].ConnectionString;//从web.config文件中读取连接字符串SqlConnection con = new SqlConnection(strcon);//定义连接对象SqlCommand cmd = new SqlCommand();//创建命令对象cmd.Connection = con;//设置命令对象的数据库连接属性mandText = "delete from student where StdSSN= '" + number+"'";//把SQL语句赋给命令对象try{con.Open();//打开数据库连接//Response.Write("连接数据库查询成功");SqlDataReader sdr = cmd.ExecuteReader();//执行SQL命令，并获取查询结果cmd.Connection = con;//设置命令对象的数据库连接属性mandText = "select * from student";//把SQL语句赋给命令对象con.Close();//关闭数据库连接con.Open();//打开数据库连接//Response.Write("连接数据库查询成功");sdr = cmd.ExecuteReader();//执行SQL命令，并获取查询结果GridView1.DataSource = sdr;//将查询结果连接到GridView1中GridView1.DataBind();//将结果与GridView1绑定//Button1.Attributes.Add("Button1_Click", "{javascript:form1.reset();return false;}");}catch (Exception ex){Response.Write("连接失败，原因是" + ex.Message);}finally{if (con.State == System.Data.ConnectionState.Open) //判断数据库连接是否关闭con.Close();//关闭数据库连接}}