private static string Save(UserLoginHistoryDataModel data, RequestProfile requestProfile, string action)
{
var sql = "EXEC ";
switch (action)
{
case "Create":
sql += "dbo.UserLoginHistoryInsert " +
" " + ToSQLParameter(BaseDataModel.BaseDataColumns.AuditId, requestProfile.AuditId) +
", " + ToSQLParameter(BaseDataModel.BaseDataColumns.ApplicationId, requestProfile.ApplicationId);
break;
case "Update":
sql += "dbo.UserLoginHistoryUpdate " +
" " + ToSQLParameter(BaseDataModel.BaseDataColumns.AuditId, requestProfile.AuditId);
break;
default:
break;
}
sql = sql + ", " + ToSQLParameter(data, UserLoginHistoryDataModel.DataColumns.UserLoginHistoryId) +
", " + ToSQLParameter(data, UserLoginHistoryDataModel.DataColumns.URL) +
", " + ToSQLParameter(data, UserLoginHistoryDataModel.DataColumns.ServerName) +
", " + ToSQLParameter(data, UserLoginHistoryDataModel.DataColumns.UserName) +
", " + ToSQLParameter(data, UserLoginHistoryDataModel.DataColumns.UserId) +
", @Date = '" + DateTime.Now + "'";
return(sql);
}
public static void Create(UserLoginHistoryDataModel data, RequestProfile requestProfile)
{
var sql = Save(data, requestProfile, "Create");
DBDML.RunSQL("UserLoginHistory.Insert", sql, DataStoreKey);
UserLoginHistoryMongoDbDataManager.Create(data, requestProfile);
}
public static bool DoesExist(UserLoginHistoryDataModel data, RequestProfile requestProfile)
{
var sql = "EXEC dbo.UserLoginHistorySearch " +
" " + ToSQLParameter(BaseDataModel.BaseDataColumns.AuditId, requestProfile.AuditId) +
", " + ToSQLParameter(BaseDataModel.BaseDataColumns.ApplicationId, requestProfile.ApplicationId) +
", " + ToSQLParameter(data, UserLoginHistoryDataModel.DataColumns.UserLoginHistoryId);
var oDT = new DBDataTable("UserLoginHistory.DoesExist", sql, DataStoreKey);
return(oDT.DBTable.Rows.Count > 0);
}
protected void btnSearch_Click(object sender, EventArgs e)
{
var data = new Framework.Components.LogAndTrace.UserLoginHistoryDataModel();
data.ServerName = txtServerName.Text;
data.UserName = txtUserName.Text;
var userdata = UserLoginHistoryMongoDbDataManager.GetList(data, SessionVariables.RequestProfile);
gridUserLogin.DataSource = userdata;
gridUserLogin.DataBind();
}
public static void Create(UserLoginHistoryDataModel data, RequestProfile requestProfile)
{
var userLogin = database.GetCollection <BsonDocument>("UserLoginHistory");
BsonDocument dataCollection = new BsonDocument {
{ UserLoginHistoryDataModel.DataColumns.Application, data.Application },
{ UserLoginHistoryDataModel.DataColumns.UserName, data.UserName },
{ UserLoginHistoryDataModel.DataColumns.URL, data.URL },
{ UserLoginHistoryDataModel.DataColumns.DateVisited, DateTime.Now },
{ UserLoginHistoryDataModel.DataColumns.ServerName, data.ServerName },
};
userLogin.InsertOneAsync(dataCollection);
}
public static DataTable Search(UserLoginHistoryDataModel data, RequestProfile requestProfile)
{
// formulate SQL
var sql = "EXEC dbo.UserLoginHistorySearch " +
" " + ToSQLParameter(BaseDataModel.BaseDataColumns.AuditId, requestProfile.AuditId) +
", " + ToSQLParameter(BaseDataModel.BaseDataColumns.ApplicationId, requestProfile.ApplicationId) +
", " + ToSQLParameter(data, UserLoginHistoryDataModel.DataColumns.UserLoginHistoryId) +
", " + ToSQLParameter(data, UserLoginHistoryDataModel.DataColumns.UserName) +
", " + ToSQLParameter(data, UserLoginHistoryDataModel.DataColumns.UserId) +
", " + ToSQLParameter(data, UserLoginHistoryDataModel.DataColumns.FromSearchDate) +
", " + ToSQLParameter(data, UserLoginHistoryDataModel.DataColumns.ToSearchDate);
var oDT = new DBDataTable("UserLoginHistory.Search", sql, DataStoreKey);
return(oDT.DBTable);
}
public static void Delete(UserLoginHistoryDataModel data, RequestProfile requestProfile)
{
const string sql = @"dbo.UserLoginHistoryDelete ";
var parameters = new
{
AuditId = requestProfile.AuditId
,
UserLoginHistoryId = data.UserLoginHistoryId
};
using (var dataAccess = new DataAccessBase(DataStoreKey))
{
dataAccess.Connection.Execute(sql, parameters, commandType: CommandType.StoredProcedure);
}
}
public static List <UserLoginHistoryDataModel> GetLastUrls(UserLoginHistoryDataModel data, int numOfRecords, RequestProfile requestProfile)
{
var SQLKey = ".SQL.GetLastUrls.sql";
// Get SQL Template and replace parameters
var assembly = Assembly.GetExecutingAssembly();
var scriptTemplate = GetResourceText(assembly, SQLKey);
var replacementFieldSet = new Dictionary <string, string>();
var replacementOtherSet = new Dictionary <string, string>();
replacementOtherSet.Add("@numOfRecords@", numOfRecords.ToString(CultureInfo.InvariantCulture));
replacementOtherSet.Add("@UserId@", data.UserId.ToString());
var sql = GetSQL(replacementFieldSet, replacementOtherSet, scriptTemplate, assembly.GetName().Name + SQLKey);
// get data
var result = new List <UserLoginHistoryDataModel>();
using (var reader = new DBDataReader("Get Details", sql, DataStoreKey))
{
var dbReader = reader.DBReader;
while (dbReader.Read())
{
var dataItem = new UserLoginHistoryDataModel();
dataItem.UserLoginHistoryId = (int)dbReader[UserLoginHistoryDataModel.DataColumns.UserLoginHistoryId];
dataItem.UserId = (int)dbReader[UserLoginHistoryDataModel.DataColumns.UserId];
dataItem.UserName = dbReader[UserLoginHistoryDataModel.DataColumns.UserName].ToString();
dataItem.ServerName = dbReader[UserLoginHistoryDataModel.DataColumns.ServerName].ToString();
dataItem.URL = dbReader[UserLoginHistoryDataModel.DataColumns.URL].ToString();
dataItem.DateVisited = (DateTime)dbReader[UserLoginHistoryDataModel.DataColumns.DateVisited];
result.Add(dataItem);
}
}
result.Reverse();
return(result);
}
public static List <UserLoginHistoryDataModel> GetEntityDetails(UserLoginHistoryDataModel dataQuery, RequestProfile requestProfile, int applicationModeId = 0)
{
const string sql = @"dbo.UserLoginHistorySearch ";
var parameters =
new
{
AuditId = requestProfile.AuditId
, UserLoginHistoryId = dataQuery.UserLoginHistoryId
, ReturnAuditInfo = ReturnAuditInfoOnDetails
};
List <UserLoginHistoryDataModel> result;
using (var dataAccess = new DataAccessBase(DataStoreKey))
{
result = dataAccess.Connection.Query <UserLoginHistoryDataModel>(sql, parameters, commandType: CommandType.StoredProcedure).ToList();
}
return(result);
}
public static List <UserLoginHistoryDataModel> GetList(UserLoginHistoryDataModel data, RequestProfile requestProfile)
{
var listUserLoginHistory = database.GetCollection <BsonDocument>("UserLoginHistory");
var listUserHistoryData = new List <BsonDocument>();
var builder = Builders <BsonDocument> .Filter;
var resultList = new List <UserLoginHistoryDataModel>();
if (string.IsNullOrEmpty(data.UserName) && string.IsNullOrEmpty(data.ServerName))
{
resultList = GetList();
}
else
{
if (!string.IsNullOrEmpty(data.UserName) && !string.IsNullOrEmpty(data.ServerName))
{
var filter = builder.Eq(UserLoginHistoryDataModel.DataColumns.UserName, data.UserName) &
builder.Eq(UserLoginHistoryDataModel.DataColumns.ServerName, data.ServerName);
listUserHistoryData = listUserLoginHistory.Find(filter).ToListAsync().Result;
}
else if (!string.IsNullOrEmpty(data.UserName) || !string.IsNullOrEmpty(data.ServerName))
{
var filter = builder.Eq(UserLoginHistoryDataModel.DataColumns.UserName, data.UserName) |
builder.Eq(UserLoginHistoryDataModel.DataColumns.ServerName, data.ServerName);
listUserHistoryData = listUserLoginHistory.Find(filter).ToListAsync().Result;
}
if (listUserHistoryData != null)
{
foreach (BsonDocument obj in listUserHistoryData) // Loop thru all Bson documents returned from the query.
{
resultList.Add(MapToUserLoginHistoryRecord(obj));
}
}
}
return(resultList);
}
public static UserLoginHistoryDataModel MapToUserLoginHistoryRecord(BsonDocument obj)
{
var userLoginHistory = new UserLoginHistoryDataModel();
try
{
if (obj.Contains("ApplicationId"))
{
userLoginHistory.ApplicationId = obj["ApplicationId"].AsInt32;
}
if (obj.Contains("Application"))
{
userLoginHistory.Application = obj["Application"].AsString;
}
userLoginHistory.URL = obj["URL"].AsString;
userLoginHistory.ServerName = obj["ServerName"].AsString;
userLoginHistory.UserName = obj["UserName"].AsString;
userLoginHistory.DateVisited = obj["DateVisited"].ToUniversalTime();
}
catch { }
return(userLoginHistory);
}
public static List <UserLoginHistoryDataModel> GetEntityDetails(UserLoginHistoryDataModel data, RequestProfile requestProfile)
{
var sql = "EXEC dbo.UserLoginHistorySearch " +
" " + ToSQLParameter(BaseDataModel.BaseDataColumns.AuditId, requestProfile.AuditId) +
", " + ToSQLParameter(BaseDataModel.BaseDataColumns.ApplicationId, requestProfile.ApplicationId) +
", " + ToSQLParameter(data, UserLoginHistoryDataModel.DataColumns.UserLoginHistoryId) +
", " + ToSQLParameter(data, UserLoginHistoryDataModel.DataColumns.UserName) +
", " + ToSQLParameter(data, UserLoginHistoryDataModel.DataColumns.UserId) +
", " + ToSQLParameter(data, UserLoginHistoryDataModel.DataColumns.FromSearchDate) +
", " + ToSQLParameter(data, UserLoginHistoryDataModel.DataColumns.ToSearchDate);
var result = new List <UserLoginHistoryDataModel>();
using (var reader = new DBDataReader("Get Details", sql, DataStoreKey))
{
var dbReader = reader.DBReader;
while (dbReader.Read())
{
var dataItem = new UserLoginHistoryDataModel();
dataItem.UserLoginHistoryId = (int)dbReader[UserLoginHistoryDataModel.DataColumns.UserLoginHistoryId];
dataItem.UserId = (int)dbReader[UserLoginHistoryDataModel.DataColumns.UserId];
dataItem.UserName = dbReader[UserLoginHistoryDataModel.DataColumns.UserName].ToString();
dataItem.ServerName = dbReader[UserLoginHistoryDataModel.DataColumns.ServerName].ToString();
dataItem.URL = dbReader[UserLoginHistoryDataModel.DataColumns.URL].ToString();
dataItem.DateVisited = (DateTime)dbReader[UserLoginHistoryDataModel.DataColumns.DateVisited];
//SetBaseInfo(dataItem, dbReader);
result.Add(dataItem);
}
}
return(result);
}
public static string ToSQLParameter(UserLoginHistoryDataModel data, string dataColumnUserName)
{
var returnValue = "NULL";
switch (dataColumnUserName)
{
case UserLoginHistoryDataModel.DataColumns.UserLoginHistoryId:
if (data.UserLoginHistoryId != null)
{
returnValue = string.Format(SQL_TEMPLATE_PARAMETER_NUMBER, UserLoginHistoryDataModel.DataColumns.UserLoginHistoryId, data.UserLoginHistoryId);
}
else
{
returnValue = string.Format(SQL_TEMPLATE_PARAMETER_NULL, UserLoginHistoryDataModel.DataColumns.UserLoginHistoryId);
}
break;
case UserLoginHistoryDataModel.DataColumns.UserName:
if (!string.IsNullOrEmpty(data.UserName))
{
returnValue = string.Format(SQL_TEMPLATE_PARAMETER_STRING_OR_DATE, UserLoginHistoryDataModel.DataColumns.UserName, data.UserName.Trim());
}
else
{
returnValue = string.Format(SQL_TEMPLATE_PARAMETER_NULL, UserLoginHistoryDataModel.DataColumns.UserName);
}
break;
case UserLoginHistoryDataModel.DataColumns.URL:
if (!string.IsNullOrEmpty(data.URL))
{
returnValue = string.Format(SQL_TEMPLATE_PARAMETER_STRING_OR_DATE, UserLoginHistoryDataModel.DataColumns.URL, data.URL.Trim());
}
else
{
returnValue = string.Format(SQL_TEMPLATE_PARAMETER_NULL, UserLoginHistoryDataModel.DataColumns.URL);
}
break;
case UserLoginHistoryDataModel.DataColumns.DateVisited:
if (data.DateVisited != null)
{
returnValue = string.Format(SQL_TEMPLATE_PARAMETER_STRING_OR_DATE, UserLoginHistoryDataModel.DataColumns.DateVisited, data.DateVisited);
}
else
{
returnValue = string.Format(SQL_TEMPLATE_PARAMETER_NULL, UserLoginHistoryDataModel.DataColumns.DateVisited);
}
break;
case UserLoginHistoryDataModel.DataColumns.ServerName:
if (!string.IsNullOrEmpty(data.ServerName))
{
returnValue = string.Format(SQL_TEMPLATE_PARAMETER_STRING_OR_DATE, UserLoginHistoryDataModel.DataColumns.ServerName, data.ServerName.Trim());
}
else
{
returnValue = string.Format(SQL_TEMPLATE_PARAMETER_NULL, UserLoginHistoryDataModel.DataColumns.ServerName);
}
break;
case UserLoginHistoryDataModel.DataColumns.UserId:
if (data.UserId != null)
{
returnValue = string.Format(SQL_TEMPLATE_PARAMETER_NUMBER, UserLoginHistoryDataModel.DataColumns.UserId, data.UserId);
}
else
{
returnValue = string.Format(SQL_TEMPLATE_PARAMETER_NULL, UserLoginHistoryDataModel.DataColumns.UserId);
}
break;
case UserLoginHistoryDataModel.DataColumns.FromSearchDate:
if (data.FromSearchDate != null)
{
returnValue = string.Format(SQL_TEMPLATE_PARAMETER_STRING_OR_DATE, UserLoginHistoryDataModel.DataColumns.FromSearchDate, data.FromSearchDate);
}
else
{
returnValue = string.Format(SQL_TEMPLATE_PARAMETER_NULL, UserLoginHistoryDataModel.DataColumns.FromSearchDate);
}
break;
case UserLoginHistoryDataModel.DataColumns.ToSearchDate:
if (data.ToSearchDate != null)
{
returnValue = string.Format(SQL_TEMPLATE_PARAMETER_STRING_OR_DATE, UserLoginHistoryDataModel.DataColumns.ToSearchDate, data.ToSearchDate);
}
else
{
returnValue = string.Format(SQL_TEMPLATE_PARAMETER_NULL, UserLoginHistoryDataModel.DataColumns.ToSearchDate);
}
break;
}
return(returnValue);
}
public static void Update(UserLoginHistoryDataModel data, RequestProfile requestProfile)
{
var sql = Save(data, requestProfile, "Update");
DBDML.RunSQL("UserLoginHistory.Update", sql, DataStoreKey);
}
public static UserLoginHistoryDataModel GetDetails(UserLoginHistoryDataModel data, RequestProfile requestProfile)
{
var list = GetEntityDetails(data, requestProfile, 1);
return(list.FirstOrDefault());
}
