SQL Server Reporting Services custom Data processing extension DPE (data processing Extension)

When I was working on a SSRS project recently, I encountered a situation where the project had multiple databases, each on a different server, but the database objects (Table/view/sps/functions) owned by each database were identical. Later, the solution was found with the numerous resources on the network, namely data processing Extensio (DPE). The so-called DPE, bluntly speaking, is to develop your own DLL to extend the SSRS data source, as shown in the following:

1. Create a new Class library project and introduce the following two DLLs:

C:\Program Files\Microsoft SQL Server\mssqlserver\reporting Services\reportserver\bin\ Microsoft.ReportingServices.Interfaces.dll

C:\Program Files (x86) \reference Assemblies\microsoft\framework\v3.5\system.data.datasetextensions.dll

2. Get connection String According to the parameters: Create a new DbConnection class file, implement the interface IDbConnectionExtension and override the methods in it:

A. Setconfiguration (): Primarily a configuration file that implements the Read database connection:

 Public voidSetconfiguration (stringconfiguration) {XmlDocument xmldoc=NewXmlDocument ();    Xmldoc.loadxml (configuration); stringConfigFilePath = AppDomain.CurrentDomain.SetupInformation.ApplicationBase + xmldoc.childnodes[0]. attributes["ConfigSource"].    InnerText; Execonfigurationfilemap file=NewExeconfigurationfilemap (); File. Execonfigfilename=ConfigFilePath; Configuration Config=configurationmanager.openmappedexeconfiguration (file, configurationuserlevel.none); foreach(Connectionstringsettings CFinchCONFIG. connectionstrings.connectionstrings) {Connectionstrings.add (cf. Name, cf.    ToString ()); }}

View Code

B. Getconnectionbyparameter (): Mainly according to the user's choice to return the corresponding database connection;

Public SqlConnection getconnectionbyparameter (idataparametercollection parameters) {    var connstring = connectionstrings["Centraldbconnstring"];    foreach (dbparameter param in Parameters)    {        if (param. Parametername.equals ("Cliniccode"))        {            //using The first value if the Cliniccode were selected for more than 1 Opti On.            if (param. Values! = null && param. Values.length > 1)            {                param. Value = param. Values[0];            }            connstring = Getconnectionstringsconfig (param. Value.tostring ());        }    }    return new SqlConnection (connstring);}

View Code

3. Execute SQL command: Create a new DbCommand class file, implement the interface IDbCommand and override the methods in it:

A. ExecuteReader (): Execute SQL Command;

 PublicIDataReader ExecuteReader (CommandBehavior behavior) {Debug.WriteLine ("Idbcommand.executereader with CommandBehavior."+behavior); //get connection based on parameters    using(SqlConnection conn = m_connection. Getconnectionbyparameter ( This. Parameters)) {Conn.        Open (); SqlCommand cmd=NewSqlCommand (CommandText, conn); Cmd.commandtype= (System.Data.CommandType) This.        CommandType; foreach(DbParameter paraminch  This. Parameters) {if(Param. Value! =NULL) cmd. Parameters.Add (NewSqlParameter (param. ParameterName, Param.            Value)); Elsecmd. Parameters.Add (NewSqlParameter (param.        ParameterName, DBNull.Value)); } SqlDataAdapter SqlDA=NewSqlDataAdapter (CMD); System.Data.DataSet Dsresult=NewSystem.Data.DataSet ();        Sqlda.fill (Dsresult); M_datatable= dsresult.tables[0]; Conn.        Close (); return NewDbDataReader ( This); }}

View Code

4. Summary: Finally, there are several interfaces that need to be implemented, including: Idatareader,idataparameter, Idatamultivalueparameter,idataparametercollection, and some of the methods to be implemented. After completing the above steps, all of the basic work has been completed and the next step is to deploy the DLL.

5. Deployment: There are two options for SSRS in design mode, and the other is for SSRS to use DPE on reporting server after publishing. Both methods have different publishing paths and configuration files, but the processes are very similar. Here are the differences between the two ways:

1. Open the project "ProjectName" and rebuild it; 2. Copy the file "ProjectName.dll" and "projectname.pdb" Inside Folder "Projectname\projectname\bin\debug" to Path "C:\Progr Am Files (x86) \microsoft Visual Studio 14.0\common7\ide\privateassemblies\ "; 3. Copy file "Projectname.config" Inside project root directory to path "C:\Program Files (x86) \microsoft Visual Studio 14.0\ Common7\ide\privateassemblies\ "; 4. Open the file "RSReportDesigner.config" Inside Folder "C:\Program Files (x86) \microsoft Visual Studio 14.0\COMMON7\IDE\PR   Ivateassemblies\ "(the exactly path base on the Visio Studio version, installed in your local); 4.1. Find the node<Data>Inside node<Extensions>, add Node "<ExtensionName= "XXXX DataSource Extension"Type= "projectname.dbconnection, ProjectName" />"Inside node<Data>; 4.2 Find the node<Desinger>Inside node<Extensions>, add Node "<ExtensionName= "XXXX DataSource Extension"Type= "Microsoft.ReportingServices.QueryDesigners.GenericQueryDesigner, Microsoft.ReportingServices.QueryDesigners "/>"Inside node<Desinger>; 5. Open the file "Rspreviewpolicy.config" Inside Folder "C:\Program Files (x86) \microsoft Visual Studio 14.0\common7\ide\pri   Vateassemblies\ "(the exactly path base on the Visio Studio version, installed in your local); 5.1 Find the node<Data>Inside node<Extensions>, add node below content inside node<CodeGroup>; <CodeGroupclass= "Unioncodegroup"version= "1"PermissionSetName= "FullTrust"Name= "XXXX DataSource Extension"Description= "Code Group for my Custom DataSource for data processing extension">             <imembershipconditionclass= "UrlMembershipCondition"version= "1"URL= "C:\Program Files (x86) \microsoft Visual Studio 14.0\common7\ide\privateassemblies\projectname.dll"             />    </CodeGroup>6. Open your DataSource of SSRS report, select the Embedded connections and choice the DPE named "XXXX DataSource Extensio N "from the DropDownList of Type;7. Click OK button to use the DPE. Ps.1. Please close and open your Visio Studio again, if you can not find the DPE named "XXXX DataSource Extension" after Y Ou finished above steps; Using the DEP in Report Server:* ***************************************************** Reporting server 1. Open the project "ProjectName" and rebuild it; 2. Copy the file "ProjectName.dll" and "projectname.pdb" Inside Folder "Projectname\projectname\bin\debug" to Path "C:\Progr Am Files\Microsoft SQL Server\msrs12. Mssqlserver\reporting services\reportserver\bin\ "; 3. Copy file "Projectname.config" Inside project root directory to path "C:\Program Files\Microsoft SQL Server\msrs12. Mssqlserver\reporting services\reportserver\bin\ "; 4. Open the file "RSReportServer.config" Inside Folder "C:\ProgrAm Files\Microsoft SQL Server\msrs12. Mssqlserver\reporting Services\reportserver\ "(the exactly path base on the Visio Studio version, installed in your Loca   L);  4.1. Find the Node<Data>Inside node<Extensions>, add below content inside node<Data>; <ExtensionName= "Dpename"Type= "Projectname.dbconnection,projectname">            <Configuration>                <connectionStringsConfigSource= "\bin\projectname.config" />            </Configuration>        </Extension>4.2 Find the node<Desinger>Inside node<Extensions>, add below inside node<Desinger>; 5.  Open your DataSource of SSRS report in Reporting Server,then Open, through right click it and select "Manage"; 6. Click "Data Source" from the menu located left side of the new page;7.  Select "A Custom Data Source" in the right side of the page,followed by selecting DPE named "XXXX DataSource Extension" as The Data source type;8. Click Apply button to use the DPE. Ps.1. Restart the Reporting Services if you still can not find the DataSource manage page after you finished above steps;

View Code

6. Summary: After the use of the DPE, overall there is no difficulty, but in the process of using the best to download an example, and then on the basis of the modification, which will save a lot of time. It is also important to note that the version of the. Net framework that is being used has just begun because the version is incorrect, resulting in no custom DPE being found, and finally the reason was found by repeating the test itself! Here are a few links that Google has found that helped me a lot in my use of the process:

• Code Project Article-https://www.codeproject.com/articles/272652/ssrs-multi-data-source-dpe-data-processing-extensi
• Custom Data Sources in ssrs-http://discoveringdotnet.alexeyev.org/2013/08/custom-data-sources-in-ssrs.html
• MSDN article-https://msdn.microsoft.com/en-us/library/ms154027.aspx

SQL Server Reporting Services custom Data processing extension DPE (data processing Extension)