Using LINQ to ADO.NET Entity Data Model

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

Get All Tables Name of Database

Hi all,

try this example using SQL Server Query to Get all Tables name of Spacififc Database

so Open MS SQL Server and write this Query :-

In this example i will get all tables name of Northwind Database

USE NORTHWIND
SELECT * FROM information_schema.tables ORDER BY table_TYPE

Hope this helps

Good Luck

Using Checkbox in ASP.NET GridView

Hi all

try this example to Using Checkbox in ASP.NET GridView control

1) Open SQLExpress and Create New Database Called “Test” for example and Add new Table  called “Categories “which has the following colomns :-

 CREATE TABLE [dbo].[Categories](
 [CategoryID] [int] IDENTITY(1,1) NOT NULL,
 [CategoryName] [nvarchar](50) NULL,
 [Approved] [bit] NULL,
 CONSTRAINT [PK_Categories] PRIMARY KEY CLUSTERED
(
 [CategoryID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

and add some data in this Table

2) Now Open VS 2008 and create a new Website and add new Page and from Toolbox add Gridview Control so our page will be like this :-

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"
    BackColor="White" BorderColor="#336699" BorderStyle="Solid" BorderWidth="1px" 
    CellPadding="0" CellSpacing="0" DataKeyNames="CategoryID" Font-Size="10"
    Font-Names="Arial" GridLines="Vertical" Width="40%">
            <Columns>           
                <asp:TemplateField>
                    <ItemTemplate>
                        <asp:CheckBox ID="chkStatus" runat="server"
                            AutoPostBack="true" OnCheckedChanged="chkStatus_OnCheckedChanged"
                            Checked='<%# Convert.ToBoolean(Eval("Approved")) %>'
                            Text='<%# Eval("Approved").ToString().Equals("True") ? " Approved " : " Not Approved " %>' />
                    </ItemTemplate>                  
                </asp:TemplateField>
                <asp:BoundField DataField="CategoryID" HeaderText="CategoryID" />                  
                <asp:BoundField DataField="CategoryName" HeaderText="CategoryName"  />
            </Columns>
    <HeaderStyle BackColor="#336699" ForeColor="White" Height="20" />
  </asp:GridView>

3) Now we need to write some code to Bind our Gridview with Data from Categories table and also to handle CheckBox Event which will update value of Approved from True to False or from False to True according to Value in table so we will write the following code :-

using System;
using System.Collections.Generic;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Data;

public partial class _Default : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!Page.IsPostBack)
        {
            LoadData();
        }
    }

    private void LoadData()
    {
        string constr = @"Server=.\SQLEXPRESS;Database=Test;Integrated Security=True";
        string query = @"SELECT CategoryID, CategoryName, Approved FROM Categories";
        SqlDataAdapter da = new SqlDataAdapter(query, constr);
        DataTable table = new DataTable();
        da.Fill(table);
        GridView1.DataSource = table;
        GridView1.DataBind();
    }

    public void chkStatus_OnCheckedChanged(object sender, EventArgs e)
    {
        CheckBox chkStatus = (CheckBox)sender;
        GridViewRow row = (GridViewRow)chkStatus.NamingContainer;
        string cid = row.Cells[1].Text;
        bool status = chkStatus.Checked;
        string constr = @"Server=.\SQLEXPRESS;Database=Test;Integrated Security=True";
        string query = "UPDATE Categories SET Approved = @Approved WHERE CategoryID = @CategoryID";
        SqlConnection con = new SqlConnection(constr);
        SqlCommand com = new SqlCommand(query, con);
        com.Parameters.Add("@Approved", SqlDbType.Bit).Value = status;
        com.Parameters.Add("@CategoryID", SqlDbType.Int).Value = cid;
        con.Open();
        com.ExecuteNonQuery();
        con.Close();
        LoadData();
    }
}

now run your website

Hope this helps

Good Luck

Create Gridview Lockup

Hi all,

try this example to Create Gridview Lockup

in this example i’m using Northwind Database

1) Open VS 2005 and create a new WebSite and add new Page called “Default.aspx” and from ToolBox add Gridview Control and Bind it to Categories Table using SqlDataSource control so our page will be like this

    <div>
        <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="CategoryID"
            DataSourceID="SqlDataSource1" OnRowDataBound="GridView1_RowDataBound" CellPadding="4" ForeColor="#333333">
            <Columns>
                <asp:TemplateField HeaderText="CategoryName" SortExpression="CategoryName">
                    <ItemTemplate>
                        <asp:TextBox ID="txt_Category" runat="server" Text='<%# Bind("CategoryName") %>' ></asp:TextBox>
                        <asp:LinkButton ID="LinkButton1" runat="server">list</asp:LinkButton><br />
                        <asp:HiddenField ID="HiddenField1" runat="server" Value='<%# Eval("CategoryID") %>'  />
                    </ItemTemplate>
                </asp:TemplateField>
            </Columns>
            <RowStyle BackColor="#EFF3FB" />
            <FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
            <PagerStyle BackColor="#2461BF" ForeColor="White" HorizontalAlign="Center" />
            <SelectedRowStyle BackColor="#D1DDF1" Font-Bold="True" ForeColor="#333333" />
            <HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
            <EditRowStyle BackColor="#2461BF" />
            <AlternatingRowStyle BackColor="White" />
        </asp:GridView>
        <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:NorthwindConnectionString %>"
            SelectCommand="SELECT [CategoryID], [CategoryName] FROM [Categories]"></asp:SqlDataSource>
    </div>

2) Now We want when user Click on HyperLink inside Gridview open popup window which has a Gridview Bindable to all Categories from Northwind database and when user select any Category from that Gridview pass CategoryName and CategoryID to the Gridview in Parent page which is “Default.aspx” so now we are going to add some Jaavscript code in Default page to Open Popup page so the javascipt code will be :

<script type="text/javascript">
        function OpenPopup(txtField, hdnField) {
            window.open("popup.aspx?TxtField=" + txtField + "&HdnField=" + hdnField,
                    "List", "scrollbars=no,resizable=no,width=400,height=280");
            return false;
        }
    </script>

as we see the popup page will be popup.aspx so we have to create a new web page and call it “popup.aspx”

3)  In code behind of Default.aspx we need to write some code  to open that popup window and pass the ID of TextBox (which contain CategoryName) and ID of hiddenField (which is bindable to CategoryID) so in RowDataBound Event of Gridview we will add this code

protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
    {
        if (e.Row.RowType == DataControlRowType.DataRow)
        {
            TextBox txt_Category = (TextBox)e.Row.FindControl("txt_Category");
            HiddenField HiddenField1 = (HiddenField)e.Row.FindControl("HiddenField1");
            LinkButton LinkButton1 = (LinkButton)e.Row.FindControl("LinkButton1");
            if (txt_Category != null && HiddenField1 != null && LinkButton1 != null)
            {
                LinkButton1.Attributes.Add("onclick", "javascript:OpenPopup('" + txt_Category.ClientID + "','" + HiddenField1.ClientID + "');return false;");
            }
        }
    }

4) In popup.aspx page we will add Gridview Control and Bind it to Categories Table using SqlDataSource control  and two hiddenField control which we will save ID of TextBox and HiddenField from the parent page so our page will be like this :-

    <div>
        <asp:HiddenField ID="HTextControlName" runat="server" /> 
        <asp:HiddenField ID="HHiddenControlname" runat="server" />
        <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="CategoryID"
            DataSourceID="SqlDataSource1" CellPadding="4" ForeColor="#333333" OnRowDataBound="GridView1_RowDataBound">
            <Columns>
                <asp:BoundField DataField="CategoryID" HeaderText="CategoryID" InsertVisible="False"
                    ReadOnly="True" SortExpression="CategoryID" />
                <asp:BoundField DataField="CategoryName" HeaderText="CategoryName" SortExpression="CategoryName" />
                <asp:TemplateField>
                    <ItemTemplate>
                        <asp:LinkButton ID="LinkButton1" runat="server">Select</asp:LinkButton>
                    </ItemTemplate>
                </asp:TemplateField>
            </Columns>
            <RowStyle BackColor="#EFF3FB" />
            <FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
            <PagerStyle BackColor="#2461BF" ForeColor="White" HorizontalAlign="Center" />
            <SelectedRowStyle BackColor="#D1DDF1" Font-Bold="True" ForeColor="#333333" />
            <HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
            <EditRowStyle BackColor="#2461BF" />
            <AlternatingRowStyle BackColor="White" />
        </asp:GridView>
        <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:NorthwindConnectionString %>"
            SelectCommand="SELECT [CategoryID], [CategoryName] FROM [Categories]"></asp:SqlDataSource>
   
    </div>

no we want when user Click On Select Link in Gridview to pass the Value of CategoryName and CategoryID from Child window to Parent Page so in popup.aspx we will add this Javascript Code

    <script language="javascript" type="text/javascript">
    function GetRowValue(val, id, txtField, hdnField)  
    {
        window.opener.document.getElementById(txtField).value = val;
        window.opener.document.getElementById(hdnField).value = id;  
        window.close();  
    }  
    </script>

and In Code behind :-

protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            if (Request.QueryString["TxtField"] != null &&
                    Request.QueryString["HdnField"] != null)
            {
                HTextControlName.Value = Request.QueryString["TxtField"];
                HHiddenControlname.Value = Request.QueryString["HdnField"];
            }
        }
    }

    protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
    {
        if ((e.Row.RowType == DataControlRowType.DataRow))
        {
            ((LinkButton)e.Row.FindControl("LinkButton1")).Attributes.Add("onclick", "javascript:GetRowValue('"
                + e.Row.Cells[1].Text + "','" + e.Row.Cells[0].Text + "','" + HTextControlName.Value
                + "','" + HHiddenControlname.Value + "')");
        }
    }

now run the website and from Gridview in Default.aspx click on List link you will find a popup window open and from that popup click on select link in Gridview you will find the Gridview in Default.aspx has the new value which is selected from popup window

Hope this helps

Good Luck

Disable and Enable TextBox via DropdowList Selection in Gridbiew

Hi all,

try this Example to Disable and Enable TextBox via DropdowList Selection in Gridbiew using Javascript

In this example i will use Category Table in Northwind Database

so let’s start this Example following this Steps :-

Step1 :- Create a new Website and add new web page and from Toolbox Add Gridview Control and Bind it to Category table using SqlDataSource control and add two Colomns to Gridview where the firs one will have a DropdownList Control and the second colomn has a TextBox which we will Enable/Disable it according DropDownList Selection so your page will be like this

    <div>
        <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" CellPadding="4"
            DataKeyNames="CategoryID" DataSourceID="SqlDataSource1" ForeColor="#333333" OnRowDataBound="GridView1_RowDataBound">
            <RowStyle BackColor="#EFF3FB" />
            <Columns>
                <asp:BoundField DataField="CategoryID" HeaderText="Category ID" InsertVisible="False"
                    ReadOnly="True" SortExpression="CategoryID" />
                <asp:BoundField DataField="CategoryName" HeaderText="Category Name" SortExpression="CategoryName" />
                <asp:TemplateField HeaderText="Payment">
                    <ItemTemplate>
                        <asp:DropDownList ID="ddl_PaymentMethod" runat="server">
                            <asp:ListItem Value="-1">----</asp:ListItem>
                            <asp:ListItem Value="0">Month</asp:ListItem>
                            <asp:ListItem>At End</asp:ListItem>
                            <asp:ListItem Value="2">At Travel</asp:ListItem>
                        </asp:DropDownList>
                    </ItemTemplate>
                </asp:TemplateField>
                <asp:TemplateField HeaderText="Value">
                    <ItemTemplate>
                        <asp:TextBox ID="txt_Value" runat="server" Width="58px" Text="0"></asp:TextBox>
                    </ItemTemplate>
                </asp:TemplateField>
            </Columns>
            <FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
            <PagerStyle BackColor="#2461BF" ForeColor="White" HorizontalAlign="Center" />
            <SelectedRowStyle BackColor="#D1DDF1" Font-Bold="True" ForeColor="#333333" />
            <HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
            <EditRowStyle BackColor="#2461BF" />
            <AlternatingRowStyle BackColor="White" />
        </asp:GridView>
        <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:NorthwindConnectionString %>"
            SelectCommand="SELECT [CategoryID], [CategoryName] FROM [Categories]"></asp:SqlDataSource>

    </div>

 

Step2 :- In the Hrader of Web Page we will add Javascript function which will Enable and Disable TextBox according to Value of Selected DropdownList

    <script type="text/javascript" language="javascript">
    function EnableTextbox(ddl,txt)
    {
        var ddl_PaymentMethod= document.getElementById(ddl);
        var txt_Value=document.getElementById(txt);
        var ddl_Value=ddl_PaymentMethod.value;
        if(ddl_Value == "0")
            {
                txt_Value.disabled= false;
            }
         else
            {
                txt_Value.disabled= true;
            }  
    }
    </script>

Step3 :- In Code Behind and in RowDataBound of Gridview Control we will pass the Javascript function so your code will be like this :-

    protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
    {
        if (e.Row.RowType == DataControlRowType.DataRow)
        {
            TextBox txt_Value = (TextBox)e.Row.FindControl("txt_Value");
            DropDownList ddl_PaymentMethod =(DropDownList) e.Row.FindControl("ddl_PaymentMethod");
            txt_Value.Attributes.Add("disabled", "disabled");
            //txt_Value.Attributes.Add("readonly", "readonly");
            ddl_PaymentMethod.Attributes.Add("onchange","javascript:EnableTextbox('"+ddl_PaymentMethod.ClientID+"','"+txt_Value.ClientID+"')");
        }
    }

So after that run your website and try the example

Hope this helps you …  Good Luck

Display All Images from Folder in DataList

Hi all

try this example to dispaly all images in spacific folder

Suppose we have folder called “Images” in my Website contains Images with Different extensions

Step1 :- Create a new Website and add new folder called “Images” and add some images in that folder

Step2 :- Add new Web Page and Drag from Toolbox DataList control which we will dispaly images from folder in it

<asp:DataList ID="DataList1" runat="server" RepeatColumns="5" CellPadding="5">
            <ItemTemplate>
            <asp:Image Width="100" ID="Image1" ImageUrl='<%# Bind("Name", "~/images/{0}") %>' runat="server" />
                <br />
                <asp:HyperLink ID="HyperLink1" Text='<%# Bind("Name") %>' NavigateUrl='<%# Bind("Name", "~/images/{0}") %>' runat="server"/>
            </ItemTemplate>
                <ItemStyle BorderColor="Silver" BorderStyle="Dotted" BorderWidth="1px" HorizontalAlign="Center"
                    VerticalAlign="Bottom" />
</asp:DataList>

and in Code behind add this code :-

protected void Page_Load(object sender, EventArgs e)
 {
   ListImages();
 }
private void ListImages()
 {
    DirectoryInfo dir = new DirectoryInfo(MapPath("~/images"));
    FileInfo[] file = dir.GetFiles();
    ArrayList list = new ArrayList();
    foreach (FileInfo file2 in file)
     {
       if (file2.Extension == ".jpg" || file2.Extension == ".jpeg" || file2.Extension == ".gif")
        {
          list.Add(file2);
        }
     }
   DataList1.DataSource = list;
   DataList1.DataBind();
 }

Don’t forget to use namespace “Using System.IO ;”

Hope this helps

Good Luck