DNet.ORM.SqlServer 1.0.0

first define the entity

public class Book { [Key(IsAutoGenerated = true)] public int? BookID { get; set; } public string BookName { get; set; } public int? AuthorID { get; set; }

[NotColumn]
public string AuthorName { get; set; }
public double? Price { get; set; }

public DateTime? PublishDate { get; set; }

}

public class Author { [Key(IsAutoGenerated =true)] //[Sequence("emp_sequence")] public int? AuthorID { get; set; } public string AuthorName { get; set; } public int? Age { get; set; } public bool? IsValid { get; set; } } then config the connection strings such as demo,DNetContext also receive a ConnectionStringSettings parameter

public DNetContext(ConnectionStringSettings settings)
    : base(settings)
{
}

1.add

    using (DNetContext db = new DNetContext())
    {
        var authorid = db.Add(new Author { AuthorName = "jim", Age = 30, IsValid = true });
        db.Add(new Book { BookName = "c#", Price = 20.5, PublishDate = DateTime.Now, AuthorID = authorid });
    }

2.delete

    using (DNetContext db = new DNetContext())
    {
        var author = db.GetSingle<Author>(m => true, q => q.OrderBy(m => m.AuthorID));
        var effect = db.Delete(author);

        int authorid = db.GetMax<Author>(m => (int)m.AuthorID);
        db.Delete<Author>(m => m.AuthorID == authorid);

    }

3.update

    using (DNetContext db = new DNetContext())
    {
        var author = db.GetSingle<Author>(m => true, q => q.OrderBy(m => m.AuthorID));
        if (author != null)
        {
            author.AuthorName = "jim";
            var effect = db.Update(author);
        }
        db.Update<Author>(m => m.AuthorName = "jim", m => m.AuthorID == 1);
        db.Update<Author>(m => { m.AuthorName = "jim"; m.Age = 30; }, m => m.AuthorID == 1);
        db.Update<Author>(m => new Author { AuthorName = m.AuthorName + "123", IsValid = true }, m => m.AuthorID == 1);
        db.UpdateOnlyFields<Author>(new Author { AuthorName = "123", Age = 20, AuthorID = 1, IsValid = true }, m => new { m.AuthorName, m.Age }, m => m.AuthorID == 1);
        db.UpdateIgnoreFields<Author>(new Author { AuthorName = "123", Age = 20, AuthorID = 1, IsValid = true }, m => m.AuthorName, m => m.AuthorID == 1);
    }

4.query (single table)

    using (DNetContext db = new DNetContext())
    {
        var author = db.GetSingle<Author>(m => true, q => q.OrderBy(m => m.AuthorID));

        var book = db.GetSingle<Book>(m => ((DateTime)m.PublishDate).ToString("yyyy-MM-dd") == "2017-11-11");

        var authors = db.GetList<Author>(m => string.IsNullOrEmpty(m.AuthorName) && m.IsValid == true);

        List<dynamic> name = db.GetDistinctList<Author>(m => m.AuthorName.StartsWith("jim") && m.IsValid == true, m => m.AuthorName + "aaa");

        List<string> name1 = db.GetDistinctList<Author, string>(m => m.AuthorName.IndexOf("jim") == 2 && m.IsValid == true, m => m.AuthorName);

        var books = db.GetList<Book>(m => SubQuery.GetList<Author>(n => n.AuthorID > 10, n => n.AuthorID).Contains(m.AuthorID));

        books = db.GetList<Book>(m => m.AuthorID >= SubQuery.GetSingle<Author>(n => n.AuthorID == 10, n => n.AuthorID));

        var authorid = db.GetMax<Author>(m => (int)m.AuthorID);

        WhereBuilder<Author> where = new WhereBuilder<Author>();
        where.And(m => m.AuthorName.Contains("jim"));
        where.And(m => m.AuthorID == 3);
        where.Or(m => m.IsValid == true);
        db.GetList<Author>(where.WhereExpression);


        PageFilter page = new PageFilter { PageIndex = 1, PageSize = 10 };
        page.And<Author>(m => "jim green".Contains(m.AuthorName));
        page.OrderBy<Author>(q => q.OrderBy(m => m.AuthorName).OrderByDescending(m => m.AuthorID));
        PageDataSource<Author> pageSource = db.GetPage<Author>(page);
    }

5.query (multi tables)

     using (DNetContext db = new DNetContext())
    {

        var books = db.JoinQuery.LeftJoin<Book, Author>((m, n) => m.AuthorID == n.AuthorID && n.IsValid == true)
            .Fields<Book, Author>((m, n) => new { BookName = m.BookName + "123", AuthorName = SqlFunctions.Count(n.AuthorName) })
            .OrderByAsc<Book>(m => m.BookName)
            .GroupBy<Book, Author>((m, n) => new { m.BookName, n.AuthorName })
            .Where<Book, Author>((m, n) => m.Price > 10 && n.IsValid == true && SubQuery.GetList<Author>(n1 => n1.AuthorID >= 1, n1 => n1.AuthorID).Contains(m.AuthorID))
            .GetList<Book>();



        var join = db.JoinQueryAlias.LeftJoin<Book, Author>((m, n) => m.AuthorID == n.AuthorID && n.IsValid == true)
            .InnerJoin<Book, Author>((m1, n) => m1.AuthorID == n.AuthorID && n.IsValid == true)
            .Fields<Book, Author>((m1, n) => new { AuthorName1 = m1.BookName + n.AuthorName, n })
            .OrderByAsc<Book>(m => m.BookName);
        PageFilter page = new PageFilter { PageIndex = 1, PageSize = 10 };
        join.GetPage<Book>(page);

    }

6.query (sql)

    using (DNetContext db = new DNetContext())
    {
        StringBuilder sql = new StringBuilder();
        List<DbParameter> parameters = new List<DbParameter>();

        sql.AppendFormat(@"SELECT {0},A.AuthorName FROM Book B 
        LEFT JOIN Author A ON A.AuthorID=B.AuthorID WHERE", SqlBuilder.GetSelectAllFields<Book>("B"));
        sql.Append(" B.BookID>@BookID ");
        parameters.Add(db.GetDbParameter("BookID", 1));

        PageFilter pageFilter = new PageFilter { PageIndex = 1, PageSize = 5 };
        pageFilter.OrderText = "B.BookID ASC";
        PageDataSource <Book> books = db.GetPage<Book>(sql.ToString(), pageFilter, parameters.ToArray());

        List<Book> bks = db.GetList<Book>(sql.ToString(), parameters.ToArray());
    }

7.db transaction

    using (DNetContext db = new DNetContext())
    {
        db.DataBase.BeginTransaction();
        try
        {
            List<Author> authors = new List<Author>();
            for (int i = 0; i <= 100; i++)
            {
                authors.Add(new Author { AuthorName = "jack" + i.ToString(), Age = 20, IsValid = true });
            }
            db.Add(authors);
            db.DataBase.Commit();
        }
        catch
        {
            db.DataBase.Rollback();
        }
    }

8.distributed transaction

    DNetTransaction transaction = new DNetTransaction();
    transaction.BeginTransaction();
    try
    {
        using (DNetContext db = new DNetContext())
        {
            List<Author> authors = new List<Author>();
            for (int i = 0; i <= 100; i++)
            {
                authors.Add(new Author { AuthorName = "测试" + i.ToString(), Age = 20, IsValid = true });
            }
            db.Add(authors);
            transaction.Commit();
        }
    }
    catch
    {
        transaction.Rollback();
    }