29 March 2011

Custom Insert/Update on LinqToSql and ASP.NET Dynamic Data

ASP.NET Dynamic Data brings major usability and RAD development changes to the existing ASP.NET data controls. RAD development is significantly increased by the use of a rich scaffolding framework.

If you want to create a custom insert or update for ASP.NET Dynamic data and LinqToSQL then you will have to perform the following steps:
1) Create a partial class for the entity you want to make the insert/update custom. For example if you had a table name employees then your code would look like this:
[ScaffoldTable(true)]
public partial class Employees
{
}



2) Create a Metadata for that class - EmployeesMetadata. Now your code should look like this:
[MetadataType(typeof(EmployeesMetadata))]
[ScaffoldTable(false)]
public partial class Employees
{
    class EmployeesMetadata
    {
        [DisplayName("Person's title")]
        public object Title { get; set; }
    }
}
  
Note: I prefer to put the metadata inside the object which is using it, but you can put it outside. I chose to put it inside because otherwise it will be visible through all the namespace however it is intended to be used only in one place.

3) Now that we have the class above we are able to create our custom Insert and Update methods. Add these methods to the our existing Employees partial class.

Insert Method Code:
public static void Insert(employee instance)
{
     var db = new BasicModelDataContext();

     var employee = new employee
     {
           Title = "myCustomTitle " + instance.Title, // Add "myCustomTitle" string to the existing employee Title inserted from the page
           Name = "My Custom Name" + instance.Name
     };

     db.employees.InsertOnSubmit(employee);
     db.SubmitChanges();
}

Update Method Code:
public static void Update(view_service_point instance)
{
     var db = new BasicModelDataContext();

     var employee=
     db.employees.Where(e => e.id == instance.id).FirstOrDefault();

     employee.Title = instance.Title + " Custom Update";
     employee.Name = instance.Name + "Custom Update";

     db.SubmitChanges();
}

4) Now that you have these two methods that doesn't mean that LinqToSQL will use them instead of the original insert and update action. We need to tell it to use our methods instead. To do that we will need to modify the DataContext. My DataContext's name is "MyDataContext". we need to indicate that instead of insert do our custom insert and instead of update do our custom update.

Code:
public partial class BasicModelDataContext : DataContext
{
        partial void InsertEmployee(Employee instance)
        {
            Employee.Insert(instance);
        }

        partial void UpdateEmployee(Employee instance)
        {
             Employee.Update(instance);
        }
}

This was the final changes, now you have the custom insert and update working.

Any questions let me know.

4 comments:

Laura Antochi said...

Hi there, nice post and thank you for the info. 1. If you put ScaffoldTable(false), the table won't be shown anylonger in the listing of tables, so my question is what's the purpose of doing it so?
2. I've followed step by step your indications, and it seems that when I insert a new object with a custom method, this new method would be called an infinite time if it were't for the error:
"Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached."
Do you have any ideas why this is happening?
Thank you.

Roman Gherman said...

Hi Laura,
1) Sorry, that was my mistake, it should be true, I have amended that.
2) I'm not sure that I can help you with that without seeing the code. I need to see the code for Insert (Your Custom method) and InsertEmployee (the partial method from DataContext)

Thanks for visiting my blog,
Roman

Random said...

Your code does not actually work. Here's why.



The overriding method must not call SubmitChanges or Attach. LINQ to SQL throws an exception if these methods are called in an override method.

Do this instead

partial void InsertUser( User instance )
{
//edit your properties here...
instance.WhatWhat = "hello world";
ExecuteDynamicInsert( instance );
}

Roman Gherman said...

Hi Random,

Most probably is that you have done something wrong, because I have all this working in a fairly big project.

+ I am creating a custom insert update, but you are not, you are doing something different - you try to amend the proprieties of the object before inserting, check if you are not using and SubmitChanges or Attach in that method.

Regards,
Roman

Post a Comment

your thoughts are welcome:

Need more? Leave comments and subscribe to my blog.

.