C#操作mysql資料庫

標籤：

轉  http://www.jb51.net/article/43486.htm

 

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);
}
}
}

C#操作mysql資料庫