Pages

Tuesday, August 23, 2016

Issuing Direct SQL from MVC5

Recently I needed to add a quick updater that added a value to Table1 when someone copied data from Table2.
Now, the normal way to do this with Entity Framework is to load the entity change the value, then save the changed entity.
But I am a SQL guy from way back, and rather than fetch the entire record and write it all back through Entity Framework, I figured it would be way more efficient if I just sent the database an update directly. Turns out that for us old SQL developers, it's terribly easy to do just that.
Here is my example subroutine, for your dining pleasure.
/// <summary>
/// Updates a Toolkit with the number of the Assignment ID Created from it.
/// </summary>
/// <param name="ToolkitID">The toolkit ID to be updated</param>
/// <param name="AssignmentID">The AssignmentID to attach</param>
public static void UpdateToolkitWithAssignmentID(int ToolkitID, int AssignmentID)
{
    using (ORM db = new ORM())
    {
        try
        {
            db.Database.ExecuteSqlCommand(
                "UPDATE [dbo].[ToolkitRequests] SET[AssignmentID] = @AID Where[UniqueId] = @ID",
                new SqlParameter("@AID", AssignmentID),
                new SqlParameter("@ID", ToolkitID)
                );
        }
        finally
        {
            db.Dispose();
        }
    }
}

That's it! It's super fast, and there is no mucking about in Entity Framework. NOTE that all the EF validity checking is skipped when you do this, so use it in cases where it's a simple SQL and you can ensure data integrity yourself. In my case, there is no way to get in this routine without valid values in both params, so I know I won't get surprised with a stray NULL.

Issuing Direct SQL from MVC5

Recently I needed to add a quick updater that added a value to Table1
when someone copied data from Table2.

Now, the normal way to do this with Entity Framework is to load the
Entity, and change the value, then save the changed entity.

But I am a SQL guy from way back, and rather than fetch the entire
record and write it all back through Entity Framework, I figured it
would be Way more efficient if I just sent the database an update
directly. Turns out that for us old SQL developers, it's terribly easy
to do just that.

Here is my example subroutine, for your dining pleasure.


        /// 
        /// Updates a Toolkit with the number of the Assignment ID Created from it. If you are trying to remove an assignment id from a toolkit, use RemoveAssignmentFromToolkit()
        /// 
        /// The toolkit ID to be updated
        /// The AssignmentID to attach
        public static void UpdateToolkitWithAssignmentID(int ToolkitID, int AssignmentID)
        {
            using (ORM db = new ORM())
            {
                try
                {
                    db.Database.ExecuteSqlCommand(
                        "UPDATE [dbo].[ToolkitRequests] SET[AssignmentID] = @AID Where[UniqueId] = @ID",
                        new SqlParameter("@AID", AssignmentID),
                        new SqlParameter("@ID", ToolkitID)
                        );
                }
                finally
                {
                    db.Dispose();
                }
            }
        }









That's it! It's super fast, and there is no mucking about in Entity
Framework. NOTE that all the EF validity checking is skipped when you
do this, so use it in cases where it's a simple SQL and you can ensure
data integrity yourself. In my case, there is no way to get in this
routine without valid values in both params, so I know I won't get
surprised with a stray NULL.


---
This email has been checked for viruses by Avast antivirus software.
https://www.avast.com/antivirus

Share This!

Contact Us

Name

Email *

Message *