public static void UpdateWithPassword(CBE.UserCBE user)
{
try
{
string spName = Constants.oraclePackagePrefix + "User_UpdateWithPassword";
DbCommand command = VaaaN.MLFF.Libraries.CommonLibrary.DBA.DBAccessor.GetStoredProcCommand(spName);
command.Parameters.Add(VaaaN.MLFF.Libraries.CommonLibrary.DBA.DBAccessor.CreateDbParameter(ref command, "p_user_id", DbType.Int32, user.UserId, ParameterDirection.Input));
command.Parameters.Add(VaaaN.MLFF.Libraries.CommonLibrary.DBA.DBAccessor.CreateDbParameter(ref command, "p_login_name", DbType.String, user.LoginName, ParameterDirection.Input, 100));
command.Parameters.Add(VaaaN.MLFF.Libraries.CommonLibrary.DBA.DBAccessor.CreateDbParameter(ref command, "p_first_name", DbType.String, user.FirstName, ParameterDirection.Input, 100));
command.Parameters.Add(VaaaN.MLFF.Libraries.CommonLibrary.DBA.DBAccessor.CreateDbParameter(ref command, "p_last_name", DbType.String, user.LastName, ParameterDirection.Input, 100));
command.Parameters.Add(VaaaN.MLFF.Libraries.CommonLibrary.DBA.DBAccessor.CreateDbParameter(ref command, "p_description", DbType.String, user.Description, ParameterDirection.Input, 100));
command.Parameters.Add(VaaaN.MLFF.Libraries.CommonLibrary.DBA.DBAccessor.CreateDbParameter(ref command, "p_password", DbType.String, user.Password, ParameterDirection.Input, 100));
command.Parameters.Add(VaaaN.MLFF.Libraries.CommonLibrary.DBA.DBAccessor.CreateDbParameter(ref command, "p_address", DbType.String, user.Address, ParameterDirection.Input, 200));
command.Parameters.Add(VaaaN.MLFF.Libraries.CommonLibrary.DBA.DBAccessor.CreateDbParameter(ref command, "p_role_id", DbType.Int32, user.RoleId, ParameterDirection.Input));
command.Parameters.Add(VaaaN.MLFF.Libraries.CommonLibrary.DBA.DBAccessor.CreateDbParameter(ref command, "p_acc_expiry_date", DbType.DateTime, user.AccountExpiryDate, ParameterDirection.Input));
command.Parameters.Add(VaaaN.MLFF.Libraries.CommonLibrary.DBA.DBAccessor.CreateDbParameter(ref command, "p_modifier_id", DbType.Int32, user.ModifierId, ParameterDirection.Input));
command.Parameters.Add(VaaaN.MLFF.Libraries.CommonLibrary.DBA.DBAccessor.CreateDbParameter(ref command, "p_creation_date", DbType.DateTime, user.CreationDate, ParameterDirection.Input));
command.Parameters.Add(VaaaN.MLFF.Libraries.CommonLibrary.DBA.DBAccessor.CreateDbParameter(ref command, "p_modification_date", DbType.DateTime, user.ModificationDate, ParameterDirection.Input));
command.Parameters.Add(VaaaN.MLFF.Libraries.CommonLibrary.DBA.DBAccessor.CreateDbParameter(ref command, "p_transfer_status", DbType.Int32, (int)VaaaN.MLFF.Libraries.CommonLibrary.Constants.TransferStatus.NotTransferred, ParameterDirection.Input));
command.Parameters.Add(VaaaN.MLFF.Libraries.CommonLibrary.DBA.DBAccessor.CreateDbParameter(ref command, "p_finger_print_1", DbType.String, user.FingerPrint1, ParameterDirection.Input));
VaaaN.MLFF.Libraries.CommonLibrary.DBA.DBAccessor.ExecuteNonQuery(command);
}
catch (Exception ex)
{
throw ex;
}
}
public static void Update(CBE.UserCBE user)
{
try
{
string spName = Constants.oraclePackagePrefix + "User_Update";
DbCommand command = VaaaN.MLFF.Libraries.CommonLibrary.DBA.DBAccessor.GetStoredProcCommand(spName);
// command.Parameters.Add(VaaaN.MLFF.Libraries.CommonLibrary.DBA.DBAccessor.CreateDbParameter(ref command, "p_atms_id", DbType.Int32, Constants.GetCurrentTMSId(), ParameterDirection.Input));
command.Parameters.Add(VaaaN.MLFF.Libraries.CommonLibrary.DBA.DBAccessor.CreateDbParameter(ref command, "p_user_id", DbType.Int32, user.UserId, ParameterDirection.Input));
command.Parameters.Add(VaaaN.MLFF.Libraries.CommonLibrary.DBA.DBAccessor.CreateDbParameter(ref command, "p_login_name", DbType.String, user.LoginName, ParameterDirection.Input, 100));
command.Parameters.Add(VaaaN.MLFF.Libraries.CommonLibrary.DBA.DBAccessor.CreateDbParameter(ref command, "p_first_name", DbType.String, user.FirstName, ParameterDirection.Input, 100));
command.Parameters.Add(VaaaN.MLFF.Libraries.CommonLibrary.DBA.DBAccessor.CreateDbParameter(ref command, "p_last_name", DbType.String, user.LastName, ParameterDirection.Input, 100));
command.Parameters.Add(VaaaN.MLFF.Libraries.CommonLibrary.DBA.DBAccessor.CreateDbParameter(ref command, "p_description", DbType.String, user.Description, ParameterDirection.Input, 100));
command.Parameters.Add(VaaaN.MLFF.Libraries.CommonLibrary.DBA.DBAccessor.CreateDbParameter(ref command, "p_address", DbType.String, user.Address, ParameterDirection.Input, 200));
command.Parameters.Add(VaaaN.MLFF.Libraries.CommonLibrary.DBA.DBAccessor.CreateDbParameter(ref command, "p_role_id", DbType.Int32, user.RoleId, ParameterDirection.Input));
command.Parameters.Add(VaaaN.MLFF.Libraries.CommonLibrary.DBA.DBAccessor.CreateDbParameter(ref command, "p_acc_expiry_date", DbType.DateTime, user.AccountExpiryDate, ParameterDirection.Input));
command.Parameters.Add(VaaaN.MLFF.Libraries.CommonLibrary.DBA.DBAccessor.CreateDbParameter(ref command, "p_modifier_id", DbType.Int32, user.ModifierId, ParameterDirection.Input));
command.Parameters.Add(VaaaN.MLFF.Libraries.CommonLibrary.DBA.DBAccessor.CreateDbParameter(ref command, "p_creation_date", DbType.DateTime, user.CreationDate, ParameterDirection.Input));
command.Parameters.Add(VaaaN.MLFF.Libraries.CommonLibrary.DBA.DBAccessor.CreateDbParameter(ref command, "p_modification_date", DbType.DateTime, user.ModificationDate, ParameterDirection.Input));
command.Parameters.Add(VaaaN.MLFF.Libraries.CommonLibrary.DBA.DBAccessor.CreateDbParameter(ref command, "p_finger_print_1", DbType.String, user.FingerPrint1, ParameterDirection.Input));
command.Parameters.Add(VaaaN.MLFF.Libraries.CommonLibrary.DBA.DBAccessor.CreateDbParameter(ref command, "P_USER_STATUS", DbType.Int32, user.user_status, ParameterDirection.Input));
command.Parameters.Add(VaaaN.MLFF.Libraries.CommonLibrary.DBA.DBAccessor.CreateDbParameter(ref command, "P_MOBILE_NO", DbType.String, user.MobileNo, ParameterDirection.Input, 10));
command.Parameters.Add(VaaaN.MLFF.Libraries.CommonLibrary.DBA.DBAccessor.CreateDbParameter(ref command, "P_EMAIL_ID", DbType.String, user.EmailId, ParameterDirection.Input, 50));
command.Parameters.Add(VaaaN.MLFF.Libraries.CommonLibrary.DBA.DBAccessor.CreateDbParameter(ref command, "P_DOB", DbType.DateTime, user.UserDob, ParameterDirection.Input));
VaaaN.MLFF.Libraries.CommonLibrary.DBA.DBAccessor.ExecuteNonQuery(command);
}
catch (Exception ex)
{
throw ex;
}
}
public static void Delete(CBE.UserCBE user)
{
try
{
string spName = Constants.oraclePackagePrefix + "User_Delete";
DbCommand command = VaaaN.MLFF.Libraries.CommonLibrary.DBA.DBAccessor.GetStoredProcCommand(spName);
command.Parameters.Add(VaaaN.MLFF.Libraries.CommonLibrary.DBA.DBAccessor.CreateDbParameter(ref command, "p_user_id", DbType.Int32, user.UserId, ParameterDirection.Input));
VaaaN.MLFF.Libraries.CommonLibrary.DBA.DBAccessor.ExecuteNonQuery(command);
}
catch (Exception ex)
{
throw ex;
}
}
public static CBE.UserCBE GetUserById(CBE.UserCBE user)
{
try
{
CBE.UserCollection users = new CBE.UserCollection();
string spName = Constants.oraclePackagePrefix + "User_GetById";
DbCommand command = VaaaN.MLFF.Libraries.CommonLibrary.DBA.DBAccessor.GetStoredProcCommand(spName);
command.Parameters.Add(VaaaN.MLFF.Libraries.CommonLibrary.DBA.DBAccessor.CreateDbParameter(ref command, "p_user_id", DbType.Int32, user.UserId, ParameterDirection.Input));
users = ConvertDataTableToCollection(VaaaN.MLFF.Libraries.CommonLibrary.DBA.DBAccessor.LoadDataSet(command, tableName).Tables[tableName]);
return(users[0]);
}
catch (Exception ex)
{
throw ex;
}
}
public static int Insert(CBE.UserCBE user)
{
int userId = 0;
try
{
string spName = Constants.oraclePackagePrefix + "User_Insert";
DbCommand command = VaaaN.MLFF.Libraries.CommonLibrary.DBA.DBAccessor.GetStoredProcCommand(spName);
// Will be incremented by sequence number
command.Parameters.Add(VaaaN.MLFF.Libraries.CommonLibrary.DBA.DBAccessor.CreateDbParameter(ref command, "p_user_id", DbType.Int32, userId, ParameterDirection.Output));
command.Parameters.Add(VaaaN.MLFF.Libraries.CommonLibrary.DBA.DBAccessor.CreateDbParameter(ref command, "p_login_name", DbType.String, user.LoginName, ParameterDirection.Input, 100));
command.Parameters.Add(VaaaN.MLFF.Libraries.CommonLibrary.DBA.DBAccessor.CreateDbParameter(ref command, "p_first_name", DbType.String, user.FirstName, ParameterDirection.Input, 100));
command.Parameters.Add(VaaaN.MLFF.Libraries.CommonLibrary.DBA.DBAccessor.CreateDbParameter(ref command, "p_last_name", DbType.String, user.LastName, ParameterDirection.Input, 100));
command.Parameters.Add(VaaaN.MLFF.Libraries.CommonLibrary.DBA.DBAccessor.CreateDbParameter(ref command, "p_description", DbType.String, user.Description, ParameterDirection.Input, 100));
command.Parameters.Add(VaaaN.MLFF.Libraries.CommonLibrary.DBA.DBAccessor.CreateDbParameter(ref command, "p_password", DbType.String, user.Password, ParameterDirection.Input, 100));
command.Parameters.Add(VaaaN.MLFF.Libraries.CommonLibrary.DBA.DBAccessor.CreateDbParameter(ref command, "p_address", DbType.String, user.Address, ParameterDirection.Input, 200));
command.Parameters.Add(VaaaN.MLFF.Libraries.CommonLibrary.DBA.DBAccessor.CreateDbParameter(ref command, "p_role_id", DbType.Int32, user.RoleId, ParameterDirection.Input));
command.Parameters.Add(VaaaN.MLFF.Libraries.CommonLibrary.DBA.DBAccessor.CreateDbParameter(ref command, "p_acc_expiry_date", DbType.DateTime, user.AccountExpiryDate, ParameterDirection.Input));
command.Parameters.Add(VaaaN.MLFF.Libraries.CommonLibrary.DBA.DBAccessor.CreateDbParameter(ref command, "p_modifier_id", DbType.Int32, user.ModifierId, ParameterDirection.Input));
command.Parameters.Add(VaaaN.MLFF.Libraries.CommonLibrary.DBA.DBAccessor.CreateDbParameter(ref command, "p_creation_date", DbType.DateTime, user.CreationDate, ParameterDirection.Input));
command.Parameters.Add(VaaaN.MLFF.Libraries.CommonLibrary.DBA.DBAccessor.CreateDbParameter(ref command, "p_modification_date", DbType.DateTime, user.ModificationDate, ParameterDirection.Input));
command.Parameters.Add(VaaaN.MLFF.Libraries.CommonLibrary.DBA.DBAccessor.CreateDbParameter(ref command, "p_finger_print_1", DbType.String, user.FingerPrint1, ParameterDirection.Input));
command.Parameters.Add(VaaaN.MLFF.Libraries.CommonLibrary.DBA.DBAccessor.CreateDbParameter(ref command, "P_MOBILE_NO", DbType.String, user.MobileNo, ParameterDirection.Input, 10));
command.Parameters.Add(VaaaN.MLFF.Libraries.CommonLibrary.DBA.DBAccessor.CreateDbParameter(ref command, "P_EMAIL_ID", DbType.String, user.EmailId, ParameterDirection.Input, 50));
command.Parameters.Add(VaaaN.MLFF.Libraries.CommonLibrary.DBA.DBAccessor.CreateDbParameter(ref command, "P_USER_STATUS", DbType.Int32, user.UserStatus, ParameterDirection.Input));
command.Parameters.Add(VaaaN.MLFF.Libraries.CommonLibrary.DBA.DBAccessor.CreateDbParameter(ref command, "P_DOB", DbType.DateTime, user.UserDob, ParameterDirection.Input));
VaaaN.MLFF.Libraries.CommonLibrary.DBA.DBAccessor.ExecuteNonQuery(command);
return(userId = (int)command.Parameters["p_user_id"].Value);
}
catch (Exception ex)
{
throw ex;
}
}
private static CBE.UserCollection ConvertDataTableToCollection(DataTable dt)
{
try
{
CBE.UserCollection users = new CBE.UserCollection();
for (int i = 0; i < dt.Rows.Count; i++)
{
CBE.UserCBE user = new CBE.UserCBE();
if (dt.Rows[i]["USER_ID"] != DBNull.Value)
{
user.UserId = Convert.ToInt32(dt.Rows[i]["USER_ID"]);
}
if (dt.Rows[i]["LOGIN_NAME"] != DBNull.Value)
{
user.LoginName = Convert.ToString(dt.Rows[i]["LOGIN_NAME"]);
}
if (dt.Rows[i]["FIRST_NAME"] != DBNull.Value)
{
user.FirstName = Convert.ToString(dt.Rows[i]["FIRST_NAME"]);
}
if (dt.Rows[i]["LAST_NAME"] != DBNull.Value)
{
user.LastName = Convert.ToString(dt.Rows[i]["LAST_NAME"]);
}
user.FullName = Convert.ToString(dt.Rows[i]["FIRST_NAME"]) + " " + Convert.ToString(dt.Rows[i]["LAST_NAME"]);
if (dt.Rows[i]["DESCRIPTION"] != DBNull.Value)
{
user.Description = Convert.ToString(dt.Rows[i]["DESCRIPTION"]);
}
if (dt.Rows[i]["PASSWORD"] != DBNull.Value)
{
user.Password = Convert.ToString(dt.Rows[i]["PASSWORD"]);
}
if (dt.Rows[i]["ADDRESS"] != DBNull.Value)
{
user.Address = Convert.ToString(dt.Rows[i]["ADDRESS"]);
}
if (dt.Rows[i]["ROLE_ID"] != DBNull.Value)
{
user.RoleId = Convert.ToInt32(dt.Rows[i]["ROLE_ID"]);
}
if (user.RoleId > 0)
{
foreach (VaaaN.MLFF.Libraries.CommonLibrary.CBE.RoleCBE role in VaaaN.MLFF.Libraries.CommonLibrary.BLL.RoleBLL.GetAll())
{
if (user.RoleId == role.RoleId)
{
user.RoleName = role.RoleName;
break;
}
}
}
if (dt.Rows[i]["ACC_EXPIRY_DATE"] != DBNull.Value)
{
user.AccountExpiryDate = Convert.ToDateTime(dt.Rows[i]["ACC_EXPIRY_DATE"]);
}
if (dt.Rows[i]["MODIFIER_ID"] != DBNull.Value)
{
user.ModifierId = Convert.ToInt32(dt.Rows[i]["MODIFIER_ID"]);
}
if (dt.Rows[i]["CREATION_DATE"] != DBNull.Value)
{
user.CreationDate = Convert.ToDateTime(dt.Rows[i]["CREATION_DATE"]);
}
if (dt.Rows[i]["MODIFICATION_DATE"] != DBNull.Value)
{
user.ModificationDate = Convert.ToDateTime(dt.Rows[i]["MODIFICATION_DATE"]);
}
if (dt.Rows[i]["USER_STATUS"] != DBNull.Value)
{
user.UserStatus = Convert.ToBoolean(dt.Rows[i]["USER_STATUS"]);
}
if (dt.Rows[i]["EMAIL_ID"] != DBNull.Value)
{
user.EmailId = Convert.ToString(dt.Rows[i]["EMAIL_ID"]);
}
if (dt.Rows[i]["MOBILE_NO"] != DBNull.Value)
{
user.MobileNo = Convert.ToString(dt.Rows[i]["MOBILE_NO"]);
}
if (dt.Rows[i]["MOBILE_NO"] != DBNull.Value)
{
user.MobileNo = Convert.ToString(dt.Rows[i]["MOBILE_NO"]);
}
if (dt.Rows[i]["DOB"] != DBNull.Value)
{
user.UserDob = Convert.ToDateTime(dt.Rows[i]["DOB"]);
}
users.Add(user);
}
return(users);
}
catch (Exception ex)
{
throw ex;
}
}