Insert Images into Oracle Table using asp.net

Insert Images into Oracle Table using asp.net

This tutorial explains how to insert/upload images into Oracle table using Oracle Client Provider.

Create a Table called Cars

ex: create table Cars(CarID number(5), CModel varchar2(20),CarImage BLOG);

Create a sequence called cars_seq

SQL>create sequence cars_seq;

Method1: Insert/Upload images to oracle table using SQLCOMMAND.

                if (FileUpload1.HasFile)
                {
                    bytes = FileUpload1.FileBytes;
                }

                String connString = ConfigurationManager.ConnectionStrings["ConnectionString1"].ConnectionString;
                OracleConnection conn = new OracleConnection(connString);
                conn.Open();

                OracleCommand cmd = new OracleCommand();
                cmd.Connection = conn;
                cmd.CommandText =

                    "INSERT INTO CARS(CARID,CMODEL,CARIMAGE) " +
                    "VALUES(cars_seq.nextval,:CMODEL,:CARIMAGE)";

               
                cmd.CommandType = CommandType.Text;
                OracleParameter pModel = new OracleParameter(":CMODEL",
                    OracleDbType.Varchar2, 20,
                    "CMODEL");
                pModel.Value = txtCarModel.Text;

               
                OracleParameter pcImage = new OracleParameter(":CARIMAGE",
                    OracleDbType.Blob,
                    bytes.Length, "CARIMAGE");
                pcImage.Value = bytes;

                cmd.Parameters.Add(pModel);
                cmd.Parameters.Add(pcImage);

                int numRecords = cmd.ExecuteNonQuery();

                lblStatus.Text = numRecords.ToString() + " Inserted...";

Method 2:  Insert/Upload Images using Typed DataSets

1) Add DataSet and Drag and Drop Cars table into the typed dataset Surface.

   Name this DataSet has "oraImagesDS"

                oraImagesDSTableAdapters.CARSTableAdapter carsAd =
                    new oraImagesDSTableAdapters.CARSTableAdapter();
              
                carsAd.Insert(
                    GetOracleSequenceValue(),
                    txtCarModel.Text,
                    GetFileData());


//Get Uploaded Picture data frol FileUpload web server control

 byte[] GetFileData()
        {
            byte[] picData = null;
            if (FileUpload1.HasFile)
            {
                picData = FileUpload1.FileBytes;
            }
            return picData;
        }

Get Oracle Sequence Value
Note: If Typed DataSet supports Sequence Value U can use that, VS2012 express edition doesnt support.

        int GetOracleSequenceValue()
        {
            int seqVal = -1;

            String connString = ConfigurationManager.ConnectionStrings["ConnectionString1"].ConnectionString;
            OracleConnection conn = new OracleConnection(connString);
            conn.Open();

            OracleCommand cmd = new OracleCommand();
            cmd.Connection = conn;
            cmd.CommandText = "select cars_seq.nextval from dual";
            cmd.CommandType = CommandType.Text;
            seqVal=Int16.Parse(cmd.ExecuteScalar().ToString());
              


           

            return seqVal;
        }

Method 3: Insert/Upload Images using Stored Procedures OracleCommand

Creating a Stored Procedure in Oracle.
 
Create or Replace Procedure imgupload(p_carModel varchar2,p_image blob)
is
begin
insert into cars values(cars_seq.nextval,p_carModel,p_image);
end;
 

        bool uploadusingStoredProc()
        {
            bool bRet = false;

            int seqVal = -1;

            String connString = ConfigurationManager.ConnectionStrings["ConnectionString1"].ConnectionString;
            OracleConnection conn = new OracleConnection(connString);
            conn.Open();

            OracleCommand cmd = new OracleCommand();
            cmd.Connection = conn;
            cmd.CommandText = "imgupload";
            cmd.CommandType = CommandType.StoredProcedure;
            OracleParameter p_cmodel = new OracleParameter();
            p_cmodel.Direction = ParameterDirection.Input;
            p_cmodel.OracleDbType = OracleDbType.Varchar2;
            p_cmodel.Size = 20;
            p_cmodel.Value = txtCarModel.Text;
            cmd.Parameters.Add(p_cmodel);

            OracleParameter p_carimage = new OracleParameter();
            p_carimage.Direction = ParameterDirection.Input;
            p_carimage.OracleDbType = OracleDbType.Blob;
            p_carimage.Size = GetFileData().Length;
            p_carimage.Value = GetFileData();
           

            cmd.Parameters.Add(p_carimage);
            int i =cmd.ExecuteNonQuery();

            return bRet;
        }

Method 4: Insert/Upload Images using Stored Procedures Typed DataSet

1. Add Typed DataSet name it as "OraImageUploadDS.xsd".

2.Just Drag and Drop Stored procedure into the surface(Typed dataset Desinger).

3.It will automatically generate

  OraImageUploadDSTableAdapters   Adapter class.

4.Using above adapter call Stored procedure as shown below.

                OraImageUploadDSTableAdapters.QueriesTableAdapter oraimgad =
                new OraImageUploadDSTableAdapters.QueriesTableAdapter();

                oraimgad.IMGUPLOAD(txtCarModel.Text, GetFileData());