How to read & write value from Infopath Datasource throught C#

Hi,

In a requirement, we need to display views based on the current user who updates the infopath form. So I thought let me write the logic to read and store values in Info path Data source from code behind. Following Code useful.

Read:
XPathNavigator nav = MainDataSource.CreateNavigator();
string fieldValue = nav.SelectSingleNode("//my:Myfield1", NamespaceManager).Value;

Write:
XPathNavigator nav = MainDataSource.CreateNavigator();
nav.SelectSingleNode("//my:Myfield1", NamespaceManager).SetValue("NewValue");

essential Namespace is Microsoft.Office.InfoPath;

Hope it helps...

Best Regards
Fauzi ~4Z

Sample to read Excel file through C#

Hey,

Today there was a requirement to project a graph from a excel sheet provided by a client. So i wrote a sample code in C# to read the data from the excel sheet. Thought let me post a sample code which will be helpful for readers...

The Class used here is System.Data.OleDb.OleDbConnection

Code:

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.UI;

using System.Web.UI.WebControls;

public partial class ExcelFromC : System.Web.UI.Page

{

protected void Page_Load(object sender, EventArgs e)

{

string strConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:\Fauzi\Excelfile\Sample.xls;Extended Properties=Excel 5.0";

//Creating the OleDb Connection

System.Data.OleDb.OleDbConnection objOleDbConnection = new System.Data.OleDb.OleDbConnection(strConnectionString);

//creating query for Sheet#1

//Here A is the text in A[0][0] ~= A[Row #1][Column #1] So the first row is considered like a Column of a table

string strExcelQuery = "Select A,C from [Sheet1$]";

//strExcelQuery = "Select * from [Sheet1$]";

//create the OleDb Command

System.Data.OleDb.OleDbCommand objOleDbCommand = new System.Data.OleDb.OleDbCommand(strExcelQuery, objOleDbConnection);

objOleDbConnection.Open();

//Read Section:

//-------------

//Create OleDb DataReader

System.Data.OleDb.OleDbDataReader objOleDbDataReader = objOleDbCommand.ExecuteReader();

//Iterating for every records

Response.Write("Reading Data from Excel file");

Response.Write("</br>");

Response.Write(" A - B - C ");

while (objOleDbDataReader.Read())

{

Response.Write("</br>");

string strValue = "";

for (int i = 0; i < objOleDbDataReader.FieldCount; i++)

{

strValue = strValue + " - " + (objOleDbDataReader.GetValue(i)).ToString();

}

Response.Write(strValue);

}

objOleDbConnection.Close();

//Update Section:

//---------------

strExcelQuery = "Update [Sheet1$] set C = C+2"; // from Sheet1";

//Create the command to be executed

objOleDbCommand = new System.Data.OleDb.OleDbCommand(strExcelQuery, objOleDbConnection);

//Open the connection to the file

objOleDbConnection.Open();

//Execute update

objOleDbCommand.ExecuteNonQuery();

//Close connection

objOleDbConnection.Close();

Response.Write("</br>");

Response.Write("</br>");

Response.Write("Reading Data AFTER(C = C+2) Excel file was updated through code");

Response.Write("</br>");

Response.Write(" A - B - C ");

strExcelQuery = "Select A,C from [Sheet1$]";

//strExcelQuery = "Select * from [Sheet1$]";

//create the OleDb Command

objOleDbCommand = new System.Data.OleDb.OleDbCommand(strExcelQuery, objOleDbConnection);

objOleDbConnection.Open();

//Create OleDb DataReader

objOleDbDataReader = objOleDbCommand.ExecuteReader();

//Iterating for every records

while (objOleDbDataReader.Read())

{

Response.Write("</br>");

string strValue = "";

for (int i = 0; i < objOleDbDataReader.FieldCount; i++)

{

strValue = strValue + " - " + (objOleDbDataReader.GetValue(i)).ToString();

}

Response.Write(strValue);

}

objOleDbConnection.Close();

}

}

Excel File: http://www.checkthiz.com/publicfiles/sample.xls

Exception:While reading the excel file the whole sheet is considered as a table & the first row texts are considered to be the column names. So when you give a select (string strExcelQuery = "Select Col from [Sheet1$]";) query in run time the compiler will look for a column 'Col' in the first row and if the particular row is missing, the following exception will be raised.

Syntax error (missing operator) in query expression 'Col'
System.Data.OleDb.Exception -> System.Data.OleDb.OleDbErrorCollection
how to read excel file through C#
Hope it helps :)

Regards
Fauzi

Sample to Read & Write XML File through C#

Hi,

Here is the sample to read and write xml file through C# code. In order to explain the process in a simple fashion, I made use of a xml file called SomeName.xml (The contents of the xml file are shown below in Section #1). Through C# code i am reading the nodes and attribute values which any one would prefer to do in their application. Then to explain the process of writing a xml file into file system, I am making use of the values I read from input xml file. I create a XmlDocument Object in memory, create nodes and with the read values append them inside and write them to a new XML file in file system called WrittenFile.xml.

Section #1 has the content of input xml file, Section #2 contains the c# code to be placed in pageload event & section #3 has the content of generated output xml file.

Section #1:

Input XML file: ~\XML\SomeFolder\SomeName.xml

<?xml version="1.0" standalone="yes" ?>

<report>

<table>

<columns>

<column name="Attribute1" />

<column name="Attribute2" />

</columns>

<rows>

<row Attribute1="6693" Attribute2="655" />

<row Attribute1="432" Attribute2="364" />

<row Attribute1="34" Attribute2="766" />

<row Attribute1="665493" Attribute2="134" />

<row Attribute1="56783" Attribute2="656" />

</rows>

</table>

</report>

Section #2:

C# Code written in page load event:

protected void Page_Load(object sender, EventArgs e)

{

try

{

//Creating XmlDocument Class object:

XmlDocument oXmlDocument = new XmlDocument();

//Loading the XML file from File System:

oXmlDocument.Load(Server.MapPath("XML/SomeFolder/SomeName.xml"));

//1.To Read a Single Node

//Giving the Xpath of the Node: eg: //report/table/rows/row

//Assigning the returned XmlNoded from SelectSingleNode Method:

XmlNode oXmlElementCampaignName = oXmlDocument.DocumentElement.SelectSingleNode("//report/table/rows/row");

//2.To Read Collection of Nodes

//Giving the Xpath to method SelectNodes eg: SelectNodes("//report/table/rows/row");

//Assigning the returned XmlNoded from SelectSingleNode Method:

XmlNodeList oXmlNodeList = oXmlDocument.DocumentElement.SelectNodes("//report/table/rows/row");

// Create a XmlDocument object to write a file:

XmlDocument xmlDocument = new XmlDocument();

// Create a XmlElement which represents an Element : Root Element

XmlElement Items = xmlDocument.CreateElement("Items");

//Iterate through every nodes in oXmlNodeList which is a collection of Nodes

foreach (XmlNode objNode in oXmlNodeList)

{

// Create a XmlElement which represents an Element : Node

XmlElement Item = xmlDocument.CreateElement("Item");

// Create Child Node XmlElement1

XmlElement xmlelement1 = xmlDocument.CreateElement("XmlElement1");

// Sets value for Child Node

XmlText xmlelement1_text = xmlDocument.CreateTextNode(objNode.Attributes["Attribute1"].

Value.ToString());

// Placed inside Child Node

xmlelement1.AppendChild(xmlelement1_text);

XmlElement xmlelement2 = xmlDocument.CreateElement("XmlElement2");

XmlText xmlelement2_text = xmlDocument.CreateTextNode(objNode.Attributes["Attribute2"].

Value.ToString());

xmlelement2.AppendChild(xmlelement2_text);

// Placing under Node

Item.AppendChild(xmlelement1);

// Placing under Node

Item.AppendChild(xmlelement2);

// Placing under Root

Items.AppendChild(Item);

}

// Placing under XML document

xmlDocument.AppendChild(Items);

// Writing the XML file into file system

xmlDocument.Save(Server.MapPath("XML/SomeFolder/WrittenFile.xml"));

}

catch (Exception ex)

{

//Exception block here

}

}

Section #3:

Output XML file: ~\XML\SomeFolder\WrittenFile.xml

<Items>

<Item>

<XmlElement1>6693</XmlElement1>

<XmlElement2>655</XmlElement2>

</Item>

<Item>

<XmlElement1>432</XmlElement1>

<XmlElement2>364</XmlElement2>

</Item>

<Item>

<XmlElement1>34</XmlElement1>

<XmlElement2>766</XmlElement2>

</Item>

<Item>

<XmlElement1>665493</XmlElement1>

<XmlElement2>134</XmlElement2>

</Item>

<Item>

<XmlElement1>56783</XmlElement1>

<XmlElement2>656</XmlElement2>

</Item>

</Items>

Hope this should be helpful for people who are looking to samples to read or write xml file.

Regards
Mohammed Fauzi