Exemplo n.º 1
0
        }//End of GetRecordByID

        public bool Insert(CreditCardDTO objDTO)
        {
            //Create Connection, assign Connection to string
            SqlConnection objConn = new SqlConnection(SQLServerDAOFactory.ConnectionString());

            //Start Error Trapping
            try
            {
                //Open connection
                objConn.Open();
                //Create SQL string
                string strSQL;
                strSQL  = "INSERT INTO CreditCard (CardNumber,OwnerName,MerchantName,ExpDate,";
                strSQL += "HouseStreetAddress,City,State,Zipcode,Country,";
                strSQL += "CreditLimit,ActivationStatus)";
                strSQL += "VALUES(@CardNumber,@CardOwnerName,@MerchantName,@ExpDate,";
                strSQL += "@HouseStreetAddress,@City,@State,@ZipCode,@Country,";
                strSQL += "@CreditLimit,@ActivationStatus);";
                //Create Command object, pass query and connection object
                SqlCommand objCmd = new SqlCommand(strSQL, objConn);
                //SET CommandType Property to text since we have a query string & NOT a Stored-Procedure
                //For stored procedures syntax is objCmd.CommandType = CommandType.StoredProcedure;
                objCmd.CommandType = CommandType.Text;
                //Add Parameter to. NOTE WE ARE ASSIGNING METHOD PARAMETER
                //IMPORTANT! Parameter TOKENS @XXXXX name must match same name Used in the INSERT QUERY
                //AND IN LISTED IN THE ORDER LISTED IN INSERT QUERY! NOTE WE ARE ASSIGNING ALL OBJECT'S DATA
                objCmd.Parameters.Add("@CardNumber", SqlDbType.VarChar).Value         = objDTO.CardNumber;
                objCmd.Parameters.Add("@CardOwnerName", SqlDbType.VarChar).Value      = objDTO.CardOwnerName;
                objCmd.Parameters.Add("@MerchantName", SqlDbType.VarChar).Value       = objDTO.MerchantName;
                objCmd.Parameters.Add("@ExpDate", SqlDbType.Date).Value               = objDTO.ExpirationDate;
                objCmd.Parameters.Add("@HouseStreetAddress", SqlDbType.VarChar).Value = objDTO.AddressLine1;
                objCmd.Parameters.Add("@City", SqlDbType.VarChar).Value               = objDTO.City;
                objCmd.Parameters.Add("@State", SqlDbType.Char).Value           = objDTO.State.ToCharArray();
                objCmd.Parameters.Add("@ZipCode", SqlDbType.VarChar).Value      = objDTO.ZipCode;
                objCmd.Parameters.Add("@Country", SqlDbType.VarChar).Value      = objDTO.Country;
                objCmd.Parameters.Add("@CreditLimit", SqlDbType.Decimal).Value  = objDTO.CreditLimit;
                objCmd.Parameters.Add("@ActivationStatus", SqlDbType.Bit).Value = objDTO.ActivationStatus;
                //Execute ACTION-Query, Test result and throw exception if failed
                int intRecordsAffected = objCmd.ExecuteNonQuery();

                //validate if INSERT QUERY was successful
                if (intRecordsAffected == 1)
                {
                    return(true);
                }
                //Terminate ADO Objects
                objCmd.Dispose();
                objCmd = null;
                //Step10-return false
                return(false);
            }//End of try
             //Trap for BO, App & General Exceptions
            catch (Exception objE)
            {
                //throw system exception since run time error has occurred.
                throw new Exception("Unexpected Error in CreditCardADO Insert(CreditCardDTO objDTO) Method:{ 0 } " + objE.Message);
            }
            finally
            {
                //Terminate connection
                objConn.Close();
                objConn.Dispose();
                objConn = null;
            }
        }//End of Insert
Exemplo n.º 2
0
        }//End of GetAllKeys

        public List <CreditCardDTO> GetAllChildRecordsOwnedByParent(int ParentKey)
        {
            //Create Connection, assign Connection to string
            SqlConnection objConn = new SqlConnection(SQLServerDAOFactory.ConnectionString());

            //Start Error Trapping
            try
            {
                //Open connection
                objConn.Open();
                //Create SQL string. Note spaces between SELECT, FROM, WHERE & AND clauses
                string strSQL;
                strSQL  = "SELECT CreditCard.CardNumber,CreditCard.CardOwnerName,";
                strSQL += "CreditCard.MerchantName,CreditCard.ExpDate,";
                strSQL += "CreditCard.HouseStreetName, CreditCard.City,CreditCard.State,";
                strSQL += "CreditCard.ZipCode,CreditCard.Country,";
                strSQL += "CreditCard.CreditLimit,CreditCard.ActivationStatus)";
                strSQL += " FROM CreditCard, Customer_CreditCard";
                strSQL += " WHERE CreditCard.CardNumber = Customer_CreditCard.CardNumber";
                strSQL += " AND Customer_CreditCard.Customer_IDNumber = @Customer_IDNumber;";
                //Create Command object, pass query and connection object
                SqlCommand objCmd = new SqlCommand(strSQL, objConn);
                //SET CommandType Property to text since we have a query string & NOT a Stored-Procedure
                //For stored procedures syntax is objCmd.CommandType = CommandType.StoredProcedure;
                objCmd.CommandType = CommandType.Text;
                //Add Parameter to. NOTE WE ARE ASSIGNING METHOD PARAMETER
                objCmd.Parameters.Add("@CustomerID", SqlDbType.Int).Value = ParentKey;
                //Create DATAREADER POINTER & Execute Query via
                //COMMAND OBJECT ExecuteReader Method which returns a populated
                //DATAREADER OBJECT with the results of the query
                SqlDataReader objDR = objCmd.ExecuteReader();
                //Test to make sure there is data in the DataReader Object
                if (objDR.HasRows)
                {
                    //Test Create a Generic List Collection Object of Data Transfer Objects
                    List <CreditCardDTO> colRecordList = new List <CreditCardDTO>();
                    //Loop through the Collection & Add Data Transfer Object (DTO)
                    while (objDR.Read())
                    {
                        //Create Data Transfer Object
                        CreditCardDTO objDTO = new CreditCardDTO();
                        //Populate Data Transfer Object with DataReader records
                        //IMPORTANT! Note that data must be extracted in the ORDER
                        //in which the QUERY RETURNS THE DATA.
                        objDTO.CardNumber       = objDR.GetString(0);
                        objDTO.CardOwnerName    = objDR.GetString(1);
                        objDTO.MerchantName     = objDR.GetString(2);
                        objDTO.ExpirationDate   = objDR.GetDateTime(3);
                        objDTO.AddressLine1     = objDR.GetString(4);
                        objDTO.City             = objDR.GetString(5);
                        objDTO.State            = objDR.GetString(6);
                        objDTO.ZipCode          = objDR.GetString(7);
                        objDTO.Country          = objDR.GetString(8);
                        objDTO.CreditLimit      = objDR.GetDecimal(9);
                        objDTO.ActivationStatus = objDR.GetBoolean(10);
                        //Add Data Transfer Object to the collection
                        colRecordList.Add(objDTO);
                    }//End of loop
                     //Return the collection
                    return(colRecordList);
                }
                else
                {
                    //Terminate ADO Objects
                    objDR.Close();
                    objDR = null;
                    objCmd.Dispose();
                    objCmd = null;
                    //return null since no records found
                    return(null);
                } //End of if/else
            }     //End of try
             //Trap for BO, App & General Exceptions
            catch (Exception objE)
            {
                //throw system exception since run time error has occurred.
                throw new Exception("Unexpected Error in CreditCardADO GetAllChildKeysOwnedByParent() Method:{ 0 } " + objE.Message);
            }
            finally
            {
                //Terminate connection
                objConn.Close();
                objConn.Dispose();
                objConn = null;
            }
        }//End of GetAllChildRecordsOwnedByParent
Exemplo n.º 3
0
        }//End of GetAllChildKeysOwnedByParent

        bool ICreditCardDAO.InsertChildObjectOfAParenet(string parentKey, CreditCardDTO objDTO)
        {
            throw new NotImplementedException();
        }
Exemplo n.º 4
0
        public CreditCardDTO GetRecordByID(string key)
        {
            //Create Connection, assign Connection to string
            SqlConnection objConn = new SqlConnection(SQLServerDAOFactory.ConnectionString());

            //Step A-Start Error Trapping
            try
            {
                //Open connection
                objConn.Open();
                //Create SQL string
                string strSQL = "SELECT * FROM CreditCard WHERE CardNumber = @CardNumber;";
                //Create Command object, pass query and connection object
                SqlCommand objCmd = new SqlCommand(strSQL, objConn);
                //SET CommandType Property to text since we have a query string & NOT a Stored-Procedure
                //For stored procedures syntax is objCmd.CommandType = CommandType.StoredProcedure;
                objCmd.CommandType = CommandType.Text;
                //Step 6-Add Parameter to. NOTE WE ARE ASSIGNING METHOD PARAMETER
                objCmd.Parameters.Add("@CardNumber", SqlDbType.VarChar).Value = key;
                //Create DATAREADER POINTER & Execute Query via
                //COMMAND OBJECT ExecuteReader Method which returns a populated
                //DATAREADER OBJECT with the results of the query
                SqlDataReader objDR = objCmd.ExecuteReader();
                //Test to make sure there is data in the DataReader Object
                if (objDR.HasRows)
                {
                    //Create Data Transfer Object
                    CreditCardDTO objDTO = new CreditCardDTO();
                    //Call Read() Method to point and read the first record
                    objDR.Read();
                    //Extract data from a row s Object Populates itself.
                    //IMPORTANT! Note that data must be extracted in the ORDER
                    //in which the QUERY RETURNS THE DATA.
                    objDTO.CardNumber       = objDR.GetString(0);
                    objDTO.CardOwnerName    = objDR.GetString(1);
                    objDTO.MerchantName     = objDR.GetString(2);
                    objDTO.ExpirationDate   = objDR.GetDateTime(3);
                    objDTO.AddressLine1     = objDR.GetString(4);
                    objDTO.City             = objDR.GetString(5);
                    objDTO.State            = objDR.GetString(6);
                    objDTO.ZipCode          = objDR.GetString(7);
                    objDTO.Country          = objDR.GetString(8);
                    objDTO.CreditLimit      = Convert.ToDecimal(objDR.GetInt32(9));
                    objDTO.ActivationStatus = objDR.GetBoolean(10);
                    //Return Data Transfer Object
                    return(objDTO);
                }
                //Terminate ADO Objects
                objDR.Close();
                objDR = null;
                objCmd.Dispose();
                objCmd = null;
                //return null since no data found
                return(null);
            }//End of try
             //Trap for BO, App & General Exceptions
            catch (Exception objE)
            {
                //throw system exception since run time error has occurred.
                throw new Exception("Unexpected Error in CreditCardADO GetRecordByID(key) Method:{ 0 } " + objE.Message);
            }
            finally
            {
                //Terminate connection
                objConn.Close();
                objConn.Dispose();
                objConn = null;
            }
        }//End of GetRecordByID