public DataTable QueryDataTable(string DBName)
{
string strsql;
if (_isQueryTotalCounts)
{
_TotalCount = GetTotalCount(DBName);
}
if (_TotalCount == 0)
{
_PageIndex = 1;
_PageCount = 1;
strsql = "select * from " + TableName;
return(OleDbHelper.ExecuteDataTable(DBName, strsql));
}
else
{
_PageCount = _TotalCount % _PageSize == 0 ? _TotalCount / _PageSize : _TotalCount / _PageSize + 1;
if (_PageIndex > _PageCount)
{
_PageIndex = _PageCount;
}
}
//利用MAX函数这种方法在大数据量测试中表现出较高的效率，稳定性最优。
//在查询第1页时，SQL语句的构成公式不适用，因为 "SELECT TOP 0 ..." 会报错，需要单独处理： SELECT TOP [PageNum] * FROM [TableName]可以使用。
strsql = "select top " + PageSize + QueryFieldName + " from " + TableName + " where " + PrimaryKey + " < ((select max(" + PrimaryKey + ") from " + TableName + ") - ("
+ (PageIndex - 1) * PageSize + "-1)) order by " + PrimaryKey + " desc ";
return(OleDbHelper.ExecuteDataTable(DBName, strsql));
}
public DataTable GetZonesDataTable(string account)
{
DataTable dt = OleDbHelper.ExecuteDataTable("Main", "select * from 防区资料 where 主机编号=@account",
new OleDbParameter("@account", account));
return(dt);
}
public CID GetEventInformation(string isNewEvent, string CID)
{
if (isNewEvent == "E")
{
IsNewEvent = true;
}
else
{
IsNewEvent = false;
}
DataTable dt = OleDbHelper.ExecuteDataTable("Main", "select * from [DRC_CONTACTID] where CID码=@ID AND 是否是新事件=@IsNewEvent",
new OleDbParameter("@ID", CID), new OleDbParameter("@IsNewEvent", IsNewEvent));
if (dt.Rows.Count <= 0)
{
return(null);
}
else if (dt.Rows.Count > 1)
{
throw new Exception("严重错误，查出多条数据！");
}
else
{
DataRow row = dt.Rows[0];
return(ToCID(row));
}
}
public SystemManager[] ListAll()
{
DataTable dt = OleDbHelper.ExecuteDataTable("Main", "select * from [值班人员]");
SystemManager[] systemmanagers = new SystemManager[dt.Rows.Count];
for (int i = 0; i < dt.Rows.Count; i++)
{
systemmanagers[i] = ToSystemManager(dt.Rows[i]);
}
return(systemmanagers);
}
/// <summary>
/// 获取所有用户资料的主机编号
/// </summary>
/// <returns></returns>
public String[] GetaccountArray()
{
DataTable table = OleDbHelper.ExecuteDataTable("Main", "select " + " 主机编号 " + " from [用户资料]");
String[] accountArray = new String[table.Rows.Count];
for (int i = 0; i < table.Rows.Count; i++)
{
DataRow row = table.Rows[i];
accountArray[i] = (string)OleDbHelper.FromDbValue(row["主机编号"]);
}
return(accountArray);
}
/// <summary>
/// 获取策略对象数组
/// </summary>
/// <param name="account"></param>
/// <returns></returns>
public Strategies[] GetStrategiesArray()
{
DataTable dt = OleDbHelper.ExecuteDataTable("Main", "select * from 处理策略 ");
Strategies[] strategiess = new Strategies[dt.Rows.Count];
for (int i = 0; i < dt.Rows.Count; i++)
{
DataRow row = dt.Rows[i];
strategiess[i] = ToStrategies(row);
}
return(strategiess);
}
/// <summary>
/// 获取数据库表名得到对象数组
/// </summary>
/// <param name="formName"></param>
/// <returns></returns>
public CID[] GetReportFormatArray(string formName)
{
DataTable dt = OleDbHelper.ExecuteDataTable("Main", "select * from " + formName);
CID[] cids = new CID[dt.Rows.Count];
for (int i = 0; i < dt.Rows.Count; i++)
{
DataRow row = dt.Rows[i];
cids[i] = ToCID(row);
}
return(cids);
}
/// <summary>
/// 获取一个用户的所有防区资料，返回一个防区对象数组
/// </summary>
/// <param name="account"></param>
/// <returns></returns>
public Contacts[] GetContactsArray(string account)
{
DataTable dt = OleDbHelper.ExecuteDataTable("Main", "select * from [联系人资料] where 主机编号=@account",
new OleDbParameter("@account", account));
Contacts[] contactss = new Contacts[dt.Rows.Count];
for (int i = 0; i < dt.Rows.Count; i++)
{
DataRow row = dt.Rows[i];
contactss[i] = ToContacts(row);
}
return(contactss);
}
/// <summary>
/// 获取一个用户的所有防区资料，返回一个防区对象数组
/// </summary>
/// <param name="account"></param>
/// <returns></returns>
public Zone[] GetZonesArray(string account)
{
DataTable dt = OleDbHelper.ExecuteDataTable("Main", "select * from 防区资料 where 主机编号=@account",
new OleDbParameter("@account", account));
Zone[] zones = new Zone[dt.Rows.Count];
for (int i = 0; i < dt.Rows.Count; i++)
{
DataRow row = dt.Rows[i];
zones[i] = ToZone(row);
}
return(zones);
}
/// <summary>
/// 根据主机编号获取该用户信息
/// </summary>
/// <param name="account"></param>
/// <returns></returns>
public DataTable GetGroupItems(string group)
{
DataTable dt = OleDbHelper.ExecuteDataTable("Main", "select * from [可选项及内容] where 组=@group ",
new OleDbParameter("@group", group));
if (dt.Rows.Count <= 0)
{
return(null);
}
else
{
return(dt);
}
}
public CID GetReportFormatByID(string tableName, int id)
{
DataTable dt = OleDbHelper.ExecuteDataTable("Main", "select * from " + tableName + " where 序号=" + id.ToString());
if (dt.Rows.Count <= 0)
{
return(null);
}
else if (dt.Rows.Count > 1)
{
throw new Exception("严重错误，查出多条数据！");
}
else
{
DataRow row = dt.Rows[0];
return(ToCID(row));
}
}
public Event GetCurrentEventOnlyOne()
{
DataTable dt = OleDbHelper.ExecuteDataTable("Event", "select " + queryFieldName + " from [报警事件] where 序号= (select max(序号) from 报警事件) ");
if (dt.Rows.Count <= 0)
{
return(null);
}
else if (dt.Rows.Count > 1)
{
throw new Exception("严重错误，查出多条数据！");
}
else
{
DataRow row = dt.Rows[0];
return(ToEvent(row));
}
}
public SystemManager GetByUserName(string userName)
{
DataTable table = OleDbHelper.ExecuteDataTable("Main", "select * from [值班人员] where [值班员代码]=@UserName",
new OleDbParameter("@UserName", userName));
if (table.Rows.Count <= 0)
{
return(null);
}
else if (table.Rows.Count > 1)
{
throw new Exception("存在重名用户！");
}
else
{
return(ToSystemManager(table.Rows[0]));
}
}
/// <summary>
/// 根据防区表序号字段获取该防区资料
/// </summary>
/// <param name="account"></param>
/// <returns></returns>
public Contacts GetContactsByID(int contactsID)
{
DataTable dt = OleDbHelper.ExecuteDataTable("Main", "select * from [联系人资料] where 序号=@contactsID",
new OleDbParameter("@contactsID", contactsID));
if (dt.Rows.Count <= 0)
{
return(null);
}
else if (dt.Rows.Count > 1)
{
throw new Exception("严重错误，查出多条数据！");
}
else
{
DataRow row = dt.Rows[0];
return(ToContacts(row));
}
}
/// <summary>
/// 根据防区表序号字段获取该防区资料
/// </summary>
/// <param name="account"></param>
/// <returns></returns>
public Zone GetZoneByID(int zoneID)
{
DataTable dt = OleDbHelper.ExecuteDataTable("Main", "select * from 防区资料 where 序号=@zoneID",
new OleDbParameter("@zoneID", zoneID));
if (dt.Rows.Count <= 0)
{
return(NullZone());
}
else if (dt.Rows.Count > 1)
{
throw new Exception("严重错误，查出多条数据！");
}
else
{
DataRow row = dt.Rows[0];
return(ToZone(row));
}
}
/// <summary>
/// 根据主机编号和防区编号获取该用户的防区信息
/// </summary>
/// <param name="account"></param>
/// <returns></returns>
public Zone GetZoneByAccountAddZoneNum(string account, int zoneNum)
{
DataTable dt = OleDbHelper.ExecuteDataTable("Main", "select * from 防区资料 where 主机编号=@account AND 防区编号=@ZoneNum",
new OleDbParameter("@account", account), new OleDbParameter("@ZoneNum", zoneNum));
if (dt.Rows.Count <= 0)
{
return(NullZone());
}
else if (dt.Rows.Count > 1)
{
throw new Exception("严重错误，查出多条数据！");
}
else
{
DataRow row = dt.Rows[0];
return(ToZone(row));
}
}
public Strategies GetStrategiesByStrategiesName(string strategiesID)
{
DataTable dt = OleDbHelper.ExecuteDataTable("Main", "select * from 处理策略 where 策略名称=@strategiesID",
new OleDbParameter("@strategiesID", strategiesID));
if (dt.Rows.Count <= 0)
{
return(null);
}
else if (dt.Rows.Count > 1)
{
throw new Exception("严重错误，查出多条数据！");
}
else
{
DataRow row = dt.Rows[0];
return(ToStrategies(row));
}
}
public User GetByID(int id)
{
DataTable dt = OleDbHelper.ExecuteDataTable("Main", "select * from [用户资料] where 序号=@ID",
new OleDbParameter("@ID", id));
if (dt.Rows.Count <= 0)
{
return(null);
}
else if (dt.Rows.Count > 1)
{
throw new Exception("严重错误，查出多条数据！");
}
else
{
DataRow row = dt.Rows[0];
return(ToUser(row));
}
}
/// <summary>
/// 根据主机编号获取该用户信息
/// </summary>
/// <param name="account"></param>
/// <returns></returns>
public User GetUserInfomation(string account, string queryFieldName)
{
DataTable dt = OleDbHelper.ExecuteDataTable("Main", "select " + queryFieldName + " from [用户资料] where 主机编号=@account",
new OleDbParameter("@account", account));
if (dt.Rows.Count <= 0)
{
return(NullUser());
}
else if (dt.Rows.Count > 1)
{
throw new Exception("严重错误，查出多条数据！");
}
else
{
DataRow row = dt.Rows[0];
return(ToUserInfomation(row));
}
}
//public DataTable GetEventsDataTable(string DBName, string tableName, int pageSize, int pageIndex)
//{
// dataPage.PageSize = pageSize;
// dataPage.TableName = tableName;
// dataPage.PageIndex = pageIndex;
// dataPage.QueryFieldName = " 序号,报警时间,用户名称,用户地址,主机编号,用户类型,主机类型,分区编号,防区编号,防区类型,探测器型号,安装位置,事件类型,辅助信息,归类处理,通讯代码,处理内容,值班员姓名,事件字体颜色,事件背景颜色,来电号码,站点编号,二级站点 ";
// dataPage.OrderStr = "[序号]";
// dataPage.PrimaryKey = "[序号]";
// DataTable table = dataPage.QueryDataTable(DBName);
// pageCount = dataPage.PageCount;
// return table;
//}
public DataTable GetEventsDataTable(string dbName, string tableName, string queryFieldName)
{
DataTable dt = OleDbHelper.ExecuteDataTable(dbName, "select " + queryFieldName + " from " + tableName + " order by [报警时间] desc");
return(dt);
}
/// <summary>
/// 获取处理策略资料
/// </summary>
/// <param name="account"></param>
/// <returns></returns>
public DataTable GetStrategiesDataTable(string account)
{
DataTable dt = OleDbHelper.ExecuteDataTable("Main", "select * from 处理策略");
return(dt);
}
public DataTable GetEventTable(string tableName)
{
DataTable table = OleDbHelper.ExecuteDataTable("Event", "select * from " + tableName);
return(table);
}
