using System;
using System.Data;
using System.Diagnostics;
using System.Collections.Generic;
using MySql.Data.MySqlClient;
namespace System
{
/// <summary>
/// Description of MySqlDBUtil.
/// </summary>
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);
}
/// <summary>
/// 返回SQL语句执行结果的第一行第一列,比如取最大值
/// </summary>
/// <returns>字符串</returns>
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;
}
/// <summary>
/// 运行SQL语句,返回DataSet对象,用法：先实例化一个DataSet，如 DataSet ds=new DataSet;然后MSQL.RunProc(SQL,ds,1,10,"test"),之后这个ds就可以直接用了
/// </summary>
/// <param name="SQL">SQL语句</param>
/// <param name="Ds">DataSet对象</param>
/// <param name="StartIndex">开始的页面，第一页是1</param>
/// <param name="PageSize">每页显示的大小</param>
/// <param name="tablename">表名</param>
/// <returns></returns>
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;
}
}
/// <summary>
/// 运行SQL语句,返回DataSet对象 用法：先实例化一个DataSet，如 DataSet ds=new DataSet;然后MSQL.RunProc(SQL,ds,"test"),之后这个ds就可以直接用了
/// </summary>
/// <param name="SQL">SQL语句</param>
/// <param name="Ds">DataSet对象</param>
/// <param name="tablename">表名</param>
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;
}
}
/// <summary>
/// 运行SQL语句,返回DataSet对象 用法：先实例化一个DataSet，如 DataSet ds=new DataSet;然后MSQL.RunProc(SQL,ds,"test"),之后这个ds就可以直接用了
/// </summary>
/// <param name="procName">SQL语句</param>
/// <param name="DataSet">DataSet对象</param>
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
<connectionStrings>
<add name="DBConnection" connectionString="server=127.0.0.1;user id=root; password=root; database=test; pooling=false;charset=gb2312;allow zero datetime=true"/>
</connectionStrings>