February 24th, 2015

The initial prototype of StockJock was a simple python web application (using Flask) with PostgreSQL backend. It was fun writing python for a while, however when development work really started to pick up it was decided to move to ASP.NET MVC and use C#. (I won’t get into why right now. If you really want details hit me up on Twitter.) We also wanted to keep the database in Postgres (again for details there is Twitter).

We also wanted to take advantage of the Universal Providers provided when using ASP.NET MVC out of the box. The problem was out of the box Simple Membership expected to connection to SqlServer with the “DefaultConnection” connection string. The data is all managed through EntityFramework. Luckily

ASP.NET SimpleMembership schema with PostgreSQL syntax

First step was to create a database in which to keep the membership data.

    CREATE DATABASE my_mvc_web_app;

Once we have a DB connect to the DB with a user who has permissions to create tables to create the schema. There are few particularities to pay attention to between Postgres & SQLServer.

  • The default schema in Postgres is “public”, in SQLServer it’s “dbo”. There are ways to tell Entity framework which schema to use or the way we chose was to create a “dbo” schema inside our database.
  • Postgres will treat any unquoted identifier as lowercase, where SQLServer is case agnostic with identifiers. The easiest way to keep EntityFramework from failing to find table was to quote our table identifiers to preserve case.

Here is the SQL we used to create our DB.

    CREATE SCHEMA IF NOT EXISTS dbo;

    CREATE TABLE IF NOT EXISTS dbo."AspNetUsers" (
        "Id"                   VARCHAR   NOT NULL,
        "Email"                VARCHAR   NULL,
        "EmailConfirmed"       BOOLEAN   NOT NULL,
        "PasswordHash"         VARCHAR   NULL,
        "SecurityStamp"        VARCHAR   NULL,
        "PhoneNumber"          VARCHAR   NULL,
        "PhoneNumberConfirmed" BOOLEAN   NOT NULL,
        "TwoFactorEnabled"     BOOLEAN   NOT NULL,
        "LockoutEndDateUtc"    TIMESTAMP NULL,
        "LockoutEnabled"       BOOLEAN   NOT NULL,
        "AccessFailedCount"    INT       NOT NULL,
        "UserName"             VARCHAR   NOT NULL,
        CONSTRAINT PK_AspNetUsers PRIMARY KEY ("Id"),
        CONSTRAINT UQ_AspNetUsers UNIQUE ("UserName")
    );

    CREATE TABLE IF NOT EXISTS dbo."AspNetUserLogins" (
        "LoginProvider" VARCHAR NOT NULL,
        "ProviderKey"   VARCHAR NOT NULL,
        "UserId"        VARCHAR NOT NULL,
        CONSTRAINT PK_AspNetUserLogins PRIMARY KEY ("LoginProvider", "ProviderKey", "UserId"),
        FOREIGN KEY ("UserId") REFERENCES dbo."AspNetUsers" ("Id") ON DELETE CASCADE
    );

    DO $$
        BEGIN
            CREATE INDEX IX_AspNetUserLogins_UserId ON dbo."AspNetUserLogins" ("UserId");
        EXCEPTION
            WHEN others THEN RAISE NOTICE 'Could not add IX_AspNetUserLogins_UserId. Does it already exist?';
        END
    $$;


    CREATE TABLE IF NOT EXISTS dbo."AspNetRoles" (
        "Id"   VARCHAR NOT NULL,
        "Name" VARCHAR NOT NULL,
        CONSTRAINT PK_AspNetRoles  PRIMARY KEY ("Id"),
        CONSTRAINT UQ_RoleName UNIQUE ("Name")
    );

    CREATE TABLE IF NOT EXISTS dbo."AspNetUserClaims" (
        "Id"         SERIAL,
        "UserId"     VARCHAR NOT NULL,
        "ClaimType"  VARCHAR NULL,
        "ClaimValue" VARCHAR NULL,
        CONSTRAINT PK_AspNetUserClaims PRIMARY KEY ("Id"),
        FOREIGN KEY ("UserId") REFERENCES dbo."AspNetUsers"("Id") ON DELETE CASCADE
    );

    DO $$
        BEGIN
            CREATE INDEX IX_AspNetUserClaims_UserId ON dbo."AspNetUserClaims" ("UserId");
        EXCEPTION
            WHEN others THEN RAISE NOTICE 'Could not add IX_AspNetUserClaims_UserId. Does it already exist?';
        END
    $$;

    CREATE TABLE IF NOT EXISTS dbo."AspNetUserRoles" (
        "UserId" VARCHAR NOT NULL,
        "RoleId" VARCHAR NOT NULL,
        CONSTRAINT PK_AspNetUserRoles PRIMARY KEY ("UserId", "RoleId"),
        FOREIGN KEY ("RoleId") REFERENCES dbo."AspNetRoles"("Id") ON DELETE CASCADE,
        FOREIGN KEY ("UserId") REFERENCES dbo."AspNetUsers"("Id") ON DELETE CASCADE
    );

    DO $$
        BEGIN
            CREATE INDEX IX_AspNetUserRoles_UserId ON dbo."AspNetUserRoles"("UserId");
        EXCEPTION
            WHEN others THEN RAISE NOTICE 'Could not add IX_AspNetUserRoles_UserId. Does it already exist?';
        END
    $$;


    DO $$
        BEGIN
            CREATE INDEX IX_AspNetUserRoles_RoleId ON dbo."AspNetUserRoles"("RoleId");
        EXCEPTION
            WHEN others THEN RAISE NOTICE 'Could not add IX_AspNetUserRoles_RoleId. Does it already exist?';
        END
    $$;

Hooking up EntityFramework with PostgreSQL

Now that we have a database we need our web application to communicate with it. The code the Microsoft provides for SimpleMembership uses EntityFramework. Francisco Figueiredo Jr., the creator of Npgsql project, has a great writeup on how to use npgsql with EntityFramework. I’d recommend following his blog post. The post summed up in bullet points:

  • Install the Npgsql NuGet package
  • Edit your web.config to hook in the Npgsql as DbProvider
    <System.data>
        <DbProviderFactories>
            <add name="Npgsql Data Provider"
                   invariant="Npgsql"
                   description="Data Provider for PostgreSQL"
                   type="Npgsql.NpgsqlFactory, Npgsql" />
        </DbProviderFactories>
    </system.data>
  • Edit your web.config to set DefaultConnection to your DB
    <connectionStrings>
        <add name="DefaultConnection"
            providerName="Npgsql"
             connectionString="server=127.0.0.1;userid=yoursqluser;password=sqlpass;database=my_mvc_web_app"/>
    </connectionStrings>

Using SimpleMembership

Now you are all set to start hooking in authentication into your web application. Following articles like this should be able to followed without modifications since the interaction with the database is all done through the EntityFramework just setup.

January 12th, 2015

This is a quick start for getting SQL Server unit tests going inside Visual Studio. This assumes that you already have a database and that you already have a development process and an installation process for your database. If you are starting completely from scratch then you may want to just follow Microsoft’s Walkthrough for Creating and Running SQL Server Unit tests.

Steps to create

  1. Open your solution add a new ‘Unit Test Project’
    NewProject
  2. Add a new Sql Server Unit Test file by right clicking the new unit test project and selecting ‘Add New Item…’
    AddSqlServerUnitTest
  3. You will be prompted to setup your SQL Server connection. Setup a connection to a new DB.
  4. Double click on SqlServerUnitTest1.cs to bring up database test designer.
    Database Test Designer
  5. The database test designer take a little bit getting used to. The top navigation bar has 3 areas. The first area is a drop down to select a test, the second area (circled in red in the image above) lets you pick pre-test, test, or post-test, and the 3rd area is for test management (add/remove/rename).
  6. In the database test designer on the top bar click the green plus sign to create a new test.
  7. Use the 2nd drop down in the top bar (circled in red) to select a pre-test. In the main window pane write whatever SQL is needed to setup your test. Note of caution: The pre-tests and post-tests also allow for test conditions. If you accidently add a condition to a pre-test your test may fail.
  8. Again using the 2nd drop down in the top bar (circled in red) select the test. Here you will write SQL that you want to test. For example this would be where you would call a stored procedure that you wish to test.
  9. The test will need test conditions. The bottom of the designer are test conditions. There are a limited set of built in test conditions. To create your own follow the Microsoft article on Custom Test Conditions. Just select a condition and click the green plus to add it. To edit what it tests use the properties window.

At this point you will have a SQL server test that you can run. There should have been a file, SqlDatabaseSetup.cs, that was automatically created that you can edit to automatically install your DB. You can also put any setup that you need to do, i.e. dropping the DB and creating test data inside this file.

June 21st, 2014

C# is a wonderful language, with a rich eco system – unapologetically build your startup technology in .NET

.NET Logo

One thing I’ve noticed is very rarely do startups build on the .NET stack. I’ve been lucky to be a part of many startups that have used the Microsoft stack to quickly and cheaply (yes, cheaply <pause for comedic effect> on the Microsoft stack) get stuff done. This is a shame because people are missing out on a powerful option for their startup.

It’s understandable why so many startups are build on the OSS stack. The tools are plentiful and always free to download. The languages are fun. I’ve dabbled with Python. I’ve build Ruby on Rail prototypes. They’re fine tools. This isn’t an attempt to peel people away from the OSS stack, far from it. I want to expose what Microsoft and the community around .NET has to offer for a startup.

.NET is cheap

Yes, it’s cheap to get started with C# in your startup. I feel Microsoft has a reputation (admittedly a deserved reputation) for being expensive and this is the number one reason as to why startups don’t give Microsoft a chance. In reality Microsoft doesn’t want your money until you can afford to give it to them.

By Steve Evans from Citizen of the World (Piggy Bank Uploaded by russavia) CC-BY-2.0 via Wikimedia Commons

  • Visual Studio Express & SQL Server Express

    The first stage of your startup project will be some form of MVP. Before you even commit to doing a startup you can get started creating a prototype/MVP with Visual Studio Express. VS Express is simply a free version of Visual Studio. And Visual Studio is hands down the best IDE/debugger I’ve ever used. If there is one positive thing I’ve heard my OSS friends say about C# is that they wish they could use Visual Studio.

    There are a few features missing from VS Express. Most of the missing features won’t become a pain until you grow out your team, and grow out the code base. If upgrading to a newer version of VS makes sense for the current state of your startup the feel free to upgrade, the code you create with Express is compatible.

    If you require a database Microsoft also has a free version of SQL Server. There is also nothing preventing you from using MySQL, PostgreSQL, or really any RDMS with your C# project.

  • Azure

    Azure is Microsoft’s cloud offering and it’s really good. Getting started is easy, and it makes the licensing costs reasonable. Personally I even do my development inside an Azure VM. With any sort of MSDN subscription there is $150 monthly credit for using Azure. Again there is also nothing stopping you from using another cloud provider, like Amazon, with your .NET projects. Things are just a little bit easier if you go with Azure.

  • Bizspark

    Simply put, Bizspark is amazing. Free access to Microsoft tools for 3 years for startups. Bizspark isn’t as well known as it should be. Microsoft will also help promote and support your startup when you are in the Bizspark program. This Bizspark program also gives you an MSDN license so you can use the aforementioned Azure credit.

.NET is Popular (outside the startup world)

C# in particular is a very popular language in the professional world. Popularity matters because it’ll be easier to find developers fluent in the language, and rarely will you be blazing a completely new trail.

By Fire_breathing_2_Luc_Viatour.jpg: Luc Viatour derivative work: Amada44  talk to me (Fire_breathing_2_Luc_Viatour.jpg) CC-BY-SA-2.5-2.0-1.0, CC-BY-SA-3.0 or GFDL, via Wikimedia Commons

The more popular the language that the code is written in, the easier it is to find people to work in that language. You’ll be able to find the person with the skill set in the right level for what you need. Have some entry level work? Since it’s a popular language there are people itching to get experience in C#. Have some hard core problems? Since it’s a popular language there are people with 10+ years experience in C#.

A popular language/toolset has a plethora of people who are dealing with the same issues as you are, and people who solved them. The library support for .NET is amazing and getting better every day. Sure writing an OFX parser in Rust is fun, but wouldn’t it be better for your business to just download a library and get more important stuff done?

Scalability

Yes, you should not pick a language/toolset solely on the promise of it easily scaling to bazillions of users. However all other things being equal wouldn’t you prefer a solution that doesn’t require architecture acrobatics after your first big burst of success? I’m being slightly facetious. The fact is Microsoft’s done a lot of the hard tuning work and put it right into the CLR.

Turning Torso 3 by Väsk - Own work. Licensed under CC BY-SA 3.0 via Wikimedia Commons

Turning Torso 3 by VäskOwn work. Licensed under CC BY-SA 3.0 via Wikimedia Commons

It’s been my experience that out of the box .NET code has the ability to scale far enough for most startups that performance becomes a non-issue, particularly at the early stages. This is just another thing to not think about as you are growing your company. Currently at eMoney advisor there are 2 middle of the road servers that can process millions of transactions a day. All the code for the processing is written in C#.

The .NET languages, and in particular C# also have design decisions built into the language that enable the CLR to do some pretty fantastic optimizations.

Open/Interoperable

Another argument I’ve heard against the .NET stack is people fear vendor lock-in. I’ll concede that this was an issue a decade or 2 ago. Today Microsoft plays much more nicely with other tools/platforms. Microsoft is even moving in the direction to be more open.

She left the door open by Hartwig HKD. Licensed under CC BY-ND 2.0

She left the door open by Hartwig HKD. Licensed under CC BY-ND 2.0 via Flickr

Go look at Github and CodePlex to see how many open source C# project there are. The projects are not only open, there are a lot of good open source projects.

Microsoft also has a whole "open center". The center is dedicated to showcasing Microsoft’s community efforts. If that’s still not open enough for you, there is also Mono that let’s you run .NET applications on Linux and Mac machines.

Microsoft’s made great strides in providing a robust, fun, popular community around it’s technology. More people in the startup world should embrace it.

Agree? Disagree? Drop me an email blog [at] sirchristian [dot] net or message me on twitter @sirchristian