ASP.net连接Mysql,并简单的增删改查

最新推荐文章于 2023-05-26 16:26:53 发布

白露-

最新推荐文章于 2023-05-26 16:26:53 发布

阅读量2.5k

点赞数 3

分类专栏： asp.net web学习笔记文章标签： mysql asp.net

本文链接：https://blog.csdn.net/m0_56227168/article/details/122181579

版权

asp.net web学习笔记专栏收录该内容

49 篇文章 14 订阅

订阅专栏

一.说明

这一篇简单的个人总结,方便自己的复制粘贴,软件是Visual Studio 2019,SQLyog

二.配置

一.在Web.config中的configuration标签下添加如下代码:

<connectionStrings>
	<add name="Conn_example" connectionString="Database='example';Data Source='127.0.0.1';User Id='root';Password='xxxxx';charset='utf8';pooling=true"/>
</connectionStrings>
<!--
数据库位置:Data Source='127.0.0.1'//此处为本地数据库
用户名:User Id=''
密码:Password=''
字符集:charset=''
-->

二.创建一个名为MysqlData.cs的类,并写入如下代码:

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Linq;
using System.Web;

namespace MyTest03
{
    public class MysqlData
    {
        public static string DataBase = ConfigurationManager.ConnectionStrings["Conn_example"].ToString();
        //将Web.config文件里的配置取出
    }
}

三.在官网下载MySql.Data.dll,并将其引用,大家请在网上查找

三.连接

创建一个asp.net web 窗口,输入如下代码:

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="WebForm2.aspx.cs" Inherits="MyTest03.WebForm2" %>

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<meta http-equiv="Content-Type" content="text/html; charset=utf-8"/>
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
        <div>

            <asp:Button ID="Button1" runat="server" OnClick="Button1_Click" Text="增" Width="31px" />
            <br />
            <asp:Button ID="Button2" runat="server" OnClick="Button2_Click" Text="删" />
            <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
            <br />
            <asp:Button ID="Button4" runat="server" OnClick="Button4_Click" Text="改" />
            <asp:TextBox ID="TextBox2" runat="server"></asp:TextBox>
            <br />
            <asp:Button ID="Button3" runat="server" OnClick="Button3_Click" Text="查" />
            <br />

        </div>
    </form>
</body>
</html>

其界面效果是这样的:
在这里插入图片描述
而对应的cs文件中,则输入如下代码:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using MySql.Data.MySqlClient;/*添加Mysql的引用*/

namespace MyTest03
{
    public partial class WebForm2 : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
        }
        
		//查寻功能
        protected void Button3_Click(object sender, EventArgs e)
        {   
            try {

                string sql = "select * from student";
                MySqlDataReader DR = MySqlHelper.ExecuteReader(MysqlData.DataBase, sql);

                string sex = "";
                while (DR.Read())
                {
                    sex = "";
                    if (DR.GetInt32(2) == 1)
                    {
                        sex = "男";
                    }
                    else
                    {
                        sex = "女";
                    }
                    Response.Write("编号:" + DR.GetString(0) + ",姓名:" + DR.GetString(1) + ",性别:" + sex + "<br/>");

                }
                DR.Close();//关闭

            } catch(Exception ex) {

                Response.Write(ex.Message);
            }

            
        }
        
		//增
        protected void Button1_Click(object sender, EventArgs e)
        {
            
           
            string sql = "insert into student value(4,'小绿',0)";

            MySqlConnection conn = new MySqlConnection(MysqlData.DataBase);//这里需要穿之前配置好类的参数
            conn.Open();//打开

            MySqlCommand cmd = new MySqlCommand();
            cmd.CommandText = sql;//指定cmd要执行的sql语句
            cmd.Connection = conn;//命令对象和那个连接对象关联
			
			/*
			上面的三行代码也可以换为更为简约的代码:
			MySqlCommand cmd = conn.CreateCommand();
            cmd.CommandText = sql;
            这样就可以省略掉:'cmd.Connection = conn;//命令对象和那个连接对象关联'这一句代码了
			*/

			//执行并返回结果
            int result =cmd.ExecuteNonQuery();
            cmd.Dispose();//将命令对象关闭
            conn.Close();//将连接对象关闭
			
			//判断返回的结果,是否执行成功
            if (result > 0)
            {
                Response.Write("增加成功!");
            }
            else
            {

                Response.Write("增加失败!");
            }




        }
        
		//删,其余的操作变化都不是很大,无非是sql语句的变化
        protected void Button2_Click(object sender, EventArgs e)
        {
            
           

            string te = TextBox1.Text;
            string sql = "delete from student where name='" + te + "'";

            MySqlConnection conn = new MySqlConnection(MysqlData.DataBase);
            conn.Open();


            MySqlCommand cmd = new MySqlCommand();
            cmd.CommandText = sql;
            cmd.Connection = conn;

            int result = cmd.ExecuteNonQuery();
            
            cmd.Dispose();
            conn.Close();
            
            
            if (result > 0)
            {
                Response.Write("删除成功!");

            }
            else
            {
                Response.Write("删除失败!");
            }

        }
		
		//改
        protected void Button4_Click(object sender, EventArgs e)
        {
            string te = TextBox2.Text;
            string sql = "update student set id=6 where name='" + te + "'";

            MySqlConnection conn = new MySqlConnection(MysqlData.DataBase);
            conn.Open();

            MySqlCommand cmd = new MySqlCommand();
            
            cmd.CommandText = sql;
            cmd.Connection = conn;

            int result =cmd.ExecuteNonQuery();
            
            cmd.Dispose();
            conn.Close();
            
            
            if (result > 0)
            {
                Response.Write("修改成功!");

            }
            else {
                Response.Write("修改失败!");
            }

        }
    }
}

四.结尾

为了数据库的安全,一般都是在取得返回之后,就将命令对象,连接对象关闭.

此为简陋的测试,接下来的学习中,如果遇到其他值得记录的代码,也会给小伙伴们分享.谢谢大家!

白露-

关注

3
点赞
踩
24

收藏

觉得还不错? 一键收藏
0
评论
ASP.net连接Mysql,并简单的增删改查

一.说明这一篇简单的个人总结,方便自己的复制粘贴,软件是Visual Studio 2019,SQLyog二.配置一.在Web.config中的configuration标签下添加如下代码:<connectionStrings> <add name="Conn_example" connectionString="Database='example';Data Source='127.0.0.1';User Id='root';Password='xxxxx';charset='
复制链接

扫一扫