Click here to Skip to main content
15,891,253 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
I have a data table with 5 columns which are different datatypes. This data Table has got about 2000 rows of data.
I need to insert this data table data into a sql table, I use the following mehtod to insert,

C#
string sSqlQuery=string.empty;
for(int i=0;i<datatable.rows.count;i++)>
{
	sSqlQuery+="Insert into sample(c1,c2,c3,c4,c5)values(
	'"+ dataTable.rows[i]["c1"].toString() +"',"
	'"+ dataTable.rows[i]["c2"].toString() +"',"
	'"+ dataTable.rows[i]["c3"].toString() +"',"
	'"+ dataTable.rows[i]["c4"].toString() +"',"
	'"+ dataTable.rows[i]["c5"].toString() +"');";
}

if(!string.IsNullOrEmpty(sSqlQuery))
{
	//COde to insert the query into database.
}
Is this the right way to insert bulk data into database. Do we have any other method to do it.
Posted
Updated 24-Jul-13 20:43pm
v2
Comments
Maciej Los 25-Jul-13 2:46am    
I'm bit confused... The question has been tagged as "SQL2005", but example code is in C#... Do you want to do it using in C# code or in T-SQL?

1 solution

Please, read my comment to the question. Did you heard about SQL Injection[^]? You should protect[^] your data. Instead creating query in code behind, use stored procedures[^].

I would suggest you to read about:
Dynamic SQL & SQL injection[^]
ADO.NET[^]
ADO.NET Code Examples[^]
Walkthrough: Using Only Stored Procedures (C#)[^]
HOW TO: Call a Parameterized Stored Procedure by Using ADO.NET and Visual C# .NET[^]
How to: Execute a Stored Procedure that Returns Rows[^]
How to: Execute a Stored Procedure that Returns No Value[^]

Please, follow the related links too.
 
Share this answer
 
Comments
sacraj 25-Jul-13 3:03am    
If I use stored procedure. For each record the database connection is made, Would not there be any problem If we have lacks of records..
Maciej Los 25-Jul-13 3:13am    
The proper way is:
1) Open connection
2) update requested records
3) close connection
but not:
1) Open connection
2) update requested records
3) close connection
4) return to step 1

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900