Développer avec un Simple Object Mapping Toolkit pour SQL Server

Simple Object Mapping

pour SQL DatabasesDévelopper simplement et rapidement

[email protected]

www.dvoituron.com

@DenisVoituron

2

A propos…

Denis Voituron

1995 – Ingénieur Civil

1999 – Co-fondateur d’une société spécialisée dans les CMS

2007 – Microsoft Senior Architect chez Trasys / NRB

2

@DenisVoituron

dvoituron.com

.be

3

Agenda

• Background

• SQL Architecture

• ADO.NET

• EntityFramework

• Comparaison

• Simple Object Mapping

• Dapper.NET

• SqlDatabaseCommand

• SQLite

• SQL Server CLR Stored Procedures

3

Backgound

5

Architecture

SCOTT Database

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

7839 KING PRESIDENT 17-NOV-81 5000 10

7698 BLAKE MANAGER 7839 01-MAY-81 2850 30

7756 CLARK MANAGER 7839 09-JUN-81 1500 10

... ...

... ...

7456 JONES MANAGER 7839 02-APR-81 2975 20

DEPTNO DNAME LOC

10 ACCOUNTING NEW YORK

20 RESEARCH DALLAS

30 SALES CHICAGO

40 OPERATIOS BOSTON

7

ADO.NET

7

using (var connection = new SqlConnection(CONNECTION_STRING)){

connection.Open();

connection.Close();}

SELECT ENAME FROM EMP WHERE EMPNO = 7369

using (var cmd = connection.CreateCommand()){

cmd.CommandText = "SELECT ENAME " +" FROM EMP " +" WHERE EMPNO = 7369 ";

}

using (var adapter = new SqlDataAdapter(cmd)){

DataTable table = new DataTable();adapter.Fill(table);string name = table.Rows[0].Field<string>("ENAME");

}

8

Entity Framework

8

Object Relational Mapping

var db = new SCOTTEntities();

var query = from e in db.EMPswhere e.EMPNO == 7369select e;

var name = query.First().DEPT.DNAME;

SELECT TOP (1) [Extent1].[EMPNO] AS [EMPNO], [Extent1].[ENAME] AS [ENAME], [Extent1].[JOB] AS [JOB], [Extent1].[MGR] AS [MGR], [Extent1].[HIREDATE] AS [HIREDATE], [Extent1].[SAL] AS [SAL], [Extent1].[COMM] AS [COMM], [Extent1].[DEPTNO] AS [DEPTNO]FROM [dbo].[EMP] AS [Extent1]WHERE 7369 = [Extent1].[EMPNO]

SELECT [Extent1].[DEPTNO] AS [DEPTNO], [Extent1].[DNAME] AS [DNAME], [Extent1].[LOC] AS [LOC]FROM [dbo].[DEPT] AS [Extent1]WHERE [Extent1].[DEPTNO] = @V1

9

ADO.NET vs Entity Framework

9

Performance

Speed of Development

Maintainable code (neat)

Flexibility

Scalability

http://stackoverflow.com/questions/2698151/entity-framework-vs-linq-to-sql-vs-ado-net-with-stored-procedures

10

Performances

10http://blogs.msdn.com/b/adonet/archive/2012/02/14/sneak-preview-entity-framework-5-0-performance-improvements.aspx

Simple Object Mapping

12

Dapper.NET

Bibliothèque qui étend IDbConnection.

Besoin d’une connexion déjà ouverte.

using (var connection = new SqlConnection(CONNECTION_STRING)){

connection.Open();

string sql = "SELECT * FROM EMP WHERE EMPNO = @Id";var emp = connection.Query<EMP>(sql, new { Id = 7369 });

}

13

Query

Query Dynamic

ExecuteScalar

Execute

Buffered

Dapper.NET

string sql = "SELECT * FROM EMP WHERE EMPNO = @Id";var emp = connection.Query<EMP>(sql, new { Id = 7369 });

string sql = "SELECT * FROM EMP WHERE EMPNO = @Id";var emp = connection.Query(sql, new { Id = 7369 });

string sql = "SELECT ENAME FROM EMP WHERE EMPNO = @Id";var emp = connection.ExecuteScalar<string>(sql, new { Id = 7369 });

var n = connection.Execute(“DELETE FROM EMP");

var emp = connection.Query(sql, buffered: false);

14

SqlDatabaseCommand

Objets and Commandes

Construction et destruction propres

Optimisation des paramètres (éviter l’injection SQL) et SQL

Conversion automatique d’objets C#

Génération des entités C#

Gestion des logs et traces

Méthodes d’extension de System.Data

Transformation des propriétés C# en paramètres SQL

DBNull

...

15

SqlDatabaseCommand

Exemple

using (var cmd = new SqlDatabaseCommand(CONNECTION_STRING)){

}

cmd.CommandText.AppendLine(" SELECT * ");cmd.CommandText.AppendLine(" FROM EMP ");

var emps = cmd.ExecuteTable<Employee>();

cmd.CommandText.AppendLine(" WHERE HIREDATE = @HireDate ");cmd.Parameters.AddValues(new{

HireDate = new DateTime(1980, 12, 17)});

16

9876 NEW

SqlDatabaseCommandEMPNO ENAME

7839 KING

7698 BLAKE

7756 CLARK

...

...

7456 JONESvar emps = cmd.ExecuteTable<Employee>();

• ExecuteTable

var smith = cmd.ExecuteRow<Employee>();

• ExecuteRow

var name = cmd.ExecuteScalar<String>();

• ExecuteScalar

var n = cmd.ExecuteNonQuery();

• ExecuteQuery

17

SqlDatabaseCommand

Paramètres

cmd.CommandText.AppendLine(" SELECT ENAME ").AppendLine(" FROM EMP ").AppendLine(" WHERE EMPNO = @EmpNo ").AppendLine(" AND HIREDATE = @HireDate ");

cmd.Parameters.AddWithValue("@EmpNo", 7369);cmd.Parameters.AddWithValue("@HireDate", new DateTime(1980, 12, 17));

var name = cmd.ExecuteScalar();

cmd.CommandText.AppendLine(" SELECT ENAME ").AppendLine(" FROM EMP ").AppendLine(" WHERE EMPNO = @EmpNo ").AppendLine(" AND HIREDATE = @HireDate ");

cmd.Parameters.AddValues(new{

EmpNo = 7369,HireDate = new DateTime(1980, 12, 17)

});

var name = cmd.ExecuteScalar();

18

SqlDatabaseCommand

Traces

Logging

Query Formatter

cmd.Log = Console.WriteLine;

cmd.Log = (message) => {

Console.WriteLine(message); };

string formatted = cmd.GetCommandTextFormatted(QueryFormat.Text);

SELECT ENAME FROM EMP

WHERE EMPNO = 7369 AND HIREDATE = '1970-05-04 14:15:16'

string formatted = cmd.GetCommandTextFormatted(QueryFormat.Html);

SELECT ENAMEFROM EMPWHERE EMPNO = 7369AND HIREDATE = '1970-05-04 14:15:16'

19

SqlDatabaseCommand

Générateur d’entités// *********************************************// Code Generated with Apps72.Dev.Data.Generator// *********************************************using System;

namespace Data.Tests.Entities{

/// <summary />public partial class BONUS{

/// <summary />public virtual String ENAME { get; set; }/// <summary />public virtual String JOB { get; set; }/// <summary />public virtual Int32? SAL { get; set; }/// <summary />public virtual Int32? COMM { get; set; }

}/// <summary />public partial class DEPT{

/// <summary />public virtual Int32 DEPTNO { get; set; }/// <summary />public virtual String DNAME { get; set; }/// <summary />public virtual String LOC { get; set; }

}

var entitiesGenerator = new SqlEntitiesGenerator(CONNECTION_STRING);foreach (var table in entitiesGenerator.Tables){

...}

20

SqlDatabaseCommand

Bonnes pratiques

public class DataService : IDataService{

public SqlDatabaseCommand GetDatabaseCommand(){

return new SqlDatabaseCommand(CONNECTION_STRING);}

public SqlDatabaseCommand GetDatabaseCommand(SqlTransaction trans){

return new SqlDatabaseCommand(trans.Connection, trans);}

} using (var cmd = service.GetDatabaseCommand()){

...}

CLR Stored Procedures

22

CLR Stored Procedures

Quoi ?

Vous pouvez écrire des procédures stockées, des déclencheurs, des types, des fonctions,

des agrégats et des fonctions d’accès aux tables, à l'aide du langage .NET Framework

Pourquoi ?

Performances

Outils de développement (VS, GIT, …)

Centralisation du code

Déploiement

23

SqlDatabaseCommand

1. Créer une bibliothèque de classe

2. Ajouter le package NuGet SqlServerClr

24

SqlDatabaseCommand

[SqlFunction(DataAccess = DataAccessKind.Read)]public static int GetMaximumAge(){

using (var cmd = new SqlDatabaseCommand("context connection=true")){

...}

}

CREATE FUNCTION GetMaximumAge()RETURNS INTAS EXTERNAL NAME SampleSqlDatabaseCommandClr.SampleCLR.GetMaximumAge

25

SqlDatabaseCommand

[SqlFunction()]public static bool IsComparableTo(string text1, string text2){

return text1.ComparableTo(text2) == 0;}

SELECT dbo.IsComparableTo('Maison', 'House') -- FALSESELECT dbo.IsComparableTo('St Ecole', 'Saint''école&') -- TRUESELECT dbo.IsComparableTo('A''&é', 'aE') -- TRUE

ADO.NET

EntityFramework

SQLite

Dapper.NET

SqlDatabaseCommand

CLR Stored Procedure

Conclusion & Questions

Merci de votre participation

[email protected]

www.dvoituron.com

@DenisVoituron

Software

Développer avec un Simple Object Mapping Toolkit pour SQL Server