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