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("无法关闭数据库连接");
}
}
