c#使用vs2010连接mysql数据库的增删改查操作

首先,要在mysql官网下载连接器, 这个是地址 http://dev.mysql.com/downloads/connector/net/

下载完成后将bin目录里面的MySql.Data.dll添加到工程的引用中.确保引用中有mysql.data.

然后是在mysql中创建mysqltest数据库,然后用下面的代码创建表

create table student
(
	id int(3) auto_increment not null primary key,
	name varchar(30) not null,
	address varchar(40) default '西安'
);

然后就是连接的代码来,贴在下面



using System;
using MySql.Data;
using MySql.Data.MySqlClient;


public class mysql_test
{
    public static void Main()
    {

        MySqlConnection mysql = getconn();

        string sqlSearch = "select * from students";
        string sqlInsert = "insert into students(name,id,address) values('测试',122,'北京')";
        string sqlUpdate = "update students set name = '成功' where id = 122";
        string sqlDelete = "delete from students where id = 122";
        string sqlTruncate = "truncate students";

        try
        {


            MySqlCommand mysqlselect = getsqlCommand(sqlSearch, mysql);
            MySqlCommand mysqlinsert = getsqlCommand(sqlInsert, mysql);
            MySqlCommand mysqlupdate = getsqlCommand(sqlUpdate, mysql);
            MySqlCommand mysqldelete = getsqlCommand(sqlDelete, mysql);
            MySqlCommand mysqlTruncate = getsqlCommand(sqlTruncate, mysql);

            mysql.Open();


            Console.WriteLine(mysql.ServerVersion + "n" + mysql.ConnectionString + "n" + mysql.Database + "n" + mysql.DataSource + "n");


            getTruncate(mysqlTruncate); //清空表
            InsertTestData(mysql);   //插入测试数据

            getResult(mysqlselect);
            Console.WriteLine();

            getDelete(mysqldelete);
            getInsert(mysqlinsert);
            getUpdate(mysqlupdate);
       //     getDelete(mysqldelete);
            getResult(mysqlselect);

            mysql.Close();
        }
        catch (MySqlException ex)
        {
            Console.Write(ex.Message);
        }

        Console.ReadLine();
    }
    public static void getResult(MySqlCommand mysqlcommand)
    {
        MySqlDataReader reader = mysqlcommand.ExecuteReader();
        try
        {
            while (reader.Read())
            {
                if (reader.HasRows)
                {
                    Console.WriteLine(" 姓名:  " + reader.GetString(1) + "  编号  " + reader.GetInt32(0) + "  地址  " + reader.GetString(2));
                }
            }
        }
        catch (MySqlException ex)
        {
            Console.WriteLine("查询失败!"+ex.Message);
        }
        finally
        {
            reader.Close();
        }
    }


    public static MySqlCommand getsqlCommand(string sql, MySqlConnection mysql)
    {
        MySqlCommand mysqlcommand = new MySqlCommand(sql, mysql);
        return mysqlcommand;
    }


    public static MySqlConnection getconn()
    {
        string mysqlStr = "Database=student;Data Source = 192.168.142.129;User Id=root;password=rootpassword;charset=gbk;port=3306";
        MySqlConnection mysql = new MySqlConnection(mysqlStr);
        return mysql;

    }
    public static void getTruncate(MySqlCommand mysqlcommand)
    {
        try
        {
            mysqlcommand.ExecuteNonQuery();
        }
        catch (MySqlException ex)
        {
            string message = ex.Message;
            Console.WriteLine("清空表失败! " + message);
        }
    }
    public static void getUpdate(MySqlCommand mysqlcommand)
    {
        try
        {
            mysqlcommand.ExecuteNonQuery();
        }
        catch (MySqlException ex)
        {
            string message = ex.Message;
            Console.WriteLine("修改数据失败! " + message);
        }
    }
    public static void getDelete(MySqlCommand mysqlcommand)
    {
        try
        {
            mysqlcommand.ExecuteNonQuery();
        }
        catch (MySqlException ex)
        {
            string message = ex.Message;
            Console.WriteLine("删除数据失败! " + message);
        }
    }

    public static void getInsert(MySqlCommand mysqlcommand)
    {
        try
        {
            mysqlcommand.ExecuteNonQuery();
        }
        catch (MySqlException ex)
        {
            string message = ex.Message;
            Console.WriteLine("插入数据失败! " + message);
        }
    }
    public static void InsertTestData(MySqlConnection mysql)
    {
        int i = 0;
        while (i++ != 10)
        {
            string cmd12 = "insert into students(name,id,address) values('小王'," + i + ",'西安')";
            MySqlCommand mycmd = new MySqlCommand(cmd12, mysql);
            if (mycmd.ExecuteNonQuery() > 0)
            {
                Console.WriteLine("数据插入成功!{0}", i);
            }

        }

    }
}

好了 大概就是这样子,希望对大家有所帮助.

发表评论

电子邮件地址不会被公开。 必填项已用*标注