développer avec un simple object mapping toolkit pour sql server
Post on 22-Jan-2018
222 Views
Preview:
TRANSCRIPT
Simple Object Mapping
pour SQL DatabasesDévelopper simplement et rapidement
dvoituron@outlook.com
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
dvoituron@outlook.com
www.dvoituron.com
@DenisVoituron
top related