Method that returns a dataset

[MrNorth]

I have a problem!

I have a method that returns a dataset based on a SELECT query.

I need to check if the dataset returned from the method contains ZERO records or ONE record and take action accordingly... how can this be done?

kind regards
Henrik

[Lunatic3]

Dataset class does not contain rows directly, instead they contain DataTables which contain DataRows. You can get the row count in this way:

dsDataset.dtDatatable.Rows.Count or
dsDataset.Tables('here goes the table index or name').Rows.Count

[MrNorth]

Great, thanks!

Can anyone point me to some great tutorials that really cover the use of datasets like databases.. I have a few asp.net books but they don't go into dataset class that deep.. just the basics...

thanks
Henrik

[MrNorth]

However, I hit a problem when trying to do it like this:

if( myDb.Execute_Return_DS("SELECT username FROM reportusers WHERE username = \'" + TextBox1.Text + "\'").Tables["reportusers"].Rows.Count == 1)

I am checking if I got a hit with the username or not...
the method returns a dataset and takes an sql query as argument. I get the following exception:



Exception Details: System.NullReferenceException: Object reference not set to an instance of an object.


anyone has any suggestions, or perhaps even an easier way to check if a certain value exists in the database...?

kind regards
Henrik

[Lunatic3]

There are many ways to check if a value exist in a database.
However in your case , are you sure you are not confusing a DataTable with a Dataset?

Will you describe what exactly your method does? Have you added the dataTable Shcema to your dataset?

[MrNorth]

Sure, it is an extremly easy method

[vb]
public System.Data.DataSet Execute_Return_DS(string sQuery)
{
//Create a dataset
System.Data.DataSet ds = new System.Data.DataSet();
//Create a command
SqlCommand objCmd = new SqlCommand(sQuery,CreateDb());
//Create a data-adapter
SqlDataAdapter myData = new SqlDataAdapter(objCmd);
//Fill the DataSet
myData.Fill(ds);
return ds;
}

[/vb]

This is C# code, but the syntaxes are pretty much the same as vb.net.

This is a generic method that operates on any select query, and it works file... the problem is when I am trying to check the returned dataset... I need to know wether or not it is empty. And the method I tried didn't work

kind regards
Henrik

[Lunatic3]

First,
CreateDB must be connection, is it? you have not declared it here in this method, maybe outside of it?
Second,
As you are using an untyped dataset maybe filling the dataset this way will be more proper, although i am not sure of it.

myData.Fill(ds, "reportusers")

[MrNorth]

Yes, the CreateDB is a class that returns a sqlconnection, which I use in this method. I will rewrite the code so I use the datatable class instead of dataset... I am only returning one table anyway...
I will let you know of my progress...

Is it okay to do it like I am doing it with say:

if(getdsmethod("sqlquery").Tables["reportusers"].Rows.Count == 1)

I am positive it works if I do it:

myData.Fill(ds);

if(ds.Tables["reportusers"].Rows.Count == 1)


you see where Im getting.. on the first I am using a method that returns the ds, on the second I am invoking it on the ds directly. THIS WAY WORKS... I have tested. But I need to use the first method instead.. encapsulation

kind regards
Henrik

[Lunatic3]

Well, i really dont know why encapsualtion does not work in your case! maybe someone help us.

[MrNorth]

I think I might have an answer to WHY I get the error when using a return value rather than the object itself...

With the "working" way I create a dataset by:

DataSet DS = new DataSet();

but with the "not-working" way, I create the dataset within a method and then return the DS and tries to perform property requests on that returned object... If this is the case I get that exception, how can I find a way around it? Should I create another new DS and copy the returned information into that? It looks like you get that exception when an object hasn't been created properly (with new keyword)


help me out here....

kind regards
Henrik

[MrNorth]

Hey, I switched to datatable, and made dt.copy() and then invoked dt.rows.count and it works!!!


kind regards
Henrik

Can anyone direct me to some articles regarding ADO.NET? I have been unable to find any good ones... I mean generic ones explaining how good code should look like etc etc