Tuesday, July 15, 2014

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

Monday, July 14, 2014

Creating Schema

What is Schema ? 
A database schema is a way to logically group objects such as tables, views, stored procedures etc. Think of a schema as a container of objects.
You can assign a user login permissions to a single schema so that the user can only access the objects they are authorized to access.
Schemas can be created and altered in a database, and users can be granted access to a schema. A schema can be owned by any user, and schema ownership is transferable.
Create New Schema
Goto Security option of desired database -->Right Click --> New  --> Schema
Now in opened window write your schema name and click OK
To check your created schema goto Security ---> Schema.

Now its Time to Add schema to the table.For this goto the properties window of desired table, in schema option change the schema. By default schema is dbo.

To see the effect Refresh Tables the view the result.

Substring Function

Substring Function
For any function there arises three basic question--:
1. What does it mean?
2. Why is this?
3. How it works?
What does it mean?
As we see SubString is a combination of two words "Sub" and "String".
String-: In Computer Science String  means "A set of consecutive characters".
Sub   -: Sub is basically a Latin prefix which has several meaning but the best suitable meaning for here is "part of".
So, Substring means "Part of a string".
Why is this?
So, from definition its clear that the work of substring is to extract a part of string.
How it works?
Syntax : Substring(index,length)
            Here index denotes the postion from where you want to extract the string andlength denotes up to how much character you need to extract.Both contains integer value.
Example :
        int ind = Convert.ToInt32(txtIndex.Text);
        int len = Convert.ToInt32(txtLength.Text);
        Literal1.Text = TextBox1.Text.Substring(ind,len); 

How To get Row Value And Column Value From DataSet

 if(ds.Tables[0].Rows[0][0].ToString()=="1")
        {
            for (int i = 0; i < ds.Tables[0].Columns.Count; i++)
            {
                if (ds.Tables[0].Rows[0][i].ToString() != "")
                {
                    Label l = new Label();
                    l.ID = "ll" + i;
                    l.Text =  "username"+ i;
                    form1.Controls.Add(l);

                }
           
            } 
        }

=============================================================================
string countryName = "USA";
        DataTable dt = new DataTable();
        int id = (from DataRow dr in dt.Rows
                  where (string)dr["CountryName"] == countryName
                  select (int)dr["id"]).FirstOrDefault();

=========================================================================================

query = "SELECT [EnrollmentNo] FROM [vw_NewAttenInfo]";
SqlDataAdapter adp = new SqlDataAdapter(query,con);
DataSet ds = new DataSet();
adp.Fill(ds);
for (int i = 0; i < ds.Tables[0].Rows.Count;i++)
{
…………
…………
string st = ds.Tables[0].Rows[i]["EnrollmentNo"].ToString();
……
……
}

=======================================================================================