//main method - pass an enum so it knows which query to call
        public DataTable GetPRCDocumentData(PRCReturnDataTableWrapperTypes aType, long primaryParamater)
        {
            
            //assign to this connection 
            using (SqlConnection theConn = new SqlConnection()) {

                //set up the connection
                string connectionString = connection.ConnectionString;
                theConn.ConnectionString = connectionString;

                //depending on which type it is, call all the necessary Commands/populate the tables
                using (SqlDataAdapter anAdaptor = new SqlDataAdapter())
                {
                    //create a command to hold whichever SQLCommand we need
                    PVillaSQLCommands aSQLCommand = new PVillaSQLCommands();
                                        
                    DataSet aDataSet = new DataSet();

                    //check which command has been called, assign it, then pull back our data
                    switch(aType)
                    {
                        case PRCReturnDataTableWrapperTypes.CustomerByCustomerID:  
                            aSQLCommand.AddStandardCustomerQueryParams();
                            anAdaptor.SelectCommand = aSQLCommand.StandardCustomerSQLCommand;
                            anAdaptor.SelectCommand.Connection = theConn;
                            anAdaptor.SelectCommand.Parameters["@CustomerID"].Value = primaryParamater;
                            DataTable CustomerDataTable = new DataTable();
                            anAdaptor.Fill(CustomerDataTable);

                            //add a date for display
                            System.Data.DataColumn col = new System.Data.DataColumn("CurrentDate", typeof(string));
                            col.DefaultValue = DateTime.Now.ToString("dd/MM/yyyy");
                            CustomerDataTable.Columns.Add(col);

                            return CustomerDataTable;
                       
                        case PRCReturnDataTableWrapperTypes.CustomerBankDetailByCustomerID:
                            aSQLCommand.AddStandardCustomerBankDetailQueryParams();
                            anAdaptor.SelectCommand = aSQLCommand.StandardCustomerBankDetailCommand;
                            anAdaptor.SelectCommand.Connection = theConn;
                            anAdaptor.SelectCommand.Parameters["@CustomerID"].Value = primaryParamater;
                            DataTable CustomerBankDetailDataTable = new DataTable();
                            anAdaptor.Fill(CustomerBankDetailDataTable);
                            return CustomerBankDetailDataTable;
                          
                      
                        case PRCReturnDataTableWrapperTypes.BookingByBookingID:
                            aSQLCommand.AddStandardBookingQueryParams();
                            anAdaptor.SelectCommand = aSQLCommand.StandardBookingSQLCommand;
                            anAdaptor.SelectCommand.Connection = theConn;
                            anAdaptor.SelectCommand.Parameters["@BookingID"].Value = primaryParamater;
                            anAdaptor.Fill(BookingDataTable);
                            //change column name


                            BookingDataTable.Columns["StartDate"].ColumnName = "BookingStartDate";
                            BookingDataTable.Columns["EndDate"].ColumnName = "BookingEndDate";

                            //BookingDataTable.Rows[0]["BookingStartDate"] = ((DateTime)BookingDataTable.Rows[0]["BookingStartDate"]).ToLongDateString();//.ToString("dd/MM/yyyy");
                            //BookingDataTable.Rows[0]["BookingEndDate"] = ((DateTime)BookingDataTable.Rows[0]["BookingEndDate"]).ToLongDateString();//.ToString("dd/MM/yyyy");
                            var test = ((DateTime)BookingDataTable.Rows[0]["BookingStartDate"]).ToLongDateString();

                            BookingDataTable.Rows[0].SetField("BookingStartDate", ((DateTime)BookingDataTable.Rows[0]["BookingStartDate"]).ToLongDateString());
                            BookingDataTable.Rows[0].SetField("BookingEndDate", ((DateTime)BookingDataTable.Rows[0]["BookingEndDate"]).ToLongDateString());                                                       

                            return BookingDataTable;
                                                 
                        case PRCReturnDataTableWrapperTypes.BookingParticipantByBookingID:
                            aSQLCommand.AddStandardStandardBookingParticipantQueryParams();
                            anAdaptor.SelectCommand = aSQLCommand.StandardBookingParticipantSQLCommand;
                            anAdaptor.SelectCommand.Connection = theConn;
                            anAdaptor.SelectCommand.Parameters["@BookingID"].Value = primaryParamater;
                            DataTable BookingParticipantDataTable = new DataTable();
                            anAdaptor.Fill(BookingParticipantDataTable);
                            return BookingParticipantDataTable;


                        case PRCReturnDataTableWrapperTypes.BookingExtraParticipantByBookingExtraSelectionID:
                            aSQLCommand.AddBookingExtraParticipantByBookingExtraSelectionIDQueryParams();
                            anAdaptor.SelectCommand = aSQLCommand.BookingExtraParticipantByBookingExtraSelectionIDSQLCommand;
                            anAdaptor.SelectCommand.Connection = theConn;
                            anAdaptor.SelectCommand.Parameters["@BookingExtraSelectionID"].Value = primaryParamater;
                            anAdaptor.Fill(BookingExtraSelectionDataTable);
                            
                            return BookingExtraSelectionDataTable;
                            
                   
                        case PRCReturnDataTableWrapperTypes.BookingExtraSelectionByCustomerID:
                            aSQLCommand.AddBookingExtraSelectionByCustomerIDQueryParams();
                            anAdaptor.SelectCommand = aSQLCommand.BookingExtraSelectionByCustomerIDSQLCommand;
                            anAdaptor.SelectCommand.Connection = theConn;
                            anAdaptor.SelectCommand.Parameters["@CustomerID"].Value = primaryParamater;
                            anAdaptor.Fill(BookingExtraSelectionDataTable);
                            return BookingExtraSelectionDataTable;
                      
                        case PRCReturnDataTableWrapperTypes.BookingExtraSelectionByBookingID:
                            aSQLCommand.AddBookingExtraSelectionByBookingIDQueryParams();
                            anAdaptor.SelectCommand = aSQLCommand.BookingExtraSelectionByBookingIDSQLCommand;
                            anAdaptor.SelectCommand.Connection = theConn;
                            anAdaptor.SelectCommand.Parameters["@BookingID"].Value = primaryParamater;
                            anAdaptor.Fill(BookingExtraSelectionDataTable);
                            return BookingExtraSelectionDataTable;


                        case PRCReturnDataTableWrapperTypes.BookingExtraSelectionByBookingExtraSelectionID:
                            aSQLCommand.AddStandardBookingExtraSelectionQueryParams();
                            anAdaptor.SelectCommand = aSQLCommand.StandardBookingExtraSelectionSQLCommand;
                            anAdaptor.SelectCommand.Connection = theConn;
                            anAdaptor.SelectCommand.Parameters["@BookingExtraSelectionID"].Value = primaryParamater;
                            anAdaptor.Fill(BookingExtraSelectionDataTable);

                            //BookingExtraSelectionDataTable.Rows[0]["ExtraRentalDate"] = ((DateTime)BookingExtraSelectionDataTable.Rows[0]["ExtraRentalDate"]).ToString("MM/dd/yyyy");
                            //BookingExtraSelectionDataTable.Rows[0]["ExtraReturnDate"] = ((DateTime)BookingExtraSelectionDataTable.Rows[0]["ExtraReturnDate"]).ToString("MM/dd/yyyy");
                            return BookingExtraSelectionDataTable;
                            

                    
                        case PRCReturnDataTableWrapperTypes.BookingExtraParticipantByBookingExtraParticipantID:
                            aSQLCommand.AddStandardBookingExtraParticipantQueryParams();
                            anAdaptor.SelectCommand = aSQLCommand.StandardBookingExtraParticipantSQLCommand;
                            DataTable BookingExtraParticipantDataTable = new DataTable();
                            anAdaptor.SelectCommand.Connection = theConn;
                            anAdaptor.SelectCommand.Parameters["@BookingExtraParticipantID"].Value = primaryParamater;
                            anAdaptor.Fill(BookingExtraParticipantDataTable);
                            return BookingExtraParticipantDataTable;
                            
                        case PRCReturnDataTableWrapperTypes.PropertyByPropertyID:
                            aSQLCommand.AddStandardPropertyQueryParams();
                            anAdaptor.SelectCommand = aSQLCommand.StandardPropertySQLCommand;
                            anAdaptor.SelectCommand.Connection = theConn;
                            anAdaptor.SelectCommand.Parameters["@PropertyID"].Value = primaryParamater;
                            DataTable PropertyDataTable = new DataTable();
                            anAdaptor.Fill(PropertyDataTable);
                            return PropertyDataTable;
                            
                            
                        case PRCReturnDataTableWrapperTypes.StandardPRCInformation:
                            aSQLCommand.AddStandardPRCInformationQueryParams();
                            anAdaptor.SelectCommand = aSQLCommand.StandardPRCInformationSQLCommand;
                            anAdaptor.SelectCommand.Connection = theConn;
                            anAdaptor.SelectCommand.Parameters["@PRCInformationID"].Value = 1;
                            DataTable PRCInformationDataTable = new DataTable();
                            anAdaptor.Fill(PRCInformationDataTable);
                            return PRCInformationDataTable;
                           
                        case PRCReturnDataTableWrapperTypes.PropertyRegionByPropertyRegionID:
                            aSQLCommand.AddStandardPropertyRegionQueryParams();
                            anAdaptor.SelectCommand = aSQLCommand.StandardPropertyRegionSQLCommand;
                            anAdaptor.SelectCommand.Connection = theConn;
                            anAdaptor.SelectCommand.Parameters["@PropertyRegionID"].Value = primaryParamater;
                            DataTable PropertyRegionDataTable = new DataTable();
                            anAdaptor.Fill(PropertyRegionDataTable);
                            return PropertyRegionDataTable;

                        case PRCReturnDataTableWrapperTypes.PropertyTownByPropertyTownID:
                            aSQLCommand.AddStandardPropertyTownQueryParams();
                            anAdaptor.SelectCommand = aSQLCommand.StandardPropertyTownSQLCommand;
                            anAdaptor.SelectCommand.Connection = theConn;
                            anAdaptor.SelectCommand.Parameters["@PropertyTownID"].Value = primaryParamater;
                            DataTable PropertyTownDataTable = new DataTable();
                            anAdaptor.Fill(PropertyTownDataTable);
                            return PropertyTownDataTable;



                        case PRCReturnDataTableWrapperTypes.BookingExtraByBookingExtraID:
                            aSQLCommand.AddStandardBookingExtraQueryParams();
                            anAdaptor.SelectCommand = aSQLCommand.StandardBookingExtraSQLCommand;
                            anAdaptor.SelectCommand.Connection = theConn;
                            anAdaptor.SelectCommand.Parameters["@BookingExtraID"].Value = primaryParamater;
                            DataTable BookingExtraDataTable = new DataTable();
                            anAdaptor.Fill(BookingExtraDataTable);

                            BookingExtraDataTable.Columns["LegacyReference"].ColumnName = "ExtraLegacyReference";
                            return BookingExtraDataTable;

                        case PRCReturnDataTableWrapperTypes.StandardPropertyOwner:
                            aSQLCommand.AddStandardPropertyOwnerQueryParams();
                            anAdaptor.SelectCommand = aSQLCommand.StandardPropertyOwnerSQLCommand;
                            anAdaptor.SelectCommand.Connection = theConn;
                            anAdaptor.SelectCommand.Parameters["@PropertyID"].Value = primaryParamater;
                            DataTable PropertyOwnerDataTable = new DataTable();
                            anAdaptor.Fill(PropertyOwnerDataTable);
                            return PropertyOwnerDataTable;
                        //added 06 -05 -2013 by ND


                        case PRCReturnDataTableWrapperTypes.StandardBookingParentContainer:
                            aSQLCommand.AddStandardBookingParentContainerParams();
                            anAdaptor.SelectCommand = aSQLCommand.StandardBookingParentContainerCommand;
                            anAdaptor.SelectCommand.Connection = theConn;
                            anAdaptor.SelectCommand.Parameters["@BookingParentContainerID"].Value = primaryParamater;
                            DataTable BookingParentContainerTable = new DataTable();
                            anAdaptor.Fill(BookingParentContainerTable);
                            //change column name
                            return BookingParentContainerTable;



                        case PRCReturnDataTableWrapperTypes.BookingExtraAttributesByBookingExtraID:
                            aSQLCommand.AddBookingExtraAttributesByBookingExtraIDParams();
                            anAdaptor.SelectCommand = aSQLCommand.BookingExtraAttributesByBookingExtraID;
                            anAdaptor.SelectCommand.Connection = theConn;
                            anAdaptor.SelectCommand.Parameters["@BookingExtraID"].Value = primaryParamater;
                            DataTable BookingExtraAttributesTable = new DataTable();
                            anAdaptor.Fill(BookingExtraAttributesTable);
                            //change column name
                            return BookingExtraAttributesTable;                            

                    }

                    return NullResults;
               

                    
                }

                 
                           
            
            }

           

        //end method
        }
示例#2
0
        //main method - pass an enum so it knows which query to call
        public DataTable GetPRCDocumentData(PRCReturnDataTableWrapperTypes aType, long primaryParamater)
        {
            //assign to this connection
            using (SqlConnection theConn = new SqlConnection()) {

                //set up the connection
                string connectionString = connection.ConnectionString;
                theConn.ConnectionString = connectionString;

                //depending on which type it is, call all the necessary Commands/populate the tables
                using (SqlDataAdapter anAdaptor = new SqlDataAdapter())
                {
                    //create a command to hold whichever SQLCommand we need
                    PVillaSQLCommands aSQLCommand = new PVillaSQLCommands();

                    DataSet aDataSet = new DataSet();

                    //check which command has been called, assign it, then pull back our data
                    switch(aType)
                    {
                        case PRCReturnDataTableWrapperTypes.CustomerByCustomerID:
                            aSQLCommand.AddStandardCustomerQueryParams();
                            anAdaptor.SelectCommand = aSQLCommand.StandardCustomerSQLCommand;
                            anAdaptor.SelectCommand.Connection = theConn;
                            anAdaptor.SelectCommand.Parameters["@CustomerID"].Value = primaryParamater;
                            DataTable CustomerDataTable = new DataTable();
                            anAdaptor.Fill(CustomerDataTable);
                            return CustomerDataTable;

                        case PRCReturnDataTableWrapperTypes.CustomerBankDetailByCustomerID:
                            aSQLCommand.AddStandardCustomerBankDetailQueryParams();
                            anAdaptor.SelectCommand = aSQLCommand.StandardCustomerBankDetailCommand;
                            anAdaptor.SelectCommand.Connection = theConn;
                            anAdaptor.SelectCommand.Parameters["@CustomerID"].Value = primaryParamater;
                            DataTable CustomerBankDetailDataTable = new DataTable();
                            anAdaptor.Fill(CustomerBankDetailDataTable);
                            return CustomerBankDetailDataTable;

                        case PRCReturnDataTableWrapperTypes.BookingByBookingID:
                            aSQLCommand.AddStandardBookingQueryParams();
                            anAdaptor.SelectCommand = aSQLCommand.StandardBookingSQLCommand;
                            anAdaptor.SelectCommand.Connection = theConn;
                            anAdaptor.SelectCommand.Parameters["@BookingID"].Value = primaryParamater;
                            anAdaptor.Fill(BookingDataTable);
                            //rename columns

                            //convert the date
                            DateTime DateConvert = (DateTime)BookingDataTable.Rows[0]["StartDate"];
                            String ConvertedDate = DateConvert.ToString("dd/M/yyyy");
                            BookingDataTable.Rows[0]["StartDate"] = ConvertedDate;
                            //create new row that is just a date
                            DataRow DateRow = BookingDataTable.NewRow();
                            DateRow["StartDate"] = ConvertedDate;
                            BookingDataTable.Rows.Add(DateRow);
                            //change column name
                            BookingDataTable.Columns["StartDate"].ColumnName = "BookingStartDate";
                            BookingDataTable.Columns["EndDate"].ColumnName = "BookingEndDate";

                            return BookingDataTable;

                        case PRCReturnDataTableWrapperTypes.BookingParticipantByBookingID:
                            aSQLCommand.AddStandardStandardBookingParticipantQueryParams();
                            anAdaptor.SelectCommand = aSQLCommand.StandardBookingParticipantSQLCommand;
                            anAdaptor.SelectCommand.Connection = theConn;
                            anAdaptor.SelectCommand.Parameters["@BookingID"].Value = primaryParamater;
                            DataTable BookingParticipantDataTable = new DataTable();
                            anAdaptor.Fill(BookingParticipantDataTable);
                            return BookingDataTable;

                        case PRCReturnDataTableWrapperTypes.BookingExtraParticipantByBookingExtraSelectionID:
                            aSQLCommand.AddBookingExtraParticipantByBookingExtraSelectionIDQueryParams();
                            anAdaptor.SelectCommand = aSQLCommand.BookingExtraParticipantByBookingExtraSelectionIDSQLCommand;
                            anAdaptor.SelectCommand.Connection = theConn;
                            anAdaptor.SelectCommand.Parameters["@BookingExtraSelectionID"].Value = primaryParamater;
                            anAdaptor.Fill(BookingExtraSelectionDataTable);
                            return BookingExtraSelectionDataTable;

                        case PRCReturnDataTableWrapperTypes.BookingExtraSelectionByCustomerID:
                            aSQLCommand.AddBookingExtraSelectionByCustomerIDQueryParams();
                            anAdaptor.SelectCommand = aSQLCommand.BookingExtraSelectionByCustomerIDSQLCommand;
                            anAdaptor.SelectCommand.Connection = theConn;
                            anAdaptor.SelectCommand.Parameters["@CustomerID"].Value = primaryParamater;
                            anAdaptor.Fill(BookingExtraSelectionDataTable);
                            return BookingExtraSelectionDataTable;

                        case PRCReturnDataTableWrapperTypes.BookingExtraSelectionByBookingID:
                            aSQLCommand.AddBookingExtraSelectionByBookingIDQueryParams();
                            anAdaptor.SelectCommand = aSQLCommand.BookingExtraSelectionByBookingIDSQLCommand;
                            anAdaptor.SelectCommand.Connection = theConn;
                            anAdaptor.SelectCommand.Parameters["@BookingID"].Value = primaryParamater;
                            anAdaptor.Fill(BookingExtraSelectionDataTable);
                            return BookingExtraSelectionDataTable;

                        case PRCReturnDataTableWrapperTypes.BookingExtraParticipantByBookingExtraParticipantID:
                            aSQLCommand.AddStandardBookingExtraParticipantQueryParams();
                            anAdaptor.SelectCommand = aSQLCommand.StandardBookingExtraParticipantSQLCommand;
                            DataTable BookingExtraParticipantDataTable = new DataTable();
                            anAdaptor.SelectCommand.Connection = theConn;
                            anAdaptor.SelectCommand.Parameters["@BookingExtraParticipantID"].Value = primaryParamater;
                            anAdaptor.Fill(BookingExtraParticipantDataTable);
                            return BookingExtraParticipantDataTable;

                        case PRCReturnDataTableWrapperTypes.PropertyByPropertyID:
                            aSQLCommand.AddStandardPropertyQueryParams();
                            anAdaptor.SelectCommand = aSQLCommand.StandardPropertySQLCommand;
                            anAdaptor.SelectCommand.Connection = theConn;
                            anAdaptor.SelectCommand.Parameters["@PropertyID"].Value = primaryParamater;
                            DataTable PropertyDataTable = new DataTable();
                            anAdaptor.Fill(PropertyDataTable);
                            return PropertyDataTable;

                        case PRCReturnDataTableWrapperTypes.StandardPRCInformation:
                            aSQLCommand.AddStandardPRCInformationQueryParams();
                            anAdaptor.SelectCommand = aSQLCommand.StandardPRCInformationSQLCommand;
                            anAdaptor.SelectCommand.Connection = theConn;
                            anAdaptor.SelectCommand.Parameters["@PRCInformationID"].Value = 1;
                            DataTable PRCInformationDataTable = new DataTable();
                            anAdaptor.Fill(PRCInformationDataTable);
                            return PRCInformationDataTable;

                        case PRCReturnDataTableWrapperTypes.PropertyRegionByPropertyRegionID:
                            aSQLCommand.AddStandardPropertyRegionQueryParams();
                            anAdaptor.SelectCommand = aSQLCommand.StandardPropertyRegionSQLCommand;
                            anAdaptor.SelectCommand.Connection = theConn;
                            anAdaptor.SelectCommand.Parameters["@PropertyRegionID"].Value = primaryParamater;
                            DataTable PropertyRegionDataTable = new DataTable();
                            anAdaptor.Fill(PropertyRegionDataTable);
                            return PropertyRegionDataTable;

                        case PRCReturnDataTableWrapperTypes.BookingExtraByBookingExtraID:
                            aSQLCommand.AddStandardBookingExtraQueryParams();
                            anAdaptor.SelectCommand = aSQLCommand.StandardBookingExtraSQLCommand;
                            anAdaptor.SelectCommand.Connection = theConn;
                            anAdaptor.SelectCommand.Parameters["@BookingExtraID"].Value = primaryParamater;
                            DataTable BookingExtraDataTable = new DataTable();
                            anAdaptor.Fill(BookingExtraDataTable);
                            return BookingExtraDataTable;

                        case PRCReturnDataTableWrapperTypes.StandardPropertyOwner:
                            aSQLCommand.AddStandardPropertyOwnerQueryParams();
                            anAdaptor.SelectCommand = aSQLCommand.StandardPropertyOwnerSQLCommand;
                            anAdaptor.SelectCommand.Connection = theConn;
                            anAdaptor.SelectCommand.Parameters["@PropertyID"].Value = primaryParamater;
                            DataTable PropertyOwnerDataTable = new DataTable();
                            anAdaptor.Fill(PropertyOwnerDataTable);
                            return PropertyOwnerDataTable;
                        //added 06 -05 -2013 by ND

                    }

                    return NullResults;

                }

            }

            //end method
        }