public IList <EmployeeSimple> PaginateWithPageSize(string lastName, int page, int pageSize)
{
return(m_DataSource.From <EmployeeSimple>(new { lastName })
.WithSorting("FirstName", "EmployeeKey")
.WithLimits(page * pageSize, pageSize)
.ToCollection().Execute());
}
public Task <EmployeeClassification?> FindByNameAsync(string employeeClassificationName,
CancellationToken cancellationToken = default)
{
return(m_DataSource.From <EmployeeClassification>(
new { EmployeeClassificationName = employeeClassificationName }).Compile().ToObjectOrNull()
.ExecuteAsync(cancellationToken));
}
static void Issue213()
{
DataSource = new SqlServerDataSource("data source=.;initial catalog=Test;integrated security=True");
//DataSource.Sql("Truncate Table dbo.Items").Execute();
//var list = new List<Item>();
//for (var i = 0; i < 100; i++)
// for (var j = 0; j < 100; j++)
// {
// list.Add(new Item()
// {
// ItemNbr = "G" + i.ToString("000"),
// CategoryID = "J" + j.ToString("000"),
// ItemDesc = "G" + i.ToString("000") + "-" + "J" + j.ToString("000"),
// Price = i + j * 0.01M
// });
// }
//var sw = Stopwatch.StartNew();
//DataSource.InsertBulk("dbo.Items", list).Execute();
//sw.Stop();
//Console.WriteLine(sw.ElapsedMilliseconds.ToString("N0"));
var single2 = DataSource.From <Item>(new { ItemDesc = "G000-J064" }).ToObject <Item>(RowOptions.DiscardExtraRows).Execute();
var list2 = DataSource.From <Item>(new { ItemDesc = "G000-J064" }).ToCollection <Item>().Execute();
var searchFilter = DataSource.From <Item>().ToString("CategoryID").Execute(); //grab any value
var single1 = DataSource.From <Item>(new { CategoryID = searchFilter }).ToObject <Item>(RowOptions.DiscardExtraRows).Execute();
var list1 = DataSource.From <Item>(new { CategoryID = searchFilter }).ToCollection <Item>().Execute();
}
public IList <EmployeeSimple> SortBy(string lastName, string sortByColumn, bool isDescending)
{
var sortDirection = isDescending ? " DESC" : "";
return(m_DataSource.From <EmployeeSimple>(new { lastName })
.WithSorting(sortByColumn + sortDirection)
.ToCollection().Execute());
}
public void Example6_Chain()
{
SetupExample6();
var data = s_DataSource.From("PostsWithOwnersView").ToCollection<Post>().Execute();
var post = data.First();
Assert.AreEqual("Sams Post1", post.Content);
Assert.AreEqual(1, post.Id);
Assert.AreEqual("Sam", post.Owner.Name);
Assert.AreEqual(99, post.Owner.Id);
}
public IList <ProductLine> FindByName(string productLineName, bool includeProducts)
{
var results = m_DataSource.From <ProductLine>(new { productLineName }).ToCollection().Execute();
if (results.Count > 0 && includeProducts)
{
var children = m_DataSource.GetByKeyList(ProductTable, "ProductLineKey",
results.Select(pl => pl.ProductLineKey)).ToCollection <Product>().Execute();
foreach (var line in results)
{
line.Products.AddRange(children.Where(x => x.ProductLineKey == line.ProductLineKey));
}
}
return(results);
}
public SqlServerDataSource AttachSoftDeleteRulesWithUser(SqlServerDataSource source)
{
var currentUser1 = source.From(EmployeeTableName).WithLimits(1).ToObject <Employee>().Execute();
return(source.WithRules(
new SoftDeleteRule("DeletedFlag", true),
new UserDataRule("DeletedByKey", "EmployeeKey", OperationTypes.Delete),
new DateTimeRule("DeletedDate", DateTimeKind.Local, OperationTypes.Delete)
).WithUser(currentUser1));
}
static void Main(string[] args)
{
//Console.WriteLine("Hello World!");
var DataSource = new SqlServerDataSource("data source=.;initial catalog=Test;integrated security=True");
//DataSource.Sql("DROP TABLE dbo.Items").Execute();
DataSource.DatabaseMetadata.PreloadTables();
//if (!DataSource.DatabaseMetadata.GetTablesAndViews().Any(t => t.Name == "dbo.Items"))
//{
// DataSource.Sql(tableDef).Execute();
// var list = new List<Item>();
// for (var i = 0; i < 100; i++)
// for (var j = 0; j < 100; j++)
// {
// list.Add(new Item()
// {
// ItemNbr = "G" + i.ToString("000"),
// CategoryID = "J" + j.ToString("000"),
// ItemDesc = "G" + i.ToString("000") + "-" + "J" + j.ToString("000"),
// Price = i % 2 == 0 ? (decimal?)null : i + j * 0.01M
// });
// }
// DataSource.InsertBulk("dbo.Items", list).Execute();
//}
var searchFilter = DataSource.From <Item>().ToString("CategoryID").Execute(); //grab any value
var single1 = DataSource.From <Item>(new { CategoryID = searchFilter }).ToObject <Item>(RowOptions.DiscardExtraRows).Execute();
var list1 = DataSource.From <Item>(new { CategoryID = searchFilter }).ToCollection <Item>().Execute();
}
protected async Task CheckPermissionTagEditorAsync(IUser currentUser)
{
if (currentUser == null || currentUser.UserKey == 0)
{
throw new UnauthorizedAccessException("Please login.");
}
var result = await m_DataSource.From("Accounts.BookEditorDetail", new { currentUser.UserKey }).AsCount().ExecuteAsync();
if (result == 0)
{
throw new UnauthorizedAccessException("Permission denied to edit tags.");
}
}
static void ExportTable(SqlServerDataSource dataSource, string tableName, string filterSql = null, object filterObject = null, string fileName = null)
{
Table data;
if (filterSql != null)
{
data = dataSource.From(tableName, filterSql, filterObject).ToTable().Execute();
}
else if (filterObject != null)
{
data = dataSource.From(tableName, filterObject).ToTable().Execute();
}
else
{
data = dataSource.From(tableName).ToTable().Execute();
}
if (data.Rows.Count == 0)
{
return;
}
GenerateSql(dataSource, tableName, fileName, data.Rows);
}
protected async Task <bool> CanEditBookAsync(int bookKey, IUser currentUser)
{
if (currentUser == null)
{
return(false);
}
var result = await m_DataSource.From("dbo.BookEditor", new { bookKey, currentUser.UserKey }).AsCount().ExecuteAsync();
return(result > 0);
}
static void ExportTable_Sections(SqlServerDataSource dataSource, object filterObject, string fileName)
{
var tableName = "Sources.Section";
var data = dataSource.From(tableName, filterObject).ToTable().Execute();
if (data.Rows.Count == 0)
{
return;
}
//Sort Sections
var rows = new List <Dictionary <string, object> >();
foreach (var row in data.Rows.Where(r => r["ParentSectionKey"] is null).OrderBy(r => r["DisplayOrder"]))
{
rows.Add(new Dictionary <string, object>(row));
AddChildern(row, data, rows);
}
//Fix Slugs
foreach (var row in rows.Where(r => r["SectionSlug"] == null))
{
var name = (string)row["SectionName"];
var slug = new StringBuilder(name.Length);
var isSkipping = false;
foreach (var c in name)
{
if (c == '(')
{
isSkipping = true;
}
else if (c == ')')
{
isSkipping = false;
}
else if (!isSkipping)
{
slug.Append(c);
}
}
row["SectionSlug"] = slug.ToString().Trim().Replace(" ", "-").Replace(" ", "-");
}
GenerateSql(dataSource, tableName, fileName, rows);
}
private static async Task UploadLangeImages(DirectoryInfo folder, SqlServerDataSource dataSource, CloudBlobContainer imageContainer, CloudBlobContainer thumbnailContainer)
{
foreach (var imageFile in folder.GetFiles())
{
if (string.Equals(imageFile.Extension, ".png", StringComparison.InvariantCultureIgnoreCase))
{
Console.Write(imageFile.Name);
using (var trans = dataSource.BeginTransaction())
{
var record = new
{
ImageName = Path.GetFileNameWithoutExtension(imageFile.Name),
FileName = imageFile.Name,
FileSize = imageFile.Length,
CreatedByUserKey = -1,
ModifiedByUserKey = -1,
ImageSetKey = 1,
};
Console.Write("...database");
var imageKey = await dataSource.Insert("Images.Image", record).ToInt32().ExecuteAsync();
var storageName = await dataSource.From("Images.ImageDetail", new { imageKey }).ToString("StorageFileName").ExecuteAsync();
Console.Write("...storage");
using var stream = imageFile.OpenRead();
await UploadFileToStorageAsync(stream, storageName, imageContainer);
Console.Write("...more storage");
using var stream2 = CreateThumbnail(imageFile);
await UploadFileToStorageAsync(stream2, storageName, thumbnailContainer);
trans.Commit(); //Don't commit unless we were able to also upload the file
Console.WriteLine("...done!");
}
}
}
}
//This version returns a lightweight object known as a "Table". It is an alternative to .NET's DataTable.
public IReadOnlyList <IReadOnlyDictionary <string, object?> > GetAll(string schemaName, string tableName)
{
return(m_DataSource.From(schemaName + "." + tableName).ToTable().Execute().Rows);
}
public DataTable FindByFlags(bool isExempt, bool isEmployee)
{
return(m_DataSource.From(TableName, new { isExempt, isEmployee }).ToDataTable().Execute());
}
public async Task <EmployeeClassification> FindByNameAsync(string employeeClassificationName)
{
return(await m_DataSource.From(TableName, new { EmployeeClassificationName = employeeClassificationName }).ToObject <EmployeeClassification>().ExecuteAsync());
}
public DataTable GetAll(string schemaName, string tableName)
{
return(m_DataSource.From(schemaName + "." + tableName).ToDataTable().Execute());
}
public ReadOnlyEmployeeClassification?FindByName(string employeeClassificationName)
{
return(m_DataSource.From <ReadOnlyEmployeeClassification>(new { employeeClassificationName })
.ToObjectOrNull(RowOptions.InferConstructor).Execute());
}
public int CountByLastName(string lastName)
{
return((int)m_DataSource.From <EmployeeSimple>(new { lastName }).AsCount().Execute());
}
public IList <EmployeeSimple> GetAll()
{
return(m_DataSource.From <EmployeeSimple>().ToCollection().Execute());
}
public IList <SimpleDepartment> GetAll()
{
return(m_DataSource.From("HR.Department").ToCollection <SimpleDepartment>().Execute());
}
public EmployeeClassification FindByName(string employeeClassificationName)
{
return(m_DataSource.From(TableName, new { EmployeeClassificationName = employeeClassificationName }).ToObject <EmployeeClassification>().Execute());
}
/// <summary>
/// Fetches the complete set of elements and returns this set as an IEnumerable.
/// </summary>
/// <returns>the set fetched</returns>
public static IEnumerable <SalesOrderHeader> FetchSet()
{
return(DataSource.From("[Sales].[SalesOrderHeader]").ToCollection <SalesOrderHeader>().Execute());
}
public IList <EmployeeDetail> FindByEmployeeClassificationKey(int employeeClassificationKey)
{
return(m_DataSource.From <EmployeeDetail>(new { employeeClassificationKey }).ToCollection().Execute());
}
public List <int> GetDivisionKeys(int maxDivisionKey)
{
return(m_DataSource.From(TableName, "DivisionKey <= @MaxDivisionKey", new { maxDivisionKey })
.ToInt32List("DivisionKey").Execute());
}
public IList <Employee> GetAll()
{
return(m_DataSource.From(TableName).ToCollection <Employee>().Execute());
}
/// <summary>
/// Fetches the individual element
/// </summary>
/// <param name="key">The key of the element to fetch.</param>
/// <returns>The fetched element, or null if not found</returns>
public override SalesOrderHeader FetchIndividual(int key)
{
return(DataSource.From("[Sales].[SalesOrderHeader]", new { SalesOrderId = key }).ToObject <SalesOrderHeader>().Execute());
}
public IEmployeeClassification?GetClassification(int employeeClassificationKey)
{
return(m_DataSource.From <EmployeeClassification>(new { employeeClassificationKey })
.ToObject <EmployeeClassification>().Execute());
}
public IList <EmployeeSimple> FindByLastName(string lastName)
{
return(m_DataSource.From <EmployeeSimple>(new { lastName }).ToCollection().Execute());
}
public EmployeeClassification FindByNameOrException(string employeeClassificationName)
{
return(m_DataSource.From <EmployeeClassification>(new { employeeClassificationName })
.ToObject().Execute());
}
