Upload Files And Save In Oracle Database In Blob Format In ASP.NET

Overview

Today, we will see how to upload files, whether it's a Word file, pdf, zip files etc., and save them in the Oracle database as well as retrieve those files and download those. This code is useful when you need to upload various documents in an organization, including process documents, news and so on. Other users will have to download those files and see the contents. To see the content of what you have uploaded, you have to save it in Binary format. Let's start:

Step 1 : Lets create a table first

First, you need to install the Oracle Database on your machine and create databases in your system. The Oracle SQL Developer is a GUI to access those databases.

Once you are through with the installation and creation of the database, it is time to add a connection.

Note:

Before installing Oracle, you need to add TNS names in tnsnames.ora in order to communicate with your databases.

Go to your system drive where you had installed the Oracle. This is my path:

path

Open tnsname.ora file and add a TNS entry there.

code

Now, configure the connection in your Oracle SQL Developer.

SQL

Click on Connect. It will add a connection successfully. We have connected to our database successfully .

Now, let's create a table.

table

Look at the Data type in Oracle carefully. I made ID as primary key. Oracle doesn’t identify the GUI. So, you have to write a query for this .

The name I gave is Varchar2 with a length of 4000 Bytes (maximum).
Content type is again varchar2 which is again 4000 Bytes long (maximum).

Data is stored in BLOB. ( Remember in the previous article in SQL server we had been storing data in Binary Format). Here, in Oracle, we will be saving Data in BLOB .

Once we have created the table, it's time to create id as autoincrement.

Just type this command,

command

It will create a sequence, doc_id. Now, just see by selecting the statement if your sequence has been created or not .

command

Step 2 : Open Visual Studio

Open Visual Studio File->New Website.

Select ASP.NET Empty Website and Give suitable name as DocumentSaveInBinary,

Now, let's create FileUpload Control as,

code

<asp:FileUpload ID="FileUpload1" runat="server" />
<asp:Button ID="btnUpload" runat="server" Text="Upload" OnClick="Upload" CssClass="btn-primary" />

Now, create Gridview with Download Link button, so that we can download respective documents or Files .

code

<asp:GridView ID="GridView1" runat="server"
AutoGenerateColumns="false" CssClass="table">
<Columns>
<asp:BoundField DataField="Name" HeaderText="File Name" />
<asp:TemplateField ItemStyle-HorizontalAlign="Center">
<ItemTemplate>
<asp:LinkButton ID="lnkDownload" runat="server" Text="Download" OnClick="DownloadFile"
CommandArgument='<%# Eval("Id") %>'></asp:LinkButton>
</ItemTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>

Here you will see that inside Gridview used <asp:BoundField/> Showing HeaderText as FileName in gridview. In that <Itemtemplate></Itemtemplate> Inside Itemtemplate you need to bind Link button with ID=”lnkDownload” OnClick=”DownloadFile”.

So my final Document.aspx code is,

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="DocumentUpload.aspx.cs" Inherits="_Default" %>
<!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 id="Head1" runat="server">
<title></title>
<link rel="Stylesheet" href="Styles/bootstrap.min.css" style="" />
<link rel="Stylesheet" href="Styles/bootstrap.css" />
</head>
<body>
<form id="form1" runat="server">
<div class="container">
<asp:FileUpload ID="FileUpload1" runat="server" />
<asp:Button ID="btnUpload" runat="server" Text="Upload" OnClick="Upload" CssClass="btn-primary" />
<hr />
<asp:GridView ID="GridView1" runat="server"
AutoGenerateColumns="false" CssClass="table">
<Columns>
<asp:BoundField DataField="Name" HeaderText="File Name" />
<asp:TemplateField ItemStyle-HorizontalAlign="Center">
<ItemTemplate>
<asp:LinkButton ID="lnkDownload" runat="server" Text="Download" OnClick="DownloadFile"
CommandArgument='<%# Eval("Id") %>'></asp:LinkButton>
</ItemTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>
</div>
</form>
</body>
</html>

Step 3 : Now let's see CS Code Part

Our File Upload Code is :

code

As you see in the above code, we are saving our table FileName,Contentype; here the content types are word, pdf, image and so on and then we are saving the posted file in binary format by using Stream. File which you had uploaded in Fileupload Control and is converted in the binary file through BinaryReader .

Here, I have added a connection string in the code .

Now, while adding, you need to insert the id. See, what I had inserted: sequencename.next val

When we were inserting in SQL, we were using “@” . Here, in Oracle, we are using “:”:

string constr = "User ID=axis_nsdl;Password=a$xis_4321;Data Source=TSTDPSEC";
using (OracleConnection con = new OracleConnection(constr))
{
string query = "insert into tblFiles values (doc_id.nextval,:Name, :ContentType, :Data)";
using (OracleCommand cmd = new OracleCommand(query))
{
cmd.Connection = con;
// cmd.Parameters.AddWithValue(":id", Number);
cmd.Parameters.AddWithValue(":Name", filename);
cmd.Parameters.AddWithValue(":ContentType", contentType);
cmd.Parameters.AddWithValue(":Data", bytes);
con.Open();
cmd.ExecuteNonQuery();
con.Close();
}
}
}
}

Similarly, we will write the code for download, as we had created Onclick in gridview as Download File

Now, we Will Bind the gridView :

So my Final CS Code is
1. using System;
2. using System.Collections.Generic;
3. using System.Linq;
4. using System.Web;
5. using System.Data;
6. using System.Data.SqlClient;
7. using System.IO;
8. using System.Web.UI.WebControls;
9. using System.Web;
10. using System.Configuration;
11. using System.Data.OracleClient;
13. public partial class _Default : System.Web.UI.Page
14. {
18. private OracleConnection con = new OracleConnection("User ID=axis_nsdl;Password=a$xis_4321;Data Source=TSTDPSEC");
19. protected void Page_Load(object sender, EventArgs e)
20. {
21. BindGrid();
22. }
24. private void BindGrid()
25. {
26. //string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
27. string constr = "User ID=axis_nsdl;Password=a$xis_4321;Data Source=TSTDPSEC";
28. using (OracleConnection con = new OracleConnection(constr))
29. {
30. using (OracleCommand cmd = new OracleCommand())
31. {
32. cmd.CommandText = "select Id, Name from tblFiles";
33. cmd.Connection = con;
34. con.Open();
35. GridView1.DataSource = cmd.ExecuteReader();
36. GridView1.DataBind();
37. con.Close();
38. }
39. }
40. }
41. protected void Upload(object sender, EventArgs e)
42. {
43. string filename = Path.GetFileName(FileUpload1.PostedFile.FileName);
44. string contentType = FileUpload1.PostedFile.ContentType;
45. using (Stream fs = FileUpload1.PostedFile.InputStream)
46. {
47. using (BinaryReader br = new BinaryReader(fs))
48. {
49. byte[] bytes = br.ReadBytes((Int32)fs.Length);
50. string constr = "User ID=axis_nsdl;Password=a$xis_4321;Data Source=TSTDPSEC";
51. using (OracleConnection con = new OracleConnection(constr))
52. {
53. string query = "insert into tblFiles values (doc_id.nextval,:Name, :ContentType, :Data)";
54. using (OracleCommand cmd = new OracleCommand(query))
55. {
56. cmd.Connection = con;
57. // cmd.Parameters.AddWithValue(":id", Number);
58. cmd.Parameters.AddWithValue(":Name", filename);
59. cmd.Parameters.AddWithValue(":ContentType", contentType);
60. cmd.Parameters.AddWithValue(":Data", bytes);
61. con.Open();
62. cmd.ExecuteNonQuery();
63. con.Close();
64. }
65. }
66. }
67. }
68. Response.Redirect(Request.Url.AbsoluteUri);
69. }
73. protected void DownloadFile(object sender, EventArgs e)
74. {
75. int id = int.Parse((sender as LinkButton).CommandArgument);
76. byte[] bytes;
77. string fileName, contentType;
78. string constr = "User ID=axis_nsdl;Password=a$xis_4321;Data Source=TSTDPSEC";
79. using (OracleConnection con = new OracleConnection(constr))
80. {
81. using (OracleCommand cmd = new OracleCommand())
82. {
83. cmd.CommandText = "select Name, Data, ContentType from tblFiles where Id=:Id";
84. cmd.Parameters.AddWithValue(":Id", id);
85. cmd.Connection = con;
86. con.Open();
87. using (OracleDataReader sdr = cmd.ExecuteReader())
88. {
89. sdr.Read();
91. bytes = (byte[])sdr["Data"];
92. contentType = sdr["ContentType"].ToString();
93. fileName = sdr["Name"].ToString();
94. }
95. con.Close();
96. }
97. }
98. Response.Clear();
99. Response.Buffer = true;
100. Response.Charset = "";
101. Response.Cache.SetCacheability(HttpCacheability.NoCache);
102. Response.ContentType = contentType;
103. Response.AppendHeader("Content-Disposition", "attachment; filename=" + fileName);
104. Response.BinaryWrite(bytes);
105. Response.Flush();
106. Response.End();
107. }
109. protected void BtnSearch_Click(object sender, System.EventArgs e)
110. {
113. con.Open();
114. OracleCommand cmd = new OracleCommand("Select * from Name where tblFiles like '" + txtSearch.Text + "%'", con);
115. OracleDataAdapter da = new OracleDataAdapter(cmd);
116. DataSet ds = new DataSet();
117. da.Fill(ds);
118. con.Close();
119. GridView1.DataSource = ds;
120. GridView1.DataBind();
121. }
122. protected void btnBack_Click(object sender, System.EventArgs e)
123. {
124. Response.Redirect("index.html");
125. }
126. }
Just Run the Application Debug on Action Events to see the FileUpload and its content.

We have successfully uploaded the file. Now, let's download and see the data .

We have successfully downloaded . Now, let's see the Oracle table part

As you can see, the data is stored in BLOB format successfully.

5 Comments

Join the conversation! Your thoughts help the community grow.

leena8y ago
Sir I am facing problem in uploading files of bigger size. getting message {"ORA-01460: unimplemented or unreasonable conversion requested"}
Alex Li9y ago
Where Can I have the source code?
- Akshay Phadke9y ago
  @Li Alex The comple source code is above for aspx and cs page
Vignesh Mani10y ago
Good one
kalu singh rao10y ago
Nice...