如何从映射表插入模型 [英] How to insert model from mapping table

问题描述

我有想法要使用表中的映射从excel中插入我的模型.因为我的excel超过100栏.如果像我一样手动编码，那会很累.我的想法是将表头Excel的名称，函数和表的行保存到数据库中

I have idea to insert my model from excel using mapping from table. Because my excel hv more then 100 column. It will be tired if I code manualy like I do. I hv idea to save to database the name of header excel, the function, and row for my table

首先，我读取了我的excel，然后捕获到数据集，然后是foreach数据集，然后是每个列表映射，然后使用是否知道是否为空，然后使用replace读取函数，然后使用映射表设置模型

First, I read my excel then capture to dataset, then foreach dataset, then for each list mapping, then using if to know is empty or not, then read function using replace, then set model using mapping table

我做什么:

[HttpPost]
public ActionResult upload(HttpPostedFileBase uploadFile)
{
    private DB_Entities db = new DB_Entities();
    dataset ds = new dataset();
    ds = GetFileExcel(uploadFile);

    foreach (DataRow dr in ds.Tables["Header$"].Rows)
    {   
        tbl_header header = new tbl_header();

        string name = dr["Name"].ToString();
        if (name != "") {
            var resultName = Convert.ToInt32(name);
            header.name = resultName;
        }

        string address = dr["Address"].ToString();
        if (address != "") {
            var resultAddress = DateTime.ParseExact(address, "dd MMM yyyy", provider);
            header.address = resultAddress;
        }

        string country = dr["Country"].ToString();
        if (country != "") {
            var resultCountry = db.tbl_Item.Where(a => a.Code == country).Select(a => a.Id).SingleOrDefault();
            header.country = ResultCountry;
        }

        db.tbl_header.Add(header);
        db.SaveChanges();
    }
}

public partial class tblT_Pengeluaran_Header
{
    public int name { get; set; }
    public DateTime address { get; set; }
    public int country { get; set; }
}

我想要什么:

[HttpPost]
public ActionResult upload(HttpPostedFileBase uploadFile)
{
    private DB_Entities db = new DB_Entities();
    dataset ds = new dataset();
    ds = GetFileExcel(uploadFile);
    var mapping = db.Tbl_Mapping.toList();

    foreach (DataRow dr in ds.Tables["Header$"].Rows)
    {
        tbl_header header = new tbl_header();

        //foreach list mapping to doing:
        //{
        //    string excel = ReadExcel
        //    if (excel != "") {
        //        string resultFunction = ReadFunction.Replace('[data]',excel);
        //        var result = Execute(resultFunction);
        //        header.ReadTable = result;
        //    }
        //} 

        db.tbl_header.Add(header);
        db.SaveChanges();
    }
}

表映射示例: Tbl_Mapping

table   | excel     | function
_____________________________________
name    | Name      | Convert.ToInt32([data]);
address | Address   | DateTime.ParseExact([data], "dd MMM yyyy", provider);
country | Country   | db.tbl_Item.Where(a => a.Code == [data]).Select(a => a.Id).SingleOrDefault();
.....
.....

如何执行此操作?任何的想法???谢谢

How to do this? any idea??? Thank you

推荐答案

我可以在您的代码中看到两个问题.

I can see two issues in your code.

1. 从foreach中的数据库中获取国家对象
2. 数据库保存在foreach中.

以上两者均不利于性能.请考虑以下内容.

The above both are bad for the performance. Consider the below instead.

首先，从数据集中获取所有国家/地区，并进行一次数据库查找，并使用AddRange代替add.

First, fetch all countries from the dataset and do single DB lookup and use AddRange instead of add.

对于将数据集转换为映射对象的方法，请使用Linq，如下所示.

For the Dataset to mapping object conversion, use Linq as below.

[HttpPost]
public ActionResult upload(HttpPostedFileBase uploadFile)
{
    private DB_Entities db = new DB_Entities();

    DataSet ds = GetFileExcel(uploadFile);

    string[] countryCodes = ds.Tables["Header$"].AsEnumerable().Select(row => row.Field<string>("country")).ToArray();

    var selectedCountries = db.tbl_Item.Where(q => countryCodes.Contains(q)).Select(s => new { Id = s.Id, Code = s.Code }).ToList();

    List<tbl_header>  headers = ds.Tables["Header$"].AsEnumerable().Select
    (
        row => new tbl_header
        {
            name = row.Field<string>("Name") == string.Empty ? 0 : int.Parse(row.Field<string>("Name")),
            address = row.Field<string>("Address") == string.Empty ? DateTime.MinValue : DateTime.ParseExact((row.Field<string>("Address")), "dd MMM yyyy", provider),
            country = selectedCountries.SingleOrDefault(q=> q.Code == row.Field<string>("Country")).Id
        }
    ).ToList();

    db.tbl_header.AddRange(headers);
    db.SaveChanges();
}

这篇关于如何从映射表插入模型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！