septembre
2011
Un article de zinzineti
Depuis que les « centres d’appels » sont devenus des centres de contacts, les télé-conseillers traitent à la fois des appels téléphoniques (voix), des emails, des tchats,…. Et la plupart des solutions de traitement d’emails pour centre de contacts stockent les pièces jointes (Attachments) des emails dans des bases de données. Pour SQL Server >= 2005 les pièces jointes sont stockées dans des tables (colonnes) de types varbinary (max) et pour des solutions utilisant le SGBD ORACLE les colonnes utilisées sont souvent de types BLOB ou CLOB ou NCLOB. Les pièces jointes d’un email peuvent être des images (.gif,.jpeg,…), des documents (.doc,.pdf,…), des vidéos (.wav, .vlc, …). Ces pièces jointes stockées dans des tables ne sont pas directement lisible par un humain.
Depuis SQL Server il est possible d’utiliser des procédures CLR pour décoder les données de type varbinary.
Prenons un exemple simple d’un model contenant la table des emails (T_Emails) et ces pièces jointes (T_Attachments)
Nous allons nous intéressé à la table des pièces jointes (T_Attachments) pour montrer comment lire (décoder) le contenu d’une colonne varbinary à l’aide d’une procédure CLR. Voici le contenu de la table T_Attchments :
¤ La première ligne de cette table montre que la pièce jointe est un fichier .PDF et le binaire de ce fichier est stocké dans la colonne AttachmentData de type varbinary(max)
¤ La deuxième ligne montre un fichier .doc
Comment décoder (rendre lisible) le contenu de la colonne AttachmentData ?
=> Création dans Visual Studio de la procédure CLR
(image du CLR dans Visual Studio)
(code du CLR)
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.IO;
public partial class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void P_CLR_GetSqlBinary(string DestFolderPath,string fileName)
{
try
{
SqlConnection cnx = new SqlConnection(@"Data Source=PP-ZINZINDOHOUE\SQL2008R2;User Id=mylogin;Password=myPWD;Initial Catalog=MaBase");
cnx.Open();
SqlCommand cmd = new SqlCommand("P_GenFileFromBinaryData", cnx);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@FileName", SqlDbType.VarChar, 2147483647).Value = fileName;
cmd.ExecuteNonQuery();
byte[] buffer = (byte[])cmd.ExecuteScalar();
FileStream fs = new FileStream(DestFolderPath + fileName, FileMode.Create);
fs.Write(buffer, 0, buffer.Length);
fs.Close();
cnx.Close();
}
catch (Exception ex)
{
SqlContext.Pipe.Send(ex.Message);
}
}
};
Cette procédure P_CLR_GetSqlBinary(string DestFolderPath,string fileName) prend en entrée le répertoire de sortie des données varbinary et le nom du fichier de sortie. Cette procédure CLR appel la procédure T-SQL P_GenFileFromBinaryData
=> Création de la procédure stockée P_GenFileFromBinaryData utilisé dans le CLR
@FileName NVARCHAR(1024)
AS
SET NOCOUNT ON
BEGIN
SELECT AttachmentData
FROM MaBase.dbo.T_Attachments
WHERE AttachmentFileName = @FileName
END
cette procédure stockée T-SQL sélectionne la colonne AttachmentData de la table T_Attachments dont le nom de la pièce jointe est passé en paramètre (@FileName)
Une fois le code C# généré et déployé dans Visual Studio on peut faire clic droit > Propriétés sur le projet pour enregistrer l’Assemblys et configurer la sécurité.
On peut aussi enregistrer l’Assemblys et configurer la sécurité depuis SSMS avec le script suivant:
RECONFIGURE WITH OVERRIDE
GO
ALTER DATABASE MaBase
SET TRUSTWORTHY ON;
GO
CREATE ASSEMBLY [P_CLR_GetSqlBinary]
AUTHORIZATION [dbo]
FROM '\Projects\GetSqlBinary\GetSqlBinary\bin\Debug\GetSqlBinary.dll'
WITH PERMISSION_SET = SAFE -- UNSAFE ou EXTERNAL_ACCESS
GO
=> Test de la procédure CLR
–> Test 1
EXEC dbo.P_CLR_GetSqlBinary 'E:\TestSQLBinary\','PDFFile.pdf'
–> Test 2
EXEC dbo.P_CLR_GetSqlBinary 'E:\TestSQLBinary\','DOCFile.doc'
=> Résultat des tests
——————————————-
Etienne ZINZINDOHOUE
——————————————-