Archive

Archive for the ‘Linq’ Category

Use The NOT IN clause in LINQ to SQL

September 8, 2011 Leave a comment

Hi all,

try this example to use the NOT IN clause in LINQ to SQL

This is one SQL query that returns that value.

SELECT * FROM [dbo].[Customers] AS c
WHERE c.[CustomerID] NOT IN (
SELECT o.[CustomerID]
FROM [dbo].[Orders] AS o
)

LINQ offers a Contains extension method that allows writing the following code.

NorthwindDataContext dbml = new NorthwindDataContext();
dc.Log = Console.Out;
var query =
from c in dbml.Customers
where !(from o in dbml.Orders
select o.CustomerID)
.Contains(c.CustomerID)
select c;
foreach (var c in query) Console.WriteLine( c );

Hope this helps

Good Luck

Advertisements
Categories: ASP.Net, Linq

Using LINQ to ADO.NET Entity Data Model

December 25, 2010 2 comments

Hi All,

try this Example to use Using LINQ to ADO.NET Entity Data Model and we will learn how to insert and update and delete and Get data from Database

In this Example i’m using Northwind Database .. you can also get it from  here

1) Open VS 2008 or VS2010 and create a new WebSite and right click on solution and select add new item then Add a new ADO.NET Data Model

Using the ADO.NET Entity Framework wizard you can easily create a representation of the sample Northwind database like the one below:

Now We are going to the following :-

Query an Entity from the Database

NorthwindEntities dbContext = new NorthwindEntities();
var query = from p in dbContext.ProductSet
           where p.Categories.CategoryName == "Seafood"
           select p;
IEnumerable<Product> products = query.ToList();

Update an Entity in the Database :-

NorthwindEntities dbContext = new NorthwindEntities();
Product product = dbContext.ProductSet.Single( p => p.ProductName == "Aniseed Syrup" );
product.UnitPrice = 1000;
dbContext.SaveChanges();

the above code using to select single Product object from the database, update its price, and then save the changes back to the database.

Insert a New Record(s) in the Database :-

The code below shows you how to create a new Category object. Then how to create two new Products and associate them with the Category. Finally, all three objects are saved in the database

NorthwindEntities dbContext = new NorthwindEntities();

Category category = new Category();
category.CategoryName = "Test Category";

Product firstProduct = new Product();
firstProduct.ProductName = "Test Product 1";

Product secondProduct = new Product();
secondProduct.ProductName = "Test Product 2";

category.Products.Add( firstProduct );
category.Products.Add( secondProduct );

dbContext.AddToCategorySet( category );
dbContext.SaveChanges();

 Delete a Record from the Database :-

The code below demonstrates you how to delete all “Test” products from the database

NorthwindEntities dbContext = new NorthwindEntities();
var query = from p in dbContext.ProductSet
           where p.ProductName.Contains( "Test" )
           select p;
foreach ( Product p in query )
   dbContext.DeleteObject( p );
dbContext.SaveChanges();

Hope this helps

Good Luck

Three Ways to Bind DropdownList Using Linq

November 4, 2010 Leave a comment

Hi all,

Try this example to show you Three Ways to Bind DropdownList Using Linq

Step1 :- Open VS2008 and create a new website

Step2 :- add new Linq to SQL DataContext  and name it “Northwind.dbml” and add Categories table in it

Step3 :- Add new Web page and fromDropdownList control inside page

<div>
    Select Category :-
        <asp:DropDownList ID="ddl_Category" runat="server" AppendDataBoundItems="True">
            <asp:ListItem Value="-1">-- Please Select --</asp:ListItem>
        </asp:DropDownList>
    </div>

Step4 :- In Code behind and the following code which will be used to Bind DropdownList

— First Way :-


private void BindCategories()
    {
        try
        {
            NorthwindDataContext dbml = new NorthwindDataContext();
            var str = "select * from Categories ";
            var category = dbml.ExecuteQuery<Category>(str);
            ddl_Category.DataTextField = "CategoryName";
            ddl_Category.DataValueField = "CategoryID";
            ddl_Category.DataSource = category.ToList();
            ddl_Category.DataBind();
        }
        catch
        {

        }
    }

— Second Way :-


private void BindCategories()
    {
        try
        {
            NorthwindDataContext dbml = new NorthwindDataContext();
            var category = from cat in dbml.Categories
                           select cat;
            ddl_Category.DataTextField = "CategoryName";
            ddl_Category.DataValueField = "CategoryID";
            ddl_Category.DataSource = category.ToList();
            ddl_Category.DataBind();
        }
        catch
        {

        }
    }

— Third Way :-


private void BindCategories()
    {
        try
        {
            NorthwindDataContext dbml = new NorthwindDataContext();
            var category = dbml.Categories;
            ddl_Category.DataTextField = "CategoryName";
            ddl_Category.DataValueField = "CategoryID";
            ddl_Category.DataSource = category.ToList();
            ddl_Category.DataBind();
        }
        catch
        {

        }
    }

and In Page Load add this code

protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            BindCategories();
        }
    }

Hope this helps

Good Luck

Categories: ASP.Net, Linq

Select Max Value With LINQ To SQL

September 10, 2010 1 comment

Hi all

Try this Example to show how to Select Max Value With LINQ To SQL

In T-SQL such a query can be written like this

SELECT MAX(UnitPrice) FROM products

In LINQ To SQL we can write the following query

NorthwindDataContext dbml = new NorthwindDataContext();

var query = (from p in dbml.Products select (p.UnitPrice)).Max()  ;

Other than MAX we can also use other aggregates such as MIN, SUM etc..

Hope this helps

Good Luck

Categories: ASP.Net, Linq

JQuery Autocomplete With Linq

September 3, 2010 3 comments

Hi all

try this example to make Autocomplete with Linq using Jquery

in previous post i provide an example to show how to do this AutoComplete from Database using JQuery

Step1 :- First download the following files jquery.autocomplete.js and jquery.autocomplete.css

Step2 :- Open VS2008 and create a new website

Step3 :- add new Linq to SQL DataContext  and name it “Northwind.dbml” and add Products table in it

Step4 :- Add new Web Page and Drage TextBox control from Toolbox inside page

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Autosugeestion.aspx.cs" Inherits="Autosugeestion" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
<script src="Scripts/jquery-1.3.2.js" type="text/javascript"></script>
<script src="Scripts/jquery.autocomplete.js" type="text/javascript"></script>
<link href="CSS/jquery.autocomplete.css" rel="stylesheet" type="text/css" />
<script type="text/javascript">
$(document).ready(function() {
$("#<%=txtSearch.ClientID%>").autocomplete('Search.ashx');
});
</script>
</head>
<body>
<form id="form1" runat="server">
<div>
<div>
Enter Product :- <asp:TextBox ID="txtSearch" runat="server" Width="190"></asp:TextBox>
</div>
</div>
</form>
</body>
</html>

Step5 :- Add new ASP.Net Generic Handlers file and name it “Search.ashx”


<%@ WebHandler Language="C#" %>

using System;
using System.Web;
using System.Linq;

public class Search : IHttpHandler {

public void ProcessRequest (HttpContext context)
{
string param = HttpContext.Current.Request.Params["q"];
NorthwindDataContext dbml = new NorthwindDataContext();
var query = from products in dbml.Products
where products.ProductName.StartsWith(param)
select products;
foreach (var item in query)
{
context.Response.Write(item.ProductName + Environment.NewLine);

}
}

public bool IsReusable {
get {
return false;
}
}
}

Hope this helps

Good Luck

Categories: ASP.Net, Jquery, Linq

Show Loading image with Autocomplete Extender Textbox

April 1, 2010 5 comments

Hi

try this Example to Show Loading image with Autocomplete Extender Textbox

Step1 :- Open VS2008 and create a new website

Step2 :- add new Linq to SQL DataContext  and name it “Northwind.dbml” and add Products table in it

Step3 :- Add new Web page and from ToolBox drag ScriptManager , TextBox and AJAX Autocomplete Extender control


<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default4.aspx.cs" Inherits="Default4" %>
<%@ Register Assembly="AjaxControlToolkit" Namespace="AjaxControlToolkit" TagPrefix="cc1" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
    <script type="text/javascript">
        function ShowImage() {
            document.getElementById('txtAutoComplete').style.backgroundImage = 'url(loading.gif)';
            document.getElementById('txtAutoComplete').style.backgroundRepeat = 'no-repeat';
            document.getElementById('txtAutoComplete').style.backgroundPosition = 'right';
        }
        function HideImage() {
            document.getElementById('txtAutoComplete').style.backgroundImage = 'none';
        }
     </script>
</head>
<body>
    <form id="form1" runat="server">
    <div>

       <asp:ScriptManager ID="ScriptManager1" runat="server" >
        <Services>
              <asp:ServiceReference Path="~/WebService.asmx" />
        </Services>
       </asp:ScriptManager>
        <div>
            Enter Product Name:-
         <asp:TextBox ID="txtAutoComplete" runat="server"></asp:TextBox>
            <cc1:AutoCompleteExtender ID="AutoCompleteExtender1" runat="server"
                MinimumPrefixLength="1"
                ServiceMethod="GetNames"
                TargetControlID="txtAutoComplete"                
                CompletionInterval="10"               
                EnableCaching="true"               
                CompletionSetCount="12"
                onclientpopulating="ShowImage"               
                onclientpopulated="HideImage">
            </cc1:AutoCompleteExtender>

        </div>
    </form>
</body>
</html>

Step4 :- In code behind add the following code:


using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

public partial class Default4 : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
      
    }

    [System.Web.Services.WebMethod]
    public static string[] GetNames(string prefixText, int count)
    {
        System.Threading.Thread.Sleep(1000);
        NorthwindDataContext db = new NorthwindDataContext();
        return db.Products.Where(n => n.ProductName.StartsWith(prefixText)).OrderBy(n => n.ProductName).Select(n => n.ProductName).Take(count).ToArray();
    }
}

Hope this helps

Good Luck.

Categories: AJAX, ASP.Net, Linq

Repeater Paging using Linq

March 29, 2010 3 comments

Hi

try this example for paging Repeater control using Linq

Step1 :- Open VS2008 and create a new website

Step2 :- add new Linq to SQL DataContext  and name it “Northwind.dbml” and add Customers table in it

Step3 :- Add new Web page and from ToolBox drag Repeater control and PlaceHolder control inside page

<asp:Repeater ID="Repeater1" runat="server">
        <HeaderTemplate>
            <table>
        </HeaderTemplate>
        <ItemTemplate>
            <tr>
                <td>
                    <asp:Label runat="server" ID="lblContactName" Text='<%# Eval("Name") %>' />
                </td>
            </tr>
        </ItemTemplate>
        <FooterTemplate>
            </table>
        </FooterTemplate>
    </asp:Repeater>
    <table>
        <tr>
            <td>
                <asp:PlaceHolder ID="plcPaging" runat="server" />
            </td>
        </tr>
    </table>

Step4 :- In code behind add the following code


using System;
using System.Linq;
using System.Web.UI.WebControls;

    public partial class _Default : System.Web.UI.Page
    {
        private int RowCount
        {
            get
            {
                return (int)ViewState["RowCount"];
            }
            set
            {
                ViewState["RowCount"] = value;
            }
        }
       
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                FetchData(10, 0);    
            }
            else
            {
                plcPaging.Controls.Clear();
                CreatePagingControl();
            }                      
        }

        private void FetchData(int take, int pageSize)
        {          
            using (NorthwindDataContext dc = new NorthwindDataContext())
            {
                var query = from p in dc.Customers
                                .OrderBy(o => o.ContactName)
                                .Take(take)
                                .Skip(pageSize)
                            select new
                            {
                                ID = p.CustomerID,
                                Name = p.ContactName,
                                Count = dc.Customers.Count()
                            };
               
                PagedDataSource page = new PagedDataSource();
                page.AllowCustomPaging = true;
                page.AllowPaging = true;
                page.DataSource = query;
                page.PageSize = 10;
                Repeater1.DataSource = page;
                Repeater1.DataBind();

                if (!IsPostBack)
                {
                    RowCount = query.First().Count;
                    CreatePagingControl();
                }
            }
        }

        private void CreatePagingControl()
        {  
            for (int i = 0; i < (RowCount / 10) + 1; i++)
            {
                LinkButton lnk = new LinkButton();               
                lnk.Click += new EventHandler(lbl_Click);
                lnk.ID = "lnkPage" + (i + 1).ToString();
                lnk.Text = (i + 1).ToString();
                plcPaging.Controls.Add(lnk);
                Label spacer = new Label();
                spacer.Text = "&nbsp;";
                plcPaging.Controls.Add(spacer);
            }
        }
      
        void lbl_Click(object sender, EventArgs e)
        {
            LinkButton lnk = sender as LinkButton;
            int currentPage = int.Parse(lnk.Text);
            int take = currentPage * 10;
            int skip = currentPage == 1 ? 0 : take - 10; 
            FetchData(take, skip);
        }

}

Hope this helps

Good Luck.

Categories: ASP.Net, Linq Tags: