none
Alternate views for Insert, Update, and Delete RRS feed

  • Question

  • I have a database that uses views to perform ACL based row filtering.
    So for the Persons table,  I have view_Persons_READ, view_Persons_CHANGE, and view_Persons_DELETE.

    Database users only have SELECT permissions on view_Persons_READ; SELECT, INSERT, and ALTER on view_Persons_CHANGE; and SELECT and DELETE on view_Persons_DELETE.

    Each view returns a different set of rows based on a users access to those rows.

    In L2S, I have the Person data class with it's source set to dbo.view_Persons_READ.
    I'd like L2S to use view_Persons_CHANGE when performing Insert and Update,  and view_Persons_DELETE when performing delete.

    Is there any way to specify an alternate view to use, or am I limited to using an alternate stored proc to handle the commands?
    Tuesday, August 4, 2009 7:03 PM

Answers

  • Hi PHallisey,

    As far as I know, we cannot only generate the SQL code and not execute it by ExecuteDynamicUpdate method.   In the UpdateOrder method, we need to build certain update SQL commands based on the order instance’s properties.   Besides, I think only replace the data table name in the update SQL commands in the LINQ to SQL log cannot realize your target successfully, because the command uses some SQL parameters. 

     

    I recommend you to check the SQL commands created by LINQ to SQL, and then build and run your own commands in LINQ to SQL update, insert, and delete partial methods, like:

    ==============================================
    Me.ExecuteCommand("INSERT INTO view_Person_CHANGE([PersonID], [PersonName]) VALUES (@p0, @p1)", 1, "Person1")

    ==============================================

     

    Have a nice weekend!

     

    Best Regards,
    Lingzhi


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Friday, August 7, 2009 10:59 AM
    Moderator

All replies

  • I have discovered the wonders of the Partial Context Classes and the ability to impliment alternate update, insert, and delete procedures.   I believe this could be an excelent way to alter the change behaviors to replace the table name just before SQL execution.  My plan is to allow LINQ to SQL to generate the SQL  Update script, but then capture the script before execution, swap in my new view name, then do a simple DataContext.ExecuteCommand:

    Partial Class DataClasses1DataContext
        Private Partial Sub UpdateOrder(ByVal instance As Order)
            Dim UpdLog as New IO.StringWriter
            Me.Log = UpdLog
            Me.ExecuteDynamicUpdate(instance)
    
            Dim NewCommand = UpdLog.ToString
            NewCommand = NewCommand.Replace("UPDATE OldTableName", "UPDATE view_TableName_CHANGE")
    
            Me.Log = Nothing
            Me.ExecuteCommand(NewCommand)
        End Sub
    End Class
    The problem with the example above is that the command is executed at "Me.ExecuteDynamic...", and I just want the SQL generated, but not executed.

    Any suggestions?
    Wednesday, August 5, 2009 3:21 AM
  • Hi PHallisey,

    As far as I know, we cannot only generate the SQL code and not execute it by ExecuteDynamicUpdate method.   In the UpdateOrder method, we need to build certain update SQL commands based on the order instance’s properties.   Besides, I think only replace the data table name in the update SQL commands in the LINQ to SQL log cannot realize your target successfully, because the command uses some SQL parameters. 

     

    I recommend you to check the SQL commands created by LINQ to SQL, and then build and run your own commands in LINQ to SQL update, insert, and delete partial methods, like:

    ==============================================
    Me.ExecuteCommand("INSERT INTO view_Person_CHANGE([PersonID], [PersonName]) VALUES (@p0, @p1)", 1, "Person1")

    ==============================================

     

    Have a nice weekend!

     

    Best Regards,
    Lingzhi


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Friday, August 7, 2009 10:59 AM
    Moderator