//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 }
//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 }