SQL Server with C# Applications

Welcome to SQL Server with C#

This page is dedicated to my book about SQL Server programming – using SQL and C# . 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#

2019 and 2023 Updates

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. If you bought the older e-book version, please go to Amazon and search for “Update Your Kindle Book Version”. You can upgrade for free.

In October 2023 I launched a new book –  Microcontrollers with C . While working with this, I also updated typos in the SQL book. This will be reflected in new purchases – but you cannot get updates of e-books already downloaded as these fixes are very minor.

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. You may say that this book provides a “timeless”, and easy-maintainable framework, that can be included with your code.

I truly believe that this gives the reader more lasting value than the many screenshot-based recipes out there.

Throughout the book a common sample is used: a Time-Registration system. This domain is easy to understand and allows you to focus your mind on the database-programming. And – Time-Registration is not as trivial as you might think 🙂

Appraisal of SQL Server with C#

I received a very nice mail from a seasoned software developer. Among other nice things he wrote:

I think your book is what has been needed because most books come off as academic rather than practical….. Your book is what the Doctor ordered for practical use.

 

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 (TimeReg)

We used the TimeReg product in my (now former) company for daily time-registration of all R&D employees. Through the fast filters we could at anytime 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 to – 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 ExcelUI for the sample application

Report UI. More advanced results go directly into Excel.

Formats and Pricing

At the time of this writing, the paperback is priced at 13.23$ at Amazon (US) while the Kindle e-book is 7.49$. 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.

To keep costs down the paperback is in B/W – most of the content is text and tables anyway. However, the Kindle version is in color. This improves e.g. screenshots and figures.

Ordering on Amazon