33 lines
4.3 KiB
HTML
33 lines
4.3 KiB
HTML
<div class="cnblogs_code">
|
||
<pre><span style="color: #008080;"> 1</span> <span style="color: #0000ff;">public</span> <span style="color: #0000ff;">static</span> <span style="color: #0000ff;">void</span> ImportTempTableDataIndex(DataSet ds,<span style="color: #0000ff;">string</span> TempTableName,<span style="color: #0000ff;">string</span><span style="color: #000000;"> strSqlConnection)
|
||
</span><span style="color: #008080;"> 2</span> <span style="color: #000000;"> {
|
||
</span><span style="color: #008080;"> 3</span>
|
||
<span style="color: #008080;"> 4</span> <span style="color: #008000;">//dcrenl:</span><span style="color: #008000;">获取写入连接</span>
|
||
<span style="color: #008080;"> 5</span> <span style="color: #0000ff;">string</span> str =<span style="color: #000000;"> strSqlConnection;
|
||
</span><span style="color: #008080;"> 6</span> SqlConnectionStringBuilder sb = <span style="color: #0000ff;">new</span><span style="color: #000000;"> SqlConnectionStringBuilder(str);
|
||
</span><span style="color: #008080;"> 7</span> <span style="color: #0000ff;">string</span> DataSource =<span style="color: #000000;"> sb.DataSource;
|
||
</span><span style="color: #008080;"> 8</span> <span style="color: #0000ff;">string</span> PersistSecurityInfo =<span style="color: #000000;"> sb.PersistSecurityInfo.ToString();
|
||
</span><span style="color: #008080;"> 9</span> <span style="color: #0000ff;">string</span> Pwd =<span style="color: #000000;"> sb.Password;
|
||
</span><span style="color: #008080;">10</span> <span style="color: #0000ff;">string</span> UserID =<span style="color: #000000;"> sb.UserID;
|
||
</span><span style="color: #008080;">11</span> <span style="color: #0000ff;">string</span> basestr =<span style="color: #000000;"> sb.InitialCatalog;
|
||
</span><span style="color: #008080;">12</span> <span style="color: #0000ff;">string</span> InitialCatalog =<span style="color: #000000;"> sb.InitialCatalog;
|
||
</span><span style="color: #008080;">13</span>
|
||
<span style="color: #008080;">14</span> <span style="color: #008000;">//dcrenl:</span><span style="color: #008000;">开始写入数据</span>
|
||
<span style="color: #008080;">15</span> str = $<span style="color: #800000;">"</span><span style="color: #800000;">Data Source={DataSource};database={basestr};user={UserID};password={Pwd}</span><span style="color: #800000;">"</span><span style="color: #000000;">;
|
||
</span><span style="color: #008080;">16</span> <span style="color: #0000ff;">using</span> (SqlBulkCopy sqlBulkCopy = <span style="color: #0000ff;">new</span><span style="color: #000000;"> SqlBulkCopy(str, SqlBulkCopyOptions.UseInternalTransaction))
|
||
</span><span style="color: #008080;">17</span> <span style="color: #000000;"> {
|
||
</span><span style="color: #008080;">18</span> <span style="color: #008000;">//</span><span style="color: #008000;">DataSet与数据库表一一对应时不需要写对应关系
|
||
</span><span style="color: #008080;">19</span> <span style="color: #008000;">//</span><span style="color: #008000;">sqlBulkCopy.ColumnMappings.Add("ID", "ID");</span>
|
||
<span style="color: #008080;">20</span>
|
||
<span style="color: #008080;">21</span>
|
||
<span style="color: #008080;">22</span> sqlBulkCopy.EnableStreaming = <span style="color: #0000ff;">true</span><span style="color: #000000;">;
|
||
</span><span style="color: #008080;">23</span> sqlBulkCopy.DestinationTableName = $<span style="color: #800000;">"</span><span style="color: #800000;">{TempTableName}</span><span style="color: #800000;">"</span><span style="color: #000000;">;
|
||
</span><span style="color: #008080;">24</span> sqlBulkCopy.WriteToServer(ds.Tables[<span style="color: #800080;">0</span><span style="color: #000000;">]);
|
||
</span><span style="color: #008080;">25</span>
|
||
<span style="color: #008080;">26</span> <span style="color: #000000;"> sqlBulkCopy.Close();
|
||
</span><span style="color: #008080;">27</span> <span style="color: #000000;"> }
|
||
</span><span style="color: #008080;">28</span> <span style="color: #000000;"> ds.Dispose();
|
||
</span><span style="color: #008080;">29</span> }</pre>
|
||
</div>
|
||
<p> </p>
|
||
<p>此方法测试30万记录，20个varchar(255)字段，导入6秒左右完成。</p> |