Transations in SQL Server and ADO.NET

When developing any database driven application in .NET, one has to deal with ADO.NET where scenario is that either saves all records or none.

How to do it?

• Option 1: Use .NET transaction as below

    public class Employee

    {

        public int EmployeeID { get; set; }

        public int EmployeeName { get; set; }

    }

    private static bool ExecuteQuery(List<Employee> empList, ref string error)

    {

        bool result = false;

        using (SqlConnection connection = new SqlConnection(ConfigurationManager.AppSettings["SQLCon"]))

        {

            connection.Open();

            SqlCommand command = new SqlCommand();

            SqlTransaction transaction = connection.BeginTransaction("EmployeeTran");

            command.Connection = connection;

            command.Transaction = transaction;

            try

            {

                foreach (Employee emp in empList)

                {

                    command.CommandText = string.Format("Insert into Employee (EmployeeID, EmpName) VALUES ({0}, '{1}')",

                        emp.EmployeeID, emp.EmployeeName);

                    command.ExecuteNonQuery();

                }

                transaction.Commit();

                result = true;

            }

            catch (Exception ex)

            {

                //Error occurred

                error = "Error 1:" + ex.Message;

                try

                {

                    transaction.Rollback(); // roll back the transaction.

                }

                catch (Exception ex2)

                {

                    error += "Error 2:" + ex2.Message;

                }

            }

        }

        return result;

    }

• Option 2:

          Pass all the Employee data in XML format to stored procedure and handle insert transaction in stored procedure.