Context.database.sqlquery用于上传excel文件 [英] Context.database.sqlquery to upload an excel file

问题描述

您好，我正在尝试上传Excel文件，方法是将Excel数据作为用户定义的数据类型传递给ASP.Net应用程序中使用Entity Framework和C＃Code的存储过程，如下所示：

 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（serviceProgList中的var项）
 {
 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; 
 
使用（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（）; 
 
返回结果; 
} 
} 
 

由于某种原因它失败了，但我导出到表中的同一个Excel文件，然后创建我正在使用的表变量类型（用户定义类型）然后从表中插入我有Excel数据的表变量，然后执行存储过程并不会导致实际导入数据失败，我不是确定存储过程执行的原因但上述方法失败，在存储过程和Excel都是相同的情况下。以下是我执行存储过程的方法：

声明@ServiceProgram ServicePrograms_UDT 
声明@fiscalPeriod nvarchar（15）='2016-2017财年'，@ ManagementBy nvarchar （50）='aaleemmo'
 
插入@ServiceProgram（ServiceCode，ProgramCode，UnitTypeDesc，RateCap，CountyCode）
选择[服务代码]，[程序代码]，[单位类型]， [价格上限]，[县代码]来自SUDCRS..zzServiceProgramCountyExcelData 
 
 exec dbo.usp_Upload_ServicePrograms @ServiceProgram，@ _FiscalPeriod，@ TreatBy 

And我的另一个疑问是，当我的WCF服务将这个巨大的Excel数据作为数据表发布到数据库时，它会在发布大量数据时丢失数据，如果它丢失了，是否还有其他方法我可以执行此操作存储过程。

请让我知道任何可能的事情，建议，代码片段甚至是一个小小的建议，任何有用的东西，它有点紧急 - 拜托，非常感谢。



我尝试了什么：



多次运行，在线搜索

解决方案

我写的有关将excel / csv文件导入数据表对象的文章。从那里，实际将导入的数据导入sql server中的表是一件相当简单的事情。



CSV / Excel文件解析器 - 重访 [ ^ ]



结果由该文章中的代码提供的是一个带有列标题的数据表，并自动确定（或手动指定）列的数据类型。



快速/脏的解决方案将解析后的数据导入sql server表：

 SqlBulkCopy sbc =  new  SqlBulkCopy（myConnection）; 
 sbc.DestinationTableName = datatable.TableName; 
 尝试 
 {
 sbc.WriteToServer（table）; 
} 
  catch （例外情况）
 {
  //  做一些异常 
} 

当然还有其他一些东西你必须做才能使代码工作（例如目标表必须已经存在），但你听起来像是自我意识到足以指出你自己的部分。

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

解决方案

I wrote an article regarding importing excel/csv file into a datatable object. From there, it is a fairly simple matter to actually get the imported data into a table in sql server.

CSV/Excel File Parser - A Revisit[^]

The result presented by the code in that article is a datatable with column headers and automatically determined (or manually specified) data types for the columns.

A quick/dirty solution to getting the parsed data into a sql server table is:

SqlBulkCopy sbc = new SqlBulkCopy(myConnection);
sbc.DestinationTableName = datatable.TableName;
try                             
{                                 
    sbc.WriteToServer(table);                            
}     
catch(Exception ex)
{
    // do something with the exception
}

Of course there is other stuff you have to do to make that code work (for instance the destination table has to already exist), but you sound like you're self-aware enough to finger that part out on your own.

这篇关于Context.database.sqlquery用于上传excel文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！