现在网的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++)
{
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);
}
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 毫秒
590
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
