SQL CE 4 batch/multi criteria support

[Filip Kinsky]

I'd like to use multi criteria queries with SQL CE 4. The DB engine should support executing multiple queries separated by GO commands according to my tests, but I'm not able to modify MsSqlCe40Dialect to issue GO commands between two select statements executed at once. I tried to turn on SupportsSqlBatches property, but it didn't help.

The derived dialect class:

public class MsSqlCe40DialectEx: MsSqlCe40Dialect

{

public override bool SupportsSqlBatches

{

get { return true; }

}

}

The queries I'm trying to execute:

var totalRecords = session.QueryOver<User>().ToRowCountQuery().FutureValue<int>();

var users = session.QueryOver<User>()

.Fetch(x => x.Role).Eager

.OrderBy(x => x.UserName).Asc

.Skip((page-1)*rows)

.Take(rows)

.Future();

var recordCount = totalRecords.Value;

...

This QueryOver future combination generates two select statements executed at once, but without GO command between them which doesn't work in SQL CE 4 - it need's to have the GO command in place to be able to execute multiple select statements at once. Generated SQL looks like this:

SELECT count(*) as y0_ FROM USERS this_;

SELECT this_.ID as ID1_1_, this_.UserName as UserName1_1_, this_.PasswordHash as Password3_1_1_, this_.Role_ID as Role4_1_1_, role2_.ID as ID0_0_, role2_.Name as Name0_0_, role2_.Description as Descript3_0_0_ FROM USERS this_ left outer join ROLES role2_ on this_.Role_ID=role2_.ID ORDER BY this_.UserName asc OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY;

Is it possibile to change the dialect class to force NH to generate GO commands?

SELECT count(*) as y0_ FROM USERS this_;

GO

SELECT this_.ID as ID1_1_, this_.UserName as UserName1_1_, this_.PasswordHash as Password3_1_1_, this_.Role_ID as Role4_1_1_, role2_.ID as ID0_0_, role2_.Name as Name0_0_, role2_.Description as Descript3_0_0_ FROM USERS this_ left outer join ROLES role2_ on this_.Role_ID=role2_.ID ORDER BY this_.UserName asc OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY;

[Fabio Maulo]

override Driver.GetResultSetsCommand

[Fabio Maulo]

Implementing this

public interface IResultSetsCommand

{

void Append(SqlCommandInfo commandInfo);

int ParametersCount { get; }

bool HasQueries { get; }

SqlString Sql { get; }

IDataReader GetReader(QueryParameters[] queryParameters, int? commandTimeout);

}

[Roy.Jacobs]

Fabio, sorry to hijack this thread, but would this interface also work for my outer join workaround I put in Jira nh-2703? I mean, to just modify the SqlString and shuffe around the joins, wihout requiring to hack the NH internals?