private void InitializeQueries()
{
_findKeywordQuery = SqlServerUtilities.GetQuery("Keyword.Find");
_insertLinkQuery = SqlServerUtilities.GetQuery("Keyword.InsertLink");
_insertKeywordQuery = SqlServerUtilities.GetQuery("Keyword.Insert");
_checkLinkQuery = SqlServerUtilities.GetQuery("Keyword.CheckLink");
}
private async void ServerButton_Click(object sender, EventArgs e)
{
var columnName = _scaffoldBuilder.ServerName.ToLower().Contains("sqlexpress") ? "InstanceName" : "ServerName";
var ops = new SqlServerUtilities();
var serverDataTable = await ops.SqlServerInstances().ConfigureAwait(false);
var serverNameList = serverDataTable.AsEnumerable()
.Where(row => !string.IsNullOrWhiteSpace(row.Field <string>(columnName)))
.Select(row => row.Field <string>(columnName)).ToList();
var serverForm = new ServersForm(serverNameList);
if (serverForm.ShowDialog() == DialogResult.OK)
{
if (string.IsNullOrWhiteSpace(serverForm.ServerName))
{
return;
}
ServerNameTextBox.Invoke(serverForm.ServerName == "SQLEXPRESS"
? new Action(() => ServerNameTextBox.Text = $@".\{serverForm.ServerName}")
: new Action(() => ServerNameTextBox.Text = $"{serverForm.ServerName}"));
SaveApplicationSettings();
}
}
private void InitializeQueries()
{
_findAllQuery = SqlServerUtilities.GetQuery("Image.FindAll");
_findByIdQuery = SqlServerUtilities.GetQuery("Image.FindById");
_incrementAndFindByIdQuery = SqlServerUtilities.GetQuery("Image.Increment") + _findByIdQuery;
_insertQuery = SqlServerUtilities.GetQuery("Image.Insert");
}
public async Task InitializeAsync(CancellationToken cancellationToken)
{
using (var dbConnection = Connection())
{
dbConnection.Open();
// create all tables. all these table files should have existance checks in them
var initializationSql = SqlServerUtilities
.GetEmbeddedFileNames(SqlInitializationEmbeddedLocation)
.OrderBy(fileName => int.Parse(fileName.Replace(SqlInitializationEmbeddedLocation + ".", "")
.Replace(".sql", "")
.Split('-')
.First()))
.ToList()
.Select(SqlServerUtilities.GetEmbeddedFile).SelectMany(
filecontents => Regex.Split(filecontents, @"^GO.*$", RegexOptions.Multiline)
.Where(s => !string
.IsNullOrWhiteSpace(
s)) // we split the file on the GO statements and run each section as its own query
).ToArray();
foreach (var sql in initializationSql)
{
// execute each update
await dbConnection.ExecuteAsync(sql);
}
}
await MigrateAsync(cancellationToken);
}
private void InitializeQueries()
{
_searchSql = SqlServerUtilities.GetQuery("Search.Search");
_randomSql = SqlServerUtilities.GetQuery("Search.Random");
_topSql = SqlServerUtilities.GetQuery("Search.Top");
_recentSql = SqlServerUtilities.GetQuery("Search.Recent");
_totalsSql = SqlServerUtilities.GetQuery("Search.Totals");
_keywordsSql = SqlServerUtilities.GetQuery("Search.Keywords");
}
public async Task <Image> FindByImageIdAsync(string imageId, CancellationToken cancellationToken,
bool increment = true)
{
using (var dbConnection = Connection())
{
dbConnection.Open();
var lookupKeywords = new Dictionary <Guid, Image>();
var results = await dbConnection.QueryAsync <Image, Keyword, Image>(
increment?_incrementAndFindByIdQuery : _findByIdQuery,
(img, keyword) => SqlServerUtilities.FormatSqlResultsData(img, keyword, lookupKeywords),
new
{
Id = imageId
});
return(results.DistinctBy(s => s.Id).FirstOrDefault());
}
}
public async Task MigrateAsync(CancellationToken cancellationToken)
{
using (var dbConnection = Connection())
{
dbConnection.Open();
var migrationSqlFilenames = SqlServerUtilities
.GetEmbeddedFileNames(SqlMigrationsEmbeddedLocation)
.OrderBy(filename => int.Parse(filename.Replace(SqlMigrationsEmbeddedLocation + ".", "")
.Replace(".sql", "").Split('-').First())).ToDictionary(
key => int.Parse(key.Replace(SqlMigrationsEmbeddedLocation, "").Replace(".sql", "").Split('-')
.First()), value => value);
var lastMigration = 0;
// these should be run in numerical order
foreach (var sqlFiles in migrationSqlFilenames)
{
if (await CheckAsync(cancellationToken) <= sqlFiles.Key)
{
continue;
}
var fileContents = SqlServerUtilities.GetEmbeddedFile(sqlFiles.Value);
// we split the file on the GO statements and run each section as its own query
var sqlQueries = Regex.Split(fileContents, @"^GO.*$", RegexOptions.Multiline)
.Where(s => !string.IsNullOrWhiteSpace(s));
foreach (var sql in sqlQueries)
{
// execute each update
await dbConnection.ExecuteAsync(sql);
}
lastMigration = sqlFiles.Key;
}
// update to current migration
await dbConnection.ExecuteAsync(
"UPDATE [Configuration] SET [Value] = @lastMigration WHERE [Id] = 'MigrationVersion'",
new { lastMigration });
}
}
public void TestMapper()
{
Stopwatch sw = new Stopwatch();
sw.Start();
for (int i = 0; i < 100; i++)
{
var user = new User();
user.ID = 78;
user.Password = "******";
MapperPropertyInfo[] mappers = SqlServerUtilities.GetMapperPropertyInfo <User>(user);
var user1 = new User();
MapperPropertyInfo[] mappers1 = SqlServerUtilities.GetMapperPropertyInfo <User>(null);
var users3 = new Users();
MapperPropertyInfo[] mapperss3 = SqlServerUtilities.GetMapperPropertyInfo <Users>(null);
}
sw.Stop();
long aaa = sw.ElapsedMilliseconds;
}
private void InitializeQueries()
{
_insertImageScrapeQuery = SqlServerUtilities.GetQuery("Scrape.Insert");
_searchQuery = SqlServerUtilities.GetQuery("Scrape.Search");
_searchHashQuery = SqlServerUtilities.GetQuery("Scrape.SearchHash");
}
public override async Task <IEnumerable <Image> > FindAllAsync(CancellationToken cancellationToken)
{
using (var dbConnection = Connection())
{
dbConnection.Open();
var lookupKeywords = new Dictionary <Guid, Image>();
return(await dbConnection.QueryAsync <Image, Keyword, Image>(_findAllQuery,
(img, keyword) => SqlServerUtilities.FormatSqlResultsData(img, keyword, lookupKeywords)
));
}
}
public async Task <List <SearchResult> > TopImageSearchAsync(TopRequest topRequest,
CancellationToken cancellationToken)
{
using (var dbConnection = Connection())
{
dbConnection.Open();
var lookupKeywords = new Dictionary <Guid, Image>();
var results = await dbConnection.QueryAsync <Image, Keyword, Image>(_topSql,
(img, keyword) => SqlServerUtilities.FormatSqlResultsData(img, keyword, lookupKeywords),
topRequest
);
return(results.DistinctBy(i => i.ImageId).Select(image => new SearchResult(image)).ToList());
}
}
