.查询方面
(查询所有数据记录[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