locked
DataRelations not working RRS feed

  • Question

  • Hi all,

    I have created a dataset with two datatables namely "category " and "item".

    These two tables are related in a way that "item" has a foreign key "i_cname" which refers to the primary key of category namely "c_name".

    ForeignKeyRelation has been established as follows

     

     public void createRelationships()
            {
                //creating relationships between the datatables in the dataset
                //between items and categories
                ForeignKeyConstraint item_cat = new ForeignKeyConstraint("CategoryItem",DataSetForAll.Tables[2].Columns["c_name"],DataSetForAll.Tables[1].Columns["i_cname"]);
                item_cat.DeleteRule = Rule.Cascade;
                DataSetForAll.Tables[1].Constraints.Add(item_cat);
            }

    tables[2] is "category" and tables[1] is "item".

    Am i supposed to do something else?Do i need to add a DataRelation also?

    The error i am getting is that "Delete statement conflicted with the Reference constraint".

    Any suggestions or solution?

    Thanks...

     


    shekhar mishra
    Thursday, July 28, 2011 2:01 PM

Answers

  • Primary keys are the most widely used constraint, but you can also add unique key and foreign key constraints to a DataTable. The DataTable has three types of constraints {UniqueConstraint, PrimaryKey, ForeignKey}. You generally won't need to create a ForeignKeyConstraint. Creating a DataRelation between the Two DataTable objects within your DataSet creates a ForeignKeyConstraint in the process.

    John Grove, MCC - Senior Software Engineer


    • Proposed as answer by JohnGrove Friday, July 29, 2011 2:18 PM
    • Marked as answer by Cookie Luo Tuesday, August 2, 2011 2:02 AM
    Thursday, July 28, 2011 2:28 PM

All replies

  • Here is how to use the DataRelation object. Example form the famous Microsoft pubs database:

     

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.Data.SqlClient;
    using System.Data;
    
    namespace DataRelationExample
    {
      class Program
      {
        static void Main(string[] args)
        {
          ForumExample();
        }
    
        private static void ForumExample()
        {
          string connString, sql;
          connString = @"Data Source=MyServer;Initial Catalog=pubs;Integrated Security=SSPI";
          SqlConnection cn = new SqlConnection(connString);
          cn.Open();
          SqlDataAdapter daAuthors, daTitles, daTitleAuthor;
          
          //authors table
          sql = "SELECT au_id, au_lname, au_fname FROM authors";
          daAuthors = new SqlDataAdapter(sql, cn);
          
          //title table
          sql = "SELECT title_id, title FROM titles";
          daTitles = new SqlDataAdapter(sql, cn);
          
          //titleauthor
          sql = "SELECT au_id, title_id FROM titleauthor";
          daTitleAuthor = new SqlDataAdapter(sql, cn);
          
          //fill tables
          DataSet ds = new DataSet();
          daAuthors.Fill(ds, "authors");
          daTitles.Fill(ds, "titles");
          daTitleAuthor.Fill(ds, "titleauthor");
    
          //Add relations  
          ds.Relations.Add("authors_titleauthor",
            ds.Tables["authors"].Columns["au_id"],
            ds.Tables["titleauthor"].Columns["au_id"],
            false);
          ds.Relations.Add("titles_titleauthor",
            ds.Tables["titles"].Columns["title_id"],
            ds.Tables["titleauthor"].Columns["title_id"],
            false);
            
          //display the join
          foreach (DataRow row in ds.Tables["authors"].Rows)
          {
            Console.WriteLine("{0}, {1}", row["au_lname"], row["au_fname"]);
            foreach (DataRow rowTitleAuthor in row.GetChildRows("authors_titleauthor"))
            {
              DataRow rowTitle = rowTitleAuthor.GetParentRow("titles_titleauthor");
              Console.WriteLine("\t" + rowTitle["title"]);
            }
          }
          Console.ReadLine();
        }
      }
    }
    
    



    John Grove, MCC - Senior Software Engineer
    Thursday, July 28, 2011 2:15 PM
  • Primary keys are the most widely used constraint, but you can also add unique key and foreign key constraints to a DataTable. The DataTable has three types of constraints {UniqueConstraint, PrimaryKey, ForeignKey}. You generally won't need to create a ForeignKeyConstraint. Creating a DataRelation between the Two DataTable objects within your DataSet creates a ForeignKeyConstraint in the process.

    John Grove, MCC - Senior Software Engineer


    • Proposed as answer by JohnGrove Friday, July 29, 2011 2:18 PM
    • Marked as answer by Cookie Luo Tuesday, August 2, 2011 2:02 AM
    Thursday, July 28, 2011 2:28 PM
  • Thanks for all replies,

    I figured out the problem being delete rule not defined in the database table,i.e On Delete Cascade.

    Once i did this from management studio,everything wotks fine

     


    shekhar mishra
    Thursday, July 28, 2011 2:42 PM
  • Yea, also the ForeignKeyConstraint class has a DeleteRule Property. The AcceptRejectRule, DeleteRule, and UpdateRule properties control how or whether changes in a parent row cascade to the child rows.
    John Grove, MCC - Senior Software Engineer
    Thursday, July 28, 2011 2:44 PM