class for DB connection to return dataset

[kevin]

I'm new to C#, so forgive me if this is n00b stuff. I've been
researching creating classes to hold commonly used methods, etc across
multiple WebForms. To a degree I understand this and managed to get a
"Hello World" example running where in my common.cs file I had:

public class dbConnection {
public const string iGreeting = "Hello World!";
}

and in my aspx files I simply put:

Response.Write(dbConnection.iGreeting.ToString());

what I'd like this method, dbConnection, to do is take a string (the
SQL Query, called 'sSQLQuery' (inventive, eh?)) and return a dataset.

my attempt failed (naturally, or I'd not be posting here), so I wonder
if a) this can be done at all, and b) what's wrong with my code (please
be gentle!!)

public object sReturnedDataSet() {
string oradb = "Data Source=XXX;User Id=YYY;Password=ZZZ;";
OracleConnection conn = new OracleConnection(oradb);
// define the query & command
string sSQLQuery = "SELECT * FROM table1";

// define the command
OracleCommand cmd = new OracleCommand(sSQLQuery,conn);

// open the connection
conn.Open();

cmd.CommandType = CommandType.Text;
OracleDataReader dr = cmd.ExecuteReader();

return dr;
}

then I attempted to use this, like so:

Response.Write(dbConnection.sReturnedDataSet["field_name"].ToString();

However, this is where it went belly up and died on me. When I tried to
browse the ASPX file, I got the following 'build' error:

'An object reference is required for the nonstatic field, method, or
property 'reports.dbConnection.sReturnedDataSet()'



can anyone help me out with some pointers, some sample code or a
website that might help explain it?

All help appreciated.

 

[sdbillsfan]

Your sReturnedDataSet() method needs to be marked as static.

 

[Kevin Blount]

so I need to update

public object sReturnedDataSet() {

to be

public static object sReturnedDataSet() {

and the way I'm using it should work? i.e.

Response.Write(dbConnection.sReturnedDataSet["field_name"].ToString();

Thanks for the response, so far

 

[sdbillsfan]

No, you're either going to need a strongly typed return value or cast
the return value (the former is better).

ie
public static OracleDataReader ReturnedDataReader();

-or-

Response.Write ( ( (OracleDataReader)
dbConnection.sReturnedDataSet)["field_name"].ToString());

Also a word of advice if you're going to be using an Oracle DB, Use the
Oracle managed provider instead of the microsoft provided one
(especially if you're going to be dealing with transaction savepoints).


http://www.oracle.com/technology/tech/windows/odpnet/index.html

 

[Kevin Blount]

just because I can ask, and won't be in the office to try for another
few hours.. let me see if I got this straight:

I should use, in common.cs
public static OracleDataReader ReturnedDataReader();

and then I can use in WebForm1.aspx
Response.Write(dbConnection.sReturnedDataSet["field_name"].ToString();

that about right?

 

[sdbillsfan]

dbConnection.sReturnedDataSet()["field_name"].ToString();

 

[Kevin Blount]

Thanks for the help so far. I'm still having some problems though. Here
are my 2 files, as they stand after going through this thread:

[code "common.cs"]
public sealed class dbConnection
{
public const string iGreeting = "Hello World!";

public static OracleDataReader sReturnedDataReader()
{
string oradb = "Data Source=xxx;User Id=yyy;Password=zzz;";
OracleConnection conn = new OracleConnection(oradb);
string sSQLQuery = "SELECT * FROM members WHERE rownum < 500";

OracleCommand cmd = new OracleCommand(sSQLQuery,conn);

conn.Open();

cmd.CommandType = CommandType.Text;
OracleDataReader dr = cmd.ExecuteReader();

return dr;

}

}
[/code]


AND

[code "WebForm1.aspx.cs"]
Response.Write(dbConnection.sReturnedDataReader()["member_id"].ToString());
[/code]


when I browse my webform, I get the following error: "Invalid attempt
to read when no data is present"

I'm fairly confident that the data is being returned (if I change the
method to return a string, I can create a string that equals the row
count of the datareader), but for some reason when I try and output
anything from the returned "dr" I get this message.

any ideas?

 

[sdbillsfan]

you need to do a dr.Read() which returns a boolean specifying whether
there is any more data so it's probably best done on the client.

OracleDataReader dr = dbConnection.sReturnedDataReader();

while(dr.Read())
{
Response.Write(dr["member_id"].ToString());

}

 

[Kevin Blount]

Awesome! Once I realized I had to keep "return dr;" in common.cs, as
well as add the new dr = line to my webform, I was able to get this
working!!

Many thanks for the help.. it's very much appreciated.

(now all I need to do is figure out how to pass the sSQLQuery string to
the method, so that I can call this same method will a multitude of
different queries.. but I wanna try and figure something out myself.
That said.. I might be posting again soon <g>)

Thanks again.