/// <summary>Get Service Package</summary> /// <cardinality>Many</cardinality> public List <GetServicePackageData> GetServicePackage( System.Guid servicePackageId ) { var ret = new List <GetServicePackageData>(); string sql = @"select sp.service_package_name ,sp.service_hotel_id ,sh.hotel_name as hotel_name ,sp.service_special_service_request_id ,sssr.service_special_service_request_name as service_special_service_request_name ,sp.service_car_rental_id ,scr.car_name as car_name ,sp.departure_airport_id ,da.airport_name as departure_airport_name ,sp.arrival_airport_id ,aa.airport_name as arrival_airport_name ,sp.stay_duration_days ,sp.user_id ,du.default_user_name ,sp.date_time ,sp.service_package_id from service_package as sp left join service_hotel as sh on sh.service_hotel_id = sp.service_hotel_id left join service_special_service_request as sssr on sssr.service_special_service_request_id = sp.service_special_service_request_id left join service_car_rental as scr on scr.service_car_rental_id = sp.service_car_rental_id inner join airport as da on da.airport_id = sp.departure_airport_id inner join airport as aa on aa.airport_id = sp.arrival_airport_id inner join default_user as du on du.default_user_id = sp.user_id "; using (var conn = new SqlConnection(Conn.ConnectionString)) { conn.Open(); conn.BeginTransaction(IsolationLevel.ReadUncommitted).Commit(); using (var command = new SqlCommand(sql, conn)) { if (servicePackageId != Guid.Empty) { command.Parameters.Add("@service_package_id", SqlDbType.UniqueIdentifier).Value = servicePackageId; sql += " where sp.service_package_id = @service_package_id"; } Logging log = Logging.PerformanceTimeStart("GetServicePackage"); IDataReader reader = command.ExecuteReader(CommandBehavior.SingleResult); log.PerformanceTimeStop(sql, command); var ordinals = new GetServicePackageDataOrdinals(reader); while (reader.Read()) { var data = new GetServicePackageData(); data.Populate(reader, ordinals); ret.Add(data); } reader.Close(); } return(ret); } }
// use ordinals to speed up access to DataReader // links: // crud definition: https://en.wikipedia.org/wiki/Create,_read,_update_and_delete // docLink: http://sql2x.org/documentationLink/327451c3-64a8-4de8-b359-76742d634497 // parameters: // reader: IDataReader from SQLClient public void Populate(IDataReader reader, GetServicePackageDataOrdinals ordinals) { if (!reader.IsDBNull(ordinals.ServicePackageName)) { ServicePackageName = reader.GetString(ordinals.ServicePackageName); } if (!reader.IsDBNull(ordinals.ServiceHotelId)) { ServiceHotelId = reader.GetGuid(ordinals.ServiceHotelId); } if (!reader.IsDBNull(ordinals.HotelName)) { HotelName = reader.GetString(ordinals.HotelName); } if (!reader.IsDBNull(ordinals.ServiceSpecialServiceRequestId)) { ServiceSpecialServiceRequestId = reader.GetGuid(ordinals.ServiceSpecialServiceRequestId); } if (!reader.IsDBNull(ordinals.ServiceSpecialServiceRequestName)) { ServiceSpecialServiceRequestName = reader.GetString(ordinals.ServiceSpecialServiceRequestName); } if (!reader.IsDBNull(ordinals.ServiceCarRentalId)) { ServiceCarRentalId = reader.GetGuid(ordinals.ServiceCarRentalId); } if (!reader.IsDBNull(ordinals.CarName)) { CarName = reader.GetString(ordinals.CarName); } if (!reader.IsDBNull(ordinals.DepartureAirportId)) { DepartureAirportId = reader.GetGuid(ordinals.DepartureAirportId); } if (!reader.IsDBNull(ordinals.DepartureAirportName)) { DepartureAirportName = reader.GetString(ordinals.DepartureAirportName); } if (!reader.IsDBNull(ordinals.ArrivalAirportId)) { ArrivalAirportId = reader.GetGuid(ordinals.ArrivalAirportId); } if (!reader.IsDBNull(ordinals.ArrivalAirportName)) { ArrivalAirportName = reader.GetString(ordinals.ArrivalAirportName); } if (!reader.IsDBNull(ordinals.StayDurationDays)) { StayDurationDays = reader.GetInt32(ordinals.StayDurationDays); } if (!reader.IsDBNull(ordinals.UserId)) { UserId = reader.GetGuid(ordinals.UserId); } if (!reader.IsDBNull(ordinals.DefaultUserName)) { DefaultUserName = reader.GetString(ordinals.DefaultUserName); } if (!reader.IsDBNull(ordinals.DateTime)) { DateTime = reader.GetDateTime(ordinals.DateTime); } if (!reader.IsDBNull(ordinals.ServicePackageId)) { ServicePackageId = reader.GetGuid(ordinals.ServicePackageId); } }