We help IT Professionals succeed at work.
Get Started

C#: adding field name to List of SQL Results

EricTaylor
EricTaylor asked
on
180 Views
Last Modified: 2014-11-20
I've written a piece to execute a SQL query and put the results into a List<object> where each record contains the fields of data from each SQL record returned. However, in my results, I no longer have the SQL field names that I can see but only have a dozen numbered fields. Is there a relatively easy way to keep the field names with the corresponding data? What I'd like to be able to do when dealing with the list is to interact with "JobID" rather than merely column 1, etc. But I've tried to write this in a generic way because this method will have to deal with many different SQL queries of varying complexity... returning anything from a single record to thousands (mostly in the 0 to several hundred range).

If you answer, please be very basic. While I've been programming for a while (almost all Delphi), I've only been working with C# for less than a week. I'm still very much in the "pirate" stage... looking for various answers online and "borrowing" and modifying to fit what I need to do. So while what I've written here works, it's is beyond my comfort level at this point. If you answer assuming that I can just pop off what's here, your answer will be over my head. (Generic suggestions to make this better or more efficient are also welcome.)

Anyway, here's what I've got so far.
    public class QueryResults : List<object[]>
    {
        public void GetData(string SQL, params object[] RequestParams)
        {
            using (FbConnection ibCon = new FbConnection(DBConnection.CreateConnString()))
            {
                try
                {
                    FbCommand cmdFBQuery = new FbCommand();

                    cmdFBQuery.Connection = ibCon;
                    cmdFBQuery.CommandType = CommandType.Text;
                    cmdFBQuery.CommandText = SQL;
                    int i = 0;
                    foreach (object obj in RequestParams)
                    {
                        cmdFBQuery.Parameters.Add(new FbParameter(i++.ToString(), obj));
                    }

                    ibCon.Open();
                    using (var reader = cmdFBQuery.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            var columns = new object[reader.FieldCount];
                            reader.GetValues(columns);
                            this.Add(columns);
                        }
                    }
                }
                catch (Exception ex)
                {
                    // handle error here
                }
            }
        }
    }

Open in new window

Comment
Watch Question
Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009
Commented:
This problem has been solved!
Unlock 1 Answer and 5 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE