C# Populate Multidimensional array from database table

Question

I have a database table with 7 columns for my blog using EF Core, and I am trying to create a multidimensional array in my c# code so that on page load I can retrieve the values from the DB and then assign the values in my controller to text fields in bootstrap cards. So card.1 will have details from Row.1 in the DB, card .2 will have details from Row.2 in the DB etc.

This is my database model:

public class ArticlesList
{
    [Key]
    public string ArticleReference { get; set; }
    public string TitleImageUrl { get; set; }
    public string UserId { get; set; }
    public string Title { get; set; }
    public string Snippet { get; set; }

    [DataType(DataType.DateTime)]
    [Column(TypeName = "datetime2")]
    public DateTime? PostedDateTime { get; set; }
    public bool WeeklyNewsList { get; set; }
}

This is my database structure:

I'm trying to assign the values from the DB to my ViewModel as per below in my Controller. But to do this I need to get the data for BOTH rows.

In my controller for this first attempt below, this works fine, it populates the 1st card with no errors:

string [,] articles = GetArticlesCardDetails.GetCardDetails();

var viewModel = new ArticlesViewModel
            {
                Article1CardImage = articles[0, 1],
                Article1CardTitle = articles[0,3],
                Article1CardContent = articles[0, 4],
                Article1CardDate = articles[0, 5],

                Article2CardTitle = "",
                Article2CardContent = "",
                Article2CardDate = ""
 };

But this throws an Index out of range exception:

string [,] articles = GetArticlesCardDetails.GetCardDetails();

var viewModel = new ArticlesViewModel
            {
                Article1CardImage = articles[0, 1],
                Article1CardTitle = articles[0,3],
                Article1CardContent = articles[0, 4],
                Article1CardDate = articles[0, 5],

                Article2CardTitle = articles[1, 3],
                Article2CardContent = articles[1, 4],
                Article2CardDate = articles[1, 5]
 };

Below are my attempts to populate the array so it can be returned to my controller. This is where it seems to be going wrong, it works fine but is only populating the 2nd database row into my array. I am getting each column as a list, then trying to add the values to an array. I can add and retrieve the values for [0,0] to [0,7] fine, but as soon as I try to retrieve [1,0] [1,1] etc. it throws an index out of range error.

I have been looking into this all day and found a lot of info on php code or raw SQL. There is also info on nested for loops, but I tried that and cant get it to work. It also seems very complicated for what is essentially populating a simple array. Someone also suggested in the comments that I look into databind, which I did. I might need it eventually to dynamically update the UI, but I dont need that right now. Right now I just need to create a simple array from a few rows of DB data.

ATTEMPT 1

public class GetArticlesCardDetails
{
    private static readonly ApplicationDbContext _context = new();

    public static string[,] GetCardDetails()
    {
        _context.ChangeTracker.QueryTrackingBehavior = QueryTrackingBehavior.NoTracking;
        var articlesEntriesCount = _context.ArticlesList.Count();
        Debug.WriteLine("************** articlesEntriesCount: " + articlesEntriesCount.ToString());
        _context.ArticlesList.OrderByDescending(x => new { x.PostedDateTime });
        var Articles = _context.ArticlesList.FirstOrDefault();
        string[,] articles = { };

        List<string> referenceList = _context.ArticlesList.Where(c => c.ArticleReference != null).Select(c => c.ArticleReference).ToList();
        List<string> titleImageUrlList = _context.ArticlesList.Where(c => c.TitleImageUrl != null).Select(c => c.TitleImageUrl).ToList();
        List<string> userIdList = _context.ArticlesList.Where(c => c.UserId != null).Select(c => c.UserId).ToList();
        List<string> titleList = _context.ArticlesList.Where(c => c.Title != null).Select(c => c.Title).ToList();
        List<string> snippetList = _context.ArticlesList.Where(c => c.Snippet != null).Select(c => c.Snippet).ToList();
        List<DateTime> postedList = _context.ArticlesList.Where(c => c.PostedDateTime != null).Select(c => c.PostedDateTime.Value).ToList();
        List<bool> weeklyBoolList = _context.ArticlesList.Select(c => c.WeeklyNewsList).ToList();

        for (int i = 0; i < articlesEntriesCount; i++)
        {
            Debug.WriteLine("************** i: " + i.ToString());
                articles = new string[,] { { referenceList[i], titleImageUrlList[i], userIdList[i], titleList[i], snippetList[i], postedList[i].ToString(), weeklyBoolList[i].ToString() } };
        };

        return articles;
    }
}

Below is my attempt nesting the for loops: ATTEMPT 2

   public class GetArticlesCardDetails
    {
        private static readonly ApplicationDbContext _context = new();

        public static string[,] GetCardDetails()
        {
            _context.ChangeTracker.QueryTrackingBehavior = QueryTrackingBehavior.NoTracking;
            var articlesEntriesCount = _context.ArticlesList.Count();
            Debug.WriteLine("************** articlesEntriesCount: " + articlesEntriesCount.ToString());
            _context.ArticlesList.OrderByDescending(x => new { x.PostedDateTime });
            var Articles = _context.ArticlesList.FirstOrDefault();
            string[,] articles = { };

            List<string> referenceList = _context.ArticlesList.Where(c => c.ArticleReference != null).Select(c => c.ArticleReference).ToList();
            List<string> titleImageUrlList = _context.ArticlesList.Where(c => c.TitleImageUrl != null).Select(c => c.TitleImageUrl).ToList();
            List<string> userIdList = _context.ArticlesList.Where(c => c.UserId != null).Select(c => c.UserId).ToList();
            List<string> titleList = _context.ArticlesList.Where(c => c.Title != null).Select(c => c.Title).ToList();
            List<string> snippetList = _context.ArticlesList.Where(c => c.Snippet != null).Select(c => c.Snippet).ToList();
            List<DateTime> postedList = _context.ArticlesList.Where(c => c.PostedDateTime != null).Select(c => c.PostedDateTime.Value).ToList();
            List<bool> weeklyBoolList = _context.ArticlesList.Select(c => c.WeeklyNewsList).ToList();

            for (int i = 0; i < articlesEntriesCount; i++)
            {
                for (int j = 0; j < articlesEntriesCount; j++)
                {
                    for (int k = 0; k < articlesEntriesCount; k++)
                    {
                        for (int l = 0; l < articlesEntriesCount; l++)
                        {
                            for (int m = 0; m < articlesEntriesCount; m++)
                            {
                                for (int n = 0; n < articlesEntriesCount; n++)
                                {
                                    for (int o = 0; o < articlesEntriesCount; o++)
                                    {
                                        Debug.WriteLine("************** i: " + i.ToString());
                                        articles = new string[,] { { referenceList[i], titleImageUrlList[i], userIdList[i], titleList[i], snippetList[i], postedList[i].ToString(), weeklyBoolList[i].ToString() }};
                                    }
                                }
                            }
                        }
                    }
                }
            };

            return articles;
        }
    }

Trying another attempt to simply use a list as suggested below in the comments throws an implicit conversion error on the return:

ATTEMPT 3

public class GetArticlesCardDetails
{
    private static readonly ApplicationDbContext _context = new();

    public static List<object> GetCardDetails()
    {
        _context.ChangeTracker.QueryTrackingBehavior = QueryTrackingBehavior.NoTracking;
        var articlesEntriesCount = _context.ArticlesList.Count();
        var articles = _context.ArticlesList.ToList();

        return articles;
    }
}

In my controller for the 3rd option above:

    List<object> articles = GetArticlesCardDetails.GetCardDetails();

What am I doing wrong? How do I just create an array and populate it from my DB so I can then retrieve the relevant values?

Answer 1

Following on from the suggestion in the comment on the original answer, and with thanks to Good Night Nerd Pride, I have now improved this further after working through the EF tutorial.

In my controller I simply have:

            var viewModel = new ArticlesViewModel
            {
             //some static variables are assigned in here...
            };

            viewModel.ListOfArticles = _context.ArticlesList;

And in my view I am dynamically generating and populating the elements, as per the suggested tutorial.

Answer 2

Got it! Turns out I needed a DataTable, not an array. I'm populating the DataTable using an SQL query and its working perfectly. Below for anyone else struggling with a similar problem in the future:

In my controller:

            DataTable articles = _context.DataTable("SELECT * FROM [dbo].[ArticlesList]", new SqlParameter("paramName", SqlDbType.NVarChar) { Value = "a" }

            var viewModel = new ArticlesViewModel
            {
                Article1CardImage = articles.Rows[0].Field<string>(1),
                Article1CardTitle = articles.Rows[0].Field<string>(3),
                Article1CardContent = articles.Rows[0].Field<string>(4),
                Article1CardDate = articles.Rows[0].Field<DateTime>(5).ToString(),

                Article2CardTitle = articles.Rows[1].Field<string>(3),
                Article2CardContent = articles.Rows[1].Field<string>(4),
                Article2CardDate = articles.Rows[1].Field<DateTime>(5).ToString(),
};

And my GetArticlesCardDetails class:

public static class GetArticlesCardDetails
{
    public static DataTable DataTable(this ApplicationDbContext _context, string sqlQuery, params DbParameter[] parameters)
    {
        _context.ChangeTracker.QueryTrackingBehavior = QueryTrackingBehavior.NoTracking;
        DataTable dataTable = new DataTable();
        DbConnection connection = _context.Database.GetDbConnection();
        DbProviderFactory dbFactory = DbProviderFactories.GetFactory(connection);

        using (var cmd = dbFactory.CreateCommand())
        {
            cmd.Connection = connection;
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = sqlQuery;

            if (parameters != null)
            {
                foreach (var item in parameters)
                {
                    cmd.Parameters.Add(item);
                }
            }
            using (DbDataAdapter adapter = dbFactory.CreateDataAdapter())
            {
                adapter.SelectCommand = cmd;
                adapter.Fill(dataTable);
            }
        }

        //Debug.WriteLine("************** dataTable: " + string.Join(Environment.NewLine, dataTable.Rows.OfType<DataRow>().Select(x => string.Join(" ; ", x.ItemArray))));
        return dataTable;
    }
}

And a little help from here.