Insert and Download files in sql server using asp.net
Insert and Download files in sql server using asp.net
Step 1:Databse deign
CREATE TABLE [dbo].[Test_File](
[Id] [int] IDENTITY(1,1) NOT NULL,
[FileName] [varchar](50) NULL,
[FileType] [varchar](50) NULL,
[Data] [varbinary](max) NULL
) ON [PRIMARY]
Step :2 : Design web page with controls
<form id="form1" runat="server">
<div>
<asp:FileUpload ID="fileUpload1" runat="server" /><br />
<asp:Button ID="btnUpload" runat="server" Text="Upload" onclick="btnUpload_Click" BackColor="YellowGreen"/>
</div>
<div>
<asp:GridView ID="gr" runat="server" AutoGenerateColumns="false" DataKeyNames="Id" AlternatingRowStyle-BackColor="WhiteSmoke" Width="40%">
<HeaderStyle BackColor="#3366ff" Font-Bold="true" ForeColor="White" />
<Columns>
<asp:BoundField DataField="Id" HeaderText="Id" />
<asp:BoundField DataField="FileName" HeaderText="FileName" />
<asp:TemplateField HeaderText="FilePath">
<ItemTemplate>
<asp:LinkButton ID="lnk" runat="server" Text="Download" OnClick="lnk_Click"></asp:LinkButton>
</ItemTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>
</div>
</form>
Step :3 :Logic On .cs Page
string strCon = "Data
Source=.\\SQLEXPRESS;Initial Catalog=xyz;User ID=sa;Password=pass@123";
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
BindGridviewData();
}
}
// Bind
Gridview Data
private void BindGridviewData()
{
using (SqlConnection con = new SqlConnection(strCon))
{
using (SqlCommand cmd = new SqlCommand())
{
cmd.CommandText = "select * from Test_File";
cmd.Connection = con;
con.Open();
gr.DataSource =
cmd.ExecuteReader();
gr.DataBind();
con.Close();
}
}
}
protected void btnUpload_Click(object sender, EventArgs e)
{
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 (SqlConnection con = new SqlConnection(strCon))
{
using (SqlCommand cmd = new SqlCommand())
{
cmd.CommandText = "insert into Test_File(FileName,FileType,FileData)
values(@Name,@Type,@Data)";
cmd.Parameters.AddWithValue("@Name",
filename);
cmd.Parameters.AddWithValue("@Type", "application/word");
cmd.Parameters.AddWithValue("@Data", size);
cmd.Connection = con;
con.Open();
cmd.ExecuteNonQuery();
con.Close();
BindGridviewData();
}
}
}
protected void lnk_Click(object sender, EventArgs e)
{
LinkButton lnkbtn = sender as LinkButton;
GridViewRow gvrow = lnkbtn.NamingContainer as GridViewRow;
int fileid = Convert.ToInt32(gr.DataKeys[gvrow.RowIndex].Value.ToString());
string name, type;
using (SqlConnection con = new SqlConnection(strCon))
{
using (SqlCommand cmd = new SqlCommand())
{
cmd.CommandText = "select FileName, FileType, FileData from Test_File
where Id=@Id";
cmd.Parameters.AddWithValue("@id",
fileid);
cmd.Connection = con;
con.Open();
SqlDataReader dr = cmd.ExecuteReader();
if (dr.Read())
{
Response.ContentType = dr["FileType"].ToString();
Response.AddHeader("Content-Disposition", "attachment;filename=\"" + dr["FileName"] + "\"");
Response.BinaryWrite((byte[])dr["FileData"]);
Response.End();
}
}
}
}
It will look like :
Comments
Post a Comment