Adding SQL Statement with all tables and column names into a Dictionary

Question

I need to save a sql SELECT Statement, which includes all tables and its columns in a database. The statement works fine and i can get all the names from the tables and columns i need.

The result looks as follows: (this is just psuedo-something)

            table_Name  Column_name
            - CallerIP  DT
            - CallerIP  ID
            - CallerIP  IP
            - queueObject Action
            - queueObject Attempt
            - queueObject DestinationAddress
            - queueObject ID

I thougt, i can save it into a Dictionary, where the tableName is a String, and the Colum_Names is a List of Strings

private Dictionary> rowAndTables = new Dictionary>();

this is my code, which should add all the tables and rows into the Dictionary

        //Some code above, that doesnt matter here
        command = new SqlCommand(sqlSelect, SqlConnector.getConnection());
        command.Connection = SqlConnector.getConnection();
        reader = command.ExecuteReader();

        while (reader.Read()) {
            if (tempTableName.Equals(reader.GetString(0)) == false) {
                tempTableName = reader.GetString(0);
                tempColumn = reader.GetString(1);
                Console.WriteLine(tempTableName);
                Console.WriteLine(tempColumn);
            } else {
                tempColumn = reader.GetString(1);
                Console.WriteLine(tempColumn);
            }

        }

This doesnt do anything, besides printing all tables and columns. The result looks as follows: //CONSOLE...

CallerIP //Table
DT
ID
IP
queue_object //Table
Action
Attempt
DestinationAddress
ID

So the printing is fine. Now I am struggeling with adding it into a Dictionary. Can anyone help ? Anything I did made no sense, and would just confuse anyone, I guess.

user_2319407 · Answer

Well, if you want to fill the dictionary

private Dictionary> rowAndTables = 
  new Dictionary>();

you should modify your code slightly:

    ...

    //DONE: wrap IDisposable (command) into using in order to release resources
    using (var command = new SqlCommand(sqlSelect, SqlConnector.getConnection())) {
      // Redundant, can be dropped
      command.Connection = SqlConnector.getConnection();

      using (var reader = command.ExecuteReader()) {
        //TODO: check if the key and value are correct ones 
        string key = Convert.ToString(reader[0]);
        string value = Convert.ToString(reader[1]);

        // Do we have the key (and corresponding list) in the dictionary?
        if (rowAndTables.TryGetValue(key, out var list))  
          // yes - we should add the value to the existing list 
          list.Add(value); 
        else 
          // no - we have to create key and list with value 
          rowAndTables.Add(key, new List() {value}); 
      }
    }

Adding SQL Statement with all tables and column names into a Dictionary

1 Answers