protected void Page_Load(object sender, EventArgs e)
{
string sql0 = @"SELECT type ,SesonName, HardwareName
FROM HardwareInfo
GROUP BY SesonName, type
ORDER BY TYPE ASC";
DataTable dt0 = DBABase.QueryDBA(sql0);
dropliststr = JsonConvert.SerializeObject(dt0);
string sql = @"SELECT DISTINCT type FROM HardwareInfo";
DataTable dt = DBABase.QueryDBA(sql);
this.DropDownList1.DataSource = dt;
this.DropDownList1.DataTextField = "type";
this.DropDownList1.DataValueField = "type";
this.DropDownList1.DataBind();
string sql2 = @"SELECT DISTINCT SesonName FROM HardwareInfo where type='" + dt.Rows[0][0] + "'";
DataTable dt2 = DBABase.QueryDBA(sql2);
this.DropDownList2.DataSource = dt2;
this.DropDownList2.DataTextField = "SesonName";
this.DropDownList2.DataValueField = "SesonName";
this.DropDownList2.DataBind();
}
/// <summary>
/// 创建并打开connection
/// </summary>
private OracleConnection ConnenctOpen()
{
if (!string.IsNullOrEmpty(ConnectionStr))
{
try
{
if (OrclConnect != null)
{
OrclConnect.Close();
OrclConnect.Dispose();
OrclConnect = null;
}
//var orclcon = new OracleConnection(ConnectionStr);
var orclcon = DBABase.GetConnection <OracleConnection>("");
OrclConnect = orclcon;
if (OrclConnect.State != ConnectionState.Open)
{
OrclConnect.Open();
}
}
catch (Exception ex)
{
throw ex;
}
return(OrclConnect);
}
throw new Exception("数据库连接字符串未设置！");
}
public static preJson <TableRes> GetData(int getCount, string Type, string Name)
{
try
{
//Temperature
//CPU Package
string sql = @"select * from
(SELECT t1.ID, t2.Identifier, t2.SesonName, t2.HardwareName, t2.type, t1.Value, t1.InsertTime FROM 'SesonsLogs' t1, 'HardwareInfo' t2
where t1.HardwareInfoID = t2.ID
and t2.type = @TYPE and t2.SesonName = @NAME
order by t1.InsertTime desc
LIMIT 0, @getCount )
order by InsertTime asc";
List <SQLiteParameter> ps = new List <SQLiteParameter>();
ps.Add(new SQLiteParameter("getCount", getCount));
ps.Add(new SQLiteParameter("TYPE", Type));
ps.Add(new SQLiteParameter("NAME", Name));
DataTable dt = DBABase.QueryDBA(sql, ps);
preJson <TableRes> r = new preJson <TableRes>(dt);
r.DataLenght = (UInt64)dt.Rows.Count;
r.StartTime = ConvertDef.GetTime(dt.Rows[0]["InsertTime"].ToString());
string minandmax = ConfigurationSettings.AppSettings[Type + "+" + "*"];
if (!String.IsNullOrWhiteSpace(minandmax))
{
string[] sArray = minandmax.Split(',');
r.ShowMIN = Convert.ToSingle(sArray[0]);
r.ShowMAX = Convert.ToSingle(sArray[1]);
r.ShowUnit = sArray[2];
}
minandmax = ConfigurationSettings.AppSettings[Type + "+" + Name];
if (!String.IsNullOrWhiteSpace(minandmax))
{
string[] sArray = minandmax.Split(',');
r.ShowMIN = Convert.ToSingle(sArray[0]);
r.ShowMAX = Convert.ToSingle(sArray[1]);
r.ShowUnit = sArray[2];
}
return(r);
}
catch { return(null); }
}
/// <summary>
/// 查询 返回dataset 注意不同数据库的sql不太一样
/// </summary>
/// <param name="SQL">查询sql</param>
/// <returns>dataset</returns>
public DataSet DBSelectDS(string SQL)
{
var dataSet = new DataSet();
if (!string.IsNullOrEmpty(SQL) && DBABase != null)
{
DBTypeEnum.DBType dbtype = (DBTypeEnum.DBType)DBTypeValue;
switch (dbtype)
{
case DBTypeEnum.DBType.Oracle:
var tupOracle = DBABase.GetOracleConnnect(SQL);
using (var connection = tupOracle.Item1)
{
try
{
var cmd = tupOracle.Item2;
var adapt = tupOracle.Item3;
adapt.SelectCommand = cmd;
adapt.Fill(dataSet);
connection.Close();
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (connection.State != ConnectionState.Closed)
{
connection.Close();
}
}
}
break;
case DBTypeEnum.DBType.Access:
var tupAccess = DBABase.GetAccessConnnect(SQL);
using (var connection = tupAccess.Item1)
{
try
{
var cmd = tupAccess.Item2;
var adapt = tupAccess.Item3;
adapt.SelectCommand = cmd;
adapt.Fill(dataSet);
connection.Close();
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (connection.State != ConnectionState.Closed)
{
connection.Close();
}
}
}
break;
case DBTypeEnum.DBType.SqlServer:
var tupSqlServer = DBABase.GetSqlServerConnnect(SQL);
using (var connection = tupSqlServer.Item1)
{
try
{
var cmd = tupSqlServer.Item2;
var adapt = tupSqlServer.Item3;
adapt.SelectCommand = cmd;
adapt.Fill(dataSet);
connection.Close();
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (connection.State != ConnectionState.Closed)
{
connection.Close();
}
}
}
break;
case DBTypeEnum.DBType.Sqlite:
var tupSqlite = DBABase.GetSqliteConnnect(SQL);
using (var connection = tupSqlite.Item1)
{
try
{
var cmd = tupSqlite.Item2;
var adapt = tupSqlite.Item3;
adapt.SelectCommand = cmd;
adapt.Fill(dataSet);
connection.Close();
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (connection.State != ConnectionState.Closed)
{
connection.Close();
}
}
}
break;
}
}
return(dataSet);
}
/// <summary>
/// 执行多条sql语句或特殊语句
/// </summary>
/// <param name="SQL">sql脚本</param>
/// <param name="IsTransAction">是否开启事务 默认true</param>
/// <returns>受影响条数</returns>
public int DBOther(string SQL, bool IsTransAction = true)
{
var num = 0;
if (!string.IsNullOrEmpty(SQL) && DBABase != null)
{
DBTypeEnum.DBType dbtype = (DBTypeEnum.DBType)DBTypeValue;
switch (dbtype)
{
case DBTypeEnum.DBType.Oracle:
var tupOracle = DBABase.GetOracleConnnect(SQL);
using (var connection = tupOracle.Item1)
{
try
{
var cmd = tupOracle.Item2;
var adapt = tupOracle.Item3;
if (IsTransAction)
{
using (OracleTransaction orclTrans = connection.BeginTransaction(IsolationLevel.ReadCommitted))
{
try
{
num = cmd.ExecuteNonQuery();
orclTrans.Commit();
}
catch (Exception ex)
{
orclTrans.Rollback();
throw ex;
}
}
}
else
{
num = cmd.ExecuteNonQuery();
}
connection.Close();
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (connection.State != ConnectionState.Closed)
{
connection.Close();
}
}
}
break;
case DBTypeEnum.DBType.Access:
var tupAccess = DBABase.GetAccessConnnect(SQL);
using (var connection = tupAccess.Item1)
{
try
{
var cmd = tupAccess.Item2;
var adapt = tupAccess.Item3;
if (IsTransAction)
{
using (OleDbTransaction orclTrans = connection.BeginTransaction(IsolationLevel.ReadCommitted))
{
try
{
num = cmd.ExecuteNonQuery();
orclTrans.Commit();
}
catch (Exception ex)
{
orclTrans.Rollback();
throw ex;
}
}
}
else
{
num = cmd.ExecuteNonQuery();
}
connection.Close();
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (connection.State != ConnectionState.Closed)
{
connection.Close();
}
}
}
break;
case DBTypeEnum.DBType.SqlServer:
var tupSqlServer = DBABase.GetSqlServerConnnect(SQL);
using (var connection = tupSqlServer.Item1)
{
try
{
var cmd = tupSqlServer.Item2;
var adapt = tupSqlServer.Item3;
if (IsTransAction)
{
using (SqlTransaction orclTrans = connection.BeginTransaction(IsolationLevel.ReadCommitted))
{
try
{
num = cmd.ExecuteNonQuery();
orclTrans.Commit();
}
catch (Exception ex)
{
orclTrans.Rollback();
throw ex;
}
}
}
else
{
num = cmd.ExecuteNonQuery();
}
connection.Close();
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (connection.State != ConnectionState.Closed)
{
connection.Close();
}
}
}
break;
case DBTypeEnum.DBType.Sqlite:
var tupSqlite = DBABase.GetSqliteConnnect(SQL);
using (var connection = tupSqlite.Item1)
{
try
{
var cmd = tupSqlite.Item2;
var adapt = tupSqlite.Item3;
if (IsTransAction)
{
using (SQLiteTransaction orclTrans = connection.BeginTransaction(IsolationLevel.ReadCommitted))
{
try
{
num = cmd.ExecuteNonQuery();
orclTrans.Commit();
}
catch (Exception ex)
{
orclTrans.Rollback();
throw ex;
}
}
}
else
{
num = cmd.ExecuteNonQuery();
}
connection.Close();
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (connection.State != ConnectionState.Closed)
{
connection.Close();
}
}
}
break;
}
}
return(num);
}
/// <summary>
/// 更新 返回受影响行数 注意不同数据库的sql不太一样
/// </summary>
/// <param name="SQL">更新SQL语句</param>
/// <returns>更新条数</returns>
public int DBUpdata(string SQL)
{
var num = 0;
if (!string.IsNullOrEmpty(SQL) && DBABase != null)
{
DBTypeEnum.DBType dbtype = (DBTypeEnum.DBType)DBTypeValue;
switch (dbtype)
{
case DBTypeEnum.DBType.Oracle:
var tupOracle = DBABase.GetOracleConnnect(SQL);
using (var connection = tupOracle.Item1)
{
try
{
var cmd = tupOracle.Item2;
var adapt = tupOracle.Item3;
num = cmd.ExecuteNonQuery();
connection.Close();
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (connection.State != ConnectionState.Closed)
{
connection.Close();
}
}
}
break;
case DBTypeEnum.DBType.Access:
var tupAccess = DBABase.GetAccessConnnect(SQL);
using (var connection = tupAccess.Item1)
{
try
{
var cmd = tupAccess.Item2;
var adapt = tupAccess.Item3;
num = cmd.ExecuteNonQuery();
connection.Close();
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (connection.State != ConnectionState.Closed)
{
connection.Close();
}
}
}
break;
case DBTypeEnum.DBType.SqlServer:
var tupSqlServer = DBABase.GetSqlServerConnnect(SQL);
using (var connection = tupSqlServer.Item1)
{
try
{
var cmd = tupSqlServer.Item2;
var adapt = tupSqlServer.Item3;
num = cmd.ExecuteNonQuery();
connection.Close();
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (connection.State != ConnectionState.Closed)
{
connection.Close();
}
}
}
break;
case DBTypeEnum.DBType.Sqlite:
var tupSqlite = DBABase.GetSqliteConnnect(SQL);
using (var connection = tupSqlite.Item1)
{
try
{
var cmd = tupSqlite.Item2;
var adapt = tupSqlite.Item3;
num = cmd.ExecuteNonQuery(); //如果有返回条数，但数据库数据为变化，需要将SQLite.Interop.066.DLL复制到输出目录
connection.Close();
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (connection.State != ConnectionState.Closed)
{
connection.Close();
}
}
}
break;
}
}
return(num);
}
