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:

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:

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:

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.

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?

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.

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:

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:

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.

Similar posts