LINQ: Query to SQL, XML and Object and JOIN them
Getting data from SQL, XML and Object and joining them using Language Integrated Query is something you might be looking for. I have created a small application which brings data from SQL Server (table name is Employee), XML (file name is Department.xml) and Object (Generic list containing list of Managers).
My SQL Table Looks like
EmpId int
EmpName varchar(50)
DeptId int
ManagerId int
Now the DeptID is linked to an XML file (where the department name is assigned against DeptId is that file)
<?xml version="1.0" encoding="utf-8" ?> <Departments> <Department id="1" name="Software"></Department> <Department id="2" name="Service"></Department> <Department id="3" name="Support"></Department> </Departments> |
And to get the XML data from the file located at C:\XMLData you need
public static List<Departments> GetDepartments() { //Loading the XML file and storing it in List of type Departments var query = from e in XElement.Load(@"C:\XMLData\Department.xml").Elements("Department") select new Departments { DeptID = (int)e.Attribute("id"), DeptName = (string)e.Attribute("name") }; return query.ToList(); }
|
Manager id is also like that and it has details in Generic List.
List<Managers> mgrs = new List<Managers> { new Managers{ManagerId = 1, ManagerName = "Manager 1"}, new Managers{ManagerId = 2, ManagerName = "Manager 2"} }; |
Now for each of the three entities I have object representation in my code,
/*
Class for the XML data coming from physical file
Using the Automatic feature
*/
public class Departments
{
public int DeptID{get;set;}
public string DeptName{get;set;}
}
/*
Class for the Linq to Sql
Table is in SQL Express 2005 Database "Northwind"
Attributes are coming from the namespace "System.Data.Linq.Mapping"
*/
[Table(Name="Employee")]
public class Employees
{
[Column]
public int EmpId { get; set; }
[Column]
public string EmpName { get; set; }
[Column]
public int DeptId { get; set; }
[Column]
public int ManagerId { get; set; }
}
/*
This class is for the pure C# List<Managers>
*/
public class Managers
{
public int ManagerId { get; set; }
public string ManagerName { get; set; }
}
Now you have to join them to get the linked data. Linq comes into the picture to help you out in this scenario.
static void Main(string[] args)
{
//Connection string for the Linq to Sql
string sConn =
@"Data Source=.\SQLEXPRESS;Initial Catalog=Northwind;
Integrated Security=True";
//Initializing the connection
DataContext db = new DataContext(sConn);
var query =
from emp in db.GetTable<Employees>().ToList()
join dep in GetDepartments() on emp.DeptId equals dep.DeptID
join mgr in GetManagers() on emp.ManagerId equals mgr.ManagerId
select new
{
EmpName = emp.EmpName,
DeptName = dep.DeptName,
ManagerName = mgr.ManagerName
};
foreach (var res in query)
{
Console.WriteLine("Name={0}, Dept={1}, Manager={2}"
,res.EmpName, res.DeptName, res.ManagerName);
}
}
I have attached full source code as attached.
Namoskar!!!