DataEngine for .NET Standard | ComponentOne
In This Topic
    Work with Command Line Tool
    In This Topic

    The C1DataEngine Tool or Command Line Tool provides a command line interface for managing C1DataEngine workspaces. It performs the following tasks:

    The tool is used by the C1DataEngine Workbench, an extension for Visual Studio Code. It can also be used to isolate workspace initialization and data import operations from programs that execute queries on the imported data.

    Note that the C1DataEngine Tool requires .NET Core 2.2.

    Installation

    If you are installing C1DataEngine Tool for .NET Core for the first time, run the following code at the command prompt:

    Copy Code
    dotnet tool install --global C1.DataEngine.Tool

    After the tool has been installed, the c1dataengine command will be available. Run c1dataengine --help to see the available options.

    But if you are upgrading C1DataEngine Tool, you have to uninstall it first. Run the following code at the command prompt:

    Copy Code
    dotnet tool uninstall --global C1.DataEngine.Tool
    dotnet tool install --global C1.DataEngine.Tool

    Importing Data

    The Command Line tool assumes that the current directory is either empty or contains a previously initialized C1DataEngine workspace ﴾with a manifest file named metadata.xml﴿. To initialize a workspace, start with an empty directory and import one or more base tables.

    To import a base table from an existing data source, run the following command:

    Copy Code
    c1dataengine table <Name> --provider <Provider> --connect <ConnectionString> --command <CommandText>

    Argument Description Default
    Name Required. Name of the base table to be created.
    Provider Optional. Data provider to use for import ﴾csv, sqlserver, json﴿. sqlserver
    ConnectionString Required. Connection string to use for the data provider.
    CommandText Optional. SQL command for the data provider. select * from DefaultTable

    This is an example code for SQL Server:

    Copy Code
    c1dataengine table MyTable --connect
    "server=mydomain.database.windows.net;user=***;password=***;database=master"
    --command "select * from TableName"

    This is an example code for JSON (array format):

    Copy Code
    c1dataengine table MyTable --provider json --connect "URI=file.json;JSONPATH=
    [*]"

    This is an example code for JSON (object format, where items is the name of the property containing the items array):

    Copy Code
    c1dataengine table MyTable --provider json --connect
    "URI=file.json;JSONPATH=$..items[*]"

    This is an example code for CSV files:

    Copy Code
    c1dataengine table MyTable --provider csv --connect
    "URI=file.csv;LineDelimiter='\n'"

    Since the command lines can be long and difficult to type correctly, the C1DataEngine tool supports response files for input. In the previous example, the response file would look like this:

    Copy Code
    table
    MyTable
    --provider
    csv
    --connect
    URI=file.csv;LineDelimiter='\n'

    Then, the command line becomes:

    Copy Code
    c1dataengine @response.txt

    Response files are valid for all command types. The lines beginning with # are treated as comments. Blank lines are not  however allowed.

    Executing Queries

    To create and execute a query, run the following command:

    Copy Code
    c1dataengine query <Name> --table <Table> --column <Column> --join <Join> --range <Range>

     

    Argument Description
    Name Required. Name of the query to be created and executed.
    Table Required. One or more base table names.
    Column Required. One or more column descriptors.
    Join Optional. Zero or more join expressions.
    Range Optional. Zero or more range expressions.

    Since queries can be complex, they are best suited for the response file format. For example, here is a basic query that aggregates a product table by color and counts the number of products of a given color:

    Copy Code
    query
    ProductsByColor
    --table
    Products
    --column
    ProductColor
    --column
    Count=Count(ProductId)

    Note that you can include as many column descriptors as required. The general format of a column descriptor is:

    Copy Code
    name
    alias=name
    alias=operation(name)

    Only the name portion of the descriptor is required. You can use the alias qualifier to specify a different name for the column. The operation is an aggregate function such as Count, Sum, Avg, First, Last, Min, or Max. Note that the Aggregate function names are case‐sensitive.

    If you want to restrict the query to products costing at least $100, you can add the following two lines to the response file:

    Copy Code
    --range
    ProductPrice=Gte(100)

    Other comparison functions commonly used in range expressions are Lte and Eq. String arguments need not be enclosed in quotes. Note that the Comparison function names are case‐sensitive.

    When using the full command line format, you may need to enclose aggregate column descriptors in quotation marks depending upon the type of shell program you are using. For example, if you are using bash on macOS, the last argument in the following example code snippet needs to be quoted:

    Copy Code
    c1dataengine query ProductsByColor --table Products --column ProductColor --column "Count=Count(ProductId)"

    To specify a join between two tables, you must provide an expression that indicates how they are related, as in the following example:

    Copy Code
    query
    OrderSummary
    --table
    Orders
    --table
    OrderDetails
    --join
    Orders.OrderId=OrderDetails.OrderId
    --column
    Orders.OrderId
    --column
    Total=Sum(OrderDetails.Price)

    Note that the join expression and column descriptors use table‐qualified names, since multiple tables participate in this query.

    Returning Results

    To return the results from a query, or the contents of a base table, run the following command:

    Copy Code
    c1dataengine result <Name> --format <Format>

    Argument Description Default
    Name Required. Name of the base table or query.
    Format Optional. Data format to use for output ﴾csv, json﴿. Output is directed to stdout. csv

    Although you can only create queries from base tables, you can use the results of a query to formulate a new base table. Further, you can use it to build the sub-queries. For example:

    Copy Code
    c1dataengine result Query1 > Query1.csv
    c1dataengine table Query1Base --provider csv --connect
    "URI=Query1.csv;LineDelimiter='\n'"

    Query1Base can now be used as the base table for one or more queries.

    Deleting a Base Table or Query

    To delete an existing base table or query result set, run the following command:

    Copy Code
    c1dataengine delete <Name>

    Argument Description
    Name Required. Name of the base table or query to delete.

    This command deletes any intermediate queries that may have been created for range and/or join expressions.