/// <summary> /// List client contracts /// </summary> /// <param name="clientID"></param> internal static List <ClientContract> List(int clientID) { List <ClientContract> clientContractList = new List <ClientContract>(); using (var connection = new SqlConnection(ConnString.ConnectionString)) { var commandString = string.Format( " SELECT " + ClientContractFieldsString() + " FROM [ClientContract] " + " WHERE FKCompanyUID = {0}", clientID); using (var command = new SqlCommand( commandString, connection)) { connection.Open(); using (SqlDataReader reader = command.ExecuteReader()) { while (reader.Read()) { ClientContract _clientContract = new ClientContract(); LoadClientContractObject(reader, _clientContract); clientContractList.Add(_clientContract); } } } } return(clientContractList); }
public static ResponseStatus GetValidContractOnDate(int clientID, DateTime date) { ResponseStatus ret = new ResponseStatus(); ret.Message = "Valid contract not found."; ret.ReturnCode = 0001; ret.ReasonCode = 0002; ret.UniqueCode = ResponseStatus.MessageCode.Warning.FCMWAR00000002; ClientContract clientContract = new ClientContract(); string dateString = date.ToString().Substring(0, 10); using (var connection = new SqlConnection(ConnString.ConnectionString)) { var commandString = " SELECT " + ClientContractFieldsString() + " FROM [ClientContract] " + " WHERE FKCompanyUID = @FKCompanyUID " + " AND StartDate <= @StartDate " + " AND EndDate >= @EndDate "; using (var command = new SqlCommand(commandString, connection)) { command.Parameters.Add("@FKCompanyUID", SqlDbType.BigInt).Value = clientID; command.Parameters.Add("@StartDate", SqlDbType.DateTime).Value = date; command.Parameters.Add("@EndDate", SqlDbType.DateTime).Value = date; connection.Open(); using (SqlDataReader reader = command.ExecuteReader()) { while (reader.Read()) { ClientContract _clientContract = new ClientContract(); LoadClientContractObject(reader, _clientContract); clientContract = _clientContract; ret.Message = "Valid contract found."; ret.ReturnCode = 0001; ret.ReasonCode = 0001; ret.XMessageType = MessageType.Informational; ret.UniqueCode = ResponseStatus.MessageCode.Informational.FCMINF00000001; break; } } } } ret.Contents = clientContract; return(ret); }
// var student1 = new Student{firstName = "Bruce", lastName = "Willis"}; /// <summary> /// Get Employee details /// </summary> internal static ClientContract Read(int clientContractUID) { // // EA SQL database // ClientContract clientContract = new ClientContract(); using (var connection = new SqlConnection(ConnString.ConnectionString)) { var commandString = " SELECT " + ClientContractFieldsString() + " FROM [ClientContract]" + " WHERE UID = @UID"; using (var command = new SqlCommand( commandString, connection)) { connection.Open(); command.Parameters.Add("@UID", SqlDbType.BigInt).Value = clientContractUID; SqlDataReader reader = command.ExecuteReader(); if (reader.Read()) { try { LoadClientContractObject(reader, clientContract); } catch (Exception) { clientContract.UID = 0; } } } } return(clientContract); }
/// <summary> /// This method loads the information from the sqlreader into the Employee object /// </summary> /// <param name="reader"></param> /// <param name="clientContract"> </param> private static void LoadClientContractObject(SqlDataReader reader, ClientContract clientContract) { clientContract.FKCompanyUID = Convert.ToInt32(reader[FieldName.FKCompanyUID]); clientContract.UID = Convert.ToInt32(reader[FieldName.UID].ToString()); clientContract.ExternalID = reader[FieldName.ExternalID].ToString(); try { clientContract.Status = reader[FieldName.Status].ToString(); } catch { clientContract.Status = "ACTIVE"; } try { clientContract.Type = reader[FieldName.Type].ToString(); } catch { clientContract.Type = "BASIC"; } try { clientContract.StartDate = Convert.ToDateTime(reader[FieldName.StartDate].ToString()); } catch { clientContract.StartDate = DateTime.Now; } try { clientContract.EndDate = Convert.ToDateTime(reader[FieldName.EndDate].ToString()); } catch { clientContract.EndDate = DateTime.Now; } clientContract.UserIdCreatedBy = reader[FieldName.UserIdCreatedBy].ToString(); clientContract.UserIdUpdatedBy = reader[FieldName.UserIdUpdatedBy].ToString(); try { clientContract.UpdateDateTime = Convert.ToDateTime(reader[FieldName.UpdateDateTime].ToString()); } catch { clientContract.UpdateDateTime = DateTime.Now; } try { clientContract.CreationDateTime = Convert.ToDateTime(reader[FieldName.CreationDateTime].ToString()); } catch { clientContract.CreationDateTime = DateTime.Now; } }