Subsonic简单的语法整理

.查询方面

(查询所有数据记录[dataset读取方法])

Myuser.Query().ExecuteDataSet().Tables[];

Myuser.Query().ExecuteDataSet();

(返回关联查询[dataset读取方法])

Myuser.Query().ExecuteDataSet().Tables[];

Myuser.Query().ExecuteDataSet()

(返回所有的查询记录[DataReader读取方法])

  List<Myuser> myu =new List<Myuser>();

       IDataReader ida = Myuser.Query().ExecuteReader();

       while (ida.Read())

       {

           Myuser myuser = new Myuser();

           myuser.Userid = int.Parse(ida.GetValue().ToString());

           myuser.Username = ida.GetValue().ToString();

           myuser.Userpassword = ida.GetValue().ToString();

           myuser.Usersex = ida.GetValue().ToString();

           myu.Add(myuser);

       }

       GridView1.DataSource = myu;

       GridView1.DataBind();

(返回所有的查询记录[dataReader读取方式])

IDataReader ida = Myuser.FetchAll();

        while (ida.Read())

       {

           Myuser myuser = new Myuser();

           myuser.Userid = int.Parse(ida.GetValue().ToString());

           myuser.Username = ida.GetValue().ToString();

           myuser.Userpassword = ida.GetValue().ToString();

           myuser.Usersex = ida.GetValue().ToString();

           myu.Add(myuser);

       }

       GridView1.DataSource = myu;

       GridView1.DataBind();

(带有返回排序完毕的查询记录[dataReader读取方式])

IDataReader ida = Myuser.FetchAll(OrderBy.Desc("usersex"));

IDataReader ida = Myuser.FetchAll(OrderBy.Asc("usersex"));

(根据ID返回其查询的记录)

Myuser.FetchByID().Username.ToString();

(根据查询条件返回查询数据记录)

IDataReader ida=Myuser.FetchByParameter("username", "张山");

(查询所有数据记录[dataReader方式])

Query q = new Query("Myusers");

       IDataReader ida=Myuser.FetchByQuery(q);

       while (ida.Read())

       {

           Myuser myuser = new Myuser();

           myuser.Userid = int.Parse(ida.GetValue().ToString());

           myuser.Username = ida.GetValue().ToString();

           myuser.Userpassword = ida.GetValue().ToString();

           myuser.Usersex = ida.GetValue().ToString();

           myu.Add(myuser);

       }

       GridView1.DataSource = myu;

       GridView1.DataBind();

(查询所有的数据记录[dataReader方式])

Myuser u=new Myuser();

       IDataReader ida = Myuser.Find(u);

       while (ida.Read())

       {

           Myuser myuser = new Myuser();

           myuser.Userid = int.Parse(ida.GetValue().ToString());

           myuser.Username = ida.GetValue().ToString();

           myuser.Userpassword = ida.GetValue().ToString();

           myuser.Usersex = ida.GetValue().ToString();

           myu.Add(myuser);

       }

       GridView1.DataSource = myu;

       GridView1.DataBind();

(可排序查询所有的数据记录[dataReader方式])

Myuser u=new Myuser();

       IDataReader ida = Myuser.Find(u,OrderBy.Asc("username"));

(查询获取listitem列表[可以于填充下拉框])

   ListItemCollection lic=Myuser.GetListItems();--默认获取除掉id的第一列的值

   ListItemCollection lic=Myuser.GetListItems();--可指定获取列

          foreach(ListItem li in lic)

          {

          Response.Write(li.Value);

          }

(获取表的结构)

Myuser.Schema

--进行表的列的添加、删除、判断是否有主键、外键、等

(返回查询的对象)

Myuser.Query().ExecuteScalar();

(可利用sql语句查询)

 IDataReader ida = newQuery(Myuser.Schema).WHERE("userid=5").ExecuteReader();

(可以用字符串直接写表名或者视图名)

IDataReader rdr = newQuery("Products").WHERE("ProductID",).ExecuteReader();

IDataReader rdr = newQuery("Products").BETWEEN_AND("DateExpires",DateTime.Now,DateTime.Now.AddDays().ExecuteReader();

(也可以用SubSonic定义好的结构来表示表名或视图名)

IDataReader rdr = newQuery(Tables.Products).AddWhere(Product.Columns.ProductID,).ExecuteReader();

(查询出特定条数的结果可以用Top关键字，也可以查询指定的字段)

Query qry = new Query(Tables.Products);--指定表名

qry.Top = "";--前n条查询

qry.SelectList =Product.Columns.ProductName+","+Product.Columns.UnitPrice;--获取指定的列名字段

qry.OrderBy =OrderBy.Desc(Product.Columns.UnitPrice);--进行指定列名排序

(支持分页查询)

Query qry = new Query(Tables.Products);--指定表名

qry.PageSize = ;--当前页面显示的数据记录数

qry.PageIndex = ;--指定的当前页面

(对于多个表的连接查询，建议使用视图实现,查询条件的复合，如用OR或者IN，可以这样查)

IDataReader rdr = newQuery("Products").WHERE("CategoryID=5").AND("UnitPrice>10").OR("CategoryID=1").And("UnitPrice>10").ExecuteReader();

(对于IN条件查询，提供了三种参数类型：ListItemCollection,ArrayList,objectarray)

//////////////////////////////////////arraylist方法

ArrayList list = new ArrayList();

for(int i =;i<=;i++)

   list.Add(i);

IDataReader rdr = newQuery("products").IN("ProductID",list).ExecuteReader();

/////////////////////////////////////object[]

IDataReader rdr = new Query("products").IN("ProductID",newobject[]{,,,,}).ExecuteReader();

/////////////////////////////////////listitemcollection

ListItemCollection coll = new ListItemCollection();

for (int i =;i<=;i++)

   ListItem item = newListItem(i.ToString(),i.ToString());

   item.Selected = true;

   coll.Add(item);

IDataReader rdr = newQuery("products").IN("ProductID",coll).ExecuteReader();

(执行纯SQL语句的查询方法)

  QueryCommand qcmd = new QueryCommand("select *from Myusers");

       DataSet ds=DataService.GetDataSet(qcmd);

       GridView1.DataSource = ds;

       GridView1.DataBind();

/////////////////////////////////////////////

       QueryCommand qcmd = newQueryCommand(Myuser.Query().GetSql());

       DataSet ds=DataService.GetDataSet(qcmd);

       GridView1.DataSource = ds;

       GridView1.DataBind();

(获取当前的query的执行语句)

Myuser.Query().GetSql()

(sql语句拼接查询)

string sql = "";

Query q = new Query("vwProduct");

q.AddWhere("productID",productID);

sql = q.GetSql()+"\r\n";

q = new Query(Commerce.Common.Image.GetTableSchema());

q.AddWhere("productID",productID);

q.OrderBy = OrderBy.Asc("listOrder");

sql += q.GetSql()+"\r\n";

....

QueryCommand cmd = new QueryCommand(sql);

cmd.AddParameter("@productID",productID,DbType.Int32);

DataSet ds = DataService.GetDataSet(cmd);

(查询统计结果)

SubSonic.Where w = new SubSonic.Where();

w.ColumnName = "CategoryID";

w.ParameterValue = "";

Query.GetCount("products","productID",w);

上面的where条件是可选的，也可以直接根据表名和列名统计，象上面的最后一句那样。除了GetCount()方法以外，还有GetAverage()、GetSum()等方法，用法类似。

(函数查询)

SubSonic.Where where = new Where();

       where.ColumnName = "userid";

       where.ParameterValue = "";

      Response.Write(Myuser.Query().GetCount("userid",where));

Myuser.Query().GetCount("userid",where)--查询当前数的数据记录数相当于count(列名)+where条件

Myuser.Query().GetAverage("userid",where)--查询当前数据的平均值相当于avg(列名)+where条件

Myuser.Query().GetSum("userid",where)--查询当前数据总和相当于sum(列名)+where条件

Myuser.Query().GetMax("userid",where)--查询当前数据的最大值相当于max(列名)+where条件

Myuser.Query().GetMin("userid",where)--查询当前数据的最小值相当于min(列名)+where条件

-------where可加可不加

(多种查询条件约定查询)

GridView1.DataSource=Myuser.Query().WHERE("userid",Comparison.LessOrEquals, ).ExecuteDataSet();

GridView1.DataSource=Myuser.Query().WHERE("列名", 约束条件,约束数值).ExecuteDataSet();

Comparison.LessOrEquals--小于或等于

Comparison.LessThan--小于

Comparison.BetweenAnd--等于

Comparison.Blank--填充为空白的

Comparison.OpenParentheses--打开括号【我认为是添加"("】

Comparison.CloseParentheses--关闭括号？？？【我认为是添加")"】

Comparison.Equals--字符串之间的对比相当于等于

Comparison.NotEquals--查询不相等的数据

Comparison.GreaterOrEquals--大于等于

Comparison.GreaterThan--大于

Comparison.In--在范围内取值

Comparison.Is--是否等于

Comparison.IsNot--在范围外取值

Comparison.Like--查询相似的数据

Comparison.NotLike--查询不相似的数据

(创建一个commd查询的连接对象)

Myuser.Query().BuildSelectCommand()相当于QueryCommand qcmd = newQueryCommand(Myuser.Query().GetSql());

(通过Query查询所有数据)

QueryCommand qcmd = Myuser.Query().BuildSelectCommand();

       DataSet ds = DataService.GetDataSet(qcmd);

       GridView1.DataSource = ds;

       GridView1.DataBind();

(去重复查询)

DISTINCT()

(查询指定值在表中的出现的次数)

 Response.Write(Myuser.Destroy("usersex","男"));

(between and使用查询方法)

   GridView1.DataSource=Myuser.Query().BETWEEN_VALUES("userid", ,).ExecuteDataSet();

   GridView1.DataBind();

---------------------------------------------------------------------------------------------------------------------------------------------

.添加方面

方法一：

        Myuser.Insert(txtusername.Text, txtuserpassword.Text,txtusersex.Text);

方法二：

  Myuser myuser = new Myuser();

           myuser.Username = txtusername.Text;

           myuser.Userpassword = txtuserpassword.Text;

           myuser.Usersex = txtusersex.Text;

           myuser.Save();

方法三：

int i = new Insert().Into(Myuser.Schema, "username","userpassword", "usersex").Values(TextBox1.Text, TextBox2.Text,TextBox3.Text).Execute();

//方法四

       Myuser my = new Myuser();

       my.SetColumnValue("username", TextBox1.Text);

       my.SetColumnValue("userpassword", TextBox2.Text);

       my.SetColumnValue("usersex", TextBox3.Text);

       my.Save();

       Response.Write("<scriptlanguage=javascript>alert('数据添加成功!');location='datashow.aspx'</script>");

//方法五

       Insert MyuserInsert = new Insert(Myuser.Schema.TableName);

       intresult=MyuserInsert.Into(Myuser.Schema,Myuser.UsernameColumn.ColumnName,Myuser.UserpasswordColumn.ColumnName,Myuser.UsersexColumn.ColumnName).Values(TextBox1.Text,TextBox2.Text, TextBox3.Text).Execute();

       if (result > )

       {

         ClientScript.RegisterStartupScript(this.GetType(), "alert","<script>alert('数据添加成功！');location='datashow.aspx'</script>)");

       }

       else

       {

         ClientScript.RegisterStartupScript(this.GetType(), "alert","<script>alert('数据添加失败！')</script>)");

       }

//方法六

 QueryCommand qcmd = new QueryCommand(sql);

                          int res=DataService.ExecuteQuery(qcmd);

.修改方面

//方法一

       //Myuser my = new Myuser(userid);

       //my.Username = username;

       //my.Userpassword = userpassword;

       //my.Usersex = usersex;

       //my.Save();

//方法二

       //Query q = Myuser.Query();

       //q.AddWhere("userid",userid);

       //q.AddUpdateSetting("username", username);

       //q.AddUpdateSetting("userpassword", userpassword);

       //q.AddUpdateSetting("usersex", usersex);

       //q.QueryType=QueryType.Update;

       //q.Execute();

       //Response.Write("<script>alert('"+q.GetRecordCount().ToString()+"')<script/>");

//方法三

       //int res = newUpdate(Myuser.Schema).Set(Myuser.UsernameColumn).EqualTo(username).Set(Myuser.UserpasswordColumn).EqualTo(userpassword).Set(Myuser.UsersexColumn).EqualTo(usersex).Where(Myuser.UseridColumn).IsEqualTo(userid).Execute();

       //if (res > 0)

       //{

       // //  Response.Write("<script>alert('数据修改成功')</script>");

       //   ClientScript.RegisterStartupScript(this.GetType(),"alert","<script>alert('数据修改成功')</script>");

       //}

       //else

       //{

       //   //Response.Write("<script>alert('数据修改失败')</script>");

       //   ClientScript.RegisterStartupScript(this.GetType(), "alert","<script>alert('数据修改失败')</script>");

       //}

//方法四

       //Myuser my = new Myuser(userid);

       //my.SetColumnValue("username", username);

       //my.SetColumnValue("userpassword", userpassword);

       //my.SetColumnValue("usersex", usersex);

       //my.Save();

 //方法五

       Update MyuserUpdate = new Update(Myuser.Schema.TableName);

       MyuserUpdate.From(Myuser.Schema.TableName);

       MyuserUpdate.Where("userid").IsEqualTo(userid);

       MyuserUpdate.Set("username").EqualTo(username);

       MyuserUpdate.Set("userpassword").EqualTo(userpassword);

       MyuserUpdate.Set("usersex").EqualTo(usersex);

       int result = MyuserUpdate.Execute();

       if (result > )

       {

           ClientScript.RegisterStartupScript(this.GetType(), "alert","<script>alert('数据修改成功')</script>");

       }

       else

       {

           ClientScript.RegisterStartupScript(this.GetType(), "alert","<script>alert('数据修改失败')</script>");

       }

//方法六

 QueryCommand qcmd = new QueryCommand(sql);

                          int res=DataService.ExecuteQuery(qcmd);

.删除方面

       //删除的id

       String id = GridView1.DataKeys[e.RowIndex].Value.ToString();

       //int res = db.deldb(int.Parse(id));

//方法一

       //Myuser.Delete(id);

//方法二

       Query q = Myuser.Query();

       //q.AddWhere("userid", id);

       //q.QueryType = QueryType.Delete;

       //q.Execute();

//方法三

       int res = newDelete(Myuser.Schema).Where("userid").IsEqualTo(id).Execute();

       if (res > )

       {

           ClientScript.RegisterStartupScript(this.GetType(), "alert","<script>alert('数据已经成功删除')</script>");

       }

       else

       {

           ClientScript.RegisterStartupScript(this.GetType(), "alert","<script>alert('数据删除失败')</script>");

       }

//方法四

       int result = newDelete().From<Myuser>().Where(Myuser.UseridColumn).IsEqualTo(id).Execute();

       if (result > )

       {

           ClientScript.RegisterStartupScript(this.GetType(), "alert","<script>alert('数据已经成功删除')</script>");

       }

       else

       {

           ClientScript.RegisterStartupScript(this.GetType(), "alert","<script>alert('数据删除失败')</script>");

       }

//方法五

       Delete MyuserDelete = new Delete();

       MyuserDelete.From(Myuser.Schema.TableName);

       MyuserDelete.Where("userid").IsEqualTo(id);

      int result=MyuserDelete.Execute();

      if (result > )

      {

          ClientScript.RegisterStartupScript(this.GetType(), "alert","<script>alert('数据已经成功删除')</script>");

      }

      else

      {

          ClientScript.RegisterStartupScript(this.GetType(), "alert","<script>alert('数据删除失败')</script>");

      }

//方法六

 QueryCommand qcmd = new QueryCommand(sql);

                          int res=DataService.ExecuteQuery(qcmd);

---------------------------------------------------------------------------------------------------------------------------------------------

//执行存储过程

 StoredProcedure sps = newStoredProcedure("Pro_CheckoutCatid");//存储名

      sps.Command.AddParameter("@UserCa