adohandler.cs

Adding and making operations LOB data in a database with C#

/**************************************************************************
@author  Chandar
@version 1.0
Development Environment        :  Visual C#.Net Beta2
Name of the File               :  ADOHandler.cs
Creation/Modification History  :
                                  02-AUG-2001     Created

File Overview
This file defines the class and its functions to communicate and work with
the database. 
**************************************************************************/

//include standard namespaces
using System;
using System.Collections;

namespace LOBSample
{
	/// <summary>
	/// Summary description for OraLOB class to handle database functions.
	/// </summary>
	public class OraLOB
	{
			ADODB.Connection m_conn;   //Declare ADODB connection object
		
		//constructor
		public OraLOB()
		{}

        /***************************************************************
		 * This function is used to open a connection to the database  
		 * ***********************************************************/
		public bool ConnectDatabase()
		{
			try
			{  				
				//specify the connection attributes in connection string
				string strConnectionString = "Provider=OraOLEDB.Oracle;"  +
										     "Data Source=" + ConnectionParams.Datasource +    
					                         "; User Id=" + ConnectionParams.Username +
					                         ";Password=" +ConnectionParams.Password ;
				//Instantiate connection object
				m_conn=new ADODB.Connection();
				m_conn.ConnectionString = strConnectionString;

				//Open the connection
				m_conn.Open("","","",0);
			
				
			}
			//Catch the exception occured during connecting	
			catch(Exception e)
			{
				//Display error message
				System.Windows.Forms.MessageBox.Show(e.Message);
				return false;
			}
			return true;   //connected successfully
		}
		
		/*********************************************
		 * This function closes connection to database
		 * ******************************************/
		public void CloseDatabase()
		{
			m_conn.Close();
		}

		/*************************************************************
		 * This function gets the product names and id's from database
		 * and displays them in the list box on form
		 * **********************************************************/
		public bool getProductNames(StartForm p_imgDlg)
		{
			try
			{
				//Create a ADODB recordset object
				ADODB._Recordset rs=new ADODB.Recordset();
				
				//Specify the SQL statement to execute
				string sql="select product_id,product_name from product_information";
			    
				//Open the recordset specfiying the connection, cursor and locktype 
				//for retrieving data
				rs.Open(sql,m_conn,ADODB.CursorTypeEnum.adOpenStatic,ADODB.LockTypeEnum.adLockOptimistic,0);
				
				//Loop till recordset contains data
				while(!rs.EOF)
				{ 
					//Retrieve the product id and name .Use appropriate casting
					string pid=((decimal)rs.get_Collect("product_id")).ToString();
					string pname=(string)rs.get_Collect("product_name");

					//Create a ComboData class object using name and id
					ComboData itemdata= new ComboData(pname,pid);
					
					//Add this object to list box. ToString funcion of ComboData
					//is called which returns the product name for display
					p_imgDlg.comboProduct.Items.Add(itemdata);
					
					//Move to next row
					rs.MoveNext();
				}
			}
			catch(Exception e)
			{
				//Display error message
				System.Windows.Forms.MessageBox.Show(e.Message);
				return false;
			}
			return true;
		}

        /**********************************************************************************
		 * This function gets the image for selected product and writes it to a 
		 * temporary file in following steps:
		 * 1. Open a recordset object to get the image length of the selected product
		 * 2. Create a command object and to call the stored procedure.
		 * 3. Set the 'SPPrmsLOB' property to true 
		 * 4. Create the parameters for product id and image and append to command object
		 * 5. Execute the command
		 * 6. Create a temporary file and write the image bytes to it.
		 * *********************************************************************************/
		public bool GetProductImage(string p_productid)
		{
			//Delete the temporary file in the application directory
			System.IO.File.Delete(System.Windows.Forms.Application.StartupPath + "/tempimage.gif");
			
			int imgsize;  //variable to hold image size

			try
			{
				//Create a recordset object
				ADODB.Recordset rs=new ADODB.Recordset();
				
				//Specify SQL statement to get image length for selected product
				string strSQL="select dbms_lob.getlength(product_image)imglength from " +
					           " product_information where product_id="+p_productid;
                
				//Open recordset object
				rs.Open(strSQL,m_conn,ADODB.CursorTypeEnum.adOpenStatic,ADODB.LockTypeEnum.adLockOptimistic,0);
				
				//Retrieve image length from recordset
				string temp =((decimal)rs.get_Collect("imglength")).ToString();
				rs.Close(); //close recordset

				//Convert string to int
				imgsize=Int32.Parse(temp);
		
				if(imgsize!=0)  //if image exists
				{
					//Call stored procedure using ODBC escape sequence
					string strProc="{Call getProductImage(?,?)}";

					//Create command object
					ADODB.Command cmd =new ADODB.Command();

					//Set its active connection to open connection
					cmd.ActiveConnection=m_conn;

					//Set the command type to text
					cmd.CommandType=ADODB.CommandTypeEnum.adCmdText;

                   //Set command text to SQL statement 
					cmd.CommandText=strProc;
              
					//Set the 'SPPrmsLOB' property to true. This is required to tell the
					//provider that a LOB parameter is being passed

					ADODB.Properties properties;
					
					//Get the command properties into ADO properties object
					properties=cmd.Properties;

					//Obtain an enumerator of the properties
					System.Collections.IEnumerator ienum=properties.GetEnumerator();
					
					ADODB.Property singleprop;
					while(ienum.MoveNext())  //loop thorugh the enumerator
					{
						//Get the current property in to Property object
						singleprop=(ADODB.Property)ienum.Current;

						//Get the name of current property
						string propname= singleprop.Name;

						//if its is 'SPPrmsLOB' set it to true
						if(propname.Equals("SPPrmsLOB"))
							singleprop.Value=true;
					}
                    
					//Convert productid from string to integer
					int proid=Int32.Parse(p_productid);

					//Create Command parameter for product id (IN parameter)
             		ADODB._Parameter productid=cmd.CreateParameter("pid",ADODB.DataTypeEnum.adNumeric,ADODB.ParameterDirectionEnum.adParamInput,20,proid);
					
					//Append the parameter to command object
					cmd.Parameters.Append(productid);
				
					//Create command parameter for product image (OUT parameter)
					ADODB._Parameter pimage = cmd.CreateParameter("pimage",ADODB.DataTypeEnum.adLongVarBinary,ADODB.ParameterDirectionEnum.adParamOutput,(int)imgsize +1,"");				
					
					//Append the parameter to command object
					cmd.Parameters.Append(pimage);

					object recs=0;
					object tparams=proid;
		            
					//Execute the command
					cmd.Execute(out recs,ref tparams,1);	
			        
					//Set SPPrmsLOB property to false
					//Reset the enumerator containing command properties to initial position
					ienum.Reset();
					while(ienum.MoveNext())  //loop thorugh the enumerator
					{
						//Get the current property into Property object
						singleprop=(ADODB.Property)ienum.Current;

						//Get the name of current property
						string propname= singleprop.Name;

						//if its is 'SPPrmsLOB' set it to true
						if(propname.Equals("SPPrmsLOB"))
							singleprop.Value=false;
					}
					//Create a file object
					System.IO.FileStream file;
				    
					//Create a temporary file for writing the image bytes
					file=new System.IO.FileStream(System.Windows.Forms.Application.StartupPath + "/tempimage.gif",System.IO.FileMode.Create);
				    
					//Obtain an enumerator for command parameters
					IEnumerator ienumparam=cmd.Parameters.GetEnumerator();
		
					while(ienumparam.MoveNext()) //iterate through the enumerator
					{
					    //Get the current parameter into Parameter object
						ADODB._Parameter image=(ADODB._Parameter)ienumparam.Current;

                        if(image.Name.Equals("pimage")) //if parameter is for image
						{
							//Obtain the value returned by stored procedure into byte array
							int x =image.Size;
							byte [] barr=(byte [])image.Value;

                            //Write the byte array to file
							file.Write(barr,0,barr.Length);
						}
				
					}	
                    //close the file
					file.Close();
				}
				else
				{   //If image size is zero display the message
					System.Windows.Forms.MessageBox.Show("Image Does not exists");
					return false;
				}
			}
			catch(Exception e)
			{
				//Display any error that occured
				System.Windows.Forms.MessageBox.Show(e.Message);
				return false;
			}
			return true;
			
		}
        
		/**********************************************************************************
		 * This function updates the new image for selected product to the database
		 * in following steps:
		 * 1. Create a command object and specify the SQL string to call the stored procedure.
		 * 2. Set the 'SPPrmsLOB' property to true 
		 * 3. Create parameters to be passed to stored procedure.
		 * 4. Open the image file. Write the data bytes from file to image parameter using 
		 *    AppendChunk method
		 * 5. Append the parameters to command object
		 * 6. Execute the command to update the image to database
		 * *********************************************************************************/
		public bool updateProductImage(string p_productid,string p_filename)
		{  
			try
			{  
				//create a copy of selected image file because it is already being used by
				//picture box for new image
				System.IO.File.Copy(p_filename,System.Windows.Forms.Application.StartupPath +
                                                                       "/datafile.gif",false);
                
				//Convert productid from string to integer
				int pid=Int32.Parse(p_productid);
			
				//Specify SQL statement to call stored procedure using ODBC escape sequence
				string strSQL="{Call setproductimage(?,?) }";
				
				//Create ADODB command object
				ADODB.Command cmd=new ADODB.Command();

				//Set command object's active connection to open connection
				cmd.ActiveConnection=m_conn;
				
				//Set the 'SPPrmsLOB' property to true. This is required to tell the
				//provider that a LOB parameter is being passed
				ADODB.Properties properties;

				//Get the command properties into ADO properties object
				properties=cmd.Properties;

                //Obtain an enumerator of the properties
				System.Collections.IEnumerator ienum=properties.GetEnumerator();
				ADODB.Property singleprop;

				while(ienum.MoveNext())  //iterate through enumerator
				{
					//Get the current property into Property object
					singleprop=(ADODB.Property)ienum.Current;

					//Get the name of current property
					string propname= singleprop.Name;

					//if it is 'SPPrmsLOB' set its value to true
					if(propname.Equals("SPPrmsLOB"))
						singleprop.Value=true;
				}

				//Set the command type to text
				cmd.CommandType=ADODB.CommandTypeEnum.adCmdText;

				//Set the command object's text to SQL statement
				cmd.CommandText=strSQL;
                
				//Open the new image file for reading
				System.IO.FileStream file=new System.IO.FileStream(System.Windows.Forms.Application.StartupPath +
                                                               "/datafile.gif",System.IO.FileMode.Open);
				
				//Create a byte array with lenth equal to filesize
				byte []barr=new byte[file.Length];

                //Create parameter for product id
				ADODB._Parameter productid = cmd.CreateParameter("pid",ADODB.DataTypeEnum.adNumeric,
					                        ADODB.ParameterDirectionEnum.adParamInput,20,pid);
				
				//Create parameter for product image
				ADODB._Parameter pimage = cmd.CreateParameter("pimage",ADODB.DataTypeEnum.adLongVarBinary,
					                     ADODB.ParameterDirectionEnum.adParamInput,(int)file.Length,"");
				
				//if file is readable
				if(file.CanRead)
				{  
					//Read the file into byte array
					file.Read(barr,0,barr.Length);

					//Append the byte array to image parameter
					pimage.AppendChunk(barr);
				}

				//Append the parameters to command object
				cmd.Parameters.Append(productid);
				cmd.Parameters.Append(pimage);
				
				//Close the image file
				file.Close();
				
                //Execute the command to update the image
				object recs;
				object sqa=pid;
				cmd.Execute(out recs,ref sqa,0);  
                
				//Set SPPrmsLOB property to false
				//Reset the enumerator containing command properties to initial position
				ienum.Reset();
				while(ienum.MoveNext())  //iterate through enumerator
				{
					//Get the current property in to Property object
					singleprop=(ADODB.Property)ienum.Current;

					//Get the name of current property
					string propname= singleprop.Name;

					//if it is 'SPPrmsLOB' set its value to true
					if(propname.Equals("SPPrmsLOB"))
						singleprop.Value=false;
				}
				
				//Delete the temporary copy of image file
				System.IO.File.Delete(System.Windows.Forms.Application.StartupPath +"/datafile.gif");
				
			}
			catch(Exception e)
			{
				//Display any errors if occured
				System.Windows.Forms.MessageBox.Show(e.Message);

				//Delete temporary image file
				System.IO.File.Delete(System.Windows.Forms.Application.StartupPath +"/datafile.gif");
				return false;
			}
			return true;	//successful completion
		}

	}
}