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();
       
        }
    }
}

Adding Water Mark on Pictures Asp.Net

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Drawing;
using System.Drawing.Imaging;
using System.Drawing.Drawing2D;
using System.Drawing.Design;
using System.Drawing.Text;


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

        }

        protected void Button1_Click(object sender, EventArgs e)
        {
            //Execute below code at the event, where u wants Water Marking.

           // Create a bitmap object of the Image, Here I am taking image from File Control "FileTest"
            Bitmap bmp = new Bitmap(FileUpload1.PostedFile.InputStream);
            Graphics canvas = Graphics.FromImage(bmp);
            try
            {
                Bitmap bmpNew = new Bitmap(bmp.Width, bmp.Height);
                canvas = Graphics.FromImage(bmpNew);
                canvas.DrawImage(bmp, new Rectangle(0, 0, bmpNew.Width, bmpNew.Height), 0, 0, bmp.Width, bmp.Height, GraphicsUnit.Pixel);
                bmp = bmpNew;
            }
            catch(Exception ee) // Catch exceptions
            {
                Response.Write(ee.Message);
            }
            // Here replace "Text" with your text and you also can assign Font Family, Color, Position Of Text etc.
            canvas.DrawString("GN somro", new Font("Verdana", 14, FontStyle.Bold), new SolidBrush(Color.Beige), (bmp.Width / 2), (bmp.Height / 2));
            // Save or display the image where you want.
            bmp.Save(System.Web.HttpContext.Current.Server.MapPath("~/images/") + FileUpload1.PostedFile.FileName, System.Drawing.Imaging.ImageFormat.Jpeg);

        }
    }
}

How to bind and Export GridView data to Ms Word file using asp.net(C#, VB.Net)

Introduction: In previous articles i explained  How to bind and Export GridView data to Ms Excel file  and Bind and Export GridView data to CSV file in asp.net and Bind and Export GridView data to PDF file in asp.net and  How to bind gridview using SqlDataAdapter, SqlCommand, DataSet and Stored procedure in Asp.net and How to bind GridView from Xml DataSource and Highlight gridview row on mouse over using CSS in asp.net  and How to bind empty GridView with header and custom message when no data present in DataSet in Asp.net .

 In this article I am going to explain with example how to Bind GridView and Export Gridview data to Ms Word file using asp.net.  

Click on image to enlarge

click on image to enlarge

Implementation: Let's create an asp.net sample application to understand.

• First of all create a Database e.g. "MyDataBase" and a also create a table under that DataBase in Sql Server and name it "EMPLOYEE" as shown in figure:

Note: EMP_ID column is set to Primary key and Identity specification is set to yes with Identity increment and Identity seed equal to 1. Insert some data in this table that you  want to show in the Gridview.

• Now in web.config file add the connection string under <configuration> tag :

<connectionStrings>

    <add name="conStr" connectionString="Data Source=LocalServer;Initial Catalog=MyDataBase;Integrated Security=True"/>

  </connectionStrings>  

Note: Replace the Data Source and the Initial catalog as per your applicaton.

• In the design page (.aspx) place a GridView control to bind with data and a Button control to Export the GridView data to MS word file.

Source Code:

<fieldset style="width:360px;">

            <legend>Bind and Export GridView data to Ms Word in asp.net</legend>

            <table>

                <tr>

                    <td>

                        <asp:GridView ID="grEmp" runat="server" AllowPaging="True" AutoGenerateColumns="False"

                   GridLines="None" Width="100%" CellPadding="4" ForeColor="#333333">

                   

                    <AlternatingRowStyle BackColor="White" ForeColor="#284775" />                   

                    <Columns>

                        <asp:BoundField DataField="EMP_NAME" HeaderText="Emp Name"  />

                        <asp:BoundField DataField="DEPT" HeaderText="Department"  />

                        <asp:BoundField DataField="SALARY" HeaderText="salary"  />

                        <asp:BoundField DataField="EMAIL_ID" HeaderText="Email Id" />

                    </Columns>                  

                    <EditRowStyle BackColor="#999999" />

                    <FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />

                    <HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />

                    <PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />

                    <RowStyle BackColor="#F7F6F3" ForeColor="#333333" />

                    <SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" />

                    <SortedAscendingCellStyle BackColor="#E9E7E2" />

                    <SortedAscendingHeaderStyle BackColor="#506C8C" />

                    <SortedDescendingCellStyle BackColor="#FFFDF8" />

                    <SortedDescendingHeaderStyle BackColor="#6F8DAE" />

                </asp:GridView>

                    </td>               

                </tr>

                <tr>

                    <td>

                         <asp:Button ID="btnExportToWord" runat="server" Text="Export To MS Word FIle" OnClick="btnExportToWord_Click" />                

                    </td>

                </tr>

            </table>

        </fieldset>

C#.Net Code to Bind and Export GridView data to Ms word file

First include the following namespaces

using System.Data;

using System.Data.SqlClient;

using System.Configuration;

using System.Net;

using System.Net.Mail;

using System.Web.UI.HtmlControls;

using System.IO;

using System.Text;

Then write the code as:

SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["conStr"].ConnectionString);

    protected void Page_Load(object sender, EventArgs e)

    {

        if (!Page.IsPostBack)

        {

            BindEmpGrid();

        }

    }

    public override void VerifyRenderingInServerForm(Control control)

    {

        //It solves the error "Control 'GridView1' of type 'GridView' must be placed inside a form tag with runat=server."

    }

    protected void BindEmpGrid()

    {

        SqlCommand cmd = new SqlCommand("select * from EMPLOYEE", con);

        DataTable dt = new DataTable();

        SqlDataAdapter adp = new SqlDataAdapter(cmd);

        adp.Fill(dt);

        grEmp.DataSource = dt;

        grEmp.DataBind();

    }

    protected void btnExportToWord_Click(object sender, EventArgs e)

    {

        try

        {

            Response.ClearContent();

            Response.AddHeader("content-disposition", "attachment;filename=MyWordFile.doc");

            Response.Cache.SetCacheability(HttpCacheability.NoCache);

            Response.Buffer = true;

            Response.Charset = "";

            Response.ContentType = "application/vnd.word";

            StringWriter strWrite = new System.IO.StringWriter();

            HtmlTextWriter htmWrite = new HtmlTextWriter(strWrite);        

            HtmlForm htmfrm = new HtmlForm();

            grEmp.Parent.Controls.Add(htmfrm);

            grEmp.AllowPaging = false;

            htmfrm.Attributes["runat"] = "server";

            htmfrm.Controls.Add(grEmp);

            htmfrm.RenderControl(htmWrite);       

            Response.Write(strWrite.ToString());

            Response.Flush();

            Response.End();

        }

        catch (Exception ex){}      

    }

VB.Net Code to Bind and Export GridView data to Ms word file

First import the following namespaces

Imports System.Data

Imports System.Data.SqlClient

Imports System.Configuration

Imports System.Net

Imports System.Net.Mail

Imports System.Web.UI.HtmlControls

Imports System.IO

Imports System.Text

Then write the code as:

  Dim con As New SqlConnection(ConfigurationManager.ConnectionStrings("conStr").ConnectionString)

    Protected Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load

        If Not Page.IsPostBack Then

            BindEmpGrid()

        End If

    End Sub

    Public Overrides Sub VerifyRenderingInServerForm(control As Control)

        'It solves the error "Control 'GridView1' of type 'GridView' must be placed inside a form tag with runat=server."

    End Sub

    Protected Sub BindEmpGrid()

        Dim cmd As New SqlCommand("select * from EMPLOYEE", con)

        Dim dt As New DataTable()

        Dim adp As New SqlDataAdapter(cmd)

        adp.Fill(dt)

        grEmp.DataSource = dt

        grEmp.DataBind()

    End Sub

    Protected Sub btnExportToWord_Click(sender As Object, e As EventArgs)

        Try

            Response.ClearContent()

            Response.AddHeader("content-disposition", "attachment;filename=MyWordFile.doc")

            Response.Cache.SetCacheability(HttpCacheability.NoCache)

            Response.Buffer = True

            Response.Charset = ""

            Response.ContentType = "application/vnd.word"

            Dim strWrite As StringWriter = New System.IO.StringWriter()

            Dim htmWrite As New HtmlTextWriter(strWrite)

            Dim htmfrm As New HtmlForm()

            grEmp.Parent.Controls.Add(htmfrm)

            grEmp.AllowPaging = False

            htmfrm.Attributes("runat") = "server"

            htmfrm.Controls.Add(grEmp)

            htmfrm.RenderControl(htmWrite)

            Response.Write(strWrite.ToString())

            Response.Flush()

            Response.[End]()

        Catch ex As Exception

        End Try

    End Sub

• Notice that I have added an overriding function VerifyRenderingInServerForm in the code behind. This is to resolve the error “Control 'GridView1' of type 'GridView' must be placed inside a form tag with runat=server” that may occur on exporting GridView data to MS Excel file or MS Word or PDF or CSV (Comma separated value) file.

Note: To view complete article on why this error occur and how to resolve that error, read my article “How to Solve Error Control 'GridView1' of type 'GridView' must be placed inside a form tag with runat=server”

• Exported gridview data to word file will look like as shown in figure:

click on image to  enlarge

  Now over to you:
"If you like my work; you can appreciate by leaving your comments, hitting Facebook like button, following on Google+, Twitter, Linked in and Pinterest, stumbling my posts on stumble upon and subscribing for receiving free updates directly to your inbox . Stay tuned and stay connected for more technical updates."