الفئة SqlBulkCopy تحت المجهر

السلام عليكم

أعني بكلمة فئة أو قالب : الـ Class

الفئة SqlBulkCopy الذي يقع في System.Data.SqlClient.SqlBulkCopy

, هي إحدى القوالب الجديدة في الإصدارة Framework 2.0.

هذا القالب مجهز لنقل مئات الالوف من القيم من جدول إلى أخر ( شبية بعمل DTS ) إلا انه الافضل على الاطلاق

من كل الادوات المتاحة سواءً كانت بشكل يدوي أو بشكل برمجي..

تقتصر وظيفتة على نقل البيانات إلى قواعد البيانات التي يستضيفها SQL Server .

أم المنقول منه فغير مشروط يمكن ان يكون Access أو Oracle أو غير ذلك.

وظيفتة : كما اسلفنا انه لنقل البيانات من جدول إلى اخر بشرط ان يكون الاخر يحوي أعمدة بنفس نوع بيانات الاول

مع نفس الـ Meta data بالضبط . غير ذلك قد تحاول الفئة SqlBulkCopy بعمل تحويل لأنواع البيانات المتقاربة في مبناها, لأجل التطابق

وقد تنجح أحياناً ولكن ذلك ستكون ضريبة على حساب الاداء والسرعة التي هي اصلاً مرجوة من هذه الفئة ,

ولا اخفيك سراً قد يخفق ويرجع بأخطاء Errors أو يقوم بنتائج غير متوقعة كمثلاً يتم اضافة جزء من البيانات و ترك اخرى , كونه كإفتراضياً

ليس على نمط الاجراءت التامة , اي انك لا تستطيع ان تعمل Roll back إلا اذا كانت العملية من نوع Transaction فقظ.

كذلك يستطيع هذا القالب نقل التحديث أو الاضافة أو الحذف.

ملاحظة بالنسبة لمستخدمي Framework 1.1 يمكنهم الاستفادة من ذلك باستخدام الاستعلام التالي

Bulk Insert Statement عن طريق الـ Command Object .

سنتعلم في هذا الدرس

1 العملية الاحادية Single Operation

2 العمليات المتعددة Multi Operations

3 العمليات الاجرائية التامة ككل أو لا Transaction Operation

الفصل الاول : Single Operation نقل قيم جدول واحد إلى جدول أخر ..

سنقوم في هذا الفصل بإنشاء إتصال بقاعدة البيانات و جلب البيانات منها ثم نقوم بإنشاء SqlBulkCopy ليقوم بنقل البيانات في جدول أخر.

يمكنك تطبيق المثال بتغير قيمة الاتصال و اسماء الجدولين كما سيأتي في النصوص البرمجية التالية.

 'Create the connection String.
	Private ConnectionString As String = _
	"Data Source=.;Initial Catalog=SqlBulkCopyDataBase;Integrated Security=True"

'Create a Connection Object 
		Using CnnMain As New SqlConnection(ConnectionString)

			'Open  the connection.
			CnnMain.Open()

			'------------- STEP 1 --------------'
			'Truncate Destination table by clearing all rows.
			'Prepare Source Table from the source Database
			'-----------------------------------'
			Dim cmd As New SqlCommand( _
			"Truncate Table DestinationTable; Select * from SourceTable", CnnMain)

			'Read all data ( rows ) from SourceTable table.
			Using dr As SqlDataReader = cmd.ExecuteReader()

				'------------- STEP 2 --------------'
				'Transformed Data to the Destination Table
				'-----------------------------------'
				Using bc As New SqlBulkCopy(ConnectionString, _
				 SqlBulkCopyOptions.KeepIdentity Or _
				  SqlBulkCopyOptions.KeepNulls)
					With bc

						'Assign the Destination table
						.DestinationTableName = "DestinationTable"

						'Push the data to the server
						.WriteToServer(dr)

						MsgBox("Single Bulk Operation Executed Successfully.")
					End With
				End Using
			End Using
		End Using

ملاحظة: ستجد أنني استخدم قيمة اتصال وحيدة : وهذا بسبب أن جدول المصدر و الجدول المقصود يقبعان في نفس قاعدة البيانات

وذلك من باب التدريب و الاختبار لا أكثر, وهو لا يمثل العمل الحقيقي .

الخطوات لإتمام المهمة كالتالي

1 – إنشاء عنصر اتصال و تحضير بيانات الجدول عن طريق العنصر SqlCommand و SqlReader

2 – إنشاء عنصر من الفئة SqlBulkCopy لتنفيذ نقل البيانات بشكل المطلوب .

متطلبات تشغيل الفئة SqlBulkCopy

1 – إستنساخ عنصر من هذه الفئة و تمرير قيمة الاتصال في الـ Construction

2 - العنصر SqlReader الذي سيقرأ بيانات جدول المصدر.

تنبية : لاتستخدم إعادة تنفيذ النصوص البرمجية, لتفادي عملية التكرار Duplicate في الجدول إلا أذا

كنت تقوم بعملية Truncate للجدول قبل ملئة.

الفصل الثاني : Multi Operations العمليات المتعددة نقل جدولين مرتبطين بعلاقة في جدولين مماثلين.

شبيهة بالعملية الاولى إلى حدٍ ما , من حيث استخدام نفس الفئة SqlBulkCopy إلا أن الطريقة تحتاج منا إلى إعادة توظيف

الفئة للجدول أو الجداول المتعلقة بالجدول الاب.

الخطوات لإتمام المهمة كالتالي

1 – إنشاء عنصر اتصال و تحضير بيانات جدول الاب عن طريق العنصر SqlCommand و SqlReader

2 – إنشاء عنصر أتصال اخر لتحضير بيانات جدول الابن بنفس العنصرين السابقين.

3 – إنشاء عنصر من الفئة SqlBulkCopy لتنفيذ نقل البيانات بشكل المطلوب

سنتعلم في هذا الدرس عملية تحديد كل عامود من المصدر Source Database بالعامود المناسب في قاعدة البيانات المقصودة

Destination Database

وذلك عن طريق الخاصية ColumnMappings التي تحصر مكان البيانات المنتقلة من و إلى .

شاهد النصوص البرمجية فهي لا تحتاج إلى تفكير عميق كونها تحتوي على تعليقات شبه مفصلة.

'------------- STEP 1 --------------'
		'Prepare Parents data from the source Database
		'-----------------------------------'

		'Create Parents Connection Object 
		Using ParentsConnection As New SqlConnection(ConnectionString)

			'Create Parents Command object.
			Dim cmdParents As SqlCommand = ParentsConnection.CreateCommand

			'Select Parents table. as a Source table..
			cmdParents.CommandText = "Select * from Parents"

			'Open the Parents connection.
			ParentsConnection.Open()

			'Read all data ( rows ) from Parents table.
			Dim drParents As SqlDataReader = cmdParents.ExecuteReader()


			'------------- STEP 2 --------------'
			'Prepare Childs data from the source Database
			'-----------------------------------'

			'Create Childs connection object.
			Using ChildsConnection As New SqlConnection(ConnectionString)

				Dim cmdChilds As SqlCommand = New SqlCommand( _
				 "SELECT P.*,C.* " & _
				 "FROM Parents p INNER JOIN " & _
				 "Childs C " & _
				 "ON P.ParentID = C.ParentID", ChildsConnection)

				ChildsConnection.Open()
				Dim drChilds As SqlDataReader = cmdChilds.ExecuteReader

				'------------- STEP 3 --------------'
				'Transformed Parents Data to the Destination Table
				'-----------------------------------'

				'Create a SBC object.
				Using bc As New SqlBulkCopy(ConnectionString)
					With bc

						'Assign the Parents Destination table
						.DestinationTableName = "DestinationParentsTable"

						'Mapping Parents Columns 
						'---------------Add(SourceTable, DestinationTable)
						.ColumnMappings.Add("ParentID", "ParentID")
						.ColumnMappings.Add("Name", "Name")

						' Write Parents Data to the destination.
						Try
							.WriteToServer(drParents)
						Catch ex As Exception
							MsgBox(ex.Message)
						Finally
							drParents.Close()
						End Try


						'------------- STEP 4 --------------'
						'Transformed Childs Data to the Destination Table
						'-----------------------------------'

						'Assign the Childs Destination table
						.DestinationTableName = "DestinationChildTable"

						'Clear Columns Mapping befor adding new.
						.ColumnMappings.Clear()

						'Mapping Childs Columns 
						.ColumnMappings.Add("ChildID", "ChildID")
						.ColumnMappings.Add("ParentID", "ParentID")
						.ColumnMappings.Add("Number", "Number")

						'Write Childs Data to the destination.
						Try
							.WriteToServer(drChilds)
							MsgBox("Multi Bulk Operation Executed Successfully.")
						Catch ex As Exception
							MsgBox(ex.Message)
						Finally
							drChilds.Close()
						End Try

					End With

				End Using ' Dispose SqlBulkCopy  object.
			End Using ' Dispose Childs Connection object.
		End Using ' Dispose Parents Connection object.

الفصل الثالث : Transaction Operation العملية الاجرائية التامة الناتج أما نقل كل البيانات أو لا .

بما أننا وصلنا إلى أخر المطاف بالنسبةٍ للشرح لا للمشروح كونه يحتاج كتاب بحالة لتغطيته, إلا اننا نريد أن نذكر هاهنا

بعض الخيارات enumeration التي يمكان ان تحسن من الاداء في هذه الفئة,

هذه الخيارات يتم تضمينها عند استنساخ عنصر من الفئة SqlBulkCopy و تمرر بعد تمرير قيمة أو عنصر الاتصال

كما سيأتي, والخيارات الممكنة كالتالي :

CheckConstraints التحقق من الثوابت عند عملية أدخال البيانات في الجدول المستهدف, هذا الخيار غير مفعل كإفتراض.

Default إستخدام القيم الإفتراضية لكل الخيارات

على سبيل المثال : عند اختيار الـ Default ستكون CheckConstraints معطلة Disable و FireTriggers معطلة وهلم جرى.

FireTriggers هذا الخيار معطل بشكل أفتراضي, عند تمكينه سيجبر المستضيف Server على تشغيل الـ Triggers المصاحبة للصفوف المراد ادخالها في الجدول المستهدف , مما يتيح لك تحكم أكثر في عملية التحديث.

KeepIdentity المحافظة على بقاء الاعمدة الإنفرادية uniquely بحيث يمنع عملية التكرار Duplicate فيها.

إضافة إلى ذلك أجبار المستضيف بخلق قيم إنفرادية تغطي القيم المحذوفة إن وجدت محذوفات في الجدول المستضيف.

KeepNulls المحافظة على القيم الفارغة ,حتى ولو كانت معنونة بقيم افتراضية default values ,

غير ذلك يتم استبدال القيم الفارغة بالقيم الافتراضية إن كانت معنونة لها أصلاً.

TableLock عملية نقل الصفوفة بشكل أفتراضي تكون مقفلة من التحديث , هذا الخيار يقوم بقفل الجدول ككل

مما يتيح أفضلية وسرعة في نقل البيانات من المصدر المقفول إلى الجدول المستقبل.

UseInternalTransaction استخدام العملية التامة Transaction بشكل آلي من نفس الفئة.

تنبية : لا يمكن أن تستخدم الخيار UseInternalTransaction مع External transaction , غير ذلك

ستظهر رسالة خطأ مصدرها الـ Argument Exception

كذلك هنالك بعض الخواص التي تحسن من أداء الـ SqlBulkCopy

الخاصية BatchSize تقرأ و تكتب وظيفتها تحديد عدد الصفوف كمجموعات لنقلها إلى الجدول المقصود

على سبيل المثال: عندما تسند لها الرقم 10 فإنك تخبر الفئة بأن الصفوف المنقولة ستكون على شكل مجموعات مكونه من عشر أرقام

هذه الخاصية تسرع من عمل الفئة لانها تحدد العدد و تنظم العمل.

الخاصية NotifyAfter تساوي القيمة صفر إذا لم تحدد, وظيفتها توليد و تفعيل حدث التنبية

Notification Event بعد تحديد عدد الصفوف التي سترسل للجدول المستضيف

على سبيل المثال : القيمة 10 سيتم توليد حدث التنبية بعد أرسال عشر صفوف للجدول المستضيف.

الخاصية BulkCopyTimeout عدد الثواني التي ينبغي ان تتم فيها عملية النقل قبل نفاذ الوقت.

إذا تعدت الثواني القيم المحددة لن يتم نقل البيانات , و البيانات المنقولة قبل نفاذ

الوقت سيتم حذفها من الجدول المقصود. القيمة الافتراضية هي 30 ثانية .

على سبيل المثال : القيمة 10 تحدد أن العملية لابد أن تتم في غضون 10 ثواني فقط.

الخاصية ColumnMappings وظيفتها تحديد عامود المنقول منه إلى العامود المستضيف

بها بعض الخصائص و السلوكيات أهمها السلوك Add لإضافة العامودين مما يعطينا تحكم أكثر

في تحديث قيم العامود في المصدر إلى العامود المقصود .

كذلك من خواصها الخاصية Count التي تخبرنا بعدد الاعمدة المرتبطة ببعضها البعض عن طريق السلوك Add

كذلك الخاصية Capacity التي نقوم بها بتحديد كمية الاعمدة المراد ادخالها , هذه الخاصية دائماً أكبر من أو تساوي

الخاصية Count فهي تتزايد بشكل آلي كلما زادت قيمة الخاصية Count .

اخيراً الخاصية DestinationTableName تحدد اسم الجدول الذي سيستقبل البيانات.

تنقسم الإجراءت التامة transactions في هذه الفئة إلى قسمين

1 الاجراءت الداخلية Internal Transaction وهي بتحديد الخيار UseInternalTransaction شاهد كيفية التطبيق

'Create a Connection Object for datareader.
		Using CnnMain As New SqlConnection(ConnectionString)

			'Open the connection object.
			CnnMain.Open()

			'------------- STEP 1 --------------'
			'Truncate Destination table by clearing all rows.
			'Prepare Source Table from the source Database
			'-----------------------------------'
			Dim cmd As New SqlCommand( _
			"Truncate Table DestinationTable; Select * from SourceTable", CnnMain)

			'Read all data ( rows ) from source table.
			Dim dr As SqlDataReader = cmd.ExecuteReader()


			'------------- STEP 2 --------------'
			'Transformed Data to the Destination Table
			'-----------------------------------'
			'Create a SBC object.
			Dim bc As New SqlBulkCopy(ConnectionString, _
			 SqlBulkCopyOptions.UseInternalTransaction)

			With bc
				'Assign the Destination table
				.DestinationTableName = "DestinationTable"

				'Push the data to the server
				.WriteToServer(dr)

				'Close Bulk Copy Object.
				.Close()

				'Close dataReder object.
				dr.Close()
			End With
			MsgBox("Internal Transaction Bulk Operation Committed Successfully.")
		End Using 'Dispose Connection CnnMain

2 الاجراءت الخارجية وهي استخدام Transaction object الفارق بين هذا و الخيارات السابقة

هو ان الفئة SqlBulkCopy تقبل عنصر اتصال لا قيم الاتصال وكذلك اي من الخيارت أو اسناد القيمة Nothing

شاهد كيفية تطبيق ذلك.

'Create a connection object for datareader.
		Using CnnMain As New SqlConnection(ConnectionString)

			'Open the connection object.
			CnnMain.Open()

			'------------- STEP 1 --------------'
			'Truncate destination table by clearing all rows.
			'Prepare source table from the source database
			'-----------------------------------'
			Dim cmd As New SqlCommand( _
			"Truncate Table DestinationTable; Select * from SourceTable", CnnMain)

			'Read all data ( rows ) from source table.
			Dim dr As SqlDataReader = cmd.ExecuteReader()



			'------------- STEP 2 --------------'
			'Create second connection for both SBC & transaction
			'Create transaction object.
			'Create SBC object.
			'-----------------------------------'
			Using cnn As New _
			 SqlConnection(ConnectionString)

				cnn.Open()

				Using transaction As SqlTransaction = _
				cnn.BeginTransaction

					Using bc As New SqlBulkCopy( _
					cnn, SqlBulkCopyOptions.Default, transaction)

						Try
							With bc
								'Assign the Destination table
								.DestinationTableName = "DestinationTable"

								'Push the data to the server
								.WriteToServer(dr)

								'push data
								transaction.Commit()


								'Close Bulk Copy Object.
								.Close()

								'Close dataReder object.
								dr.Close()

								MsgBox("Existing Transaction Bulk Operation Committed Successfully.")
							End With

						Catch ex As Exception

							transaction.Rollback()

							MsgBox(ex.Message)

						End Try
					End Using
				End Using
			End Using
		End Using

هذا والله أعلى و اعلم.

SqlBulkCopyApplication.rar