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++)
{
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://*.com/questions/6595105/bulk-insert-update-with-petapoco/14479073
亲测同样的1000条数据 拼接sql语句 的方法用时1333毫秒
WriteToServer 用时 371 毫秒