C# how to call “sp_executesql” for bulk insert

Question

I have files whic needs to be loaded to db with win form application for that i am creationg bulkload string C# code

  private void bt_loadInToTable_Click(object sender, EventArgs e)
        {
            bulkinsert = " BULK INSERT " + tableName + " FROM " + filePath +
        " WITH (FIRSTROW = 2, FIELDTERMINATOR = '','', ROWTERMINATOR = ''\n'',BATCHSIZE = 1000,  CHECK_CONSTRAINTS = ON , ERRORFILE = '" + tableName + "file_name.txt' )";

            using (SqlConnection thisConnectionT = new SqlConnection("Server=" + serverName + ";User Id=" + uName + ";Password=" + pass + ";Database=" + dbName + ";"))
            {

                thisConnectionT.Open();

                new SqlCommand(bulkinsert, thisConnectionT);
               // bulkinsert.ExecuteNonQuery();
            }

        }

however I guess i am calling it some how wrong, because its not loading, but not trowing an error
call supposed to be as exec sp_executesql bulkinsert.

this C# code should replace this SQL code which is running

DECLARE @bulkinsert NVARCHAR(2000)
set @bulkinsert = 'BULK INSERT ForTest FROM ''D:\\forBulk\\fortest.csv'' WITH (FIRSTROW = 2, FIELDTERMINATOR = '','', ROWTERMINATOR = ''\n'',BATCHSIZE = 1000 )'

EXEC sp_executesql @bulkinsert

If I am running it from sql side its insert records but From C# not

Sorry its may be a dumb question but I am DB developer

Print the bulkinsert string in C#, copy into your SQL editor and attempt to run. Any issues? — nbot, Mar 06 '18 at 22:11

score 1 · Answer 1 · answered Mar 06 '18 at 22:03

1

You may want to look into using the SqlBulkCopy class as explained in this article.

As a quick aside, I'd avoid building queries via string concatenation to mitigate against sql injection attacks.

answered Mar 06 '18 at 22:03

Fabulous

2,393
2
20
27

score 0 · Answer 2 · answered Mar 06 '18 at 22:00

0

The following sends the query EXEC('SELECT 1') to your connection.

new SqlCommand("EXEC('SELECT 1')", connection);

answered Mar 06 '18 at 22:00

nbot

184
7

but i need to send this "BULK INSERT " + tableName + " FROM " + filePath + " WITH (FIRSTROW = 2, FIELDTERMINATOR = '','', ROWTERMINATOR = ''\n'',BATCHSIZE = 1000, CHECK_CONSTRAINTS = ON )"; – Andrey Mar 06 '18 at 22:02
The OP is asking how to do bulk copy from C# and now how to send a command through. – Fabulous Mar 06 '18 at 22:04
C# is not responsible for reading the file and inserting into DB. C# should be kicking off a bulk insert with a string query, and then SQL should be reading the file... – nbot Mar 06 '18 at 22:08
@nbot than is what i am trying to achieve, I am building string with sql bulk statement and I need to send it to the sql server – Andrey Mar 06 '18 at 22:23

C# how to call “sp_executesql” for bulk insert

2 Answers2