Tuesday, July 15, 2014

Return All Dates of Selected Month

return all dates of a selected month.
So, here is the function solving this purpose -:

CREATE FUNCTION GetAllDateOfMonth
(   
    @dbDate datetime
)
RETURNS @AllDates TABLE
(
 GenDate datetime not null
)
AS
BEGIN
    DECLARE @monthNo int;
    -- Set Month no of Selected Date
    SET @monthNo = datepart(MM, @dbDate);

    -- Set first day of month
    SET @dbDate = convert(datetime, convert(varchar,datepart(yy,@dbDate)) + '.' + convert(varchar,@monthNo) + '.01 00:00:00');

     WHILE datepart(MM,@dbDate) = @monthNo
     BEGIN
      INSERT INTO @AllDates VALUES (@dbDate);
      SET @dbDate = dateadd(dd, 1, @dbDate);
     END
   
    RETURN

END

==============================================================
And this is the implementation of this function -:
SELECT * FROM [dbo].[GetAllDateOfMonth] (GETDATE())

And the Output will be -

Send GridView in Mail in asp.net C#

This example makes u enable to understand how to email server controls information like Gridview as it is.
Now I am considering that we have a web form which contains a GridView (containing Data) and a button
which will be used to send email.
At first include these namespaces to your code behind.
using System.Net.Mail;
using System.Text;
using System.IO;


Now in Button Click event write this code :
protected void ibMail_Click(object sender, ImageClickEventArgs e)
    {
        string to = "anukana@symtechindia.net";
        string From = "mafire5@gmail.com";
        string subject = "Balance Detail";
        string Body = "Dear sir ,<br> Plz Check d Attachment <br><br>";
        Body += GridViewToHtml(gvPArtyStatement); //Elaborate this function detail later
        Body += "<br><br>Regards,<br>Anukana";
        bool send = send_mail(to, From, subject, Body);//Elaborate this function detail later
        if (send == true)
        {
            string CloseWindow = "alert('Mail Sent Successfully!');";
            ClientScript.RegisterStartupScript(this.GetType(), "CloseWindow", CloseWindow, true);
        }
        else
        {
            string CloseWindow = "alert('Problem in Sending mail...try later!');";
            ClientScript.RegisterStartupScript(this.GetType(), "CloseWindow", CloseWindow, true);
         }
    }

send_mail() Definition :

public bool send_mail(string to, string from, string subject, string body)
    {
            MailMessage msg = new MailMessage(from, to);
            msg.Subject = subject;
            AlternateView view;
            SmtpClient client;
            StringBuilder msgText = new StringBuilder();
            msgText.Append(" <html><body><br></body></html> <br><br><br>  " + body);
            view = AlternateView.CreateAlternateViewFromString(msgText.ToString(), null, "text/html");

            msg.AlternateViews.Add(view);
            client = new SmtpClient();
            client.Host = "smtp.gmail.com";
            client.Port = 587;
            client.Credentials = new System.Net.NetworkCredential("jhalpharegi@gmail.com", "Your_password");
            client.EnableSsl = true; //Gmail works on Server Secured Layer
            client.Send(msg);
            bool k = true;
            return k;
   }
 GridViewToHtml() definition : 
private string GridViewToHtml(GridView gv)
    {
        StringBuilder sb = new StringBuilder();
        StringWriter sw = new StringWriter(sb);
        HtmlTextWriter hw = new HtmlTextWriter(sw);
        gv.RenderControl(hw);
        return sb.ToString();
    }
    public override void VerifyRenderingInServerForm(Control control)
    {
         //Confirms that an HtmlForm control is rendered for the specified ASP.NET server control at run time.
    }

Upload And Remove Files From Table Dynamically onitemcommand & onitemdatabound

This example shows how to add / remove rows in a grid
simoltaneously. To implement this I have used Repeater Control and DataTable where DataTable is used to store the values entered runtime also remove data if remove command will be passed and Repeater is used to show the data .Final output will be like this one 

For this add a repeater control on your aspx and  fire properties "onitemcommand " &" onitemdatabound".
DESIGN
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default2.aspx.cs" Inherits="Default2" %>

<!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>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:Repeater ID="Repeater1" runat="server" OnItemCommand="Repeater1_ItemCommand"
            OnItemDataBound="Repeater1_ItemDataBound">
            <HeaderTemplate>
                <table style="border: thin dotted #0000FF; width: 950px">
                    <tr>
                        <th>
                            File Category
                        </th>
                        <th>
                            File Access
                        </th>
                        <th>
                            File
                        </th>
                        <th>
                            Description
                        </th>
                        <th>
                            &nbsp;
                        </th>
                    </tr>
            </HeaderTemplate>
            <ItemTemplate>
                <tr>
                    <td>
                        <asp:DropDownList ID="ddlFileCat" runat="server">
                        </asp:DropDownList>
                    </td>
                    <td>
                        <asp:RadioButtonList ID="rblFileAccsess" runat="server" RepeatDirection="Horizontal">
                            <asp:ListItem>Public</asp:ListItem>
                            <asp:ListItem>Private</asp:ListItem>
                        </asp:RadioButtonList>
                    </td>
                    <td>
                        <asp:Label ID="lblFileNm" runat="server" Text='<%#DataBinder.Eval(Container.DataItem,"FileNm") %>'></asp:Label>
                        <asp:FileUpload ID="FileUpload1" runat="server" />
                    </td>
                    <td>
                        <asp:TextBox ID="txtFileDesc" runat="server" Text='<%#DataBinder.Eval(Container.DataItem,"FileDesc") %>'></asp:TextBox>
                    </td>
                    <td>
                        <asp:Button ID="btnAddAnother" runat="server" Text='<%#DataBinder.Eval(Container.DataItem,"Button") %>'
                            CommandName='<%#DataBinder.Eval(Container.DataItem,"Button") %>' />
                    </td>
                </tr>
            </ItemTemplate>
            <FooterTemplate>
                </table>
            </FooterTemplate>
        </asp:Repeater>
    </div>
    </form>
</body>
</html>

 Repeater's ItemDataBound property is used to bind dropdownlist form database and to show the value as selected runtime.

protected void Repeater1_ItemDataBound(object sender, RepeaterItemEventArgs e)
    {

        if (e.Item.ItemType == ListItemType.Item || e.Item.ItemType == ListItemType.AlternatingItem)
        {
            DropDownList ddl = (DropDownList)e.Item.FindControl("ddlFileCat");
            RadioButtonList rbl = (RadioButtonList)e.Item.FindControl("rblFileAccsess");

            SqlDataAdapter adp = new SqlDataAdapter("SELECT [CategoryID],[CatName] FROM [dbo].[MST_FileCategory]", con);
            DataSet ds = new DataSet();
            adp.Fill(ds);

            ddl.DataSource = ds;
            ddl.DataTextField = "CatName";
            ddl.DataValueField = "CategoryID";
            ddl.DataBind();
            ddl.SelectedValue = dtTemp.Rows[i]["FileCat"].ToString();
            rbl.SelectedValue = dtTemp.Rows[i]["FileAccess"].ToString();
            i++;

        }
        //   ddl.SelectedValue = Eval("FileCat").ToString();
    }
 To Add or Remove rows from Datatable we will use ItemCommand from where we will pass the Command ADD or REMOVE.
protected void Repeater1_ItemCommand(object source, RepeaterCommandEventArgs e)
    {
        //int cnt = Repeater1.Items.Count; //get total row count in repeater
        if (e.CommandName == "Add")
        {
            //  cnt++;
            AddColumns();

            foreach (RepeaterItem item in Repeater1.Items)
            {
                //Getting the value of fields
                FileUpload fu = (FileUpload)item.FindControl("FileUpload1");
                Label fileName = (Label)item.FindControl("lblFileNm");
                Button btnAdd = (Button)item.FindControl("btnAddAnother");

                if (btnAdd == e.CommandSource)
                {
                    if (fu.HasFile)
                    {
                        string path = Server.MapPath("~/Docs/");
                        fileName.Text = fu.FileName;
                        fu.SaveAs(path + fileName.Text);
                    }
                }

                string fileCat = ((DropDownList)item.FindControl("ddlFileCat")).SelectedValue;
                string fileAccess = ((RadioButtonList)item.FindControl("rblFileAccsess")).SelectedValue;
                string fileNm = ((Label)item.FindControl("lblFileNm")).Text;
                string fileDesc = ((TextBox)item.FindControl("txtFileDesc")).Text;

                //now chnage button text to remove & save values in table
                dtTemp.Rows.Add(fileCat, fileAccess, fileNm, fileDesc, "Remove");
            }
            //Add dummy rows bcoz we neeed to increase
            dtTemp.Rows.Add("", "", "", "", "Add");
            BindWithRepeater();
        }
        else if (e.CommandName == "Remove")
        {
            // cnt--;
            AddColumns();
            foreach (RepeaterItem item in Repeater1.Items)
            {
                Button btnAdd = (Button)item.FindControl("btnAddAnother");
                if (btnAdd != e.CommandSource)
                {
                    string fileCat = ((DropDownList)item.FindControl("ddlFileCat")).SelectedValue;
                    string fileAccess = ((RadioButtonList)item.FindControl("rblFileAccsess")).SelectedValue;
                    string fileNm = ((Label)item.FindControl("lblFileNm")).Text;
                    string fileDesc = ((TextBox)item.FindControl("txtFileDesc")).Text;

                    if (btnAdd.Text == "Remove")
                    {
                        dtTemp.Rows.Add(fileCat, fileAccess, fileNm, fileDesc, "Remove");
                    }
                    else
                    {
                        dtTemp.Rows.Add(fileCat, fileAccess, fileNm, fileDesc, "Add");
                    }
                }

            }
            BindWithRepeater();
        }

    }
 AddColumns() and BindWithRepeater() are user defined functions.Define this functions also and call them in pageload.
public void AddColumns()
    {
        dtTemp.Columns.Add("FileCat");
        dtTemp.Columns.Add("FileAccess");
        dtTemp.Columns.Add("FileNm");
        dtTemp.Columns.Add("FileDesc");
        dtTemp.Columns.Add("Button");
    }
    public void BindWithRepeater()
    {
        //Bind this Dataset to repeater
        Repeater1.DataSource = dtTemp;
        Repeater1.DataBind();
    }

    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            AddColumns();
            dtTemp.Rows.Add("", "", "", "", "Add");
            BindWithRepeater();
        }
    }