public void Create(LogAgentDataNew entity)
{
SqlConnectionStringBuilder builder = ConnectionStringBuilder.GetConnectionStringBuilder();
using (SqlConnection connection = new SqlConnection(builder.ConnectionString))
{
string sql = "INSERT INTO LOG_AGENTDATANEW(IDAGENT, REQUESTDATA,RESPONSEDATA,IDPROFILE) VALUES(@p_idagent, @p_requestdata,@p_responsedata,@p_idprofile)";
SqlCommand cmd = new SqlCommand(sql, connection);
//open connection
connection.Open();
cmd.Parameters.Add("@p_idagent", SqlDbType.Int).Value = entity.IDAGENT;
cmd.Parameters.Add("@p_requestdata", SqlDbType.VarChar).Value = entity.REQUESTDATA;
cmd.Parameters.Add("@p_responsedata", SqlDbType.VarChar).Value = entity.RESPONSEDATA;
cmd.Parameters.Add("@p_idprofile", SqlDbType.Int).Value = entity.IDPROFILE;
cmd.CommandType = CommandType.Text;
cmd.ExecuteNonQuery();
connection.Close();
}
}
public List <AgentDataNew> GetListAgentDelete()
{
SqlConnectionStringBuilder builder = ConnectionStringBuilder.GetConnectionStringBuilder();
try
{
//sql connection object
using (SqlConnection connection = new SqlConnection(builder.ConnectionString))
{
//retrieve the SQL Server instance version
var query = @"SELECT e.PayeeID, e.Name, e.JoinDate, e.Title, e.EmailAddress,
e.Address, e.RecruitBy, e.RecruiterName, e.AMCode, e.AMName,
e.SAMCode, e.SAMName, e.ADCode, e.ADNAme, e.GAOfficeCode,
e.GAOfficeName, e.PERSON_ID, e.LicenseID, e.Phone, e.EmployeeStatus,
e.TerminationComments, e.AccountNo, e.BankCode, e.ExpiryDate, e.TerminationDate,
e.CreatedDate, e.UpdatedDate, e.DeletedDate
FROM AGENTDATANEW e
WHERE e.CreatedDate IS NOT NULL AND e.UpdatedDate IS NOT NULL AND e.DeletedDate IS NOT NULL
ORDER BY e.DeletedDate DESC ;";
//define the SqlCommand object
SqlCommand cmd = new SqlCommand(query, connection);
//open connection
connection.Open();
//execute the SQLCommand
SqlDataReader dr = cmd.ExecuteReader();
//check if there are records
List <AgentDataNew> result = new List <AgentDataNew>();
if (dr.HasRows)
{
while (dr.Read())
{
AgentDataNew data = new AgentDataNew();
data.PayeeID = SafeGetString(dr, 0);
data.Name = SafeGetString(dr, 1);
data.JoinDate = SafeGetDatetime(dr, 2);
data.Title = SafeGetString(dr, 3);
data.EmailAddress = SafeGetString(dr, 4);
data.Address = SafeGetString(dr, 5);
data.RecruitBy = SafeGetString(dr, 6);
data.RecruiterName = SafeGetString(dr, 7);
data.AMCode = SafeGetString(dr, 8);
data.AMName = SafeGetString(dr, 9);
data.SAMCode = SafeGetString(dr, 10);
data.SAMName = SafeGetString(dr, 11);
data.ADCode = SafeGetString(dr, 12);
data.ADNAme = SafeGetString(dr, 13);
data.GAOfficeCode = SafeGetString(dr, 14);
data.GAOfficeName = SafeGetString(dr, 15);
data.PERSON_ID = SafeGetString(dr, 16);
data.LicenseID = SafeGetString(dr, 17);
data.Phone = SafeGetString(dr, 18);
data.EmployeeStatus = SafeGetString(dr, 19);
data.TerminationComments = SafeGetString(dr, 20);
data.AccountNo = SafeGetString(dr, 21);
data.BankCode = SafeGetString(dr, 22);
data.ExpiryDate = SafeGetDatetime(dr, 23);
data.TerminationDate = SafeGetDatetime(dr, 24);
data.CreatedDate = SafeGetDatetime(dr, 25);
data.UpdatedDate = SafeGetDatetime(dr, 26);
data.DeletedDate = SafeGetDatetime(dr, 27);
result.Add(data);
}
}
//close data reader
dr.Close();
//close connection
connection.Close();
if (result.Count > 0)
{
return(result);
}
}
}
catch (Exception ex)
{
//display error message
Console.WriteLine("Exception: " + ex.Message);
}
return(null);
}
public LogAgentDataNew FindById(int id)
{
SqlConnectionStringBuilder builder = ConnectionStringBuilder.GetConnectionStringBuilder();
try
{
//sql connection object
using (SqlConnection connection = new SqlConnection(builder.ConnectionString))
{
//retrieve the SQL Server instance version
string query = @"SELECT e.IDAGENT, e.REQUESTDATA, e.RESPONSEDATA, e.IDPROFILE, e.CREATEDDATE
FROM LOG_AGENTDATANEW e
WHERE e.IDAGENT = @p_id ;";
//define the SqlCommand object
SqlCommand cmd = new SqlCommand(query, connection);
var pid = new SqlParameter("p_id", SqlDbType.Int);
pid.Value = id;
cmd.Parameters.Add(pid);
//open connection
connection.Open();
//execute the SQLCommand
SqlDataReader dr = cmd.ExecuteReader();
//check if there are records
var result = new List <LogAgentDataNew>();
if (dr.HasRows)
{
while (dr.Read())
{
var data = new LogAgentDataNew();
data.IDAGENT = dr.GetInt32(0);
data.REQUESTDATA = SafeGetString(dr, 1);
data.RESPONSEDATA = SafeGetString(dr, 2);
data.IDPROFILE = SafeGetInt(dr, 3);
data.CREATEDDATE = SafeGetDatetime(dr, 4);
result.Add(data);
}
}
//close data reader
dr.Close();
//close connection
connection.Close();
if (result.Count > 0)
{
return(result.FirstOrDefault());
}
}
}
catch (Exception ex)
{
//display error message
Console.WriteLine("Exception: " + ex.Message);
}
return(null);
}