Tagged articles - SQL Server

Object relational mappers or ORMs are a great tool which can significantly reduce application development time, but if they are not use wisely they can cause bottlenecks and performance issues. Same goes for dependency injection containers. The lifetime of the components, especially the ones that can cause recurse locks like SQL Server connections must be as short as possible and they should be released as soon as work is done. ...read more

Some time ago I wrote an article which explains how to Increase service resilience using Polly and retry pattern in ASP.NET Core. This is a great way how to easily implement retrials when using .NET Core dependency injection, but in case of using Autofac with .NET Framework 4.x you do not have many out of the box solutions. However, Polly as a library is not specifically built for .NET Core and using it with other dependecy injection packages such as Autofac is fairly easy...read more

Over the time various techniques to manipulate database data have evolved in .NET. Entity Framework is certainly the most advanced one, but for small ad hock applications where you have few inserts to deal with, ADO is still the fastest way to write data to the database...read more

Unlike Entity Framework 6, EF Core and whole .NET Core relies on dependency injection. For example, configuration is expected to be injected from the calling assembly instead of being available at any time. This is one of the reasons Migrations in Code First approach do not work out of the box for EF Core...read more

JSON is the most commonly used data fromat nowdays. It is lighter than XML, more readable and it's sindax complies with most OOP C-Like lnguages such as Java, JavaScript and C#. There is not really many downsides of using JSON, so that is one of the rasons everyone is using it in one way or another. ...read more

Sending email from SQL is not a difficult to achieve, but generating rich HTML content might be. SQL Server itself is not built as a text processor, first approach would be to build HTML by simple concatenating the string elements. This approach will work, but over time it will become more and more difficult to maintain especially if you need to add layout elements over time. ...read more

So far (based on my experience) the fastest way to insert big number of records from application to database is to use SqlBulkCopy. The downsize of this is that SqlBulkCopy uses DataTable instace as an input parameter which is not so convenient when dealing with models which are strongly typed and a lot more easy to use than iterating through the DataTable. Since SqlBulkCopy accepts DataTable for insert you can not use models directly to insert your data...read more

In case you need to handle IP addresses in SQL, they are not much useful as string values. To do anything with them rather then just storing and retrieving (checking the ranges for example) you need to split IP address to segments (octets). The following is modified method I run into online. ...read more

Probably the most common error developers make in queries is dealing with date ranges and most of the time it is because of missing the minimum and maximum time of the specific date. Month ...read more

In case your data in tables needs to have unique constraint based on more than one value in the row, the proper solution for that would be a composite key. ...read more

Nowadays, in the years of ORM, barely anyone thinks about stored procedures, but they are still there and accessing and reading data with ADO and SqlDataReader ist still the fastest way. Stored procedures are validated, parsed and compiled in SQL server, so these steps are not required to be performed versus plaint text query (which is what entity framework generates for accessing data). ...read more

Entity framework is an awesome tool for working with database in .net, but sometime you have to get back to old fashion ADO for the performances. The bad thing is that as soon as you get back to ADO, you have to deal with SqlDataReader, DataTable and DataSet classes. This is not so convenient for using in upper levels of applications, especially in MVC environment where using strongly typed objects is an essential thing. ...read more

Passing complex data to and from stored procedures has always been a tricky one. The most elegant way to pass values to a stored procedure is to use predefined data types in SQL Server...read more

When working on an app you create all your stored procedures, functions, tables, view and other objects in a development database (or t least it should be like that). A small but annoying problem occurs when you have to migrate your stored procedures to test or production database. Imagine during development you had let's say 20 stored procedures...read more

Database table changes are usually not displayed right away in an application, escecially if it is a web application. With HTML5 and Web API that is about to change. New MVC introduces async controllers which can be used to do live data update to HTTP client...read more