运行环境:visual studio 2017 .NET窗口控制台程序。
安装好数据库环境后找到C:\Program Files (x86)\MySQL\Connector NET 6.10\Assemblies\v4.5.2路径下的MySql.Data动态库文件,并导入C#工程。如果没有改文件夹,在安装MySQL时需勾选Connector C#安装程序。
具体查询与插入的代码如下:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using MySql.Data.MySqlClient;
namespace LinkMySQL
{
class Program
{
static void Main(string[] args)
{
//datasource:IP地址;port:端口号;database:数据库名;user:用户名;password:密码;
public const string ConnectionString = "datasource=127.0.0.1;port=3306;database=gamedata;user=root;password=root;";
MySqlConnection connection = new MySqlConnection(ConnectionString);
try
{
connection.Open();
}
catch (Exception ex)
{
Console.WriteLine("连接数据库异常:" + ex);
return;
}
VerifyUser(connection,"sy","sy");
//依次类推删除操作
//MySqlCommand cmdInsert = new MySqlCommand("delete from user where id = @id", connection);
//cmdInsert.Parameters.AddWithValue("id", 1);
//依次类推更新操作
//MySqlCommand cmdInsert = new MySqlCommand("updata user set password = @pwd where id = 1", connection);
//cmdInsert.Parameters.AddWithValue("pwd", "sadad");
connection.Close();
}
/// <summary>
/// 插入操作(用于用户注册)
/// </summary>
/// <param name="_connection"></param>
/// <param name="_username"></param>
/// <param name="_password"></param>
/// <returns></returns>
public bool Register(MySqlConnection _connection, string _username, string _password)
{
try
{
MySqlCommand cmdInsert = new MySqlCommand("insert into user set username=@un,password=@pwd", _connection);
cmdInsert.Parameters.AddWithValue("un", _username);
cmdInsert.Parameters.AddWithValue("pwd", _password);
cmdInsert.ExecuteNonQuery();
return true;
}
catch (Exception e)
{
Console.WriteLine("Register函数调用出错:" + e);
return false;
}
}
/// <summary>
/// 根据账号密码查询(用于用户登入时校验账号密码)
/// </summary>
/// <param name="_username"></param>
/// <param name="_password"></param>
public Bool VerifyUser(MySqlConnection _connection, string _username, string _password)
{
MySqlDataReader reader = null;
try
{
MySqlCommand cmd = new MySqlCommand("select * from user where username = @username and password = @password", _connection);
cmd.Parameters.AddWithValue("username", _username);
cmd.Parameters.AddWithValue("password", _password);
//执行数据库的查询语句
reader = cmd.ExecuteReader();
if (reader.Read())
{
int id = reader.GetInt32("id");
return true;
}
}
catch
{
Console.WriteLine("VerifyUser函数调用出错: 不存在该用户!");
}
finally
{
if (reader != null) reader.Close();
}
return false;
}
}
}