none
Get SQL statement before sumitChanges is called RRS feed

  • Question

  • Hi

    Is there a way to get the sql statement from the Data Context object before actually submitting changes to the DB with the SubmitChanges method?

    Thanks

    Gilad
    Wednesday, October 14, 2009 12:06 PM

Answers

  • Short answer:

    No.

    The L2S datacontext has a public GetCommand method for retrieving the SqlCommand that will be executed for a query, without actually executing the query. Unfortunately it only works with queries (the read-side of things). I am not aware of any similar method that can be used to get the underlying command for insert/update/delete operations.

    You can however get the insert/update/delete statements _after_ the fact, i.e. while the call to SubmitChanges is executing. The DataContext's log property will give you this if you attach a TextReader to it.


    Long answer:

    No.

    The L2S datacontext has a public GetCommand method for retrieving the SqlCommand that will be executed for a query, without actually executing the query. Unfortunately it only works with queries (the read-side of things). I am not aware of any similar method that can be used to get the underlying command for insert/update/delete operations.

    You can however get the insert/update/delete statements _after_ the fact, i.e. while the call to SubmitChanges is executing. The DataContext's log property will give you this if you attach a TextReader to it.

    However, if we set practical, recommended, and safe things aside...  ...in theory it could be possible to extract the Insert/Update/Delete statements from L2S by accessing non-public members on the DataContext and the underlying classes used by the DC. However, this is not recommended nor supported in any way, shape, or form. Private/internal members could change name, signature, protection or something else in a service pack, new framework version, hotfix, etc. Security settings may also prevent the use of reflection. This in turn could break any code that use said private members/classes/interfaces etc.

    Strictly hypothetically - if one were to not heed the advice to not use reflection against private members - something like the following code snippet could potentially work. (Note: untested pseudo-code example following, use at your own peril. Or not at all. :) )

    using System;
    using System.Reflection;
    using System.Linq;
    using System.Linq.Expressions;
    using System.Data.Linq;
    using System.Data;
    
    namespace System.Data.Linq.Extensions
    {
        internal static class DataContextExtensions
        {
            /// <summary>
            /// Retrieve the insert command for a specific entity, from a L2S datacontext
            /// </summary>
            /// <param name="dc">A linq-to-sql datacontext instance</param>
            /// <param name="newEntity">An entity, previously attached to the dc with the .InsertOnSubmit method</param>
            /// <returns>The insert command (SqlCommand)</returns>
            public static IDbCommand GetInsertCommand(this DataContext dc, object newEntity)
            {
                try
                {
                    Type dcType = typeof(DataContext);
    
                    //get hold of the CommonDataServices thing in the DC
                    FieldInfo commonDataServicesField
                        = dcType.GetField("services", BindingFlags.Instance | BindingFlags.NonPublic);
                    object commonDataServices = commonDataServicesField.GetValue(dc);
                    Type commonDataServicesType = commonDataServices.GetType();
    
                    //get hold of the change director
                    PropertyInfo changeDirectorField
                        = commonDataServicesType.GetProperty("ChangeDirector", BindingFlags.Instance | BindingFlags.NonPublic);
                    object changeDirector = changeDirectorField.GetValue(commonDataServices, null);
                    Type changeDirectorType = changeDirector.GetType();
    
                    //get hold of the change tracker
                    PropertyInfo changeTrackerProperty
                        = commonDataServicesType.GetProperty("ChangeTracker", BindingFlags.Instance | BindingFlags.NonPublic);
                    object changeTracker = changeTrackerProperty.GetValue(commonDataServices, null);
                    Type changeTrackerType = changeTracker.GetType();
    
                    //get the tracked object method
                    MethodInfo getTrackedObjectMethod
                        = changeTrackerType.GetMethod("GetTrackedObject", BindingFlags.Instance | BindingFlags.NonPublic);
                    object trackedObject = getTrackedObjectMethod.Invoke(changeTracker, new object[] { newEntity });
    
                    //get the insert command
                    MethodInfo getInsertCommandMethod
                        = changeDirectorType.GetMethod("GetInsertCommand", BindingFlags.Instance | BindingFlags.NonPublic);
                    Expression insertCommand
                        = (Expression)getInsertCommandMethod.Invoke(changeDirector, new object[] { trackedObject });
    
                    //get hold of the provider
                    PropertyInfo providerProperty
                        = dcType.GetProperty("Provider", BindingFlags.Instance | BindingFlags.NonPublic);
                    object provider = providerProperty.GetValue(dc, null);
                    Type providerType = provider.GetType();
    
                    //get the IProvider interface
                    Type iProviderInterface = providerType.GetInterface("IProvider");
    
                    //get hold of the query command
                    MethodInfo getCommandMethod
                        = iProviderInterface.GetMethod("GetCommand", BindingFlags.Instance | BindingFlags.Public);
                    return (IDbCommand)getCommandMethod.Invoke(provider, new object[] { insertCommand });
                }
                catch (Exception ex)
                {
                    //told ya it was a bad idea...
                    throw new SomethingBadHappenedException("I told you so...  mwhahahaha...", ex);
                }
            }
        }
    }
    

    Kristofer - Huagati Systems Co., Ltd. - Cool tools for Linq-to-SQL and Entity Framework: www.huagati.com/dbmltools (VS designer add-in), www.huagati.com/L2SProfiler (query profiler for L2S)
    • Proposed as answer by Yichun_Feng Tuesday, October 20, 2009 2:18 AM
    • Marked as answer by Yichun_Feng Tuesday, October 20, 2009 9:16 AM
    Friday, October 16, 2009 8:58 AM
    Answerer

All replies

  • Short answer:

    No.

    The L2S datacontext has a public GetCommand method for retrieving the SqlCommand that will be executed for a query, without actually executing the query. Unfortunately it only works with queries (the read-side of things). I am not aware of any similar method that can be used to get the underlying command for insert/update/delete operations.

    You can however get the insert/update/delete statements _after_ the fact, i.e. while the call to SubmitChanges is executing. The DataContext's log property will give you this if you attach a TextReader to it.


    Long answer:

    No.

    The L2S datacontext has a public GetCommand method for retrieving the SqlCommand that will be executed for a query, without actually executing the query. Unfortunately it only works with queries (the read-side of things). I am not aware of any similar method that can be used to get the underlying command for insert/update/delete operations.

    You can however get the insert/update/delete statements _after_ the fact, i.e. while the call to SubmitChanges is executing. The DataContext's log property will give you this if you attach a TextReader to it.

    However, if we set practical, recommended, and safe things aside...  ...in theory it could be possible to extract the Insert/Update/Delete statements from L2S by accessing non-public members on the DataContext and the underlying classes used by the DC. However, this is not recommended nor supported in any way, shape, or form. Private/internal members could change name, signature, protection or something else in a service pack, new framework version, hotfix, etc. Security settings may also prevent the use of reflection. This in turn could break any code that use said private members/classes/interfaces etc.

    Strictly hypothetically - if one were to not heed the advice to not use reflection against private members - something like the following code snippet could potentially work. (Note: untested pseudo-code example following, use at your own peril. Or not at all. :) )

    using System;
    using System.Reflection;
    using System.Linq;
    using System.Linq.Expressions;
    using System.Data.Linq;
    using System.Data;
    
    namespace System.Data.Linq.Extensions
    {
        internal static class DataContextExtensions
        {
            /// <summary>
            /// Retrieve the insert command for a specific entity, from a L2S datacontext
            /// </summary>
            /// <param name="dc">A linq-to-sql datacontext instance</param>
            /// <param name="newEntity">An entity, previously attached to the dc with the .InsertOnSubmit method</param>
            /// <returns>The insert command (SqlCommand)</returns>
            public static IDbCommand GetInsertCommand(this DataContext dc, object newEntity)
            {
                try
                {
                    Type dcType = typeof(DataContext);
    
                    //get hold of the CommonDataServices thing in the DC
                    FieldInfo commonDataServicesField
                        = dcType.GetField("services", BindingFlags.Instance | BindingFlags.NonPublic);
                    object commonDataServices = commonDataServicesField.GetValue(dc);
                    Type commonDataServicesType = commonDataServices.GetType();
    
                    //get hold of the change director
                    PropertyInfo changeDirectorField
                        = commonDataServicesType.GetProperty("ChangeDirector", BindingFlags.Instance | BindingFlags.NonPublic);
                    object changeDirector = changeDirectorField.GetValue(commonDataServices, null);
                    Type changeDirectorType = changeDirector.GetType();
    
                    //get hold of the change tracker
                    PropertyInfo changeTrackerProperty
                        = commonDataServicesType.GetProperty("ChangeTracker", BindingFlags.Instance | BindingFlags.NonPublic);
                    object changeTracker = changeTrackerProperty.GetValue(commonDataServices, null);
                    Type changeTrackerType = changeTracker.GetType();
    
                    //get the tracked object method
                    MethodInfo getTrackedObjectMethod
                        = changeTrackerType.GetMethod("GetTrackedObject", BindingFlags.Instance | BindingFlags.NonPublic);
                    object trackedObject = getTrackedObjectMethod.Invoke(changeTracker, new object[] { newEntity });
    
                    //get the insert command
                    MethodInfo getInsertCommandMethod
                        = changeDirectorType.GetMethod("GetInsertCommand", BindingFlags.Instance | BindingFlags.NonPublic);
                    Expression insertCommand
                        = (Expression)getInsertCommandMethod.Invoke(changeDirector, new object[] { trackedObject });
    
                    //get hold of the provider
                    PropertyInfo providerProperty
                        = dcType.GetProperty("Provider", BindingFlags.Instance | BindingFlags.NonPublic);
                    object provider = providerProperty.GetValue(dc, null);
                    Type providerType = provider.GetType();
    
                    //get the IProvider interface
                    Type iProviderInterface = providerType.GetInterface("IProvider");
    
                    //get hold of the query command
                    MethodInfo getCommandMethod
                        = iProviderInterface.GetMethod("GetCommand", BindingFlags.Instance | BindingFlags.Public);
                    return (IDbCommand)getCommandMethod.Invoke(provider, new object[] { insertCommand });
                }
                catch (Exception ex)
                {
                    //told ya it was a bad idea...
                    throw new SomethingBadHappenedException("I told you so...  mwhahahaha...", ex);
                }
            }
        }
    }
    

    Kristofer - Huagati Systems Co., Ltd. - Cool tools for Linq-to-SQL and Entity Framework: www.huagati.com/dbmltools (VS designer add-in), www.huagati.com/L2SProfiler (query profiler for L2S)
    • Proposed as answer by Yichun_Feng Tuesday, October 20, 2009 2:18 AM
    • Marked as answer by Yichun_Feng Tuesday, October 20, 2009 9:16 AM
    Friday, October 16, 2009 8:58 AM
    Answerer
  • Thanks, very interesting stuff.

    I will stay with the ChangeSet querying before the SubmitChanges and loging the sql statement in the log in the exception handling code.

    Gilad
    Saturday, October 24, 2009 4:27 AM