ado mysql_以ADO形式操作mysql数据库

首先得需要一个连接mysql的helper类：

public class MySqlHelper

{

#region [ Connection ]

public static string connectionString = "Database=newspublish;Data Source=127.0.0.1;Port=3306;User Id=root;Password=123456;Charset=utf8;SslMode=none;";

public static MySqlConnection GetConnection

{

get

{

return new MySqlConnection(connectionString);

}

}

#endregion

#region [ ExecuteNonQuery ]

///

/// 普通SQL语句执行增删改

///

/// SQL语句

/// 可变参数

/// 受影响行数

public static int ExecuteNonQuery(string cmdText, params MySqlParameter[] commandParameters)

{

return ExecuteNonQuery(cmdText, CommandType.Text, commandParameters);

}

///

/// 存储过程执行增删改

///

/// 存储过程

/// 可变参数

/// 受影响行数

public static int ExecuteNonQueryByProc(string cmdText, params MySqlParameter[] commandParameters)

{

return ExecuteNonQuery(cmdText, CommandType.StoredProcedure, commandParameters);

}

///

/// 执行增删改

///

/// 命令字符串

/// 命令类型

/// 可变参数

/// 受影响行数

public static int ExecuteNonQuery(string cmdText, CommandType cmdType, params MySqlParameter[] commandParameters)

{

int result = 0;

using (MySqlConnection conn = GetConnection)

{

try

{

MySqlCommand command = new MySqlCommand();

PrepareCommand(command, conn, cmdType, cmdText, commandParameters);

result = command.ExecuteNonQuery();

}

catch (Exception ex)

{

throw new Exception(ex.Message);

}

finally

{

if (conn != null && conn.State != ConnectionState.Closed)

conn.Close();

}

}

return result;

}

#endregion

#region [ ExecuteReader ]

///

/// SQL语句得到 MySqlDataReader 对象

///

/// 命令字符串

/// 可变参数

/// MySqlDataReader 对象

public static MySqlDataReader ExecuteReader(string cmdText, params MySqlParameter[] commandParameters)

{

return ExecuteReader(cmdText, CommandType.Text, commandParameters);

}

///

/// 存储过程得到 MySqlDataReader 对象

///

/// 命令字符串

/// 可变参数

/// MySqlDataReader 对象

public static MySqlDataReader ExecuteReaderByProc(string cmdText, params MySqlParameter[] commandParameters)

{

return ExecuteReader(cmdText, CommandType.StoredProcedure, commandParameters);

}

///

/// 得到 MySqlDataReader 对象

///

/// 命令字符串

/// 命令类型

/// 可变参数

/// MySqlDataReader 对象

public static MySqlDataReader ExecuteReader(string cmdText, CommandType cmdType, params MySqlParameter[] commandParameters)

{

MySqlDataReader result = null;

using (MySqlConnection conn = GetConnection)

{

try

{

MySqlCommand command = new MySqlCommand();

PrepareCommand(command, conn, cmdType, cmdText, commandParameters);

result = command.ExecuteReader(CommandBehavior.CloseConnection);

}

catch (Exception ex)

{

throw new Exception(ex.Message);

}

finally

{

if (conn != null && conn.State != ConnectionState.Closed)

conn.Close();

}

}

return result;

}

#endregion

#region [ ExecuteDataSet ]

///

/// 执行SQL语句, 返回DataSet

///

/// 命令字符串

/// 可变参数

/// DataSet

public static DataSet ExecuteDataSet(string cmdText, params MySqlParameter[] commandParameters)

{

return ExecuteDataSet(cmdText, CommandType.Text, commandParameters);

}

///

/// 执行存储过程, 返回DataSet

///

/// 命令字符串

/// 可变参数

/// DataSet

public static DataSet ExecuteDataSetByProc(string cmdText, params MySqlParameter[] commandParameters)

{

return ExecuteDataSet(cmdText, CommandType.StoredProcedure, commandParameters);

}

///

/// 返回DataSet

///

/// 命令字符串

/// 命令类型

/// 可变参数

/// DataSet

public static DataSet ExecuteDataSet(string cmdText, CommandType cmdType, params MySqlParameter[] commandParameters)

{

DataSet result = null;

using (MySqlConnection conn = GetConnection)

{

try

{

MySqlCommand command = new MySqlCommand();

PrepareCommand(command, conn, cmdType, cmdText, commandParameters);

MySqlDataAdapter adapter = new MySqlDataAdapter();

adapter.SelectCommand = command;

result = new DataSet();

adapter.Fill(result);

}

catch (Exception ex)

{

throw new Exception(ex.Message);

}

finally

{

if (conn != null && conn.State != ConnectionState.Closed)

conn.Close();

}

}

return result;

}

#endregion

#region [ ExecuteDataTable ]

///

/// 执行SQL语句, 返回DataTable

///

/// 命令字符串

/// 可变参数

/// DataTable

public static DataTable ExecuteDataTable(string cmdText, params MySqlParameter[] commandParameters)

{

return ExecuteDataTable(cmdText, CommandType.Text, commandParameters);

}

///

/// 执行存储过程, 返回DataTable

///

/// 命令字符串

/// 可变参数

/// DataTable

public static DataTable ExecuteDataTableByProc(string cmdText, params MySqlParameter[] commandParameters)

{

return ExecuteDataTable(cmdText, CommandType.StoredProcedure, commandParameters);

}

///

/// 返回DataTable

///

/// 命令字符串

/// 命令类型

/// 可变参数

/// DataTable

public static DataTable ExecuteDataTable(string cmdText, CommandType cmdType, params MySqlParameter[] commandParameters)

{

DataTable dtResult = null;

DataSet ds = ExecuteDataSet(cmdText, cmdType, commandParameters);

if (ds != null && ds.Tables.Count > 0)

{

dtResult = ds.Tables[0];

}

return dtResult;

}

#endregion

#region [ ExecuteScalar ]

///

/// 普通SQL语句执行ExecuteScalar

///

/// SQL语句

/// 可变参数

/// 受影响行数

public static object ExecuteScalar(string cmdText, params MySqlParameter[] commandParameters)

{

return ExecuteScalar(cmdText, CommandType.Text, commandParameters);

}

///

/// 存储过程执行ExecuteScalar

///

/// 存储过程

/// 可变参数

/// 受影响行数

public static object ExecuteScalarByProc(string cmdText, params MySqlParameter[] commandParameters)

{

return ExecuteScalar(cmdText, CommandType.StoredProcedure, commandParameters);

}

///

/// 执行ExecuteScalar

///

/// 命令字符串

/// 命令类型

/// 可变参数

/// 受影响行数

public static object ExecuteScalar(string cmdText, CommandType cmdType, params MySqlParameter[] commandParameters)

{

object result = null;

using (MySqlConnection conn = GetConnection)

{

try

{

MySqlCommand command = new MySqlCommand();

PrepareCommand(command, conn, cmdType, cmdText, commandParameters);

result = command.ExecuteScalar();

}

catch (Exception ex)

{

throw new Exception(ex.Message);

}

finally

{

if (conn != null && conn.State != ConnectionState.Closed)

conn.Close();

}

}

return result;

}

#endregion

#region [ PrepareCommand ]

///

/// Command对象执行前预处理

///

///

///

///

///

///

///

private static void PrepareCommand(MySqlCommand command, MySqlConnection connection, CommandType cmdType, string cmdText, MySqlParameter[] commandParameters)

{

try

{

if (connection.State != ConnectionState.Open) connection.Open();

command.Connection = connection;

command.CommandText = cmdText;

command.CommandType = cmdType;

//command.CommandTimeout = 3600; //此处请自定义

//if (trans != null) command.Transaction = trans;

if (commandParameters != null)

{

foreach (MySqlParameter parm in commandParameters)

command.Parameters.Add(parm);

}

}

catch

{

}

}

#endregion

}

新建控制台应用程序：

static void Main(string[] args)

{

//string sql = "select title from news where id = 10";/*查询*/

//string result = (string)MySqlHelper.ExecuteScalar(sql);

//string sql = "INSERT INTO news(title,content,typeid) values('111','222',6)";/*新增*/

//string sql = "UPDATE news SET title = '666' WHERE id = 11";/*更新*/

//string sql = "DELETE FROM news WHERE id = 11";/*删除*/

//int i = MySqlHelper.ExecuteNonQuery(sql);

//Console.WriteLine(i);

Console.Read();

}