public DataTable GetAllProject()
{
SqlCmdBuilder cmd = DataBaseHelpers.CreateADPPipelineCommand();
cmd.Query = @"SELECT id,nama,kota,alamat,startdate,no_kontrak nokontrak,no_spk nospk,telp_spk telpspk
FROM adp_project";
return(cmd.GetTable());
}
public static DataTable RetrieveWebLoginUserByUsername(string uName)
{
SqlCmdBuilder cmd = DataBaseHelpers.CreateADPPipelineCommand();
cmd.Query = @"SELECT USERNAME, PASSWORD
FROM ADP_USER
WHERE UPPER(Username)=@uName AND allow_login = 1";
cmd.AddParameter("uName", SqlCmdParameterDirection.Input, uName.ToUpper());
return(cmd.GetTable());
}
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 RetrieveRoleByName(string name)
{
SqlCmdBuilder cmd = DataBaseHelpers.CreateCommand();
cmd.Query = @"SELECT *
FROM ADP_ROLE
WHERE NAMA_ROLE=@name";
cmd.AddParameter("name", SqlCmdParameterDirection.Input, name);
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 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());
}