Archive

Archive for July, 2009

Bind DropdownList using Linq

July 16, 2009 1 comment

Hi

try this example to bind dropdownlist using Linq with Northwind database

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

        }
    }

    private void BindCategory()
    {
        NorthwindDataContext db = new NorthwindDataContext();
        var query = from p in db.Categories
                    select new { p.CategoryID, p.CategoryName };

        DropDownList1.DataSource = query;
        DropDownList1.DataTextField = "CategoryName";
        DropDownList1.DataValueField = "CategoryID";
        DropDownList1.DataBind();
    }

Hope this helps

Good Luck

Advertisements
Categories: Linq

Insert, Update , Select and Delete using Linq

July 14, 2009 Comments off

Hi try this example :-

NorthwindDataContext ctx = new NorthwindDataContext();

//*** Get a Single Row by ID with LINQ
//Returns a single row from the database
private Product GetSingleRowExample1(int productID)
{
Product product = (from p in ctx.Products
where p.ProductID == productID
select p).First();
return product;
}

//Returns a single row from the database (lambda)
private Product GetSingleRowExample2(int productID)
{
return ctx.Products.Single(p => p.ProductID == productID);
}

//*** Get All Rows

//Retrieves all product categories

private void GetAllProductCategories()
 {
 gridResult.DataSource = ctx.ProductCategories;
 gridResult.DataBind();
 }

//*** Insert a Row and Return The Generated ID

protected void btnInsertProductCategory_Click(object sender,
EventArgs e)
{
ProductCategory productCategory = new ProductCategory();
productCategory.Name = “Sample Category”;
productCategory.ModifiedDate = DateTime.Now;
productCategory.rowguid = Guid.NewGuid();
int id = InsertProductCategory(productCategory);
lblResult.Text = id.ToString();
}
//Insert a new product category and return the generated ID (identity value)
private int InsertProductCategory(ProductCategory productCategory)
{
ctx.ProductCategories.InsertOnSubmit(productCategory);
ctx.SubmitChanges();
return productCategory.ProductCategoryID;
}

//*** Update a Row
The built-in change tracking in LINQ automatically determines, if connecting to the database is necessary. If an object is modified, LINQ then generates the necessary UPDATE-statemens automatically.

//Retrieves and updates product category with ID of 4

protected void btnUpdateProductCategory_Click(object sender, EventArgs e)
{
ProductCategory productCategory =ctx.ProductCategories.Single(pc => pc.ProductCategoryID == 4);
UpdateProductCategory(productCategory);
}
//Updates the given product category
private void UpdateProductCategory(ProductCategory productCategory)
{
productCategory.ModifiedDate = DateTime.Now;
productCategory.Name = productCategory.Name + " test";
ctx.SubmitChanges();
}

//*** Delete a Row
Deleting a row is simple.
You could also remove several rows with the DeleteAllOnSubmit-method.

protected void btnDeleteProductCategory_Click(object sender,EventArgs e)
{
ProductCategory productCategory = ctx.ProductCategories
.Single(pc => pc.ProductCategoryID == 5);
DeleteProductCategory(productCategory);
}

//Deletes the given product category
private void DeleteProductCategory(ProductCategory productCategory)
{
ctx.ProductCategories.DeleteOnSubmit(productCategory);
ctx.SubmitChanges();
}

Hope this helps
Good Luck

Categories: Linq

Linq with Returning Scalar Value from Stored Procedure

July 14, 2009 1 comment

Hi

try this example to use Linq with Returning Scalar Value from Stored Procedure

Case 1: With Output Parameter

CREATE PROCEDURE [dbo].[GetEmployeeCount]
      @OutVal DateTime OUTPUT
AS
BEGIN
      SELECT @OutVal = GetDate()
END

You need to write code which will look like,

using (TestDBDataContext db = new TestDBDataContext())
{
    //Need a Nullable type here
    //and you need to have some value to it
    DateTime? dt = null;
    var q = db.GetEmployeeCount(ref dt);
    Console.WriteLine(dt);               
}

Case 2: With Return (only for Integers)

CREATE PROCEDURE [dbo].[GetEmployeeCountRet]         
AS
BEGIN
      DECLARE @Ret INT
      SELECT @Ret = COUNT(*) FROM Emp
      RETURN @Ret
END

Your code may look like,

using (TestDBDataContext db = new TestDBDataContext())
{
    //For Stored Procedure with Return value (for Integer)
    //returns Int
    var q = db.GetEmployeeCountRet();
    Console.WriteLine(q);               

}

You cannot simply say

SELECT COUNT(*) FROM Emp and capture the value in a variable. Because in LINQ to SQL a Stored

Procedure either returns ISingleResult<T> or IMultipleResults<T>, so capturing single value

becomes very tricky.

So when you have to do it go for Scalar-Valued function

Case 3: Using Scalar-Values Functions

ALTER FUNCTION [dbo].[fn_GetEmployeeCount]()
RETURNS int
AS
BEGIN
      DECLARE @ResultVar int
      SELECT @ResultVar = Count(*) FROM Emp
      RETURN @ResultVar
END

You code,

using (TestDBDataContext db = new TestDBDataContext())
{
    var q = db.fn_GetEmployeeCount();
    Console.WriteLine(q);               
}

Hope this helps

Refrence here

Good Luck

Categories: Linq

July 14, 2009 Leave a comment

free counters

Categories: Uncategorized

Use AJAX AutoComplete with Linq

July 13, 2009 2 comments

Hi

try this example to Use AJAX AutoComplete with Linq

<asp:ScriptManager ID="SM1" runat="server" />

<asp:UpdatePanel ID="UP1" runat="server">
<ContentTemplate>
Enter Product Name:
<asp:TextBox ID="txtName" runat="server" AutoComplete="Off" />
<cc1:AutoCompleteExtender ID="txtName_AutoCompleteExtender" runat="server"
TargetControlID="txtName" MinimumPrefixLength="1" ServiceMethod="GetProductNames">
</cc1:AutoCompleteExtender>
</ContentTemplate>
</asp:UpdatePanel>

 
and in code behind :-

using System;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;

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

}

[System.Web.Services.WebMethod]
public static string[] GetProductNames(string prefixText, int count)
{
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, Linq

Assignment of Yes or No based on value read from DB

July 1, 2009 1 comment

Hi

try this example to displayYes or No based on value read from DB

public string ReplaceBitValue(Object obj)  
{  
    string returnValue = string.Empty;  
    if (!string.IsNullOrEmpty(obj.ToString()))  
    {  
        if (obj.ToString() == "1")  
        {  
            returnValue = "Yes";  
        }  
        else if (obj.ToString() == "0")  
        {  
            returnValue = "No";  
        }  
    }  
    return returnValue;  
} 

<asp:TemplateField HeaderText="Status">
     <ItemTemplate>
         <asp:Label ID="status" runat="server" Text='<%# ReplaceBitValue(Eval("BitField").ToString()) %>'></asp:Label>
      </ItemTemplate>
</asp:TemplateField>

//— another way —-

C#
<asp:label id="Label1" runat="server" text='<%# Convert.ToBoolean(Eval("MyDataField")) ? "yes" : "no" %>' />

VB.NET
<asp:label id="Label1" runat="server" text='<%# IIf(Convert.ToBoolean(Eval("MyDataField")), "yes", "no") %>' />

//— another way —-

<asp:Label ID="lblEnrollDate" runat="server"
Text='<%# IIF(CONVERT.ToString(Eval("IsEnrolled"))="True", "Yes", "No") %>'>
</asp:Label>

Hope this helps

Good Luck

Categories: ASP.Net