GettingStarted.md

Getting Started

Steps

1. Import Nuget Package
2. Create Table Definition(s) (Using Code Generator)
3. Create Database Instance in code
4. Start Writing Queries

1) Import Nuget Package

Import the Nuget package QueryLite.net into your project. Either by running the Nuget command Install-Package QueryLite.net in the package manager console or by finding the package in the package manager.

2) Use Code Generator Tool To Create Table Definitions

Download the CodeGenerator.exe tool from the latest QueryLite release. See https://github.com/EndsOfTheEarth/QueryLite/releases

The zip file CodeGenerator.zip contains the code generator tool CodeGenerator.exe and this can be found under the Assets section.

Run the code generator, enter in your database connection string and click the `Load' button. This will display all the tables in your database.

Table definitions are generated when a table is selected. Copy the table definition(s) into your project.

3) Define A Database Instance

Create a single shared instance of the database. This instance can be cached in the application for queries to share.

IDatabase database = new SqlServerDatabase(name: "MyDatabase", connectionString: "Server=localhost;Database=MyDatabase;Trusted_Connection=True;");

4) Start Writing Queries

Full Code Example:

using QueryLite.Databases.SqlServer;
using QueryLite;
using Northwind.Tables;

/*
 *  Define the database. Note: Only a single instance of class is required for each unique connection string.
 */
IDatabase database = new SqlServerDatabase(name: "Northwind", connectionString: "Server=localhost;Database=Northwind;Trusted_Connection=True;");

CustomerTable customerTable = CustomerTable.Instance;
OrderTable orderTable = OrderTable.Instance;

var result = Query
    .Select(
        row => new {
            CustomerId = row.Get(customerTable.CustomerId),
            Phone = row.Get(customerTable.Phone),
            OrderId = row.Get(orderTable.OrderId),
            ShippedDate = row.Get(orderTable.ShippedDate)
        }
    )
    .From(customerTable)
    .Join(orderTable).On(customerTable.CustomerId == orderTable.CustomerId)
    .Where(customerTable.CustomerId == "NORTS" & customerTable.City.IsNotNull)
    .Execute(database);

foreach(var row in result.Rows) {

    string CustomerId = row.CustomerId;
    string? Phone = row.Phone;
    int OrderId = row.OrderId;
    DateTime? shippedDate = row.ShippedDate;
}

/*
 *  Table Definitions Below - These were generated by the code generator tool
 */
namespace Northwind.Tables {

    using System;
    using QueryLite;

    public interface ICustomer { }

    public sealed class CustomerTable : ATable {

        public static readonly CustomerTable Instance = new CustomerTable();

        public Column<string> CustomerId { get; }
        public Column<string> CompanyName { get; }
        public NullableColumn<string> ContactName { get; }
        public NullableColumn<string> ContactTitle { get; }
        public NullableColumn<string> Address { get; }
        public NullableColumn<string> City { get; }
        public NullableColumn<string> Region { get; }
        public NullableColumn<string> PostalCode { get; }
        public NullableColumn<string> Country { get; }
        public NullableColumn<string> Phone { get; }
        public NullableColumn<string> Fax { get; }

        private CustomerTable() : base(tableName: "Customer", schemaName: "dbo") {

            CustomerId = new Column<string>(this, columnName: "CustomerId", length: 5);
            CompanyName = new Column<string>(this, columnName: "CompanyName", length: 40);
            ContactName = new NullableColumn<string>(this, columnName: "ContactName", length: 30);
            ContactTitle = new NullableColumn<string>(this, columnName: "ContactTitle", length: 30);
            Address = new NullableColumn<string>(this, columnName: "Address", length: 60);
            City = new NullableColumn<string>(this, columnName: "City", length: 15);
            Region = new NullableColumn<string>(this, columnName: "Region", length: 15);
            PostalCode = new NullableColumn<string>(this, columnName: "PostalCode", length: 10);
            Country = new NullableColumn<string>(this, columnName: "Country", length: 15);
            Phone = new NullableColumn<string>(this, columnName: "Phone", length: 24);
            Fax = new NullableColumn<string>(this, columnName: "Fax", length: 24);
        }
    }

    public interface IOrder { }

    public sealed class OrderTable : ATable {

        public static readonly OrderTable Instance = new OrderTable();

        public Column<int> OrderId { get; }
        public NullableColumn<string> CustomerId { get; }
        public NullableColumn<int> EmployeeId { get; }
        public NullableColumn<DateTime> OrderDate { get; }
        public NullableColumn<DateTime> RequiredDate { get; }
        public NullableColumn<DateTime> ShippedDate { get; }
        public NullableColumn<int> ShipVia { get; }
        public NullableColumn<decimal> Freight { get; }
        public NullableColumn<string> ShipName { get; }
        public NullableColumn<string> ShipAddress { get; }
        public NullableColumn<string> ShipCity { get; }
        public NullableColumn<string> ShipRegion { get; }
        public NullableColumn<string> ShipPostalCode { get; }
        public NullableColumn<string> ShipCountry { get; }

        private OrderTable() : base(tableName: "Order", schemaName: "dbo", enclose: true) {

            OrderId = new Column<int>(this, columnName: "OrderId", isAutoGenerated: true);
            CustomerId = new NullableColumn<string>(this, columnName: "CustomerId", length: 5);
            EmployeeId = new NullableColumn<int>(this, columnName: "EmployeeId");
            OrderDate = new NullableColumn<DateTime>(this, columnName: "OrderDate");
            RequiredDate = new NullableColumn<DateTime>(this, columnName: "RequiredDate");
            ShippedDate = new NullableColumn<DateTime>(this, columnName: "ShippedDate");
            ShipVia = new NullableColumn<int>(this, columnName: "ShipVia");
            Freight = new NullableColumn<decimal>(this, columnName: "Freight");
            ShipName = new NullableColumn<string>(this, columnName: "ShipName", length: 40);
            ShipAddress = new NullableColumn<string>(this, columnName: "ShipAddress", length: 60);
            ShipCity = new NullableColumn<string>(this, columnName: "ShipCity", length: 15);
            ShipRegion = new NullableColumn<string>(this, columnName: "ShipRegion", length: 15);
            ShipPostalCode = new NullableColumn<string>(this, columnName: "ShipPostalCode", length: 10);
            ShipCountry = new NullableColumn<string>(this, columnName: "ShipCountry", length: 15);
        }
    }
}