Microsoft SQL Server

Les Pinter

Getting Started | It's only 4 commands... | Entity Framework | Conclusion

You can't be a programmer without dealing with data. And in the programming world, data means SQL.

SQL is the acronym for Structured Query Language. It runs as a standalone program either on a server on your network or on your own computer. You usually start working with a "developer edition" on your own computer, or perhaps using a server dedicated to developers on your network, and then point your programs to the production server when your tested app is installed.

Getting Started

You'll need to install the Developer Edition of Microsoft SQL Server, unless you already have access to it or to another version of SQL. You can download and install it from (Select the Developer edition.)

During installation, if you're asked whether you want to specify userids and passwords, just say no. There are two ways to connect to SQL Server: Using Windows Authentication (which means that you're logged into Windows, which you always are) or using UserIDs and passwords. At some point, a system administrator charged with keeping people out will give your users credentials to get into SQL. But at this point, you just want in, and Windows authentication means that you're in. Using Windows authentication, the name of the edition of SQL Server that's running on your own computer will be called localhost, or (local), or simply a period ("."). That's something you're less likely to forget. I've actually had to uninstall and reinstall SQL simply because I couldn't remember my password. Screw that.

So install SQL Server Developer Edition. Once it's installed, run SQL Server Management Studio. You should add it to your Start menu, because you'll be running it several times a day. When it asks you for the server name, enter a period.

Fig. 2 - Use a period as the database name, and specify Windows Authentication

The Object Explorer window allows you to navigate through your databases, among other things:

Fig. 3 - SQL Server Management Studio

Right-click on Databases and select Add New Database:

Fig. 4 - Add a new database

Add a new database named Test in a new folder called C:\SQL_DATA. Then, expand the Test database tree and right-click on Tables. Add a new table:

Fig. 5 - Add a new table

Add an int called ID, and down below, double-click on Identity to make it auto-incrementing, starting with 1 and adding 1 every time a new row is added:

Fig. 6 - Add an ID column

Finally, right-click on the ID column and make it the Primary Key for the table:

Fig. 7 - Make the ID column the Primary Key

You can now add FirstName, LastName, Address, City, State, ZIP and Phone as VarChar(MAX) columns. VarChar columns don't have trailing blanks, so they match the way you would expect. Save the table as Clients.

After right-clicking on Tables and selecting Refresh to display the newly-added table, right-click on the Clients table and select Edit Top 200 Rows:

Fig. 8 - Edit the table to add some rows:

Type in a couple of rows of test data. You're now ready to work with the Big Four.

It's Only Four Commands...

There are dozens of vendors with different dialects, but essentially they're all used to store data in and and retrieve data from tables. Tables consist of rows, like little spreadsheets. Every row has exactly the same columns. Each column can either contain some data, or a blank string, or a NULL, which means that nothing has ever been stored in that cell. (Not quite: You can store NULL into a cell.)

Essentially, all dialects of SQL use just four commands:


There are books a thousand pages thick on these four commands, so obviously it can be a very complex skill to master. We'll only touch on the basics.


INSERT adds a row to a SQL table:
INSERT INTO CLIENTS VALUES ( 'Les', 'Pinter', '35056 Hwy 190', 'Springville', 'CA', '93265', '+1 (650) 464-6924' )
will add this row to my Clients table. To add just a few fields, name them first:
INSERT INTO CLIENTS (FirstName, LastName) VALUES ( 'Les', 'Pinter' )
(Note that fields not specified would have the value NULL).


DELETE removes one or more rows matching a criterion, such as



UPDATE will make changes to one or more rows matching a criterion:



SELECT retrieves matching rows. 99% of your SQL commands will be SELECT statements.


Related tables

All of the rows in a table have to have exactly the same structure. However, sometimes a part of the information that an object represents is variable - e.g. one set of parents, then one row for each child. To model this kind of relationship, two tables are used. That's why SQL Server is called a relational database.

For example, invoices have (usually) more than one line item, so if you don't know what you're doing, you do this:

InvoiceID        int
CustomerName     VarChar(MAX)
Date             SmallDateTime
Item1Description VarChar(MAX)
Item1UnitPrice   Money
Item1Quantity    Int
Item1Extended    Money
Item2Description VarChar(MAX)
Item2UnitPrice   Money
Item2Quantity    int
Item2Extended    Money

I've actually seen this. That's called non-normalized form. This makes programming a nightmare.

If you want to model an invoice, you'll need at least two tables:

  ID           int PRIMARY KEY IDENTITY(1,1),
  CustomerName VarChar(MAX),
  Date         SmallDateTime )

  ID           int PRIMARY KEY IDENTITY(1,1),
  InvoiceID    int,
  Description  VARCHAR(MAX),
  Quantity     int,
  UnitPrice    Money )
  [Extended]   AS ([Quantity]*[UnitPrice]) PERSISTED)

Then the items in Invoice #3 could be retrieved using this:


The specification WHERE D.InvoiceID = I.ID defines the relation between the two tables. That's why it's called a relational database.

You can also show all of the customers who ordered towels using a subselect:

 WHERE ID IN (SELECT InvoiceID FROM DETAILS WHERE Description = 'Towels')

Using SQL in programs

SQL Server Management Studio is something that's used by programmers and database administrators, but it's seldom visited by users. They just want to get at their data. Usually they don't even know that SQL exists. So your programs have to do the behind-the-scenes work. To do this, they first thave to connect to SQL.

Connecting to SQL

In order to tell your application which SQL server to "point to", you open a connection using a connection string that tells your program where the server is located. The connection string is stored in your app.config or web.config file in your application's folder.

This is what a connection string looks like:

Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Test;Data Source=.

There's a little utility program that will build the connection string for you. Create an empty text file called "x.udl" on your desktop, and then double-click on it. A "Microsoft Universal Data Link" dialog window will appear that looks like this:

Fig. 9 - the Uniform Data Link dialog

Open x.udl with NotePad or another text editor, and you'll see this:


The part of the string after "" is the connection string that you'll use in your code to access your SQL database. For example, here's how you would populate a DataTable with rows from a SQL table:

using System;
using System.Data;
using System.Data.SqlClient;

public partial class Default : System.Web.UI.Page
  protected void Page_Load(object sender, EventArgs e) {}

  protected void loadButton_Click(object sender, EventArgs e)
  { string cs = "Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Test;Data Source=.";
    SqlConnection cn = new SqlConnection(cs);
    string cmd = "SELECT * FROM INVOICE ORDER BY ID";
    SqlDataAdapter da = new SqlDataAdapter(cmd, cn);
    DataTable dt = new DataTable("totals");
    grid.DataSource = dt.DefaultView;

At least that's how we did it in the past. These days, we use Entity framework.

Entity Framework

Now that you know what SQL is doing "under the hood", I have to confess that you'll probably never again look at those four commands. The reason is that typing strings of SQL commands makes it likely, no certain, that you'll make mistakes that won't be caught until you run the program. That's dumb, and it's not necessary. With the proper object defined, IntelliSense can walk you through the process of building the SQL commands. For that, an entity class can be constructed that represent your table structures, so that as you type, available column names are supplied automatically. That makes it a lot harder to screw up. The good news is, the IDE will write the code for you.

Create a Visual Studio empty web project called DataTest. Right-click on the Project name, select Add, New Item, type ADO at the upper right, and pick the ADO.Net C# Entity Data Model. (Don't pick the Visual Basic version.)

Fig. 10 - Create the Entity Model

In the ensuing wizard screens (about 5 of them), accept all of the defaults. Once you get to the screen that asks you to select objects to include in the generated code, select the Client table:

Fig. 11 - Select Client Table

After a short delay, you'll find a bunch of code in the App_Code folder. You don't even need to look at it. It contains an entity class that can be used to instantiate an entity manager, like this:

TestEntities mgr = new TestEntities();

Thereafter, you can retrieve a table from your Test database like this:

var query = mgr.Clients;
grid.DataSource = query.ToList();

Related tables in Entity Framework

In T-SQL, when you SELECT data from two related tables, you get back a single table containing columns from both of the the two related tables:

SELECT I.ID, I.Date, L.Description, L.Quantity, L.Extended
 WHERE I.ID = L.InvoiceID
   AND I.ID = 3022

    ID    Date    Description            Quantity Extended
  3022 05/14/2018 Flower pot                    1    12.98
  3022 05/14/0218 California Poppy Seeds       12    23.88

In Entity Framework, a Navigation Property consisting matching rows in the related table is appended to each entity:

Fig. 12 - Related rows become a Navigation Property
which is why the data that's returned consists of entities rather than a table.

So each invoice has a navigation property called invoiceLines containing all related invoice lines.

Using SQL in a web page

Let's create a web page that uses this data. Right-click on the project name, select Add, New Item, and select Web Form. Again, make sure you select the C# version of the form. The default name of "Default.aspx" is fine. Your IDE should now look like this:

Fig. 12 - The empty web page

The ".aspx" extension tells the web server that the page will contain a mixture of (1) HTML and (2) C# code that writes HTML. What reaches your browser is pure HTML and JavaScript - the only things that browsers can understand. Here's what the HTML for the page looks like:

<%@ Page Language="C#" AutoEventWireup="true"
     CodeFile="Default.aspx.cs" Inherits="_Default" %>
<!DOCTYPE html>
<html xmlns="">
<head runat="server">
    <form id="form1" runat="server">

The only new element you see here is the first line, a directive that tells the IDE to expect C# code in the code-behind file, which is the same name as the HTML file but with a ".cs" (C-sharp) extension:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

public partial class _Default : System.Web.UI.Page
  protected void Page_Load(object sender, EventArgs e) { }

We'll display the data from the CLIENTS table in a <table>, which has been part of HTML since forever. Browsers use the data in a <table> to format the rows and columns. To create a table, the IDE uses a template called a GridView:

<%@ Page Language="C#" AutoEventWireup="true"
     CodeFile="Default.aspx.cs" Inherits="_Default" %>
<!DOCTYPE html>
<html xmlns="">
<head runat="server">
    <form id="form1" runat="server">
           <asp:GridView ID="grid" runat="server" />

When you saved the .edmx file graph, Entity-Framework created a class containing an entity that models your data. Since the Database name was Test, the default name will be TestEntity. When you want to do something with your data, you create a data manager based on the TestEntity class, like this:

TestEntities mgr = new TestEntities();

Thereafter, mgr.Client will return all of the rows in the Client table. All that's left is to assign them to a GridView and DataBind() it:

using System;
using System.Collections.ObjectModel;

public partial class _Default : System.Web.UI.Page
{ TestEntities mgr = new TestEntities();
  public ObservableCollection clients { get; set; }

  protected void Page_Load(object sender, EventArgs e)
  { clients = new ObservableCollection();
    var query = mgr.Clients;
    foreach(Client C in query) { clients.Add(C); }
    grid.DataSource = clients;

ObservableCollection that will hold the data. (The funny name alludes to the fact that the object announces to the surrounding environment when it's contents have changed. Remarkably, objects don't do that automatically.)

The generated code created when we added the ADO object defined a data manager object that lets you take advantage of IntelliSense to help build the SQL SELECT statement that will retrieve the data. It can be both declared and instantiated (created) in the class declaration before the first executable code to run, the Page_Load event handler.

In the Page_Load event, we create a clients object based on the prior declaration, and then create a query object based on the CLIENT class found in the generated TestEntity code. We then "populate" the collection with data returned from SQL Server. Finally, we assign the selected data to the GridView's DataSource and call DataBind(). Fig. 10 shows you the result:

Fig. 10 - The generated page

The generated HTML that formats the table is exactly what you'd have to write manually:

<table cellspacing="0" rules="all" border="1" id="GridView1" style="border-collapse:collapse;">
  <th scope="col">ID</th>
  <th scope="col">FirstName</th>
  <th scope="col">LastName</th>
  <th scope="col">Address</th>
  <th scope="col">City</th>
  <th scope="col">State</th>
  <th scope="col">ZIP</th>
  <th scope="col">Phone</th>
  <td>2032 Marshall</td>
  <td>(415) 223-2031</td>
  <td>35056 Hwy 190</td>
  <td>(650) 464-6924</td>

You can select other templates in the IDE to format the table with pre-defined styles. You can add a page title with a dropshadow. You can use cookies that may be present on the user's computer to add or change elements in the page. You can add a dropdown combo to let the user display only people with last names starting with a selected letter, using the SelectedIndexChanged event to filter the data on the server side. You can also write JavaScript to do the filtering on the full set of data which has been included in the generated page. Deciding what to do is the hard part; doing it is relatively simple.


SQL Server is only one of many data repositories that you can use to store data, but it's widely used, and you need to know at least one SQL product if you want to do this for a living. Microsoft SQL Server is a good choice.