AmtBulkCopy Interface

The Database object is returned by the GetBulkCopy function provided through the ComScript Database interface. This object exposes the functionality of the ADO.NET System.Data.SqlClient.SqlBulkCopy class.

The following functionality is provided:

Properties

Name	Type	Access	Description
BatchSize	Integer	Read/Write	Number of rows in each batch, as set in the System.Data.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.
BulkCopyTimeout	Integer	Read/Write	Number of seconds for the copy operation to complete before a timeout 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	Read-only	The number of rows copied in the bulk copy. This value is updated every time the number of rows set in the NotifyAfter property has 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 Name parameter to the SqlBulkCopy instance.
AddSqlBulkCopyColumnMapping	Void	SourceColumn	String	Adds a mapping to the ColumnMappings collection from the source column provided in the SourceColumn parameter of the source table to the destination column provided in the DestinationColumn parameter of the destination table. Note: See Column mappings below.
		DestinationColumn	String

Close	Void	---		Closes the System.Data.SqlClient.SqlBulkCopy instance.
WriteToServer	Void	Table	DataTable	Copies all rows of the table provided by the System.Data.DataTable Table object to the destination table specified in the System.Data.SqlClient.SqlBulkCopy.DestinationTableName property of the System.Data.SqlClient.SqlBulkCopy object, using the mappings set by the AddSqlBulkCopyColumnMapping 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 relationship between the columns of the data source table and the target table. The following rules apply:

When no mappings have been defined, and the ColumnMappings collection is empty, the columns are mapped implicitly by ordinal position. For this to work, the source and target schemas must match. If they do not, an InvalidOperationException is raised.
When the ColumnMappings collection is not empty, not every column in the data source needs to be specified. Columns that are 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 source table columns and destination table columns.
$SQLBulk.AddSqlBulkCopyColumnMapping('CUSTNAME', 'NAME')
$SQLBulk.AddSqlBulkCopyColumnMapping('CUSTADDRESS', 'ADDRESS')
$SQLBulk.AddSqlBulkCopyColumnMapping('CUSTCITY', 'CITY')
$SQLBulk.AddSqlBulkCopyColumnMapping('CUSTNO', 'CUSTNO')

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

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

arrow_back Previous Next arrow_forward