Tech Off Thread

3 posts

Forum Read Only

This forum has been made read only by the site admins. No new threads or comments can be added.

Relational DataTables?

Back to Forum: Tech Off
  • User profile image
    phreaks

    Question:

    What is the best approach when creating a drill-down (web)?

    Can I create a dataset that has multiple related tables?

    If so, what is the best method?

    Do I run my first query to populate the DS (which populates a gridview) and then based upon the selection populate the related tables, or do I create seperate datasets for each 'relation'?

    Perplexed

  • User profile image
    PerfectPhase

    The way I do this depends on the amount of data that the drill down brings in.  Load on demand is the best way to go if there is going to be a lot of data required for the drill down, you can do this by adding code into the TableAdapter via the partial class files.

    Either way you create multiple DataTables in your dataSet and hook them together with relations.

    If you want to bring all the data in in one hit, you can do something like this.

    Create a Sproc in the database along the lines of:

    CREATE PROCEDURE dbo.Visits_SELECTWITHDETAIL_by_VisitId
    (
       @VisitId uniqueidentifier
    )

    AS

    SELECT VisitorID, Title, Surname, FirstName, Company 
       FROM Visitors
       WHERE VisitorId IN (SELECT VisitorId
                                     FROM visits
                                     WHERE VisitId = @VisitID) ;

    SELECT UserID, Title, Surname, FirstName, EmpNumber, [DepartmentId]
       FROM Users
       WHERE UserID IN (SELECT HereToSeeUserId as UserID
                                   FROM visits
                                   WHERE VisitId = @VisitID) ;

    SELECT SiteID, SiteName, ComputerName, LastPingReply
       FROM Sites
       WHERE SiteId IN (SELECT SiteId
                               FROM visits
                               WHERE VisitId = @VisitID) ;

    SELECT VisitId, VisitorId, HereToSeeUserID, SiteId, VisitWindowStart, VisitWindowEnd
       FROM janus_Visits
       WHERE (VisitId = @VisitID)

    RETURN

    Then in your code behind for the table adapter add a function along the lines of

    [System.ComponentModel.DataObjectMethodAttribute (System.ComponentModel.DataObjectMethodType.Select, true)]

    public virtual MyDataSet GetDataSetByVisitId (Guid VisitId)
    {
       MyDataSet ds = new MyDataSet ();

       SqlDataAdapter da = new SqlDataAdapter ("Visits_SELECTWITHDETAIL_by_VisitId", this.Connection);

       da.SelectCommand.CommandType = CommandType.StoredProcedure;
       da.SelectCommand.Parameters.AddWithValue ("@VisitId", VisitId);
       da.TableMappings.Add ("Table", ds.Visitors.TableName);
       da.TableMappings.Add ("Table1", ds.Users.TableName);
       da.TableMappings.Add ("Table2", ds.Sites.TableName);
       da.TableMappings.Add ("Table3", ds.Visits.TableName);
       da.Fill (ds);
      
       return ds;
    }

  • User profile image
    odujosh

    I would highly recommend against pulling all the data at once. Specially if your Data Access Layer is not set up as a Service but is flat code in the App_Code directory or Bin. Unless you plan to share the DataSet accross sessions this is a waste. Your better off pulling the drill down as you need the data.

Conversation locked

This conversation has been locked by the site admins. No new comments can be made.