Instantly share code, notes, and snippets.

Embed
What would you like to do?
Custom Sitecore SqlDataProvider and QueryToSqlTranslator which fixes field querying in Sitecore Fast queries.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using Sitecore.Data.DataProviders.Sql.FastQuery;
using Sitecore.Data.Query;
namespace Kraftvaerk
{
public class KvSqlServerDataProvider : Sitecore.Data.SqlServer.SqlServerDataProvider
{
public KvSqlServerDataProvider(string connectionString)
: base(connectionString)
{
}
protected override Sitecore.Data.DataProviders.Sql.FastQuery.QueryToSqlTranslator CreateSqlTranslator()
{
return new KvQueryToSqlTranslator(this.Api);
}
}
public class KvQueryToSqlTranslator : Sitecore.Data.DataProviders.Sql.FastQuery.QueryToSqlTranslator
{
public KvQueryToSqlTranslator(Sitecore.Data.DataProviders.Sql.SqlDataApi api)
: base(api)
{
((Sitecore.Data.DataProviders.Sql.FastQuery.BasicTranslatorFactory)this._factory).Register(typeof(FieldElement), new KvFieldTranslator());
}
}
public class KvFieldTranslator : FieldTranslator
{
protected override string RenderFieldByGuid(Sitecore.Data.ID fieldID, ITranslationContext context)
{
context.Data["complex-fields"] = true;
var info = context.Fields[fieldID] as IFieldInfo;
if (info == null)
{
info = new KvIDFieldInfo(fieldID, context);
context.Fields[fieldID] = info;
}
return this.RenderField(info, context);
}
}
public class KvIDFieldInfo : IFieldInfo
{
public string Alias
{
get;
private set;
}
public KvIDFieldInfo(Sitecore.Data.ID fieldId, ITranslationContext context)
{
this.Alias = context.AddSubquery(context.SqlApi.Format("(SELECT {0}ItemId{1}, {0}Value{1} FROM {0}Fields{1} WHERE {0}FieldID{1}='") + context.SqlApi.Safe(fieldId.ToGuid().ToString()) + "')", "Fields");
}
}
}
var fieldId = ID.Parse("{252F9152-D23E-4203-8A21-88AF7007FE06}");
// Field querying syntax: @f252F9152D23E42038A2188AF7007FE06='Some value'
// Notice the f before field id.
var item = Context.Database.SelectSingleItem(
string.Format(@"fast://*[@@templateid='{{C29492D2-6F79-463C-A7FB-B318FAB90639}}'
and @f{0}='Some value']", fieldId.ToShortID()));
<dataProviders>
<main type="Kraftvaerk.KvSqlServerDataProvider, Kraftvaerk">
<param connectionStringName="$(1)" />
<Name>$(1)</Name>
</main>
</dataProviders>
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment