Linq adalah merupakan bagian dari satu kesatuan bahasa dan fitur framework dalam
menuliskan struktur query code yang aman dari local object collections dan remote data
sources. LINQ di perkenalkan oleh Microsoft pada C# Net Framework 3.0 dan C# .Net
Framework 3.5.
Linq dapat melakukan retreieve data dari berbagai macam source data diantaranya :
a. Linq to Object adalah Query Linq yang mengimplementasi resource data berasal dari
sebuah object yang menggunakan konsep Collection (IEnumarable, IEnumarable
<T>). Dibawah ini Contoh Linq to Object :
using System;
using System.Collections;
using System.Collections.Generic;
using System.Text;
using System.Linq;
using System.Reflection;
class Salary
{
int _id;
int _year;
double _salary;
public int ID
{
get { return _id; }
set { _id = value; }
}
public int Year
{
get { return _year; }
set { _year = value; }
}
public double SalaryPaid
{
get { return _salary; }
set { _salary = value; }
}
}
class Employee
{
int _id;
int _idRole;
string _lastName;
string _firstName;
public int ID
{
get { return _id; }
set { _id = value; }
}
public int IDRole
{
get { return _idRole; }
set { _idRole = value; }
}
public string LastName
{
get { return _lastName; }
set { _lastName = value; }
}
public string FirstName
{
get { return _firstName; }
set { _firstName = value; }
}
}
class Role {
int _id;
string role;
public int ID {
get { return _id; }
set { _id = value; }
}
public string Role {
get { return role; }
set { role = value; }
}
}
public class MainClass {
public static void Main()
{
//Lengkapi function di kertas jawaban.//
var query = from p in people
join s in salaries on p.ID equals s.ID
select new { p.FirstName, p.LastName,
s.SalaryPaid };
}
}
Linq to SQL adalah Query Linq yang mengimplementasi resource dari SQL Server
kemudian di jadikan sebuah Class. Namun Struktur Database yang ada haruse
menggunakan konsep ORM (Object Relationational Model). Di bawah ini contoh
LINQ to SQL:
// Northwnd inherits from System.Data.Linq.DataContext.
Northwnd nw = new Northwnd(@”northwnd.mdf”);
// or, if you are not using SQL Server Express
// Northwnd nw = new
Northwnd(“Database=Northwind;Server=server_name;Integrated
Security=SSPI”);
var companyNameQuery =
from cust in nw.Customers
where cust.City == “London”
select cust.CompanyName;
foreach (var customer in companyNameQuery)
{
Console.WriteLine(customer);
}
c. Linq to XML adalah Query Linq yang di implementasikan kedalam XML Code yang
sering digunakan sebagai template report yang mudah di pahami. Dibawah ini adalah
contoh Linq to XML.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Xml.Linq;
/// <summary>
/// Get the Starbuzz customer data as an XDocument
/// </summary>
/// <returns>XDocument with the Starbuzz data</returns>
static XDocument GetStarbuzzData()
{
/*
* You can use an XDocument to create an XML file, and that
includes XML
* files you can read and write using
DataContractSerializer.
* An XMLDocument object represents an XML document. It’s
part of the System.Xml.Linq namespace.
* Use XElement objects to create elements under the XML
tree.
*/
XDocument doc = new XDocument(
new XDeclaration(“1.0”, “utf-8”, “yes”),
new XComment(“Starbuzz Customer Loyalty Data”),
new XElement(“starbuzzData”,
new XAttribute(“storeName”, “Park Slope”),
new XAttribute(“location”, “Brooklyn, NY”),
new XElement(“person”,
new XElement(“personalInfo”,
new XElement(“name”, “Janet Venutian”),
new XElement(“zip”, 11215)),
new XElement(“favoriteDrink”, “Choco Macchiato”),
new XElement(“moneySpent”, 255),
new XElement(“visits”, 50)),
new XElement(“person”,
new XElement(“personalInfo”,
new XElement(“name”, “Liz Nelson”),
new XElement(“zip”, 11238)),
new XElement(“favoriteDrink”, “Double Cappuccino”),
new XElement(“moneySpent”, 150),
new XElement(“visits”, 35)),
new XElement(“person”,
new XElement(“personalInfo”,
new XElement(“name”, “Matt Franks”),
new XElement(“zip”, 11217)),
new XElement(“favoriteDrink”, “Zesty Lemon Chai”),
new XElement(“moneySpent”, 75),
new XElement(“visits”, 15)),
new XElement(“person”,
new XElement(“personalInfo”,
new XElement(“name”, “Joe Ng”),
new XElement(“zip”, 11217)),
new XElement(“favoriteDrink”, “Banana Split in a
Cup”),
new XElement(“moneySpent”, 60),
new XElement(“visits”, 10)),
new XElement(“person”,
new XElement(“personalInfo”,
new XElement(“name”, “Sarah Kalter”),
new XElement(“zip”, 11215)),
new XElement(“favoriteDrink”, “Boring Coffee”),
new XElement(“moneySpent”, 110),
new XElement(“visits”, 15))));
return doc;
}
/// <summary>
/// Save the Starbuzz data to an XML file and load it again.
/// </summary>
/// <param name=”filename”>Filename to write the data to</param>
static void SaveDataToAnXmlFile(string filename)
{
XDocument doc = GetStarbuzzData();
doc.Save(filename);
}
/// <summary>
/// Query the data and print the results to the console
/// <param name=”doc”>XDocument with Starbuzz customer loyalty data
loaded</param>
/// </summary>
static void QueryTheData(XDocument doc)
{
// Do a simple query and print the results to the console
var data = from item in doc.Descendants(“person”)
select new
{
drink = item.Element(“favoriteDrink”).Value,
moneySpent = item.Element(“moneySpent”).Value,
zipCode =
item.Element(“personalInfo”).Element(“zip”).Value
};
foreach (var p in data)
{
Console.WriteLine(p.ToString());
}
// Do a more complex query and print the results to the console
var zipcodeGroups = from item in doc.Descendants(“person”)
group item.Element(“favoriteDrink”).Value
by
item.Element(“personalInfo”).Element(“zip”).Value
into zipcodeGroup
select zipcodeGroup;
foreach (var group in zipcodeGroups)
{
Console.WriteLine(“{0} favorite drinks in {1}”,
group.Distinct().Count(), group.Key);
}
}
static void Main(string[] args)
{
// Save the Starbuzz data
SaveDataToAnXmlFile(“starbuzzdata.xml”);
// Read the XML data from starbuzzdata.xml
XDocument starbuzzData = XDocument.Load(“starbuzzdata.xml”);
// Query the data that was loaded
QueryTheData(starbuzzData);
// Don’t quit until the user presses a key (just to make it
easier to run in the
// Visual Studio debugger — since this is a learning exercise)
Console.ReadKey();
}
d. Linq to DataSet
Linq to DataSet adalah Query yang mengimplementasikan kedalam resources Dataset
Control yang ada di sediakan pada Net Framework
dsPeople ds = new dsPeople();
dsPeople.RoleRow row =
ds.Role.NewRoleRow();
row.ID = 1;
row.RoleDescription = “Manager”;
ds.Role.AddRoleRow(row);
row = ds.Role.NewRoleRow();
row.ID = 2;
row.RoleDescription = “Developer”;
ds.Role.AddRoleRow(row);
var q = from role in ds.Role
select role;
dsPeople.RoleDataTable t = new dsPeople.RoleDataTable();
q.CopyToDataTable(t, LoadOption.OverwriteChanges);
var query = from r in t
where r.ID == 1
select r;
foreach(var row in query)
{
Console.WriteLine(“Role: {0} {1}”, row.ID,
row.RoleDescription);
}
11. ADO.NET Entity Framework
ADO.Net Entity Framework adalah salah satu fitur framework yang terdapat pada Net
Framework 3.0, Net Framework 3.5 dan Net Framework 4.0. Entity Framework
menggunakan konsep ORM (Object Relational Model) yang di interpretasikan kedalam
bahasa LINQ di dalam C#. Entity Framework mengadaptasi konsep Linq to SQL namun
Entity Framework mengubaha struktur database menjadikan nya object /classes, sehingga
memudahkan dalam proses transaksi query kedalam Database Engine tanpa harus
Database Egine melakukan aktifitas yang tinggi dalam mengeksekusi query yang ada.
Entity Framework menggunakan konsep mapping struktur database yang berada di dalam
database engine menjadikannya sebuah objek/class diagram beserta
Tutorial ADO.NET Entity Framework untuk proses transaksi ORM (Object Relational Mapping) yang
berguna untuk menyimpan data dari class diagram (object) ke dalam sebuah database egine server.
Dibawah ini adalah contoh proses Insert, Edit, Delete object EntityFramework
public static bool InsertUser(MstUser user)
{
bool result = false;
if (user != null)
{
try
{
using (NYKEntities ledger = new NYKEntities())
{
var getUser = GetUserByUserLogin(user.UserLogin);
if (getUser != null)
{
throw new ArgumentException(“User” + ” ” + “‘” +
user.UserName + “sudah terdaftar”);
}
else
{
ledger.MstUsers.AddObject(user);
int succes = ledger.SaveChanges();
result = succes > 0;
}
}
}
catch (Exception)
{
throw;
}
}
return result;
}
/// <summary>UpdateUser</summary>
/// <param name=”user”>Colllections of MstUser table</param>
/// <returns>Success if its true</returns>
public static bool UpdateUser(MstUser user)
{
Create By : Fendy 2011
bool result = false;
if (user != null)
{
try
{
using (NYKEntities ledger = new NYKEntities())
{
IQueryable<MstUser> Query = from usr in ledger.MstUsers
where usr.UserPk == user.UserPk
select usr;
if (Query.Count() > 0)
{
object temp = Query.First();
ledger.MstUsers.ApplyCurrentValues(user);
ledger.DetectChanges();
int succces = ledger.SaveChanges();
result = succces > 0;
}
}
}
catch (Exception)
{
throw;
}
}
return result;
}
/// <summary>DeleteExchangeRate</summary>
/// <param name=”rate”>Collections of TrnExchangeRate</param>
/// <returns>Success if its true</returns>
public static bool DeleteExchangeRate(TrnExchangeRate rate)
{
bool result = false;
if (rate != null)
{
try
{
using (NYKEntities ledger = new NYKEntities())
{
var delObj = ledger.TrnExchangeRates.Where(s => s.ExchangeRatePk
== rate.ExchangeRatePk).First();
ledger.DeleteObject(delObj);
int changes = ledger.SaveChanges();
result = changes > 0;
}
}
catch (Exception)
Create By : Fendy 2011
{
throw;
}
}
return result;
}
Cara pemanggilan fungsi – fungsi di atas :
/// <summary>BindBackFromControl</summary>
/// <returns>Collections of MstUser table</returns>
private MstUser BindBackFromControl()
{
String _pass = MasterUser.GetHash(this.txtPassword.Text);
Int32 _role = Convert.ToInt32(this.ddlRole.SelectedValue);
Int32 _depart = Convert.ToInt32(this.ddlDepartment.SelectedValue);
String _login = this.txtUserLogin.Text.Trim();
String _name = this.txtNama.Text.Trim();
String _jabatan = this.txtJabatan.Text.Trim();
Boolean _isActive = Convert.ToBoolean(this.rblStatus.SelectedValue);
Boolean _isLedger = Convert.ToBoolean(this.rblLedgerPlus.SelectedValue);
Boolean _isBilling = Convert.ToBoolean(this.rblBilling.SelectedValue);
MstUser user = new MstUser();
user.UserPk = -1;
user.UserPassword = _pass;
user.UserName = _name;
user.UserLogin = _login;
user.UserJabatan = _jabatan;
user.UserOfficeDepartment = _depart;
user.UserRole = _role;
user.UserIsActive = _isActive;
user.CanAccesLedgerPlus = _isLedger;
user.CanAccesBillingPlus = _isBilling;
return user;
}
Create By : Fendy 2011
/// <summary>SaveProcess</summary>
private void SaveProcess()
{
MstUser user = this.BindBackFromControl();
bool result = MasterUser.InsertUser(user);
if (result)
{
this.lblPesan.Text = ErrorConstantas.ERR_SAVE_SUCCESS;
this.lblPesan.Visible = true;
this.lblPesan.ForeColor = Color.Red;
this.lblPesan.Font.Size = 10;
this.lblPesan.Font.Italic = true;
}
else
{
this.lblPesan.Text = ErrorConstantas.ERR_SAVE_FAILED;
this.lblPesan.Visible = true;
this.lblPesan.ForeColor = Color.Red;
this.lblPesan.Font.Size = 10;
this.lblPesan.Font.Italic = true;
}
}