Research in .NET: August 2013

Monday 5 August 2013

upload a file in database and download from database

string filename = Path.GetFileName(FileUpload1.PostedFile.FileName);
                Stream str = FileUpload1.PostedFile.InputStream;
                BinaryReader br = new BinaryReader(str);
                Byte[] size = br.ReadBytes((int)str.Length);
                using (con = new MySqlConnection(ss))
                {
                    con.Open();
                    using (cmd = new MySqlCommand("insert into referalprogram(Company_Id,File_Name,Referal_Description,File_Type,File_Data,Add_User_Id,Add_Date)values(@cid,@fname,@rd,@ftype,@fdata,@uid,@adddate)", con))
                    {
                        cmd.Parameters.Add(new MySqlParameter("@cid", Session["cid"]));
                        cmd.Parameters.Add(new MySqlParameter("@fname", filename));
                        cmd.Parameters.Add(new MySqlParameter("@rd", TextBox1.Text));
                        cmd.Parameters.Add(new MySqlParameter("@ftype", "application/word"));
                        cmd.Parameters.Add(new MySqlParameter("@fdata", size));
                        cmd.Parameters.Add(new MySqlParameter("@uid", Session["adduid"]));
                        cmd.Parameters.Add(new MySqlParameter("@adddate", curr));
                        cmd.ExecuteNonQuery();
                        con.Close();
                        TextBox1.Text = "";
                        Label4.Text = "Data has been Uploaded.";
                    }
                }

to download by clicking on a button in gridview

protected void GridView1_RowCommand(object sender, GridViewCommandEventArgs e)
        {
            if (e.CommandName == "Download")
            {
                int id=Convert.ToInt32(e.CommandArgument);
                //LinkButton lnkbtn = sender as LinkButton;
                //GridViewRow gvrow = lnkbtn.NamingContainer as GridViewRow;
                //int fileid = Convert.ToInt32(GridView1.DataKeys[gvrow.RowIndex].Value.ToString());
                //string name, type;
                using ( con = new MySqlConnection(ss))
                {
                    con.Open();
                    using (cmd = new MySqlCommand("select File_Name, File_Type, File_Data from referalprogram where Ref_Program_Id=@Id",con))
                    {
                        cmd.Parameters.Add(new MySqlParameter("@id", id));
                        dr = cmd.ExecuteReader();
                        if (dr.Read())
                        {
                            Response.ContentType = dr["File_Type"].ToString();
                            Response.AddHeader("Content-Disposition", "attachment;filename=\"" + dr["File_Name"] + "\"");
                            Response.BinaryWrite((byte[])dr["File_Data"]);
                            Response.End();
                        }
                    }
                }
            }
        }

highlight certain dates selected by dataReader from database

<!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>

    </div>
    <asp:DropDownList ID="DropDownList1" runat="server" AppendDataBoundItems="True"
        AutoPostBack="True" DataSourceID="SqlDataSource1" DataTextField="Vehicle"
        DataValueField="Vehicle" Height="17px"
        onselectedindexchanged="DropDownList1_SelectedIndexChanged" Width="144px">
        <asp:ListItem Selected="True">Select vehicle</asp:ListItem>
    </asp:DropDownList>
    <asp:SqlDataSource ID="SqlDataSource1" runat="server"
        ConnectionString="<%$ ConnectionStrings:rentcarConnectionString %>"
        SelectCommand="SELECT DISTINCT [Vehicle] FROM [booking]">
    </asp:SqlDataSource>
    <asp:Calendar ID="Calendar1" runat="server" BackColor="#FFFFCC"
        BorderColor="#FFCC66" BorderWidth="1px" DayNameFormat="Shortest"
        Font-Names="Verdana" Font-Size="8pt" ForeColor="#663399" Height="336px"
        ondayrender="Calendar1_DayRender" ShowGridLines="True" Width="902px">
        <DayHeaderStyle BackColor="#FFCC66" Font-Bold="True" Height="1px" />
        <NextPrevStyle Font-Size="9pt" ForeColor="#FFFFCC" />
        <OtherMonthDayStyle ForeColor="#CC9966" />
        <SelectedDayStyle BackColor="#CCCCFF" Font-Bold="True" />
        <SelectorStyle BackColor="#FFCC66" />
        <TitleStyle BackColor="#990000" Font-Bold="True" Font-Size="9pt"
            ForeColor="#FFFFCC" />
        <TodayDayStyle BackColor="#FFCC66" ForeColor="White" />
    </asp:Calendar>
    <asp:DropDownList ID="DropDownList2" runat="server">
    </asp:DropDownList>
    <asp:Label ID="Label1" runat="server" Text="Label"></asp:Label>
    </form>
</body>
</html>

Coding

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

public partial class Default14 : System.Web.UI.Page
{
    public string ss;

    protected void Page_Load(object sender, EventArgs e)
    {
        ss = ConfigurationManager.ConnectionStrings["rentcarConnectionString"].ConnectionString;
    }
    protected void DropDownList1_SelectedIndexChanged(object sender, EventArgs e)
    {
        DropDownList2.Items.Clear();
        SqlConnection con = new SqlConnection(ss);
        con.Open();
        SqlCommand cmd = new SqlCommand("select * from booking where Vehicle='" + DropDownList1.SelectedValue.ToString() + "'", con);
        SqlDataReader dr = cmd.ExecuteReader();
        while (dr.Read())
        {
            string sd = dr.GetValue(2).ToString();

            //Label1.Text = "sd;
            List<string> lists = new List<string>();
            lists.Add(sd);
            foreach (string s in lists)
            {
                DropDownList2.Items.Add(s);
                Calendar1.SelectedDates.Add(Convert.ToDateTime(s));
            }
        }
        dr.Close();
        con.Close();
    }
    protected void Calendar1_DayRender(object sender, DayRenderEventArgs e)
    {

            if (e.Day.IsSelected)
            {
                e.Cell.Text = "booked";
            }

    }
}

to perform any action on selecting date then code

protected void Calendar1_SelectionChanged(object sender, EventArgs e)
    {
        string sds =Calendar1.SelectedDate.ToShortDateString();

    }