vs2019实现asp.net对SQL Server完整的增删改查
vs2019实现asp.net对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
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;//设置命令对象的数据库连接属性
cmd.CommandText = "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 number = Request.Form["number"].ToString();
string strcon = ConfigurationManager.ConnectionStrings["Test"].ConnectionString;//从web.config文件中读取连接字符串
SqlConnection con = new SqlConnection(strcon);//定义连接对象
SqlCommand cmd = new SqlCommand();//创建命令对象
cmd.Connection = con;//设置命令对象的数据库连接属性
cmd.CommandText = "select * from student where StdSSN = '" + number + "'";//把SQL语句赋给命令对象
try
{
con.Open();//打开数据库连接
//Response.Write("连接数据库查询成功");
SqlDataReader sdr = cmd.ExecuteReader();//执行SQL命令，并获取查询结果
GridView1.DataSource = sdr;//将查询结果连接到GridView1中
GridView1.DataBind();//将结果与GridView1绑定
}
catch (Exception ex)
{
Response.Write("连接失败，原因是" + ex.Message);
}
finally
{
if (con.State == System.Data.ConnectionState.Open) //判断数据库连接是否关闭
con.Close();//关闭数据库连接
}
}
添加 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>
<body>
<form id="form1" runat="server">
<div class="div1">
<div class="div2">
<input type="text" name="number" id="number" placeholder="StdSSN" runat="server"/>
<input type="text" name="firstname" id="firstname" placeholder="Firstname" runat="server"/>
<input type="text" name="lastname" id="lastname" placeholder="Lastname" runat="server"/>
<input type="text" name="stdcity" id="stdcity" placeholder="stdcity" runat="server"/>
<input type="text" name="stdstate" id="stdstate" placeholder="stdstate" runat="server"/>
<input type="text" name="stdmajor" id="stdmajor" placeholder="stdmajor" runat="server"/>
<input type="text" name="stdclass" id="stdclass" placeholder="stdclass" runat="server"/>
<input type="text" name="stdgpa" id="stdgpa" placeholder="stdgpa" runat="server"/>
<input type="text" name="stdzip" id="stdzip" placeholder="stdzip" runat="server"/>
<asp:Button ID="Button1" runat="server" Text="添加" height="38px" Width="100px" font-size="20px" 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>
添加 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;//设置命令对象的数据库连接属性
cmd.CommandText = "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;//设置命令对象的数据库连接属性
cmd.CommandText = "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;//设置命令对象的数据库连接属性
cmd.CommandText = "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>
<body>
<form id="form1" runat="server">
<div class="div1">
<div class="div2">
<input type="text" name="number" id="number" placeholder="StdSSN" runat="server"/>
<input type="text" name="firstname" id="firstname" placeholder="Firstname" runat="server"/>
<input type="text" name="lastname" id="lastname" placeholder="Lastname" runat="server"/>
<input type="text" name="stdcity" id="stdcity" placeholder="stdcity" runat="server"/>
<input type="text" name="stdstate" id="stdstate" placeholder="stdstate" runat="server"/>
<input type="text" name="stdmajor" id="stdmajor" placeholder="stdmajor" runat="server"/>
<input type="text" name="stdclass" id="stdclass" placeholder="stdclass" runat="server"/>
<input type="text" name="stdgpa" id="stdgpa" placeholder="stdgpa" runat="server"/>
<input type="text" name="stdzip" id="stdzip" placeholder="stdzip" runat="server"/>
<asp:Button ID="Button1" runat="server" Text="修改" height="38px" Width="100px" font-size="20px" 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>
修改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;//设置命令对象的数据库连接属性
cmd.CommandText = "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;//设置命令对象的数据库连接属性
cmd.CommandText = "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;//设置命令对象的数据库连接属性
cmd.CommandText = "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;//设置命令对象的数据库连接属性
cmd.CommandText = "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;//设置命令对象的数据库连接属性
cmd.CommandText = "delete from student where StdSSN= '" + number+"'";//把SQL语句赋给命令对象
try
{
con.Open();//打开数据库连接
//Response.Write("连接数据库查询成功");
SqlDataReader sdr = cmd.ExecuteReader();//执行SQL命令，并获取查询结果
cmd.Connection = con;//设置命令对象的数据库连接属性
cmd.CommandText = "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();//关闭数据库连接
}
}
