Need help selecting substring from parameter prior to inserting into existing Oracle table using C#

Dear fellow developers:

I have a method in C# where I am trying to insert parameters that I am extracting from a website, and inserting them into an existing table in an Oracle database.  The relevant code I have attached to this question.  I have no problems inserting the parameters into the table.  The problem is that one of the parameters that I am inserting holds both date and time values, which I need to break up, and insert into separate columns in the table.  In my opinion, the best way to do this is using a sql statement similar to:

select substr('Closing on Monday, December 12, 2011  at 04:00 PM  AST.', 11 ) from dual

Open in new window


Except in my case, rather than the date being hardcoded, it will be in the form of a parameter.  However, I'm a bit new to C#, and am not sure how to incorporate this statement inside my C# code, such that everything is done dynamically (i.e. extract the relevant components from the parameter, and then insert both parts simultaneously into separate columns of my table).  Can anyone explain to me how to do this?

Thanks in advance to all who reply.
public static bool ExportData(WrExportArguments args)
	{
		try
		{
			
			
			//First we set the SQL we'll use to insert data into the database table.      
			
			args.Database.SetSql("insert into OwnerTender (DATE,DESCRIPTION) values (:Date, :Description)");
			args.Database.PrepareSql();
			
			
			//Loop htough all the export tables
			foreach (WrExportTableDefinition table in args.ExportData.TablesDefinitions.Tables)
			{
				//Open a data reader for the current table
				WrExportTableReader reader = args.ExportData.GetTableReader(table.TableName);
				try
				{
					//Loop though all rows in the current data table and write them to the target database.
					while (reader.Read())
					{
						args.Database.SetParameterTextValue("DATE",
							reader.GetStringValue("Date"));
						args.Database.SetParameterTextValue("DESCRIPTION",
							reader.GetStringValue("Description"));
						args.Database.ExecuteNonQuery();
					}
				}
				finally
				{
					reader.Close();
				}
			}

			
			
			//args.Database.ExecuteNonQuery();
	
			return true;
		}
		catch (Exception exp)
		{
			args.WriteDebug(exp.Message);
			return false;
		}
	}

Open in new window

fsyedAsked:
Who is Participating?
 
Bob LearnedConnect With a Mentor Commented:
I don't work with Oracle, but the ADO.NET providers follow a pattern, so that the syntax should be fairly similar.

Example:

Calling Oracle stored procedures from Microsoft.NET
http://www.c-sharpcorner.com/UploadFile/john_charles/CallingOraclestoredproceduresfromMicrosoftdotNET06222007142805PM/CallingOraclestoredproceduresfromMicrosoftdotNET.aspx
using (OracleConnection objConn = new OracleConnection("Data Source=ORCL; User ID=scott; Password=tiger"))
            {
                OracleCommand objCmd = new OracleCommand();
                objCmd.Connection = objConn;
                objCmd.CommandText = "count_emp_by_dept";
                objCmd.CommandType = CommandType.StoredProcedure;
                objCmd.Parameters.Add("pin_deptno", OracleType.Number).Value = 20;
                objCmd.Parameters.Add("pout_count", OracleType.Number).Direction = ParameterDirection.Output;

                try
                {
                    objConn.Open();
 
                    objCmd.ExecuteNonQuery();
 
                    System.Console.WriteLine("Number of employees in department 20 is {0}", objCmd.Parameters["pout_count"].Value);
                }
                catch (Exception ex)
                {
                    System.Console.WriteLine("Exception: {0}",ex.ToString());
                }
            }

Open in new window

0
 
Bob LearnedCommented:
You can use DateTime.ParseExact, or regular expression parsing.

DateTime.ParseExact Method (String, String, IFormatProvider)
http://msdn.microsoft.com/en-us/library/w2sa9yss.aspx

0
 
fsyedAuthor Commented:
Thanks very much for your quick reply.  I'm not sure if that would be the best approach since I am inserting the data into the table using parameters.  Can you show me an example of calling an Oracle stored procedure using C#?
0
 
fsyedAuthor Commented:
Thanks very much for providing your solution, I have what I need to continue from here!

Take care.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.