/// <summary> /// Load a data set in csv (commad separated values) from a disk file and import it into the database /// </summary> /// <param name="Filename">Name of file to load</param> /// <param name="DelInit">Delegate to call to indicate the initialization params</param> /// <param name="DelIncrement">Delete to call for each new row of data imported</param> /// <returns></returns> public bool LoadCSV(String Filename, DELInitProgress DelInit, DELIncrementProgress DelIncrement) { try { m_Header = null; List <List <double> > TempData = new List <List <double> >(); using (StreamReader reader = new StreamReader(new FileStream(Filename, FileMode.Open))) { // // Read the Training in int RowCount = 0; while (reader.EndOfStream == false) { String[] sFields = reader.ReadLine().Split(','); // // If this is the first row, perform an automatic detection // of header values. bool IsHeader = false; if (RowCount == 0) { IsHeader = ParseFileHeader(sFields); } if (!IsHeader) { // // Add a row TempData.Add(new List <double>()); // // Add the values foreach (String Value in sFields) { double Number = Convert.ToDouble(Value, GPUtilities.NumericFormat); // Enforce US "." format TempData[RowCount].Add(Number); } RowCount++; } } } return(ImportToDB(Filename, DelInit, DelIncrement, TempData)); } catch { #if GPLOG GPLog.ReportLine("csv file read error", true); #endif return(false); } }
/// <summary> /// Manages importing the data into the database /// </summary> /// <param name="Filename"></param> /// <param name="DelInit"></param> /// <param name="DelIncrement"></param> /// <param name="TempData"></param> /// <returns></returns> private bool ImportToDB(String Filename, DELInitProgress DelInit, DELIncrementProgress DelIncrement, List <List <double> > TempData) { // // Create the internal array and get the Training copied into it. We // currently support a single objective, so a 1 for the last param. m_Training.ConstructStorage(TempData.Count, TempData[0].Count - 1, 1); for (int Row = 0; Row < TempData.Count; Row++) { // // Copy the values in for (int Column = 0; Column < (TempData[0].Count - 1); Column++) { m_Training[Row, Column] = TempData[Row][Column]; } m_Training.ObjectiveRow(Row)[0] = TempData[Row][TempData[Row].Count - 1]; } // // Decide if this is a time series file (single column) if (TempData[0].Count == 1) { this.TimeSeries = true; } else { this.TimeSeries = false; } // // Have the UI prepare itself DelInit(this.Rows); // // Import this Training into the database - Let the user know the records // are being imported, that's what the 'DelIncrement' delegate is for. String[] FileParts = Filename.Split('\\'); ImportDataToDB(FileParts[FileParts.Length - 1], Filename, DelIncrement); this.Name = FileParts[FileParts.Length - 1]; this.Description = Filename; return(true); }
/// <summary> /// Load a data set from the database. This method also provides delegates /// that can be called to allow the UI to update as the data is loaded. /// </summary> /// <param name="ModelingFileID"></param> /// <param name="DelInit"></param> /// <param name="DelIncrement"></param> /// <returns></returns> public bool LoadFromDB(int ModelingFileID, DELInitProgress DelInit, DELIncrementProgress DelIncrement) { m_ModelingFileID = ModelingFileID; // // Get the DB connection using (OleDbConnection DBConnection = GPDatabaseUtils.Connect()) { // // Read the header information int FileRows = 0; int FileInputs = 0; int FileObjectives = 0; LoadHeaderDB(DBConnection, ModelingFileID, ref FileInputs, ref FileObjectives); // // Find out how many records we will get back first, this is needed to construct the data storage String SQLRowCount = "SELECT COUNT(ValueDouble) FROM tblModelingFileColumn WHERE ModelingFileID = " + ModelingFileID; OleDbCommand cmd = new OleDbCommand(SQLRowCount, DBConnection); int RowCount = (int)cmd.ExecuteScalar(); // // Determine the number of rows of data - The data is stored as a single // column and we have to transform it into rows right here, that is why // the rows have to be computed based upon the number of columns. FileRows = RowCount / (FileInputs + FileObjectives); // // Let the calling object know the size if (DelInit != null) { DelInit(FileRows); } // // Create the storage arrays m_Training.ConstructStorage(FileRows, FileInputs, FileObjectives); // // Create the query String SQL = "SELECT ValueDouble FROM tblModelingFileColumn WHERE ModelingFileID = " + ModelingFileID + " ORDER BY RowIndex,ColumnIndex"; cmd = new OleDbCommand(SQL, DBConnection); using (OleDbDataReader rdr = cmd.ExecuteReader()) { rdr.Read(); // // Grab the values for (int Row = 0; Row < FileRows; Row++) { for (int Column = 0; Column < FileInputs; Column++) { m_Training[Row, Column] = (double)rdr["ValueDouble"]; rdr.Read(); } for (int Objective = 0; Objective < FileObjectives; Objective++) { m_Training.ObjectiveRow(Row)[Objective] = (double)rdr["ValueDouble"]; rdr.Read(); } // // Update the calling object if (DelIncrement != null) { DelIncrement(); } } } } return(true); }
/// <summary> /// Imports the current array of Training into the database /// </summary> /// <param name="Name">Name to record the Training set with</param> /// <param name="Description">Description to record the Training set with</param> /// <param name="DelIncrement">Delegate to call for indicating the status of the import</param> /// <returns>True/False upon success or failure</returns> private bool ImportDataToDB(String Name, String Description, DELIncrementProgress DelIncrement) { // // Get the DB connection using (OleDbConnection DBConnection = GPDatabaseUtils.Connect()) { // // Add the Filename & Stats to tblModelingFile String sSQL = "INSERT INTO tblModelingFile(Name,Description,TimeSeries,ColumnCount,InputCount) "; sSQL = sSQL + "VALUES ('" + Name + "',"; sSQL = sSQL + "'" + Description + "',"; sSQL = sSQL + this.TimeSeries.ToString() + ","; sSQL = sSQL + "'" + (this.Columns + this.Objectives) + "',"; if (this.TimeSeries) { sSQL = sSQL + "'1'"; } else { sSQL = sSQL + "'" + this.Columns + "'"; } sSQL = sSQL + ")"; OleDbCommand cmd = new OleDbCommand("Add File", DBConnection); cmd.CommandText = sSQL; cmd.CommandType = CommandType.Text; // // Execute the command try { cmd.ExecuteNonQuery(); // // Grab the DBCode generated for this file cmd.CommandText = "SELECT @@IDENTITY"; OleDbDataReader reader = cmd.ExecuteReader(); reader.Read(); this.m_ModelingFileID = Convert.ToInt32(reader[0].ToString());; reader.Close(); // // Construct a new command object to work with cmd.Dispose(); cmd = new OleDbCommand("Add Data", DBConnection); // // Load the Training into the database for (int nRow = 0; nRow < this.Rows; nRow++) { // // Write the inputs for (int nCell = 0; nCell < this.Columns; nCell++) { sSQL = "INSERT INTO tblModelingFileColumn(ModelingFileID,RowIndex,ColumnIndex,ValueDouble) "; sSQL = sSQL + "VALUES (" + ModelingFileID + ","; sSQL = sSQL + nRow + ","; sSQL = sSQL + nCell + ","; sSQL = sSQL + Convert.ToString(this[nRow, nCell], GPUtilities.NumericFormat) + ""; sSQL = sSQL + ")"; cmd.CommandText = sSQL; cmd.CommandType = CommandType.Text; cmd.ExecuteNonQuery(); } // // Write the objectives for (int Objective = 0; Objective < this.Objectives; Objective++) { sSQL = "INSERT INTO tblModelingFileColumn(ModelingFileID,RowIndex,ColumnIndex,ValueDouble) "; sSQL = sSQL + "VALUES (" + ModelingFileID + ","; sSQL = sSQL + nRow + ","; sSQL = sSQL + (this.Columns + Objective) + ","; sSQL = sSQL + Convert.ToString(this.ObjectiveRow(nRow)[Objective], GPUtilities.NumericFormat) + ""; sSQL = sSQL + ")"; cmd.CommandText = sSQL; cmd.CommandType = CommandType.Text; cmd.ExecuteNonQuery(); } // // Let the UI know it is okay to update something DelIncrement(); } // // Save the column headers (if they exit) if (m_Header != null) { for (int item = 0; item < m_Header.Length; item++) { sSQL = "INSERT INTO tblModelingFileHeader(ModelingFileID,ColumnIndex,Name) "; sSQL = sSQL + "VALUES(" + ModelingFileID + ","; sSQL = sSQL + item + ","; sSQL = sSQL + "'" + m_Header[item] + "'"; sSQL = sSQL + ")"; cmd.CommandText = sSQL; cmd.CommandType = CommandType.Text; cmd.ExecuteNonQuery(); } } } catch (OleDbException ex) { string sError = ex.Message.ToString(); } } return(true); }