- Import Nuget Package
- Create Table Definition(s) (Using Code Generator)
- Create Database Instance in code
- Start Writing Queries
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.
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.
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;");
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);
}
}
}