/// <summary>Get Client</summary> /// <cardinality>Many</cardinality> public List <GetClientWithFilterData> GetClientWithFilter( System.String lastName , System.String clientTypeRcd ) { var ret = new List <GetClientWithFilterData>(); string sql = @"select c.first_name ,c.middle_name ,c.last_name ,c.passenger_type_rcd ,ptr.passenger_type_name ,c.nationality_rcd ,nr.nationality_name ,c.gender_rcd ,gr.gender_name ,c.title_rcd ,tr.title_name ,c.client_type_rcd ,ctr.client_type_name ,c.user_id ,du.default_user_name ,c.date_time ,c.client_id ,c.default_user_id from client as c inner join default_user as du on du.default_user_id = c.user_id left join passenger_type_ref as ptr on ptr.passenger_type_rcd = c.passenger_type_rcd left join nationality_ref as nr on nr.nationality_rcd = c.nationality_rcd left join gender_ref as gr on gr.gender_rcd = c.gender_rcd left join title_ref as tr on tr.title_rcd = c.title_rcd left join client_type_ref as ctr on ctr.client_type_rcd = c.client_type_rcd "; using (var conn = new SqlConnection(Conn.ConnectionString)) { conn.Open(); conn.BeginTransaction(IsolationLevel.ReadUncommitted).Commit(); using (var command = new SqlCommand(sql, conn)) { //command.Parameters.Add("@last_name", SqlDbType.NVarChar).Value = lastName; //command.Parameters.Add("@client_type_rcd", SqlDbType.NVarChar).Value = clientTypeRcd; Logging log = Logging.PerformanceTimeStart("GetClientWithFilter"); IDataReader reader = command.ExecuteReader(CommandBehavior.SingleResult); log.PerformanceTimeStop(sql, command); var ordinals = new GetClientWithFilterDataOrdinals(reader); while (reader.Read()) { var data = new GetClientWithFilterData(); 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, GetClientWithFilterDataOrdinals ordinals) { if (!reader.IsDBNull(ordinals.FirstName)) { FirstName = reader.GetString(ordinals.FirstName); } if (!reader.IsDBNull(ordinals.MiddleName)) { MiddleName = reader.GetString(ordinals.MiddleName); } if (!reader.IsDBNull(ordinals.LastName)) { LastName = reader.GetString(ordinals.LastName); } if (!reader.IsDBNull(ordinals.PassengerTypeRcd)) { PassengerTypeRcd = reader.GetString(ordinals.PassengerTypeRcd); } if (!reader.IsDBNull(ordinals.PassengerTypeName)) { PassengerTypeName = reader.GetString(ordinals.PassengerTypeName); } if (!reader.IsDBNull(ordinals.NationalityRcd)) { NationalityRcd = reader.GetString(ordinals.NationalityRcd); } if (!reader.IsDBNull(ordinals.NationalityName)) { NationalityName = reader.GetString(ordinals.NationalityName); } if (!reader.IsDBNull(ordinals.GenderRcd)) { GenderRcd = reader.GetString(ordinals.GenderRcd); } if (!reader.IsDBNull(ordinals.GenderName)) { GenderName = reader.GetString(ordinals.GenderName); } if (!reader.IsDBNull(ordinals.TitleRcd)) { TitleRcd = reader.GetString(ordinals.TitleRcd); } if (!reader.IsDBNull(ordinals.TitleName)) { TitleName = reader.GetString(ordinals.TitleName); } if (!reader.IsDBNull(ordinals.ClientTypeRcd)) { ClientTypeRcd = reader.GetString(ordinals.ClientTypeRcd); } if (!reader.IsDBNull(ordinals.ClientTypeName)) { ClientTypeName = reader.GetString(ordinals.ClientTypeName); } 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.ClientId)) { ClientId = reader.GetGuid(ordinals.ClientId); } if (!reader.IsDBNull(ordinals.DefaultUserId)) { DefaultUserId = reader.GetGuid(ordinals.DefaultUserId); } }