How to get data from Excel into SQL Server using Visual Studio?

Question

I want to insert data from Excel into SQL Server. I have written code but the values are not being read from Excel:

System.Data.DataTable dt = new System.Data.DataTable();                

Workbook WB = Globals.ThisAddIn.Application.ActiveWorkbook;    
Sheets worksheets = WB.Worksheets;    
lastrow = WB.ActiveSheet.UsedRange.Rows.Count;                         

for(int i = 2; i<= lastrow; i++)      
{    
     cmd.CommandText = ("INSERT Table_1 (emp_no,emp_name,salary) values (WB.Cells[i , 1] , WB.Cells[i , 2] , WB.Cells[i , 3]))");                    
}

checked itz not working.. and tried by adding '"&WB.cells[i,1]&"' also not working. — suga
– suga, Commented Apr 26, 2018 at 14:46
SQL Injection alert - you should not concatenate together your SQL statements - use parametrized queries instead to avoid SQL injection - check out Little Bobby Tables — marc_s
– marc_s, Commented Apr 26, 2018 at 14:48

Community · Accepted Answer · 2020-06-20 09:12:55Z

2

Strongly not recommended, but working solution:

for(int i = 2; I <= lastrow; i++)      
{    
    cmd.CommandText = ($"INSERT INTO Table_1 (emp_no,emp_name,salary) VALUES ({WB.Cells[i, 1]}, {WB.Cells[i, 2]}, {WB.Cells[i, 3]}))");                    
}

Please don't use this and read about SQL injection for why not to use it!

Do yourself a favor and use parametrized queries instead.

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Apr 26, 2018 at 14:49

Thomas Flinkow

5,1556 gold badges34 silver badges72 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

KeithL Over a year ago

For future reference... does the $ mean that you can use code variables inside text?

Thomas Flinkow Over a year ago

Yes, kind of, it's the string interpolation operator, which allows for use of C# code inside of the the {} in the string. further reading

Collectives™ on Stack Overflow

How to get data from Excel into SQL Server using Visual Studio?

1 Answer 1

Strongly not recommended, but working solution:

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Strongly not recommended, but working solution:

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related