public FieldPreValueSource GetPrevalueSource(Guid id)
{
//check for the default guid so we don't try to look for a provider which are not there..
//this could be considered a hack, but not everything can follow a generic model.
FieldPreValueSource pv = new FieldPreValueSource();
if (id == FieldPreValueSource.GetDefaultProvider().Id)
{
pv = FieldPreValueSource.GetDefaultProvider();
}
else
{
string sql = "SELECT * From UFPrevalueSources where id = @id";
IRecordsReader rr = sqlHelper.ExecuteReader(sql,
sqlHelper.CreateParameter("@id", id));
if (rr.Read())
{
pv = FieldPreValueSource.CreateFromDataReader(rr);
pv.Settings = settings.GetSettings(pv.Id);
rr.Dispose();
return(pv);
}
rr.Dispose();
}
return(pv);
}
public List <object> GetRecordFieldValues(RecordField rf)
{
string sql = string.Format("SELECT * FROM UFRecordData{0} where [Key] = @key ORDER BY Id ASC", rf.DataTypeAlias);
List <object> l = new List <object>();
IRecordsReader rr = sqlHelper.ExecuteReader(sql, sqlHelper.CreateParameter("@key", rf.Key));
while (rr.Read())
{
switch (rf.DataType)
{
case FieldDataType.String:
case FieldDataType.LongString:
l.Add(rr.GetString("Value"));
break;
case FieldDataType.Integer:
l.Add(rr.GetInt("Value"));
break;
case FieldDataType.DateTime:
l.Add(rr.GetDateTime("Value"));
break;
case FieldDataType.Bit:
l.Add(rr.GetBoolean("Value"));
break;
}
}
rr.Dispose();
return(l);
}
private static List <Relation> GetRelations(string alias, string sort, int parentId, int number)
{
List <Relation> retval = new List <Relation>();
ISqlHelper sqlhelper = umbraco.BusinessLogic.Application.SqlHelper;
IRecordsReader rr = sqlhelper.ExecuteReader(
string.Format(@"
SELECT TOP {0} umbracoRelation.id
FROM umbracoRelation
INNER JOIN umbracoRelationType ON umbracoRelationType.id = umbracoRelation.relType AND umbracoRelationType.alias = @alias
where parentId = @parent
ORDER BY datetime {1}
", number, sort)
,
sqlhelper.CreateParameter("@parent", parentId),
sqlhelper.CreateParameter("@alias", alias)
);
while (rr.Read())
{
retval.Add(new Relation(rr.GetInt("id")));
}
rr.Close();
rr.Dispose();
return(retval);
}
public static XPathNodeIterator UpcomingEvents()
{
int contentType = DocumentType.GetByAlias("Event").Id;
string property = "start";
string sql = string.Format(@"SELECT distinct contentNodeId from cmsPropertyData
inner join cmsPropertyType ON
cmspropertytype.contenttypeid = {0} and
cmspropertytype.Alias = '{1}' and
cmspropertytype.id = cmspropertydata.propertytypeid
where dataDate > GETDATE()", contentType, property);
ISqlHelper sqlhelper = umbraco.BusinessLogic.Application.SqlHelper;
IRecordsReader rr = sqlhelper.ExecuteReader(sql);
XmlDocument doc = new XmlDocument();
XmlNode root = umbraco.xmlHelper.addTextNode(doc, "events", "");
while (rr.Read())
{
XmlNode x = (XmlNode)umbraco.content.Instance.XmlContent.GetElementById(rr.GetInt("contentNodeId").ToString());
if (x != null)
{
x = doc.ImportNode(x, true);
root.AppendChild(x);
}
}
rr.Close();
rr.Dispose();
return(root.CreateNavigator().Select("."));
}
public Form GetForm(Guid id)
{
/*
* string sql = "SELECT TOP 1 * FROM UFforms where id = @id";
* IRecordsReader rr = sqlHelper.ExecuteReader(sql, sqlHelper.CreateParameter("@id", id));
*
* Form f = new Form();
* if (rr.Read())
* {
* f = Form.CreateFromDataReader(rr);
* f.Pages.AddRange(pageStorage.GetAllPages(f));
* }
*
* rr.Dispose();
*
* return f;
*/
string sql = "SELECT * FROM UFforms where id = @id";
IRecordsReader rr = sqlHelper.ExecuteReader(sql, sqlHelper.CreateParameter("@id", id));
Form f = new Form();
if (rr.Read())
{
f = Form.CreateFromDataReader(rr);
f.Pages.AddRange(pageStorage.GetAllPages(f));
}
rr.Dispose();
return(f);
}
public Company(int id)
{
string sql = "SELECT * from companies Where ID = @id";
IRecordsReader rr = Application.SqlHelper.ExecuteReader(sql, Application.SqlHelper.CreateParameter("@id", id));
if (rr.Read())
{
FromReader(this, rr);
}
rr.Dispose();
}
public Company(string name)
{
string sql = "SELECT * from companies Where name = @name";
IRecordsReader rr = Application.SqlHelper.ExecuteReader(sql, Application.SqlHelper.CreateParameter("@name", name));
if (rr.Read())
{
FromReader(this, rr);
}
rr.Dispose();
}
public List <Field> GetAllFields(FieldSet fieldset)
{
List <Field> l = new List <Field>();
string sql = @"SELECT UFfields.*, UFpages.form AS form, UFfieldsets.sortorder as FieldsetIndex, UFpages.sortorder as PageIndex
From UFfields
INNER JOIN UFfieldsets ON UFfieldsets.id = fieldset
INNER JOIN UFpages ON UFpages.id = UFfieldsets.page
where fieldset = @fieldset ORDER by UFfields.sortOrder ASC";
IRecordsReader rr = sqlHelper.ExecuteReader(sql, sqlHelper.CreateParameter("@fieldset", fieldset.Id));
while (rr.Read())
{
Field f = Field.CreateFromDataReader(rr);
if (!rr.IsNull("prevalueProvider") && rr.GetGuid("prevalueProvider") != Guid.Empty)
{
f.PreValueSource = prevalueSourceStorage.GetPrevalueSource(rr.GetGuid("prevalueProvider"));
if (f.PreValueSource != null &&
f.PreValueSource.Id != Guid.Empty)
{
f.PreValueSourceId = f.PreValueSource.Id;
}
// if(f.PreValueSource.Type != null)
// f.PreValueSource.Type.LoadSettings(f.PreValueSource);
}
f.Settings = settings.GetSettingsAsList(f.Id);
//if (f.FieldType.HasSettings())
// f.FieldType.LoadSettings(f.Settings);
f.Condition = conditionStorage.GetFieldCondition(f);
if (f.Condition == null)
{
f.Condition = new FieldCondition();
f.Condition.Enabled = false;
f.Condition.ActionType = FieldConditionActionType.Show;
f.Condition.LogicType = FieldConditionLogicType.All;
}
l.Add(f);
}
rr.Dispose();
return(l);
}
public Workflow GetWorkflow(Guid id)
{
string sql = "SELECT * from UFworkflows where id = @id";
IRecordsReader rr = sqlHelper.ExecuteReader(sql, sqlHelper.CreateParameter("@id", id));
Workflow wf = new Workflow();
if (rr.Read())
{
wf = Workflow.CreateFromDataReader(rr);
wf.Settings = settings.GetSettings(wf.Id);
}
rr.Dispose();
return(wf);
}
public Dictionary <string, string> GetSettings(Guid id)
{
string sql = "SELECT * from UFSettings where id = @id";
IRecordsReader settingsReader = sqlHelper.ExecuteReader(sql, sqlHelper.CreateParameter("@id", id));
Dictionary <string, string> settings = new Dictionary <string, string>();
while (settingsReader.Read())
{
settings.Add(settingsReader.GetString("key"), settingsReader.GetString("value"));
}
settingsReader.Dispose();
return(settings);
}
public static List <Company> GetAll()
{
string sql = "SELECT * from companies order by Name ASC";
IRecordsReader rr = umbraco.BusinessLogic.Application.SqlHelper.ExecuteReader(sql);
List <Company> list = new List <Company>();
while (rr.Read())
{
list.Add(FromReader(rr));
}
rr.Dispose();
return(list);
}
public FieldSet GetFieldSet(Guid id)
{
string sql = "SELECT * FROM UFfieldsets where id = @id";
IRecordsReader rr = sqlHelper.ExecuteReader(sql, sqlHelper.CreateParameter("@id", id));
FieldSet fs = new FieldSet();
if (rr.Read())
{
fs = FieldSet.CreateFromDataReader(rr);
fs.Fields.AddRange(storage.GetAllFields(fs));
return(fs);
}
rr.Dispose();
return(fs);
}
public FieldConditionRule GetFieldConditionRule(Guid id)
{
string sql = "SELECT * from UFfieldconditionrules where id = @id";
IRecordsReader rr = sqlHelper.ExecuteReader(sql, sqlHelper.CreateParameter("@id", id));
FieldConditionRule fcr = new FieldConditionRule();
if (rr.Read())
{
fcr = FieldConditionRule.CreateFromDataReader(rr);
}
rr.Dispose();
return(fcr);
}
public List <Workflow> GetAllWorkFlows(Form form)
{
string sql = "SELECT * from UFworkflows where form = @form ORDER by Name ASC";
IRecordsReader rr = sqlHelper.ExecuteReader(sql, sqlHelper.CreateParameter("@form", form.Id));
List <Workflow> l = new List <Workflow>();
while (rr.Read())
{
Workflow wf = Workflow.CreateFromDataReader(rr);
wf.Settings = settings.GetSettings(wf.Id);
l.Add(wf);
}
rr.Dispose();
return(l);
}
public List <PreValue> GetAllPreValues(Field field)
{
List <PreValue> l = new List <PreValue>();
string sql = "SELECT * From UFprevalues where field = @field";
IRecordsReader rr = sqlHelper.ExecuteReader(sql,
sqlHelper.CreateParameter("@field", field.Id));
while (rr.Read())
{
PreValue pv = PreValue.CreateFromDataReader(rr);
l.Add(pv);
}
rr.Dispose();
return(l);
}
public Field GetField(Guid id)
{
string sql = @"SELECT UFfields.*, UFpages.form AS form, UFfieldsets.sortorder as FieldsetIndex, UFpages.sortorder as PageIndex
From UFfields
INNER JOIN UFfieldsets ON UFfieldsets.id = fieldset
INNER JOIN UFpages ON UFpages.id = UFfieldsets.page
where UFfields.id = @id";
IRecordsReader rr = sqlHelper.ExecuteReader(sql, sqlHelper.CreateParameter("@id", id));
Field f = new Field();
if (rr.Read())
{
f = Field.CreateFromDataReader(rr);
if (!rr.IsNull("prevalueProvider") && rr.GetGuid("prevalueProvider") != Guid.Empty)
{
f.PreValueSource = prevalueSourceStorage.GetPrevalueSource(rr.GetGuid("prevalueProvider"));
if (f.PreValueSource != null &&
f.PreValueSource.Id != Guid.Empty)
{
f.PreValueSourceId = f.PreValueSource.Id;
}
}
f.Settings = settings.GetSettingsAsList(f.Id);
//if (f.FieldType.HasSettings())
// f.FieldType.LoadSettings(f.Settings);
f.Condition = conditionStorage.GetFieldCondition(f);
if (f.Condition == null)
{
f.Condition = new FieldCondition();
f.Condition.Enabled = false;
f.Condition.ActionType = FieldConditionActionType.Show;
f.Condition.LogicType = FieldConditionLogicType.All;
}
}
rr.Dispose();
return(f);
}
public List <FieldPreValueSource> GetAllPrevalueSources()
{
List <FieldPreValueSource> l = new List <FieldPreValueSource>();
string sql = "SELECT * From UFPrevalueSources ORDER BY name ASC";
IRecordsReader rr = sqlHelper.ExecuteReader(sql);
while (rr.Read())
{
FieldPreValueSource pv = FieldPreValueSource.CreateFromDataReader(rr);
pv.Settings = settings.GetSettings(pv.Id);
l.Add(pv);
}
rr.Dispose();
return(l);
}
public Dictionary <Guid, RecordField> GetAllRecordFields(Record record, Form form)
{
string sql = "SELECT * FROM UFRecordFields where Record = @record";
Dictionary <Guid, RecordField> l = new Dictionary <Guid, RecordField>();
IRecordsReader rr = sqlHelper.ExecuteReader(sql, sqlHelper.CreateParameter("@record", record.Id));
while (rr.Read())
{
RecordField r = RecordField.CreateFromDataReader(rr);
r.Values = recordFieldValueStorage.GetRecordFieldValues(r);
l.Add(r.FieldId, r);
}
rr.Dispose();
return(l);
}
public Page GetPage(Guid id)
{
string sql = "SELECT * from UFpages where id = @id";
IRecordsReader rr = sqlHelper.ExecuteReader(sql, sqlHelper.CreateParameter("@id", id));
Page p = new Page();
if (rr.Read())
{
p = Page.CreateFromDataReader(rr);
p.FieldSets.AddRange(storage.GetAllFieldSets(p));
}
rr.Dispose();
return(p);
}
public List <Page> GetAllPages(Form form)
{
string sql = "SELECT * FROM UFpages where form = @form ORDER BY sortorder ASC";
List <Page> l = new List <Page>();
IRecordsReader rr = sqlHelper.ExecuteReader(sql, sqlHelper.CreateParameter("@form", form.Id));
while (rr.Read())
{
Page p = Page.CreateFromDataReader(rr);
p.FieldSets.AddRange(storage.GetAllFieldSets(p));
l.Add(p);
}
rr.Dispose();
return(l);
}
public List <Record> GetAllRecords(Form form)
{
string sql = "SELECT * FROM UFRecords where form = @form ORDER BY created ASC";
List <Record> l = new List <Record>();
IRecordsReader rr = sqlHelper.ExecuteReader(sql, sqlHelper.CreateParameter("@form", form.Id));
while (rr.Read())
{
Record r = Record.CreateFromDataReader(rr);
r.RecordFields = recordFieldStorage.GetAllRecordFields(r, form);
l.Add(r);
}
rr.Dispose();
return(l);
}
public string GetSetting(Guid id, string key)
{
string set = string.Empty;
string sql = "SELECT * from UFSettings where id = @id and [key] = @key";
IRecordsReader settingsReader = sqlHelper.ExecuteReader(sql,
sqlHelper.CreateParameter("@id", id),
sqlHelper.CreateParameter("@key", key));
if (settingsReader.Read())
{
set = settingsReader.GetString("value");
}
settingsReader.Dispose();
return(set);
}
public PreValue GetPreValue(Guid id)
{
string sql = "SELECT * From UFprevalues where id = @id";
IRecordsReader rr = sqlHelper.ExecuteReader(sql,
sqlHelper.CreateParameter("@id", id));
PreValue pv = new PreValue();
if (rr.Read())
{
pv = PreValue.CreateFromDataReader(rr);
return(pv);
}
rr.Dispose();
return(pv);
}
public FieldCondition GetFieldCondition(Guid id)
{
string sql = "SELECT * from UFfieldconditions where id = @id";
IRecordsReader rr = sqlHelper.ExecuteReader(sql, sqlHelper.CreateParameter("@id", id));
FieldCondition fc = new FieldCondition();
if (rr.Read())
{
fc = FieldCondition.CreateFromDataReader(rr);
fc.Rules.AddRange(storage.GetAllFieldConditionRules(fc));
}
rr.Dispose();
return(fc);
}
//added list variant since dictionary doesn't support serialization (form export/import)
public List <Setting <string, string> > GetSettingsAsList(Guid id)
{
string sql = "SELECT * from UFSettings where id = @id";
IRecordsReader settingsReader = sqlHelper.ExecuteReader(sql, sqlHelper.CreateParameter("@id", id));
List <Setting <string, string> > settings = new List <Setting <string, string> >();
while (settingsReader.Read())
{
Setting <string, string> setting = new Setting <string, string>();
setting.Key = settingsReader.GetString("key");
setting.Value = settingsReader.GetString("value");
settings.Add(setting);
}
settingsReader.Dispose();
return(settings);
}
public static List <LogItem> ConvertIRecordsReader(IRecordsReader reader)
{
var items = new List <LogItem>();
while (reader.Read())
{
items.Add(new LogItem(
reader.GetInt("userId"),
reader.GetInt("nodeId"),
reader.GetDateTime("DateStamp"),
ConvertLogHeader(reader.GetString("logHeader")),
reader.GetString("logComment")));
}
reader.Close();
reader.Dispose();
return(items);
}
public List <Setting <string, string> > GetSettings(int dataTypeNodeID)
{
string sql = "select * from cmsDataTypePreValues where datatypenodeid = @datatypenodeid";
IRecordsReader settingsReader = sqlHelper.ExecuteReader(sql, sqlHelper.CreateParameter("@datatypenodeid", dataTypeNodeID));
List <Setting <string, string> > settings = new List <Setting <string, string> >();
while (settingsReader.Read())
{
Setting <string, string> setting = new Setting <string, string>();
setting.Key = settingsReader.GetString("alias");
setting.Value = settingsReader.GetString("value");
settings.Add(setting);
}
settingsReader.Dispose();
return(settings);
}
public FieldCondition GetAllFieldConditions()
{
string sql = "SELECT * from UFfieldconditions";
IRecordsReader rr = sqlHelper.ExecuteReader(sql);
FieldCondition fc = null;
if (rr.Read())
{
fc = new FieldCondition();
fc = FieldCondition.CreateFromDataReader(rr);
fc.Rules = storage.GetAllFieldConditionRules(fc);
}
rr.Dispose();
return(fc);
}
public List <FieldConditionRule> GetAllFieldConditionRules(FieldCondition fieldCondition)
{
List <FieldConditionRule> l = new List <FieldConditionRule>();
string sql = "SELECT * from UFfieldconditionrules where fieldcondition = @fieldcondition";
IRecordsReader rr = sqlHelper.ExecuteReader(sql, sqlHelper.CreateParameter("@fieldcondition", fieldCondition.Id));
FieldConditionRule fcr = new FieldConditionRule();
while (rr.Read())
{
fcr = FieldConditionRule.CreateFromDataReader(rr);
l.Add(fcr);
}
rr.Dispose();
return(l);
}
public Boolean IsInUse()
{
ISqlHelper sqlHelper = Helper.SqlHelper;
string sql = "Select 1 from UFfields where PreValueProvider = @id;";
IRecordsReader rr = sqlHelper.ExecuteReader(sql,
sqlHelper.CreateParameter("@id", Id));
bool inuse = false;
if (rr.Read())
{
inuse = true;
}
rr.Dispose();
return(inuse);
}