//загрузка данных записей из таблиц бд при открытии формы
private void Parameters_Load(object sender, EventArgs e)
{
using (SqlConnection conn = new SqlConnection(@"Data Source=DESKTOP-9IUFQMM\SQLEXPRESS;Initial Catalog=PateticoPractic;Integrated Security=True"))
{
string query = "SELECT p.id_parameter, p.parameter FROM Parameters AS p";
try
{
conn.Open();
SqlCommand command = new SqlCommand(query, conn);
SqlDataReader reader = command.ExecuteReader();
DataTable dt = new DataTable();
dt.Load(reader);
conn.Close();
TableParameters.DataSource = dt;
TableParameters.Update();
TableParameters.Columns[0].Width = 100;
TableParameters.Columns[1].Width = 170;
TableParameters.Columns[0].HeaderText = "Номер категории";
TableParameters.Columns[1].HeaderText = "Название категории";
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
finally
{
conn.Close();
}
}
}
public IReadOnlyDictionary <long, StateData> Get(string language, params long[] ids)
{
var idsTable = TableParameters.GeIdsTable("Ids", ids.Distinct().ToArray());
var list = _executor.Array <dynamic>("[dbo].[State_GetOrderedList]", new TableParameters(idsTable));
var table = new DataTable("Localizations");
table.Columns.Add("Value");
table.Rows.Add(language);
var localizations = _executor.Array <dynamic>(
"[dbo].[StateLocalization_Get]",
new TableParameters(idsTable, table))
.GroupBy(x => x.StateId)
.ToDictionary(x => x.Key, x => x.ToDictionary(y => y.TwoLetterISOLanguageName, y => y.Name));
return(list.ToDictionary(x => (long)x.Id,
x => new StateData
{
Name = x.Name,
Position = x.Position,
LocalizedName = localizations[x.Id][language],
IsSystem = x.IsSystem
}));
}
public async Task <PageTableResult <Genre> > GetTableAsync(TableParameters tableParameters)
{
var query = _context.Genres.AsQueryable();
if (!string.IsNullOrWhiteSpace(tableParameters.SearchTerm))
{
query = query.Where(x => x.GenreName.StartsWith(tableParameters.SearchTerm));
}
var totalRecords = await query.CountAsync();
switch (tableParameters.OrderBy)
{
case nameof(Genre.GenreId):
query = tableParameters.OrderDirection ? query.OrderBy(x => x.GenreId) : query.OrderByDescending(x => x.GenreId);
break;
case nameof(Genre.GenreName):
default:
query = tableParameters.OrderDirection ? query.OrderBy(x => x.GenreName) : query.OrderByDescending(x => x.GenreName);
break;
}
query = query.Skip(tableParameters.PageSize * (tableParameters.PageNumber - 1)).Take(tableParameters.PageSize);
var itemResults = await query.ToListAsync();
return(new PageTableResult <Genre>
{
Items = itemResults,
TotalRecords = totalRecords
});
}
public async Task <PageTableResult <InfoData> > GetTableAsync(TableParameters tableParameters)
{
var query = _context.FileInfo.AsQueryable();
query = query.Include(x => x.FileData);
if (!string.IsNullOrWhiteSpace(tableParameters.SearchTerm))
{
query = query.Where(x => x.FileName.StartsWith(tableParameters.SearchTerm) ||
x.Extension.StartsWith(tableParameters.SearchTerm) ||
x.FileInfoId.ToString().StartsWith(tableParameters.SearchTerm) ||
x.Size.ToString().StartsWith(tableParameters.SearchTerm));
}
var totalRecords = await query.CountAsync();
switch (tableParameters.OrderBy)
{
case nameof(FileInfo.FileInfoId):
query = tableParameters.OrderDirection ? query.OrderBy(x => x.FileInfoId) : query.OrderByDescending(x => x.FileInfoId);
break;
case nameof(FileInfo.FileDataId):
query = tableParameters.OrderDirection ? query.OrderBy(x => x.FileDataId) : query.OrderByDescending(x => x.FileDataId);
break;
case nameof(FileInfo.Extension):
query = tableParameters.OrderDirection ? query.OrderBy(x => x.Extension) : query.OrderByDescending(x => x.Extension);
break;
case nameof(FileInfo.Size):
query = tableParameters.OrderDirection ? query.OrderBy(x => x.Size) : query.OrderByDescending(x => x.Size);
break;
case nameof(FileInfo.FileName):
default:
query = tableParameters.OrderDirection ? query.OrderBy(x => x.FileName) : query.OrderByDescending(x => x.FileName);
break;
}
query = query.Skip(tableParameters.PageSize * (tableParameters.PageNumber - 1)).Take(tableParameters.PageSize);
var itemResults = await query.ToListAsync();
var mappedResults = itemResults.Select(item => new InfoData
{
FileInfoId = item.FileInfoId,
FileDataId = item.FileDataId,
Extension = item.Extension,
Size = item.Size,
FileName = item.FileName
}).ToList();
return(new PageTableResult <InfoData>
{
Items = mappedResults,
TotalRecords = totalRecords
});
}
private static TableParameters CreateRequestForPage(int pageNumber)
{
var requestParameters = new TableParameters(
new PagingParameters(pageNumber, PageSize),
new OrderingParameters(ExpressionReflection.Property <ComponentInterface>(x => x.Name).Name, SortDirection.Descending));
return(requestParameters);
}
// GET: api/ComponentInterface
public async Task <PagedResult <ComponentInterfaceModel> > Get(TableParameters parameters)
{
var results = await _componentInterfaceService.GetComponentInterfaces(parameters);
return(new PagedResult <ComponentInterfaceModel>(
results.PagingParameters, results.CountTotal,
Enumerable.ToList(results.Items.Select(Mapper.Map <ComponentInterfaceVO, ComponentInterfaceModel>))));
}
public Table(TableParameters parameters)
{
Parameters = parameters;
foreach (var row in Parameters.Rows)
{
row.Table = this;
}
}
public Dictionary <long, decimal> GetTariffs(long[] ids)
{
var table = TableParameters.GeIdsTable("Ids", ids);
var tariffs = _executor.Array <dynamic>("[dbo].[Sender_GetTariffs]", new TableParameters(table));
return(tariffs.ToDictionary(x => (long)x.Id, x => (decimal)x.TariffOfTapePerBox));
}
public void GetComponentInterfaces_SortByNotExistingColumn_ShouldThrowInvalidInputException()
{
//Arrange
FillRepositoryWithFakes(ListSize);
var requestParameters = new TableParameters(new PagingParameters(0, 5),
new OrderingParameters(Guid.NewGuid().ToString(), SortDirection.Ascending));
//Assert
UtilsAssert.AssertThrowsAggregateException <InvalidInputException>(
() => Service.GetComponentInterfaces(requestParameters).Wait());
}
public void SetStateVisibilities(long stateId, RoleType[] roles)
{
if (roles == null)
{
throw new ArgumentNullException("roles");
}
var table = TableParameters.GeIdsTable("Roles", roles.Select(x => (long)x).ToArray());
_executor.Execute("[dbo].[State_SetStateVisibilities]", new TableParameters(new { stateId }, table));
}
static void Main(string[] args) {
var tempTables = new TableParameters();
var session = new DbProfilingSession(tempTables);
var dataContext = new DataContext(session.Connect(new SqlConnection("Server=.;Integrated Security=SSPI")));
using(tempTables.Add(GenerateJunk(5))) {
foreach(var row in dataContext.GetTable<MyTable>())
Console.Write("{0}", row.Value);
}
}
/// <summary>
/// Cria campos ocultos com informa????es sobre essa datapage usados para controle em javascript
/// </summary>
public void CreateHiddenFields()
{
RegisterTelerikHiddenField("__PAGENUMBER", DataProvider.PageNumber.ToString());
RegisterTelerikHiddenField("__PAGECOUNT", DataProvider.PageCount.ToString());
RegisterTelerikHiddenField("__TABLENAME", TableName);
RegisterTelerikHiddenField("__DATABASENAME", DatabaseName);
RegisterTelerikHiddenField("__PAGESTATE", PageState.ToString());
RegisterTelerikHiddenField("__PAGENAME", PageName);
RegisterTelerikHiddenField("__PERMISSION", Permission);
RegisterTelerikHiddenField("__TABLEPARAMETER", TableParameters.ToString().ToLower());
RegisterTelerikHiddenField("Targets", "");
}
public async Task <PageTableResult <MovieModel> > GetTableAsync(TableParameters tableParameters)
{
var query = _context.Movie.AsQueryable();
if (!string.IsNullOrWhiteSpace(tableParameters.SearchTerm))
{
query = query.Where(x => x.Language.StartsWith(tableParameters.SearchTerm) ||
x.MovieName.Contains(tableParameters.SearchTerm) ||
x.MovieLength.ToString().StartsWith(tableParameters.SearchTerm) ||
(x.Year.HasValue && x.Year.ToString().StartsWith(tableParameters.SearchTerm)));
}
var totalRecords = await query.CountAsync();
switch (tableParameters.OrderBy)
{
case nameof(Movie.MovieId):
query = tableParameters.OrderDirection ? query.OrderBy(x => x.MovieId) : query.OrderByDescending(x => x.MovieId);
break;
case nameof(Movie.MovieLength):
query = tableParameters.OrderDirection ? query.OrderBy(x => x.MovieLength) : query.OrderByDescending(x => x.MovieLength);
break;
case nameof(Movie.Language):
query = tableParameters.OrderDirection ? query.OrderBy(x => x.Language) : query.OrderByDescending(x => x.Language);
break;
case nameof(Movie.Year):
query = tableParameters.OrderDirection ? query.OrderBy(x => x.Year) : query.OrderByDescending(x => x.Year);
break;
case nameof(Movie.MovieName):
default:
query = tableParameters.OrderDirection ? query.OrderBy(x => x.MovieName) : query.OrderByDescending(x => x.MovieName);
break;
}
query = query.Skip(tableParameters.PageSize * (tableParameters.PageNumber - 1)).Take(tableParameters.PageSize);
var itemResults = await query.ToListAsync();
var mappedResults = itemResults.Select(item => new MovieModel(item)).ToList();
return(new PageTableResult <MovieModel>
{
Items = mappedResults,
TotalRecords = totalRecords
});
}
public ReadOnlyCollection <FileInfo> GetNames(long applicationId, ApplicationFileType type)
{
var idsTable = TableParameters.GeIdsTable("AppIds", new[] { applicationId });
var parameters = new TableParameters(new { TypeId = type }, idsTable);
var names = _executor.Array <dynamic>("[dbo].[ApplicationFile_GetNames]", parameters)
.Select(x => new FileInfo
{
Name = x.Name,
Id = x.Id
}).ToArray();
return(new ReadOnlyCollection <FileInfo>(names));
}
public async Task <IActionResult> GetInfractionsAsync([FromBody] TableParameters tableParams)
{
var sortingCriteria = tableParams.Sort.ToInfractionSummarySortingCriteria();
var searchCriteria = tableParams.Filters.ToInfractionSearchCriteria();
searchCriteria.GuildId = UserGuild.Id;
var pagingCriteria = tableParams.ToPagingCriteria();
var result = await ModerationService.SearchInfractionsAsync(
searchCriteria,
sortingCriteria,
pagingCriteria);
var outranksValues = new Dictionary <ulong, bool>();
foreach (var(guildId, subjectId) in result.Records
.Select(x => (guildId: x.GuildId, subjectId: x.Subject.Id))
.Distinct())
{
outranksValues[subjectId]
= await ModerationService.DoesModeratorOutrankUserAsync(guildId, SocketUser.Id, subjectId);
}
var mapped = result.Records.Select(
x => new InfractionData
{
Id = x.Id,
GuildId = x.GuildId,
Type = x.Type,
Reason = x.Reason,
Duration = x.Duration,
Subject = x.Subject,
CreateAction = x.CreateAction,
RescindAction = x.RescindAction,
DeleteAction = x.DeleteAction,
CanRescind
= x.RescindAction is null &&
x.DeleteAction is null &&
(x.Type == InfractionType.Mute || x.Type == InfractionType.Ban) &&
outranksValues[x.Subject.Id],
CanDelete
= x.DeleteAction is null &&
outranksValues[x.Subject.Id],
}).ToArray();
public async Task <IActionResult> DeletedMessagesAsync([FromBody] TableParameters tableParams)
{
var sortProperty = DeletedMessageSummary.SortablePropertyNames.FirstOrDefault(
x => x.Equals(tableParams.Sort.Field, StringComparison.OrdinalIgnoreCase)) ?? nameof(DeletedMessageSummary.Created);
var searchCriteria = new DeletedMessageSearchCriteria()
{
GuildId = UserGuild.Id
};
foreach (var filter in tableParams.Filters)
{
searchCriteria.WithPropertyValue(filter.Field, filter.Value);
}
var result = await ModerationService.SearchDeletedMessagesAsync(searchCriteria,
new[]
{
new SortingCriteria
{
PropertyName = sortProperty,
Direction = tableParams.Sort.Direction,
}
},
new PagingCriteria
{
FirstRecordIndex = tableParams.Page *tableParams.PerPage,
PageSize = tableParams.PerPage,
});
var mapped = new
{
result.TotalRecordCount,
result.FilteredRecordCount,
Records = result.Records.Select(
x => new
{
Channel = x.Channel.Name,
Author = x.Author.GetFullUsername(),
x.Created,
CreatedBy = x.CreatedBy.GetFullUsername(),
x.Content,
x.Reason,
x.BatchId,
}),
};
return(Ok(mapped));
}
public ReadOnlyDictionary <long, ReadOnlyCollection <FileInfo> > GetInfo(long[] awbIds, AwbFileType type)
{
var idsTable = TableParameters.GeIdsTable("AwbIds", awbIds);
var parameters = new TableParameters(new { TypeId = type }, idsTable);
var dictionary = _executor.Array <dynamic>("[dbo].[AwbFile_GetNames]", parameters)
.GroupBy(x => (long)x.AwbId)
.ToDictionary(a => a.Key,
a =>
{
var infos = a.Select(x => new FileInfo
{
Id = x.Id,
Name = x.Name
}).ToArray();
return(new ReadOnlyCollection <FileInfo>(infos));
});
return(new ReadOnlyDictionary <long, ReadOnlyCollection <FileInfo> >(dictionary));
}
public void Set(EventType eventType, RoleType[] recipients)
{
var table = TableParameters.GeIdsTable("Recipients", recipients.Select(x => (long)x).ToArray());
_executor.Execute("[dbo].[EventEmailRecipient_Set]", new TableParameters(new { EventTypeId = eventType }, table));
}
public void SetCities(long carrierId, long[] cities)
{
var table = TableParameters.GeIdsTable("CityIds", cities);
_executor.Execute("[dbo].[CarrierCity_Set]", new TableParameters(new { carrierId }, table));
}
protected void PageShow(FormPositioningEnum Positioning, bool ShouldClearFields, bool RebindGrid, bool UpdateFromUI)
{
if (PageState == FormStateEnum.Navigation && CanView)
{
DisableEnableContros(false); // Habilita Enable ou Disable dos controles da tela
}
if (PageOperations.None)
{
if (PageHolder != null)
{
PageHolder.Visible = false;
}
DefinePageContent(null);
InitializeAlias(null);
FillAuxiliarTables();
ShowFormulas();
return;
}
bool IsInsertig = (PageState == FormStateEnum.Insert);
GeneralDataProviderItem Item;
if (!IsInsertig)
{
Item = GetCurrentItem(Positioning, UpdateFromUI);
if (Item == null && OpenInEditMode && !IsPostBack && PageState == FormStateEnum.Edit && CanView)
{
PageState = FormStateEnum.Navigation;
PageShow(FormPositioningEnum.First);
return;
}
// Para tabelas que sejam do tipo "Par??metros" vamos verificar se existe um registro.
// Caso n??o exista nenhum registro, iremos inserir um com valores default.
if (TableParameters.ToString().ToLower().Equals("true") && Item == null)
{
Item = GetDataProviderItem(DataProvider);
foreach (FieldBase Field in Item.Fields.Values)
{
if (Field is IntegerField || Field is LongField || Field is DecimalField)
{
Field.Value = 0;
}
if (Field is TextField || Field is DateField || Field is MemoField)
{
Field.Value = " ";
}
if (Field is BooleanField)
{
Field.Value = false;
}
if (Field is DateField)
{
Field.Value = new DateTime(1901, 01, 01);
}
}
DataProvider.InsertItem(Item);
Item = DataProvider.SelectItem(PageNumber, Positioning);
}
SetOldParameters(Item);
DefinePageContent(Item);
if (Item != null && PageOperations.AllowRead)
{
if (!CanView)
{
ClearFields();
ShowInitialValues();
ShowFormulas();
}
}
else
{
ClearFields();
InitializeAlias(Item);
FillAuxiliarTables();
ShowInitialValues();
ShowFormulas();
}
}
if (IsInsertig || (DataProvider.PageCount == 0 && sWhere == "" && PageInsert) || (!CanView && PageInsert))
{
PageState = FormStateEnum.Insert;
DataProvider.PageNumber = PageNumber; // guarda o n??mero do registro para, caso cancelar a inclus??o, recuperar depois
Item = GetDataProviderItem(DataProvider);
ClearFields(ShouldClearFields);
//vamos reposicionar o item para atualizar formulas, etc etc etc etc etc ...
DataProvider.LocateRecord(Item);
}
if (DataProvider.PageCount > 0 || PageState == FormStateEnum.Insert)
{
SetStateGrid();
}
CreateHiddenFields();
if (RebindGrid)
{
GridRebind();
}
if (Positioning != FormPositioningEnum.Current && Positioning != FormPositioningEnum.None)
{
if (IsPostBack)
{
ResetDataList();
ResetRepeaterList();
}
}
}
public static PagingCriteria ToPagingCriteria(this TableParameters tableParameters)
=> new PagingCriteria()
{
FirstRecordIndex = tableParameters.Page * tableParameters.PerPage,
PageSize = tableParameters.PerPage,
};
//удаление данных выбранной записи из таблицы бд
private void roundBtnDelete_Click(object sender, EventArgs e)
{
if (TableParameters.SelectedRows.Count == 1)
{
ID = int.Parse(TableParameters.Rows[TableParameters.CurrentRow.Index].Cells[TableParameters.CurrentCell.ColumnIndex].Value.ToString());
string message = $"Вы действительно хотите удалить данную запись?\nОтменить это действие будет невозможно";
string caption = "Удаление записи";
MessageBoxButtons buttons = MessageBoxButtons.YesNo;
DialogResult result;
result = MessageBox.Show(message, caption, buttons);
if (result == DialogResult.Yes)
{
using (SqlConnection conn = new SqlConnection(@"Data Source=DESKTOP-9IUFQMM\SQLEXPRESS;Initial Catalog=PateticoPractic;Integrated Security=True"))
{
try
{
conn.Open();
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = "DELETE FROM [Parameters] WHERE id_parameter = @ID";
cmd.Parameters.AddWithValue(@"ID", ID);
cmd.ExecuteScalar();
TableParameters.Refresh();
MessageBox.Show("Запись удалена!");
SqlDataAdapter dataAdapter = new SqlDataAdapter("SELECT p.id_parameter, p.parameter FROM Parameters AS p", conn);
DataTable dataTable = new DataTable();
dataAdapter.Fill(dataTable);
TableParameters.DataSource = dataTable;
TableParameters.Update();
TableParameters.Columns[0].Width = 200;
TableParameters.Columns[1].Width = 200;
TableParameters.Columns[0].HeaderText = "Номер категории";
TableParameters.Columns[1].HeaderText = "Название категории";
TableParameters.Refresh();
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
finally
{
conn.Close();
}
}
}
else
{
MessageBox.Show("Запись не удалена!");
}
}
else
{
if (TableParameters.SelectedRows.Count == 0)
{
MessageBox.Show("Вы не выбрали строку для удаления!");
}
else
{
MessageBox.Show("Выберите ТОЛЬКО ОДНУ строку для удаления!");
}
}
}
public async Task <IActionResult> GetTableAsync([FromQuery] TableParameters tableParameters)
{
var result = await _service.GetTableAsync(tableParameters);
return(Ok(result));
}
public TransitData[] Get(params long[] ids)
{
var idsTable = TableParameters.GeIdsTable("Ids", ids.Distinct().ToArray());
return(_executor.Array <TransitData>("[dbo].[Transit_Get]", new TableParameters(idsTable)));
}
public async Task <PagedResult <ComponentInterfaceVO> > GetComponentInterfaces(TableParameters parameters)
{
var countTotal = await CountTotal();
if (countTotal == 0)
{
return(EmptyResult);
}
var pagingParameters = CorrectPagingParameters(parameters.PagingParameters, countTotal);
var results = await SublistInterfaces(parameters.OrderingParameters, pagingParameters);
return(new PagedResult <ComponentInterfaceVO>(pagingParameters, countTotal, results));
}
public void SetCountries(long senderId, long[] countries)
{
var table = TableParameters.GeIdsTable("CountryIds", countries);
_executor.Execute("[dbo].[SenderCountry_Set]", new TableParameters(new { senderId }, table));
}
