none
Dataset, Datatable, DataRow -- Add or make a query / select RRS feed

  • Question

  • Hello,
    I want to find a foreign key with several conditions.
    I have a detail table, how is the best way to fill it with the foreign key?
    What is the best way to solve it?
    What are the options?
    It works, I am not sure if it is the best and fastest solution, which is the reason of my question.
    DataRow[] drContents = dtContents.Select(string.Format("IDVariables = '{0}' AND Index = '{1}'", drVariables[0]["ID"], 3));
    if (drContents.Count() <= 0)
    {
     return;
    }
    DataTable dtVariables = dsTEST.Tables["Variables"];
    DataRow variableRow = dsTEST.Variables.NewRow();
    variableRow["Variable"] = "ReceiverSAT";
    dsTEST.Variables.Rows.Add(variableRow);
    
    variableRow = dsTEST.Variables.NewRow();
    variableRow["Variable"] = "ReceiverSAT3";
    dsTEST.Variables.Rows.Add(variableRow);
    var validDR = dsTEST.Variables.Rows.Cast<DataRow>()
           .Where(x => x["Variable"].ToString() == "ReceiverSAT2").FirstOrDefault();
    int foreignKey = Convert.ToInt32(validDR["ID"]);
    
    DataRow serialsContentsRow;
    for (int i = 0; i < 20; i++)
    {
     serialsContentsRow = dsTEST.Contents.NewRow();
     serialsContentsRow["Index"] = i + 1;
     serialsContentsRow["Content_IBAN13"] = String.Format("{0:D10}", i);
     serialsContentsRow["IDVariables"] = foreignKey;
     dsTEST.Contents.Rows.Add(serialsContentsRow);
    }
    dsTEST.WriteXml("C:\\_Temp\\TEST.XML");

    Regards Markus

    Thursday, May 24, 2018 4:39 PM

Answers

  • a) r.Field<int>("IDVariables")

    Same purpose but different syntax. If you do r["IDVariable"] then you get back an object. If you wanted it to be, say an int, you'd have to either cast it yourself or call Convert. The Field method does that automatically for you. It simply returns the already cast value to you so you can consume it directly.

    Speed wise it is slower because it has to do a typecast but if you are going to do the typecast anyway then it takes the same amount of time.

    b) Where are the advantages of the relationship?

    Not really any benefit here. I was just replicating the data that you had shown in your table image. 

    Is it only for query, report or can I also add new rows?

    Yes, the last couple of lines showed adding a new Contents and a new Variables row to the table and then associating them. When you're working with the DataTable directly you can CRUD the data as needed.



    Michael Taylor http://www.michaeltaylorp3.net

    Wednesday, May 30, 2018 5:55 PM
    Moderator

All replies

  • If the query is complex then I'd recommend either pushing it to a view in SQL or using a stored procedure. This would allow the DBAs to optimize it as needed.

    If the data is already loaded in memory then LINQ would be my second choice.

    If you're using EF or another ORM then they already have the tools to support this so you should use them instead.

    I'd only load the data into a datatable if I needed all the related data, the size of the data was small and I didn't intend to do anything with the data outside the table structure.


    Michael Taylor http://www.michaeltaylorp3.net

    Thursday, May 24, 2018 5:09 PM
    Moderator
  • It works, I am not sure if it is the best and fastest solution, which is the reason of my question.

    I kind of doubt that it's fast or best.

    https://dzone.com/articles/reasons-move-datatables

    http://lauteikkehn.blogspot.com/2012/03/datatable-vs-list.html

    Thursday, May 24, 2018 5:56 PM
  • Hi ,
              
    >If the data is already loaded in memory then LINQ would be my second choice.
    The data I have loaded via an XML file, are in memory.
    Can you please give an example of how you ask that via LinQ?

    DataRow[] drContents = dtContents.Select(string.Format("IDVariables = '{0}'

    AND Index = '{1}'", drVariables[0]["ID"], 3));


    I have a Master/Detail relation.
    If I add details to records, are there any easier ways than noticing the foreign key? Is that automatically a way?
    I have this.
    int foreignKey = Convert.ToInt32(validDR["ID"]);
    DataRow serialsContentsRow;
    for (int i = 0; i < 20; i++)
    {
    	serialsContentsRow = dataSetBarcodesIBAN13.Contents.NewRow();
    	serialsContentsRow["CounterIndex"] = i + 1;
    	serialsContentsRow["Content"] = String.Format("{0:D10}", i);
    	serialsContentsRow["IDVariables"] = foreignKey;
    	dataSetBarcodesIBAN13.Contents.Rows.Add(serialsContentsRow);
    }
    
    I am looking for a way to fill it with the relationship automatically.
    I know the master. --> variableRow
    I checked a lot. Set in the toobox autoincrement for column ID.
    
    foreach (DataRelation dataRelation in dataSetBarcodesIBAN13.Relations)
    {
    	if (dataRelation.RelationName == "FK_Variables_Contents")
    	{
    		for (int k = 0; k < 100; k++)
    		{
    			DataRow[] children = variableRow.GetChildRows(dataRelation);
    			children[0]["CounterIndex"] = k + 1;
    			children[0]["Content"] = String.Format("{0:D10}", k);
    			dataSetBarcodesIBAN13.Contents.Rows.Add(children);
    		}
    		break;
    	}
    }
    -> works not!
    
    Here is the foreign key missing.
    serialsContentsRow["IDVariables"] = foreignKey;
    How I get it automatically?
    
    foreach (DataRelation dataRelation in dataSetClassicVariablesCounters.Relations)
    {
    	if (dataRelation.RelationName == "FK_Variables_Contents")
    	{
    		for (int k = 0; k < 100; k++)
    		{
    			// DataRow[] children = variableRow.GetChildRows(dataRelation);
    			serialsContentsRow = dataRelation.ChildTable.NewRow();
    			serialsContentsRow["CounterIndex"] = k + 1;
    			serialsContentsRow["Content"] = String.Format("{0:D10}", k);
    			dataSetClassicVariablesCounters.Contents.Rows.Add(serialsContentsRow);
    		}
    		break;
    	}
    }
    
    
    foreach (DataRelation dataRelation in dataSetClassicVariablesCounters.Relations)
    {
    	if (dataRelation.RelationName == "FK_Variables_Contents")
    	{
    		for (int k = 0; k < 100; k++)
    		{
    			DataRow[] children = variableRow.GetChildRows(dataRelation);
    			//DataRow parent = variableRow.GetParentRow(dataRelation);
    			//DataRow child = variableRow.GetChildRow(dataRelation);
    			if (children.Count() == 0)
    			{
    			}
    			serialsContentsRow = dataRelation.ChildTable.NewRow();
    			serialsContentsRow["CounterIndex"] = k + 1;
    			serialsContentsRow["Content"] = String.Format("{0:D10}", k);
    			serialsContentsRow["IDVariables"] = variableRow["ID"];
    			dataSetClassicVariablesCounters.Contents.Rows.Add(serialsContentsRow);
    		}
    		break;
    	}
    }
    Releation
    Regards Markus
    Friday, May 25, 2018 4:29 PM
  • Consider this a master class/object  with a collection of child classes/objects within the master/parent.

    It is assumed that the collection of children within the master/parent is in some relationship to the master/parent.

    http://www.dotnetcurry.com/ShowArticle.aspx?ID=513

    I bring you back to this and what is being explained on datatable vs a generic list that you need to recognize.

    https://dzone.com/articles/reasons-move-datatables

    Just look at all the unnecessary code you have going on, becuase you are using a datatable before you can even try to use Linq. 

    Friday, May 25, 2018 6:15 PM
  • What you're showing is a data table representation of the XML. It sounds like you're wanting to convert that XML to some other XML format? If so then you don't need a data table. XSDT would be a choice but it depends on the XML. Please post an example of the XML you're trying to load along with the rationale/requirements you had for loading it into a datatable to begin with. Any requirements you have may influence the answer.

    As far as using LINQ goes, it is pretty straightforward if the XML is already in a master-detail relationship. You don't need a data table for that. But posting the XML will be helpful.


    Michael Taylor http://www.michaeltaylorp3.net

    Friday, May 25, 2018 7:46 PM
    Moderator
  • What you're showing is a data table representation of the XML. It sounds like you're wanting to convert that XML to some other XML format? 

    No, I have a master detail table and a primary foreign key relationship. Now I just want to add records in the detail table. What did I do, I searched for the primary key and added it as a foreign key in the detail table. I'm looking for something simpler, maybe about the relation. How would you add records in the detail table? How would you implement LinQ queries?

    I would like to know the content of variable ReceiverSAT for Index 3?

    That's all.

    With best regards Markus

    Saturday, May 26, 2018 10:38 AM
  • Markus,

    The writing of that file takes probably some seconds. Your setting code probably some microseconds. Therefore what do you want to optimize. 

    The best is simply to set it direct and not in a kind of extra roundtrip in your code, but I would surely not look at performance about this. 

    As you can see you get very much different answers, which means that your code is not easy to read and therefore I would more focus on that part instead of performance. 

    However, writing an XML file without first creating a temporary from the old one is not clever to do. 


    Success
    Cor


    Saturday, May 26, 2018 11:13 AM
  • You cannot modify data with LINQ so that isn't an option.

    If you want to set up a master-detail relationship via a table structure then you're limited to setting the column in the detail table to the ID of the entry in the master table (for most relationships). Assuming the datatable has already figured out the relationship structure (because of a previous database load or a call to ReadSchema) then you don't need to do anything else.

    I don't see that you specify the table column relationships anywhere so I'd do a simple example.

    MasterTable
       Id : int

    DetailTable

       ParentId: int

    //Get the parent ID somehow
    var parentId = 1;
    
    var detailTable = ds.Tables["DetailTable"];
    var row = detailTable.NewRow();
    
    //Associate the detail row with the parent in the DB
    row["ParentId"] = parentId;
    detailTable.Rows.Add(row);

    When the dataset is saved the relationship will be validated and saved. To get the parent you can use LINQ or any other approach you already have.

    //Find the parent row with name "PartA"
    var row = (from r in ds.Tables["MasterTable"].Rows.OfType<DataRow>()
              where r["Name"] = "PartA"
              select r).FirstOrDefault();
    
    //Get parent ID, if any
    var parentId = row?.Field<int>("Id") ?? 0;


    Michael Taylor http://www.michaeltaylorp3.net

    Saturday, May 26, 2018 9:32 PM
    Moderator

  • //Get the parent ID somehow
    var parentId = 1;

    Yes looks good.

    It may be, I can query about the relation, but I can not add detail recordsI have to determine the parent ID, however.

    var row = (from r in ds.Tables["MasterTable"].Rows.OfType<DataRow>()
              where r["Name"] = "PartA"
              select r).FirstOrDefault();

    Is that right? Via relation, is not possible to add records, rows.

    That's important to me. To see how to create it. THANK YOU.

    With best regards Markus


    Tuesday, May 29, 2018 4:45 PM
  • Looking at your database screenshot it appears that your Variables table contains a set of values. Your Contents table has its own ID and it refers to a single value in the Variables table using the IDVariables column?

    static void Main ( string[] args )
    {
        //Building the schema
        var dsTEST = new DataSet();
        var dtContents = dsTEST.Tables.Add("Contents");
        var pkContent = dtContents.Columns.Add("ID", typeof(int));
        dtContents.Columns.Add("IDVariables", typeof(int));
        dtContents.PrimaryKey = new[] { pkContent };
    
        var dtVariables = dsTEST.Tables.Add("Variables");
        var pkVariables = dtVariables.Columns.Add("ID", typeof(int));
        dtVariables.Columns.Add("Variable", typeof(string));
        dtVariables.PrimaryKey = new[] { pkVariables };
    
        var relation = new DataRelation("FK_Variables_Contents", dtVariables.Columns["ID"], dtContents.Columns["IDVariables"], true);
                
        //Adding some data to play with
        dtContents.Rows.Add(1, 1);
        dtContents.Rows.Add(2, 1);
        dtContents.Rows.Add(3, 2);
        dtContents.Rows.Add(4, 1);
    
        dtVariables.Rows.Add(1, "Var1");
        dtVariables.Rows.Add(2, "Var2");
    
        //Queries
    
        //Get all content that use the given variable id
        var variableId = 1;
        var contents = from r in dtContents.Rows.OfType<DataRow>()
                        where r.Field<int>("IDVariables") == variableId
                        select r;
    
        //Get the variable associated with the content
        var contentId = 4;
        var content = dtContents.Rows.OfType<DataRow>().FirstOrDefault(r => r.Field<int>("ID") == contentId);
        var variable = (from r in dtVariables.Rows.OfType<DataRow>()
                        where r.Field<int>("ID") == content.Field<int>("IDVariables")
                        select r).FirstOrDefault();
    
        //Add a new content item associated with the variable
        var newContent = dtContents.Rows.Add(-1, variable["ID"]);
    
        //Add a new variable 
        var newVariable = dtVariables.Rows.Add(-1, "Var3");
    
        //Set the original content to the new variable
        content["IDVariables"] = newVariable["ID"];
    }


    Michael Taylor http://www.michaeltaylorp3.net

    Tuesday, May 29, 2018 5:52 PM
    Moderator
  • Hi CoolDadTx,
    Looks good, thanks.
    I still have 2 points that could explain it to me.
    a) r.Field<int>("IDVariables")
       The spelling is unusual, is it as fast as this representation? item["IDVariables"] item[1]?
    b) Where are the advantages of the relationship?
       var relation = new DataRelation("FK_Variables_Contents", dtVariables.Columns["ID"], dtContents.Columns["IDVariables"], true);


       Can you create a typical example?
          Is it only for query, report or can I also add new rows?

    public void Exceute2()
    {
    	//Building the schema
    	var dsTEST = new DataSet();
    	var dtContents = dsTEST.Tables.Add("Contents");
    
    	var pkContent = dtContents.Columns.Add("ID", typeof(int));
    	dtContents.Columns.Add("IDVariables", typeof(int));
    	dtContents.Columns.Add("Name", typeof(string));
    
    	dtContents.PrimaryKey = new[] { pkContent };
    
    	var dtVariables = dsTEST.Tables.Add("Variables");
    	var pkVariables = dtVariables.Columns.Add("ID", typeof(int));
    	dtVariables.Columns.Add("Variable", typeof(string));
    	dtVariables.PrimaryKey = new[] { pkVariables };
    
    	var relation = new DataRelation("FK_Variables_Contents", dtVariables.Columns["ID"], dtContents.Columns["IDVariables"], true);
    
    	//Adding some data to play with
    	dtContents.Rows.Add(1, 1, "Content1");
    	dtContents.Rows.Add(2, 1, "Content2");
    	dtContents.Rows.Add(3, 2, "Content3");
    	dtContents.Rows.Add(4, 1, "Content4");
    
    	dtVariables.Rows.Add(1, "Var1");
    	dtVariables.Rows.Add(2, "Var2");
    
    	//Queries
    
    	//Get all content that use the given variable id
    	var variableId = 1;
    	var contents = from r in dtContents.Rows.OfType<DataRow>()
    				   where r.Field<int>("IDVariables") == variableId
    				   select r;
    
    	foreach (var item in contents)
    	{
    		Trace.WriteLine($"ID={item.Field<int>("ID")}, ID={item.Field<int>("IDVariables")}");
    
    		//Trace.WriteLine($"ID={item[0]}, ID={item[0]["IDVariables"]}");
    		Trace.WriteLine($"ID={item[0]}, ID={item["IDVariables"]}, Name ={item["Name"]}");
    	}
    
    	//Get the variable associated with the content
    	var contentId = 4;
    	var content = dtContents.Rows.OfType<DataRow>().FirstOrDefault(r => r.Field<int>("ID") == contentId);
    	var variable = (from r in dtVariables.Rows.OfType<DataRow>()
    					where r.Field<int>("ID") == content.Field<int>("IDVariables")
    					select r).FirstOrDefault();
    
    	//Add a new content item associated with the variable
    	var newContent = dtContents.Rows.Add(-1, variable["ID"]);
    
    	//Add a new variable 
    	var newVariable = dtVariables.Rows.Add(-1, "Var3");
    
    	//Set the original content to the new variable
    	content["IDVariables"] = newVariable["ID"];
    
    	Trace.WriteLine($"~~~~~~~~~~~");
    	foreach (var item in dtContents.Rows.OfType<DataRow>())
    	{
    		Trace.WriteLine($"ID={item[0]}, IDVariables={item["IDVariables"]}, Name ={item["Name"]}");
    	}
    }

    Wednesday, May 30, 2018 4:42 PM
  • a) r.Field<int>("IDVariables")

    Same purpose but different syntax. If you do r["IDVariable"] then you get back an object. If you wanted it to be, say an int, you'd have to either cast it yourself or call Convert. The Field method does that automatically for you. It simply returns the already cast value to you so you can consume it directly.

    Speed wise it is slower because it has to do a typecast but if you are going to do the typecast anyway then it takes the same amount of time.

    b) Where are the advantages of the relationship?

    Not really any benefit here. I was just replicating the data that you had shown in your table image. 

    Is it only for query, report or can I also add new rows?

    Yes, the last couple of lines showed adding a new Contents and a new Variables row to the table and then associating them. When you're working with the DataTable directly you can CRUD the data as needed.



    Michael Taylor http://www.michaeltaylorp3.net

    Wednesday, May 30, 2018 5:55 PM
    Moderator