Hi, I am trying to upload an Excel file by passing excel data as User Defined Datatype to a stored Procedure using Entity Framework and C# Code in an ASP.Net Application as below:
public System.Int32 UploadServiceProgram(List<ServicePrograms_Upload> serviceProgList, string Fiscal_Period_Code, string CreatedBy)
{
DataTable serviceProgramDataTable = new DataTable();
serviceProgramDataTable.Columns.Add("ServiceCode", typeof(string));
serviceProgramDataTable.Columns.Add("ProgramCode", typeof(int));
serviceProgramDataTable.Columns.Add("UnitTypeDesc", typeof(string));
serviceProgramDataTable.Columns.Add("RateCap", typeof(decimal));
serviceProgramDataTable.Columns.Add("CountyCode", typeof(string));
foreach (var item in serviceProgList)
{
serviceProgramDataTable.Rows.Add(item.Provided_Service_Code, item.Program_Code, item.Unit_Type, item.Rate_Cap);
}
var parameter = new SqlParameter("@ServiceProgram", SqlDbType.Structured);
parameter.Value = serviceProgramDataTable;
parameter.TypeName = "dbo.ServicePrograms_UDT";
var parameter2 = new SqlParameter("@Fiscal_Period_Code", SqlDbType.NVarChar, 15);
parameter2.Value = Fiscal_Period_Code;
var parameter3 = new SqlParameter("@CreatedBy", SqlDbType.NVarChar, 50);
parameter3.Value = CreatedBy;
using (var context = new CRSContext())
{
context.Database.CommandTimeout = 360000;
var result = context.Database.SqlQuery<int>("usp_Upload_ServicePrograms @ServiceProgram, @Fiscal_Period_Code, @CreatedBy", parameter, parameter2, parameter3).FirstOrDefault();
return result;
}
}
It is failing for some reason, but the same Excel file I have exported into a Table, then creating a Table Variable of type (the User Defined Type) that I am using then inserting into that Table variable from the Table that I have Excel data in, then executing the Stored Procedure is not failing actually importing the Data as it is, I am not sure why the Stored Procedure executes but the above method fails, in both the cases Stored Procedure and Excel are the same. Here is how I am executing the Stored Procedure:
declare @ServiceProgram ServicePrograms_UDT
declare @fiscalPeriod nvarchar(15)='FY 2016-2017', @CreatedBy nvarchar(50)='aaleemmo'
insert into @ServiceProgram(ServiceCode, ProgramCode, UnitTypeDesc, RateCap, CountyCode)
select [service code], [Program Code], [Unit Type], [Rate Cap], [County Code] from SUDCRS..zzServiceProgramCountyExcelData
exec dbo.usp_Upload_ServicePrograms @ServiceProgram, @fiscalPeriod, @CreatedBy
And one more doubt I have is, when my WCF Service is posting this huge excel data as datatable to the Database, would it lose the Data when it is posting huge data, if it is losing that, is there any other approach I can execute this stored procedure.
Please let me know anything that's possible, a suggestion, a code snippet or even a tiny suggestion, any thing helps, its little bit urgent - please, thanks a lot.
What I have tried:
running multiple times, searching online