c#连接mysql数据库查询语句_C#连接MySQL，并完成对数据库的增删查改

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)

{

//数据库名为test1

//   String mysqlStr = "Database=自己的数据库名;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 插入数据

String sqlinsert = "insert into account values(5,'pyq','pyq')";

//MySql的命令类

//查询语句

MySqlCommand mySqlCommand = new MySqlCommand(sqlSearch, mysql);

//插入数据

// MySqlCommand mySqlCommand = new MySqlCommand(sqlinsert, mysql);

//打开连接

mysql.Open();

//建立流

//数据读取类

MySqlDataReader reader = mySqlCommand.ExecuteReader();

try

{//每次读取一个字节

while (reader.Read())

{

if (reader.HasRows)//是否读取玩一行

{

//从数据库读取的数据要进行类型转换

//0,1,2,表示在数据库表中的位置

Console.WriteLine("ID:" + reader.GetInt32(0) + "|账号:" + reader.GetString(1) + "|密码:" + reader.GetString(2));

}

}

}

catch (Exception)

{

Console.WriteLine("查询失败了！");

}

finally

{

reader.Close();//关闭流

}

mysql.Close();

Console.Read();

}

///

/// 建立mysql数据库链接

///

///

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;

}

///

/// 建立执行命令语句对象

///

///

///

///

public static MySqlCommand getSqlCommand(String sql, MySqlConnection mysql)

{

MySqlCommand mySqlCommand = new MySqlCommand(sql, mysql);

//  MySqlCommand mySqlCommand = new MySqlCommand(sql);

// mySqlCommand.Connection = mysql;

return mySqlCommand;

}

///

/// 查询并获得结果集并遍历

///

///

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

}

}

///

/// 添加数据

///

///

public static void getInsert(MySqlCommand mySqlCommand)

{

try

{

mySqlCommand.ExecuteNonQuery();

}

catch (Exception ex)

{

String message = ex.Message;

Console.WriteLine("插入数据失败了！" + message);

}

}

///

/// 修改数据

///

///

public static void getUpdate(MySqlCommand mySqlCommand)

{

try

{

mySqlCommand.ExecuteNonQuery();

}

catch (Exception ex)

{

String message = ex.Message;

Console.WriteLine("修改数据失败了！" + message);

}

}

///

/// 删除数据

///

///

public static void getDel(MySqlCommand mySqlCommand)

{

try

{

mySqlCommand.ExecuteNonQuery();

}

catch (Exception ex)

{

String message = ex.Message;

Console.WriteLine("删除数据失败了！" + message);

}

}

}

}