AmtBulkCopy Interface

The Database object is returned by the GetBulkCopy function that is provided through the ComScript DataBase interface. Through this object, the functionality of the ADO.NET System.Data.SqlClient.SqlBulkCopy class can be used.

The following functinallity is provided:

Properties

Name	Type	Access	Description
BatchSize	Integer	Read/Write	Number of rows in each batch as set in the System.Database.SqlClient.SqlBulkCopy.BatchSize property. At the end of each batch, the rows in the batch are sent to the server. The default value is zero.
BulkCopytTimeout	Integer	Read/Write	Number of seconds for the copy operation to complete before a time out is generated.
DestinationTableName	String	Read/Write	The string value of the System.Data.SqlClient.SqlBulkCopy.DestinationTableName property. The default value is null.
NotifyAfter	Integer	Read/Write	The number of rows to be processed before a notification event is generated, set in the System.Data.SqlClient.SqlBulkCopy.NotifyAfter property.
SqlRowsCopied	Integer	Readonly	The number of rows copied in the bulkcopy. This value is updated every time the number of rows set in the property NotifyAfter, have been processed.

Functions

Function Name	Return Type	Parameters		Description
Function Name	Return Type	Name	Type	Description
AddDataColumn	Void	Name	String	Adds a data column with the name provided in the parameter name to the SqlBulkCopy instance.
AddSqlBulkCopyColumnMapping	Void	SourceColumn	String	Adds a mapping to the ColumnMappings collection, of the source column provided in the parameter SourceColumn of the source table, to the destination column provided in the parameter DestinationColumn of the destination table. Notes: See Column Mappings below!
		DestinationColumn	String

Close	Void	---		Closes the System.Data.SqlClient.SqlBulkCopy instance.
WriteToServer	Void	Table	DataTable	Copies all the rows of the table provided by the System.Data.DataTable Table to the destination destination table specified in the System.Data.SqlClient.SqlBulkCopy.DestinationTableName property of System.Data.SqlClient.SqlBulkCopy object, using the mappings set by the AddSqlBlulkCopyColumnMapping function.
WriteToServer	Void	Reader	DataReader	Overload of the WriteToServer function above. The source table is now provided by an open DataReader object Reader.

Column Mappings

Column mappings define the mappings between the columns of the data source table and the target table. The following rules apply:

When no mapplings have been defined, i.e. the ColumnMappings collection is empty, the columns are mapped implicitely on ordinal position. For this to work, the source and target schemas must match. When they do not, an InvalidOperationException will be raised.
When the ColumnMappings collection is not empty, not every column present in the data source needs to specified. Those not mapped are ignored.
You can refer to source and target columns by either the column name or the ordinal position of the column.
You can mix by-name and by-ordinal column references in the same mapping collection.

Example

PowerShell

# See ComScript Connection Interface
# ...

$ComScr.Connect()

#Create an object of the Database Interface to be able to start a transaction
$Database = $ComScr.CreateDatabase()

#Fill a DataTable object with a select query
$DataTable = $Database.OpenQuery('SELECT CUSTNAME, CUSTADDRESS, CUSTCITY, CUSTNO FROM T_CUSTOMERS')

#Create an object of the BulkCopy Interface to start a bulk copy
$SQLBulk = $Database.GetBulkCopy()

$SQLBulk.DestinationTableName = 'CUST_INFO'

#Add Column Mappings to match the source table columns and the destination table columns
$SQLBulk.AddSqlBulkCopyColumnMapping('CUSTNAME', 'NAME')
$SQLBulk.AddSqlBulkCopyColumnMapping('CUSTADDRESS', 'ADDRESS')
$SQLBulk.AddSqlBulkCopyColumnMapping('CUSTCITY', 'CITY')
$SQLBulk.AddSqlBulkCopyColumnMapping('CUSTNO', 'CUSTNO')

#Excecute the bulk copy and close the instance
$SQLBulk.WriteToServer($DataTable)
$SQLBulk.Close()

# Finally release the variables and clean up the session.
$ComScr.Dispose()