Monday, 24 March 2014

ListView DML Using ForignKeys tables

*********************BE Lab****************
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace BE
{
  public class BELab
    {
        public int   ID{get;set;}
        public string Assignment{get;set;}
        public string RollNo{get;set;}
        public string StdName { get; set; }

    }
}
********************BE student other table*************
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace BE
{
    public class BEStudent
    {
                public string StdName{get;set;}
                public string StdClass{get;set;}

                public string StdRollNo{get;set;}
               // public string StdPic{get;set;}
                 public string StdPicName{get;set;}
                 public string StdPicPath { get; set;}


    }
}

************************BLL********************
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using BE;
using DAL;

namespace BLL
{
   public class LabBLL
    {

       public List<BELab> show()
       {
           return new LabDAL().show();
       }
       public void insertlab(BELab be)
       {
           new LabDAL().insertlab(be);
       }
       public void Updatelab(BELab be)
       {
           new LabDAL().Updatelab(be);
       }
       public void Deletelab(BELab be)
       {
           new LabDAL().Deletelab(be);
       }
     
    }
}
*************************DAL***************
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using BE;
using System.Data;
using System.Data.SqlClient;

namespace DAL
{
    public class LabDAL
    {
        private string path = MyConnection.path;
        public List<BELab> show()
        {
            List<BELab> list = new List<BELab>();
            SqlConnection con = new SqlConnection(path);
            SqlCommand cmd = new SqlCommand(MyProccs.Name.sp_selectAllLabs.ToString(),con);
            cmd.CommandType = CommandType.StoredProcedure;
            try
            {
                con.Open();
                SqlDataReader sdr = cmd.ExecuteReader();
                if (sdr.HasRows)
                {
                    while (sdr.Read())
                    {
                        BELab be = new BELab();
                        be.ID = Convert.ToInt32(sdr["ID"]);
                        be.RollNo = Convert.ToString(sdr["RollNo"]);
                        be.Assignment = Convert.ToString(sdr["Assignment"]);
                        list.Add(be);

                    }

                }

            }
            catch (Exception ex)
            {

                throw ex;
            }
            finally
            {
                con.Close();
            }
            return list;


       
        }
        public void insertlab( BELab be)
        {

            SqlConnection con = new SqlConnection(path);
            SqlCommand cmd = new SqlCommand(MyProccs.Name.sp_insertIntoLab.ToString(),con);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@ID", be.ID);
            cmd.Parameters.AddWithValue("@RollNo", be.RollNo);
            cmd.Parameters.AddWithValue("Assignment", be.Assignment);
            try
            {
                con.Open();
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {

                throw ex;
            }
            finally
            {
                con.Close();
            }

       
        }
        public void Updatelab(BELab be)
        {

            SqlConnection con = new SqlConnection(path);
            SqlCommand cmd = new SqlCommand(MyProccs.Name.sp_UpdateLab.ToString(), con);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@ID", be.ID);
            cmd.Parameters.AddWithValue("@Assignment", be.Assignment);
            cmd.Parameters.AddWithValue("@RollNo", be.RollNo);
         
            try
            {
                con.Open();
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {

                throw ex;
            }
            finally
            {
                con.Close();
            }


        }
        public void Deletelab(BELab be)
        {

            SqlConnection con = new SqlConnection(path);
            SqlCommand cmd = new SqlCommand(MyProccs.Name.sp_DeleteLab.ToString(), con);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@ID", be.ID);

            try
            {
                con.Open();
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {

                throw ex;
            }
            finally
            {
                con.Close();
            }


        }
    }
}
**************************FORM Design*************
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Test.aspx.cs" Inherits="MyTestLearnProject.Test" %>

<!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>
    <style type="text/css">
        table td
        {
            width: 150px;
            border: 1px solid red;
        }
    </style>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:ListView ID="ListView1" runat="server" DataKeyNames="ID" InsertItemPosition="LastItem" OnItemCreated="Dropdownfill" OnItemInserting="ListView_InserData" OnItemEditing="Listview_Editing" OnItemCanceling="ListView_Canceling" OnItemDeleting="Listview_itemDeleteing" OnItemUpdating="ListView_ItemUpdating">
            <LayoutTemplate>
                <table style="width: 800px;">
                    <tr class="mystyle">
                        <td>
                            ID
                        </td>
                        <td>
                            Student Name
                        </td>
                        <td>
                            RollNo
                        </td>
                        <td>
                            Action
                        </td>
                    </tr>
                </table>
                <div id="ItemPlaceHolder" runat="server">
                </div>
            </LayoutTemplate>
            <ItemTemplate>
                <table style="width: 800px;">
                    <tr class="mystyle">
                        <td>
                            <asp:Label ID="lblID" runat="server" Text='<%#Eval("ID") %>'></asp:Label>
                        </td>
                        <td>
                            <asp:Label ID="lblstdName" runat="server" Text='<%#Eval("StdName") %>'></asp:Label>
                        </td>
                        <td>
                            <asp:Label ID="lblAssignmnet" runat="server" Text='<%#Eval("Assignment") %>'></asp:Label>
                        </td>
                        <td>
                            <asp:Button ID="btnEdit" runat="server" Text="Edit" CommandName="Edit" />
                            <asp:Button ID="btnDelete" runat="server" Text="Delete" CommandName="Delete" />
                        </td>
                    </tr>
                </table>
            </ItemTemplate>
            <InsertItemTemplate>
                <table style="width: 800px;">
                    <tr class="mystyle">
                        <td>
                           <asp:TextBox ID="txtid" Text='<%#Eval("ID") %>' runat="server"></asp:TextBox>
                            <td>
 
                    <asp:HiddenField ID="hd12" runat="server" Value='<%#Eval("RollNo") %>' />
                            <asp:DropDownList ID="dropdownlistins" runat="server">
                            </asp:DropDownList>
                        </td>
                        <td>
                            <asp:TextBox ID="txtAssignment" runat="server" Text='<%#Eval("Assignment") %>'></asp:TextBox>
                        </td>
                        <td><asp:Button ID="btnSubmit" runat="server" CommandName="Insert" Text="Submit" /></td>
                    </tr>
                </table>
            </InsertItemTemplate>
            <EditItemTemplate>
           
             <table style="width: 800px;">
                    <tr class="mystyle">
                        <td>
                           <asp:TextBox ID="txtid" Text='<%#Eval("ID") %>' runat="server"></asp:TextBox>
                            <td>
 
                    <asp:HiddenField ID="hd1" runat="server" Value='<%#Eval("RollNo") %>' />
                            <asp:DropDownList ID="dropdownlistEdit" runat="server">
                            </asp:DropDownList>
                        </td>
                        <td>
                            <asp:TextBox ID="txtAssignment" runat="server" Text='<%#Eval("Assignment") %>'></asp:TextBox>
                        </td>
                        <td><asp:Button ID="btnUpdate" runat="server" CommandName="Update" Text="Update" />
                        <asp:Button ID="btnCancel" runat="server" CommandName="Cancel" Text="Cancel" />
                        </td>
                    </tr>
                </table>
           
           
            </EditItemTemplate>
        </asp:ListView>
    </div>
    </form>
</body>
</html>
******************************Code Behind**************
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using BE;
using BLL;

namespace MyTestLearnProject
{
    public partial class Test : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                databound();

            }
        }

        protected void databound()
        {
            List<BELab> list = new LabBLL().show();
            foreach (BELab item in list)
            {
                item.StdName = new StudentBLL().show().Single(id => id.StdRollNo == item.RollNo).StdName;

            }
            ListView1.DataSource = list;
            ListView1.DataBind();

        }

        protected void Dropdownfill(object sender, ListViewItemEventArgs e)
        {
            if (e.Item.ItemType == ListViewItemType.InsertItem)
            {
                DropDownList ddl = (DropDownList)e.Item.FindControl("dropdownlistins"); if (ddl != null)
                {
                    ddl.DataSource = new StudentBLL().show();
                    ddl.DataTextField = "StdName";
                    ddl.DataValueField = "StdRollNo";
                    ddl.DataBind();

                }

            }
        }

        protected void ListView_InserData(object sender, ListViewInsertEventArgs e)
        {

            BELab be = new BELab();
            TextBox ID = (TextBox)e.Item.FindControl("txtid");
            be.ID = Convert.ToInt32(ID.Text);
            DropDownList list = (DropDownList)e.Item.FindControl("dropdownlistins");
            be.RollNo = list.SelectedValue;
            TextBox Assignmnet = (TextBox)e.Item.FindControl("txtAssignment");
            be.Assignment = Assignmnet.Text;
            new LabBLL().insertlab(be);

            databound();
       
        }

        protected void Listview_Editing(object sender, ListViewEditEventArgs e)
        {

            ListView1.EditIndex = e.NewEditIndex;
            databound();

            DropDownList ddl1 = ListView1.Items[e.NewEditIndex].FindControl("dropdownlistEdit") as DropDownList;
            HiddenField hd = ListView1.Items[e.NewEditIndex].FindControl("hd1") as HiddenField;
            if (ddl1 != null)
            {

                ddl1.DataSource = new StudentBLL().show();
                ddl1.DataTextField = "StdName";
                ddl1.DataValueField = "StdRollNo";
                ddl1.DataBind();
                ddl1.SelectedValue = hd.Value;
           
            }
       
        }

        protected void ListView_Canceling(object sender ,ListViewCancelEventArgs e)
    {

        ListView1.EditIndex = -1;
        databound();
   
    }
        protected void Listview_itemDeleteing(object sender, ListViewDeleteEventArgs e)
        {

            int id = (int)ListView1.DataKeys[e.ItemIndex].Value;
            BELab be = new BELab();
            be.ID = id;
            new LabBLL().Deletelab(be);
            databound();
        }
        protected void ListView_ItemUpdating(object sender,ListViewUpdateEventArgs e)
        {

            int li = (int)ListView1.DataKeys[e.ItemIndex].Value;
            BELab be = new BELab();
            be.ID = li;
            be.RollNo = ((DropDownList)ListView1.Items[e.ItemIndex].FindControl("dropdownlistEdit")).SelectedValue;
            be.Assignment = ((TextBox)ListView1.Items[e.ItemIndex].FindControl("txtAssignment")).Text;
            new LabBLL().Updatelab(be);
            ListView1.EditIndex = -1;
            databound();
       
        }
    }
}

No comments:

Post a Comment