CODEWITHSUNDEEP
c#sql-serverloadsp-executesql
Andrey
Andrey

Reputation: 1759

C# how to call “sp_executesql” for bulk insert

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

Upvotes: 0

Views: 431

Answers (2)

Fabulous
Fabulous

Reputation: 2423

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.

Upvotes: 1

nbot
nbot

Reputation: 184

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

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

Upvotes: 0

Related Questions