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

关注

原创 2024-01-25 23:34:06 6.2k 阅读 AI 写同款· GEO 优化 ›

VS2019+SQL Server实战：若依ZrAdminNetCore框架从下载到部署全流程（附常见错误排查）本文详细介绍了在VS2019和SQL Server环境下部署若依ZrAdminNetCore框架的全流程，包括环境准备、源码获取、数据库配置、项目调试及常见错误排查。特别针对跨技术栈开发者提供实用解决方案，帮助快速实现权限管理系统的搭建与优化。阅读详情

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

关注公众号并回复：“asp数据库资源 ”即可获取源码

在这里插入图片描述

项目文件目录
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();//关闭数据库连接
            }
            
        }