public void Where8([IncludeDataSources(true, TestProvName.AllSqlServer2008Plus)] string context)
{
using (var db = GetDataContext(context))
{
var hid = SqlHierarchyId.Parse("/1/");
AreEqual(
SqlTypes.Data(context)
.Where(t => IsDescendantOf(hid, t.HID)),
db.GetTable <SqlTypes>()
.Where(t => IsDescendantOf(hid, t.HID) == true));
}
}
public void Where8([SqlServerDataContext] string context)
{
using (var db = GetDataContext(context))
{
var hid = SqlHierarchyId.Parse("/1/");
AreEqual(
SqlTypes.Data(context)
.Where(t => IsDescendantOf(hid, t.HID)),
db.GetTable <SqlTypes>()
.Where(t => IsDescendantOf(hid, t.HID) == true));
}
}
public void OperatorComparison()
{
var h1 = SqlHierarchyId.Parse("/9/2/");
var h2 = SqlHierarchyId.Parse("/9/1/");
var h3 = SqlHierarchyId.Parse("/9/1/");
Assert.IsTrue((bool)(h1 >= h2));
Assert.IsTrue((bool)(h2 <= h1));
Assert.IsFalse((bool)(h1 <= h2));
Assert.IsFalse((bool)(h2 >= h1));
Assert.IsFalse((bool)(h1 == h2));
Assert.IsTrue((bool)(h3 >= h2));
}
public void Where3(string context)
{
using (var db = GetDataContext(context))
{
var hid = SqlHierarchyId.Parse("/1/");
AreEqual(
SqlTypes.Data(context)
.Where(t => (bool)hid.IsDescendantOf(t.HID)),
db.GetTable <SqlTypes>()
.Where(t => (bool)hid.IsDescendantOf(t.HID)));
}
}
public async Task <ActionResult <Domain> > PutDomain(int id, Domain domain)
{
Domain toInsert = new Domain();
toInsert.DomainName = domain.DomainName;
toInsert.DomainTypeId = domain.DomainTypeId;
toInsert.Parentt = domain.Parentt;
toInsert.HighLevel = domain.HighLevel;
//if (domain.Parentt == "N/A")
//{
// SqlString level = new SqlString ( "/1/" );
// toInsert.Level = SqlHierarchyId.Parse("/1/");
//}else
//{
// //SqlString level = new SqlString("/1/2/");
// toInsert.Level = SqlHierarchyId.Parse("/1/3/");
//}
SqlHierarchyId id1 = new SqlHierarchyId();
if (id == 2)
{
id1 = SqlHierarchyId.Parse("/1/1/");
}
using (var stream = new MemoryStream())
{
using (var writer = new BinaryWriter(stream))
{
id1.Write(writer);
toInsert.Level = stream.ToArray();
}
}
//_context.Domains.Add(toInsert);
_context.Entry(toInsert).State = EntityState.Modified;
try
{
await _context.SaveChangesAsync();
}
catch (DbUpdateConcurrencyException)
{
if (!DomainExists(id))
{
return(NotFound());
}
else
{
throw;
}
}
return(NoContent());
}
private Object GetParameterValue(DataType dataType, Object sqlDbType, String udtTypeName)
{
switch (udtTypeName)
{
case "geometry": return(SqlGeometry.Point(137, 42, 4320));
case "geography": return(SqlGeography.Point(42, 135, 4326));
case "hierarchyid": return(SqlHierarchyId.Parse("/1/2/3/"));
default: break;
}
return(GetParameterValue(dataType, sqlDbType));
}
public void TestHierarchyID(string context)
{
using (var conn = new DataConnection(context))
{
var id = SqlHierarchyId.Parse("/1/3/");
Assert.That(conn.Execute <SqlHierarchyId> ("SELECT Cast('/1/3/' as hierarchyid)"), Is.EqualTo(id));
Assert.That(conn.Execute <SqlHierarchyId?>("SELECT Cast('/1/3/' as hierarchyid)"), Is.EqualTo(id));
Assert.That(conn.Execute <SqlHierarchyId> ("SELECT Cast(NULL as hierarchyid)"), Is.EqualTo(SqlHierarchyId.Null));
Assert.That(conn.Execute <SqlHierarchyId?>("SELECT Cast(NULL as hierarchyid)"), Is.EqualTo(null));
Assert.That(conn.Execute <SqlHierarchyId>("SELECT @p", new DataParameter("p", id)), Is.EqualTo(id));
}
}
protected void ListenForAJAX()
{
if (!String.IsNullOrEmpty(Request.Params["nodeId"]) && !String.IsNullOrEmpty(Request.Params["user"]))
{
Member User = Member.CurrentInCache;
Member DesiredReferral = new Member(Request.Params["user"]);
string NodeId = Request.Params["nodeId"].Replace('_', '/').Replace("PARENT-", "");
DesiredReferral.MatrixId = SqlHierarchyId.Parse(NodeId);
DesiredReferral.Save();
//Response.Redirect("~/user/refs/matrix.aspx");
}
}
public void Where6(string context)
{
using (var db = GetDataContext(context))
{
var hid = SqlHierarchyId.Parse("/1/");
AreEqual(
SqlTypes.Data(context)
.Where(t => (bool)(hid.IsDescendantOf(t.HID) == SqlBoolean.True) && t.ID != 1)
.OrderBy(c => c.HID),
db.GetTable <SqlTypes>()
.Where(t => (bool)(hid.IsDescendantOf(t.HID) == SqlBoolean.True) && t.ID != 1)
.OrderBy(c => c.HID));
}
}
public void Where6([IncludeDataSources(true, TestProvName.AllSqlServer2008Plus)] string context)
{
using (var db = GetDataContext(context))
{
var hid = SqlHierarchyId.Parse("/1/");
AreEqual(
SqlTypes.Data(context)
.Where(t => (bool)(hid.IsDescendantOf(t.HID) == SqlBoolean.True) && t.ID != 1)
.OrderBy(c => c.HID),
db.GetTable <SqlTypes>()
.Where(t => (bool)(hid.IsDescendantOf(t.HID) == SqlBoolean.True) && t.ID != 1)
.OrderBy(c => c.HID));
}
}
public void HIERARCHYID_Data_ScriptsCorrectly()
{
SqlHierarchyId baseData = SqlHierarchyId.Parse("/");
object data = baseData;
var fieldInfo = SchemaScriptingTests.FakeColumn("test", "test", 20, "sys.junk.hierarchyid", false, 0, 0);
Assert.AreEqual("0x", FieldScripting.valueAsTSQLLiteral(data, fieldInfo), "hierarchyid");
Assert.AreEqual("0x", FieldScripting.formatHierarchyId(data), "hierarchyid");
baseData = SqlHierarchyId.Parse("/1/");
data = baseData;
fieldInfo = SchemaScriptingTests.FakeColumn("test", "test", 20, "sys.junk.hierarchyid", false, 0, 0);
Assert.AreEqual("0x58", FieldScripting.valueAsTSQLLiteral(data, fieldInfo), "hierarchyid");
Assert.AreEqual("0x58", FieldScripting.formatHierarchyId(data), "hierarchyid");
}
public void Can_convert_hierarchyid_to_quoted_string()
{
var converter = new SqlServer.Converters.SqlServerHierarchyIdTypeConverter();
string stringValue = null;
var hierarchyId = SqlHierarchyId.Parse(stringValue); // NULL
var str = converter.ToQuotedString(typeof(SqlHierarchyId), hierarchyId);
Assert.AreEqual($"CAST(null AS {ColumnDefinition})", str);
stringValue = "/1/1/3/";
hierarchyId = SqlHierarchyId.Parse(stringValue); // 0x5ADE
str = converter.ToQuotedString(typeof(SqlHierarchyId), hierarchyId);
Assert.AreEqual($"CAST('{stringValue}' AS {ColumnDefinition})", str);
}
public static AccountDto MapToFromHierarchyEntity(AccountHierarchy acc)
{
return(new AccountDto()
{
Id = acc.Id,
Text = acc.Name,
PlacementPreference = acc.PlacementPreference,
Leg = acc.Leg,
ParentId = acc.ParentId,
UplinkId = acc.UplinkId,
Selectable = false,
Level = acc.LevelPath,
Depth = acc.LevelPath != "/" ? (int)SqlHierarchyId.Parse(acc.LevelPath).GetLevel() : 0,
Nodes = new List <AccountDto>()
});
}
public SqlHierarchyId GetMatrixFirstEmptyRight()
{
List <SqlHierarchyId> descendants = this.GetDirectDescendants().Select(x => x.MatrixId).ToList();
List <SqlHierarchyId> ancestors = new List <SqlHierarchyId>()
{
this.MatrixId
};
SqlHierarchyId result = SqlHierarchyId.Parse(this.MatrixId.ToString() + "2/");;
if (descendants.Contains(result))
{
Member rootRight = Member.GetByMatrixId(descendants.FirstOrDefault(x => (bool)(x == result)));
descendants = rootRight.GetDirectDescendants().Select(x => x.MatrixId).ToList();
ancestors = new List <SqlHierarchyId>()
{
rootRight.MatrixId
};
int level = 1;
int nodes = AppSettings.Matrix.MaxChildrenInMatrix;
while (descendants.Count == nodes)
{
level++;
nodes *= AppSettings.Matrix.MaxChildrenInMatrix;
ancestors = descendants.Clone();
descendants = rootRight.GetDescendantsLevel(level).Select(x => x.MatrixId).ToList();
}
for (int a = 0; a < ancestors.Count; a++)
{
for (int i = 0; i < AppSettings.Matrix.MaxChildrenInMatrix; i++)
{
result = SqlHierarchyId.Parse(ancestors[a].ToString() + ((i % AppSettings.Matrix.MaxChildrenInMatrix) + 1) + "/");
if (!descendants.Contains(result))
{
return(result);
}
}
}
}
return(result);
}
public async Task <IActionResult> Run(
[HttpTrigger(AuthorizationLevel.Anonymous, "get", Route = null)]
HttpRequest req,
ILogger log)
{
try
{
log.LogInformation("Getting document ...");
string fileName = req.Query["name"];
string content = req.Query["content"];
string nodeString = req.Query["node"].ToString();
SqlHierarchyId?node = !string.IsNullOrWhiteSpace(nodeString)
? SqlHierarchyId.Parse(nodeString)
: (SqlHierarchyId?)null;
string rowguidString = req.Query["rowguid"];
Guid? rowguid = !string.IsNullOrWhiteSpace(rowguidString)
? Guid.Parse(rowguidString)
: (Guid?)null;
var document = _documentService.Get(fileName, content, node, rowguid);
if (document == null)
{
throw new Exception("Document not found.");
}
if (document.DocumentData == null)
{
throw new Exception("Document is empty");
}
log.LogInformation($"Document '{document.FileName}' was successful loaded.");
return(new FileContentResult(document.DocumentData, "application/msword"));
}
catch (Exception e)
{
log.LogError("Error in document loading process", e);
throw;
}
}
public void Can_convert_to_and_from_HierarchyId_and_string_with_strong_typed_insert()
{
Db.DropAndCreateTable <HierarchyIdTable>();
var stringValue = "/5/4/1/";
var treeId = SqlHierarchyId.Parse(stringValue);
Db.Insert(new HierarchyIdTable()
{
TreeId = treeId, StringTreeId = stringValue, NullTreeId = SqlHierarchyId.Null
});
var result = Db.Select <FlippedHierarchyIdTable>().FirstOrDefault();
Assert.AreEqual(stringValue, result.StringTreeId);
Assert.AreEqual(treeId, result.TreeId);
Assert.AreEqual(null, result.NullTreeId);
}
public void Can_select_from_FileTable()
{
Db.DropAndCreateTable <FileTable>();
var ft = new FileTable
{
Name = "content.txt",
Path = SqlHierarchyId.Parse("/1/"),
FileContent = "contents".ToUtf8Bytes(),
FileType = MimeTypes.PlainText
};
Db.Insert(ft);
var fileTable = Db.Single <FileTable>(x => x.Name == "content.txt");
Assert.IsNotNull(fileTable);
Assert.IsTrue(ft.FileContent.EquivalentTo(fileTable.FileContent));
}
public override object FromDbValue(Type fieldType, object value)
{
if (value == null || value is DBNull)
{
return(SqlHierarchyId.Null);
}
if (value is SqlHierarchyId)
{
return((SqlHierarchyId)value);
}
if (value is string)
{
return(SqlHierarchyId.Parse(value.ToString()));
}
return(base.FromDbValue(fieldType, value));
}
private static object ParseSqlServerType(Type type, string value)
{
if (type == typeof(SqlHierarchyId))
{
return(SqlHierarchyId.Parse(value));
}
if (type == typeof(SqlGeography))
{
return(SqlGeography.Parse(value));
}
if (type == typeof(SqlGeometry))
{
return(SqlGeometry.Parse(value));
}
throw new InvalidOperationException("Unexpected {0}".FormatWith(type.Name));
}
public void FailToReadFromInvalidBinaryStream()
{
var stream = new MemoryStream();
var writer = new BinaryWriter(stream, System.Text.Encoding.UTF8, true);
var hierarchy = SqlHierarchyId.Parse(new System.Data.SqlTypes.SqlString("/1/"));
hierarchy.Write(writer);
//Add extra bytes after
for (int i = 0; i < 10; i++)
{
writer.Write(i);
}
writer.Close();
stream.Position = 0;
var reader = new BinaryReader(stream);
var hierarchy2 = new SqlHierarchyId();
AssertEx.ThrowsException(() => hierarchy2.Read(reader), typeof(HierarchyIdException), "24000: SqlHierarchyId operation failed because HierarchyId object was constructed from an invalid binary string. ");
}
public void TestHierarchyId(string context)
{
using (var conn = GetDataContext(context))
{
conn.GetTable <AllTypes2>()
.Where(t => (bool)(t.hierarchyidDataType.GetLevel() > 0))
.Select(t => new
{
v1 = SqlHierarchyId.GetRoot(),
v2 = Sql.ToSql(t.hierarchyidDataType.GetDescendant(SqlHierarchyId.Parse("/1/3/4/"), SqlHierarchyId.Parse("/1/3/5/"))),
v3 = t.hierarchyidDataType.IsDescendantOf(SqlHierarchyId.Parse("/1/")),
v4 = t.hierarchyidDataType.GetLevel(),
v5 = t.hierarchyidDataType.GetAncestor(0),
v6 = t.hierarchyidDataType.GetReparentedValue(SqlHierarchyId.Parse("/1/"), SqlHierarchyId.Parse("/2/")),
v7 = SqlHierarchyId.Parse("/1/2/3/4/5/"),
v8 = t.hierarchyidDataType.ToString(),
})
.ToList();
}
}
public void Can_convert_to_and_from_HierarchyId_and_string_with_anon_typed_insert()
{
Db.DropAndCreateTable <HierarchyIdTable>();
var dialect = Db.GetDialectProvider();
var tableName = dialect.GetQuotedTableName(ModelDefinition <HierarchyIdTable> .Definition);
var stringValue = "/2/3/6/";
var treeId = SqlHierarchyId.Parse(stringValue);
var sql = $"INSERT {tableName} (TreeId, StringTreeId, NullTreeId) VALUES (@TreeId, @StringTreeId, @NullTreeId);";
Db.ExecuteSql(sql, new { TreeId = treeId, StringTreeId = stringValue, NullTreeId = SqlHierarchyId.Null });
var result = Db.Select <FlippedHierarchyIdTable>().FirstOrDefault();
Assert.AreEqual(stringValue, result.StringTreeId);
Assert.AreEqual(treeId, result.TreeId);
Assert.AreEqual(null, result.NullTreeId);
}
public override object ToDbValue(Type fieldType, object value)
{
if (value == null || value is DBNull)
{
return(SqlHierarchyId.Null);
}
if (value is SqlHierarchyId)
{
return(value);
}
if (value is string)
{
var str = value as string;
return(SqlHierarchyId.Parse(str));
}
return(base.ToDbValue(fieldType, value));
}
public void CreateDirectoryAlreadyExistsTest()
{
// Arrange
var table = "MyTable";
var tableRoot = @"\MyTableDir";
var dirStructure = @"dir1\dir2\dir3";
var dirManager = new FileTableManager();
var fileTableRepo = new Mock <FileTableRepo>();
var stringId = "/0/";
var id = SqlHierarchyId.Parse(stringId);
fileTableRepo.Setup(m => m.FindPath(table, dirStructure, true, It.IsAny <SqlConnection>(), false)).Returns(id);
fileTableRepo.Setup(m => m.GetTableRootPath(table, 0, null)).Returns(tableRoot);
dirManager.FileTableRepo = fileTableRepo.Object;
// Act
var pathId = dirManager.CreateDirectory(table, dirStructure, null, true);
// Assert
Assert.AreEqual(stringId, pathId.ToString());
}
public void Can_insert_and_retrieve_HierarchyId()
{
using (var db = OpenDbConnection())
{
var stringValue = "/1/1/3/"; // 0x5ADE is hex
var treeId = SqlHierarchyId.Parse(stringValue);
db.Insert(new HierarchyTestTable()
{
TreeId = treeId, NullTreeId = SqlHierarchyId.Null
});
var result = db.Select <HierarchyTestTable>();
Assert.AreEqual(null, result[0].NullTreeId);
Assert.AreEqual(treeId, result[0].TreeId);
var parent = db.Column <SqlHierarchyId>("SELECT TreeId.GetAncestor(1) from HierarchyTestTable").First();
var str = parent.ToString();
Assert.AreEqual("/1/1/", str);
}
}
public void Can_select_from_FileStream()
{
using (var db = OpenDbConnection())
{
db.DropAndCreateTable <FileStream>();
db.Insert(new FileStream
{
ID = Guid.NewGuid(),
Name = "file.txt",
Path = SqlHierarchyId.Parse("/1/2/3/"),
ParentPath = SqlHierarchyId.Parse("/1/2/"),
FileContent = "contents".ToUtf8Bytes(),
FileType = MimeTypes.PlainText,
});
//db.Select<FileStream>().PrintDump();
var q = db.From <FileStream>();
db.Select(q);
}
}
public async Task <ActionResult <Domain> > PostDomain(Domaintest domain)
{
Domain toInsert = new Domain();
toInsert.DomainName = domain.DomainName;
toInsert.DomainTypeId = domain.DomainTypeId;
toInsert.Parentt = domain.Parentt;
toInsert.HighLevel = domain.HighLevel;
//if (domain.Parentt == "N/A")
//{
// SqlString level = new SqlString ( "/1/" );
// toInsert.Level = SqlHierarchyId.Parse("/1/");
//}else
//{
// //SqlString level = new SqlString("/1/2/");
// toInsert.Level = SqlHierarchyId.Parse("/1/3/");
//}
SqlHierarchyId id1 = SqlHierarchyId.Parse(domain.Path);
using (var stream = new MemoryStream())
{
using (var writer = new BinaryWriter(stream))
{
id1.Write(writer);
toInsert.Level = stream.ToArray();
}
}
_context.Domains.Add(toInsert);
try
{
await _context.SaveChangesAsync();
}
catch (Exception ex)
{
}
return(CreatedAtAction("GetDomain", new { id = toInsert.DomainId }, toInsert));
}
private void AddChildrens(Member member)
{
var children = member.GetDirectDescendants();
for (int i = 0; i < AppSettings.Matrix.MaxChildrenInMatrix; i++) //Matrix size = 2 (binary)
{
SqlHierarchyId result = SqlHierarchyId.Parse(member.MatrixId.ToString() + ((i % AppSettings.Matrix.MaxChildrenInMatrix) + 1) + "/");
if (children.Any(x => (bool)(x.MatrixId == result)))
{
Member child = children.First(x => (bool)(x.MatrixId == result));
AddNode(child);
AddEdge(member, child);
AddChildrens(child);
}
else
{
AddEmptyNode(member, i);
}
}
}
public async Task <ActionResult <List <Domaintest> > > GetDomains()
{
object domain = (from x in _context.Domains
select new
{
Id = x.DomainId,
Name = x.DomainName,
Type = x.DomainTypeId,
Parent = x.Parentt,
Path = x.Level.ToString(),
HighLevel = x.HighLevel
}).ToList();
//var type = domain.GetType();
//var strNode = (string) type.GetProperty("Path").GetValue(domain, null);
List <Domaintest> resultList = new List <Domaintest>();
dynamic myDyn = domain;
foreach (var dyn in myDyn)
{
int myId = dyn.Id;
string strName = dyn.Name;
int iType = dyn.Type;
string strParent = dyn.Parent;
string strNode = dyn.Path;
int hLevel = dyn.HighLevel;
SqlHierarchyId node = SqlHierarchyId.Parse(dyn.Path);
Domaintest result = new Domaintest(myId, strName, iType, strParent, strNode, hLevel);
resultList.Add(result);
}
return(resultList);
}
public static object WorkbookToDatabase(object workbookValue, Type expectedType)
{
if (workbookValue == null)
{
return(DBNull.Value);
}
if (workbookValue is string && string.IsNullOrEmpty(workbookValue.ToString()))
{
return(DBNull.Value);
}
if (expectedType != null)
{
switch (expectedType.FullName)
{
case "Microsoft.SqlServer.Types.SqlGeography":
return(SqlGeography.Parse(new SqlString(workbookValue.ToString())));
case "Microsoft.SqlServer.Types.SqlGeometry":
return(SqlGeometry.Parse(new SqlString(workbookValue.ToString())));
case "Microsoft.SqlServer.Types.SqlHierachyId":
return(SqlHierarchyId.Parse(new SqlString(workbookValue.ToString())));
case "System.DateTime":
if (workbookValue is double)
{
return(DateTime.FromOADate((double)workbookValue));
}
break;
}
}
return(workbookValue);
}
