Quick Relational Database App Setup Guide for Dummies

This guide would be for you, if you need to frequently access custom data sets in range of million entries or so across multiple Windows computers and multiple users concurrently. I will show you how to build up a program you can:

Design almost as easily as database program can be designed.
Deploy with a centralized database that can be accessed in your Local Area Network by other computers.
Quickly modify to suit your changing needs.
Quickly deploy on the computers where you need it.

I originally installed Visual Studio Community 2015 to compile PyPy for Windows. This week I have worked on a customer project where I had to get bit more features than what Excel and VBA.net -forms provide. Turns out a Microsoft SQL database and couple Windows Forms did the job rather well.

For onlooker the visual studio IDE looks like terribly convoluted mess. I had the grit to handle it only because doing a web app into LAN would've required some special trickery that could have taken weeks to stabilize. Anyway I'm glad I figured it out because there are some great pieces here.

Prerequisites

To get through the Guide and retain your pride, you'd need:

Visual Studio Community installed on your development computer. Preferably newest version.
Some introductory knowledge about C# programming language
Be ready to get yourself introduced with non-introductory knowledge about C#
Burning desire to store some tabular data persistently on a computer, or across multiple computers.
Functioning pair of Human brains, preferably inside their original enclosure. As in capable of thinking critically about things. Hamster brains or upgraded Cyberman brains won't do.

Goal

A good guide needs some attainable goal, so lets pick one. Say you'd want to track bunch of your customers, items that they've bought and funds you got. How would you do that?

You'd have relational data such as here:

Customer Table {
    Id      INT IDENTITY,
    Name    TEXT,
    Phone   TEXT,
}

Purchase Table {
    Id              INT IDENTITY,
    CustomerId      INT FOREIGN[Customer.Id],
    Name            TEXT,
    Price           NUMBER,
    TimeOfPurchase  DATETIME,
    Delivered       BIT
}

Usually you don't just want to store the relational data. You also want to gather intel. Lets say you'd want to obtain following information:

The last time and purchase of the individual
Ordered list of the people, by their largest purchase
Ordered list of people by largest total purchase of the month
Ordered list of people by the largest total purchase all-time
List of things you've not delivered yet.

I don't have enough time to go this all through in one weekend blog post. But lets get you to the point you can consider these at first.

Step 1: Set up your project

First lets use the Visual Studio to setup a project. It does little of code and I don't usually favor frameworks, but it's small enough to not bother me.

Start Visual Studio
Open New Project -dialog and pick "Windows Forms Application" from the C# -section, name it something like StansPreviouslyUsedSocks
Try to run the newly created project by pressing F5, you should see something like below:

Step 2: Set up your database

Next you want to get yourself a local database file. It's easy! See that sidebar which it calls "Project Explorer", with the entry StansPreviouslyUsedSocks in it?

Right click that one and "Add New Item", then pick up "Service-based Database" from the list, name it SuckerBooking, click through rest of the options.
Double-click the SuckerBooking.mdf -file that appears. You should see an another sidebar to appear with a directory such as 'Tables'. Right click on that and declare a new table there.
Now you can insert your schema. Roughly insert the things that I've described above in those tables. Make sure the Ids contain IDENTITY values that increment 1-up. Make sure the CustomerId is a foreign key to Customer -table. This may take some time to figure out, but be sure it has these constraints.
Press "Update" and verify the tables were created, by refreshing the directory that contained 'Tables'. You should see your newly created "Customer" and "Purchase" -table. If you right click there, you will find "Show Data" -or such, that you can use to insert some initial data there for testing.

Step 3: Set up DataSets to access your database

Visual Studio comes with a concept called "Typed DataSet", which helps you out quite a lot at beginning.

Open up your Form1.cs, and search for a panel similar to Toolbox, named "Data Sources". You should have an option to set up new data source there.
Add your database there and make sure it goes through the "Data Source Configuration Wizard". This nifty thing copies the schema directly from your database into the C# and into your IDE, so you don't need to retype it everywhere when it changes.
Now comes the hard part: Drag some of your tables from the "Data Source" -tab over the empty form and drop it!
The result is there's bunch of puke in the form, ignore it for now and run it. It should be some functioning puke.

Now you may note that the database doesn't update when you close and reopen the app. It's because the compiler copies it into bin\\Debug\\ -directory every time you press F5. If you click on your SuckerBooking.mdf in the "Project Explorer", you find a property setting that says "Copy Always", change that to "Copy if Newer", so that the file is only copied along if you update your database schema.

Step 4: Dissect the puke on your Form1.cs

If you're looking at the Form1.cs in the designer mode, you see it usefully produced bunch of widgets for you. If you tinker with the last step a bit prior dragging and dropping in, you will find you can tell it which widgets to drop in.

But then there's bunch of other things that seem overly stupid and repetitive. Actually they end up being semi-useful if you understand how they relate:

DataSet is an actual cached bunch of rows and tables from your local database.
TableAdapter is sort of an object that functions as intermediary between the dataset and the real database. You can use it to .Fill the Dataset or .Update whole or part of the dataset back up.
BindingSource is bit like a finger into the DataSet. It selects one record for editing and then you can edit it through the BindingSource. You can also feed results of LINQ queries into the BindingSource to process your DataSets.
BindingNavigator is the thing with the arrows, red cross and the blue square, it is somehow connected to the BindingSource, and I'm not entirely sure how they connect together. Anyway they do so.
DataGridView builds up a table editor around the BindingSource you can reconfigure to fit your needs. The "Finger" of the BindingSource becomes the selected item in the gridview.

Step 5: Localization features (If you're into that "multilinguality")

You may be high into localization or then not. Anyway it's quite easy to setup. Once you want to localize your app, Set parameter "Localize" in your form to "True", change the "Language" parameter to your favorite human language. Now go rampant and change all the labels and texts in the app that you want to localize.

Step 5: Study out a bit.

In minimum you want to know what Lists and Dictionaries are. It might be also semi-mandatory to read about DataSets and LINQ.

For studying, one useful thing to figure out about your IDE is the Shift-F12 when the cursor is above the item in the source code. That finds the reference for the item in that part of the program. There's also some other useful things, such as the CTRL+H. Learn some shortcuts.

Then there's some other things you may want to look into:

If you want a convenient way to store arbitrary data into plain text files, read about JsonObject.
When you have to change more than 4 controls at once, it helps yout if you know that every control is added into this.Controls -list inside a form. The information may help out to create compact code later on.

Build on what you already know, and you should be on your feet quite quickly here with just the information I provided. And remember that I am a Microsoft-hater. I wouldn't use their things if I didn't find them useful... for now.