C#: Passing FbDataReader not working

I have to make a number of SQL queries via the Firebird Data Reader (FbDataReader). Rather than repeating the code to setup the data connection each time, I tried to move this part into a separate method so I only had to pass the SQL and parameters to gather info. The idea was that this would put the results into the FbDataReader specified so the code that called the method could then interact with the returned data as needed. So I did this:
    public class Query 
    {
        public static bool GetData(ref FbDataReader dr, string Request, params object[] RequestParams )
        {
            using (FbConnection ibCon = new FbConnection(DBConnection.CreateConnString()))
            {
                try
                {
                    FbCommand cmdFBQuery = new FbCommand();

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

                    ibCon.Open();
                    dr = cmdFBQuery.ExecuteReader();
                    // I have checked at this point following my query and the FbDataREader (fb) does have the expected data

                    return dr.HasRows;
                }
                catch (Exception ex)
                {
                    // handle error here
                    return false;
                }
            }
        }
    }

Open in new window

My attempt to call this looks like this:
        public static FbDataReader QueryResults;

...

return [namespace].ChronData.Query.GetData(ref QueryResults,
                                           SQLData,  // SQL here was tested independently and works
                                           ID);  // the params for the query; only one in this case

Open in new window



I've tried passing the FbDataReader by reference and also tried making the FbDataReader the return value, but either way, the FbDataReader on the calling side remains inactive with no data.

If I'm going about this in all the wrong way or missing key concepts, please set me straight, but I'll need fairly basic instruction: I've been doing SQL and Delphi programming for quite a while (in Delphi, would have loaded results into a clientDataSet), but this is my first attempt to do something in C#.
EricTaylorAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

EricTaylorAuthor Commented:
I just tried an alternate version of this where I made an instance of the FbDataReader part of the class that is doing the data request. This attempt also correctly got data in the GetData method but no longer had it after this method returned. Here's what I tried:
    public class SQLData
    {
        public static FbDataReader Data;
        public bool GetData(string Request, params object[] RequestParams )
        {
            using (FbConnection ibCon = new FbConnection(DBConnection.CreateConnString()))
            {
                try
                {
                    FbCommand cmdFBQuery = new FbCommand();

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

                    ibCon.Open();
                    Data = cmdFBQuery.ExecuteReader();
                    // I have appropriate values at this point
                    return Data.HasRows;
                }
                catch (Exception ex)
                {
                    // handle error here
                    return false;
                }
            }
        }
    }

// here's the piece that created an instance of the above object
// it's actually in another class (and namespace)

          SQLData ActivityInfo = new SQLData();
          ActivityInfo.GetData(SQL, ID);

          // At this point, ActivityInfo.Data has IsClosed set to true
          // and it has no data

Open in new window

I may be missing something very obvious here; as I said, I'm new to C#. Appreciate any help.
0
AndyAinscowFreelance programmer / ConsultantCommented:
In your first sets of code try changing
public static FbDataReader QueryResults;
to
public FbDataReader QueryResults;

I'm a bit suspicous of you having declared the variable as static and then changing it via the assignment (dr = cmdFBQuery.ExecuteReader();) inside the GetData function.

ps.
I'd even consider having that as the return value of the GetData function like:
        public static FbDataReader GetData(string Request, params object[] RequestParams )
0
ste5anSenior DeveloperCommented:
The problem is that you haven't posted the important part: How do you invoke your GetData()? Please post the entire "attempt to call". Cause you're shortened version makes no sense.

This simple piece works:

namespace ConsoleApplication1
{
    using System;

    class Program
    {
        static void Main(string[] args)
        {
            FbDataReader QueryResults = new FbDataReader() { Name = "From Main" };
            Query.GetData(ref QueryResults);                                  
            Console.WriteLine(QueryResults.Name);
            Console.ReadLine();
        } 
    }

    public class Query
    {
        public static void GetData(ref FbDataReader dr)
        {
           dr = new FbDataReader() { Name = "From GetData" };
        }
    }
                          
    public class FbDataReader 
    {
        public string Name {get;set;}
    }
}

Open in new window

0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

EricTaylorAuthor Commented:
AndyAinscow: When I take out "static" I get ...Query is not a type which is not valid in the given content.

ste5an: I don't understand your example since you are making up a new class FbDataReader which is not a firebird data reader at all so doesn't execute a SQL query, etc. So I'm missing how this applies. However, accepting that a clearer code sample is apparently needed, I'll try again.

As before, I apologize if I'm missing something really obvious in your answers or in foundational understanding of C#; except for one very limited project 10(?) years ago, this is my first C# attempt and I just started last week (and have been interrupted several times). (I've been working almost exclusively with Delphi and SQL), so with only a few interrupted days looking at C#, many of the basics are still eluding me. I'm floundering my way to an understanding by trying stuff that mostly doesn't work yet.  :)

So, here are two strands of attempt, but of which fail. This first won't compile because 'ChronData.Query' is a 'type', which is not valid in the given context':
    public class Query
    {
        public FbDataReader GetData(string Request, params object[] RequestParams)
        {
            using (FbConnection ibCon = new FbConnection(DBConnection.CreateConnString()))
            {
                try
                {
                    FbCommand cmdFBQuery = new FbCommand();

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

                    ibCon.Open();
                    return cmdFBQuery.ExecuteReader();
                }
                catch (Exception ex)
                {
                   ...
                }
            }
        }
    }

    public class ChronJobActivity
    {
        public static FbDataReader QueryResults;
        // various public vars and methods that don't matter here

        public Boolean GetACTIVITY(int ID)
        {
            string SQL = "Select ..."; // have tested the query and it works
            QueryResults = ChronData.Query(SQL, ID);

            return true; // return something more meaningful once I have results
        }

Open in new window

My other attempt compiles but still does not work:
    public class SQLData
    {
        public static FbDataReader Data;
        public bool GetData(string Request, params object[] RequestParams )
        {
            using (FbConnection ibCon = new FbConnection(DBConnection.CreateConnString()))
            {
                try
                {
                    FbCommand cmdFBQuery = new FbCommand();

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

                    ibCon.Open();
                    Data = cmdFBQuery.ExecuteReader();
                    // at this point, data (my FbDataReader) has values as expected

                    return Data.HasRows;
                }
                catch (Exception ex)
                {   ...    }
            }
        }
    }

        public Boolean GetACTIVITY(int ID)
        {
            string SQL = "Select ..."; // have tested the query and it works
            ChronData.SQLData ActivityInfo = new ChronData.SQLData();
            ActivityInfo.GetData(SQL, ID);
            // At this point, I expect values in ActivityInfo.Data
            // but it no longer has anything
            return true; // return something more meaningful once I have results

Open in new window

0
ste5anSenior DeveloperCommented:
You didn't get it? You're still posting incomplete code which does not even compile.
0
EricTaylorAuthor Commented:
ste5an:
The piece that did not compile was an attempt to implement what Andy had suggested above. I included it in case I misunderstood what he was suggesting, which, given my newness with C#, seemed very possible.

The second piece (which compiles just fine) included two complete methods which I thought addressed "How do you invoke your GetData()? Please post the entire 'attempt to call.'" I could post a larger chunk of code, but I'm not clear what that would do since my SQL wouldn't be remotely useful without a connection to my DB, and the other methods in the class are irrelevant to the firebird connection not working correctly, so I'm not sure what more to show.
0
ste5anSenior DeveloperCommented:
Seems that I haven't posted it before:

Please post a concise and complete example. A small console application is sufficient.

btw, creating such a small repro which shows your problem is often also the normal approach for isolating a problem. So a normal step in error diagnostics.
0
EricTaylorAuthor Commented:
I'm not sure how to pass a complete example in this context. The two methods that I used rely on a class that is in another unit that executes a SQL query on a local database: I have no way to pass on the database and without that the SQL and the unit that reads from the db  are rather meaningless.

In light of this, I've taken a different approach: instead of trying to pass back the FbDataReader, I'm putting the results into a List<object> which I can successfully get back to the calling method. In case you're interested, here's what I ended up with. (I have a few questions about what I've done there, but that seems like a separate question so I'll ask that as a new question.)
    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)
                    {
                        i++;
                        cmdFBQuery.Parameters.Add(new FbParameter("P" + 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

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
C#

From novice to tech pro — start learning today.