subsonic操作实例

subsonic操作实例

2011/03/16 12:42

一、查询

1）简单查询 

一种方式（分解构造式） 

SubSonic.Queryquery = TPjWorkday.CreateQuery();

        query.SelectList = TPjWorkday.Columns.Year+ ", " + TPjWorkday.Columns.Yearmonth + "," + TPjWorkday.Columns.Workdays;

        query.AddWhere(TPjWorkday.Columns.Year,yearNum);

        query.OrderBy = SubSonic.OrderBy.Asc(TPjWorkday.Columns.Yearmonth);

        DataTabledt = query.ExecuteDataSet().Tables[0];

        returndt;

另外一种方式（LINQ式）

SqlQuery query= newSelect(TPjWorkday.Columns.Year, TPjWorkday.Columns.Yearmonth, TPjWorkday.Columns.Workdays).From(TPjWorkday.Schema

 ).Where(TPjWorkday.Columns.Year).IsEqualTo(yearNum).OrderAsc(TPjWorkday.Columns.Yearmonth);

  DataTabledt = query.ExecuteDataSet().Tables[0];

 

2）LIKE查询

queryBuilder.AddWhere(TBaseAddressbook.Columns.Name, Comparison.Like,"%"+this.tbName.Text+"%");

 

3）查询单值

SqlQuery query = newSelect(TBaseDepartment.Columns.Deptname).From(TBaseDepartment.Schema

                ).Where(TBaseDepartment.Columns.Deptid).IsEqualTo(deptid);

return query.ExecuteScalar().ToString();

 

4）多表连接查询（连接查询的表的数量超过3个最好使用视图）

根据单位ID获取用户

SqlQuery query=newSelect(TBaseUser.UseridColumn,TBaseUser.UsernameColumn, TBaseUser.PersonnameColumn, TBaseUser.PhonecodeColumn, TBaseUser.EmailColumn)

        .From(TBaseUser.Schema).InnerJoin(TLstUserindepartment.UseridColumn, TBaseUser.UseridColumn)

        .Where(TBaseUser.EnabledColumn).IsEqualTo(1)

        .And(TLstUserindepartment.DeptidColumn).IsEqualTo(deptid)

        .OrderAsc(TBaseUser.Columns.Userid);

         DataTabledt = query.ExecuteDataSet().Tables[0];

注意InnerJoin（f2,f1）的用法。也就是说当两个表连接时，From语句里面的表要放在后面。

 

5）分页查询

（方式一，Query）

//Query方式分页,序号列使用rowindex,    oracle数据库

Query query1 = newQuery(TBaseUser.Schema);

        query1.SelectList = TBaseUser.Columns.Userid+ "," + TBaseUser.Columns.Personname + ","+ TBaseUser.Columns.Phonecode+","+ TBaseUser.Columns.Email + ","+ TBaseUser.Columns.Userindex;

        query1.AddWhere(TBaseUser.Columns.Issignedin,Comparison.Equals, 1);//登录状态

        query1.AND(TBaseUser.Columns.Enabled,Comparison.Equals, 1);//启用

        query1.OrderBy = OrderBy.Asc(TBaseUser.Columns.Userindex);

        int sum= query1.GetRecordCount();//用于计算总页数

query1.PageSize = 12;//分页大小

        query1.PageIndex = pagenum -1;//当前页

        DataTableViewData = query1.ExecuteDataSet().Tables[0];

        if(sum%12==0)

            this.lbPageCount.Text= (sum / 12).ToString();

        else

            this.lbPageCount.Text= (sum / 12+1).ToString();

        this.lbPageNum.Text= pagenum.ToString();

 

（方式二、SqlQuery）

//SqlQuery方式分页，序号列使用row_number   oracle数据库

SqlQuery query = newSelect().From(VWfSupervisor.Schema).Where(VWfSupervisor.Columns.Userid).IsEqualTo(CreateUserInfo().userid).OrderAsc(VWfSupervisor.Columns.Userid);

int sum = query.GetRecordCount();//未分页前，总记录数

query.Paged(pagenum - 1, 12);

DataTable ViewData = query.ExecuteDataSet().Tables[0];

if(sum%12==0)

      this.lbPageCount.Text= (sum / 12).ToString();

 else

       this.lbPageCount.Text= (sum / 12+1).ToString();

 this.lbPageNum.Text= pagenum.ToString();

 

 

二、插入，更新和删除

插入和更新操作都是通过实体的Save()方法来实现的，判断更新与插入操作主要体现在实体的IsNew属性，如果IsNew==true，则执行的是插入操作，反之执行的是更新操作。

当实例化一个实体时，如TPjWorkday newworkday = newTPjWorkday()，此时的newworkday.IsNew=true;

当实例化传入参数时，

TPjAttendancetime attendanceTime = newTPjAttendancetime(Convert.ToInt32(dt.Rows[0]["flowid"]));此时的newworkday.IsNew=false。

 

1.插入

TPjWorkday newworkday = newTPjWorkday();

newworkday.Year = Convert.ToInt32(lbYear.Text);

newworkday.Yearmonth= Convert.ToInt32(this.lbYear.Text)* 100 + (i + 1);

newworkday.Workdays = workStr;

newworkday.Save();

 

newInsert(TLstUserinrole.Schema,false).Value(TLstUserinrole.UseridColumn,2).Value(TLstUserinrole.RoleidColumn, 2)

            .Value(TLstUserinrole.CreatebyColumn,3).Value(TLstUserinrole.CreatetimeColumn, DateTime.Now).Execute();

其中false表示不是全部字段。默认为true

 

2.更新

单字段逐渐更新方式，传入主键字段值

TPjAttendancetime attendanceTime = newTPjAttendancetime(Convert.ToInt32(dt.Rows[0]["flowid"]));

attendanceTime.Begintime = Convert.ToDateTime(tbBeginTime.Value);

attendanceTime.Endtime = Convert.ToDateTime(tbEndTime.Value);

attendanceTime.AM1 = Convert.ToDateTime(dlAm1.SelectedValue).ToShortTimeString();

attendanceTime.AM2 = Convert.ToDateTime(dlAm2.SelectedValue).ToShortTimeString();

attendanceTime.PM1 = Convert.ToDateTime(dlPm1.SelectedValue).ToShortTimeString();

attendanceTime.PM2 = Convert.ToDateTime(dlPm2.SelectedValue).ToShortTimeString();

attendanceTime.Modifyby = Convert.ToInt32(Request.QueryString["userid"]);

attendanceTime.Modifytime = System.DateTime.Now;

attendanceTime.Save();

 

newUpdate(TPjInnernews.Schema).Set(TPjInnernews.NewstitleColumn).EqualTo("123").Where(TPjInnernews.NewsidColumn).IsEqualTo(3);

 

复合主键更新方式，（userid，ascxid）

newUpdate(TLstUserhomepage.Schema).Set(TLstUserhomepage.Columns.Modifytime).EqualTo(System.DateTime.Now).Set(TLstUserhomepage.Columns.Modifyby).EqualTo(3).Where(TLstUserhomepage.UseridColumn)

            .IsEqualTo(409).And(TLstUserhomepage.AscxidColumn).IsEqualTo(11).Execute();

 

//修改记录, 先取得对象，然后再更新

            TBaseStatistictype=newSelect().From(TBaseStatistic.Schema).Where(TBaseStatistic.Columns.Typeid).IsEqualTo(int.Parse(GetEQString("Typeid")))

                .And(TBaseStatistic.Columns.Statid).IsEqualTo(int.Parse(this.Label3.Text)).ExecuteScalar();

            type.Statname = this.TextBox1.Text.Trim();

            type.Stattabname = this.Label1.Text;

            type.Statcolname = this.Label12.Text;

            type.Statcolid = this.Label7.Text;

            type.Datecolname= this.Label14.Text;

            type.Wherepart= this.Label8.Text;

type.Save();

 

UPDATE table set column=column+1

int records = newUpdate(Product.Schema)

                .SetExpression("UnitPrice").EqualTo("UnitPrice * 3")

                .Where("productid").IsEqualTo(1)

                .Execute();

 

3.删除

Query q = TPjWorkday.CreateQuery();

q.WHERE(TPjWorkday.Columns.Year, Convert.ToInt32(lbYear.Text));

q.QueryType = QueryType.Delete;

q.Execute();

 

TPjWorkday.Delete(TPjWorkday.Columns.Yearmonth, "200901");

 

 

 

三、事务处理

using (SharedDbConnectionScopesp = newSharedDbConnectionScope())

{

  using (TransactionScopescope = newTransactionScope())

  {

         //数据库操作

         // 不能使用平台的自增操作，否则会报错：

{"无法加载 DLL“oramts.dll”: 找不到指定的模块。 (异常来自HRESULT:0x8007007E)。"}

//CACA.BusinessLogic.Util.CounterUtil.increment("EntityInnernews")

         scope.Complete();

}

}

 

List<SqlQuery>list = newList<SqlQuery>();

list.Add(newDelete().From(TBaseAddressbook.Schema).Where(TBaseAddressbook.Columns.Serialno).IsEqualTo(lbl.Text));

list.Add(newDelete().From(TBaseAddressbookusual.Schema).Where(TBaseAddressbookusual.Columns.Serialno).IsEqualTo(lbl.Text));

SqlQuery.ExecuteTransaction(list);

 

List<Insert>list=new List<Insert>();

 

存储过程：

//StoredProcedure spd = newStoredProcedure("GetDataTable");

        //spd.Command.AddParameter("@userid",userid);

        //spd.Command.AddOutputParameter("@dataresult");

       //spd.Execute();

 

 

 

四、SubSonic直接执行SQL语句(后门，当遇到无法解决的问题时，直接写SQL语句)

SubSonic直接执行SQL语句可以使用以下方式：

 

//执行sql语句，返回datatable

QueryCommand qc = newQueryCommand(strSql.ToString(), null);

 

                dt =DataService.GetDataSet(qc).Tables[0];

 

 

public void Inline_Simple()

{

     QueryCommandcmd = new InlineQuery().GetCommand("SELECT productID from products");

     Assert.IsTrue(cmd.CommandSql== 

               "SELECT productID fromproducts");

}

 

public void Inline_WithCommands()

{

     QueryCommandcmd = new InlineQuery()

              .GetCommand(@"SELECTproductID from products 

                  WHEREproductid=@productid", 1);

 

     Assert.IsTrue(cmd.Parameters[0].ParameterName== "@productid");

     Assert.IsTrue((int)cmd.Parameters[0].ParameterValue== 1);

}

 

public void Inline_AsCollection()

{

     ProductCollectionproducts =

         newInlineQuery()

              .ExecuteAsCollection<ProductCollection>(

                         @"SELECTproductID from products 

                          WHEREproductid=@productid", 1);

}

 

 

五、常用方法的返回值

Execute(),返回受影响的记录条数

//ExecuteScalar()，返回一个object，（System.Decimal类型的值）

ExecuteSingle<T>()，返回T类型的对象，例如可以用这个方法获得满足要求的实体对象。

TDicItem item= newSelect().From(TDicItem.Schema).Where(TDicItem.Columns.Dicitemid).IsEqualTo(2)

            .And(TDicItem.Columns.Dictypeid).IsEqualTo(1030).ExecuteSingle<TDicItem>();

 

ExecuteDataSet(),返回一个记录集DataSet

ExecuteTypedList<T>();返回List<T>,对象的列表，例如

List<Product>products = new Select().From<Product>()

.Where(Product.ProductIDColumn).IsEqualTo(4)

.Paged(1, 30)

.ExecuteTypedList<Product>();

转自他人文章！