Wiki

##DbHug ##CRUD (Create, Read, Update, Delete) or SUID (Select, Update, Insert, Delete)

CRUD operations lives in Wxen.Data.DbHug.CRUD namespace.
All of CRUD methods are extension methods for DbHug, so do not forget to add using namespace.

#!csharp
using Wxen.Data.DbHug;
using Wxen.Data.DbHug.CRUD;

###Selecting

#!csharp
var result = dbHug.Select<Person>(from: "PERSONS", before:"TOP(3)", after: "WHERE FavoriteNumber IS NOT NULL");

will generate and execute:

#!sql
SELECT TOP(3) Id, FirstName, LastName, BirthDate, Age, ContactId FROM PERSONS WHERE FavoriteNumber IS NOT NULL;

#!csharp
var result = dbHug.Select<Person>();

executing SQL will be

#!sql
SELECT Id, FirstName, LastName, BirthDate, Age, ContactId FROM Persons;

Select single value by SelectById()

#!csharp

var result = SelectById<Phone>(10);

Select values with WHERE Select(Expression<Func<T, bool>> whereExpression)

#!csharp
var date = new Date(1960,1,1);
var result = dbHug.Select<Person>(p => p.BirthDate < date);

#!sql
SELECT Id, FirstName, LastName, BirthDate, Age, ContactId FROM Persons WHERE BirthDate < @_dp_where;

parameter @_dp_where has date value.

#!csharp
var result = dbHug.Select<Person>(p => p.FirstName.Contains("oh"));

#!sql
SELECT Id, FirstName, LastName, BirthDate, Age, ContactId FROM Persons WHERE FirstName LIKE '%'+@_dp_where+'%';

Dynamic

#!csharp
var result = dbHug.Select("PERSONS", p => p.Column<string>("FirstName").Contains("oh"));

#!sql
SELECT * FROM PERSONS WHERE FirstName LIKE '%'+@_dp_where+'%';

More about Where

###Updating

#!csharp
var val = SelectById<Person>(1);
val.LastName = "Doe";

dbHug.Update<Person>(val);

SQL will be:

#!sql
UPDATE Persons
SET FirstName=@FirstName, LastName=@LastName, BirthDate=@Birth, ContactId=@ContactId
WHERE Id=@Id;

Yes! This method will update whole row!

Updating set

#!csharp
//Updating set
//will go within transaction
List<Person> persons = ...
dbHug.UpdateMany<Person>(persons);

Dynamic value update:

#!csharp
var val = new { Id=1, FirstName="John", LastName = "Doe" };

dbHug.Update("Person", "Id", val);

SQL will be:

#!sql
UPDATE Persons
SET FirstName=@FirstName, LastName=@LastName
WHERE Id=@Id;

Update values with WHERE Update<T>(object value, Expression<Func<T, bool>> whereExpression)

#!csharp
dbHug.Update<Person>(new { LastName = "Wayne" }, p => p.FirstName == "John");

#!sql
UPDATE Persons SET LastName=@LastName WHERE FirstName = @_dp_where;

In case if pass anonymous type as an value object in Update<T> method, all present properties will be added to Sql command, except fields mapped as Id and columns generated by database. In case if the object and generic type will be the same, only not default values will be added to Sql command, Id will be ignored as well even if value is not default (default values means default(T). For reference types it is null, for value types it is some default value).

#!csharp
dbHug.Update<Person>(new Person{ LastName = "Wayne" }, p => p.FirstName == "John" && p.Age > 50);

#!sql
UPDATE Persons SET LastName=@LastName WHERE (FirstName = @_dp_where) AND (Age > @_dp_where__1);

To update fields that is restricted by mappings or not mapped there is an overload of the Update method. Or it is useful for any case...

#!csharp
dbHug.Update("PERSONS", new { Id = 100 }, p => p.Column<int>("Id") == 1);

#!sql
UPDATE PERSONS SET Id=@Id WHERE Id = @_dp_where;

###Inserting Insert() is similar to Update()... but not default Id be passed in insert command.
Example then:

#!csharp
var phone = new Phone
{
  Number = "+1 111 111 11 1111",
  ContactId=1
};
dbHug.Insert<Phone>(phone);

executes:

#!sql
INSERT INTO Phone (Number, ContactId, IsPersonal)
VALUES(@Number, @ContactId, @IsPersonal);

In case if the mapping is set to insert key then SQL query will be:

#!sql
INSERT INTO Test (Id, Number, ContactId, IsPersonal)
VALUES(@Id, @Number, @ContactId, @IsPersonal);

Insert many is similar to UpdateMany()

#!csharp
List<Phones> phones= ...;
dbHug.InsertMany<Phone>(phones);

Dynamic insert

#!csharp
var phone = new
{
  Number = "+1 111 111 11 1111",
  ContactId=1
};
dbHug.Insert("Phone", phone);

executes:

#!sql
INSERT INTO Phone (Number, ContactId)
VALUES(@Number, @ContactId);

###Deleting

#!csharp
var phone = SelectById<Phone>(10);
//test is some data from db to be deleted
dbHug.Delete(phone);

executes:

#!sql
DELETE FROM Phones WHERE Id=@Id;

//well-known id of object to be deleted
int id = 404;
dbHug.DeleteById<Phone>(id);

DeleteMany() and DeleteManyById() is present.

Delete values with WHERE Delete<T>(Expression<Func<T, bool>> whereExpression)

#!csharp
dbHug.Update<Person>(p => p.FirstName == "John");

#!sql
DELETE FROM Persons WHERE FirstName = @_dp_where;

###Count Count() simply executes Sql COUNT statement.

dbHug.Count<Phone>();

executes:

#!sql
SELECT COUNT(*) FROM Phone

Method may take where expression parameter, property name to count for specific column and distinct.

dbHug.Count<Phone>(whereExpression: p => p.Number.Contains("+1"), propOrField: p => p.Number, distinct: true);

executes:

#!sql
SELECT COUNT(DISTINCT Number) FROM Phone WHERE Number LIKE @dp_where

###Where clause

Examples will be only with Expression, but it is similar for all CRUD methods.

Equals operator:
o => o.Id == 100 => WHERE Id = @_dp_where;
o => o.Id != 100 => WHERE Id <> @_dp_where;
Same for .Equals(object) method: o => o.Id.Equals(100) => WHERE Id = @_dp_where; allowed to use only Equals(object) method overload.
o => !o.Id.Equals(100) => WHERE Id <> @_dp_where;

Greater and less operators:
o => o.Id < 100 => WHERE Id < @_dp_where;
o => o.Id > 100 => WHERE Id > @_dp_where;

IS NULL, IS NOT NULL:
o => o.Id.IsNull() => WHERE Id IS NULL; IsNull() is extension method in Wxen.Data.DbHug namespace.
o => !o.Id.IsNull() => WHERE Id IS NOT NULL;
Same:
o => o.Id.Equals(null) => WHERE Id IS NULL;
o => !o.Id.Equals(null) => WHERE Id IS NOT NULL;
For Nullable<T> valuse it is possible to use HasValue Property:
o => !o.Id.HasValue => WHERE Id IS NULL;
o => o.Id.HasValue => WHERE Id IS NOT NULL;

LIKE:
o => o.Name.Contains("o") => WHERE Name LIKE '%'+@_dp_where+'%'; allowed to use only Contains(string) overload. Same for StartsWith(string) and EndsWith(string)
o => o.Name.StartsWith("o") => WHERE Name LIKE @_dp_where+'%';
o => o.Name.EndsWith("o") => WHERE Name LIKE '%'+@_dp_where;

Other methods except Equals(object), Contains(string), StartsWith(string) and EndsWith(string) are not allowed.

It is possible to combine statements:
o => o.Id == 100 && o.Name.StartsWith("o") => WHERE (Id = @_dp_where) AND (Name LIKE @_dp_where__1+'%')

For dynamic values there is a DbDynamic type:
Select method example:

#!csharp
var result = dbHug.Select("PERSONS", p => p.Column<int>("Id") == 1);

will execute:

#!sql
SELECT * FROM PERSONS WHERE Id = @_dp_where;

Column<T> for static types, generic type for a type of a column, parameter for a column name:
o => o.Column<int?>("Id").HasValue => WHERE Id IS NOT NULL;
It provides ability to use methods specific for that type. For string it is Contains for example.
Column for object:
o => o.Column("Id").Equals(1) => WHERE Id = @_dp_where;
Same for indexer:
o => o["Id"].Equals(1) => WHERE Id = @_dp_where;

####Connections ... Partials