MySQL数据库(四)_使用MySql.Data.dll连接_读写mysql数据库

来源：互联网发布：游戏工作室网络组建编辑：程序博客网时间：2024/05/16 16:57

MySQL数据库(四)_使用MySql.Data.dll连接_读写mysql数据库<21/9/2017>

http://blog.csdn.net/liyuqian199695/article/details/53556639

1、下载 myql.data.all 下载地址如下

http://www.ddooo.com/softdown/43455.htm

2、把myql.data.all放到工程debug文件夹下，引入到C#工程中，右键工程->添加->引用，选择myql.data.all，最后确认即可

3、相关代码如下：

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 );        }    }}

实操补充:

using System;using System.Collections.Generic;using System.Linq;using System.Text;using MySql.Data.MySqlClient;namespace ToDB{    //数据库连接    //1.拿到Connection开启    //2.发送sql指令    //3.让数据库来执行sql执行    //4.拿到返回结果    //5.关闭数据库    //**Main数据插入**//    //MySqlCommand myCmd = new MySqlCommand("insert into student values(38,'郭明杰','woman',1）", myConnnect);    //Console.WriteLine(myCmd.CommandText);    //if (myCmd.ExecuteNonQuery() > 0)    //{    //    Console.WriteLine("数据插入成功！");    //}    //**Main数据插入**//    //**Main数据删除**//    //myCmd.CommandText = "delete from t_dept";    //Console.WriteLine(myCmd.CommandText);    //if (myCmd.ExecuteNonQuery() > 0)    //{    //    Console.WriteLine("user表类型数据全部删除成功！");    //}    //**Main数据删除**//    //myCmd.Dispose();    class Program    {        static void Main(string[] args)        {            string constructorString = "server=localhost;User Id=root;password=;Database=vr";            MySqlConnection myConnnect = new MySqlConnection(constructorString);            myConnnect.Open();            MySqlCommand myCmd1 = new MySqlCommand("select * from student", myConnnect);            GetResultset(myCmd1);            //MySqlCommand myCmd2 = new MySqlCommand("insert into student values(17,'liubei','man',3)",myConnnect);            MySqlCommand myCmd3 = new MySqlCommand("update student set gender='nn' where class_id=1", myConnnect);            int a = myCmd3.ExecuteNonQuery();//返回一个整数,表示执行受影响的行数            Console.WriteLine(a);            myConnnect.Close();            Console.WriteLine(myConnnect);        }        public static void GetResultset(MySqlCommand mySqlCommand)        {            MySqlDataReader reader = mySqlCommand.ExecuteReader();            try            {                while (reader.Read())                {                    if (reader.HasRows)                    {                        Console.WriteLine("sid:" + reader.GetInt32(0) + " sname:" + reader.GetString(1) + " gender:" + reader.GetString(2) + " class_id:" + reader.GetInt32(3));                    }                }            }            catch (Exception)            {                Console.WriteLine("查询失败了！");            }            finally            {                reader.Close();            }        }    }}

使用update...set...根据条件更改目标项:

关于MySqlDataReader的用法解析:

Execute补充:

//MySqlDataReader reader = comd.ExecuteReader();            //while (reader.Read())            //{            //    Console.WriteLine(reader.GetValue(0)+" \t"+reader.GetValue(1)+"      \t"+ reader.GetValue(2));            //}            //******Execute类型三选一只能同时执行一个,多个同时会报错******//            object o = comd.ExecuteScalar();//返回第一个数据            Console.WriteLine(o);

阅读全文

1 0