Microsoft SQL Server

Les Pinter



Getting Started | It's only 4 commands... | Using SQL in programs | Entity Framework | Limiting columns returned | Create, Update, Delete | 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 https://www.microsoft.com/en-ca/sql-server/sql-server-downloads. (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. 1 - 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. 2 - SQL Server Management Studio

Right-click on Databases and select Add New Database:

Fig. 3 - 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. 4 - 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. 5 - Add an ID column

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

Fig. 6 - 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. If you define FirstName as CHAR(20), you can only find it by looking for "Charlie             " with 13 blanks at the end of the name.) Save the table as Clients.

Note that you could open a query window in the database
and type in the following: CREATE TABLE CLIENTS ( ID Int NOT NULL IDENTITY(1,1) PRIMARY KEY, FirstName VarChar(20) NOT NULL DEFAULT '', LastName VarChar(20) NOT NULL DEFAULT '', Address VarChar(40) NOT NULL DEFAULT '', City VarChar(20) NOT NULL DEFAULT '', State Char(2) NOT NULL DEFAULT '', ZIP VarChar(10) NOT NULL DEFAULT '', Phone VarChar(16) NOT NULL DEFAULT '')

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. 7 - 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:

  1. INSERT
  2. DELETE
  3. UPDATE
  4. SELECT

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

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

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

DELETE CLIENTS WHERE ID = 1
or
DELETE CLIENTS WHERE FirstName = 'Les'

UPDATE

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

UPDATE CLIENTS SET LastName = 'Farkis' WHERE ID = 2

SELECT

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

SELECT CLIENTS WHERE State = 'CA'

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:

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

CREATE TABLE DETAILS (
  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:

SELECT D.* FROM INVOICE I, DETAILS D WHERE D.InvoiceID = I.ID and I.ID = 3

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

This is called a join. Actually, there are several kinds of joins: inner joins only return rows that have matching rows in both tables. If two tables contain the following rows:

Invoices

and

Invoice Lines

then the inner join

SELECT * FROM INVOICE, INVOICELINES WHERE INVOICE.ID = INVOICELINES.INVOICEID

would produce a single table:

Inner JOIN

A full outer join returns all rows from both tables, even if one or more rows in one table has no matching row in the other table, based on the WHERE clause. Columns from the absent row in each appear as <NULL> values:

Full OUTER JOIN

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

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

In the past, records from two or more tables were turned into single result tables, sometimes with huge numbers of columns. You can specify the columns that you want to return, like this:

SELECT INVOICE.ID, CustomerName, Date, Description, Quantity, Extended
  FROM INVOICE I, INVOICELINES IL WHERE I.ID = IL.INVOICEID  -- (alternative inner join syntax)
 ORDER BY I.ID

but if you only need the CustomerName once per Invoice, why return it once for each DetailLine? In this trivial example it doesn't add that much to the internet payload, but in very complex joins, half of the data you're sending over the wire isn't really needed. That slows things down considerably.

That's why Entity Framework was invented. Data from joined tables is returned in tree-like structures, with as many child record nodes as are needed, but only one parent node (in this case) per invoice. It also provides a cleaner syntax for referring to the child rows (they're called navigation properties), as we'll see below.

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.

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. 8 - the Uniform Data Link dialog

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

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

The part of the string after "Provider=SQLOLEDB.1;" 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");
    da.Fill(dt);
    grid.DataSource = dt.DefaultView;
    grid.DataBind();

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. 9 - 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. 10 - 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();
grid.DataBind();

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
  FROM INVOICES I, INVOICELINES L
 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 of the corresponding rows in the related table is appended to each entity:

Fig. 11 - Related rows become a Navigation Property

which is why the data that's returned consists of entities rather than a table. But it's so intuitive: If I is the current invoice record, I.InvoiceLines is a collection of the associated detail lines.

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

Returning data

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="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
        <div>
        </div>
    </form>
</body>
</html>

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="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
        <div>
      
           <asp:GridView ID="grid" runat="server" />
         
        </div>
    </form>
</body>
</html>

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<Client> clients { get; set; }

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

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. 13 shows you the result:


Fig. 13 - 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;">
<tr>
  <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>
</tr>
<tr>
  <td>2</td>
  <td>Christine</td>
  <td>Arce</td>
  <td>2032 Marshall</td>
  <td>Berkeley</td>
  <td>CA</td>
  <td>92002</td>
  <td>(415) 223-2031</td>
</tr>
<tr>
  <td>1</td>
  <td>Les</td>
  <td>Pinter</td>
  <td>35056 Hwy 190</td>
  <td>Springville</td>
  <td>CA</td>
  <td>93265</td>
  <td>(650) 464-6924</td>
</tr>
</table>

Limiting columns returned

As we saw above, returning only some columns is built into SELECT statements. But in Entity Framework, it's not quite that straightforward. There are three ways to return only some of the columns, regardless of the number of tables involved:

  1. Add a class that contains the columns you want to use;
  2. Create a dynamic query on the fly; or
  3. Add a view in SQL Management Studio and let Entity-Framework write the query and class for you.

1. Define a class in your code

Let's say you want to extract only First Name, LastName and Phone from your contacts table, and you want to use shorter column names for FirstName and LastName. Add this at the bottom of your code-behind:

Public class MiniContact
    {
      public string First { get; set; }
      public string Last  { get; set; }
      public string Phone { get; set; }
    }

In your code-behind's Load event, write this:

localData = new ObservableCollection<MiniContact>();
var query = mgr.Contacts
.Select(x => new MiniContact
{ First = x.FirstName,
  Last = x.LastName,
  Phone = x.Phone
})
.OrderBy(x => x.Last).ThenBy(x => x.First);

foreach (MiniContact M in query) { localData.Add(M); }
GridView1.DataSource = localData; GridView1.DataBind();

2. Use a dynamic collection

You can use a dynamic collection, which won't be checked for type safety until runtime. I do it all the time, and I haven't been struck by lightning yet. Here's how you do it:

dynamicData = new ObservableCollection<dynamic>();
var query = mgr.Contacts
.Select(x => new { First = x.firstname, Last = x.lastname, x.Phone })
.OrderBy(x => x.Last).ThenBy(x => x.First);

foreach (dynamic D in query) { dynamicData.Add(D); }
GridView.DataSource = dynamicData; GridView1.DataBind();

3. Create a SQL View

This might be my favorite. Go into SQL Management Console and type this into a New Query window (I know it's not a query; SQL calls everything a query):

SELECT TOP (100) PERCENT FirstName AS First, LastName AS Last, Phone FROM Contacts

Save your new view as "ContactView". Back in Visual Studio, open the .edmx diagram, right-click anywhere, and select Update Model From Database, open the Views panel and add your new view. File, Save will re-generate your model. Now, this code will get you your contacts:

viewData = new ObservableCollection<ContactView>();
var query = mgr.ContactView
.OrderBy(x => x.Last).ThenBy(x => x.First);

foreach (ContactView M in query) { viewData.Add(M); }
GridView1.DataSource = viewData; GridView1.DataBind();

Since you'll almost always need fewer than the full list of columns in a table, you'll be using one of these techniques regularly. Once you've done a few, it's a piece of cake.

Create, Update and Delete (CRUD) using Entity Framework

As was discussed briefly above, INSERT adds a record, DELETE removes a record, and UPDATE changes column values in an existing record. But how do we do that in Entity Framework?

To demonstrate these three operations, I've created a tiny web page that performs these three operations on the INVOICE table used in the preceding examples. The UI looks like this:


Fig. 14 - CRUD (Create, Update and Delete) in Entity-Framework

and here's what it looks like while adding or editing a row:


Fig. 15 - Adding or editing a row

This is the HTML for the page:

Listing 1: The HTML for the CRUD screen shown above:

  <%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
  <meta name="viewport" content="width=device-width, initial-scale=1" />
  <title>CRUD Ops in E-F</title>
<style>
  * { font-family: 'Courier New'; font-size: 12pt;}
  td  { vertical-align: middle; }
</style>
</head>

<body>
<form id="form" runat="server">
  <asp:GridView ID="GridView1" runat="server" OnSelectedIndexChanged="GridView1_SelectedIndexChanged" AutoGenerateSelectButton="True" AutoGenerateColumns="False">
    <Columns>
      <asp:BoundField DataField="ID" HeaderText="ID" />
      <asp:BoundField DataField="CustomerName" HeaderText="Customer Name" />
      <asp:BoundField DataField="Date" DataFormatString="{0:d}" HeaderText="Date" />
    </Columns>
  </asp:GridView><hr />
  <table>
    <tr><td><asp:Button ID="btnAdd" runat="server" Text="Add" OnClick="btnAdd_Click" /></td>
        <td><asp:Button ID="btnEdit" runat="server" Text="Edit" OnClick="btnEdit_Click" /></td>
        <td><asp:Label ID="lblState" runat="server" Text="" /></td></tr>
  </table>
  <asp:Label runat="server" ID="lblSelectedID" BackColor="Green" ForeColor="White" /><br />

  <div id="addingTable" runat="server">
    <table style="border: solid 1px black">
      <tr><td>Name: </td>
          <td><asp:Textbox runat="server" ID="txtName" Width="200px" BorderColor="black" /></td>
          <td>Date: </td><td><asp:TextBox runat="server" ID="txtDate" Width="100px"
                                          BorderColor="black" placeholder="mm/dd/yyyy"/></td>
      </tr>
    </table>

    <table>
      <tr><td><asp:Button id="btnSave" runat="server" Text="Save" OnClick="btnSave_Click" /></td>
          <td><asp:Button id="btnCancel" runat="server" Text="Cancel" OnClick="btnCancel_Click" /></td>
      </tr>
    </table>
  </div>

  <table>
    <tr><td><asp:Button
            ID="AtRem" runat="server" Text="Delete using Attach/Remove"
            OnClick="AtRem_Click" /></td>
      <td><asp:Button
            ID="State" runat="server" Text="Delete using EntityState.Deleted"
            OnClick="State_Click" Width="347px" /></td>
    </tr>
  </table>

</form>
</body>
</html>

and this is the code-behind:

Listing 2: The code-behind for adding, editing or deleting rows:

using System;
using System.Linq;
using System.Collections.ObjectModel;

public partial class _Default : System.Web.UI.Page
{
  TestEntities mgr = new TestEntities();
  public ObservableCollection<Invoice> invoices { get; set; }
  public Invoice invoice { get; set; }

  protected void Page_Load(object sender, EventArgs e)
  { invoices = new ObservableCollection<Invoice>();
    LoadInvoices();
    addingTable.Visible = false;
  }

  private void LoadInvoices()
  { invoices.Clear();
    var query = mgr.Invoices.OrderBy(x => x.ID);
    foreach (Invoice I in query) { invoices.Add(I); }
    GridView1.DataSource = invoices; GridView1.DataBind();
  }

  protected void AtRem_Click(object sender, EventArgs e)
  { if (lblSelectedID.Text.Length == 0) return;
    int sel = int.Parse(GridView1.SelectedRow.Cells[1].Text);
    invoice = mgr.Invoices.Where(x=>x.ID == sel).SingleOrDefault();
    mgr.Invoices.Remove(invoice);
    mgr.SaveChanges();
    lblSelectedID.Text = "";
    LoadInvoices();
  }

  protected void State_Click(object sender, EventArgs e)
  { if (lblSelectedID.Text.Length == 0) return;
    int sel = int.Parse(GridView1.SelectedRow.Cells[1].Text);
    invoice = mgr.Invoices.Where(x => x.ID == sel).SingleOrDefault();
    mgr.Entry(invoice).State = System.Data.Entity.EntityState.Deleted;
    lblSelectedID.Text = "";
    mgr.SaveChanges();
    LoadInvoices();
  }

  protected void btnAdd_Click(object sender, EventArgs e)
  { lblSelectedID.Text = "";
    addingTable.Visible = true;
    btnAdd.Enabled = false;
    btnEdit.Enabled = false;
    lblState.Text = " Adding ";
    txtName.Text = "";
    txtDate.Text = "";
  }

  protected void btnSave_Click(object sender, EventArgs e)
  { if (lblState.Text == " Adding ")
    { invoice = new Invoice();
      invoice.CustomerName = txtName.Text.TrimEnd();
			invoice.Date = DateTime.Parse(txtDate.Text);
      mgr.Invoices.Add(invoice);
    }
    else
    { int ID = int.Parse(GridView1.SelectedRow.Cells[1].Text);
      invoice = mgr.Invoices.Where(x => x.ID == ID).SingleOrDefault();
      invoice.CustomerName = txtName.Text.TrimEnd();
      invoice.Date = DateTime.Parse(txtDate.Text.ToString());
    }

    mgr.SaveChanges();
    LoadInvoices();
    addingTable.Visible = false;
    lblState.Text = "";
    lblSelectedID.Text = "";
    btnAdd.Enabled = true;
    btnEdit.Enabled = true;
  }

  protected void GridView1_SelectedIndexChanged(object sender, EventArgs e)
  { lblSelectedID.Text = "Selected: " + GridView1.SelectedRow.Cells[1].Text; }

  protected void btnEdit_Click(object sender, EventArgs e)
  { if (lblSelectedID.Text == "") { return; }
    txtName.Text = GridView1.SelectedRow.Cells[2].Text;
    txtDate.Text = GridView1.SelectedRow.Cells[3].Text.ToString();
    addingTable.Visible = true;
    btnAdd.Enabled = false;
    btnEdit.Enabled = false;
    lblState.Text = " Editing ";
  }

  protected void btnCancel_Click(object sender, EventArgs e)
  { addingTable.Visible = false;
    lblState.Text = "";
    lblSelectedID.Text = "";
    btnAdd.Enabled = true;
    btnEdit.Enabled = true;
  }

}

Create an empty ASP.NET website, add the Invoices table using an ADO template to create the edmx, and then paste this code into the default.aspx and default.cs files respectively. Most of the code manages the UI, but with a little digging you'll find the core of what's required to add, update and delete rows.

Conclusion

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.

❑❑❑