public class MySqlService
{
private static log4net.ILog logger = log4net.LogManager.GetLogger(typeof(MySqlService));
//server=localhost;user id=root;password=root;persist security info=True;database=test
//Data Source=127.0.0.1;port=3306;Initial Catalog=tsyw;user id=root;password=q2ii3sfc;Charset=gbk
private string conn_str = "server=localhost;user id=root;password=q2ii3sfc;persist security info=True;database=win008";
public MySqlService()
{
}
///
/// 构造函数
///
///
public MySqlService(string conn_str)
{
this.conn_str = conn_str;
}
///
/// 执行sql语句返回List-Hashtable数据集
///
///
///
public List Select(string sql)
{
List lst = new List();
using (MySqlConnection cOnnection= new MySqlConnection(this.conn_str))
{
try
{
connection.Open();
MySqlCommand cmd = new MySqlCommand(sql, connection);
MySqlDataReader mdr = cmd.ExecuteReader();
int columnCount = mdr.FieldCount;
string _key = string.Empty;
while (mdr.Read())
{
Hashtable ht = new Hashtable();
for(int i = 0; i
///
///
///
///
public System.Data.DataSet Query(string sql)
{
return null;
}
///
/// 执行操作语句 delete/insert/update
/// 返回影响行数
///
///
///
public int Execute(string sql)
{
int rest = 0;
using (MySqlConnection cOnnection= new MySqlConnection(this.conn_str))
{
try
{
connection.Open();
MySqlCommand cmd = new MySqlCommand(sql, connection);
rest = cmd.ExecuteNonQuery();
rest = (int)(cmd.LastInsertedId>0?cmd.LastInsertedId:rest);
connection.Close();
}
catch (Exception ex)
{
logger.Warn("执行操作语句异常, SQL:" + sql, ex);
}
}
return rest;
}
public Hashtable FetchRow(string sql)
{
Hashtable ht = new Hashtable();
using (MySqlConnection cOnnection= new MySqlConnection(this.conn_str))
{
try
{
connection.Open();
MySqlCommand cmd = new MySqlCommand(sql, connection);
MySqlDataReader mdr = cmd.ExecuteReader();
int columnCount = mdr.FieldCount;
string _key = string.Empty;
while (mdr.Read())
{
for (int i = 0; i
/// 获取语句第一行第一列数据
///
///
///
public T FetchFirst(string sql) {
T t;
using (MySqlConnection cOnnection= new MySqlConnection(this.conn_str))
{
try
{
connection.Open();
MySqlCommand cmd = new MySqlCommand(sql, connection);
t = (T)cmd.ExecuteScalar();
connection.Close();
}
catch (Exception ex)
{
t = default(T);
logger.Warn("执行操作语句异常, SQL:" + sql, ex);
}
}
return t;
}
///
/// 检查链接是否OK
///
///
public bool Ping()
{
bool rest = false;
using (MySqlConnection cOnnection= new MySqlConnection(this.conn_str))
{
try
{
connection.Open();
MySqlCommand cmd = new MySqlCommand("select 1", connection);
cmd.ExecuteScalar();
connection.Close();
rest = true;
}
catch
{
rest = false;
}
}
return rest;
}
}