copy remote image file to DB

Question

I am consuming one rest api given by our external vendor , which has a column containing hyperlink of digital signature, i want to write a program in C# to pick the image from remote location and store it as image in oracle db. i will create a seperate column (BLOB) for this.

Pranam Bhat · Answer

One more reference : https://www.c-sharpcorner.com/article/upload-files-and-save-in-oracle-database-in-blob-format-in-asp-net/

Pranam Bhat · Answer

First, create the following table in the Oracle database: SQL: Create table tblImageService ( Image_id timestamp default sysdate, image_data blob, constraint pk_image_id primary key (image_id) ); Create the following stored procedure to insert an image to the Oracle database: SQL: CREATE OR REPLACE PROCEDURE InsertImage ( image_data in blob ) AS BEGIN Insert into tblImageService(image_data) values (image_data); END ; Now, create a web service and expose the following subroutine as a web method for uploading images: C#: [WebMethod] public int FireImage( string user_name, string password, byte [] news_image) { // Initiate connection with oracle OracleConnection Conxn1 = new OracleConnection( "user id=test;password=test;data source=jamb" ); try { //Verify the user if (user_name == "user_name" && password == "password" ) { try { //Open the connection Conxn1.Open(); byte [] tempBuff = news_image; OracleTransaction tx; tx = Conxn1.BeginTransaction(); OracleCommand cmd; cmd = Conxn1.CreateCommand(); cmd.CommandText = "declare xx blob; begin " + "dbms_lob.createtemporary(xx, false, 0);" + " :tempblob := xx; end;" ; // PL/SQL to read blob data cmd.Parameters.Add( new OracleParameter( "tempblob" , OracleDbType.Blob)).Direction = ParameterDirection.Output; cmd.ExecuteNonQuery(); OracleBlob tempLob; tempLob = (OracleBlob)cmd.Parameters[0].Value; tempLob.BeginChunkWrite(); // start writing the LOB data tempLob.Write(tempBuff, 0, tempBuff.Length); tempLob.EndChunkWrite(); cmd.Parameters.Clear(); cmd.CommandText = "InsertImage" ; cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add( new OracleParameter( "image_data" , OracleDbType.Blob)).Value = tempLob; cmd.ExecuteNonQuery(); //Execute the command to database tx.Commit(); // Commit the transaction } catch { } finally { Conxn1.Close(); } } } catch (Exception ex) { string showError = "Error: " + ex.Message; } finally { } return 1; } In the above code, the FireImage subroutine accepts three inputs, namely user name, password, and image data in bytes. After you call this subroutine, the image data, which is sent as bytes, is converted into blob data and inserted to the Oracle database table using the stored procedure. After this, you need to create another subroutine which fetches an image from the database, transfers it to a 64 bit string, and passes it to the client: C#: [WebMethod] public string FetchNewsImage( string image_id) { //Initiate the connection OracleConnection Conxn1 = new OracleConnection( "user id=test;password=test;data source=jamb" ); OracleDataReader dtr = null ; string imageString = "" ; try { Conxn1.Open(); string id = image_id; OracleCommand cmd = new OracleCommand( "select image_data " + "from tblImageService where to_char(" + "image_id,'DD-MON:YY-HH:MI:SS')='" + id + "'" , Conxn1); dtr = cmd.ExecuteReader(); byte [] arrpicture = new byte [0]; while (dtr.Read()) { arrpicture = ( byte [])dtr[ "image_data" ]; } imageString = Convert.ToBase64String(arrpicture); } catch (Exception ex) { string showError = "Error: " + ex.Message; } finally { dtr.Close(); Conxn1.Close(); } return imageString; } The above subroutine accepts the image ID as its parameter and fetches the image from the database using that ID. After that, it pulls out the data from the database, converts it to byte[] data type, and then to a Base 64 string using the Convert.ToBase64String method. Now, you need to develop client-side interaction for this web service. First, create a page named AddImage.aspx, and using the FileUpload server control, browse the image. Then, call the FireImage subroutine from our XML web service (don’t forget to add a reference to our above XML web service). Here is the code for calling the FireImage subroutine: C#: protected void btnSubmit_Click( object sender, EventArgs e) { try { //Browse the image file and then convert it to byte[] data byte [] tempBuff = new byte [Upload1.PostedFile.InputStream.Length]; Upload1.PostedFile.InputStream.Read(tempBuff, 0, Convert.ToInt32(Upload1.PostedFile.InputStream.Length)); Upload1.PostedFile.InputStream.Close(); //Call ImageDataService Web Service newsImageDataService.ImageDataService fireImage = new newsImageDataService.ImageDataService(); fireImage.FireImage( "user_name" , "password" , tempBuff); Response.Write( "Article Successfully saved!!" ); } catch (Exception ex) { Response.Write( "Error: " + ex.Message); } finally { //do nothing } } This code saves your image to the database using the ImageDataService XML web service. Now, you need to view the inserted image from the database. First, create the web page named viewImage.aspx and add the following code to the page load subroutine. Here is the code for you: C#: protected void Page_Load( object sender, EventArgs e) { try { Conxn1.Open(); OracleCommand cmd = new OracleCommand( "select ' + "/LoadImage.aspx?image_id='||to_char(" + "image_id,'DD-MON:YY-HH:MI:SS')||'>'||" + "to_char(image_id,'DD-MON:YY-HH:MI:SS')|| " + " ' ' as news_url from tblImageService " , Conxn1); OracleDataReader dtr = cmd.ExecuteReader(); while (dtr.Read()) { this .lblImage.Text = " " + " " + dtr[ "news_url" ].ToString() + " " + " " ; } } catch (Exception ex) { Response.Write( "Error: " + ex.Message); } finally { Conxn1.Close(); } } The above code generates the link of the image that is fetched from the database, placing the appropriate query string to pass. Now, you need to build another page which actually generates the image. We name page name as LoadImage.aspx. Actually, all the processing is done by this page: C#: protected void Page_Load( object sender, EventArgs e) { string id = Request.QueryString[ "image_id" ].ToString(); try { newsImageDataService.ImageDataService getImage = new newsImageDataService.ImageDataService(); byte [] arrpicture = System.Convert.FromBase64String(getImage.FetchNewsImage(id)); Response.ContentType = "Image/Jpeg" ; Response.BinaryWrite(arrpicture); } catch (Exception ex) { Response.Write( "Error: " + ex.Message); } finally { } } The above code call the FetchNewsImage subroutine which accepts the ID of the image. Once you pass the image ID to call the FetchNewsImage subroutine, it returns a Base 64 string. After you get the Base 64 string from the subroutine, you can convert it back to byte[] data using the System.Convert.FromBase64String method. At last, you write the byte[] data as an image using the Response.BinaryWrite method.