using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using MySql.Data.MySqlClient;
namespace MySQL_Using
{
class Program
{
static void Main(string[] args)
{
//數(shù)據(jù)庫名為test1
// String mysqlStr = "Database=自己的數(shù)據(jù)庫名;Data Source=127.0.0.1;User Id=改為自己;Password=自己的密碼;pooling=false;CharSet=utf8;port=3306";
String mysqlStr = "Database=test1;Data Source=127.0.0.1;User Id=root;Password=123456;pooling=false;CharSet=utf8;port=3306";
//MySql連接類
MySqlConnection mysql = new MySqlConnection(mysqlStr);
//查詢sql
//account表格
String abc = "account";
String sqlSearch = "select * from ";
sqlSearch = sqlSearch + abc;
//sql 插入數(shù)據(jù)
String sqlinsert = "insert into account values(5,'pyq','pyq')";
//MySql的命令類
//查詢語句
MySqlCommand mySqlCommand = new MySqlCommand(sqlSearch, mysql);
//插入數(shù)據(jù)
// MySqlCommand mySqlCommand = new MySqlCommand(sqlinsert, mysql);
//打開連接
mysql.Open();
//建立流
//數(shù)據(jù)讀取類
MySqlDataReader reader = mySqlCommand.ExecuteReader();
try
{//每次讀取一個(gè)字節(jié)
while (reader.Read())
{
if (reader.HasRows)//是否讀取玩一行
{
//從數(shù)據(jù)庫讀取的數(shù)據(jù)要進(jìn)行類型轉(zhuǎn)換
//0,1,2,表示在數(shù)據(jù)庫表中的位置
Console.WriteLine("ID:" + reader.GetInt32(0) + "|賬號:" + reader.GetString(1) + "|密碼:" + reader.GetString(2));
}
}
}
catch (Exception)
{
Console.WriteLine("查詢失敗了！");
}
finally
{
reader.Close();//關(guān)閉流
}
mysql.Close();
Console.Read();
}
/// <summary>
/// 建立mysql數(shù)據(jù)庫鏈接
/// </summary>
/// <returns></returns>
public static MySqlConnection getMySqlCon()
{
String mysqlStr = "Database=test1;Data Source=127.0.0.1;User Id=root;Password=123456;pooling=false;CharSet=utf8;port=3306";
// String mySqlCon = ConfigurationManager.ConnectionStrings["MySqlCon"].ConnectionString;
MySqlConnection mysql = new MySqlConnection(mysqlStr);
return mysql;
}
/// <summary>
/// 建立執(zhí)行命令語句對象
/// </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>
/// 查詢并獲得結(jié)果集并遍歷
/// </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.GetString(2));
}
}
}
catch (Exception)
{
Console.WriteLine("查詢失敗了！");
}
finally
{
reader.Close();
}
}
/// <summary>
/// 添加數(shù)據(jù)
/// </summary>
/// <param name="mySqlCommand"></param>
public static void getInsert(MySqlCommand mySqlCommand)
{
try
{
mySqlCommand.ExecuteNonQuery();
}
catch (Exception ex)
{
String message = ex.Message;
Console.WriteLine("插入數(shù)據(jù)失敗了！" + message);
}
}
/// <summary>
/// 修改數(shù)據(jù)
/// </summary>
/// <param name="mySqlCommand"></param>
public static void getUpdate(MySqlCommand mySqlCommand)
{
try
{
mySqlCommand.ExecuteNonQuery();
}
catch (Exception ex)
{
String message = ex.Message;
Console.WriteLine("修改數(shù)據(jù)失敗了！" + message);
}
}
/// <summary>
/// 刪除數(shù)據(jù)
/// </summary>
/// <param name="mySqlCommand"></param>
public static void getDel(MySqlCommand mySqlCommand)
{
try
{
mySqlCommand.ExecuteNonQuery();
}
catch (Exception ex)
{
String message = ex.Message;
Console.WriteLine("刪除數(shù)據(jù)失敗了！" + message);
}
}
}
}
