SQL Server with C# Applications

Welcome to SQL Server with C#

This page is dedicated to my book about SQL Server programming . You find links to the book at various Amazon sites at the bottom of this page. The paperback version is also sold in other bookstores.

Cover of SQL-Server with C#
Cover of SQL-Server with C#

Book Concept

This book demonstrates how to model and create a SQL Server database, and how to use it from a C#-based application. This is done in a way that helps you understand what you are doing, instead of just using wizards. The idea is to use as few extra libraries and frameworks as possible – keeping the focus on the database and how to use it. Avoiding fancy frameworks is a goal in itself, as these come and go – making maintenance a pain.

Throughout the book a common sample is used: a Time-Registration system. This might sound rather mundane, and maybe it is, but the domain is easy to understand and allows you to focus your mind on the database-programming.

2019 Update

In August 2019 the book was updated with improved type-setting. This was based on what I had recently learned from working with my editor from De Gruyter on “Embedded Software for the IoT“. At the same time a nasty typo was fixed. This was reported by user “Paul” in the UK. Since the book is printed on demand, both paperback and e-book were immediately updated.

Back Cover

SQL Server with C# backcover
Back cover of SQL Server with C#

The back cover word-cloud contains a lot of important keywords and phrases when working with databases. The color-coding is as follows:

  • White: Terms that are general in the programming world.
  • Red: Overall concepts from the database world – often used in marketing.
  • Yellow: Specific terms from the database world that has a well defined theoretical meaning.
  • Green: SQL keywords

Text from the Back Cover of SQL Server with C#

Creating a full database application from scratch can be a challenge. You need to understand the basics of database design, as well as the specific Relational Database Management System (RDBMS) and its SQL. You also need to somehow interface to this from your application which, by the way, you also need to design. You can get far with frameworks, but can you go all the way? Do you have a well-performing and maintainable solution?

This book aims to guide you through the jungle. SQL Server is chosen as the main RDBMS, while the database-interface design is demonstrated to work with Oracle as well. The real-life sample application is written in C# using classic Windows Forms. However, the main design and database-interface is equally relevant for WPF and ASP.net.

The database framework used is ADO.net.

The author works with embedded systems and previously wrote the book “Embedded Software for the IoT”. Creating database-based tools, he faced the challenges described, and refined a pattern over time.

The SQL Server Sample

We used the product in my company for daily time-registration of all R&D employees. Through the fast filters we could answer questions such as:

  • How much time is spent on new products, and how much on maintenance?
  • What is the current spend on all projects – to this day?
  • What has specific departments worked on?
  • What is expensive in this project – embedded, digital hardware – or?
  • Do we allocate as many hours in this project as we planned – or are they spent on other stuff?
  • How many hours can we bill this customer?

The Excel export allowed us to do simple pie-charts as well as layered time-graphs and reports – accumulated as well as distributed over months.

Source for the Sample

The source is found as a git-repository on  https://bitbucket.org/klauselk/timereg.

Table of Contents

1   Introduction
1.1 What to expect from this book
1.2 Tools
1.3 TimeReg Sample

Part I SQL-Server
2   Database Basics

SQL Server relations
Unique relation

2.1 Database Vocabulary
2.2 CRUD and ACID
2.3 OLTP versus OLAP
2.4 Keys
2.4.1 Management Studio and Keys
2.4.2 Composite Keys
2.4.3 Natural and Surrogate Keys
2.4.4 Identity
2.5 Normalization
2.5.1 Case: Students Database
2.5.2 Script Table
2.5.3 Anomalies
2.5.4 Association Tables
2.5.5 Students Database 3NF
2.5.6 Order System 3NF
2.6 Joins
2.7 Data Types and NULL
2.8 Integrity and Constraints

3   TimeReg Database

Entity Relation Diagram
Main Entity Relations for TimeReg

3.1 Introduction
3.2 TimeReg Requirements
3.3 Logical Design
3.4 Physical Design
3.5 TimeReg Data sample

4   SQL 
4.1 Introduction to SQL
4.1.1 Parameters
4.1.2 SELECT
4.1.3 INSERT, UPDATE and DELETE
4.2 Next Level SQL
4.2.1 Identity
4.2.2 Group By
4.3 Learnings on CRUDSQL Server performance analysis
4.4 Indexing
4.5 Views
4.6 Common Table Expressions
4.7 Stored Procedures
4.8 Security and Connection Strings

Part II C# Programming
5   Overall Design
5.1 Database Frameworks
5.2 Layers and State
5.3 Class Diagram

Frameworks for SQL Server
Possible frameworks – incl the single-file “custom”.

6   Model Layer
6.1 Initialization
6.2 Data for the Main form

7   Database Functions
7.1 Design
7.2 ExecuteNonQuery
7.3 ExecuteScalar
7.4 ExecuteReader
7.5 SqlDataAdapter
7.6 Oracle Managed Data Access

8   Database Transfer Objects
8.1 CellData Class
8.2 Week Class
8.3 Classic Class Concept
8.4 A Dapper alternative

9   UI Design
9.1 ListBox
9.2 DataGridView
9.2.1 Initialize Grid
9.2.2 Update Grid
9.2.3 Edit in Grid
9.2.4 Submit to database
9.2.5 Double Buffering
9.2.6 Multiple uses of a single DataGridView

10   Using Excel
10.1 Exporting Data
10.2 Importing data
10.3 Copy-Paste to and from Excel

UI for the sample application
Report UI. More advanced results go directly into Excel.

Pricing

The paperback is priced at 14.95$ at Amazon while the Kindle e-book is 9.95$. If you buy the paperback first, you can download the Kindle version for free. Please note that the Kindle e-book is created as a “Text-Book” (which makes sense). This keeps all formatting, like tables and figures, but does not give the free flow and choice of e.g. fonts that you get with e-book fictional novels.

Ordering on Amazon