Home

Smark.Data

组件是基于ado.net接口扩展的数据访问组件,提供基于对象描述的条件操作方式,支持各种不同数据库,操作方式简洁高效。

1.8

        [TestMethod]
        public void Result_GetValue()
        {
            Query<Employee> query = 3;
            Console.WriteLine(query.Value.FirstName);
        }
        [TestMethod]
        public void Result_GetValue1()
        {
            Query<Employee> query = Employee.employeeID == 3;
            Console.WriteLine(query.Value.FirstName);
        }
        [TestMethod]
        public void Result_GetValue2()
        {
            Query<Employee> query = "employeeid=3";
            Console.WriteLine(query.Value.FirstName);
        }
        [TestMethod]
        public void Result_GetValue3()
        {
            Query<string> query = "select firstName from employees where employeeid=3";
            Console.WriteLine(query.Value);
        }
        [TestMethod]
        public void Result_GetValues1()
        {
            Query<List<Employee>> query = "";
            foreach (Employee item in query.Value)
            {
                Console.WriteLine(item.FirstName);
            }
        }
        [TestMethod]
        public void Result_GetValues2()
        {
            //存储过程
            CustOrderHist coh = new CustOrderHist();
            coh.CustomerID = "ALFKI";
            Query<IList<OrderHist>> query = coh;
            foreach (OrderHist item in query.Value)
            {
                Console.WriteLine(item.ProductName);
            }
        }

1.7

    var emp = 3.Load_<Employee>();
    emp.Notes = "smark.data";
    emp.Save_();
            
    (Employee.employeeID == 111).Edit<Employee>(o => { o.Notes = "text"; });

    SQL sql = "delete from employees where employeeid=@p1";
    sql["p1", 11].Execute();

    sql = "select * from orders where employeeid=@p1 and orderdate >@p2";
    var items= sql["p1", 1]["p2", "1996-1-1"].List<Order>();

    var items = (Order.employeeID == 1 & Order.orderDate > "1996-1-1").List<Order>();

    sql = "select * from orders where 1=1";
    if (from != null)
        sql = " and orderdate >=@p1" + sql["p1", from.Value];
    if (to != null)
        sql = " and orderdate <=@p2" + sql["p2", to.Value];
    items = sql.List<Order>();

    Expression exp = new Expression();
    if (EmployeeID != null)
        exp &= Order.employeeID == EmployeeID;
    if (CustomerID != null)
        exp &= Order.customerID == CustomerID;
    if (OrderDateFrom != null)
        exp &= Order.orderDate >= OrderDateFrom;
    if (OrderDateTo != null)
        exp &= Order.orderDate <= OrderDateTo;
    if (RequiredDateFrom != null)
        exp &= Order.requiredDate >= RequiredDateFrom;
    if (RequiredDateTo != null)
        exp &= Order.requiredDate <= RequiredDateTo;
    items = exp.List<Order>();

简易数据操作

制实体类型后,只需通过实体成员构造的条件表达式就能进行数据查找操作
  • 实体定义
     [Table("customers")]
    public interface ICustomer
    {
        [ID]
        string CustomerID { get; set; }
        [Column]
        string CompanyName { get; set; }
        [Column]
        string ContactName { get; set; }
        [Column]
        string Address { get; set; }
        [Column]
        string City { get; set; }
        [Column]
        string Region { get; set; }
        [Column]
        string PostalCode { get; set; }
        [Column]
        string Country { get; set; }
        [Column]
        string Phone { get; set; }
        [Column]
        string Fax { get; set; }
    }
组件提供一个插件帮助以上接口生成附属类文件,插件下载http://smark.codeplex.com/releases/view/45483#DownloadId=175073(插件暂时只支持c#,文件custom tools:i2m)
  • 查询某个国家和城市的客户
             Expression exp = Customer.country == "USA" & Customer.city == "OR";
             var items = exp.List<Customer>();

设置访问数据类型和连接信息

组件可以通过配置文件或代码的方式设置需要访问的数据设备类型和连接信息
  • 代码设置
            string dbpath = @"Data Source=..\\..\\..\\lib\\northwind.db;Pooling=true;FailIfMissing=false;";
            DBContext.SetConnectionDriver<SqliteDriver>(ConnectionType.Context1);
            DBContext.SetConnectionString(ConnectionType.Context1, dbpath);
组件可以设置多个数据访问类型,通过ConnectionType来设置相关数据库访问类型,在操作数据如果不指定数据库,则使会使第1个配置的数据库类型。

数据增,删,改

  • 增加
            Employee emp = new Employee();
            emp.FirstName = "fan";
            emp.LastName = "henry";
            emp.City = "guangzhou";
            DBContext.Save(emp);
组件会根据设置成员值的情况来更新相关字段,当没有更改值的属性并不会生成相关SQL添加到数据库中。
  • 删除
            emp = DBContext.Load<Employee>(3);
            DBContext.Delete(emp);
组件除了提供这种删除方法外,还可以在编写条件的时候直接进行删除操作
  • 修改
            emp = DBContext.Load<Employee>(3);
            emp.Notes = "test,remark";
            DBContext.Save(emp);
批修改后面会提到,通过条件表达式简单实现以上功能.

条件表达式

  • 简单查询
            Expression exp = Customer.country == "USA" & Customer.city == "OR";
            var items = exp.List<Customer>();
  • 组合查询
            Expression exp = new Expression();
            if (string.IsNullOrEmpty(employeeid))
                exp &= Order.employeeID == employeeid;
            if (string.IsNullOrEmpty(customerid))
                exp &= Order.customerID == customerid;
            if (from != null)
                exp &= Order.orderDate > from;
            if(to !=null)
                exp &= Order.orderDate <to;
            var items = exp.List<Order>();
  • 条件复用
            exp = Employee.employeeID == 6;
            var employees = exp.List<Employee>();
            var orders = exp.List<Order>();
            exp.Delete<Employee>();
            exp.Delete<Order>();
  • 修改数据
            (Customer.country == "USA").Edit<Customer>(d => { d.City = "abs"; });
  • 删除数据
            (Customer.country == new[] { "USA", "UK" }).Delete<Customer>();
  • 数据统计
            exp.Count<Customer>();
            exp.Sum<double, Product>(Product.unitPrice.Name);

关联,统计查询

  • 单一对象制定
    [Table("employees")]
    public interface IEmployeeView
    {
        [ID]
        string EmployeeID { get; set; }
        [Column]
        string FirstName { get; set; }
        [Column]
        string LastName { get; set; }
    }
            Expression exp = new Expression();
            var empviews = exp.List<EmployeeView>();
  • 表关联查询
    [Table("customers inner join orders on customers.customerid=orders.customerid")]
    public interface ICustomerOrders
    {
        [Column]
        string CompanyName { get; set; }
        [Column]
        string ContactName { get; set; }
        [Column]
        [DateTimToLong]
        DateTime OrderDate { get; set; }
        [Column]
        [DateTimToLong]
        DateTime RequiredDate { get; set; }
        [Column]
        string ShipName { get; set; }
        [Column]
        string ShipAddress { get; set; }
        [Column]
        string ShipCity { get; set; }
        [Column]
        string ShipRegion { get; set; }
        [Column]
        string ShipPostalCode { get; set; }
        [Column]
        string ShipCountry { get; set; }
    }
            Expression exp = new Expression();
            var empviews = exp.List<CustomerOrders>();
  • 数据汇总统计
数据统计需要制定相关统计对象,以下是统计各城市客户订单数量
    [Table("customers inner join orders on customers.customerid=orders.customerid")]
    public interface ICustOrderGroupByCity
    {
        [Column]
        string Country { get; set; }
        [Column]
        string City { get; set; }
        [Count]
        int Count { get; set; }
    }
            Expression exp = new Expression();
            var empviews = exp.List<CustOrderGroupByCity>();

事务

            Expression exp = new Expression();
            exp = Employee.employeeID == 5;
            using (IConnectinContext cc = DBContext.Context1)
            {
                cc.BeginTransaction();
                exp.Delete<Order>();
                exp.Delete<Employee>();
                cc.Commit();
            }
            DBContext.TransactionExecute(c => {
                exp.Delete<Order>();
                exp.Delete<Employee>();
                
            });

运行时更新映射表对象

多数据库访问和切换

支持那些数据?

组件内置支持MSSQ,MYSQL,ACCESS,SQLITE,ORACLE等数据库,使用者可以根据需要扩展对SQL92支持的数据访问设备。
  • 实现对sqlite支持的类型
    public class SqliteDriver : Smark.Data.DriverTemplate<
         System.Data.SQLite.SQLiteConnection,
         System.Data.SQLite.SQLiteCommand,
         System.Data.SQLite.SQLiteDataAdapter,
         System.Data.SQLite.SQLiteParameter,
         Smark.Data.SqlitBuilder>
    {
    }

Last edited Mar 1, 2014 at 12:17 PM by smarkhenry, version 124