SQL Server with C# Applications

Welcome to SQL Server with C#

This page is dedicated to my 2018 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 for sale via other bookstores.

Cover Page for SQL Server with C#

The concept

This book demonstrates how to 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.

Through 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 on the database-programming.

From the rear 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.

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

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

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 CRUD
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

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

Prices

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