locked
Update/insert passing username

    Question

  • Is there a simple way to get LINQ to pass additional args to an insert/update statement? Our tables have audit columns to record the last update (just who/when). Pre-LINQ, we did this by passing the "principal" name into an SP, and updating the columns at the database. We use the "trusted subsystem" model, so a trigger reading the connection's username is no use - it will show the service credentials, not the end-user's.

     

    With LINQ, I guess I need to either:

    * use default LINQ update (etc) methods, but prior to committing changes update these two entity properties - can this be automated somehow? ideally without having to implement on a entity-by-entity basis

    * use existing SP approach for update (etc) methods, but somehow get LINQ to pass the "principal" name in as the @username parameter (currently shows "(None)", since this doesn't really map to any of the entity properties)

     

    Any thoughts?

     

    Marc

    Friday, October 26, 2007 9:14 AM

Answers

  • Interesting. I am confident that with some combination of Update{x} [with ExecuteDynamicUpdate] and/or GetChangeSet(), and reflection and/interfaces - that I can get the job done.

     

    Thanks for the input.

     

    Marc

    Wednesday, October 31, 2007 2:58 PM

All replies

  • Hi Marc,

    I've buit applications using Objects that passed the username as a parameter at database update for auditing purposes.

    But I need to know:

     

    Are you making a Web application using ASP.NET or a Windows Application?

    (I can help if ASP.NET)

     

    Second, Is the audit data are stored with each row in the table or just you use the username to chack and make sure of the user identity and then store the audit info in another place?

     

    Hope I can help you

    Best regards.

     

    Friday, October 26, 2007 12:31 PM
  • It is ASP.NET (although I'm not sure quite how that changes LINQ's behavior...)

     

    For what I am talking about (a simple "last updated" / "by" pair), yes the data is in-row; if we need a detailed audit we can manage that through triggers - it's mainly a case of finding out who to blame...

     

    Marc

    Friday, October 26, 2007 1:36 PM
  • I'll talk about my experience in passing usernames in ASP.NET

     

    Assume you have a table Person that have the follwing Fields

    PersonID int,

    PersonName nvarchar(200),

    LastUpateUser nvarchar(100)

     

    when you map it to an entity there will be a property for the LastUpdateUser

     

    To I used the ASP.NET Membership system for authenticating users, Also I use Profile provider , which stores the UserName in the profile object

     

    Then in the datasoursec object in the ASP.Net Page I use a ProfileParameter in the Insert, Update Parameter collection

     

    <aspStick out tonguerofileParameter Name="LastUpdateUser" PropertyName = "UserName" type="String" />

     

    The same concept can be used with the LinqDataSource:

    • The LinqDataSource will create an old Object and a New Object and changes the New Object properties according to the Bind expression in the DataBound Control (the Editable Fields in a GridView for example)
    • the rest of the properties are left unchanged so that the generated Update SQL Statement will include only the changed fields( which is the most cooool thing in LINQ to SQL)
    • Now the Declared parameter, the LinqDatasource will get the value and add it to the list of changed fields
    • By doing so, you can automatically pass the user name to Linq

    Note: There's a bug in ASP.Net 2.0 which results to not storing the username in the Profile (The Default one)

    To work arround Declare a profile property in the web.config (called MyUserName) and populate it after login

     

    Also Use breakpoints to find out how the username value passed.

    You can use the LinqDataSource Updating Event for that

     

    Try this out and tell me

    Best regards

     

    Friday, October 26, 2007 6:01 PM
  • Well, I can give it a go, but not all uses involve a data-source. I was really hoping for something a bit more central to LINQ itself... I'll keep looking, but thanks.

     

    Marc

    Friday, October 26, 2007 7:48 PM
  • If you're doing Linq calls using DataConext class, you can get benifet from the partial methods provided by the ORM designer.

     

    Create a code file and declare a partial class of the datacontext

     

    For each Entity class there are Insert and Update and Delete Partial methos that are called ate these events. But the partial methods do not need a wire up to work, Just implement them.

     

    Also, you can have a public property in the same data context class that can store your username

     

    For Example

    public partial class MyDataContext

    {

    private string _UserName;

    public string UserName

    {

    get

    {

    return _UserName;

    }

    set

    {

    _UserName = value;

    }

    }

     

    //Now implement the Partial Method OnCreated

    //Or you can Overload the Constructor to set the UserName Value

    partial void OnCreated()

    {

    //You can add additional code here

    _UserName = null;

    }

     

    //Now the most fun thing the Update Partial Method

    //Recall the Person Entity Class Example Above

    //When the ORM creates the DataContext calss it defines this Partial Method

    partial void UpdatePerson(Person instance)

    {

    //Add Code to Assign the username value

     

    instance.LastUpdateUser = UserName;

    }

    }

     

     

     

    Now when the SubmittChanges continues it will generate the proper SQL Code

     

    Hope this will work

    Best Regards

     

    Friday, October 26, 2007 8:48 PM
  • Unless I am mistaken, the Update{Entity} pattern *replaces* the standard LINQ update, so no actual update would occur. For now, I'll look at using the default UPDATE etc, but using the following to auto-update key properties if they exist:

    Code Block

     

    private static void SetAudit(IList<object> items, bool added) {

    DateTime now = DateTime.Now;

    string user;

    try { user = Thread.CurrentPrincipal.Identity.Name; }

    catch { user = "(anon)"; }

    foreach (object item in items) {

    PropertyDescriptorCollection props = TypeDescriptor.GetProperties(item);

    PropertyDescriptor prop = props.Find("LastUpdated",true);

    if (prop == null) prop = props.Find("Last_Updated", true);

    if (prop != null && !prop.IsReadOnly && prop.PropertyType == typeof(DateTime)) {

    prop.SetValue(item, now);

    }

    prop = props.Find("UpdatedBy", true);

    if (prop == null) prop = props.Find("Updated_By", true);

    if (prop != null && !prop.IsReadOnly && prop.PropertyType == typeof(string)) {

    prop.SetValue(item, user);

    }

    if (added) {

    prop = props.Find("DateCreated", true);

    if (prop == null) prop = props.Find("Date_Created", true);

    if (prop != null && !prop.IsReadOnly && prop.PropertyType == typeof(DateTime)) {

    prop.SetValue(item, now);

    }

    prop = props.Find("CreatedBy", true);

    if (prop == null) prop = props.Find("Created_By", true);

    if (prop != null && !prop.IsReadOnly && prop.PropertyType == typeof(string)) {

    prop.SetValue(item, user);

    }

    }

    }

    }

    public override void SubmitChanges(System.Data.Linq.ConflictMode failureMode) {

    ChangeSet cs = this.GetChangeSet();

    if (cs.AddedEntities.Count > 0) SetAudit(cs.AddedEntities, true);

    if (cs.ModifiedEntities.Count > 0) SetAudit(cs.ModifiedEntities, false);

    base.SubmitChanges(failureMode);

    }

     

     

    Sunday, October 28, 2007 9:55 AM
  • Hi  Marc,

    I've looked again in my Linq to SQL learning book to find what's wrong in the sample I wrote.

    Whth the Update method, you can override the normal update behavior of the data context, but you can tell the data context to resume automatic update by calling the base type method ExecuteDynamicUpdate(instsnce) after you've entered the changes to the instsnce.

     

    Watch Bold Code Block

    public partial class MyDataContext

    {

    private string _UserName;

    public string UserName

    {

    get

    {

    return _UserName;

    }

    set

    {

    _UserName = value;

    }

    }

     

    //Now implement the Partial Method OnCreated

    //Or you can Overload the Constructor to set the UserName Value

    partial void OnCreated()

    {

    //You can add additional code here

    _UserName = null;

    }

     

    //Now the most fun thing the Update Partial Method

    //Recall the Person Entity Class Example Above

    //When the ORM creates the DataContext calss it defines this Partial Method

    //You can make your final changes and/or call your customized Update code

    //Also you can continue automatic update by calling ExecuteDynamicUpdate

    partial void UpdatePerson(Person instance)

    {

    //Add Code to Assign the username value

     

    instance.LastUpdateUser = UserName;

    this.ExecuteDynamicUpdate(instance);

    }

    }

     

     

    If you're wondering, what is that book, I've collected the blog posts for Scott Guthrie about C# 3.0 and LINQ to SQL in a little booklet. this link will lead you to Linq to SQL Post # 9 which has links to all the previous ones:

     

    http://weblogs.asp.net/scottgu/archive/2007/09/07/linq-to-sql-part-9-using-a-custom-linq-expression-with-the-lt-asp-linqdatasource-gt-control.aspx

    Hope this coud help.

     

    Wednesday, October 31, 2007 3:57 AM
  • Interesting. I am confident that with some combination of Update{x} [with ExecuteDynamicUpdate] and/or GetChangeSet(), and reflection and/interfaces - that I can get the job done.

     

    Thanks for the input.

     

    Marc

    Wednesday, October 31, 2007 2:58 PM
  •  

    I want to ask you Marc, How do you use the Line:

     

    user = Thread.CurrentPrincipal.Identity.Name;

     

    to get the user name, can it be used with ASP.NET membership?

     

    Thanks

    Wednesday, October 31, 2007 3:34 PM
  • I would imagine that the default provider sets up the principal... this is a common activity in role-based-security. It should take only a few moments to try it. For reference, we use custom login/role providers, so I can't 100% remember how vanilla asp.net behaves. But it works for me in my setup, and allows typical role/security checks at lower levels without having to know anything about asp.net

     

    Marc

    Wednesday, October 31, 2007 3:40 PM