Re: OracleDataAdapter, DataSet, XML C# .NET question

From: Mike <mike_at_descant-consulting.com>
Date: 3 Feb 2003 05:33:32 -0800
Message-ID: <50f9808a.0302030533.31de8f83@posting.google.com>

mike_at_descant-consulting.com (Mike) wrote in message news:<50f9808a.0301310756.7346ac88_at_posting.google.com>... ---- SNIP ----
> 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

I solved the problem. I'm not sure about the use of the table name ("SYSTEMLOG") in the Update() call, but I couldn't get this to work using the oda.TableMappings() to tell the OracleDataAdapter which tables mapped to which. I need to study how DataSets and DataAdapters interact more. But here is the code that works. Notice the parameter names in the oda.InsertCommand.Parameters.Add() calls. I had to differentiate the names more by adding the 'p' prefix in order for Oracle to recognize them as parameters. Notice also that I cot rid of the finally{} blocks and switched to "using" blocks which is the prefered method for releasing a critical resource in C#.

public bool LogFromFile()
{

 bool retval           = false;
 string strXml         = null;
 OracleDataAdapter oda = null;

 try 
 {                
  // We'll use this XmlDataDocument to just hold the schema we read in
  // from the m_strXsdFile. We're doing this so that we can Clone() it
  // inside the loop that reads in the XmlDocuments stored in the
  // m_strLogFile. We can't LoadXml() twice on the same
  // XmlDocument. So We'll create the XmlDataDocument inside the if()

  // block and let the GarbageCollector take care of unreferenced   // instances.

  XmlDataDocument xddSchema = new XmlDataDocument();   xddSchema.DataSet.ReadXmlSchema(m_strXsdFile);   XmlDataDocument xddAll = (XmlDataDocument)xddSchema.Clone();

  DateTime dt               = new DateTime();
        
  using (OracleConnection cn =
         new OracleConnection(m_strConnectString))

   {
    cn.Open();
    // We never want to pull data from the SystemLog into this     // DataSet, hence the where clause.
    oda = new OracleDataAdapter("select * from systemlog where 1 = 0",

                                cn);

    // Use a Mutex so that threads don't overlap reading/writing     // from/to the file.
    using (Mutex mutex = new Mutex(false, "SystemLog_LogFile_Mutex"))

     {
      mutex.WaitOne();

      // Read the log file and process each XML document found in it.
      using (StreamReader 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"] =
           xdd.DataSet.Tables["SYSTEMLOG"].Rows[0]["MILLISECONDS"];

          xddAll.DataSet.Tables["SYSTEMLOG"].Rows.Add(row);
          strXml = null;              // Clear 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 the OracleDataAdapter we created and
        // use the Update() method to stuff it all in at once.  TODO:
        // Change this to use a stored procedure.
                
        oda.InsertCommand = new OracleCommand
         ("INSERT into "
          + "SYSTEMLOG (STATUSMSGID,SEVERITY,MSGNUMBER,SOURCE,"
          + "           DESCRIPTION,FACILITY,USERNAME,MSGDATETIME,"
          + "           MILLISECONDS )"
          + "   VALUES (SystemLog_SEQ.NEXTVAL,:pSeverity,:pMsgNumber,"
          + "           :pSource,:pDescription,:pFacility,:pUsername,"
          + "           :pMsgDateTime,:pMilliseconds)", cn);
        oda.InsertCommand.CommandType = CommandType.Text;

        oda.InsertCommand.Parameters.Add("pSeverity",
                                         OracleType.VarChar,10,
                                         "SEVERITY");
        oda.InsertCommand.Parameters.Add("pMsgNumber",
                                         OracleType.Number,22,
                                         "MSGNUMBER");
        oda.InsertCommand.Parameters.Add("pSource",
                                         OracleType.VarChar,255,
                                         "SOURCE");
        oda.InsertCommand.Parameters.Add("pDescription",
                                         OracleType.VarChar,2000,
                                         "DESCRIPTION");
        oda.InsertCommand.Parameters.Add("pFacility",
                                         OracleType.VarChar,10,
                                         "FACILITY");
        oda.InsertCommand.Parameters.Add("pUsername",
                                         OracleType.VarChar,128,
                                         "USERNAME");
        oda.InsertCommand.Parameters.Add("pMsgDateTime",
                                         OracleType.DateTime,7,
                                         "MSGDATETIME");
        oda.InsertCommand.Parameters.Add("pMilliseconds",
                                         OracleType.Number,22,
                                         "MILLISECONDS");

        oda.Update(xddAll.DataSet,"SYSTEMLOG");
                
        retval = true;
       }
     }
   }
  File.Delete(m_strLogFile);       // Delete the file since all of its
                                   // information is now in the
                                   // database. The next LogToFile
                                   // will re-create it.

 }
 catch (Exception ex)

 {                                 // This is here for debug purposes.

  string s = ex.GetType().ToString();
  ex.ToString();
 }
 return retval;
}

Also notice that I changed the order of things a bit. I'm not sure it was needed because it was when I changed the parameter names that things started working. It just seems a bit more logical in this order, so I've left it alone.

Mike Lockhart Received on Mon Feb 03 2003 - 07:33:32 CST