CODEWITHSUNDEEP
c#asp.net.netdatabase
MrM
MrM

Reputation: 21999

Set value on SqlBulkCopy

I have 2 data transfer sql statements being run and sent to the same table.

SqlBuldCopy1 returns column NAME||AGE||CAT

SqlBuldCopy2 returns column NAME||AGE

Is it possible to set up a default value of "ALL" to column CAT in SqlBulkCopy2?

DataReader dr = command.ExcecuteReader();
SqlBulkCopy myCopy = new SqlBulkCopy(SQLconnection);
myCopy.DestinationTableName = "Customer";    
myCopy.BulkCopyTimeout = 0;
myCopy.BatchSize = 5000;
myCopy.WriteToServer(dr);

Upvotes: 0

Views: 651

Answers (2)

Binary Worrier
Binary Worrier

Reputation: 51711

For SQLBulkCopy2 you could change the select statement behind the dr DataReader to select out the literal "ALL" for cat e.g.

select NAME, AGE, 'ALL' CAT from  . . .

Or alternatively, implement the IDataReader interface in a class that wraps your DataReader, and provides a third column that always returns all.

On that note, I've built a CustomDataReader class that implement IDataReader. You initialise the class with an IEnumerable<T>, and add "Columns" to the class. Each column has a name, a data type (Column Type), and a lambda Func<T, CT> that takes a T and returns Column Type, basically the lambda knows how to get the column value out of the T item. The classes Read() method moves to the next item in the IEnumerable<T>, the GetValue() methods call the lambdas passing in the current item. Read returns false when the iterator MoveNext returns false for the Enumerator your got from the IEnumerable<T>.

It's actually quite straight forward to do, and is very handy for consuming odd things like files and feeds from message queues directly into SqlBulkCopy without first building them into DataTable's in memory. It's also quite handy for passing existing object graphs to SqlBulkCopy without preformatting them as data tables. I'd post the code but the company owns it now, sorry . . .

Hope this helps.

Upvotes: 2

Angelo
Angelo

Reputation: 1666

In the code you are specifying to be executed in your command.ExecuteReader, you can modify the query such that "ALL" is defined as the CAT column.

Sort of:

SELECT Name, Age, 'ALL' as CAT
FROM TableName

Upvotes: 1

Related Questions