none
DataTable.Copy() throws exception for tables with relations RRS feed

  • Question

  • Hi there,

    there is a problem in the Copy method in the DataTable if the table has relations.

    ie: if we make table A and table B and relate them by a relation, then we add to table A column based on expression like "Parent(TableBRelation).Name", when we copy table A to new table C it throws an exception in the Copy method says that he can't find the "TableBRelation" relation.

    any one help please?



    Akram MellIce
    Wednesday, October 28, 2009 12:14 PM

All replies

  • Hi Akram,

    Try cloning the datatable first and then using the ImportRow to copy the datarows across. I dont know why the copy doesnt copy across relations but it does seem to be a common problem.
    Thursday, October 29, 2009 2:44 AM
  • Hi Akram,


    I test a simple code block and it works well,

                DataTable dt1 = new DataTable();
                dt1.Columns.Add(new DataColumn("CustID"));
                dt1.Rows.Add(1);
                dt1.Rows.Add(2);
                dt1.Rows.Add(3);

                DataTable dt2 = new DataTable();
                dt2.Columns.Add(new DataColumn("CustID"));
                dt2.Rows.Add(2);

                DataSet ds = new DataSet();
                ds.Tables.Add(dt1);
                ds.Tables.Add(dt2);

                DataColumn parentColumn =
        ds.Tables[0].Columns["CustID"];
                DataColumn childColumn =
                    ds.Tables[1].Columns["CustID"];
                // Create DataRelation.
                DataRelation relCustOrder;
                relCustOrder = new DataRelation("CustomersOrders",
                    parentColumn, childColumn);
                // Add the relation to the DataSet.
                ds.Relations.Add(relCustOrder);

                DataTable dt3 = dt1.Copy();

    Could you provide the full error message of it?
    And it is favorable if you provide the sample code for us to reproduce your problem.


    Best Regards
    Yichun Feng


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Friday, October 30, 2009 10:35 AM
  • Before using the copy method remove relationship constraints
    ds.enforceconstraints = False
    Dim dt as new datatable
    dt = OriginalDataTable.copy()
    ds.enforceconstraints = true

    remember Copy() method doesn't copy over the constraints and the schema of the original table, you need to re-assign primarykey to the new datatable.


    dt.constraints.add("pkID", dt.columns(0),true)




    John
    • Proposed as answer by Codernater Monday, November 2, 2009 3:19 PM
    • Marked as answer by Yichun_Feng Wednesday, November 4, 2009 2:18 AM
    • Unmarked as answer by akram mellIce Friday, November 6, 2009 8:19 PM
    • Unproposed as answer by akram mellIce Friday, November 6, 2009 8:22 PM
    Monday, November 2, 2009 3:19 PM
  • Hi JohnFL,

    I tried to do what you said, but still getting the same problem.

    and for technocrat_aspire,

    the clone method also throws the same exception.

    and for Yichun_Feng

    Your code is not the case I wanted, because the new column is based on expression, and that what causes the problem as the exception states that "can't find the relation .."

    and below is my code

     
     dbconnection.Open();
                        DataSet ds = new DataSet();
    
                        SqlCommand selectCommand = new SqlCommand(String.Empty, dbconnection);
                        selectCommand.CommandText = "SELECT * FROM STOCK";
                        SqlDataAdapter stockAdapter = new SqlDataAdapter(selectCommand);
                        DataTable stocks = new DataTable("Stock");
                        stockAdapter.TableMappings.Add("Table", "Stock");
                        ds.Tables.Add(stocks);
                        stockAdapter.Fill(ds, stocks.TableName);
    
                        SqlCommand selectCatCommand = new SqlCommand(String.Empty, dbconnection);
                        selectCatCommand.CommandText = "SELECT * FROM CATEGORY";
                        SqlDataAdapter cat = new SqlDataAdapter(selectCatCommand);
                        DataTable category = new DataTable("Category");
                        cat.TableMappings.Add("Table", "Category");
                        ds.Tables.Add(category);
                        cat.Fill(ds, category.TableName);
    
                        DataRelation categoryStockRelation = new DataRelation("Category_Stock", ds.Tables["Category"].Columns["ID"], ds.Tables["Stock"].Columns["CategoryID"]);
                        ds.Relations.Add(categoryStockRelation);
    
                        dbconnection.Close();                    
    
                        string categoryStockRelationExpression = "Parent(Category_Stock).NAME";
                        DataColumn categoryNameCol = new DataColumn("CategoryName", typeof(string), categoryStockRelationExpression);
    
                        stocks.Columns.Add(categoryNameCol);                    
    
                        filtered = stocks.Copy();


    the exception is thrown in the last line states that :

    Exception Message
    Cannot find the parent relation 'Category_Stock'.

    Exception Source
    System.Data

    Exception Stack Trace
       at System.Data.LookupNode.Bind(DataTable table, List`1 list)
       at System.Data.DataExpression.Bind(DataTable table)
       at System.Data.DataExpression..ctor(DataTable table, String expression, Type type)
       at System.Data.DataColumn.set_Expression(String value)
       at System.Data.DataTable.CloneTo(DataTable clone, DataSet cloneDS, Boolean skipExpressionColumns)
       at System.Data.DataTable.Clone(DataSet cloneDS)
       at System.Data.DataTable.Clone()
       at System.Data.DataTable.Copy()


    Akram MellIce
    Friday, November 6, 2009 8:19 PM

  • You are getting exception because once you run the copy method it's copying the expression along and since your expression is referring to a relationship that has not been established with the filtered table yet you are getting an exception.

    What's the purpose or your objective of the copy method? what are you trying to accomplish?



    John
    Friday, November 6, 2009 10:53 PM
  • Hi johnFL,

    Yes you are correct, the exception is thrown because the filtered table doesn't know about the relation and the added column...
    what I need is a copy from the stocks table to make some operation on the copy that may tweak the table, but I need the original too.


    Thanks..
    Akram MellIce
    Saturday, November 7, 2009 8:38 AM

  • This Code should work, I tested on Northwind Database. Basically I creating a new datatable then adding columns with datatype to it then populating the datatable without bringing the expression or the relationship.

    public DataSet ds;
    public DataTable dtCat;
    public DataTable dtProd;
    
    
    
    private void // ERROR: Handles clauses are not supported in C# Form1_Load(object sender, System.EventArgs e)
    {
        
        ds = new DataSet();
        
        //Get Categories Table from Northwind Database
        SqlCommand cmdCategories = new SqlCommand("select * from dbo.Categories");
        GetDataTable("Categories", cmdCategories);
        dtCat = ds.Tables("Categories");
        dtCat.Constraints.Add("pkCategoryID", dtCat.Columns("CategoryID"), true);
        {
            dtCat.Columns("CategoryID").AutoIncrement = true;
            dtCat.Columns("CategoryID").AutoIncrementSeed = -1;
            dtCat.Columns("CategoryID").AutoIncrementStep = -1;
        }
        
        //Get Products Table from Northwind Database
        SqlCommand cmdProducts = new SqlCommand("Select * from dbo.Products");
        GetDataTable("Products", cmdProducts);
        dtProd = ds.Tables("Products");
        dtProd.Constraints.Add("pkProductID", dtProd.Columns("ProductID"), true);
        {
            dtProd.Columns("ProductID").AutoIncrement = true;
            dtProd.Columns("ProductID").AutoIncrementSeed = -1;
            dtProd.Columns("ProductID").AutoIncrementStep = -1;
        }
        
        ds.Relations.Add(new DataRelation("relCategories_Products", dtCat.Columns("CategoryID"), dtProd.Columns("CategoryID"), true));
        
        dtProd.Columns.Add("CategoryName", typeof(System.String), "Parent(relCategories_Products).CategoryName");
        
        //Add New DataTable 
        DataTable dt = new DataTable();
        dt.TableName = "NewTable";
        foreach (DataColumn dtColumn in dtProd.Columns) {
            dt.Columns.Add(dtColumn.ColumnName, dtColumn.DataType);
        }
        
        foreach (DataRow dtRow in dtProd.Rows) {
            DataRow NewRow = dt.NewRow;
            foreach (DataColumn dtColumn in dtProd.Columns) {
                NewRow(dtColumn.ColumnName) = dtRow(dtColumn.ColumnName);
            }
            dt.Rows.Add(NewRow);
        }
        
        
        this.DataGridView1.DataSource = dtCat;
        this.DataGridView2.DataSource = dtProd;
            
        this.DataGridView3.DataSource = dt;
    }
    
    public DataTable GetDataTable(string dtName, SqlCommand Cmd)
    {
        DataTable dt = new DataTable();
        using (SqlConnection Conn = new SqlConnection(My.Settings.MyConnection)) {
            Cmd.Connection = Conn;
            Cmd.CommandType = CommandType.Text;
            Conn.Open();
            using (SqlDataAdapter Adapter = new SqlDataAdapter(Cmd)) {
                Adapter.Fill(ds, dtName);
            }
        }
        return dt;
    }

    John
    • Edited by Codernater Sunday, November 8, 2009 12:34 AM adding stuff
    • Proposed as answer by Codernater Thursday, November 12, 2009 11:00 AM
    • Unproposed as answer by akram mellIce Monday, November 16, 2009 11:43 AM
    • Proposed as answer by Codernator Friday, January 1, 2010 12:06 AM
    Sunday, November 8, 2009 12:32 AM
  • Okay I understand that, but I need the columns I added with expressions

    Thanks
    Akram MellIce
    Monday, November 16, 2009 11:44 AM
  • Hi Akram,

    As I understand you have Table1 and Table2, you have a relationship established between Table1 -> Table2 and you are trying to copy Table1 to Table3. I believe the exception is coming from Table3 since Table3 don't know the relation "TableBRelation" you will need to remove or rename the relation from Table1->Table2 (TableBRelation) and create one for Table3->Table2 before copy.

    Please let me know if this helps
    Juan
    This posting is provided "AS IS" with no warranties, and confers no rights.
    Wednesday, November 18, 2009 7:51 PM
    Moderator