T-SQL/SSMS: transaction rollback in scripts with XACT_ABORT ON, GO statements and syntax errors

I’ve recently met a weird issue with T-SQL scripts at work and would like to share it with you today 🙂

T-SQL script with multiple objects created

On daily basis I work a lot with MS SQL Server databases. We often create many T-SQL objects (tables, views, procedures, functions) and because of some reasons we cannot use Entity Framework or another from widely available ORMs. Nonetheless, all objects created in the database must be kept in the form of SQL scripts (files) containing set of CREATE, ALTER, INSERT, DELETE or whatever T-SQL statements. What we often do is to create a single .sql file, which in fact often contains more than one, separate (independent) SQL statements (e.g. creates a table and a procedure). What we obviously want to ensure is that when executing the script either all statements are committed to the database or none of them. This means that if in a part responsible for creating a particular object any SQL error is raised, execution of the whole script should be interrupted and the whole transaction rolled back, so in effect none of the objects contained within this script are created (none of the statements batches is committed). Here the issue comes out.

SQL script with XACT_ABORT ON and GO statements

In order to handle above-described requirements, the template for SQL script looks as follows: .gist table { margin-bottom: 0; } Firstly, we set XACT_ABORT to ON. This setting,

according to Microsoft docs

.NET Developer Days 2017

On 18-20.10.2017 I had a pleasure to attend .NET Developer Days 2017 conference in Warsaw. The first day we took part in a full-day workshop on containers with Docker and the next two days we attended the conference itself. In this post I’d like to share my thoughts and insights on the conference, its organizational aspects as well as my subjective opinions on the sessions I attended. Let me start by describing the workshops and all sessions I was present at. You can find the list of all sessions that were held during the conference

on its official website

Entity Framework Core – database initialization with Unit Test

I’ve recently been presented a concept of initializing the database (creating or re-creating it) with Unit Test method. Initially I thought it’s a non-sense, but after a while of taking a deeper look…

Code First approach

The method of initializing the database I mentioned was used with Entity Framework Core in ASP.NET Core project, where Code First database creation approach was used. As you know, this approach implies that we create models (classes) representing our database entities in the code first and then, using an ORM system (such as EF), database structures are created. This is very convenient, especially in prototyping. I’ve developed few small or average-size ASP.NET apps and I always used Code First. However, I cannot say how it works on production as these apps were university or pet projects which I’ve never deployed on real customer’s environment. What I noticed is that creating entities using this approach is fast and quite easy.

Database initialization in development phase

As long as your project is in development phase, different developers are working on it and there is some database behind, but the data itself is not very important (you only need the database structure – there’s no production data in it yet), programmers often need to have the database (re)created. To make this process quick and easy, instead of using Migrations straightaway, you can define your models, DbContext and write a Unit Test method which initializes the database. Then, each developer working on the project only needs to re-run this Unit Test to have the database created. What’s more, as soon as another programmer makes any change in any of the models, the others just need to re-run the Unit Test which re-creates the database and potentially fills it with sample data. There’s no need to keep any migration files/scripts in the development phase. The following subsections present how to do that. Examples are based on a simple ASP.NET Core MVC application called CarServiceMvc. I’ve used .NET Core 2.0 Preview 2 and Visual Studio 2017 15.3.0 Preview 3.0. The whole source code

is available on GitHub

Xamarin.Android – debugging via WiFi

In this short post, I’m going to show you a very handy feature of Android Debug Bridge (adb) – possibility to debug Xamarin.Android apps in Visual Studio via WiFi connection.

Using ADB to debug Android apps

By default, adb is configured to “map” Android devices connected via USB ports to the computer as debug devices, which are then available e.g. in Visual Studio as the device on which our app can be deployed and debugged. In may cases we debug apps on Android emulators, which is frequently fair enough, but at some point we need to make our tests on a physical device. It may not be very comfortable to have the phone connected using USB cable all the time, especially when testing some physical sensors like accelerometer or gyroscope. For such purposes, ADB gives us the possibility to connect Android devices via WiFi instead of USB. Let’s see how to configure it.

Configure ADB to work on WiFi

The first requirement is – obviously – that both our development PC and Android device must be connected to the same WiFi network. Then we need to find out what is the IP address of our Android device – it can be checked by going to Settings -> WiFi -> Menu – Advanced settings (Android 6.0):
Android 6.0 – IP address
As soon as you have IP address of the device noted, connect it to the computer via USB port. Now we need to use adb.exe to configure it for connecting with the device via WiFi. You can either add system environmental variable pointing to where the adb.exe is stored or just open cmd, go to the catalogue where it’s located (Android\platform-tools\) and execute the following commands:
  1. Change ADB to listen on TCP port:

adb tcpip 5555

Date selection using DatePickerDialog in Xamarin.Android

In this post, we’re going to see how to provide a nice Android UI control for selecting the date using DatePickerDialog. The dialog looks as follows:
It may be opened e.g. when clicking on a button in the app, as I used it in MoneyBack.

Creating DatePickerFragment

First of all, we will implement the dialog to be displayed within DialogFragment. Doing that our dialog will be able to be displayed as independent piece of UI on the top of any Activity. The second requirement is to implement IOnDateSetListener interface (coming from Java/Android), which provides a callback on date selection action done by the user. The DatePickerFragment meeting those two requirements may look as follows: .gist table { margin-bottom: 0; } There are few crucial parts in above-listed class: Line 1: as said before, the class derives from DialogFragment and implements IOnDateSetListener interface Line 3: here we have public static readonly string TAG variable defined – it is used as a unique identifier of the Fragment; in some cases, Fragments can be displayed without any UI – in such case the only possibility to identify and get the Fragment is by using

findFragmentByTag()

SQLite-Net Extensions – one-to-many relationships

In the 3rd post from SQLite-Net Extensions series we are covering the last type of relationship – one-to-many (and the opposite – many-to-one).

One-to-many, many-to-one

One-to-many relationships are used in general for parent-children or whole-elements relations. Classic examples are: bus and passengers, document and elements etc. One-to-many relationship means that the one-end entity knows about its children and many-end entity has a reference (foreign key) to its parent (but not necessarily knows about it). On the other hand, the opposite relationship to one-to-many is many-to-one. In that case, many-end entity has a reference to its parent and knows about it, but the one-end entity doesn’t necessarily know about its children (at least not directly). I used a verb to know several times – so it’s time to explain it 🙂 By “knowing” about the other end of a relationship I understand having a reference to it. It means that, for instance, in many-to-one relationship, the one-end of it doesn’t have a reference to its children. However in most cases what we’d like to have is a hybrid of one-to-many and many-to-one relationships. I will call it one-to-many with inversion. We want both parent to know about its children, and each children to know about its parent. In this post we’ll cover one-to-many with no reversion and one-to-many with reversion, as it also includes many-to-one relationship so you can have a comprehensive overview. We are going to see it on the example of Employee and Duty* entities. Each employee has a list of his duties, whilst each single duty is assigned to only one employee. * “Task” would probably be a better name, but I didn’t want to make it confused with .NET Tasks 🙂

One-to-many with no inversion

Firstly, let’s model this kind of relationship:
One-to-many with no inversion – class diagram
Now we can transform it into C# classes: .gist table { margin-bottom: 0; } In the Employee class (parent, one-end of the relationship) we define the collection of children, decorated with OneToManyAttribute. Collections types supported as for the time of writing this post by SQLite-Net Extensions are List and Array and can be used as you prefer. Let’s now see how the child entity (Duty) looks like: .gist table { margin-bottom: 0; } In the Duty class (child, many-end of the relationship) we need to have a foreign key to parent entity defined. For that purpose, we create a property representing it (), decorating it with , additionally specifying the type of parent referenced (Employee). That’s it. We can already use it in our code: .gist table { margin-bottom: 0; } No rocket-science here. What’s interesting for us it how looks in the end:
One-to-many with no inversion
As you can see, method returned object of type Employee with its collection properly retrieved (containing two duties assigned to the employee before). Moreover, each children has its foreign key () automatically retrieved from the DB – there is no overhead here, this is simply foreign key field stored in the same SQLite database table (Duties). As previously, let’s first see how the class diagram changes after adding inversion:
One-to-many with inversion – class diagram
What changed is that now each Duty has a property of type Employee. To realize the above class diagram and make each children (each Duty in our case) knowing about its parent (having a reference to responsible Employee), the only thing we need to do is to add the following property to Duty model class:

[ManyToOne] public Employee Employee { get; set; }

SQLite-Net Extensions – one-to-one relationships

In this second short post from SQLite-Net Extensions series, we’re going to see how to create one-to-one relationships using this tiny ORM.


This is the simplest type of database relationship. An example could be vehicle and registration certificate – each vehicle has one and only one registration certificate, and one registration certificate is associated with one and only one vehicle (excluding some extraordinary law rules in other countries that I don’t know 🙂 ). We can model it in two ways:
  • as a one-way relationship – in this case only one of the relationship’s ends knows about the other one
  • as a both-ways (with inversion) relationship – both relationship’s ends know about each other.

One-To-One with no inversion (one-way)

This kind of relationship looks as follows:
One-To-One – one-way – class diagram
We use it when we assume it’s enough that Vehicle knows about RegistrationCertificate, but the document doesn’t necessarily need to know which car/motor it’s associated with (at least directly). In the code, we then create two model classes using SQLite-Net Extensions: .gist table { margin-bottom: 0; } .gist table { margin-bottom: 0; } What’s interesting here is the ForeignKeyAttribute defined on RegistrationCertificateId property. It is – as its name says – the foreign key to the primary key of related entity (of type RegistrationCertificate). The related entity property itself is decorated with OneToOneAttribute. There’s nothing more we need to do to have this relationship modeled. We can already use it: .gist table { margin-bottom: 0; } Nothing special here, right? It looks very similar to what we’ve seen in the previous post about many-to-many relationships. What’s interesting for us it that in the end, when Vehicle object is retrieved from the database using GetWithChildren method, its RegistrationCertificate property is filled as well:
One-To-One – filled related entity

One-To-One with inversion (both-ways)

This kind of relationship models itself as below:
One-To-One – with inversion – class diagram
What actually changed on the diagram compared to the relationship with no inversion is that now RegistrationCertificate has a property of type Vehicle (in the code – a reference to Vehicle related object and a foreign key). In our case, this is even more “real” – it would be nice if a Vehicle knows about its RegistrationCertificate, but also when we look at the certificate we’d like to see to which car it belongs. We can now model it in the code. The only thing that changes in the model classes is addition of a reference and foreign key to Vehicle in RegistrationCertificate model, so I’m presenting only this class’s updated code: .gist table { margin-bottom: 0; } What’s cool here is that we don’t need to modify our code for saving entities with relationships into SQLite database. We can simply add the following line at the end:

var certificateStored = db.GetWithChildren<RegistrationCertificate>(certificate.Id);