Compiled Transform in C#.NET 2005

In the .NET Framework 1.1, often we needed to make transformations of XML contained in a String data type variables was not possible. The things changed in the .NET Framework 2,0, thanks to the System.Xml.Xsl.XslCompiledTransform class of the following way:

private string Transform(String xslPath, String strXML)
{
System.Xml.Xsl.XslCompiledTransform xsl = new System.Xml.Xsl.XslCompiledTransform();
xsl.Load(xslPath);
System.IO.MemoryStream msIn = new System.IO.MemoryStream(System.Text.Encoding.UTF8.GetBytes(strXML));
System.IO.MemoryStream msOut = new System.IO.MemoryStream();
msIn.Position = 0;
System.Xml.XmlTextReader trInput = new System.Xml.XmlTextReader(msIn);
System.Xml.XmlWriter twOutput = System.Xml.XmlWriter.Create(msOut, xsl.OutputSettings);
xsl.Transform(trInput, twOutput);
trInput.Close();
msIn.Close();
msOut.Position = 0;
System.IO.StreamReader sr = new System.IO.StreamReader(msOut);
String xmlStr = String.Format("{0}", sr.ReadToEnd());
msOut.Close();
sr.Close();
return xmlStr;
}

The xslPath parameter is our XSL physical path. The strXML parameter is our XML contained in a String data type variable. In this practical example our “Transform” function will return a String data type with the result of this transformation.

String xmlTranformed = Transform(“xslEjemplo.xsl”,” ……”);

Executing SQL Server Data Transformation Service (DTS) from .NET

Frequently, Database administrators import, export and transform data like support for consolidation and data analysis tasks, fundamentally for the development of applications and updates of Databases or Servers.

Most of the organizations have different formats and locations for data storing and frequently it is precise to move the data from a location to another one. A SQL Server DTS provide the tools necessary to extract, to transform and to consolidate the different data of sources to unique or multiple destinies. These tools of DTS allow the creation of solutions for the movement of customized data that adjust to the special necessities of an organization.

The .NET Framework offers the possibility of Execute SQL Server DTS in a simple way. The only thing that is required is to make a reference to the DTS DLL and begin to codify a pair of lines.

References:

SQL Server 2005: C:\Program Files\Microsoft SQL Server\90\DTS\Binn\DTS.dll

SQL Server 2000: C:\Program Files\Microsoft SQL Server\80\Tools\Binn\DTSPkg.dll

Code:

VB.NET
Dim package As New DTS.Package2Class ‘(or DTS.Package)
package.LoadFromSQLServer(“ServerName”, “ServerUserName”, “ServerPassword”, , , , , “PackageName”)
package.Execute()

C#.NET
DTS.Package package = new DTS.Package();
package.LoadFromSQLServer(“ServerName”, “ServerUserName”, “ServerPassword”, , , , , “PackageName”);
package.Execute();

If you need to access to a SQl Server Global Variable you can do the following:

VB.NET
Dim MyGlobalVar As String = pkg.GlobalVariables.Item("MyGlobalVar").Value.ToString()

C#.NET
String MyGlobalVar = pkg.GlobalVariables.Item("MyGlobalVar").Value.ToString();

DotNet Puffs

Welcome to my English Blog, I will expose articles about my last experience in .NET, but with a special touch. Enjoy it!