MS SQL Server
  1. MS SQL Server
  3. Ajouter un assemblage pour la procédure CLR
Intereting Posts
Retourne une ligne sur l'instruction case sur la table jointe externe gauche Statistiques Microsoft SQL Server via ODBC Comment puis-je écrire un file XML XQuery stocké dans des tables SQL Server distinctes? Quel est '.' dans une requête SQL Server Comment unifier deux requêtes SQL TSQL GROUP BY sans agrégation La sélection du dernier logging dans la database SQL Server ne fonctionne pas toujours Table de tableau croisé dynamic SQL Server avec des nombres et des sums Le type de la valeur affectée à la variable "User :: de_sters" diffère du type de variable en cours Comparez chaque logging de la même table dans le server sql et renvoyez les duplicates Regrouper le resultset sql en valeur HashBytes Teste si Table a au less une certaine procédure stockée, sinon c # de test échoue Comment faire des valeurs XML séparées par des virgules en utilisant XPath, XQuery dans SQL Server SQLServer – Sélectionnez boolean si la colonne commence par une string Rechercher les dates les plus récentes et les plus récentes des loggings spécifiés à partir d'une table à l'aide de SQL

Ajouter un assemblage pour la procédure CLR

J'essaie de créer une procédure CLR pour exporter des données SQL vers Excel qui contiendra plus de fonctionnalités que d'autres options telles que les sous-totaux et la mise en évidence.

Cela m'oblige à referencer la DLL Microsoft.Office.Interop.Excel , mais je ne suis pas sûr de savoir comment inclure réellement l'assembly lorsque je comstack mon code.

Comment puis-je inclure l'assembly Excel dans ma procédure CLR? 

 using System; using System.Collections.Generic; using System.Text; using System.Data.SqlClient; using Excel = Microsoft.Office.Interop.Excel; using System.Reflection; public class ExportToExcel { [Microsoft.SqlServer.Server.SqlProcedure] public static void ExportQueryResults(ssortingng queryText, ssortingng worksheetName, ssortingng fileName) { using (SqlConnection cnn = new SqlConnection("context connection=true")) { //the temp list to hold the results in List<object[]> results = new List<object[]>(); cnn.Open(); //create the sql command SqlCommand cmd = new SqlCommand(queryText, cnn); using (SqlDataReader reader = cmd.ExecuteReader()) { int fieldCount = reader.FieldCount; object[] headers = new object[fieldCount]; for(int i = 0; i < fieldCount; i++) { headers[i] = reader.GetName(i); } //read the results while (reader.Read()) { object[] values = new object[fieldCount]; for (int i = 0; i < fieldCount; i++) { values[i] = reader[i]; } results.Add(values); } //convert the results into a 2-d array to export into Excel object[,] exportVals = new object[results.Count, fieldCount]; for (int row = 0; row < results.Count; row++) { for (int col = 0; col < fieldCount; col++) { exportVals[row, col] = results[row][col]; } } Excel.Application _app = new Excel.Application(); Excel.Workbook _book = _app.Workbooks.Add(Missing.Value); Excel.Worksheet _sheet = (Excel.Worksheet)_book.ActiveSheet; Excel.Range _range = (Excel.Range)_sheet.Cells[1, 1]; _range = _sheet.get_Range(_sheet.Cells[1, 1], _sheet.Cells[results.Count, fieldCount]); _range.Value2 = exportVals; _sheet.Name = worksheetName; //remove any extra worksheets foreach(Excel.Worksheet sht in _book.Worksheets) { if (sht.Name != worksheetName) sht.Delete(); } _book.SaveAs(fileName , Excel.XlFileFormat.xlWorkbookDefault , Missing.Value , Missing.Value , false , false , Excel.XlSaveAsAccessMode.xlNoChange , Missing.Value , Missing.Value , Missing.Value , Missing.Value , Missing.Value); } } } }

Il n'est pas possible d'utiliser des assemblys arbitraires dans SQL Server. Vous devez referencer seulement un sous – set du cadre et exercer une discipline de base . Je doute qu'une application monstre avec des couches d'historique telles qu'Excel soit chargée dans cet environnement.

Vous voudrez peut-être jeter un coup d'œil aux fonctionnalités de rlocation plus simples qui répondent à ces critères.

Si vous avez besoin de plus, envisagez d'utiliser Excel du côté client.

Après une journée de tâtonnements avec presque tous les types d'erreurs possibles, j'ai trouvé une solution à mes problèmes.

J'apprécie les réponses / commentaires donnés, et même si je suis d'accord, il peut y avoir des moyens plus efficaces / sécurisés de mettre en œuvre une solution, l'utilisation de l'assembly interop était la plus rapide et la plus familière pour compléter ce projet.

Avant que je sois ratissé sur les charbons, s'il vous plaît réaliser que l'auto-filtrage et autre formatting en dehors de la scope de la fonctionnalité d'export SQL Server plus traditionnelle étaient absolument nécessaires pour ce projet.

Solution

J'ai créé un projet de studio visuel de type sortie de bibliothèque de classs intitulé SqlProcedures , créé une nouvelle class appelée ExportToExcel et ajouté Microsoft.Office.Interop.Excel à mes references.

Voici le code dans mon file ExportToExcel.cs : 

 using System; using System.Collections.Generic; using System.Text; using System.Data.SqlClient; using Excel = Microsoft.Office.Interop.Excel; using System.Reflection; using System.Runtime.InteropServices; public class ExportToExcel { [Microsoft.SqlServer.Server.SqlProcedure] public static void ExportQueryResults(ssortingng queryText, ssortingng worksheetName, ssortingng fileName) { using (SqlConnection cnn = new SqlConnection("context connection=true")) { //the temp list to hold the results in List<object[]> results = new List<object[]>(); cnn.Open(); //create the sql command SqlCommand cmd = new SqlCommand(queryText, cnn); using (SqlDataReader reader = cmd.ExecuteReader()) { int fieldCount = reader.FieldCount; object[] headers = new object[fieldCount]; for (int i = 0; i < fieldCount; i++) { headers[i] = reader.GetName(i); } //read the results while (reader.Read()) { object[] values = new object[fieldCount]; for (int i = 0; i < fieldCount; i++) { values[i] = reader[i]; } results.Add(values); } //convert the results into a 2-d array to export into Excel object[,] exportVals = new object[results.Count, fieldCount]; for (int row = 0; row < results.Count; row++) { for (int col = 0; col < fieldCount; col++) { exportVals[row, col] = results[row][col]; } } Excel.Application _app = new Excel.Application(); Excel.Workbook _book = _app.Workbooks.Add(Missing.Value); Excel.Worksheet _sheet = (Excel.Worksheet)_book.ActiveSheet; Excel.Range _range = (Excel.Range)_sheet.Cells[1, 1]; _app.DisplayAlerts = false; //set the headers and freeze the panes _range = _sheet.get_Range(_sheet.Cells[1, 1], _sheet.Cells[1, fieldCount]); _range.NumberFormat = "@"; _range.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft; _range.Value2 = headers; _range.Font.Bold = true; _range = _sheet.get_Range(_sheet.Cells[2, 1], _sheet.Cells[2, 1]); _range.EntireRow.Select(); _range.Application.ActiveWindow.FreezePanes = true; _range = _sheet.get_Range(_sheet.Cells[2, 1], _sheet.Cells[results.Count, fieldCount]); _range.Value2 = exportVals; _range = _sheet.get_Range(_sheet.Cells[1, 1], _sheet.Cells[exportVals.Length, fieldCount]); _range.AutoFilter(1, Type.Missing, Excel.XlAutoFilterOperator.xlAnd, Type.Missing, true); _sheet.Cells.Columns.AutoFit(); _sheet.Range["A1"].Select(); _sheet.Name = worksheetName; //remove any extra worksheets foreach (Excel.Worksheet sht in _book.Worksheets) { if (sht.Name != worksheetName) sht.Delete(); } _book.SaveAs(fileName , Excel.XlFileFormat.xlExcel5 , Missing.Value , Missing.Value , false , false , Excel.XlSaveAsAccessMode.xlNoChange , Missing.Value , Missing.Value , Missing.Value , Missing.Value , Missing.Value); //_book.Close(Missing.Value, Missing.Value, Missing.Value); _app.Application.Quit(); GC.Collect(); GC.WaitForPendingFinalizers(); Marshal.ReleaseComObject(_range); Marshal.ReleaseComObject(_sheet); Marshal.ReleaseComObject(_book); Marshal.ReleaseComObject(_app); _range = null; _sheet = null; _book = null; _app = null; GC.Collect(); GC.WaitForPendingFinalizers(); } } } }

Après avoir construit la DLL avec succès, je l'ai copiée dans un directory local sur mon server SQL.

Pour exécuter la procédure ExportQueryResults , j'ai dû append plusieurs assemblys dans le server SQL dont dépend Microsoft.Office.Interop.Excel.dll .

Voici mon code SQL: 

 ALTER DATABASE main SET TRUSTWORTHY ON; create assembly [stdole] from 'C:\Program Files\Microsoft.NET\Primary Interop Assemblies\stdole.dll' WITH PERMISSION_SET = unsafe create assembly [Office] from 'C:\WINDOWS\assembly\GAC\office\12.0.0.0__71e9bce111e9429c\OFFICE.DLL' WITH PERMISSION_SET = unsafe create assembly [Vbe] FROM 'C:\WINDOWS\assembly\GAC\Microsoft.Vbe.Interop\12.0.0.0__71e9bce111e9429c\Microsoft.Vbe.Interop.dll' WITH PERMISSION_SET = unsafe create assembly [Microsoft.Office.Interop.Excel.dll] from 'C:\WINDOWS\assembly\GAC\Microsoft.Office.Interop.Excel\12.0.0.0__71e9bce111e9429c\Microsoft.Office.Interop.Excel.dll' WITH PERMISSION_SET = unsafe create assembly SqlProcedures from 'c:\sql_data_reporting\SqlProcedures.dll' WITH PERMISSION_SET = unsafe go create procedure ExportToExcel @queryText nvarchar(4000), @worksheetName nvarchar(32), @fileName nvarchar(250) as external name SqlProcedures.ExportToExcel.ExportQueryResults go

Maintenant, je sais que l'utilisation with permission_set = unsafe est un nono, mais c'était un projet "get it done now", et c'était la solution la plus rapide que je pouvais find.

Espérons que cette solution permettra aux autres users de mettre en œuvre des fonctionnalités similaires de gagner du time.

Je crois que vous pouvez simplement append l'assemblage référencé de la même manière que vous avez ajouté votre assemblage de base. Le problème est que l'assembly interop est un wrapper fin autour de l'object COM Excel. Cela signifie que l'interopérabilité sera sans valeur si vous n'installez pas Microsoft Excel sur votre server SQL. Je ne suis même pas sûr que ce soit possible, mais cela semble vraiment une très mauvaise idée.