This project has moved and is read-only. For the latest updates, please go here.

Is Inline SQL implemented

Mar 21, 2014 at 12:49 PM
Hi,

Although I prefer stored procedures, as this adds an extra layer that is only responsible for managing data, I also require a local database when users are not connected. I've been using SQLCE (compact edition) which does not support stored procedures. Firebird and SQLite are other potential possibilities.

The "Generate Inline SQL Queries" in the Project Properties>>Generation>>Database tab is disabled. Does this simply mean that Inline SQL is not implemented yet?

If this is the case, I may be able to help with this project.

Thank you,

Richard
Mar 21, 2014 at 7:15 PM
Ok.

I checked out the project using svn and found my answer. Inline queries are not implemented yet.

There's not a whole lot of difference between what I manually coded (inline) and the generated code (stored procedure).

Using CSLAGenFork
using (var ctx = ConnectionManager<SqlConnection>.GetManager("GopherDb"))
{
    using (var cmd = new SqlCommand("dbo.GetAnimalEdit", ctx.Connection))
    {
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@AnimalId", animalId).DbType = DbType.Guid;
This is the code that I manually wrote
using (var command = new SqlCeCommand()) { 
        command.CommandType = CommandType.Text;
        command.CommandText ="SELECT a.AnimalId, a.AnimalRelatedId, ... FROM Animals a, AnimalTags at, Tags t ";
There are 2 differences
1.The CommandType is Text
2.The CommandText has the inline query.

Somewhere I need to check if the GenerateInlineQueries property of the GenerationParameters class is true. If true use inline else use stored procedures. Should I be checking for value of GenerateInlineQueries in one of the templates?

I would appreciate any insight.

Richard
Mar 22, 2014 at 5:36 PM
Edited Mar 22, 2014 at 9:17 PM
Hi Richard,

As you pointed out, this feature isn't implemented yet. The only support for this feature is the option itself. The code generation process ignores the GenerateInlineQueries setting.

This feature will override the generation process somehow and some manual settings/code needs to be stored somewhere. Up to now I always thought of solving all the issues CslaGenFork's side. But on a second look, it might be far easier to solve it with predefined methods that you must write on the extend file.

So lets look at the issues involved.
1) Where do I keep the inline query?
2) How to I address the need to pass parameters back and forth to the inline query?
3) What parts of the generated code should be kept?

Let's discuss this new approach.
The inline query doesn't need to be kept CslaGenFork side. The inline query can be written by a method on the extended file. The method would be like this
private string GetDocListInlineQuery()
{
    return "SELECT TOP 10000 * FROM Docs WHERE DocStatusID = @DocStatusID";
}
Note "GetDocList" is the name you specify for the Store Procedure. So there is no need for any change on the UI side to store the name of inline query method name.

This solution take advantage of the other parts of the generated code like Command.Timeout settings, parameter passing, etc In fact it (almost) solves all issues.

Now we can improve on this solution (and solve ALL the issues) by passing to this method all parameters received by the calling method, like this
private string GetDocListInlineQuery(DocListFilteredCriteria crit)
{
    if (crit.DocID != null)
        return "SELECT TOP 10000 * FROM Docs WHERE DocID = @DocID";

    var query = "SELECT TOP 10000 * FROM Docs";
    var whereAdded = false;

    if (crit.DocClassID != null)
    {
        whereAdded = true;
        query += " WHERE" + Environment.NewLine;
        query += "DocClassID = @DocClassID";
    }
    
    ...

    if (!string.IsNullOrEmpty(crit.Subject))
    {
        AddClause(ref query, ref whereAdded);
        crit.Subject = crit.Subject + "%";
        query += "Subject = LIKE @Subject";
    }

    ...

    return query;
}

private static void AddClause(ref string query, ref bool whereAdded)
{
    if (!whereAdded)
    {
        whereAdded = true;
        query += " WHERE" + Environment.NewLine;
    }
    else
        query += " AND" + Environment.NewLine;
}
Now we can generate the appropriate query according to the context. This is an added bonus as we can write the exact query we need instead of generating lots of WHERE clauses like
[Docs].[DocStatusID] = ISNULL(@DocStatusID, [Docs].[DocStatusID])
that execute very very very slowly.

Note that the calling method would look like this:
protected void DataPortal_Fetch(DocListFilteredCriteria crit)
{
    using (var ctx = ConnectionManager<SqlConnection>.GetManager("DocStore"))
    {
        using (var cmd = new SqlCommand(GetDocListInlineQuery(crit), ctx.Connection))
        {
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.AddWithValue("@DocID", crit.DocID == null ? (object)DBNull.Value : crit.DocID.Value).DbType = DbType.Int32;
            cmd.Parameters.AddWithValue("@DocClassID", crit.DocClassID == null ? (object)DBNull.Value : crit.DocClassID.Value).DbType = DbType.Int32;
            ...
            cmd.Parameters.AddWithValue("@Subject", crit.Subject == null ? (object)DBNull.Value : crit.Subject).DbType = DbType.String;
            ...
            var args = new DataPortalHookArgs(cmd, crit);
            OnFetchPre(args);
            LoadCollection(cmd);
            OnFetchPost(args);
        }
    }
}
One can argue that there are a lot of unneeded parameter passing. But that's inexpensive while the WHERE clauses using ISNULL are very very expensive.
Mar 25, 2014 at 12:27 PM
Thank you Tiago,

There is a good chance that I will be working on a new project this year and this feature is one that would by very useful.

I'll get back to the group when I've made some progress.

Talk to you later,

Richard
Mar 25, 2014 at 11:41 PM
Hi Richard,

When I come to think of it, it's quite easy to do, meaning not too much work to do.
Since there is only one general setting and the UI part is ready. Provided CslaGenFork won't write the query, It's just a matter of changing some templates so the method is called on the appropriate places and an empty method is written on the extended file.

The point is: is this really what we want (or need)?

First of all you are using SqlCe. If CslaGenFork allows users to write their own queries, we should be able to use SQLite, MySQL, PostgreSQL, Firebird, Oracle, DB2 etc. Otherwise you need to edit the generated code all over the place.

Second, as you are using SqlCe, we want to use inline queries everywhere. What if I'm using SQL Server and want to use queries just for some objects? Suppose I want to use inline queries just to create/insert a new object of a given type?

1) We need some way to choose the target SQL engine.
2) We need some way to specify whether inline queries are allowed for some objects, for all or none.
3) We need some way to specify whether an object should use inline queries for some of its CRUD queries.

Requirement 2) means we need a tri-state general setting for Inline queries (easy to do).
Requirement 3) means we need multi-selection combo box (harder to do but already done).
Setting the general option to "inline queries" won't change the per object settings as one might be generating a multi SQL engine application and per object settings might be applicable for some SQL engines.

Stored procedures generation is an independent process and that makes sense as they might be handy as a blue print to write the inline query method.

So the harder parts are done and the easy ones still need to be done.

Requirement 1) might be a bit tricky as we need to translate a few SQL Server methods like SqlCommand, SqlDataAdapter, SqlConnection, SqlTransaction. Furthermore we also need to translate SQL engine namespace so everything gets generated as correctly as possible.
May 28, 2014 at 2:51 PM
tiago wrote:
1) We need some way to choose the target SQL engine.
(...)
Requirement 1) might be a bit tricky as we need to translate a few SQL Server methods like SqlCommand, SqlDataAdapter, SqlConnection, SqlTransaction. Furthermore we also need to translate SQL engine namespace so everything gets generated as correctly as possible.
Just to say inline queries are ready. Only requirement a) above is missing.