asp.net mysql 操作_ASP.NET 操作MySQL数据库的方法说明

using System;

using System.Data;

using System.Diagnostics;

using System.Collections.Generic;

using MySql.Data.MySqlClient;

namespace System

{

///

/// Description of MySqlDBUtil.

///

public class MySqlDBUtil

{

private static String connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["DBConnection"].ConnectionString.ToString();

private MySqlDBUtil()

{

}

//执行单条插入语句，并返回id，不需要返回id的用ExceuteNonQuery执行。

public static int ExecuteInsert(string sql,MySqlParameter[] parameters)

{

//Debug.WriteLine(sql);

using (MySqlConnection connection = new MySqlConnection(connectionString))

{

MySqlCommand cmd = new MySqlCommand(sql, connection);

try

{

connection.Open();

if(parameters!=null)cmd.Parameters.AddRange(parameters);

cmd.ExecuteNonQuery();

cmd.CommandText = @"select LAST_INSERT_ID()";

int value = Int32.Parse(cmd.ExecuteScalar().ToString());

return value;

}

catch (Exception e)

{

throw e;

}

}

}

public static int ExecuteInsert(string sql)

{

return ExecuteInsert(sql,null);

}

//执行带参数的sql语句,返回影响的记录数(insert,update,delete)

public static int ExecuteNonQuery(string sql,MySqlParameter[] parameters)

{

//Debug.WriteLine(sql);

using (MySqlConnection connection = new MySqlConnection(connectionString))

{

MySqlCommand cmd = new MySqlCommand(sql, connection);

try

{

connection.Open();

if(parameters!=null) cmd.Parameters.AddRange(parameters);

int rows = cmd.ExecuteNonQuery();

return rows;

}

catch (Exception e)

{

throw e;

}

}

}

//执行不带参数的sql语句，返回影响的记录数

//不建议使用拼出来SQL

public static int ExecuteNonQuery(string sql)

{

return ExecuteNonQuery(sql,null);

}

//执行单条语句返回第一行第一列,可以用来返回count(*)

public static int ExecuteScalar(string sql,MySqlParameter[] parameters)

{

//Debug.WriteLine(sql);

using (MySqlConnection connection = new MySqlConnection(connectionString))

{

MySqlCommand cmd = new MySqlCommand(sql, connection);

try

{

connection.Open();

if(parameters!=null) cmd.Parameters.AddRange(parameters);

int value = Int32.Parse(cmd.ExecuteScalar().ToString());

return value;

}

catch (Exception e)

{

throw e;

}

}

}

public static int ExecuteScalar(string sql)

{

return ExecuteScalar(sql,null);

}

//执行查询语句，返回dataset

public static DataSet ExecuteQuery(string sql,MySqlParameter[] parameters)

{

//Debug.WriteLine(sql);

using (MySqlConnection connection = new MySqlConnection(connectionString))

{

DataSet ds = new DataSet();

try

{

connection.Open();

MySqlDataAdapter da = new MySqlDataAdapter(sql, connection);

if(parameters!=null) da.SelectCommand.Parameters.AddRange(parameters);

da.Fill(ds,"ds");

}

catch (Exception ex)

{

throw ex;

}

return ds;

}

}

public static DataSet ExecuteQuery(string sql)

{

return ExecuteQuery(sql,null);

}

///

/// 返回SQL语句执行结果的第一行第一列,比如取最大值

///

/// 字符串

public static string ReturnValue(string SQL)

{

MySqlConnection connection = new MySqlConnection(connectionString);

if (connection.State != ConnectionState.Open)

{

connection.Open();

}

string result;

MySqlDataReader Dr;

try

{

MySqlCommand cmd = new MySqlCommand(SQL, connection);

Dr = cmd.ExecuteReader();

if (Dr.Read())

{

result = Dr[0].ToString();

Dr.Close();

}

else

{

result = "";

Dr.Close();

}

connection.Close();

connection.Dispose();

}

catch

{

throw new Exception(SQL);

}

return result;

}

///

/// 运行SQL语句,返回DataSet对象,用法：先实例化一个DataSet，如 DataSet ds=new DataSet;然后MSQL.RunProc(SQL,ds,1,10,"test"),之后这个ds就可以直接用了

///

/// SQL语句

/// DataSet对象

/// 开始的页面，第一页是1

/// 每页显示的大小

/// 表名

///

public static DataSet RunProc(string SQL, DataSet Ds, int StartIndex, int PageSize, string tablename)

{

using (MySqlConnection connection = new MySqlConnection(connectionString))

{

try

{

connection.Open();

MySqlDataAdapter Da = new MySqlDataAdapter(SQL, connection);

Da.Fill(Ds, StartIndex, PageSize, tablename);

connection.Close();

}

catch (Exception ex)

{

throw ex;

}

return Ds;

}

}

///

/// 运行SQL语句,返回DataSet对象 用法：先实例化一个DataSet，如 DataSet ds=new DataSet;然后MSQL.RunProc(SQL,ds,"test"),之后这个ds就可以直接用了

///

/// SQL语句

/// DataSet对象

/// 表名

public static DataSet RunProc(string SQL, DataSet Ds, string tablename)

{

using (MySqlConnection connection = new MySqlConnection(connectionString))

{

try

{

connection.Open();

MySqlDataAdapter Da = new MySqlDataAdapter(SQL, connection);

Da.Fill(Ds, tablename);

connection.Close();

}

catch (Exception ex)

{

throw ex;

}

return Ds;

}

}

///

/// 运行SQL语句,返回DataSet对象 用法：先实例化一个DataSet，如 DataSet ds=new DataSet;然后MSQL.RunProc(SQL,ds,"test"),之后这个ds就可以直接用了

///

/// SQL语句

/// DataSet对象

public static DataSet RunProc(string SQL, DataSet Ds)

{

using (MySqlConnection connection = new MySqlConnection(connectionString))

{

try

{

connection.Open();

MySqlDataAdapter Da = new MySqlDataAdapter(SQL, connection);

Da.Fill(Ds);

connection.Close();

}

catch (Exception ex)

{

throw ex;

}

return Ds;

}

}

}

}

------------------------WEB.Config

-------------------------需要下载MySql.Data.dll