MySQL数据库(四)_使用MySql.Data.dll连接_读写mysql数据库<21/9/2017>
http://blog.csdn.net/liyuqian199695/article/details/53556639
1、下载 myql.data.all 下载地址如下
http://www.ddooo.com/softdown/43455.htm
2、 把myql.data.all放到工程debug文件夹下，引入到C#工程中，右键工程->添加->引用，选择myql.data.all，最后确认即可
3、相关代码如下：
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 );
}
}
}
实操补充:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MySql.Data.MySqlClient;
namespace ToDB
{
//数据库连接
//1.拿到Connection开启
//2.发送sql指令
//3.让数据库来执行sql执行
//4.拿到返回结果
//5.关闭数据库
//**Main数据插入**//
//MySqlCommand myCmd = new MySqlCommand("insert into student values(38,'郭明杰','woman',1）", myConnnect);
//Console.WriteLine(myCmd.CommandText);
//if (myCmd.ExecuteNonQuery() > 0)
//{
// Console.WriteLine("数据插入成功！");
//}
//**Main数据插入**//
//**Main数据删除**//
//myCmd.CommandText = "delete from t_dept";
//Console.WriteLine(myCmd.CommandText);
//if (myCmd.ExecuteNonQuery() > 0)
//{
// Console.WriteLine("user表类型数据全部删除成功！");
//}
//**Main数据删除**//
//myCmd.Dispose();
class Program
{
static void Main(string[] args)
{
string constructorString = "server=localhost;User Id=root;password=;Database=vr";
MySqlConnection myConnnect = new MySqlConnection(constructorString);
myConnnect.Open();
MySqlCommand myCmd1 = new MySqlCommand("select * from student", myConnnect);
GetResultset(myCmd1);
//MySqlCommand myCmd2 = new MySqlCommand("insert into student values(17,'liubei','man',3)",myConnnect);
MySqlCommand myCmd3 = new MySqlCommand("update student set gender='nn' where class_id=1", myConnnect);
int a = myCmd3.ExecuteNonQuery();//返回一个整数,表示执行受影响的行数
Console.WriteLine(a);
myConnnect.Close();
Console.WriteLine(myConnnect);
}
public static void GetResultset(MySqlCommand mySqlCommand)
{
MySqlDataReader reader = mySqlCommand.ExecuteReader();
try
{
while (reader.Read())
{
if (reader.HasRows)
{
Console.WriteLine("sid:" + reader.GetInt32(0) + " sname:" + reader.GetString(1) + " gender:" + reader.GetString(2) + " class_id:" + reader.GetInt32(3));
}
}
}
catch (Exception)
{
Console.WriteLine("查询失败了！");
}
finally
{
reader.Close();
}
}
}
}
使用update...set...根据条件更改目标项:
关于MySqlDataReader的用法解析:
Execute补充:
//MySqlDataReader reader = comd.ExecuteReader();
//while (reader.Read())
//{
// Console.WriteLine(reader.GetValue(0)+" \t"+reader.GetValue(1)+" \t"+ reader.GetValue(2));
//}
//******Execute类型三选一只能同时执行一个,多个同时会报错******//
object o = comd.ExecuteScalar();//返回第一个数据
Console.WriteLine(o);