コード例 #1
0
        public List <personDAO> listAllPerson()
        {
            // making a new instance of the list
            List <personDAO> _personList = new List <personDAO>();

            try
            {
                using (SqlConnection _connection = new SqlConnection(connectionStrings))
                {
                    using (SqlCommand _command = new SqlCommand("sp_viewPerson", _connection))
                    {
                        // specify whay type of command is to be used
                        _command.CommandType = CommandType.StoredProcedure;

                        _connection.Open();
                        using (SqlDataReader _reader = _command.ExecuteReader())
                        {
                            // checking if the _reader has rows
                            if (_reader.HasRows)
                            {
                                // loop to go through the columns
                                while (_reader.Read())
                                {
                                    // new instance of to store all the values into persontolist.
                                    personDAO personToList = new personDAO();
                                    personToList.personID        = _reader.GetInt32(_reader.GetOrdinal("personID"));
                                    personToList.personFirstName = (string)_reader["personFirstName"];
                                    personToList.personLastName  = (string)_reader["personLastName"];
                                    personToList.personAddress   = (string)_reader["personAddress"];
                                    personToList.personCity      = (string)_reader["personCity"];
                                    personToList.personState     = (string)_reader["personState"];
                                    personToList.personZip       = (string)_reader["personZip"];
                                    personToList.personPhone     = (string)_reader["personPhone"];
                                    personToList.FK_roleID       = _reader.GetInt32(_reader.GetOrdinal("FK_roleID"));
                                    personToList.personUsername  = (string)_reader["personUsername"];
                                    personToList.personPassword  = (string)_reader["personPassword"];

                                    // adding values to varibale _personList.add
                                    _personList.Add(personToList);
                                }
                            }
                            else
                            {
                                // showing error if no data found
                                Console.WriteLine("No data found");
                            }
                        }
                    }
                }
            }
            catch (Exception _error)
            {
                _logger.logError(_error);
            }

            // returning person list
            return(_personList);
        }
コード例 #2
0
        public personDAO personSignIn(personDAO personSignIn)
        {
            personDAO _personSignIn = new personDAO();

            try
            {
                using (SqlConnection _connection = new SqlConnection(connectionStrings))
                {
                    using (SqlCommand _command = new SqlCommand("sp_personSignIn", _connection))
                    {
                        _command.CommandType = CommandType.StoredProcedure;

                        _command.Parameters.AddWithValue("@personUserName", personSignIn.personUsername);
                        _command.Parameters.AddWithValue("@personPassword", personSignIn.personPassword);

                        _connection.Open();

                        using (SqlDataReader _reader = _command.ExecuteReader())
                            if (_reader.HasRows)
                            {
                                while (_reader.Read())
                                {
                                    _personSignIn.personID        = _reader.GetInt32(0);
                                    _personSignIn.personFirstName = _reader.GetString(1);
                                    _personSignIn.personLastName  = _reader.GetString(2);
                                    _personSignIn.personAddress   = _reader.GetString(3);
                                    _personSignIn.personCity      = _reader.GetString(4);
                                    _personSignIn.personState     = _reader.GetString(5);
                                    _personSignIn.personZip       = _reader.GetString(6);
                                    _personSignIn.personPhone     = _reader.GetString(7);
                                    _personSignIn.FK_roleID       = _reader.GetInt32(8);
                                    _personSignIn.personUsername  = _reader.GetString(9);
                                    _personSignIn.personPassword  = _reader.GetString(10);
                                }
                            }
                            else
                            {
                                Console.WriteLine("No data found");
                            }
                    }
                }
            }
            catch (Exception _error)
            {
                // putting error into a file
                _logger.logError(_error);
            }

            return(_personSignIn);
        }
コード例 #3
0
        // mapping data back to person DAO
        public personModel map(personDAO _personMod)
        {
            // making new instance of person model
            personModel _personRetMod = new personModel();

            _personRetMod.personID        = _personMod.personID;
            _personRetMod.personFirstName = _personMod.personFirstName;
            _personRetMod.personLastName  = _personMod.personLastName;
            _personRetMod.personAddress   = _personMod.personAddress;
            _personRetMod.personCity      = _personMod.personCity;
            _personRetMod.personState     = _personMod.personState;
            _personRetMod.personZip       = _personMod.personZip;
            _personRetMod.personPhone     = _personMod.personPhone;
            _personRetMod.FK_roleID       = _personMod.FK_roleID;
            _personRetMod.personUsername  = _personMod.personUsername;
            _personRetMod.personPassword  = _personMod.personPassword;


            return(_personRetMod);
        }
コード例 #4
0
        public void addingPerson(personDAO addPerson)
        {
            try
            {
                //create a connection to a database using our connection string variable
                using (SqlConnection _connection = new SqlConnection(connectionStrings))
                {
                    using (SqlCommand _command = new SqlCommand("sp_addPerson", _connection))
                    {
                        // specify whay type of command is to be used
                        _command.CommandType = CommandType.StoredProcedure;

                        //where the values are sent to the command
                        _command.Parameters.AddWithValue("@personFirstName", addPerson.personFirstName);
                        _command.Parameters.AddWithValue("@personLastName", addPerson.personLastName);
                        _command.Parameters.AddWithValue("@personAddress", addPerson.personFirstName);
                        _command.Parameters.AddWithValue("@personCity", addPerson.personCity);
                        _command.Parameters.AddWithValue("@personState", addPerson.personState);
                        _command.Parameters.AddWithValue("@personZip", addPerson.personZip);
                        _command.Parameters.AddWithValue("@personPhone", addPerson.personPhone);
                        _command.Parameters.AddWithValue("@personUsername", addPerson.personUsername);
                        _command.Parameters.AddWithValue("@personPassword", addPerson.personPassword);


                        // this is where the connection is open
                        _connection.Open();

                        // this is where we will execute the command
                        _command.ExecuteNonQuery();
                    }
                }
            }

            catch (Exception _error)
            {
                // putting error into a file
                _logger.logError(_error);
            }
        }