Create an Array from SQL Query Results

Question

I have the following function that searches a database for entries where a column called "description" have the same value. Right now it just returns the first value it finds or a default value is there isn't one.

public static NewCode GetAltCode(int altCodeVer, string descrip)
    {
        var sql = @"select Code, Description, VersionID from Code.CodeLookup where versionid=@vers and description=@description";

        return ObjectFactory.GetInstance<IDatabaseFactory>().Query<NewCode>(sql, new { vers = altCodeVer, description = descrip, }).FirstOrDefault();
    }

I have this if statement to check and make sure the result isn't null, and if it is, to say that the "code isn't found"

[Authorize(parentAction: "Edit")]
        public ActionResult Lookup(string Code, int? VersionId = null)
        {
            var Info = VisitViews.GetDescriptionByVersionId(Code, VersionId.HasValue ? VersionId.Value : 9);
            var description = string.Empty;

            // CHECK FOR NULL
            if (Info != null)
            {
                description = Info.Description;
                if (VersionId == 9)
                {
                    var altInfo = VisitViews.GetAltCode(10, description);
                }
                if (VersionId == 10)
                {
                    var altInfo = VisitViews.GetAltCode(9, description);
                }
            }
            else
                description = "CODE NOT FOUND";

            return Json(new { Description = description });
        }

My question is, instead of doing FirstOrDefault, is there a way to store the results in an array (or even to store them in a list and call ToArray on the list)? I'm trying to get all of the codes received during the sql search instead of just one so that another function I am working on can traverse the array and place the items where they need to be in a UI.

You should be able to call ToList or ToArray instead of FirstOrDefault. Is that giving you an error? — Mike
– Mike, Commented Aug 4, 2014 at 15:42
.ToArray() and .ToList() are both giving me an error saying I can't convert type "....Code[]" to "....Code" — AtlasBowler
– AtlasBowler, Commented Aug 4, 2014 at 15:49
Without changing the way what is written? Do you mean without adding ToArray? If so, then no. You could change the return type to IEnumerable<NewCode> and then eliminate the FirstOrDefault(). — Mike
– Mike, Commented Aug 4, 2014 at 17:10
I see what you are saying. I changed the return type to NewCode[] and then was able to use .ToArray(). Thanks! — AtlasBowler
– AtlasBowler, Commented Aug 4, 2014 at 17:16

Mike · Accepted Answer · 2014-08-04 17:18:12Z

1

For future reference of this post, here is the answer:

Change the return type to NewCode[] and replace .FirstOrDefault() with .ToArray()

public static NewCode[] GetAltCode(int altCodeVer, string descrip)
{
    var sql = @"select Code, Description, VersionID from Code.CodeLookup where versionid=@vers and description=@description";

    return ObjectFactory.GetInstance<IDatabaseFactory>().Query<NewCode>(sql, new { vers = altCodeVer, description = descrip, }).ToArray();
}

answered Aug 4, 2014 at 17:18

Mike

6333 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Joel Coehoorn Over a year ago

IEnumerable<NewCode> would be better.

Mike Over a year ago

Why? The type in his example was NewCode.

Joel Coehoorn Over a year ago

-that should have been IEnumerable<NewCode> (edited).

Mike Over a year ago

It really depends on how it is being used... which we have no way of knowing. Typically, I would agree, though.

Collectives™ on Stack Overflow

Create an Array from SQL Query Results

1 Answer 1

4 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Related