Last post Dec 04, 2008 02:13 AM by PanosP
Dec 02, 2008 09:23 AM|PanosP|LINK
Is there any way of displaying fields from more than one table in a dynamic data site? all tables have a common field.
I need to display information from all three tables. How can i manage this using the gridview?
Thanxs in advance
Dec 02, 2008 01:27 PM|dj.net|LINK
you could simply join the tables in your query and bind the resultset to the gridview.
If you provide more details I might be to help out.
Dec 02, 2008 02:31 PM|marcind|LINK
there is no out-of-the-box way to do this in Dynamic Data. Are you using LINQ to SQL or Entity Framework? You would have to create a new table in your model that maps to a SQL view or stored proc on your database server.
Dec 02, 2008 03:30 PM|PanosP|LINK
Hi there, I am using Liq to sql, So if i create a view in my sql server containg the tables i would like to display in the gridview should do it? what happens with the editing functionality of the
Let me present you with my scenario:
I have three tables: TableA,TableB,TableC. Each table have one common field (Primary key in one table and as foreign in the others). So in my gridview i would like to present fields from all three tables . I would also like to be able to update a record
but in one table only. So if the gridview displays : TableA-Field1Field2, TableB-Field1,TableCFieldField2Field3 i would like the record to be updated only in TableC with the new records based ofcourse on the common field.
Any examples on how to use view or stored procedure using the dynamic data site using vb net?
Thanxs in advance
Dec 02, 2008 05:28 PM|ricka6|LINK
Create the view in SQL Server (a SQL Server question, not a Dynamic Data (DD) question). Try updating the view from T-SQL - not all views are
updateable, but if you are using the PK from the update table it should be an
updateable view. You will then need to to make the view
updateable from Link to SQL (L2S). See my blog
How to create an updateable view with ADO Entity Framework and with LINQ to SQL - I'll look for samples on using stored
procs with DD when I return to work in a couple days. Note, using stored
procs is not a DD - it's either a L2S or EF question. DD is a technology the consumes the L2S or EF data model.
Dec 03, 2008 12:36 PM|PanosP|LINK
Hi to all, thaxs for the replies.
I am a beginner to the whole linq to sql so please be patient with me :)
Can any of you provide me with an example of how to accomlish my requirement. If i do this without using the dynamic data site i am just going to create an sql statement with my joins
in the datadource and bind it to the gridview. I can also filter out data using parameters from textboxes etc.
As i really want to learn and practice the dynamic data site concept any more detailed explanations will be greatly appreciated!!
If i include my three tables in the dbml file, how can i select field values from all three tables and present them in the list page? Dynamic data site just lists all three tables.
Thanxs in advance
Dec 03, 2008 03:38 PM|ricka6|LINK
I'll get you started, but most of your question have nothing to do with Dynamic Data DD) - They are questions about SQL and the Linq to SQL (L2S) data model. I will use the AdventureWorksLT2008 Db as an example. Create a 3 table view of the customer, customerAddress
and Address table with the following T-SQL
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE VIEW [SalesLT].[rv3Tbl]
SELECT C.FirstName, C.MiddleName, C.LastName, C.CompanyName,
A.AddressLine1, A.AddressLine2, A.City, A.StateProvince,
FROM SalesLT.Customer AS C
JOIN SalesLT.CustomerAddress AS CA ON C.CustomerID = CA.CustomerID
JOIN SalesLT.Address AS A ON CA.AddressID = A.AddressID
WHERE A.CountryRegion = N'United States'
-- SELECT * FROM [SalesLT].[rv3Tbl]
Notice the last line (which is commented out) - SELECT * FROM [SalesLT].[rv3Tbl] - run that from a query window to test the view.
Next I create a new DD web site, add L2S and add the customer, customerAddress and Address tables. I also add the
rv3Tbl view. Once I enable scaffolding and set my datacontext in global.asax, my default page shows the 3 table and one view. The view data is not editable by default. If you have an updateable view (another SQL topic far beyond the scope of
DD), you can edit your data model to make the view updatable. To do that see my blog
How to create an updateable view with ADO Entity Framework and with LINQ to SQL
You are trying to do some fairly advanced topics on top of DD. You need to first get comfortable with TSQL and L2S (the lower level technologies) that DD consumes.
Dec 04, 2008 02:13 AM|PanosP|LINK
Thanxs for your quidelines and for your excellent example. Now things are more clear in my head, i need to be more confident with Linq to SQL first. I confused DD with Linq to Sql.