Question 113340 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – How to show tables names and for each table rows count wpf c#

I am trying to finish my graduation project which is a desktop application for database transfer. The application was made by C# WPF.
I want to introduce a feature in the application which is Quality Assurance, and it should be done as follows:
When transfering a specific database, a message must be shown with the names of the tables in the database and the number of data rows in each table.
I searched a lot for a solution to the problem but couldn’t find anything specific.
Can someone please write me the code for this feature in csharp?

here is xaml file:

                <DataGrid Name="DataGridTable">
                    <DataGrid.Columns>
                        <DataGridTextColumn x:Name="DaGrTableName" Header="Table Name"/>
                        <DataGridTextColumn x:Name="DaGrRowsCount" Header="Row Count"/>
                    </DataGrid.Columns>
                </DataGrid>

enter image description here

I tried with the following code, but it show just a message with number of tables and number of rows just from first table:

                    int rowsNbr = 0;
                    using MySqlDataReader mySqlDataReader = cmd.ExecuteReader();
                    while (mySqlDataReader.Read())
                    {
                        ++rowsNbr;
                    }

                    int tableCount = 0;
                    string countTable = $"SELECT TABLE_NAME, SUM(TABLE_ROWS) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = '{dbName}' GROUP BY TABLE_NAME;";
                    using MySqlConnection tableConn = new(connString);
                    using MySqlCommand tableComm = new(countTable);
                    tableComm.Connection = tableConn;
                    tableConn.Open();
                    using MySqlDataReader tableReader = tableComm.ExecuteReader();
                    while (tableReader.Read())
                    {
                        ++tableCount;
                    }
                    transferedTextBlock.Text = $"{rowsNbr} Data Rows and {tableCount} Tables have been successfully transfered.";

The result should be displayed like this:

enter image description here

Tags:

2

Answers


  1. 0

    I would suggest populating the DataGrid by binding it to an item source. In my project, a simple datagrid works like this

    <DataGrid Name="DataGridTable"
          AutoGenerateColumns="False"
          ItemsSource="{Binding TableNameRowCountCollection, UpdateSourceTrigger=PropertyChanged}"
          CanUserAddRows="False">
    <DataGrid.Columns>
        <DataGridTextColumn DataGridTextColumn
                            x:Name="DaGrTableName"
                            Header="Table Name"
                            IsReadOnly="True"
                            Binding="{Binding TableName}"/>
        <DataGridTextColumn x:Name="DaGrRowsCount"
                            Header="Row Count"
                            IsReadOnly="True"
                            Binding="{Binding RowCount}"/>
    </DataGrid.Columns>
</DataGrid>

    TableNameRowCountCollection is a property in your ViewModel of type ObservableCollection<TableRowCount> that needs to be populated before resp. as you show your form. TableRowCount is a simple object holding the table name and row count data like

    public class TableRowCount : INotifyPropertyChanged
{
    public string TableName {get; set;}
    public string RowCount {get; set;}
    // TODO: Implement INotifyPropertyChanged
    // TODO: Raise PropertyChanged Event in property setters
}
    Login or Signup to reply.
  2. 0

    Not entirely sure what you’re aiming for, but an easy thing would be to make a DataTable that stores all of the table names/row counts. Then just set the data context of the DataGrid to the default view of the DataTable

    DataTable myData = new DataTable();
myData.Columns.Add("Table Name", typeof(string));
myData.Columns.Add("Row Count", typeof(string));
myData.Rows.Add("Table 1", "14");
myData.Rows.Add("Table 2", "15");
DataGridTable.DataContext = myData.DefaultView;

    Based on your question, it seems like you’re pulling the data values from a SQL server. Just iterate over each data value and add to your data table, just like I did above.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search