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:



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



Now, configure the connection in your Oracle SQL Developer.



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



Now, let's create a 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,



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



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,

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


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,
Step 3 : Now let's see CS Code Part

Our File Upload Code is :



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 “:”:

• 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.