Telemark
University College Department of Electrical Engineering, Information Technology and Cybernetics

Using SQL Server in C# with Examples Hans-­‐Petter Halvorsen, 2014.03.01

Faculty of Technology,

Postboks 203, Kjølnes ring 56, N-3901 Porsgrunn, Norway.

Tel: +47 35 57 50 00

Fax: +47 35 57 54 01

Table of Contents 1. Introduction ....................................................................................................................... 4 2. SQL Server .......................................................................................................................... 5 2.1. Database Systems ....................................................................................................... 5 2.2. Structured Query Language ........................................................................................ 5 3. Visual Studio ...................................................................................................................... 6 3.1. The .NET Framework ................................................................................................... 6 3.2. The C# Language ......................................................................................................... 6 4. Database Programming in C# ............................................................................................. 7 4.1. ADO.NET ..................................................................................................................... 7 4.2. Enterprise Library & Data Access Application Block ................................................... 8 4.3. LINQ ............................................................................................................................ 8 4.3.1. LINQ to SQL .......................................................................................................... 8 4.4. Entity Framework ........................................................................................................ 8 4.4.1. LINQ to Entities .................................................................................................... 8 5. ADO.NET .......................................................................................................................... 10 5.1. Introduction .............................................................................................................. 10 5.2. Examples ................................................................................................................... 10 6. Enterprise Library ............................................................................................................. 11 6.1. Data Access Application Block .................................................................................. 11 6.1.1. Installation ......................................................................................................... 11 7. LINQ ................................................................................................................................. 13 2

3

Table of Contents

7.1. LINQ to SQL ............................................................................................................... 13 8. Entity Framework ............................................................................................................. 14 8.1. … ............................................................................................................................... 14 9. Web Services .................................................................................................................... 15 10. 3-­‐Tier Architecure .......................................................................................................... 18 11. References ..................................................................................................................... 19

Tutorial: Using SQL Server in C# -­‐ with Examples

1. Introduction This Tutorial gives an overview of different techniques for communicationg with SQL Server in Visual Studio and C#. For more information about Software and Programming, please see my web site located here: http://home.hit.no/~hansha/

4

2. SQL Server …

2.1.

Database S ystems

… For more information about Database Systems, please see the Tutorial “Introduction to Database Systems” located here: http://home.hit.no/~hansha/?tutorial=database

2.2.

Structured Q uery L anguage

… For more information about SQL, please see the Tutorial “Structured Query Language” located here: http://home.hit.no/~hansha/?tutorial=sql

5

3. Visual Studio Microsoft Visual Studio is an integrated development environment (IDE) from Microsoft. Home page of Visual Studio: http://www.visualstudio.com

For more information about Visual Studio and C#, please see the Tutorial “Introduction to Visual Studio and C#” located here: http://home.hit.no/~hansha/?tutorial=csharp

3.1.

The . NET F ramework

The .NET Framework (pronounced “dot net”) is a software framework that runs primarily on Microsoft Windows. It includes a large library and supports several programming languages such as C#. .NET is included with Visual Studio.

3.2.

The C # L anguage

C# is pronounced “see sharp”. C# is an object-­‐oriented programming language and part of the .NET family from Microsoft. C# is very similar to C++ and Java. C# is developed by Microsoft and works only on the Windows platform.

6

4. Database Programming in C# Visual Studio, .NET and C# offers lots of different techniques for communicationg with Databases, espessially SQL Server. Some of the techniques discussed in this document are: •
•
•

•

ADO.NET Enterprise Library & Data Access Application Block LINQ o LINQ to SQL o LINQ to Enitities Entity Framework

4.1.

ADO.NET

ADO.NET is the basic data-­‐access technology for .NET. ADO.NET (ActiveX Data Object for .NET) is a set of computer software components that programmers can use to access data and data services. It is a part of the base class library that is included with the Microsoft .NET Framework. It is commonly used by programmers to access and modify data stored in relational database systems, though it can also access data in non-­‐relational sources. http://msdn.microsoft.com/en-­‐us/library/e80y5yhx(v=vs.110).aspx ADO.NET provides consistent access to data sources such as SQL Server and XML, and to data sources exposed through OLE DB and ODBC. The ADO.NET classes are found in System.Data.dll.

ADO.NET Code Examples: http://msdn.microsoft.com/en-­‐us/library/dw70f090(v=vs.110).aspx

7

8

Error! Reference source not found.

4.2. Enterprise L ibrary & D ata A ccess Application B lock Microsoft Enterprise Library is a collection of different libraries that makes your life as a programmer easier. In this document we will only use the Data Access Application Block. The Data Access Application Block simplifies many common data access tasks such as reading data for display, passing data through application layers, and submitting changed data back to the database system. It includes support for both stored procedures and in-­‐line SQL, can expose the data as a sequence of objects for client-­‐side querying, and provides access to the most frequently used features of ADO.NET in simple-­‐to-­‐use classes. You can use the NuGet Package Manager in Visual Studio to install the Enterprise Library assemblies that you need in your projects. Data Access Application Block Web Site: http://msdn.microsoft.com/en-­‐us/library/dn440726(v=pandp.60).aspx

4.3.

LINQ

…

4.3.1.

LINQ t o S QL

LINQ to SQL is a method to work with Microsoft SQL Server databases using LINQ.

Microsoft recommends that you use Entity Framework for new applications, because LINQ to SQL will not be further developed.

4.4.

Entity F ramework

… Entity Framework Overview: http://msdn.microsoft.com/en-­‐us/library/bb399567(v=vs.110).aspx

4.4.1.

LINQ t o E ntities Tutorial: Using SQL Server in C# -­‐ with Examples

9

Error! Reference source not found.

…

Tutorial: Using SQL Server in C# -­‐ with Examples

5. ADO.NET …

5.1.

Introduction

…

5.2.

Examples

…

10

6. Enterprise Library Enterprise Library is part of Microsoft Patters & Practices. Microsoft Patters & Practices are recommendations on how to design and develop custom applications using the Microsoft platform.

Microsoft Patters & Practices Web Site: http://msdn.microsoft.com/en-­‐us/library/ff921345.aspx

Enterprise Library Web Site: http://msdn.microsoft.com/en-­‐us/library/ff648951.aspx

Microsoft Enterprise Library is a collection of different libraries that makes your life as a programmer easier. In this document we will only use the Data Access Application Block.

6.1.

Data A ccess A pplication B lock

The Data Access Application Block simplifies many common data access tasks such as reading data for display, passing data through application layers, and submitting changed data back to the database system. It includes support for both stored procedures and in-­‐line SQL, can expose the data as a sequence of objects for client-­‐side querying, and provides access to the most frequently used features of ADO.NET in simple-­‐to-­‐use classes.

6.1.1.

Installation

You can use the NuGet Package Manager in Visual Studio to install the Enterprise Library assemblies that you need in your projects. Installation of the The Data Access Application Block:

11

12

Error! Reference source not found.

Tutorial: Using SQL Server in C# -­‐ with Examples

7. LINQ …

7.1.

LINQ t o S QL

…

13

8. Entity Framework …

8.1.

…

…

14

9. Web Services In many situations our apps don’t necessarly have direct access to the datbase, especially when your sofware is used ourside the local company network. In these situations we need to use a Web Service in order to write or read data from the database.

Today Web Services have been very popular. A Web service is a method of communications between two devices over the World Wide Web, and makes it easy to share data over a network or the internet. A Web Service is •
•
•
•

A Web API A Standard defined by W3C Cross-­‐platform and Platform-­‐independent Communication Distributed Application Development

Web Services can be implemented and used in most Programming Languages (C#/ASP.NET, PHP, LabVIEW, Objective-­‐C, Java, etc.) 15

16

Error! Reference source not found.

Web Services uses standard Web technology (Web protocols) such as HTTP, REST, SOAP, XML, WSDL, JSON, etc.

Web Services technology used in Web Services: •

HTTP -­‐ Hypertext Transfer Protocol

•

XML – Extensible Markup Language

•

WSDL -­‐ Web Services Description Language

•

SOAP -­‐ Simple Object Access Protocol

Tutorial: Using SQL Server in C# -­‐ with Examples

17

Error! Reference source not found.

Tutorial: Using SQL Server in C# -­‐ with Examples

10. 3-­‐Tier Architecure …

18

11. References ….

19

Telemark University College Faculty of Technology Kjølnes Ring 56 N-­‐3914 Porsgrunn, Norway www.hit.no

Hans-­‐Petter Halvorsen, M.Sc. Telemark University College Faculty of Technology Department of Electrical Engineering, Information Technology and Cybernetics

E-­‐mail: hans.p.halvorsen@hit.no Blog: http://home.hit.no/~hansha/

21

Error! Reference source not found.

Tutorial: Using SQL Server in C# -­‐ with Examples