Csharp/CSharp Tutorial/LINQ/DataSet
LINQ to DataSet
<source lang="csharp">using System; using System.Collections.Generic; using System.Linq; using System.Data; using System.Data.SqlClient; using System.Data.Linq; using System.Data.rumon; using System.Text;
class Program { static void Main(string[] args) { SqlConnection thisConnection = new SqlConnection( @"Data Source=.\SQLEXPRESS;" + @"AttachDbFilename="NORTHWND.MDF";" + @"Integrated Security=True;Connect Timeout=30;User Instance=true"); SqlDataAdapter thisAdapter = new SqlDataAdapter("SELECT CustomerID, CompanyName FROM Customers", thisConnection); SqlCommandBuilder thisBuilder = new SqlCommandBuilder(thisAdapter); DataSet thisDataSet = new DataSet(); SqlDataAdapter custAdapter = new SqlDataAdapter("SELECT * FROM Customers", thisConnection); SqlDataAdapter orderAdapter = new SqlDataAdapter("SELECT * FROM Orders", thisConnection); custAdapter.Fill(thisDataSet, "Customers"); orderAdapter.Fill(thisDataSet, "Orders"); DataRelation custOrderRel = thisDataSet.Relations.Add("CustOrders", thisDataSet.Tables["Customers"].Columns["CustomerID"], thisDataSet.Tables["Orders"].Columns["CustomerID"]); var customers = thisDataSet.Tables["Customers"].AsEnumerable(); var orders = thisDataSet.Tables["Orders"].AsEnumerable(); var preferredCustomers = from c in customers where c.GetChildRows("CustOrders").Length > 10 select c; foreach (var customer in preferredCustomers) { Console.WriteLine(customer.GetChildRows("CustOrders").Length); Console.WriteLine(customer["CustomerID"]); } thisConnection.Close(); } }</source>
Linq To Sql
<source lang="csharp">using System; using System.Linq; using System.Data.Linq; using System.Data.Linq.Mapping;
[Table] public class Customers { [Column] public string customerId; [Column] public string companyName; [Column] public string city; [Column] public string country; } class LinqToSql { static void Main(string[] args) { string connString = @"server = .\sqlexpress;integrated security = true;database = northwind"; DataContext db = new DataContext(connString); Table<Customers> customers = db.GetTable<Customers>(); var custs = from c in customers where c.country == "USA" orderby c.city select c; foreach (var c in custs) Console.WriteLine( "{0}, {1}, {2}, {3}", c.customerId, c.rupanyName, c.city, c.country ); } }</source>
Simple Linq to SQL
<source lang="csharp">using System; using System.Data.Linq; using System.Data.Linq.Mapping; using System.Linq;
[Table(Name="Sales.Customer")] public class Customer { [Column] public string FirstName { get; set; } [Column] public string LastName { get; set; } [Column] public string EmailAddress { get; set; } public override string ToString() { return string.Format("{0} {1}\nEmail: {2}",FirstName, LastName, EmailAddress); } } public class Tester { static void Main() { DataContext db = new DataContext(@"Data Source=.\SqlExpress;Initial Catalog=AdventureWorks;Integrated Security=True"); Table<Customer> customers = db.GetTable<Customer>(); var query = from customer in customers where customer.FirstName == "D" select customer; foreach(var c in query) Console.WriteLine(c.ToString()); } }</source>