C#代码CRUD操作MySQL数据库

首先Program.cs代码如下:

 class Program
    {
        static void Main(string[] args)
        {
            //
            CrudDemo app = new CrudDemo("localhost", "b2cshop", "root", "123456");

            //测试连接状态
            app.TestConnection();
            app.CreateData();
            app.ReadData();
            app.UpdateData(7); 
            app.ReadData();
            app.DeleteData(8); 
            app.ReadData();
            app.BulkData();
        }
    }

CrudDemo.cs内容如下:

 public class CrudDemo
    {
        private string connString;


        //构造函数 初始化连接字符串
        public CrudDemo(string server,string database,string uid,string password)
        {
            connString = string.Format("server={0};database={1};user={2};password={3}", 
                server, database, uid, password);
        }

        //测试连接状态
        public void TestConnection()
        {
            try
            {
                MySqlConnection conn = new MySqlConnection(connString);
                conn.Open();
                Console.WriteLine("Connected");
                conn.Close();
                Console.WriteLine("Closed");
            }catch(MySqlException e)
            {
                Console.WriteLine("Error: " + e.Message);
            }

        }

        //创建数据
        public void CreateData()
        {
            try
            {
                MySqlConnection conn = new MySqlConnection(connString);
                conn.Open();
                Console.WriteLine("Connected");

                string query = "insert into product(name,price,created) values(@name,@price,@created)";
                MySqlCommand cmd = new MySqlCommand(query, conn);
                cmd.Parameters.Add("@name", MySqlDbType.VarChar, 45);
                cmd.Parameters.Add("@price", MySqlDbType.Float);
                cmd.Parameters.Add("@created", MySqlDbType.DateTime);

                Console.Write("Inserting 10 data....");
                DateTime now = DateTime.Now;
                for (int i = 1; i <= 10;i++ )
                {
                    cmd.Parameters[0].Value = "product-" + i;
                    cmd.Parameters[1].Value = 0.26 * i;
                    cmd.Parameters[2].Value = now;

                    cmd.ExecuteNonQuery();
                }
                Console.WriteLine("Done");

                conn.Close();
                Console.WriteLine("Closed");
            }catch(MySqlException e)
            {
                Console.WriteLine("Error: " + e.Message);
            }
        }

        //批量增加数据
        public void BulkData()
        {
            try
            {
                MySqlConnection conn = new MySqlConnection(connString);
                conn.Open();
                Console.WriteLine("Connected");

                MySqlBulkLoader bulk = new MySqlBulkLoader(conn);
                bulk.TableName = "product";
                bulk.FieldTerminator = "\t";
                bulk.LineTerminator = "\n";
                bulk.FileName = "D:/product.txt"; 
                bulk.NumberOfLinesToSkip = 0;
                bulk.Columns.Add("name");
                bulk.Columns.Add("price");
                bulk.Columns.Add("created");
                
                Console.Write("Inserting bulk data....");
                int count = bulk.Load();
                Console.WriteLine("Done-" + count.ToString());

                conn.Close();
                Console.WriteLine("Closed");
            }
            catch (MySqlException e)
            {
                Console.WriteLine("Error: " + e.Message);
            }
        }

        //读取数据
        public void ReadData()
        {
            try
            {
                MySqlConnection conn = new MySqlConnection(connString);
                conn.Open();
                Console.WriteLine("Connected");

                string query = "select idproduct,name,price,created from product";
                MySqlCommand cmd = new MySqlCommand(query, conn);

                MySqlDataReader rd = cmd.ExecuteReader();
                while(rd.Read())
                {
                    Console.WriteLine("Id: " + rd["idproduct"].ToString());
                    Console.WriteLine("Name: " + rd["name"].ToString());
                    Console.WriteLine("Price: " + rd["price"].ToString());
                    Console.WriteLine("Created: " + rd["created"].ToString());
                    Console.WriteLine("---------------------------");
                }
                rd.Close();

                conn.Close();
                Console.WriteLine("Closed");
            }catch(MySqlException e)
            {
                Console.WriteLine("Error: " + e.Message);
            }
        }
        //更新数据
        public void UpdateData(int id)
        {
            try
            {
                MySqlConnection conn = new MySqlConnection(connString);
                conn.Open();
                Console.WriteLine("Connected");

                string query = "update product set name=@name,price=@price where idproduct=@id";
                MySqlCommand cmd = new MySqlCommand(query, conn);
                cmd.Parameters.Add("@name", MySqlDbType.VarChar, 45);
                cmd.Parameters.Add("@price", MySqlDbType.Float);
                cmd.Parameters.Add("@id", MySqlDbType.Int32);

                Console.Write("Updating data....");
                cmd.Parameters[0].Value = "product-update";
                cmd.Parameters[1].Value = 0.75;
                cmd.Parameters[2].Value = id;

                cmd.ExecuteNonQuery();
                Console.WriteLine("Done");


                conn.Close();
                Console.WriteLine("Closed");
            }catch(MySqlException e)
            {
                Console.WriteLine("Error: " + e.Message);
            }
        }

        //删除数据
        public void DeleteData(int id)
        {
            try
            {
                MySqlConnection conn = new MySqlConnection(connString);
                conn.Open();
                Console.WriteLine("Connected");

                string query = "delete from product where idproduct=@id";
                MySqlCommand cmd = new MySqlCommand(query, conn);
                cmd.Parameters.Add("@id", MySqlDbType.Int32);

                Console.Write("Deleting data....");
                cmd.Parameters[0].Value = id;

                cmd.ExecuteNonQuery();
                Console.WriteLine("Done");


                conn.Close();
                Console.WriteLine("Closed");
            }
            catch (MySqlException e)
            {
                Console.WriteLine("Error: " + e.Message);
            }
        }
    }

product.txt数据如下:

product10	105	2014-01-09 19:26:30
product11	105	2014-01-09 19:26:30
product12	105	2014-01-09 19:26:30
product13	105	2014-01-09 19:26:30
product14	105	2014-01-09 19:26:30

b2cshop.sql脚步内容:

/*
Navicat MySQL Data Transfer

Source Server         : localhost_3306
Source Server Version : 50617
Source Host           : localhost:3306
Source Database       : b2cshop

Target Server Type    : MYSQL
Target Server Version : 50617
File Encoding         : 65001

Date: 2016-07-31 22:06:36
*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for `product`
-- ----------------------------
DROP TABLE IF EXISTS `product`;
CREATE TABLE `product` (
  `idproduct` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(200) DEFAULT NULL,
  `price` float DEFAULT NULL,
  `created` datetime DEFAULT NULL,
  PRIMARY KEY (`idproduct`)
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of product
-- ----------------------------
INSERT INTO `product` VALUES ('1', 'product-1', '0.26', '2016-07-30 00:36:02');
INSERT INTO `product` VALUES ('2', 'product-2', '0.52', '2016-07-30 00:36:02');
INSERT INTO `product` VALUES ('3', 'product-3', '0.78', '2016-07-30 00:36:02');
INSERT INTO `product` VALUES ('4', 'product-4', '1.04', '2016-07-30 00:36:02');
INSERT INTO `product` VALUES ('5', 'product-5', '1.3', '2016-07-30 00:36:02');
INSERT INTO `product` VALUES ('6', 'product-6', '1.56', '2016-07-30 00:36:02');
INSERT INTO `product` VALUES ('7', 'product-update', '0.75', '2016-07-30 00:36:02');
INSERT INTO `product` VALUES ('9', 'product-9', '2.34', '2016-07-30 00:36:02');
INSERT INTO `product` VALUES ('10', 'product-10', '2.6', '2016-07-30 00:36:02');
INSERT INTO `product` VALUES ('11', 'product10', '105', '2014-01-09 19:26:30');
INSERT INTO `product` VALUES ('12', 'product11', '105', '2014-01-09 19:26:30');
INSERT INTO `product` VALUES ('13', 'product12', '105', '2014-01-09 19:26:30');
INSERT INTO `product` VALUES ('14', 'product13', '105', '2014-01-09 19:26:30');
INSERT INTO `product` VALUES ('15', 'product14', '105', '2014-01-09 19:26:30');