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);
}
/// <summary>
/// Retrieve a list of the id's of all CMSNodes given the objecttype and the first letter of the name.
/// </summary>
/// <param name="objectType">The objecttype identifier</param>
/// <param name="letter">Firstletter</param>
/// <returns>
/// A list of all CMSNodes which has the objecttype and a name that starts with the given letter
/// </returns>
protected int[] GetUniquesFromObjectTypeAndFirstLetter(Guid objectType, char letter)
{
using (
IRecordsReader dr =
SqlHelper.ExecuteReader(
"Select id from umbracoNode where nodeObjectType = @objectType AND text like @letter",
SqlHelper.CreateParameter("@objectType", objectType),
SqlHelper.CreateParameter("@letter", letter.ToString() + "%")))
{
List <int> tmp = new List <int>();
while (dr.Read())
{
tmp.Add(dr.GetInt("id"));
}
return(tmp.ToArray());
}
}
/// <summary>
/// Get all tasks assigned to a node
/// </summary>
/// <param name="nodeId"></param>
/// <returns></returns>
public static Tasks GetTasks(int nodeId)
{
var sql = "select id, taskTypeId, nodeId, parentUserId, userId, DateTime, comment, closed from cmsTask where nodeId = @nodeId";
Tasks tasks = new Tasks();
using (IRecordsReader dr = SqlHelper.ExecuteReader(sql, SqlHelper.CreateParameter("@nodeId", nodeId)))
{
while (dr.Read())
{
var t = new Task();
t.PopulateTaskFromReader(dr);
tasks.Add(t);
}
}
return(tasks);
}
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 static IEnumerable <CMSNode> GetNodesWithTags(string tags)
{
var nodes = new List <CMSNode>();
string sql = @"SELECT DISTINCT cmsTagRelationShip.nodeid from cmsTagRelationShip
INNER JOIN cmsTags ON cmsTagRelationShip.tagid = cmsTags.id WHERE (cmsTags.tag IN (" + GetSqlStringArray(tags) + "))";
using (var sqlHelper = Application.SqlHelper)
using (IRecordsReader rr = sqlHelper.ExecuteReader(sql))
{
while (rr.Read())
{
nodes.Add(new CMSNode(rr.GetInt("nodeid")));
}
}
return(nodes);
}
private void setup()
{
using (IRecordsReader dr = SqlHelper.ExecuteReader("select macroPropertyTypeAlias, macroPropertyTypeRenderAssembly, macroPropertyTypeRenderType, macroPropertyTypeBaseType from cmsMacroPropertyType where id = @id", SqlHelper.CreateParameter("@id", _id)))
{
if (dr.Read())
{
_alias = dr.GetString("macroPropertyTypeAlias");
_assembly = dr.GetString("macroPropertyTypeRenderAssembly");
_type = dr.GetString("macroPropertyTypeRenderType");
_baseType = dr.GetString("macroPropertyTypeBaseType");
}
else
{
throw new ArgumentException("No macro property type found with the id specified");
}
}
}
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);
}
protected override void setupNode()
{
base.setupNode();
using (IRecordsReader dr = SqlHelper.ExecuteReader("select dbType, controlId from cmsDataType where nodeId = '" + this.Id.ToString() + "'"))
{
if (dr.Read())
{
_controlId = dr.GetGuid("controlId");
_dbType = dr.GetString("dbType");
}
else
{
throw new ArgumentException("No dataType with id = " + this.Id.ToString() + " found");
}
}
}
/// <summary>
/// Gets the pre values collection.
/// </summary>
/// <param name="DataTypeId">The data type id.</param>
/// <returns></returns>
public static SortedList GetPreValues(int DataTypeId)
{
SortedList retval = new SortedList();
IRecordsReader dr = SqlHelper.ExecuteReader(
"Select id, sortorder, [value] from cmsDataTypePreValues where DataTypeNodeId = @dataTypeId order by sortorder",
SqlHelper.CreateParameter("@dataTypeId", DataTypeId));
int counter = 0;
while (dr.Read())
{
retval.Add(counter, new PreValue(dr.GetInt("id"), dr.GetInt("sortorder"), dr.GetString("value")));
counter++;
}
dr.Close();
return(retval);
}
/// <summary>
/// Initializes a new instance of the <see cref="Task"/> class.
/// </summary>
/// <param name="TaskId">The task id.</param>
public Task(int TaskId)
{
using (IRecordsReader dr =
SqlHelper.ExecuteReader(
"select id, taskTypeId, nodeId, parentUserId, userId, DateTime, comment, closed from cmsTask where id = @id",
SqlHelper.CreateParameter("@id", TaskId)))
{
if (dr.Read())
{
PopulateTaskFromReader(dr);
}
else
{
throw new ArgumentException("Task with id: '" + TaskId + "' not found");
}
}
}
public List <Field> GetAllFields(Form form)
{
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 UFpages.form = @form
ORDER by UfPages.SortOrder ASC, UFFieldsets.sortorder ASC, UFfields.sortOrder ASC
";
IRecordsReader rr = sqlHelper.ExecuteReader(sql,
sqlHelper.CreateParameter("@form", form.Id));
while (rr.Read())
{
Field f = Field.CreateFromDataReader(rr);
if (!rr.IsNull("prevalueProvider"))
{
f.PreValueSource = prevalueSourceStorage.GetPrevalueSource(rr.GetGuid("prevalueProvider"));
}
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 template(int templateID)
{
if (templateCache["template" + templateID.ToString()] != null)
{
template t = (template)templateCache["template" + templateID];
this._masterTemplate = t._masterTemplate;
this._templateAlias = t._templateAlias;
this._templateDesign = t._templateDesign;
this._masterTemplate = t._masterTemplate;
this._templateName = t._templateName;
}
else
{
using (IRecordsReader templateData = SqlHelper.ExecuteReader("select nodeId, alias, master, text, design from cmsTemplate inner join umbracoNode node on node.id = cmsTemplate.nodeId where nodeId = @templateID", SqlHelper.CreateParameter("@templateID", templateID)))
{
if (templateData.Read())
{
// Get template master and replace content where the template
if (!templateData.IsNull("master"))
{
_masterTemplate = templateData.GetInt("master");
}
if (!templateData.IsNull("alias"))
{
_templateAlias = templateData.GetString("alias");
}
if (!templateData.IsNull("text"))
{
_templateName = templateData.GetString("text");
}
if (!templateData.IsNull("design"))
{
_templateDesign = templateData.GetString("design");
}
templateData.Close();
templateCache.Insert("template" + templateID.ToString(), this);
}
}
}
// Only check for master on legacy templates - can show error when using master pages.
if (!UmbracoSettings.UseAspNetMasterPages)
{
checkForMaster(templateID);
}
}
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);
}
/// <summary>
/// Returns the permissions for a node
/// </summary>
/// <param name="user"></param>
/// <returns></returns>
public static IEnumerable <UserTypePermissions> GetNodePermissions(CMSNode node)
{
var items = new List <UserTypePermissions>();
using (IRecordsReader dr = SqlHelper.ExecuteReader("select * from UserGroupPermissions_UserType2NodePermission where nodeId = @nodeId order by nodeId", SqlHelper.CreateParameter("@nodeId", node.Id)))
{
while (dr.Read())
{
items.Add(new UserTypePermissions()
{
NodeId = dr.GetInt("nodeId"),
PermissionId = Convert.ToChar(dr.GetString("permission")),
UserTypeId = dr.GetInt("userTypeId")
});
}
}
return(items);
}
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 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 <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 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);
}
public static PropertyType[] GetAll()
{
var result = new List <PropertyType>();
using (IRecordsReader dr =
SqlHelper.ExecuteReader("select id, Name from cmsPropertyType order by Name"))
{
while (dr.Read())
{
PropertyType pt = GetPropertyType(dr.GetInt("id"));
if (pt != null)
{
result.Add(pt);
}
}
}
return(result.ToArray());
}
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 override IEnumerable GetDescendants()
{
var tmp = new List <Media>();
using (IRecordsReader dr = SqlHelper.ExecuteReader(
string.Format(m_SQLOptimizedMany.Trim(), "umbracoNode.path LIKE '%," + this.Id + ",%'", "umbracoNode.level"),
SqlHelper.CreateParameter("@nodeObjectType", Media._objectType)))
{
while (dr.Read())
{
Media d = new Media(dr.GetInt("id"), true);
d.PopulateMediaFromReader(dr);
tmp.Add(d);
}
}
return(tmp.ToArray());
}
/// <summary>
/// Returns the notifications for a node
/// </summary>
/// <param name="user"></param>
/// <returns></returns>
public static IEnumerable <Notification> GetNodeNotifications(CMSNode node)
{
var items = new List <Notification>();
using (IRecordsReader dr = SqlHelper.ExecuteReader("select * from umbracoUser2NodeNotify where nodeId = @nodeId order by nodeId", SqlHelper.CreateParameter("@nodeId", node.Id)))
{
while (dr.Read())
{
items.Add(new Notification()
{
NodeId = dr.GetInt("nodeId"),
ActionId = Convert.ToChar(dr.GetString("action")),
UserId = dr.GetInt("userId")
});
}
}
return(items);
}
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);
}
private static int GetNewDocumentSortOrder(int parentId)
{
var sortOrder = 0;
using (IRecordsReader dr = SqlHelper.ExecuteReader(
"SELECT MAX(sortOrder) AS sortOrder FROM umbracoNode WHERE parentID = @parentID AND nodeObjectType = @GuidForNodesOfTypeDocument",
SqlHelper.CreateParameter("@parentID", parentId),
SqlHelper.CreateParameter("@GuidForNodesOfTypeDocument", Document._objectType)
))
{
while (dr.Read())
{
sortOrder = dr.GetInt("sortOrder") + 1;
}
}
return(sortOrder);
}
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);
}
/// <summary>
/// Returns the permissions for a user
/// </summary>
/// <param name="user"></param>
/// <returns></returns>
public static IEnumerable <Permission> GetUserPermissions(User user)
{
var items = new List <Permission>();
using (IRecordsReader dr = SqlHelper.ExecuteReader("select * from umbracoUser2NodePermission where userId = @userId order by nodeId", SqlHelper.CreateParameter("@userId", user.Id)))
{
while (dr.Read())
{
items.Add(new Permission()
{
NodeId = dr.GetInt("nodeId"),
PermissionId = Convert.ToChar(dr.GetString("permission")),
UserId = dr.GetInt("userId")
});
}
}
return(items);
}
private static IEnumerable <Tag> ConvertSqlToTags(string sql, params IParameter[] param)
{
List <Tag> tags = new List <Tag>();
using (IRecordsReader rr = SqlHelper.ExecuteReader(sql, param))
{
while (rr.Read())
{
tags.Add(new Tag(
rr.GetInt("id"),
rr.GetString("tag"),
rr.GetString("group"),
rr.GetInt("nodeCount")));
}
}
return(tags);
}
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 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);
}
private static XPathNodeIterator CommentsToXml(IRecordsReader rr)
{
XmlDocument xd = new XmlDocument();
XmlNode x = umbraco.xmlHelper.addTextNode(xd, "comments", "");
while (rr.Read())
{
XmlNode c = xd.CreateElement("comment");
c.Attributes.Append(umbraco.xmlHelper.addAttribute(xd, "id", rr.GetInt("id").ToString()));
c.Attributes.Append(umbraco.xmlHelper.addAttribute(xd, "nodeid", rr.GetInt("nodeid").ToString()));
c.Attributes.Append(umbraco.xmlHelper.addAttribute(xd, "created", rr.GetDateTime("created").ToString()));
c.AppendChild(umbraco.xmlHelper.addCDataNode(xd, "name", rr.GetString("name")));
c.AppendChild(umbraco.xmlHelper.addCDataNode(xd, "email", rr.GetString("email")));
c.AppendChild(umbraco.xmlHelper.addCDataNode(xd, "website", rr.GetString("website")));
c.AppendChild(umbraco.xmlHelper.addCDataNode(xd, "message", rr.GetString("comment")));
x.AppendChild(c);
}
xd.AppendChild(x);
rr.Close();
return xd.CreateNavigator().Select(".");
}
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")));
}
return items;
}