private void rows_select_select_button_Click(object sender, EventArgs e)
{
if (rows_select_selectedColumns_listBox.Items.Count == 0 || string.IsNullOrWhiteSpace(dash_tables_listBox.SelectedItem?.ToString()))
{
HandleError("You must select a table columns.");
return;
}
if (rows_select_where_checkBox.Checked)
{
if (string.IsNullOrWhiteSpace(rows_select_whereColumn_comboBox.SelectedItem?.ToString()) || string.IsNullOrWhiteSpace(rows_select_whereOperator_comboBox.SelectedItem?.ToString()) || string.IsNullOrWhiteSpace(rows_select_whereValue_textBox.Text))
{
HandleError("All 'where' forms must have value.");
return;
}
}
var dto = new FormSelectDto()
{
TableName = $"{dash_statusStrip_schema_value.Text}.{dash_tables_listBox.SelectedItem?.ToString()}",
Columns = new List <ColumnDto>()
};
foreach (var col in rows_select_selectedColumns_listBox.Items)
{
dto.Columns.Add(new ColumnDto()
{
ColumnName = col.ToString().Split('(')[0].Trim(),
Value = col.ToString().Split('(')[1].TrimEnd(')')
});
}
if (rows_select_where_checkBox.Checked)
{
dto.Where = true;
dto.WhereColumn = new ColumnDto()
{
ColumnName = rows_select_whereColumn_comboBox.SelectedItem?.ToString().Split('(')[0].Trim(),
Value = rows_select_whereColumn_comboBox.SelectedItem?.ToString().Split('(')[1].TrimEnd(')')
};
dto.WhereOperator = rows_select_whereOperator_comboBox.SelectedItem?.ToString();
dto.WhereValue = rows_select_whereValue_textBox.Text;
}
try
{
var rows = _sql.GetRows(dto, connDto);
WriteToLog($"Successfully retrieved {rows.Count} rows");
WriteToLog("Opening data grid");
while (rows_select_selectedColumns_listBox.Items.Count > 0)
{
rows_select_availableColumns_listBox.Items.Add(rows_select_selectedColumns_listBox.Items[0]);
rows_select_selectedColumns_listBox.Items.RemoveAt(0);
}
rows_select_where_checkBox.Checked = false;
rows_select_whereColumn_comboBox.SelectedIndex = -1;
rows_select_whereOperator_comboBox.SelectedIndex = -1;
rows_select_whereValue_textBox.Text = string.Empty;
var data = new DataViewerDto()
{
Columns = new List <string>(),
Values = new List <List <string> >()
};
var uniqueColumns = rows.Select(x => x.ColumnName).Distinct();
data.Columns.AddRange(uniqueColumns);
var listToAdd = new List <string>();
for (int i = 0; i < rows.Count; i++)
{
listToAdd.Add(rows[i].Value);
if (i + 1 == uniqueColumns.Count() || (i + 1) % uniqueColumns.Count() == 0)
{
data.Values.Add(listToAdd);
listToAdd = new List <string>();
}
}
var dataGrid = new DataViewer(data);
dataGrid.Show();
}
catch (Exception ex)
{
HandleError("Error getting rows:", ex);
return;
}
}
public List <ColumnDto> GetRows(FormSelectDto dto, ConnectionDto connDto)
{
try
{
using var con = new NpgsqlConnection(ConnectionString(connDto));
con.Open();
using var cmd = new NpgsqlCommand();
cmd.Connection = con;
cmd.CommandText = "SELECT";
foreach (var col in dto.Columns)
{
cmd.CommandText += $" {col.ColumnName},";
}
cmd.CommandText = cmd.CommandText.TrimEnd(',');
cmd.CommandText += $" FROM {dto.TableName}";
if (dto.Where)
{
cmd.CommandText += $" WHERE {dto.WhereColumn.ColumnName} {dto.WhereOperator}";
if (RequiresQuotes(dto.WhereColumn.Value))
{
cmd.CommandText += $" '{dto.WhereValue}'";
}
else
{
cmd.CommandText += $" {dto.WhereValue}";
}
}
cmd.CommandText += ";";
using NpgsqlDataReader rdr = cmd.ExecuteReader();
var columns = new List <ColumnDto>();
while (rdr.Read())
{
for (int i = 0; i < dto.Columns.Count; i++)
{
columns.Add(new ColumnDto()
{
ColumnName = dto.Columns[i].ColumnName,
Value = rdr.GetValue(i).ToString()
});
}
}
con.Close();
return(columns);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
