【LeanEAP.NET】精益企业应用平台实战----构造DataAccessor与增删改查

 

创建DataAccessor，通过Insert、Delete、Update、Select方法构造SQL命令并实现数据增删改查。 

 

创建DataAccessor

使用连接字符串创建DataAccessor

DataAccessor da = new DataAccessor(@"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\App_Data\Demo.mdf;Integrated Security=True;User Instance=True");

 

因为Sql2000与Sql2005的连接字符串是一样的，DataAccessor无法识别，需要指定相应的DbCategery

DataAccessor da = new DataAccessor(DbCategory.SqlServer9, 
                @"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\App_Data\Demo.mdf;Integrated Security=True;User Instance=True");

 

或者使用配置文件中的connectionStrings

DataAccessor da = new DataAccessor("connectionStringsInConfig");

providerName有效的值为：sql、sql2005、oracle、access、mysql

<connectionStrings>
    <add name="connectionStringsInConfig"
        connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\App_Data\Demo.mdf;Integrated Security=True;User Instance=True"
        providerName="sql2005" />
</connectionStrings>

 

 

Select

读取指定的字段得到DataSet，IDataReader或者只读取一个值。

 DataAccessor da = new DataAccessor("connectionStringsInConfig");

 AccountGroupTable table = new AccountGroupTable();

 SelectStatement sql = da.Select(table, table.Id, table.Name, table.UpdateDate, table.UpdateUser)
    .Where(table.Visible == true)
    .OrderBy(table.Name.Asc);

 System.Data.DataSet ds = sql.ToDataSet();

 System.Data.IDataReader dr = sql.ToDataReader();
 
 int id  = sql.ToScalar<int>();

 

把数据读到AccountGroupModel

  DataAccessor da = new DataAccessor("connectionStringsInConfig");

  AccountGroupTable table = new AccountGroupTable();

  SelectStatement sql = da.Select(table, table.AllColumns())
       .Where(table.Visible == true);

  if (!name.IsNullOrEmpty())
       sql.Where(table.Name == name);

  using (SafeDataReader sdr = sql.ToSafeDataReader())
  {
       AccountGroupModels models = new AccountGroupModels();
       while (sdr.Read())
       {
             AccountGroupModel m = new AccountGroupModel();
             sdr.Fetch(m, table);
             models.Add(m);
       }
       return models;
  }

 

 

 Insert

 指定插入的值，返回受影响的行数，或者返回自增长的ID。

DataAccessor da = new DataAccessor("connectionStringsInConfig");

AccountGroupTable table = new AccountGroupTable();

InsertStatement sql = da.Insert(table)
    .AddColumn(table.Name, "DemoName")
    .AddColumn(table.Parent, 2)
    .AddColumn(table.UpdateDate, DateTime.Now)
    .AddColumn(table.UpdateUser, "Demo")
    .AddColumn(table.Visible, true);

int rowAffected = sql.Execute();

int id = sql.ExecuteIdentity(table.Id);

 

把AccountGroupModel的值插入到数据库，并返回自增长的ID。AddColumns中第二个参数是param DbColumn[]类型，表示不用插入的DbColumn。

public int AddModel(AccountGroupModel model)
{
    model.UpdateDate = System.DateTime.Now;


    DataAccessor da = new DataAccessor("connectionStringsInConfig");


    AccountGroupTable table = new AccountGroupTable();

    return da.Insert(table)
        .AddColumns(model, table.Id)
        .ExecuteIdentity(table.Id);
}

 

 

 Update

 指定更新的值，返回受影响的行数。

DataAccessor da = new DataAccessor("connectionStringsInConfig");

AccountGroupTable table = new AccountGroupTable();

UpdateStatement sql = da.Update(table)
    .AddColumn(table.Name, "DemoName")
    .AddColumn(table.Parent, 2)
    .AddColumn(table.UpdateDate, DateTime.Now)
    .AddColumn(table.UpdateUser, "Demo")
    .AddColumn(table.Visible, true)
    .Where(table.Id == 1);

int rowAffected = sql.Execute();

 

把AccountGroupModel的值更新到数据库，AddColumns中第二个参数是param DbColumn[]类型，表示不用更新的DbColumn。

public int UpdateModel(AccountGroupModel model)
{
    model.UpdateDate = System.DateTime.Now;

    DataAccessor da = new DataAccessor("connectionStringsInConfig");

    AccountGroupTable table = new AccountGroupTable();

    return da.Update(table)
        .AddColumns(model, table.Id)
        .Where(table.Id == model.Id)
        .Execute();
}

 

 

 Delete

 根据指定的条件删除数据，返回受影响的行数。

DataAccessor da = new DataAccessor("connectionStringsInConfig");

AccountGroupTable table = new AccountGroupTable();

DeleteStatement sql = da.Delete(table)
    .Where(table.Id == 2);

int rowAffected = sql.Execute();

 

 

 示例代码DataAccessDemo.zip

 

 【LeanEAP.NET】精益企业应用平台----系列目录