这是关于MySQL数据库的C#教程,包含了对MySQL数据库基本操作;
数据库访问组件MySql Connect/NET
MySql Connect/NET是MySQL官方提供给C#的接口,封装的非常好,操作MySQL就如同操作自家的SQLServer;
开始程序之旅
数据库模型层UserEntity.cs
using System;
using System.Collections.Generic;
using System.Text;
namespace AccessOfMysql.Entity
{
public class MysqlConfig
{
//主机
private string server;
public string Server
{
get
{
return server;
}
set
{
server = value;
}
}
//数据库
private string database;
public string Database
{
get
{
return database;
}
set
{
database = value;
}
}
//用户id
private string userid;
public string Userid
{
get
{
return userid;
}
set
{
userid = value;
}
}
//密码
private string password;
public string Password
{
get
{
return password;
}
set
{
password = value;
}
}
}
public class UserEntity
{
/// <summary>
/// 用户id
/// </summary>
private uint id;
public uint Id
{
get
{
return id;
}
set
{
id = value;
}
}
/// <summary>
/// 用户姓名
/// </summary>
private string name;
public string Name
{
get
{
return name;
}
set
{
name = value;
}
}
/// <summary>
/// 用户住址
/// </summary>
private string address;
public string Address
{
get
{
return address;
}
set
{
address = value;
}
}
}
}
数据库访问层UserAccess.cs
using System;
using System.Collections.Generic;
using System.Text;
using MySql.Data.MySqlClient;
using AccessOfMysql.Entity;
namespace AccessOfMysql
{
class UserAccess
{
private string connectString;
public UserAccess(string connectString)
{
this.connectString = connectString;
}
public List<UserEntity> GetUserListFromDb()
{
string query = @"SELECT *
FROM t_user";
List<UserEntity> userEntityList = new List<UserEntity>();
using (MySqlConnection connection = new MySqlConnection(this.connectString))
{
//打开数据库连接
connection.Open();
//创建SqlCommand对象
MySqlCommand command = new MySqlCommand(query, connection);
//执行SQL,返回查询结果
using (MySqlDataReader dataReader = command.ExecuteReader())
{
while (dataReader.Read())
{
UserEntity userEntity = new UserEntity();
userEntity.Id = dataReader.GetUInt32(0);
userEntity.Name = dataReader.GetString(1);
userEntity.Address = dataReader.GetString(2);
userEntityList.Add(userEntity);
}
}
}
return userEntityList;
}
public void InserUserToDb(UserEntity user)
{
string query = @"INSERT INTO t_user (name, address)
VALUES (@name, @address)";
using (MySqlConnection connection = new MySqlConnection(this.connectString))
{
//打开数据库
connection.Open();
//创建SqlCommand
MySqlCommand command = new MySqlCommand(query, connection);
command.Parameters.AddWithValue("@name", user.Name);
command.Parameters.AddWithValue("@address", user.Address);
//执行SQL语句,不查询
command.ExecuteNonQuery();
}
}
public int GetUserCountFromDb()
{
string query = @"SELECT COUNT(*)
FROM t_user";
using (MySqlConnection connection = new MySqlConnection(connectString))
{
//打开数据库
connection.Open();
MySqlCommand command = new MySqlCommand(query, connection);
//执行SQL,返回条目数
int count = int.Parse(command.ExecuteScalar().ToString());
return count;
}
}
}
}
主程序Program.cs
using System;
using System.Collections.Generic;
using System.Text;
using MySql.Data.MySqlClient;
using AccessOfMysql.Entity;
namespace AccessOfMysql
{
class Program
{
static void Main(string[] args)
{
//数据库连接字符串(charset=utf8:解决插入汉字乱码问题)
string connectString = "server=127.0.0.1;database=cjtdb;uid=root;pwd=root;charset=utf8";
UserAccess userAccess = new UserAccess(connectString);
try
{
//取得用户信息一览
List<UserEntity> userEntityList = userAccess.GetUserListFromDb();
foreach(UserEntity userEntity in userEntityList)
{
Console.WriteLine("id={0}, name={1}, address={2}",
userEntity.Id, userEntity.Name, userEntity.Address);
}
//插入用户信息
UserEntity user = new UserEntity();
user.Name = "李小龙";
user.Address = "上海";
userAccess.InserUserToDb(user);
//统计用户信息总条数
int count = userAccess.GetUserCountFromDb();
}
catch (MySqlException ex)
{
Console.WriteLine(ex.ToString());
}
}
}
}