C#操作mysql数据库的代码实例

直接看代码

using system;
using system.configuration;
using mysql.data.mysqlclient;
/// <summary>
/// testdatebase 的摘要说明
/// </summary>
public class testdatebase
{
    public testdatebase()
    {
        //
        // todo: 在此处添加构造函数逻辑
        //
    }
    public static void main(string[] args)
    {
        mysqlconnection mysql = getmysqlcon();
        //查询sql
        string sqlsearch = "select * from student";
        //插入sql
        string sqlinsert = "insert into student values (12,'张三',25,'大专')";
        //修改sql
        string sqlupdate = "update student set name='李四' where id= 3";
        //删除sql
        string sqldel = "delete from student where id = 12";
        //打印sql语句
        console.writeline(sqldel);
        //四种语句对象
        //mysqlcommand mysqlcommand = getsqlcommand(sqlsearch, mysql);
        //mysqlcommand mysqlcommand = getsqlcommand(sqlinsert, mysql);
        //mysqlcommand mysqlcommand = getsqlcommand(sqlupdate, mysql);
        mysqlcommand mysqlcommand = getsqlcommand(sqldel, mysql);
        mysql.open();
        //getresultset(mysqlcommand);
        //getinsert(mysqlcommand);
        //getupdate(mysqlcommand);
        getdel(mysqlcommand);
        //记得关闭
        mysql.close();
       string readline = console.readline();
    }
    /// <summary>
    /// 建立mysql数据库链接
    /// </summary>
    /// <returns></returns>
    public static mysqlconnection getmysqlcon()
    {
        string mysqlstr = "database=test;data source=127.0.0.1;user id=root;password=root;pooling=false;charset=utf8;port=3306";
        // string mysqlcon = configurationmanager.connectionstrings["mysqlcon"].connectionstring;
        mysqlconnection mysql = new mysqlconnection(mysqlstr);
        return mysql;
    }
    /// <summary>
    /// 建立执行命令语句对象
    /// </summary>
    /// <param name="sql"></param>
    /// <param name="mysql"></param>
    /// <returns></returns>
    public static mysqlcommand getsqlcommand(string sql,mysqlconnection mysql)
    {
        mysqlcommand mysqlcommand = new mysqlcommand(sql, mysql);
        //  mysqlcommand mysqlcommand = new mysqlcommand(sql);
        // mysqlcommand.connection = mysql;
        return mysqlcommand;
    }
    /// <summary>
    /// 查询并获得结果集并遍历
    /// </summary>
    /// <param name="mysqlcommand"></param>
    public static void getresultset(mysqlcommand mysqlcommand)
    {
        mysqldatareader reader = mysqlcommand.executereader();
        try
        {
            while (reader.read())
            {
                if (reader.hasrows)
                {
                    console.writeline("编号:" + reader.getint32(0) + "|姓名:" + reader.getstring(1) + "|年龄:" + reader.getint32(2) + "|学历:" + reader.getstring(3));
                }
            }
        }
        catch (exception)
        {

            console.writeline("查询失败了！");
        }
        finally
        {
            reader.close();
        }
    }
    /// <summary>
    /// 添加数据
    /// </summary>
    /// <param name="mysqlcommand"></param>
    public static void getinsert(mysqlcommand mysqlcommand)
    {
        try
        {
            mysqlcommand.executenonquery();
        }
        catch (exception ex)
        {
            string message = ex.message;
            console.writeline("插入数据失败了！" + message);
        }

    }
    /// <summary>
    /// 修改数据
    /// </summary>
    /// <param name="mysqlcommand"></param>
    public static void getupdate(mysqlcommand mysqlcommand)
    {
        try
        {
            mysqlcommand.executenonquery();
        }
        catch (exception ex)
        {

            string message = ex.message;
            console.writeline("修改数据失败了！" + message);
        }
    }
    /// <summary>
    /// 删除数据
    /// </summary>
    /// <param name="mysqlcommand"></param>
    public static void getdel(mysqlcommand mysqlcommand)
    {
        try
        {
            mysqlcommand.executenonquery();
        }
        catch (exception ex)
        {
            string message = ex.message;
            console.writeline("删除数据失败了！" + message);
        }
    }
}