1. 如果想要连接mysql数据库,则需要额外添加dll,也就好比java中的mysql的jar包, dll名称为:
MySql.Data.dll
那么,下载好dll文件后怎么引入呢?点击工程,右键 ->添加->引用->浏览->找到dll存放位置,点击确定即可引入引用
2. 增删改查操作代码
C# Code
1
2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 |
using System;
using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; using MySql.Data.MySqlClient; namespace DatabaseConn { class Program { // 获取数据库连接 public static MySqlConnection getMysqlConn() { MySqlConnectionStringBuilder builder = new MySqlConnectionStringBuilder(); builder.Database = "test"; builder.Server = "127.0.0.1"; builder.Port = 3306; builder.UserID = "root"; builder.Password = "lenovo"; builder.CharacterSet = "utf8"; // 连接方法2 //builder.Add("Database", "test"); //builder.Add("Data Source", "××××"); //builder.Add("User Id", "×××"); //builder.Add("Password", "×××"); MySqlConnection conn = new MySqlConnection(builder.ConnectionString); return conn; } static void Main( string[] args) { //1. 查询 //getResultSet(); //2. 插入 insert(); //3. 更新 //update(); //4. 删除 //delete(); } public static void getResultSet() { MySqlConnection conn = getMysqlConn(); // 获得连接 conn.Open(); String sql = "select * from student"; MySqlCommand command = new MySqlCommand(sql, conn); MySqlDataReader reader = command.ExecuteReader(); while (reader.Read()) { if (reader.HasRows) { Console.WriteLine(reader[ 1] + "," + reader[ 2]); } } Console.ReadKey(); // 释放资源,关闭连接 conn.Dispose(); } public static void insert() { MySqlConnection conn = getMysqlConn(); // 获得连接 conn.Open(); // 插入中文需要设置数据库为utf8,并且CharacterSet最好也是utf8 for ( int i = 1; i <= 10; i++ ) { String sql = "insert into student(name, password, age, sex) values(@name, @password, @age, @sex)"; MySqlCommand command = new MySqlCommand(sql, conn); command.Parameters.AddWithValue( "@name", "zhi" + i); command.Parameters.AddWithValue( "@password", "123" + i); command.Parameters.AddWithValue( "@age", 20 + i); if (i % 2 == 0) { command.Parameters.AddWithValue( "@sex", "男"); } else { command.Parameters.AddWithValue( "@sex", "女"); } command.ExecuteNonQuery(); } Console.WriteLine( "插入成功"); Console.ReadKey(); // 释放资源,关闭连接 conn.Dispose(); } public static void update() { MySqlConnection conn = getMysqlConn(); // 获得连接 conn.Open(); // 插入中文需要设置数据库为utf8,并且CharacterSet最好也是utf8 // 使用占位符设置参数 // 可以写成?id的这种形式,但是不推荐使用,还是写成@xxx吧 // String sql = "update student set password=@password where id=?id"; String sql = "update student set password=@password where id=@id"; MySqlCommand command = new MySqlCommand(sql, conn); // 给占位符设置值, 同样的也可以写成?password,但是为了统一,还是写成@password command.Parameters.AddWithValue( "@password", "456"); command.Parameters.AddWithValue( "@id", 1); command.ExecuteNonQuery(); Console.WriteLine( "更新成功"); Console.ReadKey(); // 释放资源,关闭连接 conn.Dispose(); } public static void delete() { MySqlConnection conn = getMysqlConn(); // 获得连接 conn.Open(); String sql = "delete from student where id=@idd"; MySqlCommand command = new MySqlCommand(sql, conn); // 给占位符设置值,@idd此处的值必须和sql中的@idd一致 command.Parameters.AddWithValue( "@idd", 2); command.ExecuteNonQuery(); Console.WriteLine( "删除成功"); Console.ReadKey(); // 释放资源,关闭连接 conn.Dispose(); } } } |