How To Pass A Data Collection In To A Stored Procedure

For an example let’s say we need to pass a students marks list in to a stored procedure.
One technique is to create an xml document out of data list and pass it in to stored procedure. With in the stored procedure we can do retrieve the table back

First I m preparing a data table, having separate recode for each student. and pass it to my method which retune the xml document.

xmlHelper c = new xmlHelper();

DataTable dt = new DataTable();

dt.Columns.Add("StudendID");

dt.Columns.Add("Name");

dt.Columns.Add("Math");

dt.Columns.Add("Science");

dt.Columns.Add("Engilsh");

DataRow dr = dt.NewRow();

dr["StudendID"] = "100";

dr["Name"] = "Annna";

dr["Math"] = "10";

dr["Science"] = "50";

dr["Engilsh"] = "45";

dt.Rows.Add(dr);

DataRow dr1 = dt.NewRow();

dr1["StudendID"] = "101";

dr1["Name"] = "Ronny";

dr1["Math"] = "70";

dr1["Science"] = "60";

dr1["Engilsh"] = "50";

dt.Rows.Add(dr1);

XmlDocument studentList= c.setXmlDoc(dt);

The data table to xml convertor methord is as follows.

public XmlDocument setXmlDoc(DataTable dtStudentMarkList)

{

XmlDocument xmlDoc = new XmlDocument();

string filename = "outputXML.xml";

XmlTextWriter xmlWriter = new XmlTextWriter(filename, System.Text.Encoding.UTF8);

xmlWriter.WriteStartElement("StudentmarkLsit");

xmlWriter.Close();

xmlDoc.Load(filename);

XmlNode root = xmlDoc.DocumentElement;

DataColumnCollection dc = dtStudentMarkList.Columns;

string[] arrr = new string[dtStudentMarkList.Columns.Count];

int a = 0;

foreach (DataColumn col in dc)

{

arrr[a++] = col.ColumnName;

}

foreach (DataRow drStudent in dtStudentMarkList.Rows)

{

XmlElement childNode = xmlDoc.CreateElement("Student");

for (int b = 0; b <>

{

XmlElement StudnetNode = xmlDoc.CreateElement(arrr[b].ToString());

XmlText textSubject = xmlDoc.CreateTextNode(drStudent[arrr[b].ToString()].ToString());

StudnetNode.AppendChild(textSubject);

childNode.AppendChild(StudnetNode);

root.AppendChild(childNode);

}

}

return xmlDoc;

}

Now you have the list of student marks in xml format. You can pass it as a string into a stored procedure and retrieve the data table back.

CREATE PROCEDURE [passingXml]

(

@studentmarkList VARCHAR(MAX),

)

AS

BEGIN

declare @xmlDoc as xml

set @xmlDoc =convert(xml,@ studentmarkList)

DECLARE @StudentMarks TABLE

(

[StudendID] varchar(max),

[Name] varchar(max),

[Math] varchar(max),

[Science] varchar(max) ,

[Engilsh] varchar(max)

)

INSERT INTO @ StudentMarks

([StudendID],[Name], [Math], [Science], [Engilsh])

SELECT

StudentTable. StudentTableColumn.value('./StudendID[1]','varchar(max)'),

StudentTable. StudentTableColumn.value('./Name[1]','varchar(max)') ,

StudentTable. StudentTableColumn.value('./ Science [1]','varchar(max)') ,

StudentTable. StudentTableColumn.value('./ Engilsh [1]','varchar(max)') ,

FROM

@xmlDoc.nodes('/StudentmarkLsit/Student') AS TemplateTable(TemplateColumn)

/*-----------------WHAT EVER THE LOGIC------------------------------------*/

END

Reading Characters from an Image

One of my friend asked me to find a way of reading characters from an image. My googling ended up with very expensive software’s and bulky codes. Ultimately I came across with amazing OCR component available in Microsoft office package.

Microsoft Office Document Imaging 2003 (MODI) adds programmability features to the document scanning and viewing tools that Microsoft Office 2002 (XP) included for the first time. Programmers can take advantage of a simple object model built around the Document and Image (page) objects to display and read a scanned document as easily as a paper document, perform optical character recognition (OCR), search for text within scanned documents, copy and export scanned text and images, combine multiple pages into a single compressed file, and reorganize scanned document pages as easily as rearranging papers in a folder.

Reference

• http://www.microsoft.com/downloads/details.aspx?FamilyId=8F93E445-B1CF-4477-A373-E17417D616BC&displaylang=en
• http://msdn.microsoft.com/en-us/library/aa167607(office.11).aspxhttp:/msdn.microsoft.com/en-us/library/aa167607(office.11).aspx

How To Code

1. First you have to add MODI reference to .Net application.

2. Add flowing name space using MODI

3. codes for application as follows


// browse the new iamge
MODI.Document doc = new Document();
OpenFileDialog imageDialog = new OpenFileDialog();
imageDialog.Filter= "*.jpeg|*.jpg|*.gif|*.bmp";
imageDialog.ShowDialog();
string filename = openFileDialog1.FileName;

doc.Create(filename);
doc.OCR(MODI.MiLANGUAGES.miLANG_ENGLISH, false, false);

MODI.Image im = (MODI.Image)doc.Images[0];
MODI.Layout imagelayout = im.Layout;

string strResult = string.Empty;
//read each word by word
for (int i = 0; i < imagelayout.Words.Count; i++)
{
MODI.Word w = (MODI.Word)imagelayout.Words[i];
strResult += strResult + w.Text;
}
doc.Close(false);
//show the result by a message box.
MessageBox.Show(strResult);

Filter a Data Table

While programming, some times we need to get couple of rows by filtering of a data table.Best option is DataTable.Select Method available in .NET frame work 2.0 or above.

DataTable.Select Method (filter expression )

All you have to do is pass the filter criteria in to the select method then it returns the set of matching data rows. You can use all most every SQL key word to build you filter condition.

E g:

//populate the data table.

DataTable dt = new DataTable();

dt.Columns.Add("ID", typeof(int));

dt.Columns.Add("Name", typeof(string));

dt.Columns.Add("Age", typeof(float));

dt.Columns.Add("Gender of person", typeof(string));

DataTable dtresult = new DataTable();

dtresult = dt.Clone();

for (int i = 0; i <100;i++)

{

DataRow dr = dt.NewRow();

dr["ID"] = i;

dr["Name"] = "Mary" + i.ToString();

dr["Age"] = i;

if (i % 2 == 1)

{

dr["Gender of person"] = "f";

}

else

{

dr["Gender of person"] = "m";

}

dt.Rows.Add(dr);

}

//Retun all the ladies over 50 old.

string strFilerCondition =" [Gender of person]= 'f' AND age>50";

DataRow[] DR = dt.Select(strFilerCondition);

foreach (DataRow dr in DR)

{

dtresult.ImportRow(dr);

}

With in filter condition you can see, I am using square brackets, since the name of the third column “Gender of person” is not a single ward. Otherwise it courses an error.