Sunday 24 July 2011

Linq to SQL Basics for asp.net

In my section,the basics of LINQ include few steps you can say just 15 steps...my this section is basically for developers who are new to sql and don't know how to implement LINQ in asp.net
OK so here we start!

1)if i am not wrong you know basics of asp.net
2)OK so just create a new project for LINQ
3)if i am not wrong,now you have a default.aspx

4)now right click on your project and in add new item,add LINQ TO SQL CLASS(which will create DataClasses.dbml(which include layout+cs file) don't bother about any thing everything is on the right way
5)Now Look on server explorer in left side of your Visual studio (if not then go to menu bar on head view>>server explorer)
6)now go to the table you want to work with LINQ
7)open the DataClasses.dbml you've just created and Drag your table from server explorer to DataClasses.dbml file(believe,Dragging works here)
8)now DEBUG the DataClasses.dbml file
9)Now from here the actual work starts...look at the DataClasses.designer.cs(under DataClasses.dbml),after debuging the DataClasses.designer.cs includes your table value automatically
10)now you will find DATACONTEXT function here with name DataClassesDataContext,just remember it!
11)now come back to your Default.aspx & cs file and paste it on page load
12)Now Add a Grid View to your aspx page(if i am not wrong it will come up with a name GridView1)
13) Now 

        DataClassesDataContext dc=new DataClassesDataContext ();
        var q = from a in dc.GetTable<tbEmp1>() select a;
        GridView1.DataSource = q;
        GridView1.DataBind();
14)
Now if you've a confusion that why the sql query Are different here!
then my answer is "that the LINQ is"
select * from tbvijay
is same for LINQ as
from a in dc.
GetTable<tbVijay>() select a



15)
now just these lines and if you are aware of gridview you can do a lot from it!Because All Data Is In GridView!!

still i am giving you some more sql queries for LINQ

i)

 DataClasses1DataContext dc = new DataClasses1DataContext();
 var q = from a in dc.GetTable<Order>()      select a;
 GridView1.DataSource = q;
ii)DataClasses1DataContext dc = new DataClasses1DataContext();
    GridView1.DataSource = dc.GetTable<Order>();

iii)

DataClasses1DataContext dc = new DataClasses1DataContext();
 var q = from a in dc.GetTable<Order>() where a.CustomerID.StartsWith("A") select a;
    dataGridView1.DataSource = q;



iv)

DataClasses1DataContext
 dc = new DataClasses1DataContext();

     var q =  from a in dc.GetTable<Order>()where a.CustomerID == "VINET"  select a;
     dataGridView1.DataSource = q;


v)

DataClasses1DataContext dc = new DataClasses1DataContext();
  var q =   from a in dc.GetTable<Order>()  where a.CustomerID.StartsWith("A")orderby a.OrderDate ascending   select a;
   dataGridView1.DataSource = q;

vi)

DataClasses1DataContext dc = new DataClasses1DataContext();

    var q= (from orders in dc.GetTable<Order>()
            from orderDetails in dc.GetTable<Order_Detail>()
            from prods in dc.GetTable<Product>()
            where ((orderDetails.OrderID == orders.OrderID) &&
                 (prods.ProductID == orderDetails.ProductID) &&
                 (orders.EmployeeID == 1))
            orderby orders.ShipCountry
            select new CustomerOrderResult
            {
                CustomerID = orders.CustomerID,
                CustomerContactName = orders.Customer.ContactName,
                CustomerCountry = orders.Customer.Country,
                OrderDate = orders.OrderDate,
                EmployeeID = orders.Employee.EmployeeID,
                EmployeeFirstName = orders.Employee.FirstName,
                EmployeeLastName = orders.Employee.LastName,
                ProductName = prods.ProductName
            }).ToList<CustomerOrderResult>();

    dataGridView1.DataSource = q;



vii)

 DataClasses1DataContext dc = new DataClasses1DataContext();

    var query = (from orders in dc.GetTable<Order>()
             from orderDetails in dc.GetTable<Order_Detail>()
             from prods in dc.GetTable<Product>()
             where ((orderDetails.OrderID == orders.OrderID)
                  && (prods.ProductID == orderDetails.ProductID)
                  && (orders.EmployeeID == 1))
             orderby orders.ShipCountry
             select new CustomerOrderResult
             {
                 CustomerID = orders.CustomerID,
                 CustomerContactName = orders.Customer.ContactName,
                 CustomerCountry = orders.Customer.Country,
                 OrderDate = orders.OrderDate,
                 EmployeeID = orders.Employee.EmployeeID,
                 EmployeeFirstName = orders.Employee.FirstName,
                 EmployeeLastName = orders.Employee.LastName,
                 ProductName = prods.ProductName
             }).ToList<CustomerOrderResult>();

    var matches = (from c in query
                  where c.CustomerID == "RICAR"
                  select c).ToList<CustomerOrderResult>();

    dataGridView1.DataSource = matches;


viii)

DataClasses1DataContext dc = new DataClasses1DataContext();

    var query = (from orders in dc.GetTable<Order>()
                 select orders);

    var matches = (from c in query
                   where c.OrderID == 10248
                   select
                   c.Employee.LastName).SingleOrDefault<System.String>();

    MessageBox.Show(matches);




Hope it will work for you
Ask me if you have any further question!

No comments:

Post a Comment

Print Only Grid View in ASP.net

ASP.net How to Print Only GridView < div id ="gridviewDiv" >   < asp:GridView ID ="gridViewToPri...