Bind DropdownList using Linq

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

Insert, Update , Select and Delete using Linq

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

Linq with Returning Scalar Value from Stored Procedure

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

Use AJAX AutoComplete with Linq

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

Assignment of Yes or No based on value read from DB

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