Thursday, December 15, 2016

How to Import Excel or CSV data into SQL Server using SSDT

I have recently rebuilt my computer and decided to give SSDT a try.  This is SQL Server Data Tools.  I would use it instead of SQL Server Management Studio (SSMS).

It has been going really well so far.  I'm able to do all the normal things I'm used to.  Until now.  I wanted to import an Excel file into a table.  I've done it a million times in SSMS, but how do I do it in SSDT?

After some googling, I came up empty.  I should note that I am a C# developer.  I'm not using database projects.

Anyway, I figured out a really easy and almost ridiculous way to import the data.

Before you do this, make sure your database is backed up.  Only do this with small data sets.

  1. Open Visual Studio with SSDT.  To see if you have SSDT, look in your Help > About dialog and look in the list of Installed products.
  2. Open the SQL Server Object Explorer view.  (It's in the View menu).
  3. Connect to your DB and drill down to the table you want to import to.
  4. Right click the table and select View Data.
  5. Now go into Excel and select the data you want to import and hit our good old friend, CTRL-C or Copy.
  6. Switch back to Visual Studio, click on the row with all of the NULL values.  This row is intended for adding a single new row.  However, with the row selected, you can hit CTRL-V and paste all of the data into the new table.  Make sure the data fields line up.
  7. This will even work with IDENTITY columns.  Just make sure to have a column in Excel filled with zeros.
  8. That's it!  You might have to wait a while!
I should note that this is not the best plan if you have many rows of data.  If you have 1000 or less, this seems like a decent approach.  It may take a little while to import the data via copy/paste though.

Friday, May 27, 2016

Comparison and Review of .NET Fuzzy Matching Nuget Packages

I am simply using Jaro-Winkler to get a similarity factor of 2 strings.  I'm using this for name and address comparisons and doing my own score aggregation and weighting.

I first tried Fuzzy String.  Unfortunately, it has several issues preventing it from working properly.  Even among these issues, I found other examples that caused the Jaro-Winkler algorithm to go into an infinite loop.  It's funny that this package has a 5 star rating, because for my use case, only using Jaro-Winkler, it failed miserably.

Then I tried BlueSimilarity.  This package also had issues loading a BlueSimilarity.Interop.dll.  At this point I was tired of troubleshooting and just wanted a solution that worked.  Besides, on nuget the project site is a broken link.  Man.

Finally I tried SimMetrics-TextFunctions.  This worked really well!  I had a few small unit tests to simply verify that the bugs in FuzzyString are not in this implementation.  Awesome!
EDIT: Wow.  I found out 7 months later that this package does indeed have a bug.  It is easy to work around, but I consider it a bug non-the-less.  This code, with a space prefix on one of the strings returns with a zero similarity.  EDIT 2: Over a year later, I have found a bug.  With strings "Canyon Rd" and "Canyon Est Dr" I am getting a similarity score of .4.  It should be much higher than that.  So...  I'm changing my implementation... again.

Now I am using some code from Stack Overflow.  I'm really surprised that all the NuGet packages have some kind of issue and that this bit of code passes where all the others fail.  Thank goodness for unit tests.  I have a ton of them.

Monday, May 9, 2016

My Entity Framework Cheat Sheet

This is a list of tools I use to get generated code and get work done quickly with Entity Framework.

Generate Classes for Entity Framework Code-First from Database

  1. Prerequisite: An existing database
  2. Do one time:  Download Entity Framework 6 Tools for Visual Studio 2012 & 2013.
  3. Right click a project
  4. Add new item...
  5. Search for ADO, select ADO.NET Entity DataModel.
  6. Name it.
  7. Select Code First from database.
  8. Select / Create Database connection.
  9. Select tables you want to include.
  10. Finish
  11. Now you have models and a DbContext to work with.

Generate SQL/Database from a DbContext

  1. For the entities, create a models directory and create classes in it.
    1. If your primary key does not follow convention name, use the [Key] attribute.
    2. Create navigation properties for many to one relationships; create a property for the ID and the object.
    3. If your nav prop ID does not follow convention, use the [ForeignKey] attribute on the object.
    4. Other attributes to use: Required, MaxLength, Index, InverseProperty, Column (if you want your db column to have a different name), [Table("TableName", Schema = "SchemaName")]
  2. VIEW > Other > Package Manager Console.
  3. Make sure the console is showing the correct project.  You also may need to set that project as the startup project in VS so it can find the correct config file with the DB connection string.
  4. If you are trying to recreate your database, make sure to drop the DB or change the connection string.  Also remove the Migrations folder.
  5. Make sure you have a DbContext file with:
    1. A good database connection string.
    2. Sub-classing DbContext
    3. public DbSet<Entity> Entities {get;set;}
  6. Type: Enable-Migrations
  7. Add-Migration MigrationName
  8. Update-Database -Script.  Save off the SQL if you want it.
  9. Update-Database.  Now your database tables are created.

Wednesday, March 30, 2016

Generate .NET MVC Views for a ViewModel

So I have a ViewModel that I created.  Now I want to generate a controller and views for it with the typical CRUD actions.  I'm not going to use all the actions, but I did want a few of them.  This is very easy to do with normal Entity Framework entities, but what about a view model?

I decided to see if I could fake out the scaffolding tool and I was able to!

Here's what you do:

  1. Make your POCO MyThingViewModel class.
  2. Make sure to have one property in the class annotated with [Key].
  3. Take some time to annotate your view model with attributes like:
    1. [StringLength(10)]
    2. [Required]
    3. [DataType(DataType.Date)]
    4. [Display(Name = @"Unit Type")]
    5. [DisplayFormat(DataFormatString = "{0:MM/dd/yyyy}", ApplyFormatInEditMode = true)]
    6. [Range(1,100)]
  4. Find your DbContext class and add a DbSet<MyThingViewModel>
  5. BUILD your solution.
  6. Now on the controllers folder, right click and "Add Scaffolded Item..."
  7. Choose MVC 5 Controller with views, using Entity Framework.
  8. Choose your DbContext, MyThingViewModel Class, and desired controller name like, MyThingsController.
  9. Click Finish
The scaffolding tool will do its work and create the views for you!  Obviously now you have code that will not run since there is no MyThingViewModel in the database.  (Don't try to run this code with automatic migrations!)

Now that you have your views:

  1. Remove the DbSet and Key attribute.
  2. Edit the controller to populate the View Model however you need to.
Enjoy the "free" views.

Alternate Option

There is another option.  You can simply use a DB Model in the Add Scaffolded Item Wizard and manually change the code to use a view model.  This is pretty easy to do for CRUD implementations where the DB Model and the view model are almost identical.

Friday, February 12, 2016

Windows 8.1 Disk Usage / IO at 99 or 100%


We ended up getting solid state drives on our team and our laptops are running super fast now!

The Long Story

So I just resumed my laptop from hibernation.  Things are running pretty slowly.  It can last for up to 15 minutes.

It is a fresh install of Windows 8.1 from about 3 months ago.  I have a DELL PRECISION M4800.  The laptop is pretty good but it does suffer from slowness.  I have finally pinpointed the slowness to the Disk.  If I open Task Manager, I can see whenever my laptop is slow, the Disk column is at 99%.  There is usually no single process hogging the disk.  Usually it is several.  I have never seen Memory maxed out, and CPU will spike occasionally, but not often.

Most of the time the slowness is during the first 10 minutes of boot up and starting any program.  I mean any normal GUI program like chrome,, IE, Outlook, etc.  It takes at least 30 seconds (usually more) to start any program.

Back to the disk.  I've also seen slowness when I am emptying the trash or doing large file operations.

On to the fix.  This is the first thing I tried, changing the virtual memory settings:

Of course you have to reboot to make this take effect and I have work to do!  So stay tuned!

This did not help at all.  I should have known.

I just now went to Control Panel > System > Advanced System Settings > Advanced tab > Performance > Settings > "Adjust for best performance".  

This helped a lot actually.  I still see my laptop maxing out the disk at 99% periodically, but the performance is noticeably faster, especially after boot up when I open several applications.  I don't think this solved the root of the problem.

I also tried to disable search indexing on my C drive.  Let's see if that helps.  (WARNING: Disabling search indexing on the full drive can take a long time.)

Turning off indexing did not seem to help that much.

I also tried this solution, but it did not seem to do much:

Ug.  I think it's fair to say that nothing so far has really helped.  See screenshot below.

I'm starting to believe that my virus scanner is the problem.  Not sure what to do next though.  I don't see any way to temporarily disable it.

My buddy Tim has the same computer and suggested disabling SpeedStep, but unfortunately there is no noticeable change for me.

I have disabled SpeedStep again and it is not helping.  Now I have done this google search:

And found that my virtual memory is quite low.  I don't have my physical memory maxed out, but it is worth a try.  I have set it to min 8000MB (same as my physical memory) and max 16000MB (double physical memory).  I need to reboot (later) and find out if this helps!

It didn't help.

I tried stopping some services using Start > Search > msconfig to disable and services.msc to see what the service is.  I disabled a lot of bluetooth services since I don't use them and superfetch.  This did not help either.

I restarted my computer in safe mode and safe mode with networking.  Both were working much better - I could start programs in only a few seconds, but I still do not know what services I need to stop.

Wow!  Now I am getting somewhere.  I have a balanced Power Settings profile in effect.  I changed from balanced to High Performance temporarily.  Then in a balanced profile, I found that setting my Hard Drive sleep minutes to zero (when plugged in) to disable Hard Drive sleep is helping.  Programs are now starting up in only a few seconds!

Ug. That was a fluke.  It's still very slow to start up.  No root cause identified yet.  It is something software related since it was starting up applications very quickly in safe mode.  Now I am trying a fully High Performance Power profile.

A friend from work recommended Start > Run > control.exe srchadmin.dll.  Then disable indexing for mapped drives.  I tried turning indexing off for everything.  We'll see if that helps.