none
Non-column Stored Proc insert parameter RRS feed

  • Question

  • I have 2 tables in the database (dtEntity and dtEntityActivity).  dtEntityActivity is a log table for modifications to the related record in dtEntity.  I have a stored proc (insert_Entity) that handle the insert of the Entity record.  Inside the proc it generates the log information for the insert event and inserts it into dtEntityActivity.  My issue stems from the fact that I need to reference the entitykey for the user who was performing the insert.

    CREATE PROCEDURE dbo.insert_Entity
    (
    @EntityTypeKey TINYINT,
    @FirstName VARCHAR(100),
    @MiddleName VARCHAR(100),
    @LastName VARCHAR(100),
    @FullName VARCHAR(300),
    @UserEntityKey INT, 
    @EntityKey INT OUTPUT
    )
    	AS
    	
    	DECLARE @EventDescription VARCHAR(1000);
    	
    	INSERT INTO dtEntity
    	(EntityTypeKey, FirstName, MiddleName, LastName, FullName)
    	VALUES
    	(@EntityTypeKey, @FirstName, @MiddleName, @LastName, @FullName)
    	
    	SET @EntityKey = SCOPE_IDENTITY(); 
    	
    	SET @EventDescription = 'Entity: ' + @FullName + ' was created.';
    	
    	INSERT INTO dtEntityActivity
    	(CurrentUserEntityKey, EventDescription)
    	VALUES
    	(@UserEntityKey, @EventDescription)
    	
    	RETURN
    The @UserEntityKey variable is not stored as a column in the dtEntity table.  It is expected that wherever the Linq-to-SQL classes are being consumed this value needs to be set prior to SubmitChanges();  So my question is how can I extend the classes created by the Linq-to-SQL designer so that I can use the stored proc to handle the insert since the logging logic is embedded in the stored procs.  Obviously this is the simplest of the stored procs since the update stored procs do much more checking and comparing of fields (new and original) in order to build the event description.
    Friday, April 23, 2010 6:36 PM

Answers

  • Ed,

    Thanks for the quick reply.  Technically calling as a UDF won't work in my scenarios because the insert into dtEntityActivity is actually handled by a stored proc.  For the purposes of making sure everyone had a clear picture of the intended goal.  As for the issues with Linq and stored procs with more than 1 query, I'm having a hard time finding any reference to that "goober" anywhere on the net.  I would think that it wouldn't matter because that logic is encapsulated at the database level rather than part of the abstraction of Linq classes.  (Whew)

    I did find a way to handle the user key.  I extended (partial) the ModuleDataContext class (inheriting from DataContext) so that it contained a property for the current user key.  I then added constructors to allow me to pass in the key when creating the instance.  This allowed me to place the methods for the stored procs in my extended class.  (Downside: I can't use the designer to add the stored procs, but that's a minor issue)  Since the stored proc methods are now in my extended class I can reference the user key property I created. 

    My current issue is related to MethodInfo.GetCurrentMethod().  When the call for ExecuteMethodCall is hit, I get an exception that it cannot evalute a security function.

    Any ideas?

    -Tom

    Friday, April 23, 2010 9:08 PM

All replies

  • One thought that might work is to convert the proc into a user defined function. Linq has trouble with procs that have multiple queries.

    Another cheesy way to do this is to create a dummy proc (using a the the same name you want) that has one query that has a signiture that you want linq to see.

    Drop that onto the dbml so that it can put up the signiture correctly, then update the proc afterward to whatever you want.

    Cheesy I admit, but it has gotten me out of more than one jam.

     

    Friday, April 23, 2010 7:09 PM
  • Ed,

    Thanks for the quick reply.  Technically calling as a UDF won't work in my scenarios because the insert into dtEntityActivity is actually handled by a stored proc.  For the purposes of making sure everyone had a clear picture of the intended goal.  As for the issues with Linq and stored procs with more than 1 query, I'm having a hard time finding any reference to that "goober" anywhere on the net.  I would think that it wouldn't matter because that logic is encapsulated at the database level rather than part of the abstraction of Linq classes.  (Whew)

    I did find a way to handle the user key.  I extended (partial) the ModuleDataContext class (inheriting from DataContext) so that it contained a property for the current user key.  I then added constructors to allow me to pass in the key when creating the instance.  This allowed me to place the methods for the stored procs in my extended class.  (Downside: I can't use the designer to add the stored procs, but that's a minor issue)  Since the stored proc methods are now in my extended class I can reference the user key property I created. 

    My current issue is related to MethodInfo.GetCurrentMethod().  When the call for ExecuteMethodCall is hit, I get an exception that it cannot evalute a security function.

    Any ideas?

    -Tom

    Friday, April 23, 2010 9:08 PM
  • Hello Tom,

    Please start a new thread for the exception issue. By the way, it would be better if you could provide us a code snippet where your exception occurred.

    Thanks a lot!


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    If you have any feedback, please tell us.
    Welcome to the All-In-One Code Framework!
    Thursday, April 29, 2010 8:40 AM