从上传的Web API CSV和实体框架将行保存到数据库 [英] Save Rows to Database From Uploaded Web API CSV and Entity Framework

问题描述

我有一个上传的CSV文件，已发布到Web API路由。我已经能够通过字节数组将CSV存入内存，这就是我遇到的问题。我要获取CSV的每一行，添加与文件一起从上传表单中发布的三个其他字段，并将每个带有其他字段的CSV行插入到SQL Server表中。我该如何获取字节数组，转换行并插入数据？

I have an uploaded CSV file that is posted to a Web API route. I have been able to get the CSV into memory via a byte array and that is where I am stuck. I want to take each row of the CSV, add three additional fields that are posted from the upload form along with the file, and insert every CSV row with additional fields to a SQL server table. How do I take my byte array, transform the rows, and insert the data?

这是我的AngularJS使用ng-file-upload。

Here is my AngularJS that uses ng-file-upload.

$scope.upload = function (file) {
Upload.upload({
    url: 'api/UploadProfile',
    data: {
        file: file,
        'ProfileName': $scope.ProfileName,
        'SubmittedBy': $scope.SubmittedBy,
        'InsertDate': $scope.InsertDate
    }
})}

这是我的Web API控制器

And here is my Web API controller

public class ProfileUploadController : ApiController
{
    private BIMarketOrderEntities db = new BIMarketOrderEntities();

    [HttpPost, Route("api/UploadProfile")]
    public async Task<IHttpActionResult> Upload()
    {
        if (!Request.Content.IsMimeMultipartContent())
            throw new HttpResponseException(HttpStatusCode.UnsupportedMediaType);

        var provider = new MultipartMemoryStreamProvider();
        await Request.Content.ReadAsMultipartAsync(provider);
        foreach (var file in provider.Contents)
        {
            var filename = file.Headers.ContentDisposition.FileName.Trim('\"');
            var buffer = await file.ReadAsByteArrayAsync();

            // How do I get 'buffer' to my database while adding the additional fields?
        }

        return Ok();
    }
}

推荐答案

此处是我最终针对该特定实例所做的事情。如果有更有效的方法，我肯定会接受它们。

Here is what I ended up doing for this particular instance. If there more efficient methods, I am definitely open to them.

[HttpPost, Route("api/UploadProfile")]
public async Task<IHttpActionResult> Upload()
{
    //Get attachment and form data
    if (!Request.Content.IsMimeMultipartContent())
        throw new HttpResponseException(HttpStatusCode.UnsupportedMediaType);

    var provider = new MultipartMemoryStreamProvider();
    await Request.Content.ReadAsMultipartAsync(provider);

    //File and 3 form parameters will be saved to this array
    string[] results = new string[4];

    //Read all data into array
    int i = 0;
    foreach (var parameter in provider.Contents)
    {
        var bytes = await parameter.ReadAsByteArrayAsync();
        results[i++] = Encoding.Default.GetString(bytes);
    }

    //Split lines of CSV into array
    string[] stringArray = results[0].Split(new[] { "\r\n", "\r", "\n" }, StringSplitOptions.None);

    //Check if header row matches expected CSV layout
    if (stringArray[0] != "CSVField1,CSVField2,CSVField3,CSVField4,CSVField5,CSVField6")
    {
        //Failure
        var message = "File does not contain necessary header fields.";
        return Content(HttpStatusCode.BadRequest, message);
    }

    //Remove header row
    stringArray = stringArray.Skip(1).ToArray();

    //Create db object store all Insert data
    var profileObjs = db.Set<T_ProfileStaging>();
    foreach (var s in stringArray)
    {

        //Save each column in array
        string[] columns = s.Split(',');

        //Add form data to individial records
        T_ProfileStaging profileObj = new T_ProfileStaging();
        profileObj.Field1 = columns[0];
        profileObj.Field2 = results[1];
        profileObj.Field3 = columns[1];
        profileObj.Field4 = columns[2];
        profileObj.Field5 = columns[3];
        profileObj.Field6 = columns[4];
        profileObj.Field7 = results[2];
        profileObj.Field8 = columns[5];
        profileObj.Field9 = results[3];

        profileObjs.Add(profileObj);
    }

    //Save all objects to database
    db.SaveChanges();

    //Success
    return Ok();
}

这篇关于从上传的Web API CSV和实体框架将行保存到数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！