Adding to dataset

[MalCarne]

I'm not sure if this is the best way to accomplish my end goal, but here is where I am at:

I have 3 functions

function1: returns top level product categories in a dataset
function2: returns sub categories of the selected category in a dataset
function3: returns items for the selected sub category in a dataset

I'm trying to populate some drop downs as a track back from the category/sub/item drill down.

Now I have function 4, which depending on where you are in the drilldown, is to return the categories and subcategories above the selection. Since I already have the functions built to return these, I don't really see a need to write a new function that does what these already do. Unfortunately, I'm hitting a roadblock in trying to add to a dataset using a function.

DataSet dsTrackBack = new DataSet();
dsTrackBack = getProductCategories();
return dsTrackBack;

Works fine.
But now I move to the next step:

DataSet dsTrackBack = new DataSet();
dsTrackBack = getProductCategories();
   if (subCat_id != "null") 
   {
       DataSet subCats = new DataSet();
       dsTrackBack.Tables.Add("subCats");
       populate table somehow
    }
return dsTrackBack;

I'm at a loss of how to populate the subCats table using the already existing function. Any ideas?

Thanks in advance

 

[jmeckley]

there a many ways to populate the datatable. here are some google results on creating a strongly typed dataset and populating it with data. 4GuysFromRolla is a good starting point

Jason Meckley
Programmer
Specialty Bakers, Inc.

 

[MalCarne]

Thanks,
but what I'm specifically trying to do is add the result of a function into this dataset. I'd like to try to add it in a method similar to this:

MySqlCommand objCmd = new MySqlCommand("Call sp_getColors ('" + prod_id + "')", objCon);
MySqlDataAdapter item = new MySqlDataAdapter(objCmd);
DataSet dsItem = new DataSet();
dsItem.Tables.Add("colors");
item.Fill(dsItem,"colors");
objCmd= new MySqlCommand("Call sp_getSizes ('" + prod_id + "')", objCon);
item = new MySqlDataAdapter(objCmd);
dsItem.Tables.Add("sizes");
item.Fill(dsItem,"sizes");

Only using the returned dataset to populate the added table.
Can this be done?

 

[jmeckley]

pass the dataset to the function as a parameter

public class MyClass
{
   public void FillDataSet(DataSet dataSet)
   {
      //create command...
      item = new MySqlDataAdapter(objCmd);

      dataSet.Tables.Add("MyTable");
      item.Fill(dataSet, "MyTable");
   }
}

then call from event

DataSet myDataSet = new DataSet();
new MyClass.FillDataSet(myDataSet);
//populate dropdowns with myDataSet

Jason Meckley
Programmer
Specialty Bakers, Inc.

 

[MalCarne]

OK, here's the big question: In your example, you are creating a dataset, than a command and a dataadapter and then adding the table and filling the dataset with the dataadapter.
Is there a way to forgo the creation of the command and dataadapter, since I already have a perfectly usable dataset from another function, and still be able to append it to the dataset that I'm trying to return? Confusing, I know, but here's the original code again with some comments.

//populate first dataset from outside function
DataSet dsTrackBack = new DataSet();
dsTrackBack = getProductCategories();
   if (subCat_id != "null") 
   {
       DataSet subCats = new DataSet();
       //populate second dataset from outside function
       subCats = getProductSubCategories(cat_id)
       dsTrackBack.Tables.Add("subCats");
       //append subCats to dsTrackback somehow
    }
return dsTrackBack;

 

[jmeckley]

if you already have a dataset containing some of the necessary information then continue to use that same dataset and create/populate the other tables within the same dataset.

whether you want to reuse objects depends on what you want to do with your code. You should be able to reuse the dataadatper. I would recommend new command objects for each sql statement. at least re-initialize the command like this

// fetch data with cmd for sql query 1
SqlCommand cmd = new SqlCommand(...);
...

// fetch data with cmd for sql query 2
cmd = new SqlCommand(...);
...

You'll also want to add the table relations to easilty link parent/child records.

Jason Meckley
Programmer
Specialty Bakers, Inc.