Question
I need to insert multiple rows at the same time (1000 rows) into a SQL Server database. I think best way is to use SqlBulkCopy but I'm not sure how to parametrize insert queries to be safe from SQL injection.
Can you please help me? What is best way to perform multiple insert statements (SQL injection safe)?
Thank you.
Expert Answer
The best way to insert multiple rows is by using SqlBulkCopy.
The SqlBulkCopy class is already safe from SQL Injection. So you don't have to worry about this.
Popular Answer
I have used this solution on multiple occassions to do multiple inserts : http://www.sqlteam.com/article/sql-server-2008-table-valued-parameters
Keep in mind that there is an issue with the SQL server security for table valued types. You need to use a wonky syntax to set them:
grant execute on TYPE::dbo.tableType to role_or_user