petapoco writetoserver excal批量添加数据库

   现在网的petapoco 批量添加数据库的源码很少， 自己在网上找了一些资料， 都是拼接 sql 语句，感觉不是很理想。自己有 writetoserver，写了一个方法 。

下面是主要的writetoserver写入数据库代码

本次主要是excal 批量添加到数据库

首先nuget 引入NPOI的包

 

action 中的代码

1

2

3

4

5

6

7

8

9

10

public ActionResult POstUpLoad(HttpPostedFileBase file)

{

    Stopwatch time = Stopwatch.StartNew();

    TestManager Manager = new TestManager();

    Manager.BulkInsert(new Function.ExecData<Ceshi>().ImportExcelToDataTable(file).AsEnumerable(), 2000);

   // Manager.SQLBulkInsert(new Function.ExecData<Ceshi>().ImportExcelToDataTable(file));

    time.Stop();

    long etime = time.ElapsedMilliseconds;

    return RedirectToAction("Index");

}

　　

excal 转换成list实体

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

63

64

65

66

67

68

69

70

71

72

73

74

75

public List<T> ImportExcelToDataTable(HttpPostedFileBase postedfile)

{

    if (postedfile != null)

    {

        try

        {

            lock (obj)

            {

                List<T> list = new List<T>();

                List<string> Columns = new List<string>();

                string ExtensionName = Path.GetExtension(postedfile.FileName);

                string filename = DateTime.Now.ToString("yyyyMMddhhssmmffffff") + ExtensionName;

                string fullpath = HttpContext.Current.Server.MapPath("~/Upload/TempFiles/");

                if (!Directory.Exists(fullpath))

                {

                    Directory.CreateDirectory(fullpath);

                }

                fullpath += filename;

                postedfile.SaveAs(fullpath);

 

                stream = File.Open(fullpath, FileMode.Open, FileAccess.Read);

                dynamic workbook;

                if (ExtensionName == "xlsx")

                {

                    workbook = new XSSFWorkbook(stream);

                }

                else

                {

                    workbook = new HSSFWorkbook(stream);

                }

                ISheet sheet = workbook.GetSheetAt(0);

                //获取sheet的首行

                IRow headerRow = sheet.GetRow(0);

                int cellCount = headerRow.LastCellNum;

                //获取列名

                headerRow.Cells.ForEach(x => { Columns.Add(x.StringCellValue); });

 

                int rowCount = sheet.LastRowNum;

                for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)

                {

                    T model = new T();


                    IRow row = sheet.GetRow(i);

                    for (int j = row.FirstCellNum; j < cellCount; j++)

                    {

                        foreach (PropertyInfo column in model.GetType().GetProperties())

                        {

                            if (Columns[j] == column.Name && row.GetCell(j) != null)

                            {

                                column.SetValue(model, GetCellValue(column, row.GetCell(j)));

                                continue;

                            }

                        }

                    }

                    list.Add(model);

                }

                stream.Close();

                File.Delete(fullpath);

                return list;

            }

        }

        catch (Exception ex)

        {

            log.WriteTraceLog(ex);

            return null;

        }

        finally

        {

            stream.Close();

        }

    }

    else

    {

        return null;

    }

}

　　excal 中的值转换成实体的代码

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

//获取cell的数据，并设置为对应的数据类型

public object GetCellValue(PropertyInfo prop, ICell cell)

{

    object value = null;

    if (prop != null)

    {//case中的类型可根据prop.PropertyType的值， 自己判断添加， 本文中的case 仅供测试使用

        switch (prop.PropertyType.ToString())

        {

            case "System.Int32":

            case "System.Nullable`1[System.Int32]":

                value = (int)cell.NumericCellValue;

                break;

            case "System.Boolean":

            case "System.Nullable`1[System.Boolean]":

                value = cell.BooleanCellValue;

                break;

            case "System.DateTime":

            case "System.Nullable`1[System.DateTime]":

                value = cell.DateCellValue;

                break;

            default:

                value = cell.ToString();

                break;

        }

    }

    return value;

}

　　

插入数据库的代码

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

/// <summary>

/// DataTale整张表数据插入数据

/// </summary>

/// <param name="dt">要插入的table数据</param>

/// <param name="tableName">目标数据表名</param>

/// <param name="fieldName">必须提供所有的字段</param>

/// <returns>返回成功，或者失败 true  or false</returns>

public bool SqlBulkInsert(DataTable dt, string tableName, string[] fieldName)

{

    try

    {

        OpenSharedConnection();

        using (SqlBulkCopy bulk = new SqlBulkCopy(_sharedConnection.ConnectionString))

        {

            try

            {

                //when the table data handle done

                bulk.DestinationTableName = tableName;

                foreach (string field in fieldName)

                {

                    bulk.ColumnMappings.Add(field, field);

                }

                bulk.WriteToServer(dt);

                return true;

            }

            catch

            {

                return false;

            }

            finally

            {

                bulk.Close();

            }

        }

    }

    finally

    {

        CloseSharedConnection();

    }

}

　　下面是实体转换成datatable的方法

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

/// <summary>

/// 实体处理转换成dataTable

/// </summary>

/// <param name="List"></param>

/// <returns></returns>

public Tuple<DataTable, string[]> batchExecData(List<T> List)

{

    DataTable dt = new DataTable();

    List<string> list = new List<string>();

    foreach (T entity in List)

    {

        DataRow dr = dt.NewRow();

        foreach (PropertyInfo column in entity.GetType().GetProperties())

        {

            if (!dt.Columns.Contains(column.Name))

            {

                dt.Columns.Add(column.Name);

                list.Add(column.Name);


            }

            object value = column.GetValue(entity);

            if (value != null)

            {

                dr[column.Name] = value;

            }

        }

        dt.Rows.Add(dr);

    }

    return new Tuple<DataTable, string[]>(dt, list.ToArray());

}

　　批量导入数据库调用部分

1

2

3

4

5

6

7

8

9

10

public bool BulkInsert(List<T> pocos)

{

    Type type = typeof(T);

    Tuple<DataTable, string[]> tule = batchExecData(pocos);

    using (var db = dbcontext)

    {

      return  db.SqlBulkInsert(tule.Item1, type.Name, tule.Item2);

    }

}

　

1

下面是拼接sql 的方法

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

63

64

65

66

67

68

69

70

71

72

73

74

75

76

77

78

79

80

81

82

83

84

85

86

87

88

89

90

91

92

93

94

95

96

97

98

99

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

#region  insert 批量导入

// <summary>

/// Bulk inserts multiple rows to SQL

/// </summary>

/// <param name="tableName">The name of the table to insert into</param>

/// <param name="primaryKeyName">The name of the primary key column of the table</param>

/// <param name="autoIncrement">True if the primary key is automatically allocated by the DB</param>

/// <param name="pocos">The POCO objects that specifies the column values to be inserted</param>

/// <param name="batchSize">The number of POCOS to be grouped together for each database rounddtrip</param>       

public void BulkInsert(string tableName, string primaryKeyName, bool autoIncrement, IEnumerable<object> pocos, int batchSize = 25)

{

    try

    {

        OpenSharedConnection();

        try

        {

            using (var cmd = CreateCommand(_sharedConnection, ""))

            {

                var pd = PocoData.ForObject(pocos.First(), primaryKeyName, _defaultMapper);

                // Create list of columnnames only once

                var names = new List<string>();

                foreach (var i in pd.Columns)

                {

                    // Don‘t insert result columns

                    if (i.Value.ResultColumn)

                        continue;

 

                    // Don‘t insert the primary key (except under oracle where we need bring in the next sequence value)

                    if (autoIncrement && primaryKeyName != null && string.Compare(i.Key, primaryKeyName, true) == 0)

                    {

                        // Setup auto increment expression

                        string autoIncExpression = _dbType.GetAutoIncrementExpression(pd.TableInfo);

                        if (autoIncExpression != null)

                        {

                            names.Add(i.Key);

                        }

                        continue;

                    }

                    names.Add(_dbType.EscapeSqlIdentifier(i.Key));

                }

                var namesArray = names.ToArray();

 

                var values = new List<string>();

                int count = 0;

                do

                {

                    cmd.CommandText = "";

                    cmd.Parameters.Clear();

                    var index = 0;

                    foreach (var poco in pocos.Skip(count).Take(batchSize))

                    {

                        values.Clear();

                        foreach (var i in pd.Columns)

                        {

                            // Don‘t insert result columns

                            if (i.Value.ResultColumn) continue;

 

                            // Don‘t insert the primary key (except under oracle where we need bring in the next sequence value)

                            if (autoIncrement && primaryKeyName != null && string.Compare(i.Key, primaryKeyName, true) == 0)

                            {

                                // Setup auto increment expression

                                string autoIncExpression = _dbType.GetAutoIncrementExpression(pd.TableInfo);

                                if (autoIncExpression != null)

                                {

                                    values.Add(autoIncExpression);

                                }

                                continue;

                            }

 

                            values.Add(string.Format("{0}{1}", _paramPrefix, index++));

                            AddParam(cmd, i.Value.GetValue(poco), i.Value.PropertyInfo);

                        }

 

                        string outputClause = String.Empty;

                        if (autoIncrement)

                        {

                            outputClause = _dbType.GetInsertOutputClause(primaryKeyName);

                        }

 

                        cmd.CommandText += string.Format("INSERT INTO {0} ({1}){2} VALUES ({3})", _dbType.EscapeTableName(tableName),

                                                         string.Join(",", namesArray), outputClause, string.Join(",", values.ToArray()));

                    }

                    // Are we done?

                    if (cmd.CommandText == "") break;

                    count += batchSize;

                    DoPreExecute(cmd);

                    cmd.ExecuteNonQuery();

                    OnExecutedCommand(cmd);

                }

                while (true);

 

            }

        }

        finally

        {

            CloseSharedConnection();

        }

    }

    catch (Exception x)

    {

        if (OnException(x))

            throw;

    }

}

 

 

/// <summary>

/// Performs a SQL Bulk Insert

/// </summary>

/// <param name="pocos">The POCO objects that specifies the column values to be inserted</param>       

/// <param name="batchSize">The number of POCOS to be grouped together for each database rounddtrip</param>       

public void BulkInsert(IEnumerable<object> pocos, int batchSize = 25)

{

    if (!pocos.Any()) return;

    var pd = PocoData.ForType(pocos.First().GetType());

    BulkInsert(pd.TableInfo.TableName, pd.TableInfo.PrimaryKey, pd.TableInfo.AutoIncrement, pocos);

}

#endregion

　　拼接方法的调用

1

2

3

4

5

6

7

public void BulkInsert(IEnumerable<object> pocos, int batchSize = 25)

{

    using (var db = dbcontext)

    {

        db.BulkInsert(pocos, batchSize);

    }

}

　　

1

<br>下面是拼接sql语句的连接

https://pastebin.com/aiviDREu

https://stackoverflow.com/questions/6595105/bulk-insert-update-with-petapoco/14479073

亲测同样的1000条数据 拼接sql语句 的方法用时1333毫秒

WriteToServer  用时 371 毫秒