Solved

C#: Passing FbDataReader not working

Posted on 2014-11-07
9
248 Views
Last Modified: 2016-06-23
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#.
0
Comment
Question by:EricTaylor
  • 4
  • 3
9 Comments
 

Author Comment

by:EricTaylor
ID: 40429765
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
 
LVL 44

Expert Comment

by:AndyAinscow
ID: 40430029
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
 
LVL 32

Expert Comment

by:ste5an
ID: 40430088
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
 

Author Comment

by:EricTaylor
ID: 40436144
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 32

Expert Comment

by:ste5an
ID: 40437299
You didn't get it? You're still posting incomplete code which does not even compile.
0
 

Author Comment

by:EricTaylor
ID: 40438221
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
 
LVL 32

Expert Comment

by:ste5an
ID: 40438257
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
 

Accepted Solution

by:
EricTaylor earned 0 total points
ID: 40439201
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

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

We all know that functional code is the leg that any good program stands on when it comes right down to it, however, if your program lacks a good user interface your product may not have the appeal needed to keep your customers happy. This issue can…
This document covers how to connect to SQL Server and browse its contents.  It is meant for those new to Visual Studio and/or working with Microsoft SQL Server.  It is not a guide to building SQL Server database connections in your code.  This is mo…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now