如果是一筆一筆新增的話，在網站是非常耗效能的，
在資料庫這一來一往的開關，會耗掉非常多效能，
當資料量小時也許沒有差別，但當資料是上萬筆以上，這樣一開一關就非常的耗效能了，
ADO.NET 2.0以上提供了SqlBulkCopy這個類別可以方便的把大量的資料複製，
SqlBulkCopy的詳細內容可以參考MSDN上的說明 (詳細)，
在資料庫中我範例的SQL Script如下：
CREATE TABLE [dbo].[ExcelData](
[id] [int] NOT NULL,
[name] [nvarchar](50) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
[email] [nvarchar](50) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
CONSTRAINT [PK_ExcelData] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
在這邊有兩點需要注意的地方，
(1)有黃底的這些欄位是跟資料庫對應的欄位
(2)Excel頁籤是再匯入時的table名稱，需要加上$字號，如ExcelData$
範例：
<%@ Page Language="VB" AutoEventWireup="false" CodeFile="test_BulkCopy.aspx.vb" Inherits="test_BulkCopy" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>Excel的資料匯入SQL Sever</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:Button ID="btnImport" runat="server" Text="匯入" />
</div>
</form>
</body>
</html>
Imports System
Imports System.Collections.Generic
Imports System.IO
Imports System.Web.UI
Imports System.Text
Imports System.Web.Configuration
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.OleDb
Imports System.Data.Common
Imports System.Web.Security
Imports System.Web.UI.HtmlControls
Partial Class test_BulkCopy
Inherits System.Web.UI.Page
Friend Sub ImportDB()
'Excel檔案的實體路徑
Dim ExcelPath As String = "D:\TEST\test.xls"
'Excel的OLEDB ConnectionString
Dim ExcelConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & ExcelPath & ";Extended Properties=""Excel 8.0;HDR=YES;IMEX=1"""
Dim ExcelCn As New OleDbConnection(ExcelConnectionString)
Dim ExcelCmd As New OleDbCommand
Dim ExcelDr As DbDataReader
ExcelCmd.CommandText = "Select id,name,email from [ExcelData$]" '抓取Excel資料的SQL指令
ExcelCmd.CommandType = CommandType.Text
ExcelCmd.Connection = ExcelCn
ExcelCmd.Connection.Open()
ExcelDr = ExcelCmd.ExecuteReader(CommandBehavior.CloseConnection)
Dim cn As New SqlConnection '設定資料庫Connect物件
'設定資料庫Connection連接
cn.ConnectionString = ConfigurationManager.ConnectionStrings("test_conndb").ConnectionString
cn.Open()
Dim BulkCopy As New SqlBulkCopy(cn) '宣告SqlBulkCopy物件
BulkCopy.DestinationTableName = "ExcelData" '定義要匯入的資料庫Table
BulkCopy.WriteToServer(ExcelDr) '寫入資料
ExcelDr.Close()
ExcelCmd.Dispose()
ExcelCn.Dispose()
cn.Dispose()
End Sub
Protected Sub btnImport_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnImport.Click
ImportDB()
End Sub
End Class
沒有留言:
張貼留言