Learn C# ASP.NET MVC WCF SQL Angular: Validate an XML file against a XSD in ASP. net

In this article I am going to explain real time scenario to validate XML file according to predefined XSD file in Asp. Net . There are several way is to validate XML file in ASP. Net, but here I am using XmlReaderSettings and XmlReader class to achieve.

Scenario

I am Working on Project Where I have to Import some data form excel file data into SQL Server database with some validation before Importation. Do the some simple step to achieve this.

Step 1

Firstly we import EXCEL data into DataSet and then convert it into XML using ODBC connection in asp. net. I have taken three ASP.NET control FileUpload, Button and Label in .aspx page.

<asp:FileUpload ID="flbupload" runat="server" />
<asp:Button ID="btnupload" runat="server" onclick="btnupload_Click" Text="Upload" />

<asp:Label ID ="lblMsg" runat ="server" ></asp:Label>

Step 2

After completing Step 1 ,now Create a Excel file , according to your need first row of excel contain column name. I have created this

Description of Excel file column validation

Name : It only allow alphabet with space.

Phone : It must be 10 digit long and numeric.

Email : Must be valid email id

Age : Must be numeric and between 0 to 90

DOB : Must be in dd/mm/yyyy format

Gender : Only Male or Female allow.

Note : All fields are optional Except Name

Step 3

Now write code on .cs page for Import Excel data into dataset and convert into XML.

using System.Xml;

using System.Xml.Schema;

using System.IO;

using System.Configuration;

using System.Data.OleDb;

using System.Data;

private string UploadFile()

{

string strcon = string.Empty;

string xmlstring = string.Empty;

if (flbupload.HasFile)

{

string exten = Path.GetExtension(flbupload.FileName);

if (exten.ToLower() == ".xls") //Excel 97-03

{

strcon = ConfigurationManager.ConnectionStrings["Excel2003"].ConnectionString;

}

else if (exten.ToLower() == ".xlsx") //Excel 07

{

strcon = ConfigurationManager.ConnectionStrings["Excel2007"].ConnectionString;

}

else

{

lblMsg.Text = "Only .xls or .xlsx file allow ";

return "";

}

string uploaddir = ConfigurationManager.AppSettings["Upload"];

string physicalDirPath = Server.MapPath("~/" + uploaddir + "/");

if (!Directory.Exists(physicalDirPath))

{

Directory.CreateDirectory(physicalDirPath);

}

string filename = physicalDirPath + Path.GetFileNameWithoutExtension(flbupload.FileName) + Guid.NewGuid() + Path.GetExtension(flbupload.FileName);

flbupload.SaveAs(filename);

using (OleDbConnection con = new OleDbConnection())

{

con.ConnectionString = string.Format(strcon, filename, true);

if (con.State == ConnectionState.Closed)

con.Open();

//Get the name of First Sheet

DataTable dtExcelSchema;

dtExcelSchema = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

string SheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();

OleDbCommand cmd = new OleDbCommand("SELECT * From [" + SheetName + "]", con);

OleDbDataAdapter da = new OleDbDataAdapter(cmd);

DataSet ds = new DataSet();

da.Fill(ds);

xmlstring = Getxml(ds);

}

else

{

lblMsg.Text = "Please Choose .xls or .xlsx file";

return "";

}

return xmlstring;

}

This Method convert dataset table row into XML

private string Getxml(DataSet ds)

{

DataTable dtclone = ds.Tables[0].Clone();

foreach (DataColumn column in dtclone.Columns)

{

column.DataType = typeof(string);

}

foreach (DataRow row in ds.Tables[0].Rows)

{

dtclone.ImportRow(row);

}

foreach (DataRow row in dtclone.Rows)

{

for (int i = 0; i < dtclone.Columns.Count; i++)

{

dtclone.Columns[i].ReadOnly = false;

if (string.IsNullOrEmpty(row[i].ToString()))

{

row[i] = string.Empty;

}

else

{

row[i] = Convert.ToString(row[i]).Trim();

}

DataSet dsclone = new DataSet();

dsclone.Tables.Add(dtclone);

dsclone.DataSetName = "Persons";

dsclone.Tables[0].TableName = "Person";

return dsclone.GetXml();

}

Note: I have defined connection string into web.config file for both .xls and .xlsx and folder name

</connectionStrings>

</appSettings>

Step 4

I have Imported excel file data into dataset and converted into XML Now need to create XSD file based on your xml file do this by using some simple step.

First of all create a XML file in your project Right Click on your Project Name =>Add New Item => Choose XML file into Installed Templates

Here I have created XML file only for create XSD file otherwise it has no used because my XML file content created dynamically from excel file. write your dynamically generated XML content into xml file and then create XSD by using

Suppose my XML file have content

<?xml version="1.0" encoding="utf-8" ?>

<Name>Surya Prakash</Name>

<Address>Malkajgiri</Address>

<Email>suryaprakasash@gmail.com</Email>

</Person>

<Name>Satya Prakash</Name>

<Address>kljkljkjklj</Address>

<Email>satya@gmail.com</Email>

<DOB />

</Person>

</Persons>

Create XSD file Go to Toolbar =>XML =>Create Schema

Now your XSD file create need to modify it according to your need to validate XML. My XSD file is

<?xml version="1.0" encoding="utf-8"?>

<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">

<xs:element name="Persons">

<xs:complexType>

<xs:sequence>

<xs:element maxOccurs="unbounded" name="Person">

<xs:complexType>

<xs:sequence>

<xs:element name="Name" type="validateName" minOccurs ="0" default="s" nillable ="true"/>

<xs:element name="Address" type="xs:string" />

<xs:element name="Phone" type="Phonevalidate" />

<xs:element name="Email" type="ValidateEmail" minOccurs="0" maxOccurs="unbounded" default="abc@gmail.com" />

<xs:element name="Age" type="Validateage" minOccurs="0" default="0" />

<xs:element name="DOB" type="validateDob" minOccurs="0" nillable ="true" default="01/01/1900"/>

<xs:element name="Gender" type="validategender" default="Male" />

</xs:sequence>

</xs:complexType>

</xs:element>

</xs:sequence>

</xs:complexType>

</xs:element>

<xs:simpleType name="ValidateEmail" >

<xs:restriction base="xs:string">

<xs:pattern value="\w+([-+.']\w+)*@\w+([-.]\w+)*\.\w+([-.]\w+)*" />

</xs:restriction>

</xs:simpleType>

<xs:simpleType name="Validateage">

<xs:restriction base ="xs:integer">

<xs:minInclusive value="0"></xs:minInclusive>

<xs:maxInclusive value ="90"></xs:maxInclusive>

</xs:restriction>

</xs:simpleType>

<xs:simpleType name="Phonevalidate">

<xs:restriction base="xs:string">

<xs:length value="10"/>

<xs:pattern value="^[0-9]{10}$"/>

</xs:restriction>

</xs:simpleType>

<xs:simpleType name="validateName">

<xs:restriction base="xs:string">

<xs:minLength value ="0"></xs:minLength>

<xs:maxLength value ="50"></xs:maxLength>

<xs:pattern value="^[a-zA-Z]+(\s[a-zA-Z]+)?$"/>

</xs:restriction>

</xs:simpleType>

<xs:simpleType name="validateDob">

<xs:restriction base="xs:string">

<xs:pattern value="(((0|1)[1-9]|2[1-9]|3[0-1])\/(0[1-9]|1[0-2])\/((19|20)\d\d))$"/>

</xs:restriction>

</xs:simpleType>

<xs:simpleType name ="validategender">

<xs:restriction base ="xs:string">

<xs:enumeration value="male"/>

<xs:enumeration value="female"/>

<xs:enumeration value="MALE"/>

<xs:enumeration value="FEMALE"/>

<xs:enumeration value="Male"/>

<xs:enumeration value="Female"/>

</xs:restriction>

</xs:simpleType>

</xs:schema>

Step 4

Till Now I have create XML and XSD file. Now need to write C# code to validate xml file in .cs page

private bool Validatexmlfile(string xsdfilepath, string xmlfile)

{

try

{

XmlReaderSettings settings = new XmlReaderSettings();

settings.Schemas.Add(null, xsdfilepath);

settings.ValidationType = ValidationType.Schema;

settings.ValidationEventHandler += new System.Xml.Schema.ValidationEventHandler(ValidationEventHandler);

XmlReader reader = XmlReader.Create(new StringReader(xmlfile), settings);

XmlDocument document = new XmlDocument();

document.Load(reader);

}

catch (Exception ex)

{

lblMsg.Text += ex.Message;

}

if (nErrors > 0)

{

return false;

}

else

{

return true;

}

Note: nErrors is page label variable

private int nErrors = 0;

XMLReaderSettings Handler

void ValidationEventHandler(object sender, ValidationEventArgs e)

{

int xmllineno = e.Exception.LineNumber;

int linenoques = xmllineno / 8;

int linenoremainder = xmllineno % 8;

int rownoofexcel = 0;

nErrors++;

if (linenoremainder > 0)

{

linenoques += 1;

}

rownoofexcel = linenoques;

switch (e.Severity)

{

case XmlSeverityType.Error:

lblMsg.Text += String.Format("Line: {0}, Error :{1} </br>", rownoofexcel, e.Exception.Message);

break;

case XmlSeverityType.Warning:

lblMsg.Text += String.Format("Line: {0}, Warning :{1} </br>", rownoofexcel, e.Exception.Message);

break;

}

Now need to call validate validate function on button click event.

protected void btnupload_Click(object sender, EventArgs e)

{

string xml = UploadFile();

if (xml != "")

{

lblMsg.Text = string.Empty;

string xsdfilepath = Server.MapPath("~/MessageNew.xsd");

if (Validatexmlfile(xsdfilepath, xml))

{

lblMsg.Text = "Success";

}

Author- Satya Prakesh

Learn C# ASP.NET MVC WCF SQL Angular

Thursday, December 17, 2015

Validate an XML file against a XSD in ASP. net

1 comment: