public static List <UrlTrackerDomain> GetDomains()
{
if (_urlTrackerDomains == null)
{
lock (_locker)
{
_urlTrackerDomains = new List <UrlTrackerDomain>();
ISqlHelper sqlHelper = Application.SqlHelper;
using (var dr = sqlHelper.ExecuteReader("SELECT * FROM umbracoDomains where CHARINDEX('*',domainName) < 1"))
{
while (dr.Read())
{
_urlTrackerDomains.Add(new UrlTrackerDomain(dr.GetInt("id"), dr.GetInt("domainRootStructureID"), dr.GetString("domainName")));
}
}
_urlTrackerDomains = _urlTrackerDomains.OrderBy(x => x.Name).ToList();
if (UrlTrackerSettings.HasDomainOnChildNode)
{
using (var dr = sqlHelper.ExecuteReader("SELECT * FROM umbracoDomains where CHARINDEX('*',domainName) = 1"))
{
while (dr.Read())
{
_urlTrackerDomains.Add(new UrlTrackerDomain(dr.GetInt("id"), dr.GetInt("domainRootStructureID"), dr.GetString("domainName")));
}
}
}
_urlTrackerDomains = _urlTrackerDomains.OrderBy(x => x.Name).ToList();
}
}
return(_urlTrackerDomains);
}
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") && 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;
}
l.Add(f);
}
rr.Dispose();
return(l);
}
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("."));
}
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);
}
private void setupUser(int ID)
{
_id = ID;
using (IRecordsReader dr = SqlHelper.ExecuteReader(
"Select userNoConsole, userDisabled, userType,startStructureID, startMediaId, userName,userLogin,userEmail,userDefaultPermissions, userLanguage, defaultToLiveEditing from umbracoUser where id = @id",
SqlHelper.CreateParameter("@id", ID)))
{
if (dr.Read())
{
_userNoConsole = dr.GetBoolean("usernoconsole");
_userDisabled = dr.GetBoolean("userDisabled");
_name = dr.GetString("userName");
_loginname = dr.GetString("userLogin");
_email = dr.GetString("userEmail");
_language = dr.GetString("userLanguage");
_startnodeid = dr.GetInt("startStructureID");
if (!dr.IsNull("startMediaId"))
{
_startmediaid = dr.GetInt("startMediaID");
}
_usertype = UserType.GetUserType(dr.GetShort("UserType"));
_defaultToLiveEditing = dr.GetBoolean("defaultToLiveEditing");
}
else
{
throw new ArgumentException("No User exists with ID " + ID.ToString());
}
}
_isInitialized = true;
}
/// <summary>
/// Loads the data value from the database.
/// </summary>
protected virtual void LoadValueFromDatabase()
{
//this is an optimized version of this query. In one call it will return the data type
//and the values, this will then set the underlying db type and value of the BaseDataType object
//instead of having it query the database itself.
var sql = @"
SELECT dataInt, dataDate, dataNvarchar, dataNtext, dbType FROM cmsPropertyData
INNER JOIN cmsPropertyType ON cmsPropertyType.id = cmsPropertyData.propertytypeid
INNER JOIN cmsDataType ON cmsDataType.nodeId = cmsPropertyType.dataTypeId
WHERE cmsPropertyData.id = " + m_PropertyId;
using (var r = SqlHelper.ExecuteReader(sql))
{
if (r.Read())
{
//the type stored in the cmsDataType table
var strDbType = r.GetString("dbType");
//get the enum of the data type
var dbType = BaseDataType.GetDBType(strDbType);
//get the column name in the cmsPropertyData table that stores the correct information for the data type
var fieldName = BaseDataType.GetDataFieldName(dbType);
//get the value for the data type, if null, set it to an empty string
m_Value = r.GetObject(fieldName) ?? string.Empty;
//now that we've set our value, we can update our BaseDataType object with the correct values from the db
//instead of making it query for itself. This is a peformance optimization enhancement.
_dataType.SetDataTypeProperties(fieldName, dbType);
}
}
}
/// <summary>
/// Reads all items from the database and stores in local cache
/// </summary>
private static void EnsureCache()
{
using (var lck = new UpgradeableReadLock(Locker))
{
if (_cacheIsEnsured)
{
return;
}
lck.UpgradeToWriteLock();
using (var dr = SqlHelper.ExecuteReader("Select pk, id, [key], parent from cmsDictionary"))
{
while (dr.Read())
{
//create new dictionaryitem object and put in cache
var item = new DictionaryItem(dr.GetInt("pk"),
dr.GetString("key"),
dr.GetGuid("id"),
dr.GetGuid("parent"));
DictionaryItems.TryAdd(item.key, item);
}
}
_cacheIsEnsured = true;
}
}
public void LoadDocTypesTest_RecursiveParent()
{
ISqlHelper fakeSql = Isolate.Fake.Instance <ISqlHelper>(Members.CallOriginal);
IRecordsReader reader = Isolate.Fake.Instance <IRecordsReader>();
Isolate.WhenCalled(() => reader.HasRecords).WillReturnRepeat(true, 3).AndThen().CallOriginal();
Isolate.WhenCalled(() => reader.Read()).WillReturnRepeat(true, 3).AndThen().CallOriginal();
Isolate.WhenCalled(() => reader.GetId()).WillReturn(2);
Isolate.WhenCalled(() => reader.GetParentId()).WillReturn(1);
Isolate.WhenCalled(() => reader.GetId()).WillReturn(1);
Isolate.WhenCalled(() => reader.GetParentId()).WillReturn(3);
Isolate.WhenCalled(() => reader.GetId()).WillReturn(3);
Isolate.WhenCalled(() => reader.GetParentId()).WillReturn(-1);
Isolate.WhenCalled(() => fakeSql.ExecuteReader(string.Empty)).WillReturn(reader);
DocTypeObjectBuilder target = new DocTypeObjectBuilder(string.Empty);
Isolate.WhenCalled(() => target.SqlHelper).WillReturn(fakeSql);
Isolate.WhenCalled(() => target.GetProperties(0)).WillReturn(new System.Collections.Generic.List <DocTypeProperty>());
Isolate.WhenCalled(() => target.BuildAssociations(0)).WillReturn(new System.Collections.Generic.List <DocTypeAssociation>());
target.LoadDocTypes();
Assert.AreEqual(3, target.DocumentTypes.Count);
Assert.AreEqual(target.DocumentTypes[0].Id, target.DocumentTypes[1].ParentId);
Isolate.Verify.WasCalledWithExactArguments(() => target.LoadParentDocType(3));
}
public PropertyType(int id)
{
using (IRecordsReader dr = SqlHelper.ExecuteReader(
"Select mandatory, DataTypeId, tabId, ContentTypeId, sortOrder, alias, name, validationRegExp, description from cmsPropertyType where id=@id",
SqlHelper.CreateParameter("@id", id)))
{
if (!dr.Read())
{
throw new ArgumentException("Propertytype with id: " + id + " doesnt exist!");
}
_mandatory = dr.GetBoolean("mandatory");
_id = id;
if (!dr.IsNull("tabId"))
{
_tabId = dr.GetInt("tabId");
}
_sortOrder = dr.GetInt("sortOrder");
_alias = dr.GetString("alias");
_name = dr.GetString("Name");
_validationRegExp = dr.GetString("validationRegExp");
_DataTypeId = dr.GetInt("DataTypeId");
_contenttypeid = dr.GetInt("contentTypeId");
_description = dr.GetString("description");
}
}
/// <summary>
/// Creates a new language given the culture code - ie. da-dk (denmark)
/// </summary>
/// <param name="cultureCode">Culturecode of the language</param>
public static void MakeNew(string cultureCode)
{
lock (Locker)
{
var culture = GetCulture(cultureCode);
if (culture != null)
{
//insert it
SqlHelper.ExecuteNonQuery(
"insert into umbracoLanguage (languageISOCode) values (@CultureCode)",
SqlHelper.CreateParameter("@CultureCode", cultureCode));
InvalidateCache();
//get it's id
var newId = SqlHelper.ExecuteScalar <int>("SELECT MAX(id) FROM umbracoLanguage WHERE languageISOCode=@cultureCode", SqlHelper.CreateParameter("@cultureCode", cultureCode));
//load it and raise events
using (var dr = SqlHelper.ExecuteReader(string.Format("{0} where id = {1}", m_SQLOptimizedGetAll, newId)))
{
while (dr.Read())
{
var ct = new Language();
ct.PopulateFromReader(dr);
ct.OnNew(new NewEventArgs());
}
}
}
}
}
/// <summary>
/// Reads all items from the database and stores in local cache
/// </summary>
private static void EnsureCache()
{
if (!_cacheIsEnsured)
{
lock (Locker)
{
if (!_cacheIsEnsured)
{
using (IRecordsReader dr = SqlHelper.ExecuteReader("Select pk, id, [key], parent from cmsDictionary"))
{
while (dr.Read())
{
//create new dictionaryitem object and put in cache
var item = new DictionaryItem(dr.GetInt("pk"),
dr.GetString("key"),
dr.GetGuid("id"),
dr.GetGuid("parent"));
DictionaryItems.TryAdd(item.key, item);
}
}
_cacheIsEnsured = true;
}
}
}
}
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 IRecordsReader ExecuteReader(string commandText, params IParameter[] parameters)
{
if (UrlTrackerSettings.IsCacheDisabled)
{
return(SqlHelper.ExecuteReader(commandText, parameters));
}
var stringBuilder = new StringBuilder(commandText);
foreach (var parameter in parameters)
{
stringBuilder.Append(parameter.ParameterName);
stringBuilder.Append(parameter.Value);
}
var key = stringBuilder.ToString();
string hash;
using (var md5Hash = MD5.Create())
{
hash = HashHelper.GetMd5Hash(md5Hash, key);
}
var recordReaderCacheEntry = SqlCacheProvider.Instance.GetItem(hash, () => GetRecordReaderCacheEntry(commandText, parameters));
return(new RecordsReader(recordReaderCacheEntry));
}
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);
}
public IEnumerable <Schedule> GetAllScheduleTasks()
{
string sql = "SELECT VGS.[ID] ,[OrganizationID] ,[ServiceTypeId] ,[ServiceSubTypeId] ,[SourceId] ,[DateFrom] ,[DateTo] ,[LastRun] ,[Params] ,[Email] ,[Active], [Frequency] ,[DayToRun],[Time] FROM [vlfGenericSchedules] VGS INNER JOIN vlfGenericSchedulesTime VGST ON VGST.ScheduleID = VGS.ID WHERE[Active] = 1 and LastRun < DATEADD(dd, 0, DATEDIFF(dd, 0, GETUTCDATE()))";
var rows = _sqlHelper.ExecuteReader <Schedule>(_connectionStrings.Value.SentinelFmMain, sql, CommandType.Text);
return(rows);
}
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 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);
}
/// <summary>
/// Executes a command and returns a records reader containing the results.
/// </summary>
/// <param name="commandText">The command text.</param>
/// <param name="parameters">The parameters.</param>
/// <returns>
/// A data reader containing the results of the command.
/// </returns>
public IRecordsReader ExecuteReader(string commandText, params IParameter[] parameters)
{
foreach (ISqlHelperExtension extension in m_Extensions)
{
extension.OnExecuteReader(m_SqlHelper, ref commandText, ref parameters);
}
return(m_SqlHelper.ExecuteReader(commandText, parameters));
}
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 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);
}
/// <inheritdoc />
public IEnumerable <TEntity> GetAll()
{
var returnList = new List <TEntity>();
using (var con = sqlHelper.CreateConnection(this.connectionString))
{
var COMMAND = $"select * " +
$"from {typeof(TEntity).GetAttributeValue((DbTableAttribute dbTable) => dbTable.TableName)}";
using (var rdr = sqlHelper.ExecuteReader(con, COMMAND))
{
while (rdr.Read())
{
var art = this.Mapping(rdr);
returnList.Add(art);
}
}
}
return(returnList);
}
//here we do all the detail, and dig into each individual member
public void CalculateKarmaHistory()
{
string memberSql = "SELECT nodeId from cmsMember";
IRecordsReader rr = _sqlhelper.ExecuteReader(memberSql);
while (rr.Read())
{
ProcessMember(rr.GetInt("nodeId"));
}
}
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 void LoadDocTypesTest_DbCalled()
{
ISqlHelper sql = Isolate.Fake.Instance <ISqlHelper>(Members.ReturnRecursiveFakes);
DocTypeObjectBuilder target = new DocTypeObjectBuilder(string.Empty);
Isolate.WhenCalled(() => target.SqlHelper).WillReturn(sql);
target.LoadDocTypes();
Isolate.Verify.WasCalledWithAnyArguments(() => target.SqlHelper);
Isolate.Verify.WasCalledWithAnyArguments(() => sql.ExecuteReader(string.Empty));
}
public List <FieldSet> GetAllFieldSets(Page page)
{
string sql = "SELECT * FROM UFfieldsets where page = @id ORDER BY sortorder ASC";
IRecordsReader rr = sqlHelper.ExecuteReader(sql, sqlHelper.CreateParameter("@id", page.Id));
List <FieldSet> l = new List <FieldSet>();
while (rr.Read())
{
FieldSet fs = FieldSet.CreateFromDataReader(rr);
fs.Fields.AddRange(storage.GetAllFields(fs));
l.Add(fs);
}
rr.Dispose();
return(l);
}
public List <Form> GetAllForms(bool archived)
{
//SQL
string sql = "SELECT * FROM UFforms where archived = @archived order by name ASC;";
List <Form> l = new List <Form>();
IRecordsReader rr = sqlHelper.ExecuteReader(sql, sqlHelper.CreateParameter("@archived", archived));
while (rr.Read())
{
Guid id = rr.GetGuid("id");
Form f = Form.CreateFromDataReader(rr);
f.Pages.AddRange(pageStorage.GetAllPages(f));
l.Add(f);
}
rr.Dispose();
return(l);
}
public List <Workflow> GetActiveWorkFlows()
{
string sql = "SELECT * from UFworkflows where Active = 1 ORDER by Name ASC";
IRecordsReader rr = sqlHelper.ExecuteReader(sql);
List <Workflow> l = new List <Workflow>();
while (rr.Read())
{
Workflow wf = Workflow.CreateFromDataReader(rr);
wf.Settings = settings.GetSettings(wf.Id);
//if the type is missing, it will deactivate, the type will be missing if 3rd party dll is gone
//this way we avoid non-existing workflows from trying to execute
//the UI should reflect it this.
if (wf.Active)
{
l.Add(wf);
}
}
rr.Dispose();
return(l);
}
private void InitDomain(int id)
{
using (IRecordsReader dr = SqlHelper.ExecuteReader(
"select domainDefaultLanguage, domainRootStructureID, domainName from umbracoDomains where id = @ID",
SqlHelper.CreateParameter("@ID", id)))
{
if (dr.Read())
{
_id = id;
_language = new Language(dr.GetInt("domainDefaultLanguage"));
_name = dr.GetString("domainName");
_root = dr.GetInt("domainRootStructureID");
}
}
}
public RelationType(int id)
{
using (IRecordsReader dr = SqlHelper.ExecuteReader(
"select id, dual, name, alias from umbracoRelationType where id = @id", SqlHelper.CreateParameter("@id", id)))
{
if (dr.Read())
{
PopulateFromReader(dr);
}
else
{
throw new ArgumentException("Not RelationType found for id " + id.ToString());
}
}
}
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);
}