OracleDataAdapter, DataSet, XML C# .NET question

From: Mike <mike_at_descant-consulting.com>
Date: 31 Jan 2003 07:56:54 -0800
Message-ID: <50f9808a.0301310756.7346ac88@posting.google.com>

Hi all,

A brief explanation of what I'm trying to do might be best before I ask the question.

I am writing a C# component that will log messages to an Oracle database. In the event that the database becomes unavailable, we want to log the information as XML to a file. We've chosen to write multiple XML documents with one table row per document into the file. On a timed basis, we will read the file, extracting the XML documents and merging them into a single XmlDataDocument.DataSet. We've also read an XmlSchema file (.xsd) into this XmlDataDocument (the same schema was read into the XmlDataDocument that created the XML documents (written as strings) in the temporary file. I've defined an INSERT SQL statement and added it to the OracleDataAdapter object and want to use the Update() method to move the rows of data we've read from the file (and stored in the XmlDataDocument.DataSet) to write the data to the Oracle database. Ideally, with a single call to Update() rather than getting the Rows collection in the DataSet.Tables collection and iterating through them to write them one row at a time.

I'm not sure what I want to do is possible. However, the documentation on Update() says that it calls the InsertCommand(), UpdateCommand() and DeleteCommand() SQL statements for any DataSet rows that are added, modified or deleted in the DataSet. Here is my code (reformatted a bit to fit here and BTW, I'm an old UNIX hack, this is my first C# component, so don't laugh ;).

public bool LogFromFile()
{
 bool retval = false;
 string strXml = null;

 OracleDataAdapter oda = null;
 OracleConnection cn   = null;
 StreamReader sr       = null;
 Mutex mutex           = null;

 try
 {
  DateTime dt = new DateTime();   XmlDataDocument xddSchema = new XmlDataDocument();   xddSchema.DataSet.ReadXmlSchema(m_strXsdFile);   XmlDataDocument xddAll = (XmlDataDocument)xddSchema.Clone();

// Use a Mutex so that threads don't overlap writing to the file.
  mutex = new Mutex(false, "SystemLog_LogToFile_Mutex");   mutex.WaitOne();     

// Read the log file and process each XML document found in it.
  sr = new StreamReader(m_strLogFile, true);   while (sr.Peek() > -1)
  {
   string line = sr.ReadLine();
   strXml += line + '\r' + '\n';
   if (line.IndexOf("</Document>") >= 0)    {
    XmlDataDocument xdd = (XmlDataDocument)xddSchema.Clone();     xdd.LoadXml(strXml) ;
    if (xdd.DataSet.Tables["SYSTEMLOG"].Rows.Count < 1)

     throw new Exception("Failed to load ANY DATA into "
                         + "the XmlDataDocument.");

    DataRow row = xddAll.DataSet.Tables["SYSTEMLOG"].NewRow();

    row["SEVERITY"] =
      xdd.DataSet.Tables["SYSTEMLOG"].Rows[0]["SEVERITY"];     row["MSGNUMBER"] =
      xdd.DataSet.Tables["SYSTEMLOG"].Rows[0]["MSGNUMBER"];     row["SOURCE"] =
      xdd.DataSet.Tables["SYSTEMLOG"].Rows[0]["SOURCE"];     row["DESCRIPTION"] =
      xdd.DataSet.Tables["SYSTEMLOG"].Rows[0]["DESCRIPTION"];     row["FACILITY"] =
      xdd.DataSet.Tables["SYSTEMLOG"].Rows[0]["FACILITY"];     row["USERNAME"] =
      xdd.DataSet.Tables["SYSTEMLOG"].Rows[0]["USERNAME"];     row["MSGDATETIME"] =
      xdd.DataSet.Tables["SYSTEMLOG"].Rows[0]["MSGDATETIME"];     row["MILLISECONDS"] =x
      dd.DataSet.Tables["SYSTEMLOG"].Rows[0]["MILLISECONDS"];

    xddAll.DataSet.Tables["SYSTEMLOG"].Rows.Add(row);

    strXml = null;          // Release the current XML document so we
                            // can read in the next one.

   }
  }
// We've retrieved all of the data from the file and put it into
// xddAll. Now we need to write it to the database. We'll use a
// DataAdapter.

  cn = new OracleConnection(m_strConnectString);   oda = new OracleDataAdapter("select * from systemlog where 1 = 0",

                              cn);

  oda.InsertCommand = new OracleCommand
   ("INSERT into "

    + "SYSTEMLOG (statusmsgid,severity,msgnumber,source,description,"
    + "           facility,username,msgdatetime,milliseconds)"
    + "   VALUES (SystemLog_SEQ.NEXTVAL,:SEVERITY,:MSGNUMBER,:SOURCE,"
    + "           :DESCRIPTION,:FACILITY,:USERNAME,:MSGDATETIME,"
    + "           :MILLISECONDS)", cn);
  oda.InsertCommand.CommandType = CommandType.Text;
                
  oda.InsertCommand.Parameters.Add("STATUSMSGID", OracleType.Number,
                                   22, "STATUSMSGID");
  oda.InsertCommand.Parameters.Add("SEVERITY",    OracleType.Number,
                                   22, "SEVERITY");
  oda.InsertCommand.Parameters.Add("MSGNUMBER",   OracleType.Number,
                                   22, "MSGNUMBER");
  oda.InsertCommand.Parameters.Add("SOURCE",      OracleType.VarChar,
                                   255, "SOURCE");
  oda.InsertCommand.Parameters.Add("DESCRIPTION", OracleType.VarChar,
                                   2000, "DESCRIPTION");
  oda.InsertCommand.Parameters.Add("FACILITY",    OracleType.VarChar,
                                   10, "FACILITY");
  oda.InsertCommand.Parameters.Add("USERNAME",    OracleType.VarChar,
                                   128, "USERNAME");
  oda.InsertCommand.Parameters.Add("MSGDATETIME", OracleType.DateTime,
                                   7, "MSGDATETIME");
  oda.InsertCommand.Parameters.Add("MILLISECONDS",OracleType.Number,
                                   22, "MILLISECONDS");

  oda.TableMappings.Add("SYSTEMLOG","SYSTEMLOG");   oda.Fill(xddAll.DataSet,"SYSTEMLOG");
  cn.Open();
  oda.Update(xddAll.DataSet.GetChanges(DataRowState.Added),"SYSTEMLOG");

                
                
  //File.Delete(m_strLogFile); // Delete the file since all of its
                             // information is now in the database.
                             // The next LogToFile will re-create it.

  retval = true;
 }
 catch (Exception ex)
 {
  ex.ToString();
 }
 finally
 { // Release and close any critical resources.

  if (mutex != null) mutex.ReleaseMutex();
  if (sr != null) sr.Close();
  if (cn != null) cn.Close();

 }
 return retval;
}

Some of this code is purely trial ane error stuff, especially the Parameters.Add() statements. I currently get a "ORA-01036: illegal variable name/number" error which is why I'm fiddling with the Parameters.Add() statements.

Thanks,

Mike Lockhart Received on Fri Jan 31 2003 - 09:56:54 CST