DataTable.Copy() throws exception for tables with relations

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

Answer 1

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.

Answer 2

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.

Answer 3

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

Answer 4

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

 <pre lang="x-c#"> 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

Answer 5

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

Answer 6

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

Answer 7

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

Answer 8

Okay I understand that, but I need the columns I added with expressions

Thanks

---

Akram MellIce

Answer 9

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.