Exemple #1
0
        public SysParam GetParamValue(string paramname)
        {
            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.PARAMID, e.PARAMNAME, e.PARAMVALUE
                    FROM INTTASPENSYSPARAM e
                    WHERE e.PARAMNAME = @p_paramname ;";
                    //define the SqlCommand object
                    SqlCommand cmd = new SqlCommand(query, connection);

                    var pname = new SqlParameter("p_paramname", SqlDbType.Char);
                    pname.Value = paramname;
                    cmd.Parameters.Add(pname);

                    //open connection
                    connection.Open();

                    //execute the SQLCommand
                    SqlDataReader dr = cmd.ExecuteReader();

                    //check if there are records
                    var result = new List <SysParam>();
                    if (dr.HasRows)
                    {
                        while (dr.Read())
                        {
                            var data = new SysParam();
                            data.PARAMNAME  = SafeGetString(dr, 1);
                            data.PARAMVALUE = SafeGetString(dr, 2);

                            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);
        }
Exemple #2
0
        public LogAgentDataUpdate FindById(int id)
        {
            SqlConnectionStringBuilder builder = ConnectionStringBuilder.GetConnectionStringBuilder();

            try
            {
                //sql connection object
                using (SqlConnection connection = new SqlConnection(builder.ConnectionString))
                {
                    string query = @"SELECT e.IDAGENT, e.REQUESTDATA, e.RESPONSEDATA, e.CREATEDDATE
                    FROM LOG_AGENTDATAUPDATE e
                    WHERE e.IDAGENT = @p_id ;";

                    SqlCommand cmd = new SqlCommand(query, connection);

                    var pid = new SqlParameter("p_id", SqlDbType.Int);
                    pid.Value = id;
                    cmd.Parameters.Add(pid);

                    connection.Open();
                    SqlDataReader dr = cmd.ExecuteReader();

                    var result = new List <LogAgentDataUpdate>();
                    if (dr.HasRows)
                    {
                        while (dr.Read())
                        {
                            var data = new LogAgentDataUpdate();
                            data.IDAGENT      = dr.GetInt32(0);
                            data.REQUESTDATA  = SafeGetString(dr, 1);
                            data.RESPONSEDATA = SafeGetString(dr, 2);
                            data.CREATEDDATE  = SafeGetDatetime(dr, 3);


                            result.Add(data);
                        }
                    }
                    dr.Close();
                    connection.Close();
                    if (result.Count > 0)
                    {
                        return(result.FirstOrDefault());
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine("Exception: " + ex.Message);
            }
            return(null);
        }
Exemple #3
0
        public void Create(LogAgentDataUpdateHistory entity)
        {
            SqlConnectionStringBuilder builder = ConnectionStringBuilder.GetConnectionStringBuilder();

            using (SqlConnection connection = new SqlConnection(builder.ConnectionString))
            {
                string     sql = "INSERT INTO LOG_AGENTDATAUPDATEHISTORY(IDAGENT, REQUESTDATA,RESPONSEDATA) VALUES(@p_idagent, @p_requestdata, @p_responsedata)";
                SqlCommand cmd = new SqlCommand(sql, 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.CommandType = CommandType.Text;
                cmd.ExecuteNonQuery();
                connection.Close();
            }
        }
        public void Create(LogAgentDataDelete entity)
        {
            SqlConnectionStringBuilder builder = ConnectionStringBuilder.GetConnectionStringBuilder();

            using (SqlConnection connection = new SqlConnection(builder.ConnectionString))
            {
                string     sql = "INSERT INTO LOG_AGENTDATADELETE(IDPROFILE, REQUESTDATA,RESPONSEDATA) VALUES(@p_idprofile, @p_requestdata, @p_responsedata)";
                SqlCommand cmd = new SqlCommand(sql, connection);
                connection.Open();
                cmd.Parameters.Add("@p_idprofile", SqlDbType.Int).Value        = entity.IDPROFILE;
                cmd.Parameters.Add("@p_requestdata", SqlDbType.VarChar).Value  = entity.REQUESTDATA;
                cmd.Parameters.Add("@p_responsedata", SqlDbType.VarChar).Value = entity.RESPONSEDATA;
                cmd.CommandType = CommandType.Text;
                cmd.ExecuteNonQuery();
                connection.Close();
            }
        }
Exemple #5
0
        public void Update(LogAgentDataUpdate entity)
        {
            SqlConnectionStringBuilder builder = ConnectionStringBuilder.GetConnectionStringBuilder();

            using (SqlConnection connection = new SqlConnection(builder.ConnectionString))
            {
                string sql = "UPDATE LOG_AGENTDATAUPDATE SET REQUESTDATA = @p_requestdata, RESPONSEDATA = @p_responsedata, CREATEDDATE = @p_createddate WHERE IDAGENT = @p_idagent ";

                SqlCommand cmd = new SqlCommand(sql, 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_createddate", SqlDbType.DateTime).Value = DateTime.Now;

                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);
        }
Exemple #7
0
        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);
        }