c#操作mysql类_C#操作MySQL的类

C#操作MySQL的类

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 < columnCount; i++)

{

_key = mdr.GetName(i);

ht[_key] = mdr[i];

}

lst.Add(ht);

}

mdr.Close();

connection.Close();

}

catch (Exception ex)

{

logger.Warn("执行操作语句异常, SQL:" + sql, ex);

}

}

return lst;

}

///

///

///

///

///

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 < columnCount; i++)

{

_key = mdr.GetName(i);

ht[_key] = mdr[i];

}

break;

}

mdr.Close();

connection.Close();

}

catch (Exception ex)

{

logger.Warn("执行操作语句异常, SQL:" + sql, ex);

}

}

return ht;

}

///

/// 获取语句第一行第一列数据

///

///

///

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;

}

}

C#操作MySQL的类

[C#cāozuò MySQL de lèi]

C# operation MySQL class