In one of my previous posts, I detailed using Table Value parameters. That method works great for my uses. However, I ran into one gotcha worth pointing out.
SqlMetaData Gotcha
posted on January 18, 2017 by long2know in ASP.NET, Database
Querying Timezones in MS SQL
posted on November 2, 2016 by long2know in Database
If you’ve read my various posts on dealing with datetimes and timezones lately, I discovered a handy little mechanism for dealing with timezones in Microsoft SQL.
Using Table Value Parameters
posted on May 23, 2016 by long2know in Database, Entity Framework
Over the weekend, I needed to be able to execute a stored procedure from EntityFramework that had a table value parameter (TVP) as an input.
I had never worked with TVP’s before. Utilizing them was not obvious to me from either a code-based approach or even directly with T-SQL. Here’s how I accomplished it, though.
Stored Procedure Progress Messages
posted on April 14, 2016 by long2know in ASP.NET, Database, Entity Framework
Earlier today, I needed to be able to retrieve progress indicators from a long-running stored procedure. The procedure itself executed many different queries, so the idea of sending messages to the client between those queries seemed like a good place to start.
Entity Framework SqlQuery for Stored Procs
posted on March 22, 2016 by long2know in ASP.NET, Database, Entity Framework
If you recall my previous previous post Stored Procs with Entity Framework, I mentioned that you could use SqlQuery<T> to populate a list from a stored procedure.
However, I found that optional parameters must be handled properly.
Updating SQL Aggregate Columns
posted on September 3, 2015 by long2know in Database
Often in dealing with data in a normalized database, aggregate data is stored on within a parent table. This is done to simplify display of data and general querying.
However, it can cause data to become out of sync if the child tables are edited.
Entity Framework Transactions
posted on August 18, 2015 by long2know in Database, Entity Framework
Entity Framework uses the Unit of Work pattern. It’s useful, but it doesn’t quite cover all of the bases.
In using a repository pattern, I rely on EF to simply deal with UOW and have not had occasion to go beyond UOW. However, on a recent project, I found that I needed to manage my own transactions.
ORMs are awful.. long live ORMS!
posted on April 29, 2015 by long2know in ASP.NET, Database
Recently, I had a conversation with another developer about the role of DBA’s in the development process. It was immediately clear that, philosophically, our viewpoints diverged.
The conversation digressed, and I found that this developer was completely against using ORMs to make queries. The term ‘ad-hoc queries’ was bandied about quite a bit. This other developer also went so far as to tell me that, in a code review in ‘hard-core’ dev shops, ad-hoc queries would get smacked down.
Optimizing Entity Framework
posted on April 27, 2015 by long2know in ASP.NET, Database, Entity Framework
In my previous post discussing profiling Entity Framework, I alluded to DbInterceptors as being able to provide lots of useful functionality. In this post I’ll expand on the DbInterceptor and show a few optimizations / manipulations that I like to perform on EF’s generated queries.
Stored Procs with Entity Framework
posted on April 23, 2015 by long2know in ASP.NET, Database, Entity Framework, Web
Using Stored Procedures in the latest version of Entity Framework is pretty doable. You lose a lot in terms of flexibility, but sometimes there are trade-offs that one must make. This week I was playing around with converting an application that used a Repository pattern with EF, Lambdas, and IQueryables to StoredProcs for all CRUD operations.
Here are some interesting bits that I learned.