mysql几种使用代码

1. Reader

/// <summary>

/// 获取数据量不为0的产品线名称列表

/// </summary>

/// <returns></returns>

public static List<string> GetPrductLineWithData()

{

List<string> pnames = new List<string>();

string cmd = "SELECT DISTINCT (path) FROM stat WHERE size >0 ORDER BY size DESC";

// 执行查询语句

MySqlCommand DBComm;

DBComm = new MySqlCommand(cmd, GetConnection());

// 读取数据

MySqlDataReader DBReader = DBComm.ExecuteReader();

// 显示数据

try

{

while (DBReader.Read())

{

pnames.Add(DBReader.GetString(0)); //获取名称

}

}

finally

{

DBReader.Close();

CloseDatabase();

}

return pnames;

}

2. dataset

/// <summary>

/// 只包括今天和开始日期开始当前周开始每周一的数据量

/// </summary>

/// <param name="start">开始日期</param>

/// <returns></returns>

public static DataSet GetStatByWeek(DateTime start)

{

//得到一周的周一

int t = (int)start.DayOfWeek;

start = start.AddDays(-t + 1);

string sql = "SELECT * FROM dmpstat WHERE DATE(DATE) >='" + start.ToString("yyyy-MM-dd", DateTimeFormatInfo.InvariantInfo) + "'";

MySqlCommand cmd = new MySqlCommand(sql, GetConnection());

DataSet ds = new DataSet();

try

{

MySqlDataAdapter da = new MySqlDataAdapter(cmd);

da.Fill(ds);

}

catch (Exception ex)

{

ds = null;

}

finally

{

CloseDatabase();

}

return ds;

}

3. 链接数据库，关闭数据库

private static string constr = "User Id=**;Host=***;Database=**;password=**";

private static MySqlConnection DBConn = null;

/// <summary>

/// 获取活跃的数据库链接

/// </summary>

/// <returns></returns>

public static MySqlConnection GetConnection()

{

try

{

if (DBConn == null)

{

DBConn = new MySqlConnection(constr);

DBConn.Open();

}

else if (DBConn.State == System.Data.ConnectionState.Closed || DBConn.State == System.Data.ConnectionState.Broken)

{

DBConn.Open();

}

return DBConn;

}

catch (Exception)

{

throw new Exception("无法正常连接数据库，请检查配置");

}

}

/// <summary>

/// 关闭数据库

/// </summary>

public static void CloseDatabase()

{

try

{

//关闭数据库连接

if (DBConn != null && DBConn.State != System.Data.ConnectionState.Closed)

DBConn.Close();

}catch( Exception)

{

throw new Exception("无法关闭数据库连接");

}

}