LINQ to Untyped DataSet Samples

SAMPLE:1

VB.NET

Dim query = From em In ds.Tables("Employees").AsEnumerable() _
            Where em.Field(Of String)("FirstName") = "Bijo" _

            Select em

'To display the results
   For Each em In query
       Console.WriteLine("Name = {1} {2}", _
                em.Field(Of String)("FirstName"), _
                em.Field(Of String)("LastName"))

        Next


C#

var query = from em in ds.Tables("Employees").AsEnumerable() _
            where em.Field<string>("FirstName") == "Bijo"

            select em.Field<string>(“FirstName”);



C# METHOD-2

var query = ds.Tables("Employees").AsEnumerable()
    .Where(dr => dr.Field<string>("FirstName") == "Bijo")

    .Select(dr => dr.Field<string>("FirstName"));

READING XML DOCUMENTS USING XDOCUMENT

READING XML DOCUMENTS USING XDOCUMENT

<employees>
    <department name="HR" >
        <employee ID="1" Name="Alex" passcode="100"/>
        <employee ID="2" Name="Anil" passcode="1545"/>
        <employee ID="3" Name="Vinod" passcode="856"/>
        <employee ID="4" Name="Sibi" passcode="1254"/>
    </department>
    <department name="Sales" >
        <employee ID="1" Name="Jeen" passcode="700"/>
        <employee ID="2" Name="Anil" passcode="545"/>
        <employee ID="3" Name="Kichu" passcode="86"/>
    </department>
</employees>


TO READ THE ABOVE XML USING XDOCUMENT OBJECT

Dim xdoc As XDocument = XDocument.Parse(<<xml string>>)
Dim deptList = From dl In xdoc.Descendants("department") Select dl

For Each dl In deptList
    '//PROCESS EACH DEPT.
    'ie dl="<department name="HR">
               <employee ID="1" Name="Alex" passcode="100"/>
               <employee ID="2" Name="Anil" passcode="1545"/>
               <employee ID="3" Name="Vinod" passcode="856" />
               <employee ID="4" Name="Sibi" passcode="1254" /> 
            </department>"

     'to read attributes use dl.Attribute("name").Value
 
     For Each em In dl.Elements("employee")
         '//PROCESS AN EMPLOYEE
         'ie em="<employee ID="1" Name="Alex" passcode="100" />"
     Next

Next

READING XML DOCUMENTS

READING XML DOCUMENTS

EXAMPLE-1

<?xml version="1.0" encoding="utf-16" standalone="yes"?>
     <generalsettings>
          <billheader>
                <title>Title Name</title>
                <phone> 0+91 xxxx xxxx</phone>
                <email> xxxxxx@xyz.com</email>
                <web address="hotelxyz.com"/>
          </billheader>
    </generalsettings>


To load the xml into an xml document object:

  Dim xmldoc As New XmlDocument
  xmldoc.LoadXml(<<xmlString>>)

To select a specific node from xml document:
     Dim titleNode As XmlNode = xmldoc.SelectSingleNode("/generalsettings/billheader/title")
     Dim titleText =   titleNode.InnerText

To read attribute values:
     Dim webNode As XmlNode = xmldoc.SelectSingleNode("/generalsettings/billheader/web ")
     Dim webAddress =   webNode.Attributes("address").Value



EXAMPLE-2

<?xml version="1.0" encoding="utf-16" standalone="yes"?>
<waiterList>
    <waiter ID="1" Name="Alex" passcode="100"/>
    <waiter ID="2" Name="Anil" passcode="1545"/>
    <waiter ID="3" Name="Vinod" passcode="856"/>
    <waiter ID="4" Name="Sibi" passcode="1254"/>
</waiterList>

To Read All Waiter Elements:

nodelist = doc.GetElementsByTagName("waiter")
For Each node As XmlElement In nodelist
   //process nodes//
Next

PAGING SQLSELECT QUERY

IMPLEMENT PAGING IN SQL SELECT QUERY

SAMPLE QUERY-1

SELECT x.*
  FROM (SELECT c.id,
               c.ChannelName,
               c.ChannelAddress,
               ROW_NUMBER() OVER (ORDER BY c.id) AS rank
          FROM CHANNELS c) x
 WHERE x.rank BETWEEN 100 AND 200

SAMPLE QUERY-2

With ChannelEntities As
(
    Select ROW_NUMBER() Over (Order By id) As Row,
        id
    From CHANNELS
)
Select C1.Id,
C1.ChannelAddress,
C1.ChannelName
From ChannelEntities c
Inner Join CHANNELS c1 on c.Id = c1.Id
Where c.Row Between 100 AND 200
Order By c.Row Asc

Common Table Expressions(CTE)

A common table expression (CTE) is a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement.

Example:
Normal Query

SELECT * FROM  (
        SELECT A.Address, E.Name, E.Age From Address A
        Inner join Employee E on E.EID = A.EID) T
WHERE T.Age > 50
ORDER BY T.NAME

Rewriting the query using CTE expression

With T(Address, Name, Age)  --Column names for Temporary table
AS
(
SELECT A.Address, E.Name, E.Age from Address A
INNER JOIN EMP E ON E.EID = A.EID
)
SELECT * FROM T  --SELECT or USE CTE temporary Table
WHERE T.Age > 50
ORDER BY T.NAME

Ref:

http://www.codeproject.com/Articles/265371/Common-Table-Expressions-CTE-in-SQL-SERVER-2008

http://technet.microsoft.com/en-us/library/ms190766%28v=sql.105%29.aspx

 

How to convert an excel serial date to a .NET date time?

How to convert an excel serial date to a .NET date time?
[ For eg: '29815' to '17/Aug/1981' ]

Use :

DateTime dt = DateTime.FromOADate(29815);

Upload a file using Web Service

Upload a file using byte array :

Web service method:


  [WebMethod]
        public string UploadFile(byte[] f, string fileName)
        {
            // the byte array argument contains the content of the file and 
            //the string argument contains the name and extension
            try
            {
                MemoryStream ms = new MemoryStream(f);

                //open a file stream pointing the storage folder 'uFiles'

                FileStream fs = new FileStream(System.Web.Hosting.HostingEnvironment.MapPath
                            ("~/uFiles/") + fileName, FileMode.Create);

                // write the memory stream  to the filestream
                ms.WriteTo(fs);
               
                ms.Close();
                fs.Close();
                fs.Dispose();
                return "OK";
            }

            catch (Exception ex)
            {
                     return ex.Message.ToString();
            }
        }


Sample win application code which consumes the method.


 FileInfo fInfo = new FileInfo(<<path & filename>>);

 FileStream fStream = new FileStream(<<path & filename>>,FileMode.Open, FileAccess.Read);

 BinaryReader br = new BinaryReader(fStream);
                 
 // convert the file to a byte array
 byte[] data = br.ReadBytes((int) fInfo.Length);
 br.Close();

localhost.Service1 s = new localhost.Service1();

s.UploadFile(data, <<filename>>);

 fStream.Close();

fStream.Dispose();