[Solved] Executing Commands

[Jonathan]

How are interesting in switching to OpenAccess from the Entity Framework, but our application needs to be able to execute raw SQL commands... including insert, update and delete commands.

1) Can OpenAccess execute SQL commands... similar to EF's ObjectContext.ExecuteStoreCommand(...) method?

2) Can the commands be executed along with the auto-generated insert/update/delete code?

[Petko_I]

Hello Jonathan,

OpenAccess supports execution of direct SQL select statements and stored procedures. You cannot directly call an insert, update or delete statement but you can encapsulate such commands in a stored procedure. This is done because the state of the context entities cannot be tracked when custom SQL commands are executed.
OpenAccess supports two approaches - classic wizards and visual designer. You should choose to work with one of the two approaches as they cannot be mixed. The visual designer is where our main effort is concentrated on and we would strongly recommend using it. We are elaborating on this as the direct SQL select statement execution is available with the classic scope API. When you work with the visual designer approach you can gain access to a scope by exposing it through a method in the OpenAccessContext derived class. You can take advantage of the partial class definition feature so that the automatic code generation does not override your changes. So, for example, if your domain context class is called NorthwindEntityDiagrams, the scope retrieval is done in the following way:

public partial class NorthwindEntityDiagrams : OpenAccessContext
{
    public IObjectScope GetInternalScope()
    {
        return this.GetScope();
    }
}

Here is a link to an online help topic that provides details on the SQL statements invocation. Also, this topic shows how to execute stored procedures through the OpenAccessContext. You can encapsulate custom commands in stored procedures.

Can you tell us a concrete scenario you need to accomplish so that we may give you a better idea as to how you can achieve it with OpenAccess? You can specify that you want to map CUD operations to stored procedures and modify the stored procedures so that custom logic is plugged in. The mapped procedure is invoked when Add, Delete and Update methods are called on the part of the OpenAccess context. Bear in mind that with the visual designer currently you can map CUD procedures for MS SQL Server.

Do not hesitate to contact us, should you need further information.

Kind regards,
Petko_I
the Telerik team