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