SQL Server with C# Applications

Welcome to SQL Server with C#

This page is dedicated to my book about SQL Server programming – released July 2018 on Amazon.  You find links to the book at various Amazon sites at the bottom of this page.

Cover Page for SQL Server with C#

The concept

This book explains how to create a SQL Server database, and how to use it from a C#-based application. This is done in a way so that you actually 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.

All 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 again 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 databaseinterface 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

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

Framework Used

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

Price

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 normal e-books.

Ordering on Amazon