This is a migrated thread and some comments may be shown as answers.

Execute custom procedure when saving changes, within the same transaction

4 Answers 80 Views
Data Access Free Edition
This is a migrated thread and some comments may be shown as answers.
This question is locked. New answers and comments are not allowed.
Alex
Top achievements
Rank 1
Alex asked on 24 Apr 2013, 02:34 PM
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.

4 Answers, 1 is accepted

Sort by
0
Doroteya
Telerik team
answered on 29 Apr 2013, 11:40 AM
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 >>
0
Alex
Top achievements
Rank 1
answered on 30 Apr 2013, 03:52 PM
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).
0
Accepted
Doroteya
Telerik team
answered on 02 May 2013, 11:56 AM
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 >>
0
Alex
Top achievements
Rank 1
answered on 03 May 2013, 03:09 PM
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.
Tags
Data Access Free Edition
Asked by
Alex
Top achievements
Rank 1
Answers by
Doroteya
Telerik team
Alex
Top achievements
Rank 1
Share this question
or