How can I construct a jagged array from a select result from a SQL database?

Question

I want to create a jagged array as follows:

string[][] jaggedarray = {
        new string[ ] {"1","fred", "test1", "test2", "test3", "test4"},
        new string[ ] { "2", "test2", "test2A" },
        new string[ ] {"3", "test3", "test3a", "test3b" },
    };

This data is in a database and I get it by running a select. So each row may or may not have data.

I have tried the following :

List<string[]> myList = new List<string[]>();

string r1 = $"{reader.GetString(0)}" + ";";
string r2 = $"{reader.GetString(1)}" + ";";
string r3 = $"{reader.GetString(2)}";
string r4 = reader.GetString(3) == null ? "" : ";" + $"{reader.GetString(3)}";
string r5 = reader.GetString(4) == null ? "" : ";" + $"{reader.GetString(4)}";

string resultset = r1 + r2 + r3 + r4 + r5;                                              

myList.Add(new string[] { resultset });

But how can I get the data into a jagged array as shown above? Is there a way I can convert the myList to a jagged array?

Answer 1

You could try following code.

List<string[]> lists = new List<string[]>();

string connectionString = "Server=192.168.2.68;Database=test02;User Id=sa;Password=xxxxx;";
using (SqlConnection connection = new SqlConnection(connectionString))
{
    connection.Open();

    string sql = "SELECT * FROM table01";
    using (SqlCommand command = new SqlCommand(sql, connection))
    {
        using (SqlDataReader reader = command.ExecuteReader())
        {
            while (reader.Read())
            {
                string r0 = reader.GetInt32(0).ToString();
                string r1 = reader.IsDBNull(1) ? "" : reader.GetString(1);
                string r2 = reader.IsDBNull(2) ? "" : reader.GetString(2);
                string r3 = reader.IsDBNull(3) ? "" : reader.GetString(3);
                string r4 = reader.IsDBNull(4) ? "" : reader.GetString(4); 
                string r5 = reader.IsDBNull(5) ? "" : reader.GetString(5);


                lists.Add(new string[] { r0, r1, r2, r3, r4, r5 });
            }
        }
    }
    connection.Close();
}

string[][] jaggedarray = lists.ToArray();

var result = jaggedarray;

Test

Answer 2

In order to get a true jagged array. You would need to check fields and content and create the 2nd dimension during the read. Believe the below would work as you require:

using (IDataReader reader = comm.ExecuteReader())
{
  //  First dimension of the array...
  jaggedArray = new string[reader.GetSchemaTable().Rows.Count][];
  int rowCounter = 0;

  //  Full column count which we will verify...
  string[] content = new string[reader.FieldCount];
  bool[] useContent = new bool[reader.FieldCount];
  int fieldCount = 0;

  while (reader.Read())
  {
    fieldCount = 0;    

    for (int fieldLoop = 0; fieldLoop < reader.FieldCount; fieldLoop++)
    {
      //  Get the content at the position...                                        
      content[fieldLoop] = reader[0] == DBNull.Value ? String.Empty : reader.GetString(fieldLoop);

      //  The content is not empty - we can use it.
      if (!string.IsNullOrEmpty(content[fieldLoop]))
      {
        useContent[fieldLoop] = true;
        fieldCount++;
      }
    }

    //  Create the 2nd dimension of the array with available data.
    jaggedArray[rowCounter] = new string[fieldCount];

    if (fieldCount != useContent.Length)
    {
      // Handle this unlikely case...
      continue;
    }

    for (int fieldLoop = 0; fieldLoop < reader.FieldCount; fieldLoop++)
    {
      if (useContent[fieldLoop])
        jaggedArray[rowCounter][fieldLoop] = content[fieldLoop];

      rowCounter++;
    }
  }
}

Answer 3

You can convert your List<string[]> to a jagged array by using the ToArray method.

List<string[]> myList = new List<string[]>();

// Your data retrieval logic here, and then add to the list
while (reader.Read())
{
    string r1 = $"{reader.GetString(0)}" + ";";
    string r2 = $"{reader.GetString(1)}" + ";";
    string r3 = $"{reader.GetString(2)}";
    string r4 = reader.IsDBNull(3) ? "" : ";" + $"{reader.GetString(3)}";
    string r5 = reader.IsDBNull(4) ? "" : ";" + $"{reader.GetString(4)}";

    string resultset = r1 + r2 + r3 + r4 + r5;

    myList.Add(resultset.Split(';'));
}

// Convert List<string[]> to jagged array
string[][] jaggedArray = myList.ToArray();