Additional parameter in Stored Procedures for CUD actions

10 posts, 0 answers
  1. Hans
    Hans avatar
    7 posts
    Member since:
    Sep 2012

    Posted 21 Sep 2012 Link to this post

    We are busy to select an ORM tool for our business software. OpenAccess is one of the two remaining ORMs.

    For our business Entity model we want to use Stored procedures for the CUD actions to map to our database model with SQL Anywhere from Sybase. We map the Retrieve action with a database View. We have identified some business rules that should reject some user roles having write access to our database. If we put that business rule in the Stored Procedure we can have less C# code and better performance due to the Stored Procedure. In that case we need a user name or user ID ‘injection’ as an additional parameter in our Stored Procedure.

     

    I could not find this functionality described somewhere. Is there someone who can answer my questions 1-3 ?

     

    1. Is it possible to add additional (user ID) parameters as an input for our Stored Procedures in OpenAccess?
    2. How can we do that?
    3. How can we pass the additional parameter user ID value to the Stored Procedure with OpenAccess?

     

    Thank you for the answers in advance.

  2. PetarP
    Admin
    PetarP avatar
    754 posts

    Posted 25 Sep 2012 Link to this post

    Hi Hans,

     The answer to your question pretty much depends on the way you would like to use your stored procedures. If you would like to map them so that they automatically fire for your objects during CUD operations as described here then I am afraid I have bad news as this is currently not supported. The only supported procedures there are ones created by OpenAccess itself. They are limited to a certain amount of parameters that OpenAccess expects and cannot be extended further.

    If you however want to map those procedures to CLR methods that can be called from your code then this is perfectly possible. Examples on how to do that can be found in our online documentation here.

    Greetings,
    Petar
    the Telerik team
    Follow @OpenAccessORM Twitter channel to be the first one to get the latest updates on new releases, tips and tricks and sneak peeks at our product labs!
  3. DevCraft banner
  4. Hans
    Hans avatar
    7 posts
    Member since:
    Sep 2012

    Posted 27 Sep 2012 Link to this post

    Hi Petar,

    I am surprised that the normal CUD actions in OpenAccess can’t be performed by our own Stored Procedures because Entity Framework can. And is also able to select and map all its properties (in a dialog). In the comparison between Entity Framework and OpenAccess (at the Telerik OpenAccess site) you find there Full Stored Procedure support for both tools. OpenAccess is able to import Entity Framework Domain models. Also the Stored Procedure CUD functionality as I described from above? 

    We want to work with a database first Domain model and an OData Data Provider for an OData Data Service. With that we have almost no plumbing code  at the server and client side (and the Data service to the database as simple as possible). 

    The normal CUD option of the tool (generated stored procedures) is not really an option. We can change the generated stored procedure if we want, but those can be overwritten if for example one property is removed and  the stored procedures are -regenerated- from the domain model. Then all changes in the stored procedures are removed. That is not maintainable. 

    The other and better solution you suggested was to map our stored procedures to Domain methods. We still want to have the same CUD functionality as we discussed for the many Entities we have in the domain model. We could have an override for the Add, Delete and SaveChanges method in partial classes (in our own class files). Is that what you suggested? The three override methods should have the corresponding stored procedure called for all the CUD actions. Is that a correct interpretation?

    Hans
  5. Hans
    Hans avatar
    7 posts
    Member since:
    Sep 2012

    Posted 27 Sep 2012 Link to this post

    Hi Petar,

    I am surprised that the normal CUD actions in OpenAccess can’t be performed by our own Stored Procedures because Entity Framework can. And is also able to select and map all its properties (in a dialog). In the comparison between Entity Framework and OpenAccess (at the Telerik OpenAccess site) you find there Full Stored Procedure support for both tools. OpenAccess is able to import Entity Framework Domain models. Also the Stored Procedure CUD functionality as I described from above? 

    We want to work with a database first Domain model and an OData Data Provider for an OData Data Service. With that we have almost no plumbing code  at the server and client side (and the Data service to the database as simple as possible). 

    The normal CUD option of the tool (generated stored procedures) is not really an option. We can change the generated stored procedure if we want, but those can be overwritten if for example one property is removed and  the stored procedures are -regenerated- from the domain model. Then all changes in the stored procedures are removed. That is not maintainable. 

    The other and better solution you suggested was to map our stored procedures to Domain methods. We still want to have the same CUD functionality as we discussed for the many Entities we have in the domain model. We could have an override for the Add, Delete and SaveChanges method in partial classes (in our own class files). Is that what you suggested? The three override methods should have the corresponding stored procedure called for all the CUD actions. Is that a correct interpretation?

    Hans
  6. PetarP
    Admin
    PetarP avatar
    754 posts

    Posted 01 Oct 2012 Link to this post

    Hi Hans,

     Yes. Currently that is the only workaround we are able to suggest you. Having those methods in a partial class would allow you to call the correct procedure when addressing order.Delete() for example.
    We are sorry for the inconvenience this might be causing you.

    Kind regards,
    Petar
    the Telerik team
    Follow @OpenAccessORM Twitter channel to be the first one to get the latest updates on new releases, tips and tricks and sneak peeks at our product labs!
  7. Hans
    Hans avatar
    7 posts
    Member since:
    Sep 2012

    Posted 04 Oct 2012 Link to this post

    Hi Petar,

    Thanks so far. I am now able to store my new created objects to the database with my own Stored Procedure. And I can use my own (additional) parameters for that.

    But I still have one big problem. I have to change the ObjectState to ObjectState.Clean because the ORM does not know that the object is already stored in the database. If I call the base.SaveChanges the ORM starts to store the "new" created object again! Without an exception it will be saved twice.

    Greetings,
    Hans
  8. Hans
    Hans avatar
    7 posts
    Member since:
    Sep 2012

    Posted 05 Oct 2012 Link to this post

    Hi Petar,

    Thanks so far. I am now able to store my new created objects to the database with my own Stored Procedure. And I can and am able to use my own (and additional) parameters for that.

    But I still have one problem. I have to change the ObjectState from ObjectState
    .New to ObjectState.Clean (I think?). Because the ORM does not know that the object is already stored in the database. If I call the base.SaveChanges (see part of code in own derived context) the ORM starts to store the "new" created object again! Without an unique constrain exception it will be saved twice.

    1. How can I change the ObjectState?
    2. Should I have to use the ObjectState Clean for by the Stored procedures insert and update objects?

    3. Should I have to use the ObjectState Deleted for the by the Stored procedures deleted objects?

    Greetings,
    Hans




     

     

     

    public class MyEntityContext : EntitiesContext

     

    {

     

     

        new public void SaveChanges()

     

        {

     

     

     

            if (HasChanges)

     

            {

     

     

                IList<object> insertObjects = GetChanges().GetInserts<object>();

     

     

     

                IList<object> updateObjects = GetChanges().GetUpdates<object>();

     

     

     

                IList<object> deleteObjects = GetChanges().GetDeletes<object>();

     

     

     

                foreach (IDatabaseInterface a in insertObjects)

     

                {

                    a.Insert(a, Connection);

                    ObjectState state = PersistenceState.GetState(a);

     

     

                    Console.WriteLine(state.ToString());  // <--- New 

     

                    state = ObjectState.Clean;

     

    // <---- This is needed ???

     

     

     

     

     

                }

     

     

                foreach (IDatabaseInterface b in updateObjects)

     

                {

                    b.Update(b, Connection);

                }

     

     

                foreach (IDatabaseInterface c in deleteObjects)

     

                {

                    c.Delete(c, Connection);

                }

            }

     

     

            base.SaveChanges();

     

        }

    }

     

  9. DJ
    DJ avatar
    1 posts
    Member since:
    May 2011

    Posted 05 Oct 2012 Link to this post

    Hi Petar,

    Thanks so far. I am now able to store my new created objects to the database with my own Stored Procedure. And I can and am able to use my own (and additional) parameters for that. In this attached example the Stored Procedure has only 3 parameters.

    But I still have one problem. I have to change the ObjectState from ObjectState.New to ObjectState.Clean (I think?). Because the ORM does not know that the object is already stored in the database. If I call the base.SaveChanges (see part of code in my own derived context) the ORM starts to store the "new" created object again! Without an unique constrain exception it will be saved twice.
    1. How can I change the ObjectState?
    2. Should I have to use the ObjectState Clean for by the Stored procedures insert and update objects?
    3. Should I have to use the ObjectState Deleted for the by the Stored procedures deleted objects?

    Note: It was still not possible to post this issue with my own Telerik account, so thats the reason it looks like someone else posts it.

    Greetings,
    Hans

     




     ----------------------------------- code example I have used -------------------------------------------

    public interface IDatabaseInterface
    {
        void Insert(object inObject, OAConnection inConnection);
        void Update(object inObject, OAConnection inConnection);
        void Delete(object inObject, OAConnection inConnection);
    }

    public partial class Project : IDatabaseInterface
    {
        public void Insert(object inObject, OAConnection inConnection)
        {

            using (OAConnection dbConnection = inConnection)
            {

                OAParameter projectNumberParameter = new OAParameter();
                projectNumberParameter.DbType = System.Data.DbType.String;
                projectNumberParameter.ParameterName = "@inProjectNumber";
                projectNumberParameter.Value = Project_number;

                OAParameter nameParameter = new OAParameter();
                nameParameter.DbType = System.Data.DbType.String;
                nameParameter.ParameterName = "@inName";
                nameParameter.Value = Name;

                OAParameter statusParameter = new OAParameter();
                statusParameter.DbType = System.Data.DbType.Int16;
                statusParameter.ParameterName = "@inStatus";
                statusParameter.Value = 1;

                using (OACommand spCommand = dbConnection.CreateCommand())
                {
                    spCommand.CommandText = "Insertproject";
                    spCommand.CommandType = System.Data.CommandType.StoredProcedure;
                    spCommand.Parameters.Add(projectNumberParameter);
                    spCommand.Parameters.Add(nameParameter);
                    spCommand.Parameters.Add(statusParameter);

                    Id = (Int32)spCommand.ExecuteScalar();  // set the returned Id from SP
                }

            }
        }

        public void Update(object inObject, OAConnection inConnection)
        {
            //TODO
        }

        public void Delete(object inObject, OAConnection inConnection)
        {
            //TODO
        }

    }

     

     

    public class MyEntityContext : EntitiesContext
    {
        new public void SaveChanges()
        {
            if (HasChanges)
            {
                IList<object> insertObjects = GetChanges().GetInserts<object>();
                IList<object> updateObjects = GetChanges().GetUpdates<object>();
                IList<object> deleteObjects = GetChanges().GetDeletes<object>();
                foreach (IDatabaseInterface a in insertObjects)
                {
                    a.Insert(a, Connection);
                    ObjectState state = PersistenceState.GetState(a);
                    Console.WriteLine(state.ToString());  // <--- New state
                    state = ObjectState.Clean; // <---- This is needed ???
                }
                foreach (IDatabaseInterface b in updateObjects)
                {
                    b.Update(b, Connection);
                }
                foreach (IDatabaseInterface c in deleteObjects)
                {
                    c.Delete(c, Connection);
                }
            }
            base.SaveChanges();
        }
    }

    // Main application
    namespace TestApplication
    {
        class Program
        {
            static void Main(string[] args)
            {
                using (MyEntityContext mycontext = new MyEntityContext())
                {
               

                    Project project = new Project();
                    project.Name = "My Name is Bond, James Bond";
                    project.Project_status_id = 1;
                    project.Project_number = "123";

                    mycontext.Add(project);

                    mycontext.SaveChanges();
                }
            }
        }
    }

  10. Hans
    Hans avatar
    7 posts
    Member since:
    Sep 2012

    Posted 05 Oct 2012 Link to this post

    See next post...
  11. PetarP
    Admin
    PetarP avatar
    754 posts

    Posted 08 Oct 2012 Link to this post

    Hello DJ,

     The problem here is that you are indeed adding the object twice to the context. Once you are doing so by using the stored procedure call and the other time is when you call the context.Add method.
    The best and easiest way to avoid that would be to modify your code in the following way:

    // Main application
    namespace TestApplication
    {
        class Program
        {
            static void Main(string[] args)
            {
                using (MyEntityContext mycontext = new MyEntityContext())
                {
                
                    Project project = new Project();
                    project.Name = "My Name is Bond, James Bond";
                    project.Project_status_id = 1;
                    project.Project_number = "123";
                    project.Insert();
                    mycontext.SaveChanges();
                }
            }
        }
    }
    Note that when you do that you will not need to override the SaveChanges and you can use the default implementation.

    Greetings,
    Petar
    the Telerik team
    Follow @OpenAccessORM Twitter channel to be the first one to get the latest updates on new releases, tips and tricks and sneak peeks at our product labs!
Back to Top
DevCraft banner