c# 连接mysql 增删改查操作

[c#]代码库

 
usingSystem;
usingSystem.Configuration;
usingMySql.Data.MySqlClient;
/// <summary>
/// TestDatebase 的摘要说明
/// </summary>
publicclassTestDatebase
{
publicTestDatebase()
{
//
// TODO: 在此处添加构造函数逻辑
//
}
publicstaticvoidMain ( 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>
publicstaticMySqlConnection 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 =newMySqlConnection ( mysqlStr );
returnmysql;
}
/// <summary>
/// 建立执行命令语句对象
/// </summary>
/// <param name="sql"></param>
/// <param name="mysql"></param>
/// <returns></returns>
publicstaticMySqlCommand getSqlCommand ( String sql,MySqlConnection mysql )
{
MySqlCommand mySqlCommand =newMySqlCommand ( sql, mysql );
// MySqlCommand mySqlCommand = new MySqlCommand(sql);
// mySqlCommand.Connection = mysql;
returnmySqlCommand;
}
/// <summary>
/// 查询并获得结果集并遍历
/// </summary>
/// <param name="mySqlCommand"></param>
publicstaticvoidgetResultset ( 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>
publicstaticvoidgetInsert ( MySqlCommand mySqlCommand )
{
try
{
mySqlCommand.ExecuteNonQuery();
}
catch( Exception ex )
{
String message = ex.Message;
Console.WriteLine ("插入数据失败了！"+ message );
}
 
}
/// <summary>
/// 修改数据
/// </summary>
/// <param name="mySqlCommand"></param>
publicstaticvoidgetUpdate ( MySqlCommand mySqlCommand )
{
try
{
mySqlCommand.ExecuteNonQuery();
}
catch( Exception ex )
{
 
String message = ex.Message;
Console.WriteLine ("修改数据失败了！"+ message );
}
}
/// <summary>
/// 删除数据
/// </summary>
/// <param name="mySqlCommand"></param>
publicstaticvoidgetDel ( MySqlCommand mySqlCommand )
{
try
{
mySqlCommand.ExecuteNonQuery();
}
catch( Exception ex )
{
String message = ex.Message;
Console.WriteLine ("删除数据失败了！"+ message );
}
}
}