/// <summary>
/// returns specific key
/// </summary>
/// <param name="idpeKeyType">specific key</param>
/// <returns></returns>
public IdpeKey Key(IdpeKeyTypes idpeKeyType)
{
if ((idpeKeyType == IdpeKeyTypes.Custom) ||
(idpeKeyType.IsConnectionStringType()))
{
return(null);
}
List <IdpeKey> keys = (from e in this.Keys
where e.Type == (int)idpeKeyType
select e).ToList();
if (keys.Count > 0)
{
if (keys.Count > 1)
{
ExtensionMethods.TraceInformation("Warning:: More than one key of type '{0}' defined in data source '{1}'. Taking first key.",
idpeKeyType.ToString(), this.Id);
}
return(keys[0]);
}
else
{
//throw new Exception(string.Format("No key of type '{0}' is defined in data source '{1}'",
// idpeKeyType.ToString(), this.Id));
return(null);
}
}
private GSearchResultCube FormatResultKey(GSearchResultCube result)
{
bool isErrored = false;
result.DataSource = new SqlClientManager(ConnectionString, IdpeKeyTypes.ConnectionStringSqlCe)
.ExecuteQuery("select name from idpedatasource ds inner join idpekeydatasource kds on kds.datasourceid = ds.id where keyid = " + result.ReferenceId
, ref isErrored);
int keyType = 0;
if (int.TryParse(result.Param1, out keyType))
{
IdpeKeyTypes idpeKeyType = (IdpeKeyTypes)keyType;
result.Param1 = idpeKeyType.ToString();
if (result.Param1.Contains("OutputWriter"))
{
result.Where = "Output Writer";
}
else if (result.Param1 == "Custom")
{
result.Where = "Custom - " + result.ReferenceName;
}
else
{
result.Where = result.Param1;
}
}
return(result);
}
string FindSREKeyUsingName(List <IdpeKey> keys, IdpeKeyTypes idpeKeyType)
{
IdpeKey key = (from e in keys
where e.Name == idpeKeyType.ToString()
select e).SingleOrDefault();
return(key != null ? key.Value : string.Empty);
}
public bool CheckReferenceKey(string connectionString, IdpeKeyTypes databaseType, string query, string data)
{
bool found = false;
IDbConnection con = null;
IDbCommand command = null;
IDataReader reader = null;
IDal myDal = new DataAccessLayer(Information.EyediaCoreConfigurationSection.Database.DatabaseType).Instance;
string myConnectionString = string.Empty;
if (!string.IsNullOrEmpty(connectionString))
{
myDal = new DataAccessLayer(GetDalDBType(databaseType)).Instance; //use current
myConnectionString = connectionString;
}
else
{
myDal = _Dal; //use default
myConnectionString = _ConnectionString; //use default
}
try
{
con = myDal.CreateConnection(myConnectionString);
con.Open();
command = myDal.CreateCommand(query, con);
reader = command.ExecuteReader();
while (reader.Read())
{
if (reader.GetString(0).ToUpper().Equals(data.ToUpper()))
{
found = true;
break;
}
}
}
catch (Exception ex)
{
Trace.TraceError("Error while checking reference key." + Environment.NewLine + ex.Message + Environment.NewLine + ex.StackTrace);
}
finally
{
if (con != null)
{
if (con.State != System.Data.ConnectionState.Closed)
{
con.Close();
}
}
reader.Dispose();
command.Dispose();
con.Close();
con.Dispose();
}
return(found);
}
public DataTable ExecuteQueryAndGetDataTable(string connectionString, IdpeKeyTypes databaseType, string query, ref string errorMessage, int timeOut = 5, bool silent = false)
{
string returnString = string.Empty;
IDbConnection con = null;
IDbCommand command = null;
DataTable dataTable = new DataTable();
IDal myDal = new DataAccessLayer(Information.EyediaCoreConfigurationSection.Database.DatabaseType).Instance;
string myConnectionString = string.Empty;
if (!string.IsNullOrEmpty(connectionString))
{
myDal = new DataAccessLayer(GetDalDBType(databaseType)).Instance; //use current
myConnectionString = connectionString;
}
else
{
myDal = _Dal; //use default
myConnectionString = _ConnectionString; //use default
}
try
{
con = myDal.CreateConnection(myConnectionString);
con.Open();
command = myDal.CreateCommand(query, con);
command.CommandTimeout = 60 * timeOut; //5 Minutes
dataTable.Load(command.ExecuteReader());
}
catch (Exception ex)
{
errorMessage = ex.Message;
if (!silent)
{
Trace.TraceError("Error while executing query '{0}'{1}{2} on '{3}'.", query, Environment.NewLine, ex.Message, GetDatabaseName(myConnectionString));
}
}
finally
{
if (con != null)
{
if (con.State != System.Data.ConnectionState.Closed)
{
con.Close();
}
}
if (command != null)
{
command.Dispose();
}
if (con != null)
{
con.Dispose();
}
}
return(dataTable);
}
public DataSourceCSharpCodeReferenceExtractor(int dataSourceId, IdpeKeyTypes idpeKeyType)
{
this.DataSourceId = dataSourceId;
Key = new Manager().GetKey(DataSourceId, idpeKeyType.ToString());
AttributeNames = new List <string>();
SystemAttributeNames = new List <string>();
ProcessVariablesIncorrectWay = new List <string>();
ProcessVariables = new List <string>();
}
public static bool IsConnectionStringType(this IdpeKeyTypes type)
{
if ((type == IdpeKeyTypes.ConnectionStringOracle) ||
(type == IdpeKeyTypes.ConnectionStringSqlCe) ||
(type == IdpeKeyTypes.ConnectionStringSqlServer) ||
(type == IdpeKeyTypes.ConnectionStringDB2iSeries))
{
return(true);
}
return(false);
}
public IdpeKey GetKey(IdpeKeyTypes keyType)
{
string commandText = "select [KeyId],[Name],[Value],[ValueBinary], [Type],[IsDeployable],[NextKeyId],[CreatedTS],[CreatedBy],[ModifiedTS],[ModifiedBy],[Source] from [IdpeKey] where [Type] = " + (int)keyType;
DataTable table = CoreDatabaseObjects.Instance.ExecuteCommand(commandText);
if (table == null)
{
return(null);
}
if (table.Rows.Count == 1)
{
return(RowToSreKey(table.Rows[0]));
}
return(null);
}
public static string GetKeyValue(this List <IdpeKey> keys, IdpeKeyTypes keyType)
{
if (keys == null)
{
return(string.Empty);
}
List <IdpeKey> filteredKeys = (from k in keys
where k.Name == keyType.ToString()
select k).ToList();
IdpeKey key = GetFirstItem(filteredKeys);
if (key == null)
{
return(string.Empty);
}
return(string.IsNullOrEmpty(key.Value) ? string.Empty : key.Value);
}
//private void btnSave_Click(object sender, EventArgs e)
public void Save()
{
IdpeKey key = new IdpeKey();
IdpeKeyTypes selectedType = GetSreKeyType();
key.Name = txtConnectionStringName.Text;
key.Value = txtConnectionString.Text;
key.Type = (int)selectedType;
try
{
Manager dm = new Manager();
dm.Save(key, DataSourceId);
refreshToolStripMenuItem_Click(null, null);
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString(), "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
public static DatabaseTypes GetDatabaseType(this IdpeKey key)
{
IdpeKeyTypes idpeKeyType = (IdpeKeyTypes)key.Type;
switch (idpeKeyType)
{
case IdpeKeyTypes.ConnectionStringSqlServer:
return(DatabaseTypes.SqlServer);
case IdpeKeyTypes.ConnectionStringOracle:
return(DatabaseTypes.Oracle);
case IdpeKeyTypes.ConnectionStringDB2iSeries:
return(DatabaseTypes.DB2iSeries);
case IdpeKeyTypes.ConnectionStringSqlCe:
return(DatabaseTypes.SqlCe);
default:
throw new Exception(idpeKeyType.ToString() + " is not database type key!");
}
}
DatabaseTypes GetDalDBType(IdpeKeyTypes keytype)
{
DatabaseTypes dalDbType = DatabaseTypes.SqlCe;
switch (keytype)
{
case IdpeKeyTypes.ConnectionStringSqlCe:
dalDbType = DatabaseTypes.SqlCe;
break;
case IdpeKeyTypes.ConnectionStringSqlServer:
dalDbType = DatabaseTypes.SqlServer;
break;
case IdpeKeyTypes.ConnectionStringOracle:
dalDbType = DatabaseTypes.Oracle;
break;
case IdpeKeyTypes.ConnectionStringDB2iSeries:
dalDbType = DatabaseTypes.DB2iSeries;
break;
}
return(dalDbType);
}
public string GenerateParameter(string connectionString, IdpeKeyTypes databaseType, string paramName, string query)
{
bool isErrored = false;
return(ExecuteQuery(connectionString, databaseType, query, ref isErrored));
}
public SqlClientManager(string defaultConnectionString, IdpeKeyTypes databaseType)
{
_ConnectionString = defaultConnectionString;
_DatabaseType = databaseType;
_Dal = new DataAccessLayer(GetDalDBType(databaseType)).Instance;
}
public int ExecuteNonQuery(string query, bool silent = false, string connectionString = null, IdpeKeyTypes databaseType = IdpeKeyTypes.ConnectionStringSqlCe)
{
string returnString = string.Empty;
IDbConnection con = null;
IDbCommand command = null;
int noOfRowsAffected = 0;
IDal myDal = new DataAccessLayer(Information.EyediaCoreConfigurationSection.Database.DatabaseType).Instance;
string myConnectionString = string.Empty;
if (!string.IsNullOrEmpty(connectionString))
{
myDal = new DataAccessLayer(GetDalDBType(databaseType)).Instance; //use current
myConnectionString = connectionString;
}
else
{
myDal = _Dal; //use default
myConnectionString = _ConnectionString; //use default
}
try
{
con = myDal.CreateConnection(myConnectionString);
con.Open();
command = myDal.CreateCommand(query, con);
noOfRowsAffected = command.ExecuteNonQuery();
}
catch (Exception ex)
{
if (!silent)
{
Trace.TraceError("Error while executing query '{0}'{1}{2} on '{3}'.", query, Environment.NewLine, ex.Message, GetDatabaseName(myConnectionString));
}
}
finally
{
if (con != null)
{
if (con.State != System.Data.ConnectionState.Closed)
{
con.Close();
}
}
command.Dispose();
con.Close();
con.Dispose();
}
return(noOfRowsAffected);
}
public string ExecuteQuery(string connectionString, IdpeKeyTypes databaseType, string query, ref bool isErrored)
{
string returnString = "NULL";
isErrored = false;
IDbConnection con = null;
IDbCommand command = null;
IDataReader reader = null;
IDal myDal = new DataAccessLayer(Information.EyediaCoreConfigurationSection.Database.DatabaseType).Instance;
string myConnectionString = string.Empty;
if (!string.IsNullOrEmpty(connectionString))
{
myDal = new DataAccessLayer(GetDalDBType(databaseType)).Instance; //use current
myConnectionString = connectionString;
}
else
{
myDal = _Dal; //use default
myConnectionString = _ConnectionString; //use default
}
try
{
con = myDal.CreateConnection(myConnectionString);
con.Open();
command = myDal.CreateCommand(query, con);
reader = command.ExecuteReader();
if (reader.Read())
{
if (reader.FieldCount == 1)
{
returnString = reader[0].ToString();
}
else
{
for (int i = 0; i < reader.FieldCount; i++)
{
returnString += reader[i] == null ? "NULL" : reader[i].ToString() + "|";
}
if (returnString.Length > 0)
{
returnString = returnString.Substring(0, returnString.Length - 1);
}
}
}
}
catch (Exception ex)
{
isErrored = true;
Trace.TraceError("Error while executing query '{0}'{1}{2} on '{3}'.", query, Environment.NewLine, ex.Message, GetDatabaseName(myConnectionString));
}
finally
{
if (con != null)
{
if (con.State != System.Data.ConnectionState.Closed)
{
con.Close();
}
}
reader.Dispose();
command.Dispose();
con.Close();
con.Dispose();
}
return(returnString);
}
public void CheckCodeSet(string connectionString, IdpeKeyTypes databaseType, string tableName, string code, ref string value, ref int enumCode, ref string referenceKey, ref string errorMessage)
{
errorMessage = "ERROR";
bool DBReturnedNothing = true;
IDbConnection con = null;
IDbCommand command = null;
IDataReader reader = null;
IDal myDal = null;
string myConnectionString = string.Empty;
if (!string.IsNullOrEmpty(connectionString))
{
myDal = new DataAccessLayer(GetDalDBType(databaseType)).Instance; //use current
myConnectionString = connectionString; //use current
}
else
{
myDal = _Dal; //use default
myConnectionString = _ConnectionString; //use default
}
string commandText = string.Empty;
try
{
con = myDal.CreateConnection(myConnectionString);
con.Open();
commandText = string.Format("select [value],[enumcode],isnull([ReferenceKey],'') from {0} with(nolock) where Code = '{1}'", tableName, code);
command = myDal.CreateCommand(commandText, con);
reader = command.ExecuteReader();
while (reader.Read())
{
DBReturnedNothing = false;
if (reader.GetInt32(1) == enumCode)
{
enumCode = reader.GetInt32(1);
referenceKey = reader.GetString(2);
errorMessage = string.Empty;
break;
}
else if (reader.GetString(0).ToUpper().Equals(value.ToUpper()))
{
enumCode = int.Parse(reader.GetInt32(1).ToString());
referenceKey = reader.GetString(2);
errorMessage = string.Empty;
break;
}
}
if (DBReturnedNothing)
{
//lets trace more information, error is anyway returned back to caller
string errMsg = string.Format("Error: Nothing retrieved from DB while checking codeset with code:'{0}', value:'{1}',table name:'{2}', SQL Query ='{3}'",
code, value, tableName, commandText);
Trace.TraceError(errMsg);
}
}
catch (Exception ex)
{
Trace.TraceError("Error while checking codeset." + Environment.NewLine + ex.Message + Environment.NewLine + ex.StackTrace);
}
finally
{
if (con != null)
{
if (con.State != System.Data.ConnectionState.Closed)
{
con.Close();
}
}
reader.Dispose();
command.Dispose();
con.Close();
con.Dispose();
}
}
private void ExtractHeaderFooter()
{
IdpeKey headerAttribute = DataSource.Key(IdpeKeyTypes.HeaderLine1Attribute);
IdpeKey footerAttribute = DataSource.Key(IdpeKeyTypes.FooterLine1Attribute);
if ((headerAttribute == null) &&
(footerAttribute == null))
{
return;
}
headerAttribute = null;
footerAttribute = null;
try
{
string[] allLines = FileContent.ToString().Split(new string[] { "\n", "\r\n" }, StringSplitOptions.RemoveEmptyEntries);
string line = string.Empty;
int howManyFound = 0;
for (int i = 1; i <= 6; i++)
{
IdpeKeyTypes keyType = (IdpeKeyTypes)Enum.Parse(typeof(IdpeKeyTypes), "HeaderLine" + i + "Attribute");
headerAttribute = DataSource.Key(keyType);
if (headerAttribute != null)
{
line = allLines[i - 1];
ProcessVariables.AddOrUpdate(headerAttribute.Value, line, (key, oldValue) => line);
}
else
{
break;
}
howManyFound++;
}
allLines = allLines.SubArray(howManyFound, allLines.Length - howManyFound);
howManyFound = 0;
for (int i = 6; i >= 1; i--)
{
IdpeKeyTypes keyType = (IdpeKeyTypes)Enum.Parse(typeof(IdpeKeyTypes), "FooterLine" + i + "Attribute");
footerAttribute = DataSource.Key(keyType);
if (footerAttribute == null)
{
continue;
}
else
{
line = allLines[allLines.Length - (howManyFound + 1)];
ProcessVariables.AddOrUpdate(footerAttribute.Value, line, (key, oldValue) => line);
}
howManyFound++;
}
allLines = allLines.SubArray(0, allLines.Length - howManyFound);
FileNameWithoutHeaderAndOrFooter = FileName + ShortGuid.NewGuid().ToString();
using (StreamWriter sw = new StreamWriter(FileNameWithoutHeaderAndOrFooter))
{
for (int i = 0; i < allLines.Length; i++)
{
sw.WriteLine(allLines[i]);
}
}
return;
}
catch (Exception ex)
{
ExtensionMethods.TraceInformation(ex.ToString());
this.Errors.Add("File content is invalid! Please check that file has required header(s) and footer(s) and at least 1 valid record!");
}
}
