0

Seeding a Code-First database from a text file

So, I’ve been working on a simple application where I can learn various technologies that are used in various tiers of an application. For the Data Access Layer I am using Entity Framework 6 with MSSQL Server 2014, and this is where the focus of this post lies.

I am using the Entity Framework Code-First approach where I’m using ordinary C# classes to define the structure of my database. The table structure produced is the following:
RelevantTables
The challenge that I needed to solve was ensuring that after creating the database, the Nationalities table was pre-populated with the names of all countries in the world, along with their 2-letter country codes. I have this data in a text file, as illustrated below:
CountryList
Now, if I had gone the Database-First approach, this would not have been an issue because I could then import the data into the relevant table and that would be the end. With code-first, every time I recreate the Nationalities table the previously-imported data is lost.
Entity Framework gives us the ability to seed our tables with data, after they are created, however, I had only used this functionality to add C# objects created in code. For example, I could add rows into the PersonType table. This can be accomplished by adding the following code in the Seed method within the Configuration.cs file:

context.PersonTypes.AddOrUpdate(
    p=>p.TypeName,
    new PersonType { Id=new Guid("F2E8C90A-212C-4E36-B752-02718830BB0C"),
        TypeName ="Administrator"},
    new PersonType { Id = new Guid("9B258E4A-3766-46C0-B6BC-76946BF615D1"),
        TypeName = "Instructor" },
    new PersonType { Id = new Guid("5443EDED-E7BB-45C8-9B63-8F5CD3E0C5CE"),
        TypeName = "Learner" });

context.SaveChanges();

This produces the following data in the PersonType table:
PersonTypeTable
So I needed a similarly terse means of populating the Nationalities table from the text file. I knew that I could use a SQL Server Integration Services (SSIS) package somehow. If I could just “call” an SSIS package from the Seed method and have it import the data into the table, that would be awesome.
I learnt that when you import data into a table, you have the option to save that operation as an SSIS package. That means you then have a package that you can execute later to carry out the same operation, exactly what i needed!
Below I show how to import data from a text file and how to subsequently save an SSIS package for that operation:
Right-click the database of interest, select Tasks, then Import Data…

TasksImportData

In the SQL Server Import and Export Wizard, specify a Flat File data source:

FlatFileDataSource

In the next screen you get to choose the source file. I checked the checkbox specifying that the first row contains column names:

SourcePath

For the Destination, I specified that I would like to save the data in SQL Server. Specify any login credentials if required. In my case Windows Authentication is fine.

Destination

In the next screen I specify the table into which the data will be imported:

DestinationTable

Then I clicked the Edit Mappings button and set the options as shown below, basically saying that the column Code in the text file would map to the field CountryCode in the table, and the column English Name in the text file would map to the column CountryName in the table:

Mappings

Then in the next screen I chose to save the package in the file system. This is very important.

SaveAndRunPackage

In the next screen I specify the location (and name) of the package by browsing to a desired location:

SavePackageAs

Then after completing the wizard, the following success screen displays:

SuccessfulExecution

Browsing to the directory specified earlier as the Save location of the package shows that the package was indeed created:

CreatedPackage

Ok, now we have our package that is able to import data from a text file into the Nationalities table. The last task is to call this package from the Seed method. Fortunately this was easier than I expected thanks to this link: https://msdn.microsoft.com/en-us/library/ms136090(v=sql.120).aspx

Here’s the code to do just that. It should be easy to understand. Just make sure you add the Microsoft.SqlServer.ManagedDTS assembly and in your code, add a using statement for the Microsoft.SqlServer.Dts.Runtime namespace:

//Import country list from text file
string packageLocation = @"E:\Projects\LearnerDriverManager\CountryListImport.dtsx";
Application app = new Application();
Package pkg = app.LoadPackage(packageLocation, null);
DTSExecResult pkgResults = pkg.Execute();

This results in the following data inserted into the Nationalities table:

NationalitiesData

1

ASP.NET MVC AntiForgeryToken with Ajax

Not too long ago when I first started using ASP.NET MVC, Ajax, and the various web technologies, I was faced with the challenge of sending data back to the server using Ajax. I wanted to implement a solution that utilized the security features built into ASP.NET MVC, specifically using the AntiForgeryToken to avoid Cross Site Request Forgeries. Safe to say, back then i left that task undone. Today i decided to revisit that problem, and after a bit of googling I found that the antiforgerytoken needs to be sent manually. The following discusses my implementation.

If you use scaffolding to generate your controllers and views, it will take care of including the ValidateAntiForgeryToken() attribute on your controller action, and the @Html.AntiForgeryToken() in your razor view, within a form tag. When the form is submitted, the AntiForgeryToken is sent along to the server.

If, however, you make an Ajax call (a post) to the server, say, on a button click, the AntiForgeryToken is not automatically sent to the server.

Sample Ajax Call:

@Html.AntiForgeryToken()

<div...
</div>
.
.
.
<script>
    function SendData() {
        var options = {
            url: '@Url.Action("Create","Car")',
            type: "post",
            data: {
                regNumber: $("#regNumber").val(),
                make: $("#make").val(),
                model: $("#model").val()
            }
        };
        $.ajax(options);
    }
</script>

Internet Explorer Developer Tools Output showing the request and response body for the same request:

Error-AjaxRequestBody


Error-AjaxResponseBody2

So, the request body did not have the antiforgerytoken (neither did the header of course). Now lets manually add the antiorgerytoken:

<script>
    function SendData() {
        var token = $("[name='__RequestVerificationToken']").val();
        var options = {
            url: '@Url.Action("Create","Car")',
            type: "post",
            data: {
                __RequestVerificationToken: token,
                regNumber: $("#regNumber").val(),
                make: $("#make").val(),
                model: $("#model").val()
            }
        };
        $.ajax(options);
    }
</script>

As the output below shows, the request is successful this time around:

Success-AjaxRequest2