Tip: How to Transfer any SQLite Table into a Data Table

Based on my approach about SQLite here a C# code for an Invoke Code activity to transfer any SQLite table into a standard data table.

The code is easy to understand. The input parameters of the Invoke Code activity are the SQLiteDbFileName and SQLiteDbTableName and the output parameters are the data table and ErrorReturn, in case of error. At first the SQLite database is opened, then the column names and types are detected and the data table is created with this information. Now the SQLite table is processed row by row and the data is transferred to the data table, depending on the data type. At the end of the process, all the data is in the data table and it is returned by the Invoke Code activity.

//-UiPath Begin---------------------------------------------------------

dtData = new DataTable();
ErrorReturn = string.Empty;

string SQLiteConnStr = @"Data Source=" + SQLiteDbFileName + ";Version=3;";
SQLiteConnection SQLiteConn = new SQLiteConnection(SQLiteConnStr);
SQLiteConn.Open();

string SQLiteComStr = "SELECT * FROM " + SQLiteDbTableName + ";";

SQLiteCommand SQLiteCmd = new SQLiteCommand(SQLiteComStr, SQLiteConn);
SQLiteDataReader SQLiteReader = SQLiteCmd.ExecuteReader();

dtData.Clear();

//-Add columns with type to data table----------------------------------
for(var i = 0; i < SQLiteReader.FieldCount; i++) {
  DataColumn dtColName = new DataColumn(SQLiteReader.GetName(i));
  dtColName.DataType = SQLiteReader.GetFieldType(i);
  dtData.Columns.Add(dtColName);
}

//-Write data from SQLite table to data table---------------------------
while(SQLiteReader.Read()) {

  DataRow dtNewRow = dtData.NewRow();

  for (var i = 0; i < SQLiteReader.FieldCount; i++) {

    //------------------------------------------------------------------
    //-
    //- https://docs.microsoft.com/en-us/dotnet/standard/data/sqlite/types
    //-
    //------------------------------------------------------------------

    //-Boolean (Integer, 0 or 1)----------------------------------------
    if(typeof(System.Boolean).IsEquivalentTo(SQLiteReader.GetFieldType(i))) {
      dtNewRow[i] = SQLiteReader.GetBoolean(i);
    }

    //-Byte (Integer)---------------------------------------------------
    else if(typeof(System.Byte).IsEquivalentTo(SQLiteReader.GetFieldType(i))) {
      dtNewRow[i] = SQLiteReader.GetByte(i);
    }

    //-Byte[] (Blob)----------------------------------------------------
    else if(typeof(System.Byte[]).IsEquivalentTo(SQLiteReader.GetFieldType(i))) {
      dtNewRow[i] = (byte[])SQLiteReader[i];
    }

    //-Char (Text, UTF8)----------------------------------------------
    else if(typeof(System.Char).IsEquivalentTo(SQLiteReader.GetFieldType(i))) {
      dtNewRow[i] = SQLiteReader.GetChar(i);
    }

    //-DateTime (Text, yyyy-MM-dd HH:mm:ss.FFFFFFF)-------------------
    else if(typeof(System.DateTime).IsEquivalentTo(SQLiteReader.GetFieldType(i))) {
      dtNewRow[i] = SQLiteReader.GetDateTime(i);
    }

    //-DateTimeOffset (Text, yyyy-MM-dd HH:mm:ss.FFFFFFFzzz)----------
    else if(typeof(System.DateTimeOffset).IsEquivalentTo(SQLiteReader.GetFieldType(i))) {
      dtNewRow[i] = (DateTimeOffset)SQLiteReader[i];
    }

    //-Decimal (Text, 0.0#... format. REAL would be lossy-------------
    else if(typeof(System.Decimal).IsEquivalentTo(SQLiteReader.GetFieldType(i))) {
      dtNewRow[i] = SQLiteReader.GetDecimal(i);
    }

    //-Double (Real)--------------------------------------------------
    else if(typeof(System.Double).IsEquivalentTo(SQLiteReader.GetFieldType(i))) {
      dtNewRow[i] = SQLiteReader.GetDouble(i);
    }

    //-Guid (Text, 00000000-0000-0000-0000-000000000000)--------------
    else if(typeof(System.Guid).IsEquivalentTo(SQLiteReader.GetFieldType(i))) {
      dtNewRow[i] = SQLiteReader.GetGuid(i);
    }

    //-Int16 (Integer)------------------------------------------------
    else if(typeof(System.Int16).IsEquivalentTo(SQLiteReader.GetFieldType(i))) {
      dtNewRow[i] = SQLiteReader.GetInt16(i);
    }

    //-Int32 (Integer)------------------------------------------------
    else if(typeof(System.Int32).IsEquivalentTo(SQLiteReader.GetFieldType(i))) {
      dtNewRow[i] = SQLiteReader.GetInt32(i);
    }

    //-Int64 (Integer)------------------------------------------------
    else if(typeof(System.Int64).IsEquivalentTo(SQLiteReader.GetFieldType(i))) {
      dtNewRow[i] = SQLiteReader.GetInt64(i);
    }

    //-SByte (Integer)------------------------------------------------
    else if(typeof(System.SByte).IsEquivalentTo(SQLiteReader.GetFieldType(i))) {
      dtNewRow[i] = SQLiteReader.GetInt16(i);
    }

    //-Single (Real)--------------------------------------------------
    else if(typeof(System.Single).IsEquivalentTo(SQLiteReader.GetFieldType(i))) {
      dtNewRow[i] = SQLiteReader.GetFloat(i);
    }

    //-String (Text, UTF8)--------------------------------------------
    else if(typeof(System.String).IsEquivalentTo(SQLiteReader.GetFieldType(i))) {
      dtNewRow[i] = SQLiteReader.GetString(i);
    }

    //-TimeSpan (Text, d.hh:mm:ss.fffffff)----------------------------
    else if(typeof(System.TimeSpan).IsEquivalentTo(SQLiteReader.GetFieldType(i))) {
      dtNewRow[i] = (TimeSpan)SQLiteReader[i];
    }

    //-UInt16 (Integer)-----------------------------------------------
    else if(typeof(System.UInt16).IsEquivalentTo(SQLiteReader.GetFieldType(i))) {
      dtNewRow[i] = SQLiteReader.GetInt32(i);
    }

    //-UInt32 (Integer)-----------------------------------------------
    else if(typeof(System.UInt32).IsEquivalentTo(SQLiteReader.GetFieldType(i))) {
      dtNewRow[i] = SQLiteReader.GetInt64(i);
    }

    //-UInt64 (Integer)-----------------------------------------------
    else if(typeof(System.UInt64).IsEquivalentTo(SQLiteReader.GetFieldType(i))) {
      dtNewRow[i] = SQLiteReader.GetDecimal(i);
    }

    else {
      ErrorReturn += SQLiteReader.GetFieldType(i).ToString() + 
        " missing" + Environment.NewLine;
    }

  }

  dtData.Rows.Add(dtNewRow);

}

SQLiteConn.Close();

//-UiPath End-----------------------------------------------------------

Here as example, in the debug perspective of the Studio, how to use it. In this case I have exported the SFLIGHT table, from the SAP training model, to SQLite and I transfer this table here:

SQLite offers us fantastic possibilities to model data structures and thus to create and to persist consistent sets of data. With this approach is it possible to bring this into a standard data table and with this into the RPA context of UiPath automation.

1 Like