/// <summary> /// Gets all by upload identifier paged. /// </summary> /// <param name="uploadId">The upload identifier.</param> /// <param name="gridSettings">The grid settings.</param> /// <returns></returns> public List <Review> GetAllByUploadIdPaged(int uploadId, GridSettings gridSettings) { if (string.IsNullOrEmpty(gridSettings.SortColumn)) { gridSettings.SortColumn = "ReviewId"; } const string sprocName = "PaReviewsGetAllByUpload"; var reviewList = new List <Review>(); using (var connection = new SqlConnection(DbConnection)) { using (var command = new SqlCommand(sprocName, connection)) { var sqlParams = new List <SqlParameter>(); var paramReturnValue = new SqlParameter("@return_value", SqlDbType.Int) { Direction = ParameterDirection.ReturnValue }; sqlParams.Add(paramReturnValue); SqlHelper.AddIntPara(uploadId, "@UploadId", sqlParams); SqlHelper.AddVarcharPara(gridSettings.SortColumn, "@sortColumnName", sqlParams); SqlHelper.AddVarcharPara(gridSettings.SortOrder, "@sortOrder", sqlParams); if (gridSettings.PageSize > 0) { SqlHelper.AddIntPara(gridSettings.PageSize, "@pageSize", sqlParams); } if (gridSettings.PageIndex > 0) { SqlHelper.AddIntPara(gridSettings.PageIndex, "@pageIndex", sqlParams); } if (gridSettings.IsSearch && gridSettings.Where != null) { foreach (var rule in gridSettings.Where.rules) { // convert rule into a parameter if (rule.field.IndexOf("Date", StringComparison.Ordinal) > -1) { SqlHelper.AddDatePara(DateTime.Parse(rule.data), "@" + rule.field, sqlParams); } else { if ((rule.field.IndexOf("Id", StringComparison.Ordinal) > -1) || (rule.field.IndexOf("Number", StringComparison.Ordinal) > -1)) { SqlHelper.AddBigIntPara(Int64.Parse(rule.data), "@" + rule.field, sqlParams); } else { SqlHelper.AddVarcharPara(rule.data, "@" + rule.field, sqlParams); var opValue = rule.op.Equals("eq") ? 1 : 0; SqlHelper.AddIntPara(opValue, "@" + rule.field + "Op", sqlParams); } } } } command.CommandType = CommandType.StoredProcedure; command.Parameters.AddRange(sqlParams.ToArray()); connection.Open(); LoadList(reviewList, command, sprocName); } } return(reviewList); }
/// <summary> /// Adds the specified entity. /// </summary> /// <param name="entity">The entity.</param> public void Add(Sample entity) { var sample = entity; Debug.Assert(sample != null, "sample != null"); using (var connection = new SqlConnection(DbConnection)) { using (var command = new SqlCommand("PaSampleInsert", connection)) { var sqlParams = new List <SqlParameter>(); SqlHelper.AddReturnPara("@return_value", sqlParams); SqlHelper.AddVarcharPara(sample.SessionKey, "@SessionKey", sqlParams); SqlHelper.AddVarcharPara(sample.ClaimTypeDescription, "@ClaimTypeDescription", sqlParams); SqlHelper.AddVarcharPara(sample.ContractTypeDescription, "@ContractTypeDescription", sqlParams); SqlHelper.AddVarcharPara(sample.SiteDescription, "@SiteDescription", sqlParams); SqlHelper.AddVarcharPara(sample.EsaDescription, "@EsaDescription", sqlParams); SqlHelper.AddVarcharPara(sample.OrgDescription, "@OrgDescription", sqlParams); SqlHelper.AddBigIntPara(sample.ClaimId, "@ClaimId", sqlParams); SqlHelper.AddIntPara(sample.ClaimSequenceNumber, "@ClaimSequenceNumber", sqlParams); SqlHelper.AddVarcharPara(sample.ClaimType, "@ClaimType", sqlParams); SqlHelper.AddMoneyPara(sample.ClaimAmount, "@ClaimMoney", sqlParams); SqlHelper.AddVarcharPara(sample.SiteCode, "@SiteCode", sqlParams); SqlHelper.AddVarcharPara(sample.SupervisingSiteCode, "@SupervisingSiteCode", sqlParams); SqlHelper.AddVarcharPara(sample.OrgCode, "@OrgCode", sqlParams); SqlHelper.AddBigIntPara(sample.ActivityId, "@ActivityId", sqlParams); SqlHelper.AddDatePara(sample.ClaimCreationDate, "@ClaimCreationDate", sqlParams); SqlHelper.AddVarcharPara(sample.StatusCode, "@ClaimStatusCode", sqlParams); SqlHelper.AddVarcharPara(sample.StatusCodeDescription, "@ClaimStatusDescription", sqlParams); SqlHelper.AddVarcharPara(sample.StateCode, "@StateCode", sqlParams); SqlHelper.AddVarcharPara(sample.ManagedBy, "@ManagedBy", sqlParams); SqlHelper.AddVarcharPara(sample.ContractId, "@ContractId", sqlParams); SqlHelper.AddVarcharPara(sample.ContractType, "@ContractType", sqlParams); SqlHelper.AddVarcharPara(sample.ContractTitle, "@ContractTitle", sqlParams); SqlHelper.AddVarcharPara(sample.EsaCode, "@EsaCode", sqlParams); SqlHelper.AddBigIntPara(sample.JobseekerId, "@JobseekerId", sqlParams); SqlHelper.AddVarcharPara(sample.GivenName, "@GivenName", sqlParams); SqlHelper.AddVarcharPara(sample.Surname, "@LastName", sqlParams); SqlHelper.AddCharPara(sample.AutoSpecialClaimFlag, "@AutoSpecialClaimFlag", sqlParams); SqlHelper.AddCharPara(sample.ManSpecialClaimFlag, "@ManualSpecialClaimFlag", sqlParams); SqlHelper.AddVarcharPara(sample.CreatedBy, "@CreatedBy", sqlParams); // Output parameters var paramId = new SqlParameter("@Id", SqlDbType.Int) { Direction = ParameterDirection.InputOutput, Value = 0 }; sqlParams.Add(paramId); command.CommandType = CommandType.StoredProcedure; command.Parameters.AddRange(sqlParams.ToArray()); connection.Open(); command.ExecuteNonQuery(); if (((Int32)command.Parameters["@return_value"].Value) != 0) { return; } sample.Id = (int)paramId.Value; } } }