I am calling the stored procedure from >net Core
[Authorize(Roles = "1,2,3")]
[HttpPost("SaveDocumentAndParties")]
public async Task<IActionResult> saveDocumentAndParties([FromBody] SaveDocumentParties sd)
{
string jsondata = string.Empty;
await _lock.WaitAsync().ConfigureAwait(false);
using (var dbContext = _dbContextFactory.CreateDbContext())
{
dbContext.Database.GetDbConnection().ConnectionString = sd.ConnectionString;
try
{
if (dbContext.Database.GetDbConnection().State == ConnectionState.Open)
{
dbContext.Database.GetDbConnection().Close();
}
//Open the connection
var Title = Regex.Replace(sd.Title, @"s+", "");
var actionParam = new SqlParameter("@Action", "Save");
var partiesHistoryJsonParam = new SqlParameter("@partiesHistoryJson", sd.arrayParties);
var exportExtraHistoryJsonParam = new SqlParameter("@exportExtraHistoryJson", sd.customField);
var fieldSettingsJsonParam = new SqlParameter("@FieldSettingsJson", sd.FieldSettings);
var codingSessionDetailsParam = new SqlParameter("@CodingSessionDetails", sd.CodingSessionDetails == "undefined" || String.IsNullOrEmpty(sd.CodingSessionDetails) == true ? null : sd.CodingSessionDetails);
var documentIdParam = new SqlParameter("@DocumentID", sd.Document_ID);
var projectIdParam = new SqlParameter("@ProjectId", sd.ProjectId);
var documentDateParam = new SqlParameter("@DocumentDate", sd.Document_Date);
var documentTypeParam = new SqlParameter("@DocumentType", sd.Document_Type);
var enteredByIdParam = new SqlParameter("@EnteredById", sd.EnterBy);
var estimatedParam = new SqlParameter("@Estimated", sd.Estimated);
var titleParam = new SqlParameter("@Title", sd.Title);
var codingQATimeParam = new SqlParameter("@CodingQATime", sd.CodingQATime);
var isCorrectedParam = new SqlParameter("@IsCorrected", sd.IsCorrected);
var userTaskParam = new SqlParameter("@UserTask", sd.UserTask);
var isHistoryDocumentParam = new SqlParameter("@isHistoryDocument", sd.isHistoryDocument);
var returnJSONResultParam = new SqlParameter("@ReturnJSONResult", null);
//var returnJSONResultParam = new SqlParameter("@ReturnJSONResult", System.Data.SqlDbType.NVarChar, -1);
//returnJSONResultParam.Direction = System.Data.ParameterDirection.Output;
var sql1 = "[dbo].[sp_SaveCodedDatas] {0},{1},{2},{3},{4},{5},{6},{7},{8},{9}";
// Execute the stored procedure
//Initialize the parameters
documentIdParam = documentIdParam ?? throw new ArgumentNullException(nameof(documentIdParam));
projectIdParam = projectIdParam ?? throw new ArgumentNullException(nameof(projectIdParam));
documentDateParam = documentDateParam ?? throw new ArgumentNullException(nameof(documentDateParam));
documentTypeParam = documentTypeParam ?? throw new ArgumentNullException(nameof(documentTypeParam));
enteredByIdParam = enteredByIdParam ?? throw new ArgumentNullException(nameof(enteredByIdParam));
estimatedParam = estimatedParam ?? throw new ArgumentNullException(nameof(estimatedParam));
titleParam = titleParam ?? throw new ArgumentNullException(nameof(titleParam));
codingQATimeParam = codingQATimeParam ?? throw new ArgumentNullException(nameof(codingQATimeParam));
isCorrectedParam = isCorrectedParam ?? throw new ArgumentNullException(nameof(isCorrectedParam));
userTaskParam = userTaskParam ?? throw new ArgumentNullException(nameof(userTaskParam));
var result = dbContext.Set<CodedData>().FromSqlRaw(sql1,
documentIdParam.Value,
projectIdParam.Value,
documentDateParam.Value,
documentTypeParam.Value,
enteredByIdParam.Value,
estimatedParam.Value,
titleParam.Value,
codingQATimeParam.Value,
isCorrectedParam.Value,
userTaskParam.Value).ToList();
var sql = "[dbo].[sp_SaveDcumentAndParties] {0},{1},{2},{3},{4},{5},{6},{7},{8},{9},{10},{11},{12},{13},{14},{15},{16}";
var jsonString = dbContext.JsonResponseResult.FromSqlRaw(sql,
actionParam.Value, partiesHistoryJsonParam.Value,
exportExtraHistoryJsonParam.Value,
fieldSettingsJsonParam.Value,
codingSessionDetailsParam.Value,
documentIdParam.Value,
projectIdParam.Value,
documentDateParam.Value,
documentTypeParam.Value,
enteredByIdParam.Value,
estimatedParam.Value,
titleParam.Value,
codingQATimeParam.Value,
isCorrectedParam.Value,
userTaskParam.Value,
isHistoryDocumentParam.Value,
returnJSONResultParam.Value).AsEnumerable().Select(x => x.JsonResponse).FirstOrDefault();
// Retrieve the output parameter value
//jsondata = returnJSONResultParam.Value.ToString();
// Deserialize the JSON string into a JObject dynamically
JObject jsonObject = JObject.Parse(jsonString);
// Create a dictionary to represent the JSON object
var jsonProperties = new Dictionary<string, object>();
// Loop through each property in the JObject
foreach (var property in jsonObject.Properties())
{
// Get the name and value of each property
jsonProperties[property.Name] = property.Value.ToString();
}
jsondata = JsonConvert.SerializeObject(jsonObject);
// Explicit refresh
await dbContext.SaveChangesAsync();
//await dbContext.CodedDatas.LoadAsync();
//await dbContext.Entry(dbContext.CodedDatas).ReloadAsync();
obj.Information("transaction completed - " + sd.Document_ID);
}
catch (SqlException ex) when (IsTransientFailure(ex))
{
obj.Information("SaveDocumentAndParties Transient Failure : " + ex.ToString() + ". " + sd.Document_ID);
jsondata = Newtonsoft.Json.JsonConvert.SerializeObject(
new { Response = "Transient Failure", Statuscode = StatusCodes.Status500InternalServerError });
}
catch (Exception ex)
{
if (ex.Message == "Sequence contains more than one element")
{
obj.Information("SaveDocumentAndParties : " + ex.ToString() + ". " + sd.Document_ID);
jsondata = Newtonsoft.Json.JsonConvert.SerializeObject(
new { Response = "Document Saving fail", Statuscode = StatusCodes.Status500InternalServerError });
}
else
{
var sqlException = ex.InnerException?.InnerException as SqlException;
if (sqlException != null)
obj.Information("1205 - " + sqlException.Number);
obj.Information("SaveDocumentAndParties : Main Catch Block. " + sd.Document_ID);
obj.Information("SaveDocumentAndParties : " + ex.ToString() + ". " + sd.Document_ID);
jsondata = Newtonsoft.Json.JsonConvert.SerializeObject(
new { Response = ex.Message, Statuscode = StatusCodes.Status500InternalServerError });
}
}
finally
{
_lock.Release();
}
}
return Ok(jsondata);
}
In [dbo].[sp_SaveCodedDatas] stored procedure Iam trying to insert data on Codedatas table and it is successfully saving the record.
alter procedure [dbo].[sp_SaveCodedDatas]
(
@DocumentID VARCHAR(50) = NULL,
@ProjectId VARCHAR(50) = NULL,
@DocumentDate NVARCHAR(255) = NULL,
@DocumentType NVARCHAR(255) = NULL,
@EnteredById INT = NULL,
@Estimated NVARCHAR(255) = NULL,
@Title NVARCHAR(4000) = NULL,
@CodingQATime INT = NULL,
@IsCorrected INT = NULL,
@UserTask VARCHAR(10) = NULL
)
As
BEGIN
BEGIN TRY
BEGIN TRANSACTION;
DECLARE @TempCodedDatas AS TABLE (
[Document_ID] NVARCHAR(255), [Image_File_Name] NVARCHAR(500), [page_label] NVARCHAR(255), [page_num] INT, [num_pages] INT,
[Coded] INT, [Revision] INT, [DocType] NVARCHAR(255), [EnteredBy] INT, [HostDocId] NVARCHAR(255),
[ExportDate] DATETIME, [ImportDate] DATETIME, [Percentage] INT, [SetId] INT, [DateCreated] DATETIME,
LastModified DATETIME, Host_Reference NVARCHAR(255),[Document_Date] DATETIME, [Estimated] NVARCHAR(255),
[Document_Type] NVARCHAR(255),Title NVARCHAR(4000), Document_DateValue DATETIME,
[CodingDate] DATETIME, [CodingTime] INT,[QADate] DATETIME, [QATime] INT, [IsCorrected] INT
);
IF @UserTask = 'Coder'
BEGIN
INSERT inTO @TempCodedDatas ([Document_ID], [Image_File_Name], [page_label], [page_num], [num_pages], [Coded], [Revision], [DocType], [EnteredBy], [HostDocId],
[ExportDate], [ImportDate], [Percentage], [SetId], [DateCreated], [LastModified], [Host_Reference], [Document_Date], [Estimated], [Document_Type],
[Title], [Document_DateValue], [CodingDate], [CodingTime], [IsCorrected])
SELECT IPS.[Document_ID],IPS.[Image_File_Name],IPS.[page_label],IPS.[page_num],IPS.[num_pages],1 AS [Coded],
(CASE WHEN @UserTask='Coder' THEN 0 ELSE 1 END) AS [Revision],@DocumentType AS [DocType],@EnteredById AS [EnteredBy],
IPS.[HostDocId],IPS.[ExportDate],IPS.[ImportDate],IPS.[Percentage],IPS.[SetId],IPS.[DateCreated],GETDATE() AS [LastModified],
ICD.[Host_Reference],
CASE
WHEN ISNULL(@DocumentDate, '') = '' THEN NULL
ELSE CONVERT(DATETIME, @DocumentDate)
END AS [DocDate],
@Estimated AS [Estimated],
@DocumentType AS [Document_Type],
CASE
WHEN LEN(@Title) > 0 THEN @Title
ELSE 'Untitled'
END AS [Title],
CASE
WHEN ISNULL(ICD.Document_DateValue, '') = '' THEN NULL
ELSE CONVERT(DATETIME, ICD.Document_DateValue)
END AS [Document_DateValue],
GETDATE() AS [CodingDate],
@CodingQATime AS [CodingTime],
@IsCorrected AS [IsCorrected]
FROM
ImportPages IPS with(nolock)
INNER JOIN
ImportCodedDatas ICD with(nolock) ON ICD.Document_ID = IPS.Document_ID
WHERE
IPS.Document_ID = @DocumentID
INSERT INTO CodedDatas ([Document_ID], [Image_File_Name], [page_label], [page_num], [num_pages], [Coded], [Revision], [DocType], [EnteredBy], [HostDocId],
[ExportDate], [ImportDate], [Percentage], [SetId], [DateCreated], [LastModified], [Host_Reference], [Document_Date], [Estimated], [Document_Type],
[Title], [Document_DateValue], [CodingDate], [CodingTime], [IsCorrected])
SELECT [Document_ID], [Image_File_Name], [page_label], [page_num], [num_pages],[Coded], [Revision], [DocType], [EnteredBy], [HostDocId],
[ExportDate], [ImportDate], [Percentage], [SetId], [DateCreated], LastModified, Host_Reference,
[Document_Date], [Estimated], [DocType],Title, Document_DateValue,[CodingDate], [CodingTime], [IsCorrected]
FROM @TempCodedDatas
PRINT 'Coded Data Saved. '+ @DocumentID
END
ELSE
BEGIN
INSERT INTO CodedDatas ([Document_ID], [Image_File_Name], [page_label], [page_num], [num_pages], [Coded], [Revision], [DocType], [EnteredBy], [HostDocId],
[ExportDate], [ImportDate], [Percentage], [SetId], [DateCreated], [LastModified], [Host_Reference], [Document_Date], [Estimated], [Document_Type],
[Title], [Document_DateValue], [QADate], [QATime], [IsCorrected])
SELECT [Document_ID], [Image_File_Name], [page_label], [page_num], [num_pages],[Coded], [Revision], [DocType], [EnteredBy], [HostDocId],
[ExportDate], [ImportDate], [Percentage], [SetId], [DateCreated], [LastModified], Host_Reference,
[Document_Date], [Estimated], [DocType],Title, Document_DateValue, [QADate],[QATime], [IsCorrected]
FROM @TempCodedDatas
PRINT 'Review Data Saved. '+ @DocumentID
END;
Select [Id]
,[Document_ID]
,[Image_File_Name]
,[page_label]
,[page_num]
,[num_pages]
,[Coded]
,[Revision]
,[DocType]
,[EnteredBy]
,[HostDocId]
,[ExportDate]
,[ImportDate]
,[Percentage]
,[SetId]
,[DateCreated]
,[LastModified]
,[main_id]
,[End_Page]
,[No_Pages]
,[Host_Reference]
,[Document_Date]
,[Estimated]
,[Document_Type]
,[Title]
,[Document_DateValue]
,[CodingDate]
,[QADate]
,[CodingTime]
,[QATime]
,[CodingStatus]
,[QAStatus]
,[IsCorrected]
,[IsBackendQA] from CodedDatas
COMMIT TRANSACTION; -- Inner transaction commit
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION; -- Inner transaction rollback
-- Handle the inner catch block
END CATCH;
END
After execution of the stored procedure when trying to fetch the records from CodedData table then exception is coming Data is Null. This method or property cannot be called on Null values. In my another procedure for other tables also data is saving but no issue with other tables. Why issue is coming for coded data table.
public class CodedData
{
[Key]
public int Id { get; set; }
[ConcurrencyCheck]
public string Document_ID { get; set; }
public string Image_File_Name { get; set; }
public string page_label { get; set; }
public int page_num { get; set; }
public int num_pages { get; set; }
[ConcurrencyCheck]
public int Coded { get; set; }
[ConcurrencyCheck]
public int Revision { get; set; }
public string DocType { get; set; }
[ConcurrencyCheck]
public int? EnteredBy { get; set; }
public string HostDocId { get; set; }
public DateTime? ExportDate { get; set; }
public DateTime? ImportDate { get; set; }
public int Percentage { get; set; }
public int SetId { get; set; }
public DateTime? DateCreated { get; set; }
public DateTime? LastModified { get; set; }
public string main_id { get; set; }
public string End_Page { get; set; }
public int No_Pages { get; set; }
public string Host_Reference { get; set; }
public DateTime? Document_Date { get; set; }
public string Estimated { get; set; }
public string Document_Type { get; set; }
public string Title { get; set; }
public DateTime? Document_DateValue { get; set; }
public DateTime? CodingDate { get; set; }
public DateTime? QADate { get; set; }
public int CodingTime { get; set; }
public int QATime { get; set; }
public int IsCorrected { get; set; }
public string CodingStatus { get; set; }
public string QAStatus { get; set; }
public bool? IsBackendQA { get; set; }
}
But When I am truncating the codedatas table then dbcontext.codeddatas is working.
1