C#连接MySQL的详细步骤_Asp.net

c# 连接mysql

1. 添加mysql引用

安装完mysql之后，在安装的默认目录 c:program files (x86)mysqlconnector net 8.0 中查找mysqldata.dll文件。

在visual studio 中为项目中添加引用。

2. 引入命名空间

using mysql.data.mysqlclient;

3. 构建连接

private static mysqlconnection m_connect = null;
private static void connect()
{
    string connectstr = "server=127.0.0.1;port=3306;database=test;user=root;password=root;";
    m_connect = new mysqlconnection(connectstr);
}


server=ip地址;
port=端口号;
database=数据库名字;
user=管理员账号;
password=账号的密码;

4. 增删改查

增

private static void insert()
{
    try
    {
        m_connect.open();
        //string sqlstr = "insert into users(username, password) values('takil', '789')";
        //string sqlstr = "insert into users(username, password, registerdate) values('taksil', '789789', '2015-05-09')";
        string sqlstr = "insert into users(username, password, registerdate) values('wilhelm', '78889', '" + datetime.now + "')";
       
        mysqlcommand cmd = new mysqlcommand(sqlstr, m_connect);
        int result = cmd.executenonquery();
        console.writeline("成功影响了{0}条数据", result);
    }
    catch (exception ex)
    {
        console.writeline(ex.tostring());
    }
    finally
    {
        m_connect?.close();
    }
}

删

private static void delete()
{
    try
    {
        m_connect.open();
        string sql = "delete from users where username = 'takil'";

        
        mysqlcommand cmd = new mysqlcommand(sql, m_connect);
        int result = cmd.executenonquery();
        console.writeline("成功影响了{0}条数据", result);
    }
    catch (exception ex)
    {
        console.writeline(ex.tostring());
    }
    finally
    { m_connect?.close(); }
}

改

private static void update()
{
    try
    {
        m_connect.open();
        string sql = "update users set username = 'wqrwq', password = '123' where id = 5";

        mysqlcommand cmd = new mysqlcommand( sql, m_connect);
        int result = cmd.executenonquery();
        console.writeline("成功影响了{0}条数据", result);
    }
    catch (exception ex)
    {
        console.writeline(ex.tostring());
    }
    finally
    { m_connect?.close(); }
}

查

private static void read()
{
    try
    {
        m_connect.open();
        string sqlstr = "select * from users";
        //string sqlstr = "select id, username, registerdate from users";
        
        mysqlcommand cmd = new mysqlcommand(sqlstr, m_connect);
        mysqldatareader reader = cmd.executereader();
        while (reader.read())
        {
            //console.writeline(reader[0].tostring() + " " + reader[1].tostring() + " " + reader[2].tostring());
            //console.writeline(reader.getint32(0) + " " + reader.getstring(1) + " " + reader.getstring(2));
            console.writeline(reader.getint32("id") + " " + reader.getstring("username") + " " + reader.getstring("password"));
        }
    }
    catch(exception ex)
    {
        console.writeline(ex.tostring());
    }
    finally
    {
        m_connect?.close();
    }
}


private static void readcount()
{
    try
    {
        m_connect.open();
        string sqlstr = "select count(*) from users";
        mysqlcommand cmd = new mysqlcommand(sqlstr, m_connect);

        //mysqldatareader reader = cmd.executereader();
        //reader.read();
        //int count = convert.toint32(reader[0]);
        //console.writeline("总数为{0}", count);

        object result = cmd.executescalar();
        int count = convert.toint32(result);
        console.writeline("总数为{0}", count);
    }
    catch (exception ex)
    {
        console.writeline(ex.tostring());
    }
    finally
    { m_connect?.close(); }
}

验证

private static bool valifyuser(string username, string password)
{
    try
    {
        m_connect.open();
        //string sqlstr = "select * from users where username = '" + username + "' and password = '" + password + "'";
        string sqlstr = "select * from users where username = @para1 and password = @para2";

        mysqlcommand cmd = new mysqlcommand(sqlstr, m_connect);

        cmd.parameters.addwithvalue("para1", username);
        cmd.parameters.addwithvalue("para2", password);

        mysqldatareader reader = cmd.executereader();
        if(reader.read())
        {
            return true;
        }
    }
    catch (exception ex)
    {
        console.writeline(ex.tostring());
    }
    finally
    {
        m_connect?.close();
    }
    return false;
}

到此这篇关于c#连接mysql的详细步骤的文章就介绍到这了,更多相关c#连接mysql内容请搜索代码网以前的文章或继续浏览下面的相关文章希望大家以后多多支持代码网！