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

Additional parameter in Stored Procedures for CUD actions

9 Answers 136 Views
Databases and Data Types
This is a migrated thread and some comments may be shown as answers.
This question is locked. New answers and comments are not allowed.
Hans
Top achievements
Rank 1
Hans asked on 21 Sep 2012, 10:16 AM

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.

9 Answers, 1 is accepted

Sort by
0
PetarP
Telerik team
answered on 25 Sep 2012, 09:35 AM
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!
0
Hans
Top achievements
Rank 1
answered on 27 Sep 2012, 06:31 AM
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
0
Hans
Top achievements
Rank 1
answered on 27 Sep 2012, 06:40 AM
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
0
PetarP
Telerik team
answered on 01 Oct 2012, 03:53 PM
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!
0
Hans
Top achievements
Rank 1
answered on 04 Oct 2012, 05:36 PM
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
0
Hans
Top achievements
Rank 1
answered on 05 Oct 2012, 06:47 AM

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();

 

    }

}

 

0
DJ
Top achievements
Rank 1
answered on 05 Oct 2012, 08:32 AM

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();
            }
        }
    }
}

0
Hans
Top achievements
Rank 1
answered on 05 Oct 2012, 09:58 AM
See next post...
0
PetarP
Telerik team
answered on 08 Oct 2012, 04:09 PM
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!
Tags
Databases and Data Types
Asked by
Hans
Top achievements
Rank 1
Answers by
PetarP
Telerik team
Hans
Top achievements
Rank 1
DJ
Top achievements
Rank 1
Share this question
or