Quick start.

To Start with LightADO.net add the LightADO.net reference in you project by ruining Nuget Command:

PM> Install-Package LightAdo.net

Once the reference get installed, you can immediately get a DataTable from execute a query like this:

DataTable table = new Query().EexecuteToDataTable("select * from Applications", CommandType.Text);

The Query object will get the connection string by default from the connection string section in the configuration file by default it named DefaultConnection as you can see:

< connectionstrings>
     < add name="DefaultConnection" connectionstring="Data Source=.\SQLEXPRESS;Initial Catalog=YourDataBase;Integrated Security=True" />
< / connectionstrings>
                    

Configuration

This section will explains the configuration needed to setup lightado.net

Connection String

< connectionstrings>
     < add name="DefaultConnection" connectionstring="Data Source=.\SQLEXPRESS;Initial Catalog=YourDataBase;Integrated Security=True" />
< /connectionstrings>
                    

Query newQuery = new Query("NameOfTheConnectionInTheConfigrationFile", True);

NonQuery newNonQuery = new NonQuery(@"Data Source=.\SQLEXPRESS;Initial Catalog=YourDataBase;Integrated Security=True", False);

Encryption Key

if you want to enable encryption you will need to set the encryption key app setting section

  < appSettings>
    < add key="EncryptKey" value="745jhduerhsm"/>
  < /appSettings>

Introduction

In general Most method will take 3 parameter

• Command Name or Command Text: the command can be text or the stored procedures name
• CommandType can be:
  • Stored procedures
  • Text
  • Table
• Params of parameters: parameter object

Example:

DataTable table = new Query().EexecuteToDataTable("select * from Applications", System.Data.CommandType.Text);
Console.ReadLine();

Execute Command Text with parameter

DataTable table = new Query().EexecuteToDataTable("select * from Applications where ID = @ID", System.Data.CommandType.Text, new Parameter("ID", 324));
Console.ReadLine();

Execute with stored procedure

DataTable table = new Query().EexecuteToDataTable("Application_Get", System.Data.CommandType.StoredProcedure);
Console.ReadLine();

Execute Query

Query Class will handle will of sqlCOmmand.ExecuteQuery method, but you can do more the Query class, as you can get one of those type:

• DataTable
• DataSet
• Object
• List of Object
• dynamic Object
• List of dynamic Object

Execute To Data Table

DataTable table = new Query().EexecuteToDataTable("Application_Get", System.Data.CommandType.StoredProcedure);
Console.ReadLine();

Execute To Data Set

DataSet table = new Query().EexecuteToDataSet("Application_Get", System.Data.CommandType.StoredProcedure);
Console.ReadLine();

Execute To Object

Application application = new Query().EexecuteToObject< Application>("Application_Get_ByID", System.Data.CommandType.StoredProcedure, new Parameter("ID", 324));
Console.ReadLine();

Execute To List Of Object

List< Application> application = new Query().EexecuteToListOfObject< Application>("select * from Applications", System.Data.CommandType.StoredProcedure);
Console.ReadLine();

Execute To Dynamic object

dynamic application = new Query().EexecuteToDynamicObject("select * from Applications where ID = @ID", System.Data.CommandType.Text, new Parameter("ID", 324));
Console.WriteLine(application.Name);
Console.ReadLine();

Execute To List Of Dynamic Object

List< dynamic> applications = new Query().EexecuteToListOfDynamicObject("select * from Applications", System.Data.CommandType.Text);
Console.ReadLine();

Execute NunQuery

This object can execute NonQuery with auto mapped object or with manual mapping

Execute NonQuery with manual mapping

bool result = new NonQuery().Execute("Insert into Applications (Name, Password) values (@Name, @Password)",
                            CommandType.Text,
                            new Parameter("Name", "Test"),
                            new Parameter("Password", "123456"));

Execute NonQuery with auto mapping

bool result = new NonQuery().Execute< Application>("Application_Create",
                            CommandType.StoredProcedure,
                            new Parameter("Name", "Test"),
                            new Parameter("Password", "123456"));

Execute NonQuery with output values

Output parameter is in the property of object

if (new NonQuery().Execute< Application>("Application_Create", application) == true)
{
	Console.WriteLine(application.ID);
}

Create PROCEDURE [dbo].[Application_Create]
	 @ID int output,
     @Merchant int,
     @Name nvarchar(1500),
     @IsEnabled bit,
     @CreateDate datetime,
     @FailedURL ntext,
     @SuccessURL ntext,
     @Notes ntext, 
	 @AuthenticationKey ntext
AS
BEGIN
	insert into Applications(Merchant, Name, IsEnabled, CreateDate, FailedURL, SuccessURL, Notes, AuthenticationKey)
	values (@Merchant, @Name, @IsEnabled, @CreateDate, @FailedURL, @SuccessURL, @Notes, @AuthenticationKey)
	set @ID = SCOPE_IDENTITY();
	
END

output parameter is not one of the properties of the object:

Parameter parameter = new Parameter("ThisOutputParameter", null, ParameterDirection.Output);
if (new NonQuery().Execute("Application_Create", application, parameter) == true)
{
	Console.WriteLine(parameter.Value);
	Console.WriteLine(application.ID);
}

Object Relationship

Console.WriteLine(Application.Merchant.Name)

namespace ConsoleApplication1
{
    using LightADO;

    class Application
    {
        [PrimaryKey]
        public string ID { get; set; }

        public string Name { get; set; }

        [ForeignKey]
        public Merchant Merchant { get; set; }
    }
}

namespace ConsoleApplication1
{
    using LightADO;

    class Merchant
    {
        public Merchant()
        {
        }

        public Merchant(int id)
        {
            Merchant mecrhant = new Query().EexecuteToObject("select * from Merchants where ID = @ID", System.Data.CommandType.Text, new Parameter("ID", id);
            if(mecrhant != null)
            {
                this.ID = mecrhant.ID;
                this.Name = mecrhant.Name;
            }
        }

        [PrimaryKey]
        public int ID { get; set; }

        public int Name { get; set; }
    }
}

Once the LightADO.net find a property marked as foreign key it will call a constructor with integer value int

Encryption

You can use the Encrypted Attribute class to enable encryption for class or single property, to encrypt a every string property in class mark that as encrypted

    [Encrypted]
    public class Application
    {
        public int ID { get; set; }

        public int Merchant { get; set; }

        public string Name { get; set; }

        public bool IsEnabled { get; set; }

        public string FailedURL { get; set; }

        public string SuccessURL { get; set; }

        public string Notes { get; set; }

        public string AuthenticationKey { get; set; }

        public DateTime CreateDate { get; set; }
    }

    
    public class Application
    {
        public int ID { get; set; }

        public int Merchant { get; set; }

        public string Name { get; set; }

        public bool IsEnabled { get; set; }

        public string FailedURL { get; set; }

        public string SuccessURL { get; set; }

        public string Notes { get; set; }

        [Encrypted]
        public string AuthenticationKey { get; set; }

        public DateTime CreateDate { get; set; }
    }

Validate Object

Instead of writing if statement LightADo.net give you the option to Separate the validation part then the object by calling the AutoValidation calss

Validate property for null

To validate property for null mark that property for NullValidation and set the AllowNull = false , and the error message in case this property was set as null

[NullValidation(AllowNull = false, ErrorMessage = "Application Name can't be null")]
public string Name { get; set; }

Validate property for Regular Expression

[RegularExpressionsValidation(RegularExpression = @"^([\w\.\-]+)@([\w\-]+)((\.(\w){2,3})+)$", ErrorMessage = "Email not correct")]
public string Email { get; set; }

Create Custom Validation

namespace ConsoleApplication1
{
    using LightADO;
    using System;

    class MerchantIsExist : AutoValidation
    {
        public override bool Validate(object propertyValue)
        {
            throw new NotImplementedException();
        }
    }
}

[MerchantIsEexist(ErrorMessage = "Merchant is not exist")]
[ForeignKey]
public Merchant Merchant { get; set; }

Exception

This section will explain the exception that throw by LightADO.net

LightADOException

try 
{
	// your Code
}  
catch(LightADOException ex)
{
	Console.WriteLine(ex);
}

LightADOValidationException

try 
{
	// your Code
}  
catch(LightADOValidationException ex)
{
	Console.WriteLine(ex);
}

Error List