public static DataTable DisplayAllColumnsName()
{
DataTable tempTable = new DataTable();
try
{
using (SqlConnection connection = new SqlConnection(ConnectionString))
{
SqlCommand command = new SqlCommand("SELECT TABLE_NAME AS [Tabellens namn], COLUMN_NAME AS [Kolumnens namn] FROM INFORMATION_SCHEMA.COLUMNS " +
"JOIN TablesOfInterest AS[TI] ON INFORMATION_SCHEMA.COLUMNS.TABLE_NAME = TI.tableName; ", connection);
connection.Open();
//command.Prepare();
SqlDataReader dataReader = command.ExecuteReader();
if (dataReader.HasRows)
{
tempTable.Columns.Add("Tabellnamn", typeof(String));
tempTable.Columns.Add("Kolumnnamn", typeof(String));
while (dataReader.Read())
{
String tempOne = dataReader.GetString(0).ToString();
String tempTwo = dataReader.GetString(1).ToString();
tempTable.Rows.Add(new String[] { tempOne, tempTwo });
}
}
}
}
catch (SqlException e)
{
Controller_PCTwo.ErrorHandler(19171251);
MessageBox.Show(e.Message);
}
return(tempTable);
}
public static DataTable DisplayAllColumnsName(string specificTableName)
{
DataTable tempTable = new DataTable();
try
{
using (SqlConnection connection = new SqlConnection(ConnectionString))
{
string query = "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS " +
"JOIN TablesOfInterest ON INFORMATION_SCHEMA.COLUMNS.TABLE_NAME = TablesOfInterest.tableName " +
"WHERE TablesOfInterest.tableName = @Value";
SqlCommand command = new SqlCommand(query, connection);
connection.Open();
command.Prepare();
command.Parameters.AddWithValue("@Value", specificTableName);
SqlDataReader dataReader = command.ExecuteReader();
if (dataReader.HasRows)
{
tempTable.Columns.Add("Kolumnnamn", typeof(String));
while (dataReader.Read())
{
String tempOne = dataReader.GetString(0).ToString();
tempTable.Rows.Add(tempOne);
}
}
}
}
catch (SqlException e)
{
Controller_PCTwo.ErrorHandler(19171252);
MessageBox.Show(e.Message);
}
return(tempTable);
}
public static DataTable DisplayNumberOfRows()
{
DataTable tempTable = new DataTable();
try
{
using (SqlConnection connection = new SqlConnection(ConnectionString))
{
SqlCommand command = new SqlCommand("SELECT[Tables].name AS [Tabellens namn], SUM([Partitions].[rows]) AS[Antalet rader] " +
"FROM sys.tables AS[Tables] JOIN sys.partitions AS[Partitions] ON[Tables].[object_id] = [Partitions].[object_id] " +
"AND[Partitions].index_id IN(0, 1) JOIN TablesOfInterest ON[Tables].name = TablesOfInterest.tableName GROUP BY[Tables].name; ", connection);
connection.Open();
//command.Prepare();
SqlDataReader dataReader = command.ExecuteReader();
if (dataReader.HasRows)
{
tempTable.Columns.Add("Tabellnamn", typeof(String));
tempTable.Columns.Add("Antal rader", typeof(String));
while (dataReader.Read())
{
String tempOne = dataReader.GetString(0).ToString();
String tempTwo = dataReader.GetInt64(1).ToString();
tempTable.Rows.Add(new String[] { tempOne, tempTwo });
}
}
}
}
catch (SqlException e)
{
Controller_PCTwo.ErrorHandler(19171250);
MessageBox.Show(e.Message);
}
return(tempTable);
}
private void BtnViewAlternatives_Click(object sender, EventArgs e)
{
ClearAllFeedback();
DataTable table = new DataTable();
if (comboBoxViewAlternatives.Text == "Inga visningsalternativ ännu")
{
lblViewAlternativesNF.Text = "Ingen data har ännu hämtats";
lblViewAlternativesNF.Visible = true;
}
else
{
string temp = comboBoxViewAlternatives.SelectedItem.ToString();
if (temp.Contains("Alla kolumnnamn för "))
{
string tempString = temp.Substring(20, temp.Length - 20);
table = Controller_PCTwo.DisplayAllColumsNameForSpecificTable(tempString);
if (table.Rows.Count == 0)
{
lblViewAlternativesNF.Text = "Tabellen innehåller inga kolumnnamn";
lblViewAlternativesNF.Visible = true;
}
else
{
dataGridViewDisplayRetrievedData.DataSource = table;
lblViewAlternativesPF.Text = $"Tabellen {tempString} kolumnnamn visas";
lblViewAlternativesPF.Visible = true;
}
}
else if (temp.Contains("Antal rader per tabell"))
{
table = Controller_PCTwo.DisplayNumberOfRows();
dataGridViewDisplayRetrievedData.DataSource = table;
dataGridViewDisplayRetrievedData.DataSource = table;
lblViewAlternativesPF.Text = "Antal rader för respektive tabell i TablesOfInterest visas";
lblViewAlternativesPF.Visible = true;
}
else if (temp.Contains("Alla kolumnnamn"))
{
table = Controller_PCTwo.DisplayAllColumnsName();
dataGridViewDisplayRetrievedData.DataSource = table;
lblViewAlternativesPF.Text = "Alla kolumnnamn för respektive tabell i TablesOfInterest visas";
lblViewAlternativesPF.Visible = true;
}
}
}
private void BtnRetrieveData_Click(object sender, EventArgs e)
{
groupBoxViewAlternatives.Visible = true;
dataGridViewDisplayRetrievedData.DataSource = null;
ClearAllFeedback();
numberOfRows.Clear();
allColumnNames.Clear();
comboBoxViewAlternatives.Items.Clear();
comboBoxViewAlternatives.Items.AddRange(new string[] { "Antal rader per tabell",
"Alla kolumnnamn" });
comboBoxViewAlternatives.SelectedIndex = 0;
numberOfRows = Controller_PCTwo.DisplayNumberOfRows();
allColumnNames = Controller_PCTwo.DisplayAllColumnsName();
if (numberOfRows.Rows.Count == 0 || allColumnNames.Rows.Count == 0)
{
if (numberOfRows.Rows.Count == 0 && allColumnNames.Rows.Count > 0)
{
lblRetrieveDataNF.Text = "Finns inga rader i någon av tabellerna i TablesOfInterest";
lblRetrieveDataNF.Visible = true;
}
else if (numberOfRows.Rows.Count > 0 && allColumnNames.Rows.Count == 0)
{
lblRetrieveDataNF.Text = "Finns inga kolumnnamn i någon av tabellerna i TablesOfInterest";
lblRetrieveDataNF.Visible = true;
}
else
{
lblRetrieveDataNF.Text = "Finns inga rader eller kolumnnamn i någon av tabellerna i TablesOfInterest";
lblRetrieveDataNF.Visible = true;
}
}
else
{
lblRetrieveDataPF.Text = "Datan har hämtats, välj visningsalternativ nedan";
lblRetrieveDataPF.Visible = true;
foreach (DataRow row in numberOfRows.Rows)
{
string temp = "Alla kolumnnamn för " + row["Tabellnamn"].ToString();
comboBoxViewAlternatives.Items.Add(temp);
}
}
}
