LINQ to SQL in a nutshell

For LINQ to SQL, remember the Using System.Data.Linq; Using System.Linq;  assembly declaration
this uses System.Core.dll

 

CustomersDataContext dc = new CustomersDataContext();

SELECTING:
var query = from m in dc.Customers
                  where m.LastName = "Brown"
                  order by m.LastName descending
                  select m;

var query = from m in dc.Customers

                  join g in dc.Titles on m.TitleId equals g.Id
                  select m;

var query = (from m in dc.Customers
                  select m).Skip(10).Take(10);

var query = from m in dc.Customers
                  group m by m.Country into g
                  select new { Country = g.Key , Count = g.Count() };

query.Count(), query.Max(m => m.DOB), query.Min(m => m.DOB), query.Ave(m => m.Age), query.Sum(m => m.Balance)

foreach (Customer c in query)
{ ... }

GridView1.DataSource = query;
GridView1.DataBind();

 

DELETING:
var query = from m in dc.Customers
                  where m.LastName = "Brown"
                  select m;

dc.Customers.DeleteAllOnSubmit(query);  // to delete all that matches the where filter

var OneCust = dc.Customers.Single (m => m.id = 55);
dc.Customers.DeleteOnSubmit(OneCust);  // to delete just the one customer in OneCust (id=55)

dc.SubmitChanges();

INSERTING:
Customer m = new Customer { FirstName = "pete", LastName = "brown" };
dc.Customers.InsertOnSubmit(m);  // to delete all that matches the where filter
dc.SubmitChanges();

remember to --- try { } catch (ChangeConflictException  e) { }

 

 

 

 

More C# Linq2SQL MSDN Examples at:

http://msdn.microsoft.com/en-us/vcsharp/aa336746.aspx

 

Getting a summary of your database content (database named XXXXX)  
[replace XXXXX with your database name]

Tablename vs Row count 

 

 

use XXXXX
go

use GUMCAD
go

    SET NOCOUNT ON
 
    DECLARE @SQL VARCHAR(255)
    SET @SQL = 'DBCC UPDATEUSAGE (' + DB_NAME() + ')'
    EXEC(@SQL)
 
    CREATE TABLE #foo
    (
        tablename VARCHAR(255),
        rc INT
    )
    
    INSERT #foo
        EXEC sp_msForEachTable
            'SELECT PARSENAME(''?'', 2) + ''.'' + PARSENAME(''?'', 1) ,
            COUNT(*) FROM ?'
 
    SELECT tablename, rc
        FROM #foo
        --ORDER BY rc DESC
        ORDER BY tablename asc
 
    DROP TABLE #foo