C#操作mysql数据库的代码实例
直接看代码
using system;
using system.configuration;
using mysql.data.mysqlclient;
/// <summary>
/// testdatebase 的摘要说明
/// </summary>
public class testdatebase
{
public testdatebase()
{
//
// todo: 在此处添加构造函数逻辑
//
}
public static void main(string[] args)
{
mysqlconnection mysql = getmysqlcon();
//查询sql
string sqlsearch = "select * from student";
//插入sql
string sqlinsert = "insert into student values (12,'张三',25,'大专')";
//修改sql
string sqlupdate = "update student set name='李四' where id= 3";
//删除sql
string sqldel = "delete from student where id = 12";
//打印sql语句
console.writeline(sqldel);
//四种语句对象
//mysqlcommand mysqlcommand = getsqlcommand(sqlsearch, mysql);
//mysqlcommand mysqlcommand = getsqlcommand(sqlinsert, mysql);
//mysqlcommand mysqlcommand = getsqlcommand(sqlupdate, mysql);
mysqlcommand mysqlcommand = getsqlcommand(sqldel, mysql);
mysql.open();
//getresultset(mysqlcommand);
//getinsert(mysqlcommand);
//getupdate(mysqlcommand);
getdel(mysqlcommand);
//记得关闭
mysql.close();
string readline = console.readline();
}
/// <summary>
/// 建立mysql数据库链接
/// </summary>
/// <returns></returns>
public static mysqlconnection getmysqlcon()
{
string mysqlstr = "database=test;data source=127.0.0.1;user id=root;password=root;pooling=false;charset=utf8;port=3306";
// string mysqlcon = configurationmanager.connectionstrings["mysqlcon"].connectionstring;
mysqlconnection mysql = new mysqlconnection(mysqlstr);
return mysql;
}
/// <summary>
/// 建立执行命令语句对象
/// </summary>
/// <param name="sql"></param>
/// <param name="mysql"></param>
/// <returns></returns>
public static mysqlcommand getsqlcommand(string sql,mysqlconnection mysql)
{
mysqlcommand mysqlcommand = new mysqlcommand(sql, mysql);
// mysqlcommand mysqlcommand = new mysqlcommand(sql);
// mysqlcommand.connection = mysql;
return mysqlcommand;
}
/// <summary>
/// 查询并获得结果集并遍历
/// </summary>
/// <param name="mysqlcommand"></param>
public static void getresultset(mysqlcommand mysqlcommand)
{
mysqldatareader reader = mysqlcommand.executereader();
try
{
while (reader.read())
{
if (reader.hasrows)
{
console.writeline("编号:" + reader.getint32(0) + "|姓名:" + reader.getstring(1) + "|年龄:" + reader.getint32(2) + "|学历:" + reader.getstring(3));
}
}
}
catch (exception)
{
console.writeline("查询失败了!");
}
finally
{
reader.close();
}
}
/// <summary>
/// 添加数据
/// </summary>
/// <param name="mysqlcommand"></param>
public static void getinsert(mysqlcommand mysqlcommand)
{
try
{
mysqlcommand.executenonquery();
}
catch (exception ex)
{
string message = ex.message;
console.writeline("插入数据失败了!" + message);
}
}
/// <summary>
/// 修改数据
/// </summary>
/// <param name="mysqlcommand"></param>
public static void getupdate(mysqlcommand mysqlcommand)
{
try
{
mysqlcommand.executenonquery();
}
catch (exception ex)
{
string message = ex.message;
console.writeline("修改数据失败了!" + message);
}
}
/// <summary>
/// 删除数据
/// </summary>
/// <param name="mysqlcommand"></param>
public static void getdel(mysqlcommand mysqlcommand)
{
try
{
mysqlcommand.executenonquery();
}
catch (exception ex)
{
string message = ex.message;
console.writeline("删除数据失败了!" + message);
}
}
}