# (More) efficient and elegant way to retrieve data from a dataset?

I have a DataSet with only two tables in it. The first table is a list of models (of products) with default parameters (settings), and the second table is a list of modules. A module is a product based on a given model but with some parameters that have been changed. A sample DataSet in XML format could be something like this :

<?xml version="1.0" encoding="utf-8" ?>
<Parameters>
<Model>
<IdModel>1</IdModel>
<Par1>10</Par1>
<Par2>0.5</Par2>
<Par3>15</Par3>
</Model>
<Model>
<IdModel>2</IdModel>
<Par1>20</Par1>
<Par2>0.5</Par2>
<Par3>20</Par3>
</Model>
<Module>
<IdModule>11</IdModule>
<IdModel>1</IdModel>
<Par1>11</Par1>
</Module>
<Module>
<IdModule>12</IdModule>
<IdModel>1</IdModel>
<Par3>12</Par3>
</Module>
<Module>
<IdModule>21</IdModule>
<IdModel>2</IdModel>
<Par1>21</Par1>
</Module>
</Parameters>


What I need to do is to retrieve a module in this "database" and its parameters. The parameters are the default ones (those of the corresponding model) but with the personnalized ones that have been specified in the XML.

I managed to retrieve such data but in a very non-efficient and non-elegant way as don't know much of SQL or Linq requests :

    private static void RetrieveLaserSettings(DataSet ds, string Id)
{
//Retrieve the two tables from dataset
DataTable Models = ds.Tables["Model"];
DataTable Modules = ds.Tables["Module"];

//The corresponding model and module
Dictionary<string, string> model = new Dictionary<string, string>();
Dictionary<string, string> module = new Dictionary<string, string>();

//Look for the right module
DataRow[] rowsModules = Modules.Select("IdModule = " + Id);
if(rowsModules.Length > 0)
{
if(rowsModules.Length > 1)
{
Console.WriteLine("Several modules with the same ID");
return;
}
//Fill the data
DataRow rowModule = rowsModules[0];
for(int i = 0; i < rowModule.Table.Columns.Count; i++)
{
string columnName = rowModule.Table.Columns[i].ColumnName;
string colValue = rowModule.ItemArray[i].ToString();
}

//Look for the model
string modelId = rowModule["IdModel"].ToString();
DataRow[] rowsModels = Models.Select("IdModel = " + modelId);
if(rowsModels.Length > 0)
{
if(rowsModels.Length > 1)
{
Console.WriteLine("Several models with the same ID");
return;
}
//Fill the data
DataRow rowModel = rowsModels[0];
for(int i = 0; i < rowModel.Table.Columns.Count; i++)
{
string columnName = rowModel.Table.Columns[i].ColumnName;
if(string.Equals(columnName, "IdModel"))
continue;
string colValue = rowModel.ItemArray[i].ToString();
foreach(KeyValuePair<string, string> pair in module)
{
if(string.Equals(columnName, pair.Key.ToString()) && !string.IsNullOrEmpty(pair.Value.ToString()))
{
colValue = pair.Value.ToString();
}
}
}
}
}
//Display the result
foreach(KeyValuePair<string, string> pair in model)
{
Console.WriteLine(pair.Key.ToString() + " : " + pair.Value.ToString());
}
}


Any help or suggestion is welcome !! Thanks

-

## migrated from stackoverflow.comJan 10 '13 at 14:03

This question came from our site for professional and enthusiast programmers.

Side note: do your really need to call ToString() on string property: pair.Value.ToString()? –  Alexei Levenkov Jan 9 '13 at 3:01
Instead of using two Dictionary<string, string> create a Model and Module classes. then create a List<Model> this will allow you to neaten up the code. –  Jastill Jan 9 '13 at 3:01
(Alexei), it is indeed not necessary, but just in case, to be sure... (Jastill) That is what I'll do in a not so far future because a Module is in fact a model with personnalized parameters. I just wanted here to be more generic and to be XML driven instead of fixing the schema in a class. The "exercise" here is to play with kind of relational tables instead of searching by hand in tables. Thanks for your comments ! –  G_A Jan 9 '13 at 3:05
@G_A Rest assured, you can be sure that pair.Value will always return a string or a null reference. The only things that the ToString() call adds is the overhead of an extra method call (possibly optimized away, but not necessarily) and the possibility of a NullReferenceException. –  phoog Jan 9 '13 at 3:49
If it's a classic one-to-many (parent/child) relationship you have, you might find it helpful to add a DataRelation to the dataset: msdn.microsoft.com/en-us/library/dbwcse3d%28v=VS.80%29.aspx –  Steve Wellens Jan 9 '13 at 3:55

Really, all I did was refactor your code into smaller methods.

I'm loading the Module properties and values into a dictionary. I then get the corresponding Model and load it's properties into the same dictionary. During the filling of the dictionary, it ignores any data that is null, or if it has already been loaded.

You can change the code to load the Model first and then the Module if need be. If this doesn't help, I hope it atleast gets you on the right track.

private static void RetrieveLaserSettings(DataSet ds, string id)
{
try
{
var keydata = new Dictionary<string, string>();
LoadData(keydata, ds.Tables["Module"], string.Format("IdModule = {0}", id));
if (keydata.ContainsKey("IdModel"))
LoadData(keydata, ds.Tables["Model"], string.Format("IdModel = {0}", keydata["IdModel"]));

foreach (KeyValuePair<string, string> pair in keydata)
{
Console.WriteLine(pair.Key.ToString() + " : " + pair.Value.ToString());
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}

private static void LoadData(IDictionary<string, string> keyVals, DataTable table, string idSelect)
{
DataRow[] rowsModules = table.Select(idSelect);
if (rowsModules.Length > 0)
{
if (rowsModules.Length > 1)
{
throw new Exception(string.Format("Several {0}s with the same ID",table.TableName));
}
FillRowDataInDictionary(keyVals, rowsModules[0]);
}
}

private static void FillRowDataInDictionary(IDictionary<string, string> keyVals, DataRow dataRow)
{
for (var i = 0; i < dataRow.Table.Columns.Count; i++)
{
var columnName = dataRow.Table.Columns[i].ColumnName;
var colValue = dataRow.ItemArray[i];
// Making sure we only want values that are not null and not already populated
if (colValue!=DBNull.Value && !keyVals.ContainsKey(columnName))
{
`