/// <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.client_type_rcd ,ptr.client_type_name ,c.client_nationality_rcd ,nr.client_nationality_name ,c.client_gender_rcd ,gr.client_gender_name ,c.client_title_rcd ,tr.client_title_name ,c.user_id ,du.default_user_name ,c.date_time ,c.client_id ,c.user_id as client_user_id from client as c inner join default_user as du on du.default_user_id = c.user_id left join client_type_ref as ptr on ptr.client_type_rcd = c.client_type_rcd left join client_nationality_ref as nr on nr.client_nationality_rcd = c.client_nationality_rcd left join client_gender_ref as gr on gr.client_gender_rcd = c.client_gender_rcd left join client_title_ref as tr on tr.client_title_rcd = c.client_title_rcd left join client_type_ref as ctr on ctr.client_type_rcd = c.client_type_rcd "; using (var conn = new SqlConnection(ConfigurationManager.AppSettings["Conn"])) { 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: // 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.ClientTypeRcd)) { ClientTypeRcd = reader.GetString(ordinals.ClientTypeRcd); } if (!reader.IsDBNull(ordinals.ClientTypeName)) { ClientTypeName = reader.GetString(ordinals.ClientTypeName); } if (!reader.IsDBNull(ordinals.ClientNationalityRcd)) { ClientNationalityRcd = reader.GetString(ordinals.ClientNationalityRcd); } if (!reader.IsDBNull(ordinals.ClientNationalityName)) { ClientNationalityName = reader.GetString(ordinals.ClientNationalityName); } if (!reader.IsDBNull(ordinals.ClientGenderRcd)) { ClientGenderRcd = reader.GetString(ordinals.ClientGenderRcd); } if (!reader.IsDBNull(ordinals.ClientGenderName)) { ClientGenderName = reader.GetString(ordinals.ClientGenderName); } if (!reader.IsDBNull(ordinals.ClientTitleRcd)) { ClientTitleRcd = reader.GetString(ordinals.ClientTitleRcd); } if (!reader.IsDBNull(ordinals.ClientTitleName)) { ClientTitleName = reader.GetString(ordinals.ClientTitleName); } 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.ClientUserId)) { ClientUserId = reader.GetGuid(ordinals.ClientUserId); } }