首页
学习
活动
专区
圈层
工具
发布
社区首页 >问答首页 >复杂的JSON到多个mssql表

问复杂的JSON到多个mssql表
EN

Stack Overflow用户
提问于 2017-10-31 10:50:19
回答 1查看 88关注 0票数 0

任务:将JSON响应插入多个mssql表中

问题:这份工作要花很长时间。

现状:

我从API获得以下内容响应:

代码语言:javascript
复制
[{
        "About": "",
        "Name": "Employee_1",
        "EmployeeId": "aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee",
        "Address1": "Empstreet 4, 3. floor",
        "Phone": "90909090",
        "Birthdate": "1980-01-21",
        "City": "Empcity",
        "Zip": "8989",
        "Competencies": [{
            "Name": "Tablet",
            "ShortCode": "TAB",
            "Status": false
        }, {
            "Name": "Flex",
            "ShortCode": "FLX",
            "Status": false
        }],
        "CustomColumns": [{
            "Description": "",
            "Name": "",
            "Type": "CheckBox",
            "Value": "False"
        }],
        "CustomRoles": [{
            "CustomRoleModels": [{
                "IsAdminRole": false,
                "IsEmployeeRole": false,
                "IsPlannerRole": true,
                "Name": "Shop Manager"
            }],
            "DepartmentId": "49664884-8382-4712-9953-fb36ca13fd1b"
        }],
        "HistoricalWages": [{
            "StartDate": "2000-01-01",
            "Wage": 120
        }],
        "NotAvailable": [{
            "From": "12:00",
            "To": "05:00",
            "WeekDayIndex": 1
        }, {
            "From": "12:00",
            "To": "04:00",
            "WeekDayIndex": 0
        }, {
            "From": "12:00",
            "To": "03:00",
            "WeekDayIndex": 4
        }, {
            "From": "12:00",
            "To": "05:00",
            "WeekDayIndex": 3
        }],
        "Notes": [{
            "Author": "4a408a58-f9d6-4898-80a5-c3ef5667d2c6",
            "Comment": "",
            "Date": "2016-03-17"
        }],
        "Roles": [{
            "Admin": false,
            "DepartmentId": "aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee",
            "Employee": true,
            "Planner": false
        }]
    }, {
        "About": "",
        "Name": "Employee_2",
        "EmployeeId": "aaaaaaaa-bbbb-cccc-dddd-ffffffffffff",
        "Address1": "Empstreet 5, 3. floor",
        "Phone": "80808080",
        "Birthdate": "1981-01-21",
        "City": "Empcity",
        "Zip": "8987",
        "Competencies": [{
            "Name": "Tablet",
            "ShortCode": "TAB",
            "Status": false
        }, {
            "Name": "Flex",
            "ShortCode": "FLX",
            "Status": false
        }],
        "CustomColumns": [{
            "Description": "",
            "Name": "",
            "Type": "CheckBox",
            "Value": "False"
        }],
        "CustomRoles": [{
            "CustomRoleModels": [{
                "IsAdminRole": false,
                "IsEmployeeRole": false,
                "IsPlannerRole": true,
                "Name": "Shop Manager"
            }],
            "DepartmentId": "49664884-8382-4712-9953-fb36ca13fd1b"
        }],
        "HistoricalWages": [{
            "StartDate": "2000-01-01",
            "Wage": 120
        }],
        "NotAvailable": [{
            "From": "12:00",
            "To": "05:00",
            "WeekDayIndex": 1
        }, {
            "From": "12:00",
            "To": "04:00",
            "WeekDayIndex": 0
        }, {
            "From": "12:00",
            "To": "03:00",
            "WeekDayIndex": 4
        }, {
            "From": "12:00",
            "To": "05:00",
            "WeekDayIndex": 3
        }],
        "Notes": [{
            "Author": "4a408a58-f9d6-4898-80a5-c3ef5667d2c6",
            "Comment": "",
            "Date": "2016-03-17"
        }],
        "Roles": [{
            "Admin": false,
            "DepartmentId": "aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee",
            "Employee": true,
            "Planner": false
        }]
    }]

因此,我做了以下的课程

代码语言:javascript
复制
public class Competency
    {
        public string Name { get; set; }
        public string ShortCode { get; set; }
        public bool Status { get; set; }
    }

public class CustomColumn
{
    public string Description { get; set; }
    public string Name { get; set; }
    public string Type { get; set; }
    public string Value { get; set; }
}

public class CustomRoleModel
{
    public bool IsAdminRole { get; set; }
    public bool IsEmployeeRole { get; set; }
    public bool IsPlannerRole { get; set; }
    public string Name { get; set; }
}

public class CustomRole
{
    public List<CustomRoleModel> CustomRoleModels { get; set; }
    public string DepartmentId { get; set; }
}

public class HistoricalWage
{
    public string StartDate { get; set; }
    public int Wage { get; set; }
}

public class NotAvailable
{
    public string From { get; set; }
    public string To { get; set; }
    public int WeekDayIndex { get; set; }
}

public class Note
{
    public string Author { get; set; }
    public string Comment { get; set; }
    public string Date { get; set; }
}

public class Role
{
    public bool Admin { get; set; }
    public string DepartmentId { get; set; }
    public bool Employee { get; set; }
    public bool Planner { get; set; }
}

public class Employees
{
    public string About { get; set; }
    public string Name { get; set; }
    public string EmployeeId { get; set; }
    public string Address1 { get; set; }
    public string Phone { get; set; }
    public string Birthdate { get; set; }
    public string City { get; set; }
    public string Zip { get; set; }
    public List<Competency> Competencies { get; set; }
    public List<CustomColumn> CustomColumns { get; set; }
    public List<CustomRole> CustomRoles { get; set; }
    public List<HistoricalWage> HistoricalWages { get; set; }
    public List<NotAvailable> NotAvailable { get; set; }
    public List<Note> Notes { get; set; }
    public List<Role> Roles { get; set; }
}

JSON内容存储在一个名为'json‘的变量中,并且反序列化为上面的类,如下所示:

代码语言:javascript
复制
List<Employees> employees = JsonConvert.DeserializeObject<List<Employees>>(json);

使用下面新创建的对象,函数调用

代码语言:javascript
复制
private static void InsertDataToEmployees(List<Employees> allEmployees)
{
    string connectionString = ConfigurationManager.ConnectionStrings["msEmployees"].ConnectionString;
    List<Employees> records = allEmployees;

    string insert_employee = "INSERT INTO dbo.dtEmployees (Name, EmployeeId, Address1, Phone, Birthdate, City, Zip) " +
        " VALUES (@Name, @EmployeeId, @Address1, @Phone, @Birthdate, @City, @Zip)";

    string insert_competencies = "INSERT INTO dbo.dtCompetencies (EmployeeId, Name, ShortCode, Status) " +
        " VALUES (@EmployeeId, @Name, @ShortCode, @Status)";

    string insert_available = "INSERT INTO dbo.dtAvailable (EmployeeId, [From], [To], WeekDayIndex) " +
        " VALUES (@EmployeeId, @From, @To, @WeekDayIndex)";

    string insert_roles = "INSERT INTO dbo.dtRoles (EmployeeId, Admin, DepartmentId, Employee, Planner) " +
        " VALUES (@EmployeeId, @Admin, @DepartmentId, @Employee, @Planner)";

    string insert_wages = "INSERT INTO dbo.dtWages (EmployeeId, StartDate, Wage) " +
        " VALUES (@EmployeeId, @StartDate, @Wage)";

    using (SqlConnection conn = new SqlConnection(connectionString))
    {
        conn.Open();

        foreach (var employee in allEmployees)
        {
            string empID = employee.EmployeeId;

            using (SqlCommand cmd_employees = new SqlCommand(insert_employee, conn))
            {
                    cmd_employees.Parameters.AddWithValue("@Name", (object)employee.Name ?? DBNull.Value);
                    cmd_employees.Parameters.AddWithValue("@EmployeeId", (object)employee.EmployeeId ?? DBNull.Value);
                    cmd_employees.Parameters.AddWithValue("@Address1", (object)employee.Address1 ?? DBNull.Value);
                    cmd_employees.Parameters.AddWithValue("@Phone", (object)employee.Phone ?? DBNull.Value);
                    cmd_employees.Parameters.AddWithValue("@Birthdate", (object)employee.Birthdate ?? DBNull.Value);
                    cmd_employees.Parameters.AddWithValue("@Zip", (object)employee.Zip ?? DBNull.Value);
                    cmd_employees.Parameters.AddWithValue("@City", (object)employee.City ?? DBNull.Value);
                    cmd_employees.ExecuteNonQuery();
                    cmd_employees.Parameters.Clear();
            }

            foreach (var c in employee.Competencies)
            {
                using (SqlCommand cmd_competencies = new SqlCommand(insert_competencies, conn))
                {
                    cmd_competencies.Parameters.AddWithValue("@EmployeeId", (object)empID ?? DBNull.Value);
                    cmd_competencies.Parameters.AddWithValue("@Name", (object)c.Name ?? DBNull.Value);
                    cmd_competencies.Parameters.AddWithValue("@ShortCode", (object)c.ShortCode ?? DBNull.Value);
                    cmd_competencies.Parameters.AddWithValue("@Status", (object)c.Status ?? DBNull.Value);
                    cmd_competencies.ExecuteNonQuery();
                    cmd_competencies.Parameters.Clear();
                }
            }

            foreach (var a in employee.NotAvailable)
            {
                using (SqlCommand cmd_available = new SqlCommand(insert_available, conn))
                {
                    cmd_available.Parameters.AddWithValue("@EmployeeId", (object)empID ?? DBNull.Value);
                    cmd_available.Parameters.AddWithValue("@From", (object)a.From ?? DBNull.Value);
                    cmd_available.Parameters.AddWithValue("@To", (object)a.To ?? DBNull.Value);
                    cmd_available.Parameters.AddWithValue("@WeekDayIndex", (object)a.WeekDayIndex ?? DBNull.Value);
                    cmd_available.ExecuteNonQuery();
                    cmd_available.Parameters.Clear();
                }
            }

            foreach (var r in employee.Roles)
            {
                using (SqlCommand cmd_roles = new SqlCommand(insert_roles, conn))
                {
                    cmd_roles.Parameters.AddWithValue("@EmployeeId", (object)empID ?? DBNull.Value);
                    cmd_roles.Parameters.AddWithValue("@Admin", (object)r.Admin ?? DBNull.Value);
                    cmd_roles.Parameters.AddWithValue("@DepartmentId", (object)r.DepartmentId ?? DBNull.Value);
                    cmd_roles.Parameters.AddWithValue("@Employee", (object)r.Employee ?? DBNull.Value);
                    cmd_roles.Parameters.AddWithValue("@Planner", (object)r.Planner ?? DBNull.Value);
                    cmd_roles.ExecuteNonQuery();
                    cmd_roles.Parameters.Clear();
                }
            }

            foreach (var w in employee.HistoricalWages)
            {
                using (SqlCommand cmd_wages = new SqlCommand(insert_wages, conn))
                {
                    cmd_wages.Parameters.AddWithValue("@EmployeeId", (object)empID ?? DBNull.Value);
                    cmd_wages.Parameters.AddWithValue("@StartDate", (object)w.StartDate ?? DBNull.Value);
                    cmd_wages.Parameters.AddWithValue("@Wage", (object)w.Wage ?? DBNull.Value);
                    cmd_wages.ExecuteNonQuery();
                    cmd_wages.Parameters.Clear();
                }
            }
        }
        conn.Close();
    }
}

上面的函数可以工作,并将数据发送到sql服务器,但实际上需要花费很长时间。

所以我的问题是,由于我不是这里的专家,什么是完成JSON到MSSQL传输的最有效的方法(就时间而言)。

EN

回答 1

Stack Overflow用户

回答已采纳

发布于 2017-10-31 11:10:12

造成延迟的原因是代码每一行执行一个命令。考虑到每一行的大小,几乎所有的时间都浪费在网络往返和单个操作的日志记录上。

您可以使用SqlBulkCopy作为记录流发送所有行,这些记录将使用最小日志记录插入到目标表中。

SqlBulkCopy需要一个DataTable或DbDataReader。与创建自己的DataTable和双缓冲所有行或编写自己的数据读取器不同,您可以使用FastMember包从集合中创建IDataReader:

代码语言:javascript
复制
using(var bcp = new SqlBulkCopy(connection)) 
using(var reader = ObjectReader.Create(allEmployees, "Name", "EmployeeId", "Address1", "Phone", "Birthdate", "City", "Zip")) 
{ 
    bcp.DestinationTableName = "dtEmployees"; 
    bcp.WriteToServer(reader); 
}

如果属性名称和表字段不匹配,则必须用源名和目标名填充ColumnMappings属性。

您必须使用LINQ将嵌套的集合夷平,以获得一个可以与SqlBulkCopy一起使用的集合,例如:

代码语言:javascript
复制
var allCompetencies = from employee in allEmployees
                      from competency in employee.Competencies
                      select new { employee.EmpoyeeID,
                                   competency.Name,
                                   competency.ShortCode,
                                   competency.Status};

using(var cmpBcp = new SqlBulkCopy(connection)) 
using(var var cmpReader=ObjectReader.Create(allCompetencies,"EmployeeID","Name","ShortCode","Status"))
{ 
    cmpBcp.DestinationTableName = "dtCompetencies"; 
    cmpBcp.WriteToServer(cmpReader); 
}
票数 0
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/47033542

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档