Friday, June 29, 2012

How to search record in DataTable?


DataTable-

DataTable is server side object in .Net which is collection of Rows and Columns. It represents the in-memory table's data. It can hold data of any single table but using join we can hold data from multiple table. In programming it is used to hold data after getting data from database.


Generally we use DataTable to hold result-set (query's result), but in some of the cases we want to perform searching operation on DataTable. Here I am explaining -
How to search record in DataTable?

If we want to search any data in DataTable object then it can be done by using looping, but this is not a good approach because DataTable have some inbuilt methods to complete this task.


There are following methods-


1. Find () 
- Using this method we can perform searching on DataTable based on Primary  key, it   
                    returns DataRow object.

2. Select()
- Using this method we can perform searching on DataTable based on search criteria   
                    means by using column name we can search , it returns array of DataRow object.

Here I am explaining both methods, Follow this code-

 
 private void btnSearch_Click(object sender, EventArgs e)
        {
            try
            {
                //Creating DataTable object
                DataTable dtRecords = new DataTable();

                //Creating DataColumn object
                DataColumn colName = new DataColumn("Name");
                DataColumn colID = new DataColumn("ID");

                //Adding DataColumns to DataTable
                dtRecords.Columns.Add(colID);
                dtRecords.Columns.Add(colName);

                //Creating Array of DataColumn to set primary key for DataTable
                DataColumn[] dataColsID = new DataColumn[1];
                dataColsID[0] = colID;

                //Setting primary key
                dtRecords.PrimaryKey = dataColsID;

                //Adding static data to DataTable by creating DataRow object
                DataRow row1 = dtRecords.NewRow();
                row1["ID"] = 1;
                row1["Name"] = "Rajendra";
                dtRecords.Rows.Add(row1);

                DataRow row2 = dtRecords.NewRow();
                row2["ID"] = 2;
                row2["Name"] = "Rakesh";
                dtRecords.Rows.Add(row2);

                DataRow row3 = dtRecords.NewRow();
                row3["ID"] = 3;
                row3["Name"] = "Pankaj";
                dtRecords.Rows.Add(row3);

                DataRow row4 = dtRecords.NewRow();
                row4["ID"] = 4;
                row4["Name"] = "Sandeep";
                dtRecords.Rows.Add(row4);

                DataRow row5 = dtRecords.NewRow();
                row5["ID"] = 5;
                row5["Name"] = "Rajendra";
                dtRecords.Rows.Add(row5);

                //Find() Method

                //Finding data in DataTable based on primary key using Find() method
                DataRow FindResult = dtRecords.Rows.Find("3");

                if (FindResult != null)
                {
                   //Showing result to grid
                    DataTable dtTemp = new DataTable();
                    dtTemp = dtRecords.Clone();
                    dtTemp.Rows.Add(FindResult.ItemArray);
                    dataGridFind.DataSource = dtTemp;
                }

               //Select () Method

               //Selecting data from DataTable based on search criteria using Select() method
                DataRow[] SelectResult = dtRecords.Select("name='Rajendra'");

                if (SelectResult != null)
                {
                   //Showing result to grid
                    DataTable dtTemp = SelectResult.CopyToDataTable();
                    dataGridSelect.DataSource = dtTemp;
                }
            }
            catch (Exception)
            {
                //Handle exception here
                throw;
            }
 }


Note -
 

Before using Find () of DataTable it is must that we need to Primary key for DataTable object. Because Find () method works based on primary key value only.

In above code, I have created on DataTable object (dtRecords) using DataTable class, and I have filled this DataTable object with some static records. For this I have used DataColumn and DataRow classes.
I have set Primary key column for DataTable object, so that we can use Find () method over DataTable.

//Setting primary key
 dtRecords.PrimaryKey = dataColsID;

Here  dataColsID is array of DataColumn.
After setting primary key to DataTable, I have Find () method like this-

//Finding data in DataTable based on primary key using Find () method
 DataRow FindResult = dtRecords.Rows.Find("3");


Here Find method will return result as DataRow object, for fetching data from DataTable object using Find () method we need to pass primary key value because based on that it will return result.


For getting data using Select () method, I have written code like this-


//Selecting data from DataTable based on search criteria using Select () method
DataRow[] SelectResult = dtRecords.Select("name='Rajendra'");


Select () method works based on search criteria, Here ("name='Rajendra'“) is search criteria for Select () method, it returns array of DataRow object.


Finally to show result I have used two separate DataGridVIew controls and output will be like this-



Result of Find() and Select() method

As we can see in image, For Find () method, one record in coming because in DataTable object we have only one record with ID=3.
And for Select () Method, 2 records are coming because we have 2 records in DataTable obejct with name = "Rajendra".

To perform search operation on DataTable Find() and Select() methods can be used but both methods have different criteria. 


Note - 

1. If we have primary key in table then Find() and Select() both methods can be used
2. If we don't have primary key then we can use only Select() method.


Thanks


Thursday, June 21, 2012

Error logging using Event log in C#


Error Logging-

 
Error logging is a process of logging or tracking error which comes in run-time. When you we run our application.  It is good practice to log application's error so that user can easily understand the error cause. This is also useful for developer to track the error and resolve those errors.

 .Net provides inbuilt class (EventLog) to log Application events; by including System.Diagnostics Namespace we can use this class to log events or errors. Using EventLog class we can create Error source and Error log.


Here Error source represent to the application which will generate the error. And Error log will hold all error related to that Error source. By this way errors can be easily identified that what is the source of error because it is maintain in one block only. 

In this example, I am showing - How to log error using Event log in C#?

Here is code-

Include this line on top of the form-


using System.Diagnostics;

Now I have defined this function-

//Function to log error

public void LogError(string strError)
        {
            // Creating the source, which will generate error
            if (!EventLog.SourceExists("MyApplication"))
            {
                //Creating log, where error will be logged
                EventLog.CreateEventSource("MyApplication", "MyLog");
            }

            // Creating object of EventLog to log error
            EventLog myAppLog = new EventLog();
            myAppLog.Source = "MyApplication";
         
            //Writing error to log
            myAppLog.WriteEntry(strError);
        }



In above code, I have defined one function (LogError) to log error. Here first I am Checking for existence of source which can be name of application, If it is not present then we need to create it, because under this only logger will be created. Using  CreateEventSource() method, I am creating log (MyLog) under source (MyApplication).

Suppose if source is already exists then directly I am writing entry under created log using WriteEntry(). For writing entry under log first we need to create object of EventLog class.
In this code, I am assigning source to using Source property of EventLog object.

Now to log error I have used this function like this-

private void btnTest_Click(object sender, EventArgs e)
        {
            try
            {
            //Explicitly generating error
                 int a = 10;
                int b = 0;
                a = a / b;
            }
            catch (Exception EX)
            {
              //Calling function to log error
                LogError(EX.Message);
            }
        }


In this code, I am explicitly generating error- DivideByZero . In catch block I have used LogError () to log generated error using EventLog.

Here I am getting generated error using EX.Message property of Exception object and passing to LogError () as string. After executing this lines of code error will be logged in Event Log and output will be-



Error logging
 
 

In output, we can easily see that Here Source is "MyApplication" and Log Name is "MyLog", both are created by code. Here all the errors which will be generated by this application will be logged in same block under "MyApplication" source and "MyLog" log. One application can have more that one Log also, based on category application log can be divided to different log.



Thanks

Wednesday, June 20, 2012

How to send e-mail in ASP.Net?


Asp.Net provides some classes, using these classes we can send e-mail to any destination address.
Basically for sending e-mail there are following classes are required-

1. MailMessage
2. SmtpClient
 

MailMessage Class-
 
This is provides facility to create object for sending e-mail by setting required properties values.
After creating object for MailMessage class we can configure mail object by using following properties-

To    - for setting destination address
From    - for setting source address
Subject - for setting subject of e-mail
Body    - for setting body of e-mail

These all are basic property to send email, based on your  need these properties can be used.
 

SmtpClient Class-
 
This class provides facility to send e-mail by using the Simple Mail Transfer Protocol (SMTP), we can pass MailMessage Class object as a parameter to send a mail.

SmtpClient class has also some properties, which is used for sending e-mail, these properties can be set in web.config file, for this we need to add setting under <system.net> tag.

Like this-
 

<system.net>
    <mailSettings>
    ----------------
    ----------------
    </mailSettings>
</system.net>


Here I am explaining, How to send e-mail in ASP.Net?

In this example, I have used SmtpClient and MailMessage Classes. For this you need to include System.Net.Mail namespace in our code.

Here is code-


Add following code in Web.config file-

   <system.net>
    <mailSettings>
      <smtp deliveryMethod="Network" from="abc@gmail.com">
        <network enableSsl="true"
        host="smtp.gmail.com"
        userName="abc@gmail.com"
        password="12343"
        port="587" />
      </smtp>
    </mailSettings>
  </system.net>

 
This is STMP related setting; these setting will be used while sending e-mail.


Now .cs code-

protected void btnSend_Click(object sender, EventArgs e)
        {
            //Creating MainlMessage object
            MailMessage msgEmail = null;
            try
            {
                msgEmail = new MailMessage();
                msgEmail.IsBodyHtml = true;

                //Adding to address
                if (!string.IsNullOrEmpty(txtEmailTo.Text))
                {
                    msgEmail.To.Add(txtEmailTo.Text);
             
                    //Adding subject
                    msgEmail.Subject = txtSubject.Text;

                    //Adding body
                    msgEmail.Body = txtBody.Text;
          
                   //Creating object of SMTP class
                    SmtpClient smtpEmail = new SmtpClient();

                    //Sending message
                    smtpEmail.Send(msgEmail);

                    Response.Write("<script>alert('Message sent successfully.');</script>");
                }
                else
                {
                    Response.Write("<script>alert('Enter To address.');</script>");
                }
             }
         catch (Exception ex)
            {
                Response.Write("<script>alert('" + ex.Message + "');</script>");
            }
        finally
            {
                msgEmail.Dispose();
            }
        }


In this code, I have created object (msgEmail) of MailMessage class, using properties of this class object I am configuring mail object, after configuration finally I am creating object for SmtpClient class and passing msgEmail object to Send() method for sending  message.

While sending message all the SMTP related settings will be taken from web.config file. We can add SMTP settings in web.config file like I have done, Web.config file contains separate section for this. Under  <mailSettings> section in web.config file all mail setting can be set.


If we are not setting these configurations in web.config file then we have to set this setting by using code. For this we have to use SmtpClient Class of System.Net.Mail Namespace. like this-

SmtpClient objSmtp = new SmtpClient("Host Address");
smtp.Credentials = new NetworkCredential("username", "Password");
smtp.Send(msgEmail);


Here first we need to set Host Address  while creating object of SmtpClient class object, after that we have to set credentials for sending mail, Here  msgEmail is object of MailMessage Class. Finally using Send() method of SmtpClient class object message will be sent.



Thanks

Tuesday, June 19, 2012

How to use JQuery Calendar in ASP.Net?


Asp.Net provides Server side calendar control but because it is server side so it is  heavy weight. Instead of using Server side calendar control we can also use client side calendar control. Using client side functionality doesn’t make any overload for server because this functionality is handled by client side browser. And it is better practice to perform this kind of task in client side only so that server performance can be increased.

In this article, I am explaining, How to use JQuery Calendar in ASP.Net?

For this example I have used JQueryUI. Here first we need to embed this JQueryUI plugin in our application. To configure this, follow these my previous articles -


http://steptodotnet.blogspot.in/2012/06/auto-complete-textbox-using-jquery-and.html
http://steptodotnet.blogspot.in/2011/12/jquery-popup-window-in-aspnet.html

Here you will get How to add JQueryUI plugin to the project. After adding JQuery plug-in see this code-
 

.aspx code-

<head id="Head1" runat="server">
    <script src="Scripts/jquery-1.4.1.min.js" type="text/javascript"></script>
    <script src="Scripts/jquery-ui-1.8.20.custom.min.js" type="text/javascript"></script>
    <link href="Css/jquery-ui-1.8.20.custom.css" rel="stylesheet" type="text/css" />
    <title>Auto complete TextBox</title>
    <script type="text/javascript">
    function ShowCalendar() {
            $("#<%=txtDOJ.ClientID%>").datepicker({
                changeMonth: true,
                changeYear: true
            });
        };
    </script>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <table>
            <tr>
                <td>
                    Enter date of joining:
                </td>
                <td>
                    <asp:TextBox ID="txtDOJ" runat="server"></asp:TextBox>
                </td>
            </tr>
        </table>
    </div>
    </form>
</body>


Here I have defined on client side function-  ShowCalendar(),  In this function I am binding JQuery Calendar to Server side TextBox. For binding JQuery Calendar to server side TextBox, I have used ClientID of TextBox, like this-

  $("#<%=txtDOJ.ClientID%>")

In this example, I have added only 2 parameter to Calendar -

 changeMonth
 changeYear 

based on calendar view, we can add more options to it.

 .cs code-

 protected void Page_Load(object sender, EventArgs e)
    {
        txtDOJ.Attributes.Add("onfocus", "ShowCalendar();");
    }


In this code, I am binding defined client side function to TextBox. For binding client side event to TextBox, I have used Attributes.Add() method, In Add() method I have passed "onfocus" as client side event and  "ShowCalendar()" as client side function.

Now done, when you will set focus on TextBox then output will be like this-
 

JQuery Calendar



JQuery plug-in have different properties or method to display calendar in different format based on our requirement it can be set.   For this we need to set these options while configuring calendar to any textbox, here we can pass parameter for this. 

Note-

For using client side functionality it is must that browser should support this functionality and client script for browser is also true otherwise this functionality will not work.


Thanks

Friday, June 15, 2012

How to change GridView row color on mouseover?


GridView doesn't have any built-in functionality to change color row on mouse-over, for this functionality we need to explicitly write code. This can be done easily using client side code after binding client side functionality to mouse-over and mouse-out events.


Here I am explaining - How to change GridView row color on mouse-over?
 
For this we require mouse-over and mouse-out functionality in client side. I have written following client side functions for this-
 

Client side functions-

<head runat="server">
     <script type="text/javascript">
        //Function to change color of grid row on mouseover
        function mouseIn(row) {
            row.style.backgroundColor = '#D3DFF8';
        }
        //Function to change color of grid row on mouseout
        function mouseOut(row) {
            row.style.backgroundColor = '#FFFFFF';
        }
    </script>
</head>


In above code, I have defined 2 client side functions.
 

1. mouseIn ()
2. mouseOut () 

 
mouseIn() - This function is taking one parameter, using this parameter I am passing row object,
In this function, I am setting style for row object, here I am setting row color value as- #D3DFF8 .

mouseOut() - This function is also taking one parameter, but in this function, I am setting row color value as- #FFFFFF.


Now to make it work, I have bounded these client side functions to GridView row using RowDataBound() event.
 

Here is server side code-

protected void GridEmpData_RowDataBound(object sender, GridViewRowEventArgs e)
    {
        try
        {
            if (e.Row.RowType == DataControlRowType.DataRow)
            {
                //Binding client side functions to GridView row
                e.Row.Attributes.Add("onmouseover", "mouseIn(this);");
                e.Row.Attributes.Add("onmouseout", "mouseOut(this);");
            }
        }
        catch (Exception ex)
        {
            throw;
        }
    }


Now in server side code, I am binding client side functions- mounseIn and mouseOut to GridView's
Row. For this I have used RowDataBound() event of GridView. Here "this" is object which is referring to row of GridView.


Thanks

Thursday, June 14, 2012

How to maintain selected Checkboxes state while paging in GridView?


This is common problem- When we implement paging in GridView then it doesn't maintain selected record when you change page index in GridView.
 

Workaround-   

To solve this problem, we can maintain selected records using ViewState variable and after rebinding Grid we can again make those checkboxes selected. For this we have to maintain code in OnCheckedChanged() and PageIndexChanging() events. 

Here I am explaining - How to maintain selected Check-boxes state while paging in GridView?
 

In this example, I have taken one ViewState["SelectedRows"] variable and one List<string> variable. The purpose of using ViewState["SelectedRows"] variable in this example is that- to maintain selected values in GridView.

There are 2 main steps for this task-

1. While selecting value in GridView, I am string those values in View State variable
2. After rebinding GridView, I am  getting selected values from View State variable and setting in GridView.

Here is code-
 

.aspx code-
 
<table>
            <tr>
                <td>
                    <asp:GridView ID="GridEmpData" runat="server" AutoGenerateColumns="false" Width="300px"
                        AllowPaging="True" PageSize="5" OnPageIndexChanging="GridEmpData_PageIndexChanging">
                        <Columns>
                            <asp:TemplateField HeaderText="Select">
                                <ItemTemplate>
                                    <asp:CheckBox ID="chkEmp" runat="server" OnCheckedChanged="chkEmp_OnCheckedChanged" />
                                </ItemTemplate>
                            </asp:TemplateField>
                            <asp:TemplateField HeaderText="Emp Id">
                                <ItemTemplate>
                                    <asp:Label ID="lblEmpId" runat="server" Text='<%# Eval("id") %>'></asp:Label>
                                </ItemTemplate>
                            </asp:TemplateField>
                            <asp:TemplateField HeaderText="Emp Name">
                                <ItemTemplate>
                                    <asp:Label ID="lblEmpName" runat="server" Text='<%# Eval("name") %>'> </asp:Label>
                                </ItemTemplate>
                            </asp:TemplateField>
                        </Columns>
                    </asp:GridView>
                </td>
            </tr>
        </table>

 
 I have taken one CheckBox inside ItemTemplate of GridView, using this CheckBox records will be selected. I have bounded one server side event to this CheckBox - chkEmp_OnCheckedChanged().
In this event I have written code to store selected record in View State Variable.

The output of this page will be-


Maintaining selected Checkboxes state while paging


Now .cs code-

 
 protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            //Loading data to Grid
            GetData();
        }
    }

    protected void chkInactivate_OnCheckedChanged(object sender, EventArgs e)
    {
        CheckBox chkStatus = (CheckBox)sender;
        GridViewRow selectedrow = (GridViewRow)chkStatus.NamingContainer;

        //Getting selected records from View state
        List<string> selectedItems = null;
        if (ViewState["SelectedRows"] != null)
        {
            selectedItems = (List<string>)ViewState["SelectedRows"];
        }
        else
        {
            selectedItems = new List<string>();
        }

        Label lblEmpId = (Label)selectedrow.FindControl("lblEmpId");

        //If checked then adding to list
        if (chkStatus.Checked)
        {

            selectedItems.Add(lblEmpId.Text);
        }
        //if unchecked then remove from list if exist
        else
        {
            var result = selectedItems.Find(item => item == lblEmpId.Text);

            if (result != null)
            {
                selectedItems.Remove(lblEmpId.Text);
            }
        }

        //Assigning Selected records to ViewState
        ViewState["SelectedRows"] = selectedItems;
    }

    //Function to Fill Grid
    private void GetData()
    {
        try
        {
            SqlConnection cn = new SqlConnection("connection string");
            cn.Open();
            DataSet ds = new DataSet();
            SqlDataAdapter da = new SqlDataAdapter("select id,name  from tablename", cn);
            da.Fill(ds);
            GridEmpData.DataSource = ds;
            GridEmpData.DataBind();
        }
        catch (Exception)
        { 
            throw;
        }
    
    }

    protected void GridEmpData_PageIndexChanging(object sender, GridViewPageEventArgs e)
    {
        GridEmpData.PageIndex = e.NewPageIndex;
      
        //Loading data to Grid
        GetData();

        //Code to maintain selected record while paging
        if (ViewState["SelectedRows"] != null)
        {
            List<string> selectedItems = (List<string>)ViewState["SelectedRows"];
            foreach (GridViewRow row in GridEmpData.Rows)
            {
                Label lblEmpId = (Label)row.FindControl("lblEmpId");
                var result = selectedItems.Find(item => item == lblEmpId.Text);
                if (result != null)
                {
                    CheckBox chk = (CheckBox)row.FindControl("chkEmp");
                    if (chk != null)
                    {
                        chk.Checked = true;
                    }
                }
            }
        }
    }
 


In above code, First I am binding GridView using GetData() function in page_Load() event.

In OnCheckedChanged() event of CheckBox, first I am getting selected record from View State variable, and after that I am adding newly selected record into View State variable. Suppose if any record is already present then I am removing from View State variable, For this operation I am using List<string>, using List<string> records are adding  and deleting.Finally I am assigning List<string> variable to ViewState.

In PageIndexChanging () event, I have written logic for GridView paging, Here I am setting new page index for paging in GridView. In this event only after rebinding GridView, I am getting selected record from View State variable and assign to GridVIew.


Note- It you want to prevent postback then you can make use of AJAX, for this put your GridView inside the UpdatePanel.




Thanks


Wednesday, June 13, 2012

How to redirect to another page after alert message?


This is common requirement that- after doing some server side functionality we want to show alert message and then redirect to another page.

Here I am showing - How to redirect to another page after alert message?

Here is code-
 

Method 1:
 
Response.Write("<script> alert('Your Message.');window.location='TargetpageURL'; </script>");

Method 2:  If you are using Ajax functionality on that case Response.Write() will not work then you can use this code-

  ScriptManager.RegisterClientScriptBlock(this, this.GetType(), "ScriptKey", "alert('Your Message.');window.location='TargetpageURL'; ", true);

Thanks

Tuesday, June 12, 2012

How to get Session value using javascript?


In some of the cases we want to access Session variable value in Client side. but getting session value in client side is bit difficult, because is it server side object which can not be accessed in client side. But even then we can make trick to access Session object value in client side. Here I am explaining easy way to for-

How to get Session value using JavaScript?

To complete this task I have used HiddenField. In this example I have assigned Session object value to HiddenField variable which can be easily accessed from client side.

Here is code-

.aspx code-

 
<head runat="server">
    <title>How to get session value in javascript</title>
    <script type="text/javascript">
        function GetSessionValue() {
            var hdnSession= document.getElementById("<%= hdnSession.ClientID %>");
            alert(hdnSession.value);
            return false;
        }
    </script>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:Button ID="btnGetSession" runat="server" OnClientClick ="return GetSessionValue();" Text="Get Session Value" />
        <asp:HiddenField ID="hdnSession" runat="server" />
    </div>
    </form>
</body>


I above code I have taken one HiddenField Varible, using this HiddenField I will get value of Session variable. To get value of HiddenField I have defined one client side function called- GetValue(). 

.cs code-

Now I have set Session variable value in Page_Load() event-
  

 protected void Page_Load(object sender, EventArgs e)
    {
      //Setting data to Session variable
        Session["Data"]="Jitendra" ;

      //Copying Session data HiddenField variable
        hdnSession.Value = Session["Data"].ToString();
    }

In above code, I have copied some data to Session variable, in next line same data I have copied to HiddenFiled variable, so that I can access this HiddenFiled value from client side.  

Now done, when you will click on button you will get output like this-


Getting Session value using JavaScript.

Thanks


Monday, June 11, 2012

Downloading file using GridView in Asp.Net.



In some of the cases we want to give file listing with download option. Here I am explaining, How to download file using GridView in Asp.Net?

For this task, I have created my Database table like this-
FileDetails(FileId,FileName,FilePath)


Here-

FileId -  Auto generated filed, to maintain primary key for file
FileName - Name of Uploaded file
FilePath - Complete file path for uploaded file
 

In this example, I am designing GridView with three columns, Here FileId and FilePath will be invisible mode, FileName will be displayed to the user. based on user action FileId and FilePath will be fetched from GridView and these values will be used for further processing.  
 

.aspx code-

 <asp:GridView ID="grdFileDetails" runat="server" AutoGenerateColumns="false" OnRowCommand="grdFileDetails_RowCommand">
        <EmptyDataTemplate>
            <div style="color: Red; text-align: center; width: 700px">
                No Data Found.
            </div>
        </EmptyDataTemplate>
        <Columns>
            <asp:TemplateField HeaderText="File ID" ItemStyle-HorizontalAlign="Left">
                <ItemTemplate>
                    <asp:Label ID="lblFileId" runat="server" Text='<%# Eval("FileId") %>'></asp:Label>
                </ItemTemplate>
            </asp:TemplateField>
            <asp:TemplateField HeaderText="File Name" ItemStyle-HorizontalAlign="Left">
                <ItemTemplate>
                    <asp:Label ID="lblFileName" runat="server" Text='<%# Eval("FileName") %>'></asp:Label>
                </ItemTemplate>
            </asp:TemplateField>
            <asp:TemplateField HeaderText="File Path" Visible="false">
                <ItemTemplate>
                    <asp:Label ID="lblFilePath" runat="server" Text='<%# Eval("FilePath") %>'></asp:Label>
                </ItemTemplate>
            </asp:TemplateField>
            <asp:TemplateField HeaderText="Download">
                <ItemTemplate>
                    <asp:Button ID="btnDownLoad" runat="server" Text="Download" CommandName="download"  CommandArgument='<%# Eval("FilePath") %>' />
                </ItemTemplate>
            </asp:TemplateField>
        </Columns>
    </asp:GridView>

 
In above code, I have taken 4 columns in GridView, but here File ID and File Path is hidden, I am not showing File Path and File ID. I am passing FilePath value using CommandArgument property, this value will be used in server side code to download file. For button I have set CommandName= "download" so that button click can be handled in RowCommand() event of GridView.

        <ItemTemplate>
            <asp:Button ID="btnDownLoad" runat="server" Text="Download" CommandName="download"  CommandArgument='<%# Eval("FilePath") %>' />
        </ItemTemplate>

 

The output of this .aspx will be -

Downloading file using GridView.

Now .cs code-

 protected void grdFileDetails_RowCommand(object sender, GridViewCommandEventArgs e)
        {
            try
            {
                //Code to download file
                if (e.CommandName == "download")
                {
                    string filepath = Convert.ToString(e.CommandArgument);

                    byte[] data = System.IO.File.ReadAllBytes(filepath);
                    Response.Clear();
                    Response.ClearHeaders();
                    Response.AddHeader("Content-Type", "Application/octet-stream");
                    Response.AddHeader("Content-Length", data.Length.ToString());
                    Response.AddHeader("Content-Disposition", "attachment; filename=" + filepath);
                    Response.BinaryWrite(data);
                    Response.End();
                }

            }

            catch (Exception)
            {
                 throw;
            }
        }


In above code, I have implemented GridVIew_RowCommand() event , here first I am checking commandName value using e.CommandName.

To get particular file path here I am using e.CommandArgument like this-

   string filepath = Convert.ToString(e.CommandArgument);

 After getting File path I have used Response object to download file like this-
 
   Response.BinaryWrite(data);
 
Here data will contain binary data that will be downloaded as a file. Before download the file it is must that we have to set some settings using Response.AddHeader() method. Here we have to set Content-Type and  Content-Length for downloading object.  Now if you will click on download button then related file will be downloaded.

Thanks

Wednesday, June 6, 2012

How to convert DataTable to HTML Table?


Asp.Net provides different data controls to display data on page but in some cases if you don't want to use those controls then we can simply use Custom table to display data on page. Here I am explaining, How we can convert DataTable (data source) to HTML table?


Asp.Net provides classes like Table, TableRow, TableCell to create table using code, we can easily create table using these classes and we can display data in created table. In this example, I have Used Table, TableRow, TableCell Classes to create Table.

Here is code-

protected void btnGetData_Click(object sender, EventArgs e)
    {
        //Code to get data from database
        SqlConnection cn = new SqlConnection("Connection string");
        cn.Open();
        DataTable dt = new DataTable();
        SqlDataAdapter da = new SqlDataAdapter("select ID,Name from tabEmployee", cn);
        da.Fill(dt);
      
        //Code to show Data table’s data to HTML table
        Table tbl = new Table();
        tbl.CellPadding = 0;
        tbl.CellSpacing = 0;

       //Flag to add Header for HTML table
        bool AddedColumnName = false;

        //Looping for getting Data table’s data
        foreach (DataRow dtRow in dt.Rows)
        {
            TableRow row = new TableRow();
            foreach (DataColumn col in dt.Columns)
            {
                //Adding heading to HTML table
                if (AddedColumnName == false)
                {
                    TableCell cell = new TableCell();
                    cell.BorderStyle = BorderStyle.Solid;
                    cell.BorderWidth = 2;
                    cell.BorderColor = System.Drawing.Color.Gray;
                    cell.BackColor = System.Drawing.Color.Green;
                    cell.ForeColor = System.Drawing.Color.White;
                    cell.HorizontalAlign = HorizontalAlign.Center;
                    cell.Text = col.ColumnName;
                    row.Cells.Add(cell);
                }
                //Adding data to HTML table
                else
                {
                    TableCell cell = new TableCell();
                    cell.BorderStyle = BorderStyle.Solid;
                    cell.BorderWidth = 2;
                    cell.BorderColor = System.Drawing.Color.Gray;
                    cell.Text = dtRow[col].ToString();
                    row.Cells.Add(cell);
                }
            }

            tbl.Rows.Add(row);
            AddedColumnName = true;
        }

        //Adding created HTML table to Panel control
        pnlData.Controls.Add(tbl);
    }


In this code, I have read data from database and stored on one data source- DataTable.
Now using looping, I have iterated all the data to create HTML table. First I am creating header for the table, for this I have written code like this- 


//Adding heading to HTML table
                if (AddedColumnName == false)
                {
                    TableCell cell = new TableCell();
                    cell.BorderStyle = BorderStyle.Solid;
                    cell.BorderWidth = 2;
                    cell.BorderColor = System.Drawing.Color.Gray;
                    cell.BackColor = System.Drawing.Color.Green;
                    cell.ForeColor = System.Drawing.Color.White;
                    cell.HorizontalAlign = HorizontalAlign.Center;
                    cell.Text = col.ColumnName;
                    row.Cells.Add(cell);
                }


This line of code will add header to the table by getting column names from result set, These column names cab be changed in select query.

Now to add data of result set I have used this code- 

//Adding data to HTML table
                else
                {
                    TableCell cell = new TableCell();
                    cell.BorderStyle = BorderStyle.Solid;
                    cell.BorderWidth = 2;
                    cell.BorderColor = System.Drawing.Color.Gray;
                    cell.Text = dtRow[col].ToString();
                    row.Cells.Add(cell);
                }


This code will be executed for each row in result set except header row. For creating HTML table, I have used Table, TableRow and TableCell classes, in foreach loop for every row in DataTable I am creating object for TableRow class and for every column object for TableCell class.

To add data to HTML Table cell using this code-

row.Cells.Add(cell);

Here "row" is object of TableRow and "cell" is object of TableCell class.
 
Output-



DataTable to HTML Table


Thanks

Auto complete TextBox using jquery and Database.


Auto complete TextBox is common for most of the application, but there are different ways to implement this functionality. Here I am explaining this functionality using JQuery and Database. In this example, I am implementing Auto complete functionality for "Name" field in database table. I have used following JavaScript and CSS files to complete this functionality.

JavaScript Files-
jquery-1.4.1.min.js
jquery-ui-1.8.20.custom.min.js

CSS Files-
jquery-ui-1.8.20.custom.css

Now Include these files in your application like this-


Solution Exlporer


Make same directory structure as I have shown you in image.

Now design your .aspx page like this-
 

AutoComplete.aspx

<head runat="server">
    <script src="Scripts/jquery-1.4.1.min.js" type="text/javascript"></script>
    <script src="Scripts/jquery-ui-1.8.20.custom.min.js" type="text/javascript"></script>
    <link href="Css/jquery-ui-1.8.20.custom.css" rel="stylesheet" type="text/css" />
    <title>Auto complete TextBox</title>
    <script type="text/javascript">
        function AutoComplete() {
            var names = $('#<%= hdnNames.ClientID %>').val();
            if (names != null && names != "") {
                var arrOfNames = names.split("?");
                $("#<%=txtName.ClientID %>").autocomplete({
                    source: arrOfNames
                });
            }
        }
    </script>
</head>

<body>
    <form id="form1" runat="server">
    <div>
        <table>
            <tr>
                <td>
                    Enter Name:
                </td>
                <td>
                    <asp:TextBox ID="txtName" runat="server"></asp:TextBox>
                    <asp:HiddenField ID="hdnNames" runat="server" />
                </td>
            </tr>
        </table>
    </div>
    </form>
</body>
</html>



In this page I have taken one HiddenField, why- After reading all the names from table I am concatenating names with separator character "?" and  assigning to  HiddenField.

Now in script code (AutoComplete() function ), I am implementing functionality for auto complete TextBox  after splitting value of HiddenField.
 

Server side code-

protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            GetNames();
            ClientScript.RegisterStartupScript(typeof(Page), "AutoComplete", "AutoComplete();",true);
        }
    }
    private void GetNames()
    {
        try
        {
            SqlConnection con = new SqlConnection("Connection string");
            con.Open();
            DataSet ds = new DataSet();
            SqlDataAdapter da = new SqlDataAdapter("select name from employee", con);
            da.Fill(ds);
            if (ds!=null)
            {
                foreach (DataRow row in ds.Tables[0].Rows)
                {
                    //concatenating value with separator character -"?"
                    hdnNames.Value = hdnNames.Value + row[0].ToString() + "?"; 
                }
            }
        }
        catch (Exception)
        {  
            throw;
        }
    }


In server side code, I have defined one function (GetNames()) to get names form employee table and after concatenating assign   to HiddenField value. The main backbone for this functionality is implementing by using this line-


ClientScript.RegisterStartupScript(typeof(Page), "AutoComplete", "AutoComplete();",true);

Using this line, I am binding Auto complete feature to txtName textBox.

Now done, After running the application output will be like this-



Auto complete textBox



Thanks.