How do you access data that has been inserted using a ADO.NET transaction?

Question

I'm trying to get the data that has been successfully input into the database via ADO.NET transaction.

Once you've called trans.Commit() there doesn't seem to be a way of getting back the data that has been committed since all identity columns that were created during the transaction are 'virtual' since it is an offline dataset until commit

Many thanks

Answer 1

You may just need to reselect the data.

Answer 2

Ahh, the problem is, I can't do a reselect as I don't have anything unique to select on other than the identity of the data inserted as part of the transaction.

I can't get the last entered item as this is a multiuser system

Code Sample from a book, not the code in question, but good enough to illustrate what I need:

using System.Data;
using System.Data.SqlClient;

namespace DataAdapterTransaction
{
    class Program
    {
        private static string sqlConnectString = "Data Source=(local);" +
            "Integrated security=SSPI;Initial Catalog=AdoDotNet35Cookbook;";

        private static string sqlSelect = "SELECT * FROM DataAdapterTransaction";

        static void Main(string[] args)
        {
            object[,] o1 = {{ "1", "field 1.1", "field 2.1" },
                          { "2", "field 1.2", "field 2.2" }};
            InsertRecords(o1);

            object[,] o2 = {{ "3", "field 1.3", "field 2.3" },
                           { null, "field 1.4", "field 2.4" }};
            InsertRecords(o2);

            // Retrieve and output the contents of the table
            SqlDataAdapter daRead = new SqlDataAdapter(sqlSelect, sqlConnectString);
            DataTable dtRead = new DataTable( );
            daRead.Fill(dtRead);
            Console.WriteLine("---TABLE DataAdapterTransaction---");
            foreach (DataRow row in dtRead.Rows)
                Console.WriteLine("Id = {0}\tField1 = {1}\tField2 = {2}",
                    row["Id"], row["Field1"], row["Field2"]);

            Console.WriteLine("\nPress any key to continue.");
            Console.ReadKey( );
        }

        static void InsertRecords(object[,] o)
        {
            DataTable dt = new DataTable( );
            SqlTransaction tran;

            SqlConnection connection = new SqlConnection(sqlConnectString);

            // Create a DataAdapter
            SqlDataAdapter da = new SqlDataAdapter(sqlSelect, connection);
            // Stop updating when an error is encountered for roll back.
            da.ContinueUpdateOnError = false;
            // Create CommandBuilder and generate updating logic.
            SqlCommandBuilder cb = new SqlCommandBuilder(da);
            // Create and fill a DataTable with schema and data
            da.Fill(dt);
            // Open the connection
            connection.Open( );
            // Begin a new transaction and assign it to the DataAdapter
            tran = connection.BeginTransaction( );
            da.SelectCommand.Transaction = tran;

            // Add two rows that will succeed update
            for (int i = 0; i <= o.GetUpperBound(0); i++)
            {
                dt.Rows.Add(new object[] { o[i, 0], o[i, 1], o[i, 2] });
                Console.WriteLine(
                    "=> Row with [Id = {0}] added to DataTable.", o[i, 0]);
            }

            Console.WriteLine("=> Updating data source using DataAdapter.");
            try
            {
                da.Update(dt);
                tran.Commit( );

                Console.WriteLine("\nTRANSACTION COMMIT.\n");
            }
            catch (Exception ex)
            {
                tran.Rollback( );
                Console.WriteLine("\nTRANSACTION ROLLBACK.\n{0}\n", ex.Message);
            }
            finally
            {
                connection.Close( );
            }
        }
    }
}

Okay, so what i'm after is just after the transaction commit, I want to get the (scope) identity of the the last inserted row.

My application is successful in updating three dataadapters as part of the transaction, however I am having dificulty looking at the final committed data. I can do a select of the table and see it in there, but that really isn't good enough for production code.

SC

Answer 3

The Books Online says that you should call a Fill again to bring the update your Dataset:

http://msdn.microsoft.com/en-us/library/33y2221y(VS.71).aspx

I generally set my Insert and Update commands so that they they return back a valid DataRow for my DataTable and control the update of the rows in the application that way.

Answer 4

Right so I should do:

// update datatable
da.Update(dt);
// commit updates
tran.Commit( );
// get the updated datatable
da.Fill(dt);

I assume all the identity colums will be updated.

I'll give it a go

SC

Answer 5

I understand you're using identity columns, but is there any natural key in the data you can use to reselect?

(that then raises the question of 'why use identities' but that's a whole other subject...)

Answer 6

Unfortunately no, I cannot reselect using a natural key... I'm stuck with identities... after 8 hours of head banging I contemplated adding a guid field so I could get it to work, but decided that it is against my principles to give up!

SC

Answer 7

This MSDN article describes how to get back identity values when calling the Update method of a DataAdapter.