Validating XML against an XSD, .net

Following code will validate an XML file agaisnt an XSD stored in a file system and stored all the errors encoutered


Public Function ValidatingProcess(ByVal XSDPath As String, ByVal XMLPath As String, ByVal ValidationErrorsSavePath As String)

Try

' 1- Read XML file content
Reader = New XmlTextReader(XMLPath)

' 2- Read Schema file content
SR = New StreamReader(XSDPath)

' 3- Create a new instance of XmlSchema object
Dim Schema As XmlSchema = New XmlSchema



' 4- Set XmlSchema object by calling XmlSchema.Read() method
' Schema = XmlSchema.Read(SR, new ValidationEventHandler(ValidatingReader_ValidationEventHandler));
Schema = XmlSchema.Read(SR, New ValidationEventHandler(AddressOf ValidatingReader_ValidationEventHandler))

'Addhandler SR,

'AddHandler Reader.ValidationEventHandler, AddressOf Me.ValidatingReader_ValidationEventHandler

' 5- Create a new instance of XmlValidationReader object
Dim ValidatingReader As XmlValidatingReader = New XmlValidatingReader(Reader)

' 6- Set ValidationType for XmlValidationReader object
ValidatingReader.ValidationType = ValidationType.Schema

' 7- Add Schema to XmlValidationReader Schemas collection
ValidatingReader.Schemas.Add(Schema)

' 8- Add your ValidationEventHandler address to
' XmlValidationReader's ValidationEventHandler
'ValidatingReader.ValidationEventHandler += New ValidationEventHandler(ValidatingReader_ValidationEventHandler)
AddHandler ValidatingReader.ValidationEventHandler, AddressOf ValidatingReader_ValidationEventHandler

test:

Try

' 9- Read XML content in a loop
While (ValidatingReader.Read())

End While
Catch

GoTo test
End Try

SaveErrorsToAFile(ValidationErrorsSavePath, Me.Results)


'Handle exceptions if you want
Catch AccessEx As UnauthorizedAccessException

Throw AccessEx

Catch Ex As Exception
Throw Ex
End Try

End Function


Private Sub ValidatingReader_ValidationEventHandler(ByVal sender As Object, ByVal args As System.Xml.Schema.ValidationEventArgs)

' 10- Implement your logic for each validation iteration
Dim strTemp As String
strTemp = "Line: " + Me.Reader.LineNumber.ToString() + " - Position: " + Me.Reader.LinePosition.ToString() + " - " + args.Message

Me.Results.Add(strTemp)
End Sub


Private Function SaveErrorsToAFile(ByVal filepath As String, ByVal errors As ArrayList) As Boolean

Dim blnSuccess As Boolean = False

Try

Dim tw As TextWriter = New StreamWriter(filepath, False)

'/ write a line of text to the file
Dim e As IEnumerator = errors.GetEnumerator()

While (e.MoveNext())
tw.WriteLine(e.Current.ToString())
End While
' close the stream
tw.Close()
blnSuccess = True

Catch
blnSuccess = False
End Try

Return blnSuccess
End Function



References

http://aspalliance.com/941

Exporting datatable to an Excel Sheet

Simple function to export the data in a Datatable to an excel sheet.

#region Custom References
using System.Xml;
using System.IO;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;
using Microsoft.Office.Interop.Excel;
using Excel = Microsoft.Office.Interop.Excel;
using System.Reflection;
#endregion


public override bool GenerateOutput(System.Data.DataTable sourceData, string outputfilepath)
{
bool blnSuccess = false;
try
{
Excel.Application xlApp;
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
object misValue = System.Reflection.Missing.Value;

xlApp = new Excel.ApplicationClass();
xlWorkBook = xlApp.Workbooks.Add(misValue);
xlApp.DisplayAlerts = false;
xlApp.Visible = false;

// delete the sheet if already exists
try
{
xlWorkSheet = (Worksheet)xlWorkBook.Sheets["test"];
if (xlWorkSheet != null)
{
xlWorkSheet.Delete();
}
}
catch
{

}

xlWorkSheet = (Worksheet)xlWorkBook.Worksheets.Add(Missing.Value, Missing.Value, Missing.Value, Missing.Value);
xlWorkSheet.Name = "test";

xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item("test");

for (int rowCount = 1; rowCount <= sourceData.Rows.Count; rowCount++)
{
for (int readerColIndex = 1; readerColIndex <= sourceData.Columns.Count; readerColIndex++)
{
xlWorkSheet.get_Range(xlWorkSheet.Cells[rowCount, readerColIndex], xlWorkSheet.Cells[rowCount, readerColIndex]).Value2 = sourceData.Rows[rowCount - 1].ItemArray[readerColIndex - 1].ToString();
}
}

xlWorkBook.SaveAs(outputfilepath, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);

xlWorkBook.Close(true, misValue, misValue);
xlApp.DisplayAlerts = true;
xlApp.Quit();

releaseObject(xlWorkSheet);
releaseObject(xlWorkBook);
releaseObject(xlApp);

blnSuccess = true;
}
catch (Exception ex)
{
blnSuccess = false;
throw new Exception(ex.ToString());
}

return blnSuccess;
}

public static void releaseObject(object obj)
{
try
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
obj = null;
}
catch (Exception ex)
{
obj = null;
//MessageBox.Show("Exception Occured while releasing object " + ex.ToString());
}
finally
{
GC.Collect();
}
}

How to convert a SqlDatareader to a Dataset

How to Copy a datareader to a dataset

private DataSet CopyReaderToDataSet(SqlDataReader reader)
{
DataSet dataSet = new DataSet();
System.Data.DataTable schemaTable = reader.GetSchemaTable();
System.Data.DataTable dataTable = new System.Data.DataTable();

for (int cntr = 0; cntr < schemaTable.Rows.Count; ++cntr)
{
DataRow dataRow = schemaTable.Rows[cntr];
string columnName = dataRow["ColumnName"].ToString();
Type colType = dataRow["ColumnName"].GetType();
DataColumn column = new DataColumn(columnName, colType);
dataTable.Columns.Add(column);
}

dataSet.Tables.Add(dataTable);

while (reader.Read())
{
DataRow dataRow = dataTable.NewRow();
for (int cntr = 0; cntr < reader.FieldCount; ++cntr)
{
dataRow[cntr] = reader.GetValue(cntr);
}
dataSet.Tables[dataTable.TableName.ToString()].Rows.Add(dataRow);
}

return dataSet;
}

Reference: Roni's blog, added couple of lines missing in his blog

How to update the custom Task property at runtime?

I am currently working on to find a way to update the properties of the custom task view at runtime. I have a custom task with a read-only property in the view. But that property will be chaning during the runtime by the Execute method of the task. I am unable to display the changed value during the runtime by the view. I need to update this value in the view to get it stored in the checkpointfile of the package so that the task can support the checkpointfile.

Creating Custom Tasks with SSIS

Recently i started working with SSIS. I had to develop a workflow that will allow me to copy different files/directories from various locations into a local folder. I used the FileCopy Task Provided in SSIS. But to copy from multiple sources to multiple/single target, i need to use multiple FileCopy tasks. So, i thought, i should probably write a simple code to do it and SSIS supports using couple of interfaces. All you need to know is about those interfaces and implement them.

I am going to use SQL Server 2008 and VS 2008 for this purpose. You need to have both software in the machine.

Source Code Overview – The source code holds two projects, one project with a single class that holds the actual functionality. Another project holds classes/forms to display the UI for the source code class. I am separating the UI code with the actual functionality for ease.

Source Code Project
• Open VS 2008, click New Project from and select "Class Library Project" under visual C# project type
• Type the Project Name as "CustomFileCopyTask"
• Uncheck the "Create Directory for Solution" option and save the project into the folder, SSISCustomTasks
• Rename the class1.cs under the project to "clsCustomFileCopyTask"
• Add a reference to an assembly called, Microsoft.SqlServer.ManagedDTS. This assembly is registered under GAC (C:\Windows\assembly\GAC_MSIL folder in Vista). So, use the browse tab of the references dialog to add a reference to this dll
• This class needs to be derived from an abstract class Task and IDTSComponentPersist interface.
• Define the properties Name, Description and FileSourceColl. Because this class is going to have multiple file sources, defining the filesources as a collection property. To hold these values, we are going to define a custom class called, FileSource
public class FileSource : Component
{
private String _source;
public String Source
{
get { return _source; }
set { _source = value; }
}

private string _target;
public string Target
{
get { return _target; }
set { _target = value; }
}

private string _desc;
public string Description
{
get { return _desc; }
set { _desc = value; }
}
}
• All custom classes that need to get display in the property grid of the task should derive from the component class
• Override the Validate and Execute methods of the Task class. Validate method is a design time property of the task to add any validations around the property values. Execute method is the runtime property of the class.
• In the validate method, I am validating for the source directory existence and the validity of the paths. I am taking the help of the private function called, IsValidPath. Check the source code attached
• In the Execute method, Copying the source directory to the target location for each source/destination pair defined in the FileSourceColl property. Again, I am taking help of a private recursive function to copy the contents
private bool CopyDirectory(string SourcePath, string DestinationPath, bool overwriteexisting)
{
bool ret = false;
try
{
SourcePath = SourcePath.EndsWith(@"\") ? SourcePath : SourcePath + @"\";
DestinationPath = DestinationPath.EndsWith(@"\") ? DestinationPath : DestinationPath + @"\";

if (Directory.Exists(SourcePath))
{
if (Directory.Exists(DestinationPath) == false)
{
Directory.CreateDirectory(DestinationPath);

}

foreach (string fls in Directory.GetFiles(SourcePath))
{
FileInfo flinfo = new FileInfo(fls);
flinfo.CopyTo(DestinationPath + flinfo.Name, overwriteexisting);
FileInfo flTargetFile = new FileInfo(DestinationPath + flinfo.Name);
flTargetFile.IsReadOnly = false;
}
foreach (string drs in Directory.GetDirectories(SourcePath))
{
DirectoryInfo drinfo = new DirectoryInfo(drs);
if (CopyDirectory(drs, DestinationPath + drinfo.Name, overwriteexisting) == false)
ret = false;
}
}
ret = true;
}
catch (Exception ex)
{
ret = false;
}
return ret;
}
• Implement the two methods, LoadXML and SaveXML methods of the IDTSComponentPersist interface. These two methods are useful for storing and retrieving the property values of this task into the package. We don’t need to implement this interface unless we are using any complex datatypes in the custom task. But we are using the FileSource class to hold the multiple values and that is the reason we are going to implement these methods
• Add a DTSTask attribute to the “clsCustomFileCopyTask” with 3 attributes called, DisplayName, Description and UITypeName (UITypename should be Namespace.UITaskname, Assemblyname)
UI code Project
This project is going to have 3 different objects. A single class file, a windows form and a user control.
Class File – Is used to instantiate the windows form to display the property page of the task
Windows File – This is the form used that displays the property sheet of the task
User control – Is the one that displays the filesource class with the properties added upon clicking the fileSourcesColl property in the windows form

• Add a New c# class library project to the same solution with the name as “CustomFileCopyTaskUI”
• Rename the class1.cs to “clsCustomFileCopyTaskUI.cs” and add a reference to the dll, Microsoft.SqlServer.Dts.Design and “Microsoft.SqlServer.ManagedDTS” dll in the GAC
• Add a using statement for referring to the assembly
• Derive the class from IDTSTaskUI interface of the DTs.Design.Runtime library and implement the Getview() and Initialize methods of the interface. Remove the not implemented exception statements from the remaining methods of the interface
• Add the source project as a reference to the UI project

• Add a windows form named, clsCustomFileCopyTaskForm to the project, derive it from the base class called DTSBaseTaskUI. This base class is used to display the default property pages provided by SSIS
• Add a reference, Microsoft.DataTransformationServices.Controls.dll from the GAC and the related using statements

• Add a new user control item to the project and name it as clsCustomFileCopyTaskView.cs
• Add the using statement for Microsoft.DataTransformationServices.Controls
• Derive the control from IDTSTaskUIView interface apart from the usercontrol
• Implement the OnCommit, OnInitialize and OnValidate methods of the interface
Building the Sample
• As mentioned earlier, all custom tasks need to be registered in GAC before use. Follow the steps provided below for GAC registration
o Open Visual Studio command prompt and navigate to the folder where the project is saved
o Type “sn –k CustomFileCopyTaskKey.snk” to generate a private key
o Extract the public key token from the private key generated using the command “sn –k CustomFileCopyTaskKey.snk CustomFileTaskPublicKey.snk”
o Display the public key token using the command “sn –t CustomFileCopyTaskPublicKey.snk”
o Copy the token to clipboard
o In the code, locate the DtsTask attribute. In that line of code, replace the existing value of the public key token in the* UITypeName* attribute property in the IncrementTask.cs. The alphanumeric value after "PublicKeyToken=" should be replaced with the one displayed in the previous step
o Add the private key both the projects
• Open the source project properties, click on the Build Events tab and add the following commands into the post Build event command line box
copy "$(TargetDir)" "C:\Program Files\Microsoft SQL Server\100\DTS\Tasks" /y
"C:\Program Files\Microsoft Visual Studio 8\SDK\v2.0\Bin\gacutil.exe" /if "$(TargetPath)"

Above commands, first copies the dll to the Tasks folder of the SSIS, which is mandatory the custom tasks to get displayed in SSIS. Second one registers the dll’s into the GAC (Notice the /if command, reinstalls the assembly even though it already exists in the assembly)
• Click on the “Signing” tab of the project properties and check the “Sign the assembly” checkbox. From the list of strong name key files, choose the file generated in earlier steps
• Follow the same project related steps for UI project also
• Build the solution and that’s it.
• Open a new BIDS (Business Intelligence Developement Studio) project in VS 2008 and the control to the toolbox.


Friends, I have referred to couple of sites and blogs for preparing this code sample. I am giving the references below but if I miss anything, please leave your comments. Also, provide me your feedback and comments on this article. I will definitely include them to update this article. I am sure there are lot of areas I left over and will try to improve in my next article.

Download the source code from here

References
• MSND Samples on Codeplex.com
• Developing Custom Task
• Data Persistency sample
• How to Edit and Persist collections with CollectionEditor