No driver reference is required when SQL Server or MS Access is used. If you are planning to use; Oracle, Firebird, VistaDB, Sqlite, or MySQL; you will need to add a reference to the driver in your project. For example, if using Sqlite, a reference to System.SQLite.Data.dll will need to be added.
Examples of Drivers
FirebirdSql.Data.FirebirdClient.dll
MySql.Data.dll & MySql.Data.Entity.dll
Oracle.DataAccess.dll
VistaDb.4.dll
System.SQLite.Data.dll
Npgsql.dll
Add Configuration File Settings
Add settings to your web.config or app.config for the to use the Knight .NET Data Access Layer. It is also possible to have no config file settings and simply instantiate the DataHelper and pass the provider in the constructor.
Define the connection string normally in the config file. Specify the DALConnectionStringName as the name in your connection string. Specify the Provider Type using one of the Provider types below.
<connectionStrings>
<add name="ApplicationServices" connectionString="Data Source=Invoices.vdb5;"/>
</connectionStrings>
<appSettings>
<add key="DALConnectionStringName" value="ApplicationServices"/>
<add key="DALProviderType" value="VistaDb5Net40Provider"/>
</appSettings>
Providers
Use the SessionFactory() method to get a thread static safe session. The session will be created once per thread. The config settings will be retrieved when the session is created. You can also manually create a session. See Manually Creating A Session.
'Get a thread static safe session
Dim db As IDataHelper = DataHelper.SessionFactory()
The default is a 30 day trial mode. Skip this step if you are doing a trial. After you receive your user name and license key during the purchase process, set it. See also Licensing and Distribution.
db.UserName = "John Smith 1234"
db.LicenseKey = "a9sdkw=="
By default primary keys are mapped from classes with property names of either ID or ClassNameID and table primary keys of ClassNameID. To override this behavior, change the DefaultPrimaryKeyName. By default, tables are assumed that they are plural. An example of overriding this behavior:
db.Mapper.DefaultPrimaryKeyName = "ID"
db.Mapper.PluralTableNames = False
Knight .NET Data Access Layer has the unique ability to create the database and all the tables for a namespace and keep the properties and columns synchronized. If the database already exists and the tables are already set up and no synchronization is required, this step can be skipped.
'Create the database if it does not exist (uses the database specified in the connection string)
'Create tables if they do not exist for a namespace
'Add columns that do not exist for corresponding classes
db.Setup("AcmeInc.Business.Entities")
When the primary key is the default value, a record will be inserted. When the primary key is not the default value, the record will be updated.
Dim customer As New Customer()
customer.Name = "John Smith"
db.Save(customer)
It is possible to load a single record by primary key, loading a record parents & children, or loading using a LINQ expression. Here are examples of each:
Load By Primary Key
Dim customer As Customer = db.LoadByPrimaryKey(Of Customer)(5)
Loading with Parents and Children
Dim customer As Customer = db.LoadWithChildren(Of Customer)(5)
Loading with a LINQ expression
Dim customer As Customer = db.CreateQuery(Of Customer)().FirstOrDefault(Function(o) o.CustomerId = 5)
Dim customerList As List(Of Customer) = db.CreateQuery(Of Customer)().Where(Function(o) o.Name = "John Smith").ToList()