Master and Detail record (Create, Update, Delete) in same view with ASP.net MVC 3

Question

I am new in ASP.net MVC. I can work with single table data but now i want to create a view where i can create a master and multiple child record.

It will be good if any one give me a link or sample project from where i can learn about it.

I have searched severeal forum thread but unable to find any good resource.

i've checked http://www.dotnet-programming.com/post/2010/10/30/Defining-MVC-Controls-2-Using-the-DataGrid.aspx

but i need another one which one is very simple and easy to learn.

I need something like

Master Record:

Order ID: OR1            Order Date : 21.04.2011

Child Record

Item ID            Qty                  Price
---------------------------------------
101                     2                     50
102                     3                     15

Hasibul Haque · Answer

Please Check following link. http://code.msdn.microsoft.com/Detail-CRUD-Operations-fbe935ef http://blog.e-rains.com/?p=200

Jiteendra Sampathirao · Answer

hi, refer this This Link Link (videos)

Suthish Nair · Answer

did you tried http://www.asp.net/mvc

Raja Krishnamurthy · Answer

I don't know how far you are familiar with ViewModels (if you are not then google it...very useful for MVC). Your scenario requires a view model which has properties for Master Record like OrderID, OrderDate and one list of OrderItems. Then use this as the Model for your view. HTH.

Hasibul Haque · Answer

Hi, krish, Thanks for replay. Actually i am not searching for SQL View. I need help on ASP.net MVC3 (Razor/asp) view for master and detail record.

Crish · Answer

The definition of schema will be encrypted and stored as object in the database. This can be done using the ENCRYPTION option in the view creation. IF OBJECT_ID ( '[DBO].Vw_SqlObjects_Encrypted' ) IS NOT NULL BEGIN DROP VIEW [DBO] . Vw_SqlObjects_Encrypted PRINT ' >' END GO CREATE VIEW [DBO] . Vw_SqlObjects_Encrypted WITH ENCRYPTION AS SELECT O . Object_ID , O . Name , 'Type' = CASE O . type WHEN 'S' THEN 'Scalar Functions' WHEN 'F' THEN 'Functions' WHEN 'V' THEN 'Views' WHEN 'PK' THEN 'Primary keys' WHEN 'TR' THEN 'Triggers' WHEN 'P' THEN 'Procedures' WHEN 'U' THEN 'User Defined Functions' WHEN 'TF' THEN 'Table Valued Functions' WHEN 'IF' THEN 'Inline Functions' END , O . create_date , O . modify_date , CASE WHEN SC . encrypted = 0 THEN 'No' ELSE 'Yes' END AS [IsEncrypted] , SC . text FROM SYS.OBJECTS O INNER JOIN SYSCOMMENTS SC ON SC . id = O . object_id GO IF OBJECT_ID ( '[DBO].Vw_SqlObjects_Encrypted' ) IS NOT NULL BEGIN PRINT ' >' END GO Now if you want to see the view schema definition for the above view is not possible. We have stored in the encrypted format. This is a significant option to hide the important calculations inside the view from the others. In case of any alter in the view must be stored externally somewhere else. SELECT text FROM SYSCOMMENTS WHERE id = OBJECT_ID ( '[DBO].Vw_SqlObjects_Encrypted' ) SELECT definition FROM SYS.sql_modules WHERE object_id = OBJECT_ID ( '[DBO].Vw_SqlObjects_Encrypted' ) sp_helptext Vw_SqlObjects_Encrypted If you execute the above queries then it will say like view is encrypted. There are three types of views in the sql server 2005. They are Normal or Standard view Indexed or permanent view Partitioned view Normal or Standard view: This view is most frequently used by the developers. When create the view the schema will be stored an object in the database. When we retrieve the content from this virtual table, it will be executed the schema and stored the data from the parent table. Here if you have the result from the same table then it can be updated and inserted. The deleted row will be reflected in the original table. USE [Northwind] GO IF OBJECT_ID ( '[DBO].vw_ViewProducts' , 'V' ) IS NOT NULL BEGIN DROP VIEW [DBO] . vw_ViewProducts PRINT ' >' END GO CREATE VIEW [DBO] . vw_ViewProducts AS SELECT ProductID , ProductName , SupplierID , CategoryID , QuantityPerUnit , UnitPrice , UnitsInStock , UnitsOnOrder , ReorderLevel , Discontinued FROM Products GO IF OBJECT_ID ( '[DBO].vw_ViewProducts' , 'V' ) IS NOT NULL BEGIN PRINT ' >' END GO --O/P SELECT * FROM [DBO] . vw_ViewProducts --INSERT INSERT INTO [DBO] . vw_ViewProducts ( ProductName , SupplierID , CategoryID , QuantityPerUnit , UnitPrice , UnitsInStock , UnitsOnOrder , ReorderLevel , Discontinued ) VALUES ( 'Test View' , 1 , 2 , '100 per bag' , 25.45 , 89 , 57 , 15 , 0 ) --DELETE DELETE FROM [DBO] . vw_ViewProducts WHERE ProductID = 81 Here you can do the DML operations in the view when you have only one table. Indexed views: The indexed or permanent view is one of the new features introduced in the sql server 2005 version. We have seen that the view only store the schema definition and it will get execute and load the data into the virtual table at the time of view used. But this view creates the permanent view and we can create the indexes on the table. It allows us to create the instead of trigger. The indexed view can be created with the WITH SCHEMA BINDING option while creating the view. The indexed view has some restrictions like cannot use the TOP, DISTINCT, UNION, ORDER BY and aggregate functions. It allows us to use the GROUP BY statement but we cannot use COUNT statement. Instead of that COUNT_BIG statement can be used. IF EXISTS( SELECT OBJECT_ID FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID ( N '[DBO].Vw_Product_Sales_Report' , N 'V' )) BEGIN DROP VIEW [DBO] . Vw_Product_Sales_Report PRINT ' >' END GO CREATE VIEW [DBO] . Vw_Product_Sales_Report WITH SCHEMABINDING AS SELECT O . OrderID , C . CustomerID , C . CompanyName , C . Address + ', ' + C . City AS [Customer Address] , OD . ProductID , P . ProductName , OD . UnitPrice , OD . Quantity ,( OD . UnitPrice * OD . Quantity ) AS [Total] ,( OD . UnitPrice * OD . Quantity ) * OD . Discount / 100 AS [Discount] FROM [DBO] . Orders O ( NOLOCK ) INNER JOIN [DBO] . "Order Details" OD ( NOLOCK ) ON OD . OrderID = O . OrderID INNER JOIN [DBO] . Customers C ( NOLOCK ) ON C . CustomerID = O . CustomerID INNER JOIN [DBO] . Products P ( NOLOCK ) ON P . ProductID = OD . ProductID GO IF EXISTS( SELECT OBJECT_ID FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID ( N '[DBO].Vw_Product_Sales_Report' , N 'V' )) BEGIN PRINT ' >' END GO Here the indexed view has created. When you retrieve the data from this table, it will execute like normal table. There are some retrictions while creating this indexed view like the name of the view must be two part name and we cannot use select * in the view schema defintion. Normally view cannot have the triggers but from the sql server 2005 onwards We can create the Instead of trigger on the instead of trigger. Partitioned Views: The partitioned view and its execution is like normal view. It will work across the database and across the server. There are two types of Partitioned views. They are Local Partitioned View Global Partitioned View 1. Local Partitioned View: The local partitioned view can be created within same server but different database. The view schema definition will be stored in the executed database. But when we try to retrieve the data from the table, it has to execute the schema definition internally and load the data. Let us see an example. USE [Northwind] GO CREATE TABLE EmployeeList ( iEmployeeID INT IDENTITY ( 1 , 1 ), vFirstName VARCHAR ( 25 ) NOT NULL, vLastName VARCHAR ( 25 ) NOT NULL, iDeptID INT , vAddress VARCHAR ( 25 ) NOT NULL, vCity VARCHAR ( 25 ) NOT NULL, vState VARCHAR ( 25 ) NOT NULL, vCountry VARCHAR ( 25 ) NOT NULL, ) GO USE [Master] GO CREATE TABLE Department ( iDeptID INT IDENTITY ( 1 , 1 ) PRIMARY KEY , vDeptName VARCHAR ( 50 ), vDeptDesc VARCHAR ( 25 ), vDeptAddedBy VARCHAR ( 50 ), vPostedDate DATETIME DEFAULT GETDATE () ) GO --SELECT * FROM Department USE [Northwind] GO IF OBJECT_ID ( '[DBO].vw_LocalPartion_View' , 'V' ) IS NOT NULL BEGIN DROP VIEW [DBO] . vw_LocalPartion_View PRINT '[DBO].vw_LocalPartion_View view dropped...' END GO CREATE VIEW [DBO] . vw_LocalPartion_View AS SELECT E . iEmployeeID , E . vFirstName + SPACE ( 1 )+ E . vLastName AS [Name] , D . vDeptName , E . vAddress , E . vCity , E . vState FROM EmployeeList E --INNER JOIN Master..Department D ON D.iDeptID = E.iDeptID --Either one of the way will be used. INNER JOIN Master . dbo . Department D ON D . iDeptID = E . iDeptID GO IF OBJECT_ID ( '[DBO].vw_LocalPartion_View' , 'V' ) IS NOT NULL BEGIN PRINT '[DBO].vw_LocalPartion_View view created...' END GO --O/p SELECT * FROM [DBO] . vw_LocalPartion_View 2. Global Partitioned View The global Partitioned view will work across the server. The view can be created to join the table across the server. The accessing format will be like this. [Server Name]. Database Name. Table Name When we execute the view if it is not linked with the current server then it will ask us to link the external server. The following system stored procedure will be used to link the server. sp_addlinkedserver 'Server name' The following system catalog table is used to see the list of linked servers. SELECT * FROM SYS.SERVERS INSTEAD OF Triggers on the Indexed View Normally the triggers cannot be created on the view. But sql server 2005 onwards we can create the INSTEAD OF trigger on the indexed views. USE [Northwind] GO IF OBJECT_ID ( '[DBO].[VW_Trigger_Example' ) IS NOT NULL BEGIN DROP VIEW [DBO] . [VW_Trigger_Example] PRINT '[DBO].[VW_Trigger_Example view dropped..' END GO CREATE VIEW [DBO] . [VW_Trigger_Example] WITH SCHEMABINDING AS SELECT P . ProductID , P . ProductName , P . SupplierID , OD . OrderID , OD . UnitPrice , OD . Quantity FROM [DBO] . Products P INNER JOIN [DBO] . [Order Details] OD ON OD . ProductID = P . ProductID GO IF OBJECT_ID ( '[DBO].[VW_Trigger_Example' ) IS NOT NULL BEGIN PRINT '[DBO].[VW_Trigger_Example view created..' END GO --SELECT * FROM VW_Trigger_Example IF OBJECT_ID ( '[DBO].Tr_Delete_TriggerExample' , 'TR' ) IS NOT NULL BEGIN DROP TRIGGER [DBO] . Tr_Delete_TriggerExample PRINT '[DBO].Tr_Delete_TriggerExample trigger dropped..' END GO CREATE TRIGGER [DBO] . Tr_Delete_TriggerExample ON [DBO] . VW_Trigger_Example INSTEAD OF DELETE AS BEGIN PRINT '----------------------------------------' PRINT 'This is an example of INSTEAD OF Trigger' PRINT '----------------------------------------' SELECT TOP 1 * FROM DELETED END GO IF OBJECT_ID ( '[DBO].Tr_Delete_TriggerExample' , 'TR' ) IS NOT NULL BEGIN PRINT '[DBO].Tr_Delete_TriggerExample trigger created..' END GO --O/P --SELECT * FROM [DBO].[VW_Trigger_Example] WHERE ProductID = 11 DELETE FROM [DBO] . [VW_Trigger_Example] WHERE ProductID = 11 How to view the Created Views? There are few ways to view the scehema definition of the created views. SP_HELPTEXT vw_LocalPartion_View SELECT id , text FROM SYSCOMMENTS WHERE id = OBJECT_ID ( '[DBO].vw_LocalPartion_View' ) SELECT object_id , definition FROM SYS.SQL_MODULES WHERE OBJECT_ID = OBJECT_ID ( '[DBO].vw_LocalPartion_View' )

Hasibul Haque · Answer

hi Ankit, Actually I am searching for MVC3 view(form) not MS SQL View

Ankit Nandekar · Answer

visit this articles it will explain u all about view http://www.c-sharpcorner.com/UploadFile/skumaar_mca/viewsinsqlserver200510132009103348AM/viewsinsqlserver2005.aspx http://www.c-sharpcorner.com/UploadFile/shashikantray/1509302008072932AM/15.aspx