public DataTable RetrieveRoleById(string idRole)
{
SqlCmdBuilder cmd = DataBaseHelpers.CreateADPPipelineCommand();
cmd.Query = @"SELECT *
FROM ADP_ROLE
WHERE ID_ROLE=@idRole";
cmd.AddParameter("idRole", SqlCmdParameterDirection.Input, idRole);
return(cmd.GetTable());
}
public DataTable RetrieveUser(string uName, string pass)
{
SqlCmdBuilder cmd = DataBaseHelpers.CreateADPPipelineCommand();
cmd.Query = @"SELECT *
FROM ADP_USER
WHERE UPPER(USERNAME)=@uName AND PASSWORD=@pass";
cmd.AddParameter("uName", SqlCmdParameterDirection.Input, uName.ToUpper());
cmd.AddParameter("pass", SqlCmdParameterDirection.Input, ADP.Encryption.CryptoTools.ActionEncrypt(pass.ToUpper()));
return(cmd.GetTable());
}
public DataTable RetrieveUser(string uName)
{
SqlCmdBuilder cmd = DataBaseHelpers.CreateADPPipelineCommand();
cmd.Query = @"SELECT *
FROM ADP_USER
WHERE UPPER(USERNAME)=@uName";
cmd.AddParameter("uName", SqlCmdParameterDirection.Input, uName.ToUpper());
return(cmd.GetTable());
}
public bool CreateUser(string Username, string Password, string IdRole)
{
SqlCmdBuilder cmd = DataBaseHelpers.CreateADPPipelineCommand();
cmd.Query = "INSERT INTO ADP_USER (USERNAME, PASSWORD, ID_ROLE)" +
"VALUES (@user, @pass, @idrole)";
cmd.AddParameter("user", SqlCmdParameterDirection.Input, Username);
cmd.AddParameter("pass", SqlCmdParameterDirection.Input, Password);
cmd.AddParameter("idrole", SqlCmdParameterDirection.Input, IdRole);
return(cmd.ExecuteNonQuery() > 0);
}
public DataTable RetrieveUserRoles(string username)
{
SqlCmdBuilder cmd = DataBaseHelpers.CreateADPPipelineCommand();
cmd.Query = @"SELECT *
FROM ADP_USER usr INNER JOIN ADP_ROLE uro " +
@"ON usr.id_role = uro.id_role";
if (!string.IsNullOrEmpty(username))
{
cmd.Query += "WHERE UPPER(usr.username)";
cmd.AddParameter("username", SqlCmdParameterDirection.Input, username.ToUpper());
}
return(cmd.GetTable());
}
public bool CreateEmployee(string nama, string tempLahir, DateTime tglLahir, string noTlp, string email, string jabatan)
{
SqlCmdBuilder cmd = DataBaseHelpers.CreateADPPipelineCommand();
cmd.Query = "INSERT INTO ADP_EMPLOYEE (ID_EMPLOYEE, NAMA, TEMPAT_LAHIR, TGL_LAHIR, NO_TELP, MAIL_ADDRESS, JABATAN) " +
"VALUES (@idemp, @nm, @tempLahir, @tglLahir, @notlp, @email, @jabatan)";
cmd.AddParameter("idemp", SqlCmdParameterDirection.Input, Guid.NewGuid().ToString());
cmd.AddParameter("nm", SqlCmdParameterDirection.Input, nama);
cmd.AddParameter("tempLahir", SqlCmdParameterDirection.Input, tempLahir);
cmd.AddParameter("tglLahir", SqlCmdParameterDirection.Input, tglLahir);
cmd.AddParameter("notlp", SqlCmdParameterDirection.Input, noTlp);
cmd.AddParameter("email", SqlCmdParameterDirection.Input, email);
cmd.AddParameter("jabatan", SqlCmdParameterDirection.Input, jabatan);
return(cmd.ExecuteNonQuery() > 0);
}