public byte[] OutputBlob(string strSql)
{
System.Data.IDbCommand command = null;
command = new DB2Command();
command.Connection = this.dbConnection as DB2Connection;
command.Transaction = dbTrans;
command.CommandType = System.Data.CommandType.Text;
command.Parameters.Clear();
command.CommandText = strSql + "";
System.Data.IDataReader reader = null;
try
{
reader = command.ExecuteReader();
}
catch (DB2Exception ex)
{
this.Err = "执行产生错误!" + ex.Message;
this.ErrCode = strSql;
this.DBErrCode = ex.ErrorCode;
this.WriteErr();
return(null);
}
catch (Exception ex)
{
this.Err = ex.Message;
this.WriteErr();
return(null);
}
byte[] byteData = new byte[0];
if (reader.Read())
{
// Create a byte array
// fetch the value of Oracle parameter into the byte array
//byteData = (byte[])(cmd.Parameters[0].Value);
try
{
byteData = (byte[])(reader[0]);
reader.Close();
}
catch (Exception ex)
{
this.Err = ex.Message;
reader.Close();
return(null);
}
}
else
{
reader.Close();
return(null);
}
return(byteData);
}
/// <summary>
/// Load All Modules and maintain Cache
/// </summary>
private static void LoadAllModules()
{
string aName = "", nSpace = "", vNo = "", vId = "";
//int keyId = 0;
//bool isRead = false;
System.Data.IDataReader dr = null;
try
{
if (_cacheModules.Count == 0)
{
dr = DataBase.DB.ExecuteReader(" SELECT AD_ModuleInfo_ID,AssemblyName,NameSpace,"
+ " VersionNo,VersionID,prefix FROM AD_ModuleInfo WHERE Prefix != 'VIS_'");
Tuple <string, string, string, string> modules = null;
while (dr.Read())
{
aName = dr[1].ToString() + "Svc";
nSpace = dr[2].ToString();
vNo = dr[3].ToString();
vId = dr[4].ToString();
modules = new Tuple <string, string, string, string>(dr[1].ToString() + "Svc", dr[2].ToString(), dr[3].ToString(), dr[4].ToString());
if (!_cacheModules.ContainsKey(dr[5].ToString()))
{
_cacheModules.Add(dr[5].ToString(), modules);
}
}
dr.Close();
dr = DataBase.DB.ExecuteReader(" SELECT AD_ModuleInfo_ID,AssemblyName,NameSpace,"
+ " VersionNo,VersionID,prefix,Name FROM AD_ModuleInfo WHERE Prefix='VIS_' ");
while (dr.Read())
{
modules = new Tuple <string, string, string, string>(dr[1].ToString() + "Svc", dr[2].ToString(), dr[3].ToString(), dr[4].ToString());
if (!_cacheVISModules.ContainsKey(dr[6].ToString()))
{
_cacheVISModules.Add(dr[6].ToString(), modules);
}
}
dr.Close();
}
}
catch (Exception ex)
{
_log.Config("Error Loading Modules" + ex.Message);
if (dr != null)
{
dr.Close();
dr = null;
}
}
}
/// <summary>
/// Returns the value of the specified variable name
/// </summary>
/// <param name="varName">Variable name</param>
/// <returns>variable value</returns>
public string GetVarValue(string varName)
{
string Function_Name = "GetVarValue";
LogHelper.Trace(CLASS_NAME, Function_Name, "Function_Entered");
string varValue = "null";
string localSQL = "SELECT VAR_VALUE FROM CONFIG_VARS WHERE VAR_NAME = '" + varName + "'";
System.Data.IDataReader drReader = SimpleDatabase.GetInstance().ExecuteQuery(localSQL);
if (drReader != null)
{
try
{
while (drReader.Read())
{
if (!drReader.IsDBNull(0))
{
varValue = drReader.GetString(0).ToString();
}
}
}
catch (System.Exception ex)
{
LogHelper.Error(CLASS_NAME, Function_Name, ex.ToString());
}
drReader.Close();
drReader.Dispose();
}
LogHelper.Trace(CLASS_NAME, Function_Name, "Function_Exited");
return(varValue);
}
public IEnumerable <string> MapSet(System.Data.IDataReader reader)
{
List <string> retorno = new List <string>();
try
{
while (reader.Read())
{
//Classe variavel = new Classe();
//variavel.objeto = = reader["Campo"] == DBNull.Value ? String.Empty : reader["Campo"].ToString().Trim();
retorno.Add(reader["Sample"] == DBNull.Value ? string.Empty : reader["Sample"].ToString());
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
reader.Close();
System.GC.Collect();
}
return(retorno);
}
/// <summary>
/// Returns the total sample group records count in table
/// </summary>
/// <returns>No of Sample group configured</returns>
public int GetRowCount()
{
string Function_Name = "GetRowCount";
LogHelper.Trace(CLASS_NAME, Function_Name, "Function_Entered");
string localSQL;
int nRowCount = 0;
localSQL = "SELECT COUNT(SAMPLE_GRP_NAME) AS MAXROWS FROM OPC_DT_SAMPLE_GRP";
System.Data.IDataReader drReader = SimpleDatabase.GetInstance().ExecuteQuery(localSQL);
try
{
if (drReader != null && drReader.Read())
{
if (!drReader.IsDBNull(0))
{
LogHelper.Debug(CLASS_NAME, Function_Name, "Function_Exited");
nRowCount = Convert.ToInt32(drReader["MAXROWS"]);
}
}
}
catch (System.Exception ex)
{
LogHelper.Error(CLASS_NAME, Function_Name, ex.ToString());
}
if (drReader != null)
{
drReader.Close();
drReader.Dispose();
}
LogHelper.Trace(CLASS_NAME, Function_Name, "Function_Exited");
return(nRowCount);
}
private string GetCodeInfo(string id, out string cop, out int counter)
{
cop = "";
counter = -1;
if (!string.IsNullOrEmpty(id))
{
string strSQL = "select * from thc_codes where cid='" + id + "'";
DB.DataBaseControl dbCtl = new DB.DataBaseControl();
dbCtl.Open();
System.Data.IDataReader dataReader = dbCtl.GetReader(strSQL, null);
string strResult = "";
if (dataReader.Read())
{
strResult = dataReader["link"].ToString().Trim();
cop = dataReader["cop"].ToString().Trim();
counter = int.Parse(dataReader["counter"].ToString());
}
dataReader.Close();
if (cop.Length > 0)
{
strSQL = "update thc_codes set counter=counter+1 where cid='" + id + "'";
dbCtl.ExecuteCommad(strSQL, null);
counter++;
}
dbCtl.Close();
return(strResult);
}
else
{
return("");
}
}
public string GetLocationNameFromKey(double locationKey)
{
string Function_Name = "GetLocationNameFromKey";
LogHelper.Trace(CLASS_NAME, Function_Name, "Function_Entered");
string locationName = "";
string localSQL = "SELECT NAME FROM LOCATION WHERE PKEY = " + locationKey.ToString();
System.Data.IDataReader drReader = SimpleDatabase.GetInstance().ExecuteQuery(localSQL);
try
{
if (drReader != null && drReader.Read())
{
if (!drReader.IsDBNull(drReader.GetOrdinal("NAME")))
{
locationName = DAOHelper.convert8859P1ToGB2312(drReader["NAME"].ToString());
}
}
}
catch (System.Exception ex)
{
LogHelper.Error(CLASS_NAME, Function_Name, ex.ToString());
}
if (drReader != null)
{
drReader.Close();
drReader.Dispose();
//SimpleDatabase.GetInstance().CloseCurrentSession();
}
LogHelper.Trace(CLASS_NAME, Function_Name, "Function_Exited");
return(locationName);
}
/// <summary>
/// Permite transformar un datareader en un objeto que implemente IEntidadPersistente de un SP
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="resultadoDataReader"></param>
internal List <T> RealizarConversionSp <T>(System.Data.IDataReader resultadoDataReader) where T : class, IEntidadPersistente, new()
{
var columnas = this.ObtenerColumnasDelDatareader(resultadoDataReader);
var resultadoFinal = new List <T>();
while (resultadoDataReader.Read())
{
var nuevoObjeto = new T();
foreach (var item in columnas)
{
var pi = nuevoObjeto.GetType().GetProperty(item);
//Solo mapeo si encuentro dicho campo.
if (pi != null)
{
pi.SetValue(nuevoObjeto, resultadoDataReader[columnas.IndexOf(item)]);
}
else
{
pi = null;
}
}
resultadoFinal.Add(nuevoObjeto);
}
resultadoDataReader.Close();
return(resultadoFinal);
}
/// <summary>
/// retrieve all the configuration name for real-time charting.
/// </summary>
/// <returns></returns>
public List <string> GetAllDPGrpNames()
{
string Function_Name = "GetAllDPGrpNames";
LogHelper.Trace(CLASS_NAME, Function_Name, "Function_Entered");
List <string> dpGrpNames = new List <string>();
string localSQL = " SELECT DISTINCT CONFIG_NAME FROM TRENDVIEWER_CONFIG order by config_name ";
System.Data.IDataReader drReader = SimpleDatabase.GetInstance().ExecuteQuery(localSQL);
if (drReader != null)
{
try
{
while (drReader.Read())
{
if (!drReader.IsDBNull(0))
{
dpGrpNames.Add(DAOHelper.convert8859P1ToGB2312(drReader["CONFIG_NAME"].ToString()));
}
}
}
catch (System.Exception ex)
{
LogHelper.Error(CLASS_NAME, Function_Name, ex.ToString());
}
drReader.Close();
drReader.Dispose();
//SimpleDatabase.GetInstance().CloseCurrentSession();
}
LogHelper.Trace(CLASS_NAME, Function_Name, "Function_Exited");
return(dpGrpNames);
}
/// <summary>
/// 按设定的条件执行搜索，并返回指定页数的数据。
/// </summary>
/// <param name="pageNum">搜索第几页的数据。</param>
/// <returns></returns>
public override IList <int> Search(int pageNum)
{
base.PageNum = pageNum;
List <int> lstId = new List <int>();
try
{
System.Data.IDataReader dr = base.GetDataReader(false);
if (dr != null)
{
while (dr.Read())
{
int intParentId = Function.ToInt(dr[Tables.Device.ParentId]);
if (intParentId > 0 && lstId.Contains(intParentId) == false)
{
lstId.Add(intParentId);
}
}
dr.Close();
}
}
finally
{
base.DbConnection.Close();
}
return(lstId);
}
/// <summary>
/// 获取指定实例内的所有数据库名。
/// </summary>
/// <param name="instanceName">实例名。如 SQL Server 所在计算机的IP地址</param>
/// <param name="loginSecure">是否使用 Windows 身份验证方式。如不使用此方式则需输入登录名与密码</param>
/// <param name="username">使用 SQL Server 身份验证方式时的登录用户名</param>
/// <param name="password">使用 SQL Server 身份验证方式时的登录密码</param>
/// <returns></returns>
public static IEnumerable <string> GetSqlServerDatabases(string instanceName, bool loginSecure, string username, string password)
{
List <string> lsNames = new List <string>();
SQLServerDb db = new SQLServerDb();
db.InstanceName = instanceName;
db.LoginSecure = loginSecure;
if (loginSecure == false)
{
db.UserName = username;
db.Password = password;
}
db.DatabaseName = "master";
System.Data.IDbConnection conn = db.GetDbConnection();
conn.Open();
System.Data.IDbCommand cmd = conn.CreateCommand();
cmd.CommandText = "SELECT name FROM sysdatabases";
cmd.Connection = conn;
System.Data.IDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
lsNames.Add(Function.ToTrim(dr["name"]));
}
dr.Close();
conn.Close();
return(lsNames);
}
} // End Sub RetrieveFile
// http://stackoverflow.com/questions/2885335/clr-sql-assembly-get-the-bytestream
// http://stackoverflow.com/questions/891617/how-to-read-a-image-by-idatareader
// http://stackoverflow.com/questions/4103406/extracting-a-net-assembly-from-sql-server-2005
public static void RetrieveFile(System.Data.IDbCommand cmd, string columnName, string path)
{
using (System.Data.IDataReader reader = ExecuteReader(cmd, System.Data.CommandBehavior.SequentialAccess | System.Data.CommandBehavior.CloseConnection))
{
bool hasRows = reader.Read();
if (hasRows)
{
const int BUFFER_SIZE = 1024 * 1024 * 10; // 10 MB
byte[] buffer = new byte[BUFFER_SIZE];
int col = string.IsNullOrEmpty(columnName) ? 0 : reader.GetOrdinal(columnName);
int bytesRead = 0;
int offset = 0;
// Write the byte stream out to disk
using (System.IO.FileStream bytestream = new System.IO.FileStream(path, System.IO.FileMode.Create, System.IO.FileAccess.Write, System.IO.FileShare.None))
{
while ((bytesRead = (int)reader.GetBytes(col, offset, buffer, 0, BUFFER_SIZE)) > 0)
{
bytestream.Write(buffer, 0, bytesRead);
offset += bytesRead;
} // Whend
bytestream.Close();
} // End Using bytestream
} // End if (!hasRows)
reader.Close();
} // End Using reader
} // End Function RetrieveFile
public bool GetOPCDataPointByName(string dataPoint)
{
string Function_Name = "GetOPCDataPointByName";
LogHelper.Trace(CLASS_NAME, Function_Name, "Function_Entered");
string localSQL = "SELECT PKEY FROM DATALOG_DP_TREND WHERE DP_NAME = '" + dataPoint + "'";
System.Data.IDataReader drReader = SimpleDatabase.GetInstance().ExecuteQuery(localSQL);
bool bFound = false;
try
{
if (drReader != null && drReader.Read())
{
if (!drReader.IsDBNull(drReader.GetOrdinal(COLUMN_PKEY)))
{
bFound = true;
}
}
}
catch (System.Exception ex)
{
LogHelper.Error(CLASS_NAME, Function_Name, ex.ToString());
}
if (drReader != null)
{
drReader.Close();
drReader.Dispose();
}
LogHelper.Trace(CLASS_NAME, Function_Name, "Function_Exited");
return(bFound);
}
private void Doldur(TreeNode tnt)
{
tnt.Nodes.Clear();
Utility.Engine.sql = "SELECT DepoKodu, DepoAdi, Sevk FROM depo_tanim (NOLOCK) WHERE (UstDepoKodu = N'" + tnt.Text + "') ORDER BY DepoKodu ";
try
{
System.Data.IDataReader dr = Utility.Engine.dat.TSelect(Utility.Engine.sql);
if (dr != null)
{
while (dr.Read())
{
TreeNode tn = new TreeNode();
tn.Text = dr.GetValue(0).ToString();
tn.Tag = dr.GetValue(2).ToString();
tnt.Nodes.Add(tn);
}
}
if (dr != null)
{
dr.Close();
}
}
catch (Exception exc)
{
MessageBox.Show("DEPO TANIMLARINDA HATA VAR OKUNAMIYOR!!", "DiKKAT!", MessageBoxButtons.OK, MessageBoxIcon.Exclamation, MessageBoxDefaultButton.Button1);
return;
}
}
/*SELECT DepoKodu, DepoAdi, Sevk, Eksi, UstDepoKodu
* FROM depo_tanim
* WHERE (UstDepoKodu = N'CIKIS')
* ORDER BY DepoKodu*/
private void Doldur()
{
Utility.Engine.sql = "SELECT DepoKodu, DepoAdi, Sevk FROM depo_tanim (NOLOCK) WHERE (Level_ = 0) ORDER BY DepoKodu ";
try
{
System.Data.IDataReader dr = Utility.Engine.dat.TSelect(Utility.Engine.sql);
if (dr != null)
{
while (dr.Read())
{
string s = dr.GetValue(0).ToString(),
ss = "";
int c = s.LastIndexOf('-');
ss = s.Substring(c + 1, s.Length - c);
TreeNode tn = new TreeNode();
tn.Text = ss;
tn.Tag = dr.GetValue(2).ToString();
treeView1.Nodes.Add(tn);
}
}
if (dr != null)
{
dr.Close();
}
}
catch (Exception exc)
{
MessageBox.Show("DEPO TANIMLARINDA HATA VAR OKUNAMIYOR!!", "DiKKAT!", MessageBoxButtons.OK, MessageBoxIcon.Exclamation, MessageBoxDefaultButton.Button1);
return;
}
}
/// <summary>
///Return data as sorted Array - not implemented
/// </summary>
/// <param name="mandatory">mandatory</param>
/// <param name="onlyValidated">only validated</param>
/// <param name="onlyActive">only active</param>
/// <param name="temporary">force load for temporary display</param>
/// <returns></returns>
public override List <NamePair> GetData(bool mandatory, bool onlyValidated, bool onlyActive, bool temporary)
{
List <NamePair> list = new List <NamePair>();
if (!mandatory)
{
list.Add(new KeyNamePair(-1, ""));
}
//
StringBuilder sql = new StringBuilder(
"SELECT C_Location_ID from C_Location WHERE AD_Client_ID = @ClientId AND (AD_Org_ID = 0 OR @parameter = 0)");
if (onlyActive)
{
sql.Append(" AND IsActive='Y'");
}
sql.Append(" ORDER BY 1");
System.Data.IDataReader dr = null;
System.Data.SqlClient.SqlParameter[] param = null;
try
{
param = new System.Data.SqlClient.SqlParameter[2];
param[0] = new System.Data.SqlClient.SqlParameter("@ClientId", GetCtx().GetAD_Client_ID(_WindowNo));
param[1] = new System.Data.SqlClient.SqlParameter("@parameter", GetCtx().GetAD_Org_ID(_WindowNo));
dr = DataBase.DB.ExecuteReader(sql.ToString(), param);
while (dr.Read())
{
list.Add(Get(Utility.Util.GetValueOfInt(dr[0])));
}
dr.Close();
dr = null;
param = null;
}
catch (Exception e)
{
if (dr != null)
{
dr.Close();
dr = null;
param = null;
}
log.Log(Level.SEVERE, sql.ToString(), e);
}
// Sort & return
return(list);
}
/// <summary>
/// Returns Sample Group details of the specified Sample group Key.
/// </summary>
/// <param name="opcGrpID">Sample group key</param>
/// <returns>Sample Group entity</returns>
public EtyOPCSampleGroup GetOPCGrpByID(string opcGrpID)
{
string Function_Name = "GetOPCGrpByID";
LogHelper.Trace(CLASS_NAME, Function_Name, string.Format("Function_Entered with params - {0}", opcGrpID));
EtyOPCSampleGroup etyOPCSampleGrp = new EtyOPCSampleGroup();
string localSQL = " SELECT SAMPLE_GRP_NAME,SAMPLE_GRP_DESC,SAMPLE_INTERVAL,INTERVAL_TYPE,START_TIME,DELTA_VALUE,DISABLE "
+ " FROM OPC_DT_SAMPLE_GRP WHERE SAMPLE_GRP_ID = " + opcGrpID;
System.Data.IDataReader drReader = SimpleDatabase.GetInstance().ExecuteQuery(localSQL);
try
{
while (drReader != null && drReader.Read())
{
if (!drReader.IsDBNull(drReader.GetOrdinal("SAMPLE_GRP_NAME")))
{
etyOPCSampleGrp.SampleGrpName = DAOHelper.convert8859P1ToGB2312(drReader["SAMPLE_GRP_NAME"].ToString());
}
if (!drReader.IsDBNull(drReader.GetOrdinal("SAMPLE_GRP_DESC")))
{
etyOPCSampleGrp.SampleGrpDescription = DAOHelper.convert8859P1ToGB2312(drReader["SAMPLE_GRP_DESC"].ToString());
}
if (!drReader.IsDBNull(drReader.GetOrdinal("SAMPLE_INTERVAL")))
{
etyOPCSampleGrp.Interval = Convert.ToInt32(drReader["SAMPLE_INTERVAL"]);
}
if (!drReader.IsDBNull(drReader.GetOrdinal("INTERVAL_TYPE")))
{
etyOPCSampleGrp.IntervalType = drReader["INTERVAL_TYPE"].ToString();
}
if (!drReader.IsDBNull(drReader.GetOrdinal("START_TIME")))
{
etyOPCSampleGrp.StartTime = drReader["START_TIME"].ToString();
}
if (!drReader.IsDBNull(drReader.GetOrdinal("DELTA_VALUE")))
{
etyOPCSampleGrp.DeltaValue = Convert.ToDouble(drReader["DELTA_VALUE"]);
}
if (!drReader.IsDBNull(drReader.GetOrdinal("DISABLE")))
{
etyOPCSampleGrp.Disabled = DAOHelper.ChangeStrToBool(drReader["DISABLE"].ToString());
}
}
}
catch (System.Exception ex)
{
LogHelper.Error(CLASS_NAME, Function_Name, ex.ToString());
}
if (drReader != null)
{
drReader.Close();
drReader.Dispose();
}
LogHelper.Trace(CLASS_NAME, Function_Name, "Function_Exited");
return(etyOPCSampleGrp);
}
private void Load()
{
AC.Base.Database.DbConnection dbConn = this.m_Application.GetDbConnection();
if (dbConn != null)
{
try
{
string strSql = "SELECT * FROM " + Tables.TaskGroup.TableName;
System.Data.IDataReader dr = dbConn.ExecuteReader(strSql);
while (dr.Read())
{
TaskGroup _TaskGroup = new TaskGroup(this.m_Application);
_TaskGroup.SetDataReader(dr);
this.Add(_TaskGroup);
}
dr.Close();
strSql = "SELECT * FROM " + Tables.TaskConfig.TableName;
dr = dbConn.ExecuteReader(strSql);
while (dr.Read())
{
TaskGroup _TaskGroup = this.GetById(Function.ToInt(dr[Tables.TaskConfig.TaskGroupId]));
if (_TaskGroup != null)
{
TaskType _TaskType = this.m_Application.TaskTypes.GetTaskType(Function.ToString(dr[Tables.TaskConfig.TaskType]));
if (_TaskType != null)
{
TaskConfig _TaskConfig = _TaskType.CreateTaskConfig(_TaskGroup);
_TaskConfig.SetDataReader(dr);
_TaskGroup.TaskConfigs.Add(_TaskConfig);
}
}
}
dr.Close();
}
catch (Exception ex)
{
throw ex;
}
finally
{
dbConn.Close();
}
}
}
} // End Function IsSimpleType
public virtual System.Collections.Generic.List <T> GetList <T>(System.Data.IDbCommand cmd)
{
System.Collections.Generic.List <T> lsReturnValue = new System.Collections.Generic.List <T>();
T tThisValue = default(T);
System.Type tThisType = typeof(T);
using (System.Data.IDataReader idr = ExecuteReader(cmd))
{
if (IsSimpleType(tThisType))
{
while (idr.Read())
{
object objVal = idr.GetValue(0);
// tThisValue = System.Convert.ChangeType(objVal, T),
tThisValue = (T)ConvertResult <T>(objVal, true);
lsReturnValue.Add(tThisValue);
} // End while (idr.Read())
}
else
{
int iFieldCount = idr.FieldCount;
Setter_t <T>[] mems = new Setter_t <T> [iFieldCount];
for (int i = 0; i < iFieldCount; ++i)
{
string strName = idr.GetName(i);
mems[i] = LinqHelper.GetSetter <T>(strName);
} // Next i
while (idr.Read())
{
tThisValue = System.Activator.CreateInstance <T>();
for (int i = 0; i < iFieldCount; ++i)
{
Setter_t <T> setter = mems[i];
if (setter != null)
{
object objVal = idr.GetValue(i);
setter(tThisValue, objVal);
}
} // Next i
lsReturnValue.Add(tThisValue);
} // Whend
} // End if IsSimpleType(tThisType)
idr.Close();
} // End Using idr
return(lsReturnValue);
} // End Function GetList
/// <summary>
/// Returns the DataPoints based specified datapoint name substring.
/// </summary>
/// <param name="opcServerName">Datapoint server name</param>
/// <param name="DataPointNameSubstr"> Datapoint name substring to be queried</param>
/// <returns>Datapoints</returns>
public List <EtyDataLogDPTrend> GetDataPointByName(string opcServerName, string DataPointNameSubstr)
{
const string Function_Name = "GetDataPointByName";
LogHelper.Trace(CLASS_NAME, Function_Name, "Function_Entered");
List <EtyDataLogDPTrend> etyDPTrendList = new List <EtyDataLogDPTrend>();
string localSQL = "SELECT et.PKEY AS ENTITY_KEY,CONCAT(et.NAME,'.Value')as DP_NAME ,DP.DP_GRP_ID AS DP_GRP_ID FROM ENTITY et LEFT JOIN DATALOG_DP_TREND DP" +
" ON et.PKEY = DP.ENTITY_KEY WHERE TYPEKEY = (SELECT PKEY FROM ENTITYTYPE WHERE NAME = 'DataPoint')" +
" AND UPPER(et.NAME) LIKE '%" + DAOHelper.convertEscapeStringAndGB2312To8859P1(DataPointNameSubstr) + "%'";
string LocationClauseStr = DAOHelper.CheckLocationAndAddSQL(COLUMN_ENTITY_LOCATIONKEY);
if (LocationClauseStr.Length != 0)
{
localSQL += " AND" + LocationClauseStr;
}
localSQL += " ORDER BY DP_NAME ";
System.Data.IDataReader drReader = SimpleDatabase.GetInstance().ExecuteQuery(localSQL);
if (drReader != null)
{
try
{
while (drReader.Read())
{
EtyDataLogDPTrend etyDPTrend = new EtyDataLogDPTrend();
if (!drReader.IsDBNull(drReader.GetOrdinal(COLUMN_ENTITY_KEY)))
{
etyDPTrend.EntityKey = Convert.ToDouble(drReader[COLUMN_ENTITY_KEY]);
}
if (!drReader.IsDBNull(drReader.GetOrdinal(COLUMN_NAME)))
{
etyDPTrend.OPCDataPointName = DAOHelper.convert8859P1ToGB2312(drReader[COLUMN_NAME].ToString());
}
if (!drReader.IsDBNull(drReader.GetOrdinal(COLUMN_SAMPLE_GRP_ID)))
{
etyDPTrend.OPCSampleGrpId = Convert.ToDouble(drReader[COLUMN_SAMPLE_GRP_ID]);
}
etyDPTrendList.Add(etyDPTrend);
}
}
catch (System.Exception ex)
{
LogHelper.Error(CLASS_NAME, Function_Name, ex.ToString());
}
drReader.Close();
drReader.Dispose();
//SimpleDatabase.GetInstance().CloseCurrentSession();
}
LogHelper.Trace(CLASS_NAME, Function_Name, "Function_Exited");
return(etyDPTrendList);
}
/// <summary>
/// Returns all the Root Data Nodes.
/// </summary>
/// <returns>Root Data Nodes</returns>
public List <EtyOPCDataNode> GetAllOPCDataNode(string serverRootName, string opcServerName)
{
const string Function_Name = "GetAllOPCDataNode";
LogHelper.Trace(CLASS_NAME, Function_Name, "Function_Entered");
List <EtyOPCDataNode> OPCDataNodeList = new List <EtyOPCDataNode>();
string localSQL = " SELECT KEYID,DT_NODE_NAME,DT_NODE_DESC " +
" FROM OPC_DT_NODE WHERE PKEYID IN (SELECT DISTINCT PKEY FROM ENTITY WHERE NAME = '" + serverRootName + "') AND DISABLE = 'N' AND DATA_PT_SERVER = '" + opcServerName
+ "' ORDER BY DT_NODE_NAME";
//sql for getting from entity table
/*string localSQL = " SELECT PKEY,NAME,DESCRIPTION" +
* " FROM ENTITY WHERE TYPEKEY = (SELECT PKEY FROM ENTITYTYPE WHERE NAME = 'DataNode')"+
* " AND PARENTKEY IN (SELECT DISTINCT PKEY FROM ENTITY WHERE TYPEKEY IN (SELECT PKEY FROM ENTITYTYPE WHERE NAME = 'SCADAROOT'))"+
* " ORDER BY NAME";*/
System.Data.IDataReader drReader = SimpleDatabase.GetInstance().ExecuteQuery(localSQL);
if (drReader != null)
{
try
{
while (drReader.Read())
{
EtyOPCDataNode etyOPCDataNode = new EtyOPCDataNode();
if (!drReader.IsDBNull(drReader.GetOrdinal("KEYID")))
{
etyOPCDataNode.OPCDataNodeId = Convert.ToDouble(drReader["KEYID"]);
}
if (!drReader.IsDBNull(drReader.GetOrdinal("DT_NODE_NAME")))
{
etyOPCDataNode.OPCDataNodeName = DAOHelper.convert8859P1ToGB2312(drReader["DT_NODE_NAME"].ToString());
}
if (!drReader.IsDBNull(drReader.GetOrdinal("DT_NODE_DESC")))
{
etyOPCDataNode.OPCDataNodeDesc = DAOHelper.convert8859P1ToGB2312(drReader["DT_NODE_DESC"].ToString());
}
OPCDataNodeList.Add(etyOPCDataNode);
}
}
catch (System.Exception ex)
{
LogHelper.Error(CLASS_NAME, Function_Name, ex.ToString());
}
drReader.Close();
drReader.Dispose();
}
LogHelper.Trace(CLASS_NAME, Function_Name, "Function_Exited");
return(OPCDataNodeList);
}
/// <summary>
/// Returns the DataPoints based on the filterstring for specified server name.
/// e.g. for filterstring can include substring datapoint name to search.
/// </summary>
/// <param name="opcServerName">datapoint server name</param>
/// <param name="filterString">Where clause query string</param>
/// <returns>Datapoints</returns>
public List <EtyDataLogDPTrend> GetOPCDataPoint(string opcServerName, string filterString)
{
const string Function_Name = "GetOPCDataPoint";
LogHelper.Trace(CLASS_NAME, Function_Name, "Function_Entered");
List <EtyDataLogDPTrend> etyDPTrendList = new List <EtyDataLogDPTrend>();
string localSQL = "SELECT et.PKEY AS ENTITY_KEY,CONCAT(et.NAME,'.Value') as DP_NAME ,DP.DP_GRP_ID as DP_GRP_ID FROM ENTITY et LEFT JOIN DATALOG_DP_TREND DP"
+ " ON et.PKEY = DP.ENTITY_KEY WHERE et.TYPEKEY = (SELECT PKEY FROM ENTITYTYPE WHERE NAME = 'DataPoint')";
if (filterString.Trim() != "")
{
localSQL += filterString;
}
localSQL += " ORDER BY DP_NAME ";
System.Data.IDataReader drReader = SimpleDatabase.GetInstance().ExecuteQuery(localSQL);
if (drReader != null)
{
try
{
while (drReader.Read())
{
EtyDataLogDPTrend etyDPTrend = new EtyDataLogDPTrend();
if (!drReader.IsDBNull(drReader.GetOrdinal(COLUMN_ENTITY_KEY)))
{
etyDPTrend.EntityKey = Convert.ToDouble(drReader[COLUMN_ENTITY_KEY]);
}
if (!drReader.IsDBNull(drReader.GetOrdinal(COLUMN_NAME)))
{
etyDPTrend.OPCDataPointName = DAOHelper.convert8859P1ToGB2312(drReader[COLUMN_NAME].ToString());
}
if (!drReader.IsDBNull(drReader.GetOrdinal(COLUMN_SAMPLE_GRP_ID)))
{
etyDPTrend.OPCSampleGrpId = Convert.ToDouble(drReader[COLUMN_SAMPLE_GRP_ID]);
}
etyDPTrendList.Add(etyDPTrend);
}
}
catch (System.Exception ex)
{
LogHelper.Error(CLASS_NAME, Function_Name, ex.ToString());
}
drReader.Close();
drReader.Dispose();
//SimpleDatabase.GetInstance().CloseCurrentSession();
}
LogHelper.Trace(CLASS_NAME, Function_Name, "Function_Exited");
return(etyDPTrendList);
}
public string GenerateScreen(object _query)
{
string IP = System.Web.HttpContext.Current.Request.UserHostAddress.ToString();
if (IP == "::1")
{
IP = "127.0.0.1";
}
JsonOutput json = new JsonOutput();
bool isAuthorized = plus.Security.Authorization.Authorize(plus.Security.Util.GetClientIP(), false);
if (!isAuthorized)
{
return(json.Write(plus._System.Configuration.Manager.Get(plus._System.Configuration.ConfigurationElement.SECURITY_PATH) + "unauthorized/").Serialize());
}
json.WriteStartArray();
try
{
Dictionary <string, object> query = (Dictionary <string, object>)_query;
IDictionary dict = new Dictionary <string, object>();
dict.Add("dbName", plus.Util.Helper.EmptyToNull(query["dbName"]));
dict.Add("ObjectName", plus.Util.Helper.EmptyToNull(query["ObjectName"]));
dict.Add("Prefix", plus.Util.Helper.EmptyToNull(query["Prefix"]));
dict.Add("IsSubForm", plus.Util.Helper.EmptyToNull(query["IsSubForm"]));
dict.Add("ParentObject", plus.Util.Helper.EmptyToNull(query["ParentObject"]));
dict.Add("ElPerRow", plus.Util.Helper.EmptyToNull(query["ElPerRow"]));
dict.Add("Lang", plus.Util.Helper.EmptyToNull(query["Lang"]));
System.Data.IDataReader reader = plus.Data.DAL.Read("Default", @"exec [reg].[spGenerateScreen] @dbName, @ObjectName, @Prefix, @IsSubForm, @ParentObject, @ElPerRow, @Lang ", dict);
bool fetchColumns = (bool)plus.Util.Helper.IfNothing(query.ContainsKey("fetchColumns") ? query["fetchColumns"] : false, false);
//json.WriteTableResult(table, reader, fetchColumns);
//json.WriteQueryResult(String.Concat(pathArray[1], ".", pathArray[2]), reader, fetchColumns, true);
json.WriteQuickResult(String.Concat("Tconfsys.reg", ".", "Screen"), reader, fetchColumns, true);
if (!reader.IsClosed)
{
reader.Close();
}
}
catch (Exception ex)
{
json.WriteError(ex.Message /*+ ex.StackTrace*/);
json.WriteEndArray();
return(json.Serialize());
}
json.WriteEndArray();
return(json.Serialize());
}
public static async Task <SigninResponse> SigninAsync(LoginRequest loginRequest)
{
int AccessCountFailed = 0;
string DbUsername = "", DbPasswordhash = "", Message = "LockedOut";
try
{
using (var conn = Connection)
{
DynamicParameters parameters = new DynamicParameters();
parameters.Add("Username", loginRequest.Username);
System.Data.IDataReader reader = await conn.ExecuteReaderAsync("SigninProcedure", parameters, commandType : System.Data.CommandType.StoredProcedure);
while (reader.Read())
{
DbUsername = reader["Username"].ToString();
DbPasswordhash = reader["PasswordHash"].ToString();
AccessCountFailed = reader.GetInt32(2);
break;
}
reader.Close();
while (AccessCountFailed < 5)
{
if ((loginRequest.Username == DbUsername) && (GetHash.HashCode(loginRequest.Password) == DbPasswordhash))
{
Message = "true";
}
else
{
//await conn.ExecuteAsync("AccessFailedCountIncrement",parameters, commandType: System.Data.CommandType.StoredProcedure);
Message = "false";
}
break;
}
return(new SigninResponse(true, new List <ServiceResponse>
{
new ServiceResponse("200", Message, null)
}));
}
}
catch (Exception ex)
{
return(new SigninResponse(false, new List <ServiceResponse>
{
new ServiceResponse("400", "Error Logging In", new List <Error>()
{
new Error(ex.GetHashCode().ToString(), ex.Message)
})
}));
}
}
public static List <E> GetList(System.Data.IDataReader dr)
{
List <E> list = new List <E>();
while (dr.Read())
{
list.Add(GetNotClose(dr));
}
dr.Close();
return(list);
}
} // End Function GetColumnNames
public override System.Data.DataTable GetColumnNamesForTable(string strTableName)
{
strTableName = strTableName.Replace("'", "''");
System.Data.DataTable dtSchemaTable = null;
using (System.Data.IDataReader idr = ExecuteReader("SELECT * FROM " + strTableName))
{
dtSchemaTable = idr.GetSchemaTable();
idr.Close();
}
return(dtSchemaTable);
} // End Function GetColumnNamesForTable
} // End Sub SimpleInsert
// http://stackoverflow.com/questions/2885335/clr-sql-assembly-get-the-bytestream
// http://stackoverflow.com/questions/891617/how-to-read-a-image-by-idatareader
// http://stackoverflow.com/questions/4103406/extracting-a-net-assembly-from-sql-server-2005
public static void RetrieveFile(string fileName, string path)
{
string sql = @"
--DECLARE @__filename nvarchar(255)
--SET @__filename = 'lkik'
SELECT
uid
,data
,filename
FROM _____save
WHERE filename = @__filename
";
using (System.Data.IDbCommand cmd = SQL.CreateCommand(sql, 0))
{
SQL.AddParameter(cmd, "__filename", fileName);
using (System.Data.IDataReader reader = SQL.ExecuteReader(cmd))
{
bool hasRows = reader.Read();
if (hasRows)
{
const int BUFFER_SIZE = 1024 * 1024 * 10; // 10 megs
byte[] buffer = new byte[BUFFER_SIZE];
int col = reader.GetOrdinal("data");
int bytesRead = 0;
int offset = 0;
// write the byte stream out to disk
//using (System.IO.FileStream bytestream = new System.IO.FileStream(path, System.IO.FileMode.CreateNew))
using (System.IO.FileStream bytestream = new System.IO.FileStream(path, System.IO.FileMode.Create))
{
// SqlBytes bytes = reader.GetSqlBytes(0);
while ((bytesRead = (int)reader.GetBytes(col, offset, buffer, 0, BUFFER_SIZE)) > 0)
{
bytestream.Write(buffer, 0, bytesRead);
offset += bytesRead;
} // Whend
bytestream.Close();
} // End Using bytestream
} // End if (!hasRows)
reader.Close();
} // End Using reader
} // End Using cmd
} // End Function RetrieveFile
public static void EntityListByEmit(System.Data.IDataReader DataReader, DbExecuteReadOnebyOneAction <T> callback)
{
Dictionary <string, string> FieldDic = new Dictionary <string, string>();
int i = 0;
while (i < DataReader.FieldCount)
{
string Name = DataReader.GetName(i);
if (!FieldDic.ContainsKey(Name.ToLower()))
{
FieldDic.Add(Name.ToLower(), Name);
}
i++;
}
EntityPropertyEmitSetter[] ps = EntityPropertyEmitSetter.GetProperties(typeof(T));
long rowNum = 0;
while (DataReader.Read())
{
T obj = new T();
int num = 0;
while (num < ps.Length)
{
string fieldName = ps[num].Info.Name;
string name = fieldName.ToLower();
object val = DataReader[FieldDic[name]];
if (FieldDic.ContainsKey(name) && val != DBNull.Value)
{
ps[num].Setter(obj, val);
}
num++;
}
rowNum++;
DbExecuteReadOnebyOneResult <T> result = new DbExecuteReadOnebyOneResult <T>(rowNum, obj);
try
{
callback(result);
}
catch
{
break;
}
if (!result.Next)
{
break;
}
}
DataReader.Close();
}
public static T Entity(System.Data.IDataReader DataReader)
{
Dictionary <string, DataReaderInfo> FieldDic = new Dictionary <string, DataReaderInfo>();
int i = 0;
while (i < DataReader.FieldCount)
{
string Name = DataReader.GetName(i);
FieldDic.Add(Name.ToLower(), new DataReaderInfo {
Index = i, Name = Name, FieldType = DataReader.GetFieldType(i)
});
i++;
}
List <T> Li = new List <T>();
while (Li.Count < 1 && DataReader.Read())
{
T obj = (T)Activator.CreateInstance(typeof(T));
System.Reflection.PropertyInfo[] pis = obj.GetType().GetProperties();
int j = 0;
while (j < pis.Length)
{
string fieldName = pis[j].Name;
string name = fieldName.ToLower();
if (FieldDic.ContainsKey(name))
{
DataReaderInfo info = FieldDic[name];
string key = info.Name;
object o = DataReader[key];
if (TargetTypeIsBool(pis[j].PropertyType))
{
pis[j].SetValue(obj, ConverToBool(o), null);
}
else
{
if (DBNull.Value != o)
{
pis[j].SetValue(obj, o, null);
}
}
}
j++;
}
Li.Add(obj);
}
DataReader.Close();
return(Li.Count > 0 ? Li[0] : default(T));
}
public virtual void Close()
{
try {
closed = true;
if (!(resultSet).IsClosed)
{
if (!(resultSet).IsClosed)
{
resultSet.Close();
}
}
} catch (System.Exception e) {
throw new Net.Vpc.Upa.Exceptions.FindException(e, new Net.Vpc.Upa.Types.I18NString("CloseFailed"));
}
}