Exemplo n.º 1
0
        public IEnumerable<ValidHourEntry> SelValidHourEntries()
        {
            //Set up the variables
            int intRC = 0;
            SqlDataReader dr;
            List<ValidHourEntry> retVal = new List<ValidHourEntry>();

            //Set up the parameters using my custom parameter factory
            IParameterFactory objParams = new ValidHourEntryParameterFactory();

            try
            {
                using (SqlConnection sqlConnection = new ADOConnectionFactory().Connection)
                {
                    sqlConnection.Open();
                    using (SqlCommand sqlCommand = new SqlCommand())
                    {

                        sqlCommand.Connection = sqlConnection;

                        sqlCommand.Parameters.Add((objParams.Parameters["RC"]));

                        //call stored procedure to insert record
                        sqlCommand.CommandType = CommandType.StoredProcedure;
                        sqlCommand.CommandText = "pSelValidHours";

                        dr = sqlCommand.ExecuteReader();

                        while (dr.Read())
                        {
                            ValidHourEntry vhe = new ValidHourEntry();
                            vhe.TimePeriodID = (int)dr["TimePeriodID"];
                            vhe.TimePeriod = (string)dr["TimePeriod"];
                            retVal.Add(vhe);
                        }
                        dr.Close();

                        //check the return code for errors
                        intRC = (int)objParams.Parameters["RC"].Value;
                        if (intRC < 0)
                        {
                            throw new Exception("Error reported in Stored Procedure: " + objParams.Parameters["RC"].Value.ToString());
                        }

                    }
                }
            }
            catch (Exception)
            {
                throw;
            }
            return retVal;
        }
Exemplo n.º 2
0
        public IEnumerable<ThisYearsDate> SelThisYearsDates()
        {
            //Set up the variables
            int intRC = 0;
            SqlDataReader dr;
            List<ThisYearsDate> retVal = new List<ThisYearsDate>();

            //Set up the parameters using my custom parameter factory
            IParameterFactory objParams = new ThisYearsDateParameterFactory();

            try
            {
                using (SqlConnection sqlConnection = new ADOConnectionFactory().Connection)
                {
                    sqlConnection.Open();
                    using (SqlCommand sqlCommand = new SqlCommand())
                    {

                        sqlCommand.Connection = sqlConnection;

                        sqlCommand.Parameters.Add((objParams.Parameters["RC"]));

                        //call stored procedure to insert record
                        sqlCommand.CommandType = CommandType.StoredProcedure;
                        sqlCommand.CommandText = "pSelThisYearsDates";

                        dr = sqlCommand.ExecuteReader();

                        while (dr.Read())
                        {
                            ThisYearsDate tyd = new ThisYearsDate();
                            tyd._dateID = (int)dr["dateID"];
                            tyd._dateName = (string)dr["dateName"];
                            retVal.Add(tyd);
                        }

                        dr.Close();

                        //check the return code for errors
                        intRC = (int)objParams.Parameters["RC"].Value;
                        if (intRC < 0)
                        {
                            throw new Exception("Error reported in Stored Procedure: " + objParams.Parameters["RC"].Value.ToString());
                        }

                    }
                }
            }
            catch (Exception)
            {
                throw;
            }
            return retVal;
        }
Exemplo n.º 3
0
        public IEnumerable<EmployeeProjectHour> SelEmployeeProjectHours()
        {
            //Set up the variables
            int intRC = 0;
            SqlDataReader dr;
            List<EmployeeProjectHour> retVal = new List<EmployeeProjectHour>();

            //Set up the parameters using my custom parameter factory
            IParameterFactory objParams = new EmployeeProjectHourParameterFactory(0, String.Empty, 0, String.Empty, DateTime.Now, 0);

            try
            {
                using (SqlConnection sqlConnection = new ADOConnectionFactory().Connection)
                {
                    sqlConnection.Open();
                    using (SqlCommand sqlCommand = new SqlCommand())
                    {

                        sqlCommand.Connection = sqlConnection;

                        sqlCommand.Parameters.Add((objParams.Parameters["RC"]));

                        //call stored procedure to insert record
                        sqlCommand.CommandType = CommandType.StoredProcedure;
                        sqlCommand.CommandText = "pSelEmployeeProjectHours";

                        dr = sqlCommand.ExecuteReader();

                        while (dr.Read())
                        {
                            EmployeeProjectHour eph = new EmployeeProjectHour();
                            eph.EmployeeID = (int)dr["EmployeeID"];
                            eph.EmployeeName = (string)dr["EmployeeName"];
                            eph.ProjectID = (int)dr["ProjectID"];
                            eph.ProjectName = (string)dr["ProjectName"]; 
                            eph.Date = (DateTime)dr["Date"];
                            eph.Hours = (decimal)dr["Hours"];
                            retVal.Add(eph);
                        }
                        dr.Close();

                        //check the return code for errors
                        intRC = (int)objParams.Parameters["RC"].Value;
                        if (intRC < 0)
                        {
                            throw new Exception("Error reported in Stored Procedure: " + objParams.Parameters["RC"].Value.ToString());
                        }

                    }
                }
            }
            catch (Exception)
            {
                throw;
            }
            return retVal;
        }
Exemplo n.º 4
0
        public int DelEmployeeProjectHours(int EmployeeID, int ProjectID, DateTime Date)
        {
            //Set up the variables
            int intRC = 0; //Used to trap the Stored Procedure's return code

            //Set up the parameters using my custom parameter factory
            IParameterFactory objParams = new EmployeeProjectHourParameterFactory(EmployeeID, String.Empty, ProjectID, String.Empty, Date, 0);

            try
            {
                using (SqlConnection sqlConnection = new ADOConnectionFactory().Connection)
                {
                    sqlConnection.Open();
                    using (SqlCommand sqlCommand = new SqlCommand())
                    {

                        sqlCommand.Connection = sqlConnection;

                        //add parameters to procedure   
                        sqlCommand.Parameters.Add(objParams.Parameters["RC"]);
                        sqlCommand.Parameters.Add(objParams.Parameters["EmployeeID"]);
                        sqlCommand.Parameters.Add(objParams.Parameters["ProjectID"]);
                        sqlCommand.Parameters.Add(objParams.Parameters["Date"]);

                        //call stored procedure to insert record
                        sqlCommand.CommandType = CommandType.StoredProcedure;
                        sqlCommand.CommandText = "pDelEmployeeProjectHours";

                        sqlCommand.ExecuteNonQuery();

                        //Trap or return the Stored Procedure's return code
                        intRC = (int)objParams.Parameters["RC"].Value;
                        if (intRC < 0)
                        {
                            throw new Exception("Error reported in Stored Procedure: " + objParams.Parameters["RC"].Value.ToString());
                        }
                    }
                }
            }
            catch (Exception)
            {
                throw;
            }
            return intRC;
        }
Exemplo n.º 5
0
        public int UpdProject(int ProjectID, string ProjectName, string ProjectDescription)
        {
            //Set up the variables
            int intRC = 0; //Used to trap the Stored Procedure's return code

            //Set up the parameters using my custom parameter factory
            IParameterFactory objParams = new ProjectsParameterFactory(ProjectID, ProjectName, ProjectDescription);

            try
            {
                using (SqlConnection sqlConnection = new ADOConnectionFactory().Connection)
                {
                    sqlConnection.Open();
                    using (SqlCommand sqlCommand = new SqlCommand())
                    {

                        sqlCommand.Connection = sqlConnection;

                        //add parameters to procedure   
                        sqlCommand.Parameters.Add(objParams.Parameters["RC"]);
                        sqlCommand.Parameters.Add(objParams.Parameters["ProjectID"]);
                        sqlCommand.Parameters.Add(objParams.Parameters["ProjectName"]);
                        sqlCommand.Parameters.Add(objParams.Parameters["ProjectDescription"]);

                        //call stored procedure to insert record
                        sqlCommand.CommandType = CommandType.StoredProcedure;
                        sqlCommand.CommandText = "pUpdProjects";

                        sqlCommand.ExecuteNonQuery();

                        //Trap or return the Stored Procedure's return code
                        intRC = (int)objParams.Parameters["RC"].Value;
                        if (intRC < 0)
                        {
                            throw new Exception("Error reported in Stored Procedure: " + objParams.Parameters["RC"].Value.ToString());
                        }
                    }
                }
            }
            catch (Exception)
            {
                throw;
            }
            return intRC;
        }
Exemplo n.º 6
0
        public int InsProject(string ProjectName, string ProjectDescription, out int NewRowID)
        {
            //Set up the variables
            int intRC = 0; //Used to trap the Stored Procedure's return code

            //Set up the parameters using my custom parameter factory
            IParameterFactory objParams = new ProjectsParameterFactory(ProjectName : ProjectName, ProjectDescription: ProjectDescription);

            try
            {
                using (SqlConnection sqlConnection = new ADOConnectionFactory().Connection)
                {
                    sqlConnection.Open();
                    using (SqlCommand sqlCommand = new SqlCommand())
                    {

                        sqlCommand.Connection = sqlConnection;

                        //add parameters to procedure   
                        sqlCommand.Parameters.Add(objParams.Parameters["RC"]);
                        sqlCommand.Parameters.Add(objParams.Parameters["ProjectName"]);
                        sqlCommand.Parameters.Add(objParams.Parameters["ProjectDescription"]);
                        sqlCommand.Parameters.Add(objParams.Parameters["NewRowID"]);

                        //call stored procedure to insert record
                        sqlCommand.CommandType = CommandType.StoredProcedure;
                        sqlCommand.CommandText = "pInsProjects";

                        sqlCommand.ExecuteNonQuery();

                        //Get the new row ID created by the table's Identity feature
                        if (objParams.Parameters["NewRowID"].Value is DBNull)
                        { NewRowID = 0; } //if the insert has failed, then set this to an arbitrary number
                        else { NewRowID = (int)objParams.Parameters["NewRowID"].Value; } //else send it back as output

                        //Trap or return the Stored Procedure's return code
                        intRC = (int)objParams.Parameters["RC"].Value;
                        if (intRC < 0)
                        {
                            throw new Exception("Error reported in Stored Procedure: " + objParams.Parameters["RC"].Value.ToString());
                        }
                    }
                }
            }
            catch (Exception)
            {
                throw;
            }
            return intRC;
        }