none
How do i create a dataset that updated multiple tables throgh view? RRS feed

  • Question

  • Hi friend,

    I'm pretty much new to using Datasets, i'd prefer to design the dataset using the wizard. I want my dataset to handle these operations:
    1.Optimistic Concurrency
    2. fetch data from multiple data tables
    3. can perform CRUD on multiple data tables

    athe the momemnt i can do 1, 2.

    If i need ot fetch data from multiple tables eg. assume there are two tables Products and ProductCategory. Assume i need to fetch ProductDetails thus i need fetch data from Products and ProductCategory tables. In this case i will create a view called vProductDetails. I could fetch data an send it to my form but i run into problem if the user modifiies the fetched. I dont know how to update data through the view.

    I think that i dont know the pattern that is typically used when retreiveing data from multiple tables and do CRUD on multiple ables.

    should i use views?

    could you provide me some guidence on how to design my dataset to do 1, 2, 3  objecttive above: such as when should i create a view, and how to update base tables through view...etc?

    thanks
    Sunday, December 13, 2009 5:22 PM

Answers

  • Every DataTable has a default DataView inherent in them. Retreiving data from multiple tables requires you establish a DataRelation object between those tables. Let me share with you an example (many to many example) from 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);            
                SqlDataAdapter daAuthors, daTitles, daTitleAuthor;
                sql = "SELECT au_id, au_lname, au_fname FROM authors";
                daAuthors = new SqlDataAdapter(sql, cn);
                sql = "SELECT title_id, title FROM titles";
                daTitles = new SqlDataAdapter(sql, cn);
                sql = "SELECT au_id, title_id FROM titleauthor";
                daTitleAuthor = new SqlDataAdapter(sql, cn);
                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);
                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();
            }
        }
    }
    
    Do you have any ADO.NET books at your disposal? I would recommend David Sceppa or Sahil Malik
    John Grove - TFD Group, Senior Software Engineer, EI Division, http://www.tfdg.com
    • Marked as answer by code_warrior Monday, December 14, 2009 5:42 PM
    Sunday, December 13, 2009 5:58 PM

All replies

  • Every DataTable has a default DataView inherent in them. Retreiving data from multiple tables requires you establish a DataRelation object between those tables. Let me share with you an example (many to many example) from 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);            
                SqlDataAdapter daAuthors, daTitles, daTitleAuthor;
                sql = "SELECT au_id, au_lname, au_fname FROM authors";
                daAuthors = new SqlDataAdapter(sql, cn);
                sql = "SELECT title_id, title FROM titles";
                daTitles = new SqlDataAdapter(sql, cn);
                sql = "SELECT au_id, title_id FROM titleauthor";
                daTitleAuthor = new SqlDataAdapter(sql, cn);
                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);
                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();
            }
        }
    }
    
    Do you have any ADO.NET books at your disposal? I would recommend David Sceppa or Sahil Malik
    John Grove - TFD Group, Senior Software Engineer, EI Division, http://www.tfdg.com
    • Marked as answer by code_warrior Monday, December 14, 2009 5:42 PM
    Sunday, December 13, 2009 5:58 PM
  • thanks for the self descriptive code : )

    i think the code showed me, the questiuon that i had in my mind is if i create a table "titleAuthor" from "title" and "authors", in the dataset "titleauthor" wold be a "virtual table" and it shouldnt be isolated, it soould be linked to parent tables, title and author 


    In my implmenting dataset i have  Product, ProductCategory and ProductDetails (made out of Product and ProductCategory)

    Product is liked to ProductCateggory through "CatIndex" but ProductDetails should be linked with "Prodtcs" as well as "ProductCategory"

    in a simple diagram? 
    Products --> ProductCategory
         \             /
          \           /
           \         / 
            \       /
             V    V
         ProductDetails
     
    Monday, December 14, 2009 4:46 AM