SqlBulkCopy 微軟官網版

微軟官網版

Imports System.Data.SqlClient

Module Module1
Sub Main()
Dim connectionString As String = GetConnectionString()

' Open a connection to the AdventureWorks database.
Using sourceConnection As SqlConnection = _
New SqlConnection(connectionString)
sourceConnection.Open()

' Perform an initial count on the destination table.
Dim commandRowCount As New SqlCommand( _
"SELECT COUNT(*) FROM dbo.BulkCopyDemoMatchingColumns;", _
sourceConnection)
Dim countStart As Long = _
System.Convert.ToInt32(commandRowCount.ExecuteScalar())
Console.WriteLine("NotifyAfter Sample")
Console.WriteLine("Starting row count = {0}", countStart)

' Get data from the source table as a SqlDataReader.
Dim commandSourceData As SqlCommand = New SqlCommand( _
"SELECT ProductID, Name, ProductNumber " & _
"FROM Production.Product;", sourceConnection)
Dim reader As SqlDataReader = commandSourceData.ExecuteReader

' Create the SqlBulkCopy object using a connection string.
' In the real world you would not use SqlBulkCopy to move
' data from one table to the other in the same database.
Using bulkCopy As SqlBulkCopy = _
New SqlBulkCopy(connectionString)
bulkCopy.DestinationTableName = "dbo.BulkCopyDemoMatchingColumns"

' Set up the event handler to notify after 50 rows.
AddHandler bulkCopy.SqlRowsCopied, AddressOf OnSqlRowsCopied
bulkCopy.DestinationTableName = _
"dbo.BulkCopyDemoMatchingColumns"
bulkCopy.NotifyAfter = 50

Try
' Write from the source to the destination.
bulkCopy.WriteToServer(reader)

Catch ex As Exception
Console.WriteLine(ex.Message)

Finally
' Close the SqlDataReader. The SqlBulkCopy
' object is automatically closed at the end
' of the Using block.
reader.Close()
End Try
End Using

' Perform a final count on the destination table
' to see how many rows were added.
Dim countEnd As Long = _
System.Convert.ToInt32(commandRowCount.ExecuteScalar())
Console.WriteLine("Ending row count = {0}", countEnd)
Console.WriteLine("{0} rows were added.", countEnd - countStart)

Console.WriteLine("Press Enter to finish.")
Console.ReadLine()
End Using
End Sub

Private Sub OnSqlRowsCopied(ByVal sender As Object, _
ByVal args As SqlRowsCopiedEventArgs)
Console.WriteLine("Copied {0} so far...", args.RowsCopied)
End Sub

Private Function GetConnectionString() As String
' To avoid storing the sourceConnection string in your code,
' you can retrieve it from a configuration file.
Return "Data Source=(local);" & _
"Integrated Security=true;" & _
"Initial Catalog=AdventureWorks;"
End Function
End Module

SqlBulkCopy A至B

Imports System.Data
Imports System.Data.SqlClient
Partial Class _Default
Inherits System.Web.UI.Page

Protected Sub btnBulkCopy_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnBulkCopy.Click
'程式本機 Database Server Connection String
Dim shcs As New SqlConnectionStringBuilder
shcs.DataSource = ".\SQLEXPRESS"
shcs.InitialCatalog = "Share"
shcs.UserID = "bruce"
shcs.Password = "123456789"
'遠端Database Server Connection String
Dim ntcs As New SqlConnectionStringBuilder
ntcs.DataSource = "192.168.3.12\SQLEXPRESS"
ntcs.InitialCatalog = "Northwind"
ntcs.UserID = "bruce"
ntcs.Password = "987654321"
Dim shcmd As New SqlCommand
Dim shdr As SqlDataReader
Dim shconn As New SqlConnection(shcs.ConnectionString)
shcmd.CommandText = " SELECT ID, First_Name, Last_Name, 'CEO' as Source FROM MailingList"
shcmd.CommandType = CommandType.Text
shcmd.Connection = shconn
shcmd.Connection.Open()
'與遠端建立SqlBulkCopy實體
Dim ntbcp As New SqlBulkCopy(New SqlConnection(ntcs.ConnectionString))
'指定遠端的資料表名稱
ntbcp.DestinationTableName = "Employees"
'本機與遠端的Column對應，如本機與遠端的Schema一樣，可省略ColumnMappings ntbcp.ColumnMappings.Add("ID", "EmployeeID")
ntbcp.ColumnMappings.Add("First_Name", "FirstName")
ntbcp.ColumnMappings.Add("Last_Name", "LastName")
'自定義欄位，把Source對應到Employees的Title欄位，所有欄位都會等於CEO
Dim TitleMapping As New SqlBulkCopyColumnMapping("Source", "Title") ntbcp.ColumnMappings.Add(TitleMapping)
'TimeOut，單位秒
ntbcp.BulkCopyTimeout = 360
'多少筆發一次通知
ntbcp.NotifyAfter = 1000
'委派
AddHandler ntbcp.SqlRowsCopied, AddressOf OnSqlRowsCopied
'AddHandler ntbcp.SqlRowsCopied, New SqlRowsCopiedEventHandler(AddressOf sqlRowsCopied)
shdr = shcmd.ExecuteReader
Try　　　　　
'執行WriteToServer，把本機資料(DataReader)大量複製到遠端資料庫
ntbcp.WriteToServer(shdr)
Catch ex As Exception
lblResult.Text = ex.Message
Finally
shdr.Close()
End Try
End Sub

Private Sub OnSqlRowsCopied(ByVal sender As Object, ByVal args As SqlRowsCopiedEventArgs) 　　　　　
'由於ntbcp.NotifyAfter的關係，每1000筆觸發一次
lblCounter.Text += args.RowsCopied.ToString() + " rows are copied. "
End SubEnd

Class

程式流程不會很難，


建立本機SqlConnection實體
建立遠端SqlBulkCopy實體(使用遠端Sqlconnection實體)
執行本機ExecuteReader方法，取得資料
執行SqlBulkCopy實體的WriteToServer方法，將DataReader寫到遠端Server去

TransactionScope

微軟的
參考1
參考2

交易元件 TransactionScope

Private Sub TestTransactionScope()
'以下是交易元件 TransactionScope 的範例
Dim strSql As String

strSql = "INSERT INTO tb1L (F1L_id, F1L_name) " & _
"VALUES (@F1L_id, @F1L_name) "

Using Scope As New TransactionScope
Try
'注意 TransactionScope 元件是以一個連線 SqlConnection 為準
'所以盡量使用
'Using Conn As New SqlConnection(strConnection)
'End Using
'以確保會再發生錯誤時正確關閉連線
Using Conn As New SqlConnection(strConnection)
Conn.Open()

Dim RmvCMD As SqlCommand = New SqlCommand
RmvCMD.CommandType = CommandType.Text
RmvCMD.Connection = Conn

RmvCMD.CommandText = strSql

'欄位格式設定
With RmvCMD.Parameters
.Add(New SqlParameter("@F1L_id", SqlDbType.VarChar))
.Add(New SqlParameter("@F1L_name", SqlDbType.VarChar))
End With

'TEST1
RmvCMD.Parameters("@F1L_id").Value = "T900009"
RmvCMD.Parameters("@F1L_name").Value = "測試1"
RmvCMD.ExecuteNonQuery()
'以上這一段理論上是可以正常通過的

'TEST2 這裡 F1L_id 超過欄位長度 所以會錯誤
RmvCMD.Parameters("@F1L_id").Value = "T900006000000000"
RmvCMD.Parameters("@F1L_name").Value = "測試2"
RmvCMD.ExecuteNonQuery()

End Using
'--注意 End Using 一定要在 Scope.Complete() 之前
' 不然會發揮不了
' TransactionScope 的作用且也不會有錯誤提示

'交易確認
Scope.Complete()
MsgBox("交易完成!")
Catch ex As Exception
MsgBox("交易發生錯誤!" & ex.Message)
End Try
End Using
End Sub

dotnetspider

dotnetspider

Dotnetspider.com is founded by Tony John to gather a good and quality IT professionals under one roof. This is the community sharing Technical views. Several Microsoft MVPs are directly or indirectly involved in this community offering their guidance.

Dotnetspider is platform for all .Net Professional/developers to share their stuffs. In addition, we do offer the platforms like resources, discussion forums, communities and few others coming soon. One of the key features in Dotnetspider is, members who contribute content will get 90% of Google AdSense revenue from their pages for a specific period of time.

分享世界

分享世界

讚!! 真是豐富的一站

租書軟體,數據分析圖表

如何在 DBCS 系统中显示扩展的 ASCII WingDings

微軟原站
難得找到這篇,記錄起來;詳細還請到微軟查看!!
---------------------------------------
Type LOGFONT
lfHeight As Long
lfWidth As Long
lfEscapement As Long
lfOrientation As Long
lfWeight As Long
lfItalic As Byte
lfUnderline As Byte
lfStrikeOut As Byte
lfCharSet As Byte
lfOutPrecision As Byte
lfClipPrecision As Byte
lfQuality As Byte
lfPitchAndFamily As Byte
lfFaceName As String * 32
End Type

'Charset constants
'Values for lf.lfCharSet:
Public Const ANSI_CHARSET = 0
Public Const DEFAULT_CHARSET = 1
Public Const SYMBOL_CHARSET = 2
Public Const SHIFTJIS_CHARSET = 128
Public Const HANGUL_CHARSET = 129
Public Const GB2312_CHARSET = 134
Public Const CHINESEBIG5_CHARSET = 136
Public Const GREEK_CHARSET = 161
Public Const TURKISH_CHARSET = 162
Public Const HEBREW_CHARSET = 177
Public Const ARABIC_CHARSET = 178
Public Const BALTIC_CHARSET = 186
Public Const RUSSIAN_CHARSET = 204
Public Const THAI_CHARSET = 222
Public Const EE_CHARSET = 238
Public Const OEM_CHARSET = 255

'Values to be used with GetSysColor:
Public Const COLOR_BTNFACE = 15
Public Const COLOR_ACTIVEBORDER = 10
Public Const COLOR_ACTIVECAPTION = 2
Public Const COLOR_ADJ_MAX = 100
Public Const COLOR_ADJ_MIN = -100 'shorts
Public Const COLOR_APPWORKSPACE = 12
Public Const COLOR_BACKGROUND = 1
Public Const COLOR_BTNHIGHLIGHT = 20
Public Const COLOR_BTNSHADOW = 16
Public Const COLOR_BTNTEXT = 18
Public Const COLOR_CAPTIONTEXT = 9
Public Const COLOR_GRAYTEXT = 17
Public Const COLOR_HIGHLIGHT = 13
Public Const COLOR_HIGHLIGHTTEXT = 14
Public Const COLOR_INACTIVEBORDER = 11
Public Const COLOR_INACTIVECAPTION = 3
Public Const COLOR_INACTIVECAPTIONTEXT = 19
Public Const COLOR_MENUTEXT = 7
Public Const COLOR_SCROLLBAR = 0
Public Const COLOR_WINDOW = 5
Public Const COLOR_WINDOWFRAME = 6
Public Const COLOR_WINDOWTEXT = 8

Declare Function TextOut Lib "gdi32" Alias "TextOutA" (ByVal hdc As _
Long, ByVal x As Long, ByVal y As Long, lpString As Any, _
ByVal nCount As Long) As Long
Declare Function DeleteObject Lib "gdi32" (ByVal hObject As Long) _
As Long
Declare Function CreateFontIndirect Lib "gdi32" Alias _
"CreateFontIndirectA" (lpLogFont As LOGFONT) As Long
Declare Function SelectObject Lib "gdi32" (ByVal hdc As Long, _
ByVal hObject As Long) As Long
Declare Function SetBkColor Lib "gdi32" (ByVal hdc As Long, _
ByVal crColor As Long) As Long
Declare Function GetBkMode Lib "gdi32" (ByVal hdc As Long) As Long
Declare Function GetSysColor Lib "user32" (ByVal nIndex As Long) _
As Long


--
Private Sub Command1_Click()

Dim lf As LOGFONT
Dim chars(257) As Byte
Dim sysColor As Long
Dim DispChar As Integer
Dim NumDispchar As Integer
Dim prevBkCol As Long
Dim hFont As Long
Dim StartASCII As Integer
Dim StopASCII As Integer
Dim StartRow As Integer
Dim NumOfChars As Integer
Dim i As Integer
Dim j As Integer
Dim EndRow As Integer
Dim NewStart As Integer
Dim tmp As Integer
Dim xd As Long
Dim xt As Long
Dim xs As Long

'These are the important settings: lfCharSet and lfFaceName
lf.lfCharSet = SYMBOL_CHARSET
lf.lfFaceName = "WingDings" & Chr$(0)

lf.lfClipPrecision = 64
lf.lfOutPrecision = 0
lf.lfEscapement = 0
lf.lfItalic = 0
lf.lfWidth = 16
lf.lfHeight = 32
lf.lfOrientation = 0

Form1.Caption = lf.lfFaceName
Form1.Refresh
sysColor = GetSysColor(COLOR_BTNFACE) 'Gets button system color
prevBkCol = SetBkColor(Form1.hdc, sysColor) 'Set background color
hFont = CreateFontIndirect(lf)
xs = SelectObject(Form1.hdc, hFont)

DispChar = 32 'Number of characters to display in a row
StartASCII = 32 'Starting with character having this ASCII value
StopASCII = 255 'Ending with this ASCII character
StartRow = 1

If StartASCII < 0 Then StartASCII = 32
If StopASCII > 255 Then StopASCII = 255
If StartASCII > StopASCII Then 'If StartASCII is larger than
'StopASCII swap
tmp = StartASCII
StartASCII = StopASCII
StopASCII = tmp
End If

NumOfChars = StopASCII - StartASCII + 1

If Int(NumOfChars / DispChar) = (NumOfChars) / DispChar Then
EndRow = Int(NumOfChars / DispChar)
Else
EndRow = Int(NumOfChars / DispChar) + 1
End If

For i = StartASCII To StopASCII
chars(i) = i
Next i

'Display characters on the form in rows containing 32 characters
For j = StartRow To EndRow
NewStart = StartASCII + (j - 1) * DispChar

If NumOfChars < DispChar And NumOfChars > 0 Then
NumDispchar = NumOfChars
Else
NumDispchar = DispChar
End If

xt = TextOut(Form1.hdc, 0, j * lf.lfHeight + 2, _
chars(NewStart), NumDispchar)
NumOfChars = NumOfChars - NumDispchar
Next j

xd = DeleteObject(hFont)
Form1.Caption = Form1.Caption & " From " & _
StartASCII & " to " & StopASCII

End Sub