Execute custom procedure when saving changes, within the same transaction

5 posts, 1 answers
  1. Alex
    Alex avatar
    7 posts
    Member since:
    Mar 2013

    Posted 24 Apr 2013 Link to this post

    Hello!

    I am currently evaluating OpenAccess for use for new module within an application that currently does not use any ORM. My version is Q1 2013 (version 2013.1.219.3), as stated by Samples Kit.

    As the most of the application logic is located within stored procedures, I am trying to accomplish the following when persisting data:

    BEGIN TRAN
    EXEC BeforeSaveChangesProcedure
    {Saving changes}
    EXEC AfterSaveChangesProcedure
    COMMIT

    (i.e. executing custom procedures along with data persistence in the same transaction).

    I saw that SaveChanges begins and ends a transaction by itself and I do not know if OpenAccess allows this scenario. To easily understand the issue, I have created a small Windows Forms App - I will paste the relevant code:

        public partial class MainForm : Form
        {
            Document theDocument = null;
            DocumentModel context = new DocumentModel();

            private const string connectionId = "TestConnection";

            public MainForm()
            {
                InitializeComponent();

                LoadData();
                BindControls();
            }

            private void LoadData()
            {
                int documentId = 1;
                theDocument = context.Documents.FirstOrDefault<Document>(ent => ent.DocumentId == documentId);
            }

            private void BindControls()
            {
                txtDocumentNumber.DataBindings.Clear();
                txtDocumentNumber.DataBindings.Add(new Binding("Text", theDocument, "DocumentNumber"));
                dtDocumentDate.DataBindings.Clear();
                dtDocumentDate.DataBindings.Add(new Binding("Value", theDocument, "DocumentDate"));
                txtDocumentAmount.DataBindings.Clear();
                txtDocumentAmount.DataBindings.Add(new Binding("Text", theDocument, "DocumentValue", true));
            }

            private void btnSaveData_Click(object sender, EventArgs e)
            {
                try
                {
                    // context.Connection.BeginTransaction();       // does not work for bound connections
                    context.SaveChanges();
                }
                catch (Exception)
                {
                }
            }
        }

    Any idea how this can be done? Thank you,

    Alex D.
  2. Doroteya
    Admin
    Doroteya avatar
    502 posts

    Posted 29 Apr 2013 Link to this post

    Hello Alex,

    The scenario you describe is supported by Telerik OpenAccess ORM thanks to its Low Level (ADO) API. Basically, it offers means to execute all kinds of SQL statements and queries from your code using either syntax similar to ADO.NET (connections, commands, parameters) or our context approach.

    The actual implementation of the mentioned scenario would vary depending on the stored procedures but the next code snippet might be useful in your evaluation. It is based on the NorthwindOA database shipped with OpenAccess Samples Kit:
    using (EntitiesModel dbContext = new EntitiesModel())
    {
        OAParameter categoryIdParam = new OAParameter();
        categoryIdParam.DbType = DbType.Int32;
        categoryIdParam.Direction = ParameterDirection.Input;
        categoryIdParam.ParameterName = @"categoryID";
        categoryIdParam.Value = 1;
     
        Category someCategory = dbContext.ExecuteQuery<Category>("[ReadCategory]",
                   CommandType.StoredProcedure, categoryIdParam).First();
     
        OAParameter parameterCategoryID = new OAParameter();
        parameterCategoryID.ParameterName = "CategoryID";
        parameterCategoryID.Value = someCategory.CategoryID;
        parameterCategoryID.DbType = DbType.Int32;
     
        OAParameter parameterCategoryName = new OAParameter();
        parameterCategoryName.ParameterName = "CategoryName";
        parameterCategoryName.Value = "New Cat Name";
        parameterCategoryName.DbType = DbType.String;
     
        OAParameter parameterDescription = new OAParameter();
        parameterDescription.ParameterName = "Description";
        parameterDescription.Value = someCategory.Description;
        parameterDescription.DbType = DbType.String;
     
        OAParameter parameterPicture = new OAParameter();
        parameterPicture.ParameterName = "Picture";
        parameterPicture.Value = someCategory.Picture;
     
        OAParameter parameterOLDCategoryName = new OAParameter();
        parameterOLDCategoryName.ParameterName = "OLD_CategoryName";
        parameterOLDCategoryName.Value = someCategory.CategoryName;
        parameterOLDCategoryName.DbType = DbType.String;
     
        dbContext.ExecuteNonQuery("[UpdateCategory]", CommandType.StoredProcedure,
                      parameterCategoryID, parameterCategoryName,
                      parameterDescription, parameterPicture,
                      parameterOLDCategoryName);
     
        // Execute the AfterChangesProcedure
        // using the same pattern
     
        dbContext.SaveChanges();
    }
    More on the subject you can find in the Stored Procedures and Functions with OpenAccess example in Samples Kit.

    If you have additional questions, do not hesitate to get back to us.

     

    All the best,
    Doroteya
    the Telerik team
    Using Encrypted Connection Strings with Telerik OpenAccess ORM. Read our latest blog article >>
  3. DevCraft banner
  4. Alex
    Alex avatar
    7 posts
    Member since:
    Mar 2013

    Posted 30 Apr 2013 Link to this post

    Based on your example I have tried the following:

                    using (DocumentModel context = new DocumentModel())
                    {
                        int documentId = 1;
                        theDocument = context.Documents.FirstOrDefault<Document>(ent => ent.DocumentId == documentId);

                        theDocument.DocumentDate = DateTime.Now.AddDays(3); // put something to force save changes to to something

                        context.ExecuteQuery<object>("uspBeforeSaveTest", CommandType.StoredProcedure,
                            new OAParameter[] { new OAParameter("SomeString", "etc") });

                        context.SaveChanges();

                        context.ExecuteQuery<object>("uspAfterSaveTest", CommandType.StoredProcedure,
                            new OAParameter[] { new OAParameter("SomeString", "etc") });
                    }

    and the profiler output is something like (simplified to reduce size):

    begin tran
    exec uspBeforeSaveTest ... update Document ...
    commit

    begin tran
    exec uspAfterSaveTest ...
    commit

    So, I can execute a procedure before save in transaction, but not after.

    I think this behavior is by design and I think a solution would be to allow user to handle the transaction (to manually begin, commit or rollback it). However OpenAccess does not seem to allow this (methods throw exceptions).
  5. Answer
    Doroteya
    Admin
    Doroteya avatar
    502 posts

    Posted 02 May 2013 Link to this post

    Hello Alex,

    Indeed, that would be the expected outcome from the posted code snippet. You can change it in two ways, depending on the means for persisting the data (a stored procedure or the context) .

    In the first case, if you use a stored procedure, all you need is to place the context.SaveChanges() call after the call to the uspAfterSaveTest procedure. The point here is that OpenAccess executes stored procedures without an explicit commitment of the changes they perform (additional information is available in this knowledge base article and in this section of our documentation). Here is an example:
    using (DocumentModel context = new DocumentModel())
    {
        // Data retrieval and possible modifications
     
        context.ExecuteQuery<object>("uspBeforeSaveTest", CommandType.StoredProcedure,
             new OAParameter[] { new OAParameter("SomeString", "etc") });
     
        // Persisting the changes via a stored procedure
     
        context.ExecuteQuery<object>("uspAfterSaveTest", CommandType.StoredProcedure,
             new OAParameter[] { new OAParameter("SomeString", "etc") });
     
        context.SaveChanges();
    }

    In the second scenario where you use the context in order to persist the changes in the database, you can use the context.FlushChanges() method after the call to the uspBeforeSaveTest procedure and SaveChanges after uspAfterSaveTest. FlushChanges caries the data to the database and leaves the transaction opened. The workflow would be as follows:
    using (DocumentModel context = new DocumentModel())
    {
        // Data retrieval and possible modifications
     
        context.ExecuteQuery<object>("uspBeforeSaveTest", CommandType.StoredProcedure,
             new OAParameter[] { new OAParameter("SomeString", "etc") });
     
        context.FlushChanges();
     
        context.ExecuteQuery<object>("uspAfterSaveTest", CommandType.StoredProcedure,
             new OAParameter[] { new OAParameter("SomeString", "etc") });
     
        context.SaveChanges();
    }

    Regarding the control over the transactions, currently this feature is not in our backlog and I advise you to post it on our Ideas & Feedback Portal. There the developers who use OpenAccess can vote for it and once it gathers the approval of the community, we are going to schedule it for implementation.

    Let us know if the suggested solution is not feasible to you or if additional questions come up.


    Kind regards,
    Doroteya
    the Telerik team
    OpenAccess ORM Q1'13 SP1 update is out now. Take a glimpse at the highlights >>
  6. Alex
    Alex avatar
    7 posts
    Member since:
    Mar 2013

    Posted 03 May 2013 Link to this post

    The second solution works very well for me, as the usual scenario is to let the context save changes and to run a procedure to make custom validations (which may rollback the entire transaction if something is wrong). So, manually controlling the transaction is not required yet, at least not for me :)

    Thank you.
Back to Top
DevCraft banner