Insert multiple rows into SQL Server 2005 using a stored procedure

Question

I am working on a ASP.NET MVC / C# application.

I have a list of objects lets say List<Student> students that were imported from a CSV.

I want to insert them into a SQL Server 2005 database using stored procedures and transactions. In case a row fails it should rollback.

Any suggestions?

Nice - so go ahead and write some code! – marc_s Jan 12 '15 at 15:26 — marc_s, Jan 12 '15 at 15:26

score 0 · Answer 1 · edited May 23 '17 at 12:27

0

You can do it in two ways

Bulk insert using Bulk Insert command (from files to DB)

Please check this

How to insert xml data into table in sql server 2005

You can generate XML and then insert.

Please check this

How to insert xml data into table in sql server 2005

Thanks, Anand

edited May 23 '17 at 12:27

Community

1
1

answered Jan 12 '15 at 15:28

Anand Gangadhara

51
5

To insert list to database, please check this http://stackoverflow.com/questions/12426300/how-to-insert-list-from-c-sharp-into-sql-server-2008 – Anand Gangadhara Jan 12 '15 at 15:32

Giorgi Nakeuri · Accepted Answer · 2015-02-06T14:56:44.857

Do you use ADO or Entity?

You can use transactions from your code. For ADO

try
{
    sqlTransaction = sqlConnection.BeginTransaction();

    //call insert in loop

    sqlTransaction.Commit();
}
catch{
    sqlTransaction.Rollback();
}

For Entity

try
{
    entityTransaction = Context.Connection.BeginTransaction();

    //call insert in loop

    entityTransaction.Commit();
}
catch{
    entityTransaction.Rollback();
}

Insert multiple rows into SQL Server 2005 using a stored procedure

2 Answers2