public DBDataReader(string process, string sql, string connectionKey, Boolean singleRow, Boolean SingleValue)
{
var formattedSql = DBDataTable.FormatSql(sql);
Log4Net.SqlStatementStack.Push(new Log4Net.SqlStatementStackMessage(formattedSql));
Log4Net.LogDebug(formattedSql, connectionKey);
Connection = StartUp.OpenConnection(connectionKey);
DBCommand = new SqlCommand(formattedSql, Connection);
DBCommand.CommandTimeout = StartUp.TimeOut;
try
{
ReturnValue = DBCommand.ExecuteScalar();
}
catch
{
try
{
if (Connection.State == ConnectionState.Closed)
{
Connection.Open();
}
ReturnValue = DBCommand.ExecuteScalar();
}
catch (Exception ex)
{
LogException(sql, "Scalar", ex, process);
}
}
LogInfo(sql, "Scalar");
}
private static void LoadConnectionStrings(int auditId)
{
ConnectionStrings = new Dictionary <string, ConnectionStringSettings>();
// get application related connection string ids from ConnectionString X Application
var sql = "EXEC dbo.ConnectionStringXApplicationSearch " +
" @AuditId = "+ auditId +
", @ApplicationId = "+ ApplicationId;
var oDT = new DBDataTable("Get List", sql, DataStoreKey);
sql = "EXEC ConnectionStringSearch " +
" @AuditId = "+ auditId;
// Get All Connection Strings
var oConnectionList = new DBDataTable("Get List", sql, DataStoreKey);
// Get related Connection String for each Connection String Id Record
foreach (DataRow dr in oDT.DBTable.Rows)
{
var rows = oConnectionList.DBTable.Select("ConnectionStringId = " + dr["ConnectionStringId"]);
if (rows.Length > 0)
{
// build connection string
var connectionStringSetting = BuildConnectionString(rows[0]);
ConnectionStrings.Add(connectionStringSetting.Name, connectionStringSetting);
//System.Configuration.ConfigurationManager.ConnectionStrings.Add(connectionStringSetting);
}
}
}
public static DataTable GetList()
{
if (string.IsNullOrEmpty(DataStoreKey))
{
DataStoreKey = GetConnectionString("Configuration");
}
var sql = "EXEC dbo.SetUpConfigurationSearch " +
" @ApplicationId = " + ApplicationId;
var oDT = new DBDataTable("Get List", sql, DataStoreKey);
ConnectionKeyDictionary = new Dictionary <string, string>();
for (var i = 0; i < oDT.DBTable.Rows.Count; i++)
{
var key = oDT.DBTable.Rows[i].ItemArray[0].ToString();
var value = oDT.DBTable.Rows[i].ItemArray[1].ToString();
if (!ConnectionKeyDictionary.ContainsKey(key))
{
ConnectionKeyDictionary.Add(value, value);
}
}
return(oDT.DBTable);
}
public static void SetConnectionList(int auditId)
{
if (string.IsNullOrEmpty(DataStoreKey))
{
DataStoreKey = GetConnectionString("Configuration");
}
var sql = "EXEC dbo.SetUpConfigurationSearch " +
" @ApplicationId = " + ApplicationId +
", @AuditId = 5";
var oDT = new DBDataTable("Get List", sql, DataStoreKey);
ConnectionKeyDictionary = new Dictionary <string, string>();
for (var i = 0; i < oDT.DBTable.Rows.Count; i++)
{
var row = oDT.DBTable.Rows[i];
var key = row["EntityName"].ToString();
var value = row["ConnectionKeyName"].ToString();
if (!ConnectionKeyDictionary.ContainsKey(row.ItemArray[0].ToString()))
{
ConnectionKeyDictionary.Add(key, value);
}
}
LoadConnectionStrings(auditId);
}
public static DataTable GetList(int applicationId, string connectionKeyName = "")
{
var sql = string.Empty;
if (string.IsNullOrEmpty(DataStoreKey))
{
DataStoreKey = GetConnectionString("Configuration");
}
sql = "EXEC dbo.SetUpConfigurationSearch ";
if (!string.IsNullOrEmpty(connectionKeyName))
{
sql += " @ConnectionKeyName='" + connectionKeyName + "'";
}
else
{
sql += " @ConnectionKeyName=NULL";
}
if (applicationId != -1)
{
sql += ", @ApplicationId = " + applicationId;
}
else
{
sql += ", @ApplicationId=NULL";
}
var oDT = new DBDataTable("Get List", sql, DataStoreKey);
return(oDT.DBTable);
}
public static DataTable GetByTask(int taskId, int auditId)
{
var sql = "EXEC TaskXCompetencySearch @TaskId =" + taskId + ", " +
" " + ToSQLParameter(BaseDataModel.BaseDataColumns.AuditId, auditId);
var oDT = new Framework.Components.DataAccess.DBDataTable("GetDetails", sql, DataStoreKey);
return(oDT.DBTable);
}
public static DataTable GetByTaskPriority(int taskPriorityId, int auditId)
{
var sql = "EXEC TaskPriorityXApplicationUserDetails @TaskId ="+ taskPriorityId + ", " +
"@AuditId ="+ auditId;
var oDT = new Framework.Components.DataAccess.DBDataTable("GetDetails", sql, DataStoreKey);
return(oDT.DBTable);
}
public static DataTable GetByApplicationUser(int applicationUserId, int auditId)
{
var sql = "EXEC FieldConfigurationModeXApplicationUserSearch @ApplicationUserId =" + applicationUserId + ", " +
" " + ToSQLParameter(BaseModel.BaseDataColumns.AuditId, auditId);
var oDT = new Framework.Components.DataAccess.DBDataTable("GetDetails", sql, DataStoreKey);
return(oDT.DBTable);
}
public static DataTable GetByUseCase(int UseCaseId, int auditId)
{
var sql = "EXEC UseCaseXUseCaseStepSearch @UseCaseId =" + UseCaseId + ", " +
" " + ToSQLParameter(BaseDataModel.BaseDataColumns.AuditId, auditId);
var oDT = new Framework.Components.DataAccess.DBDataTable("GetDetails", sql, DataStoreKey);
return(oDT.DBTable);
}
public static DataTable GetByNotificationSubscriber(int NotificationSubscriberId, RequestProfile requestProfile)
{
var sql = "EXEC NotificationSubscriberXEventTypeSearch @NotificationSubscriberId =" + NotificationSubscriberId + ", " +
"@AuditId=" + requestProfile.AuditId + ", " +
"@ApplicationId=" + requestProfile.ApplicationId;
var oDT = new Framework.Components.DataAccess.DBDataTable("GetDetails", sql, DataStoreKey);
return(oDT.DBTable);
}
public static DataTable GetList(RequestProfile requestProfile)
{
var sql = "EXEC dbo.ProjectXUseCaseSearch " +
" " + ToSQLParameter(BaseDataModel.BaseDataColumns.AuditId, requestProfile.AuditId);
var oDT = new Framework.Components.DataAccess.DBDataTable("ProjectXUseCase.List", sql, DataStoreKey);
return(oDT.DBTable);
}
static public DataTable GetList(int auditId)
{
var sql = "EXEC dbo.ApplicationList" +
" " + BaseColumns.ToSQLParameter(BaseColumns.AuditId, auditId);
var oDT = new Framework.Components.DataAccess.DBDataTable("Application.List", sql, DataStoreKey);
return(oDT.DBTable);
}
public static DataTable GetList(int auditId, int applicationId, string dataStoreKey, string storedProcedrue)
{
var sql = "EXEC " + storedProcedrue +
" " + SetCommonParameters(auditId, applicationId);
var oDT = new DBDataTable("Get List", sql, dataStoreKey);
return(oDT.DBTable);
}
public static DataTable GetDetails(FunctionalityImageXFunctionalityImageAttributeDataModel data, int auditId)
{
var sql = "EXEC dbo.FunctionalityImageXFunctionalityImageAttributeDetails " +
" " + ToSQLParameter(BaseDataModel.BaseDataColumns.AuditId, auditId) +
", " + ToSQLParameter(data, FunctionalityImageXFunctionalityImageAttributeDataModel.DataColumns.FunctionalityImageXFunctionalityImageAttributeId);
var oDT = new Framework.Components.DataAccess.DBDataTable("FunctionalityImageXFunctionalityImageAttribute.Details", sql, DataStoreKey);
return(oDT.DBTable);
}
public static DataTable GetList(int auditId)
{
var sql = "EXEC dbo.FunctionalityImageXFunctionalityImageAttributeList" +
" " + ToSQLParameter(BaseDataModel.BaseDataColumns.AuditId, auditId) +
", " + ToSQLParameter(BaseDataModel.BaseDataColumns.ApplicationId, ApplicationId);
var oDT = new Framework.Components.DataAccess.DBDataTable("FunctionalityImageXFunctionalityImageAttribute.List", sql, DataStoreKey);
return(oDT.DBTable);
}
public static DataTable GetList(RequestProfile requestProfile)
{
var sql = "EXEC dbo.NotificationRegistrarSearch " +
" " + ToSQLParameter(BaseDataModel.BaseDataColumns.AuditId, requestProfile.AuditId) +
", " + ToSQLParameter(BaseDataModel.BaseDataColumns.ApplicationId, requestProfile.ApplicationId);
var oDT = new Framework.Components.DataAccess.DBDataTable("NotificationRegistrar.List", sql, DataStoreKey);
return(oDT.DBTable);
}
public static DataTable GetDetails(DomainModel.FieldConfigurationModeXApplicationUser data, int auditId)
{
var sql = "EXEC dbo.FieldConfigurationModeXApplicationUserDetails " +
" " + ToSQLParameter(BaseModel.BaseDataColumns.AuditId, auditId) +
", " + ToSQLParameter(data, DomainModel.FieldConfigurationModeXApplicationUser.DataColumns.FieldConfigurationModeXApplicationUserId);
var oDT = new Framework.Components.DataAccess.DBDataTable("FieldConfigurationModeXApplicationUser.Details", sql, DataStoreKey);
return(oDT.DBTable);
}
public static DataTable GetList(int auditId)
{
var sql = "EXEC dbo.FieldConfigurationModeXApplicationUserList" +
" " + ToSQLParameter(BaseModel.BaseDataColumns.AuditId, auditId) +
", " + ToSQLParameter(BaseModel.BaseDataColumns.ApplicationId, ApplicationId);
var oDT = new Framework.Components.DataAccess.DBDataTable("FieldConfigurationModeXApplicationUser.List", sql, DataStoreKey);
return(oDT.DBTable);
}
public static DataTable GetList(int applicationId, RequestProfile requestProfile)
{
var sql = "EXEC dbo.TaskEntityTypeList "
+ " " + ToSQLParameter(BaseDataModel.BaseDataColumns.ApplicationId, requestProfile.ApplicationId)
+ "," + ToSQLParameter(BaseDataModel.BaseDataColumns.AuditId, requestProfile.AuditId);
var oDT = new Framework.Components.DataAccess.DBDataTable("Get List", sql, DataStoreKey);
return(oDT.DBTable);
}
static public DataTable GetDetails(Data data, int auditId)
{
var sql = "EXEC dbo.ApplicationUserDetails " +
" " + BaseColumns.ToSQLParameter(BaseColumns.AuditId, auditId) +
", " + data.ToSQLParameter(DataColumns.ApplicationUserId);
var oDT = new Framework.Components.DataAccess.DBDataTable("ApplicationUser.Details", sql, DataStoreKey);
return(oDT.DBTable);
}
public static DataTable GetPublishDetails(decimal maxcreatedDateid)
{
var sql = "EXEC dbo.NotificationRegistrarPublishSearch " +
" @MaxPublishDate=" + maxcreatedDateid;
var oDT = new Framework.Components.DataAccess.DBDataTable("NotificationRegistrar.PublishDetails", sql, DataStoreKey);
return(oDT.DBTable);
}
static public DataTable DoesExist(Data data, int auditId)
{
var sql = "EXEC dbo.ApplicationDoesExist " +
" " + BaseColumns.ToSQLParameter(BaseColumns.AuditId, auditId) +
", " + data.ToSQLParameter(DataColumns.ApplicationId) +
", " + data.ToSQLParameter(DataColumns.Name);
var oDT = new Framework.Components.DataAccess.DBDataTable("Application.DoesExist", sql, DataStoreKey);
return(oDT.DBTable);
}
public static DataTable GetDetails(Data data, RequestProfile requestProfile)
{
var sql = "EXEC dbo.NotificationPublisherXNotificationEventTypeSearch " +
" " + ToSQLParameter(BaseDataModel.BaseDataColumns.AuditId, requestProfile.AuditId) +
", " + ToSQLParameter(BaseDataModel.BaseDataColumns.ReturnAuditInfo, BaseDataManager.ReturnAuditInfoOnDetails) +
", " + data.ToSQLParameter(DataColumns.NotificationPublisherXNotificationEventTypeId);
var oDT = new Framework.Components.DataAccess.DBDataTable("NotificationPublisherXNotificationEventType.Details", sql, DataStoreKey);
return(oDT.DBTable);
}
public static bool DoesExist(TaskXCompetencyDataModel data, RequestProfile requestProfile)
{
var sql = "EXEC dbo.TaskXCompetencySearch " +
" " + ToSQLParameter(BaseDataModel.BaseDataColumns.AuditId, requestProfile.AuditId) +
", " + ToSQLParameter(BaseDataModel.BaseDataColumns.ApplicationId, requestProfile.ApplicationId) +
", " + ToSQLParameter(data, TaskXCompetencyDataModel.DataColumns.TaskXCompetencyId);
var oDT = new Framework.Components.DataAccess.DBDataTable("TaskXCompetency.DoesExist", sql, DataStoreKey);
return(oDT.DBTable.Rows.Count > 0);
}
public static bool DoesExist(SkillXPersonXSkillLevelDataModel data, RequestProfile requestProfile)
{
var sql = "EXEC dbo.SkillXPersonXSkillLevelSearch " +
" " + ToSQLParameter(BaseDataModel.BaseDataColumns.AuditId, requestProfile.AuditId) +
", " + ToSQLParameter(BaseDataModel.BaseDataColumns.ApplicationId, requestProfile.ApplicationId) +
", " + ToSQLParameter(data, SkillXPersonXSkillLevelDataModel.DataColumns.SkillXPersonXSkillLevelId);
var oDT = new Framework.Components.DataAccess.DBDataTable("SkillXPersonXSkillLevel.DoesExist", sql, DataStoreKey);
return(oDT.DBTable.Rows.Count > 0);
}
public static DataTable DoesExist(CompetencyXSkillDataModel data, RequestProfile requestProfile)
{
var sql = "EXEC dbo.CompetencyXSkillSearch " +
" " + ToSQLParameter(BaseDataModel.BaseDataColumns.AuditId, requestProfile.AuditId) +
", " + ToSQLParameter(data, CompetencyXSkillDataModel.DataColumns.SkillId) +
", " + ToSQLParameter(data, CompetencyXSkillDataModel.DataColumns.CompetencyId);
var oDT = new Framework.Components.DataAccess.DBDataTable("CompetencyXSkill.DoesExist", sql, DataStoreKey);
return(oDT.DBTable);
}
public static DataTable GetDetails(Data data, RequestProfile requestProfile)
{
var sql = "EXEC dbo.TaskRiskRewardRankingXPersonSearch " +
" " + ToSQLParameter(BaseDataModel.BaseDataColumns.AuditId, requestProfile.AuditId) +
", " + ToSQLParameter(BaseDataModel.BaseDataColumns.ReturnAuditInfo, BaseDataManager.ReturnAuditInfoOnDetails) +
", " + data.ToSQLParameter(DataColumns.TaskRiskRewardRankingXPersonId);
var oDT = new Framework.Components.DataAccess.DBDataTable("TaskRiskRewardRankingXPerson.Details", sql, DataStoreKey);
return(oDT.DBTable);
}
public static DataTable GetDetails(ProjectXUseCaseDataModel data, RequestProfile requestProfile)
{
var sql = "EXEC dbo.ProjectXUseCaseSearch " +
" " + ToSQLParameter(BaseDataModel.BaseDataColumns.AuditId, requestProfile.AuditId) +
", " + ToSQLParameter(BaseDataModel.BaseDataColumns.ReturnAuditInfo, BaseDataManager.ReturnAuditInfoOnDetails) +
", " + ToSQLParameter(data, ProjectXUseCaseDataModel.DataColumns.ProjectXUseCaseId);
var oDT = new Framework.Components.DataAccess.DBDataTable("ProjectXUseCase.Details", sql, DataStoreKey);
return(oDT.DBTable);
}
public static DataTable Search(string entityName, RequestProfile requestProfile)
{
// formulate SQL
var sql = "EXEC dbo.SetUpConfigurationSearch " +
"@AuditId=" + requestProfile.AuditId +
",@EntityName='" + entityName + "'";
var oDT = new DBDataTable("SetUpConfiguration.Search", sql, DataStoreKey);
return(oDT.DBTable);
}
public static bool DoesExist(Data data, RequestProfile requestProfile)
{
var sql = "EXEC dbo.NotificationPublisherXNotificationEventTypeSearch " +
" " + ToSQLParameter(BaseDataModel.BaseDataColumns.AuditId, requestProfile.AuditId) +
", " + ToSQLParameter(BaseDataModel.BaseDataColumns.ApplicationId, requestProfile.ApplicationId) +
", " + data.ToSQLParameter(DataColumns.NotificationPublisherXNotificationEventTypeId);
var oDT = new Framework.Components.DataAccess.DBDataTable("NotificationPublisherXNotificationEventType.DoesExist", sql, DataStoreKey);
return(oDT.DBTable.Rows.Count > 0);
}
