DataSet相关操作:
string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
using(SqlConnection conn=new SqlConnection(constr))
{
string sql = @"select * from T_Users";
using(SqlDataAdapter adapter=new SqlDataAdapter(sql,conn))
{
DataSet ds = new DataSet();
adapter.Fill(ds,"T_Users");
// GridView1.DataSource = ds.Tables[0];// 通过索引查找本地缓存数据中的表
// GridView1.DataSource=ds.Tables["T_Users"];// 通过表名查找本地缓存数据中的表
DataTableCollection dtc = ds.Tables;//本地缓存数据中表的集合
//GridView1.DataSource = dtc[0];
//GridView1.DataSource = dtc["T_Users"];
DataTable dt = dtc["T_Users"];
GridView1.DataSource = dt;
GridView1.DataBind();
// DataRow[] dr = dt.Select();//获取表中所有行
//DataRow[] dr = dt.Select("Id>8");//获取表中符合条件的行
// DataRow[] dr = dt.Select("Id>8","Id DESC");//获取表中符合条件的行,并按降序排列
DataRow[] dr = dt.Select("Id>8", "Id DESC",DataViewRowState.OriginalRows);//获取符合筛选条件和指定状态的行,并按指定的排序进行排序
foreach (var row in dr)
{
Response.Write(row["Name"]+"|"+row["Password"]+"|"+row["Age"]+"<br />");
}
}
}
DataTable相关操作:
string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
using (SqlConnection conn = new SqlConnection(constr))
{
string sql=@"select * from T_Users";
using(SqlDataAdapter adpater = new SqlDataAdapter(sql,conn))
{
DataSet ds = new DataSet();
adpater.Fill(ds, "T_Users");
DataTable dt = ds.Tables["T_Users"];
int count=(int)dt.Compute("count(Id)","Age>20");//统计出年龄大于20的所有表数据的个数4
int avg = (int)dt.Compute("avg(Age)", "true");//统计出平均年龄20
long sum = (long)dt.Compute("Sum(Age)","true");//统计出年龄的总和222
Response.Write(count+"<br />");
Response.Write(avg + "<br />");
Response.Write(sum + "<br />");
DataColumn dc = new DataColumn("Total",Type.GetType("System.Decimal"));
dc.Expression = "Num*Price";
dt.Columns.Add(dc);
object sum2 =dt.Compute("sum(Total)", "true");//新增一列数据并计算其总和6810
Response.Write(sum2 + "<br />");
GridView2.DataSource = dt;
GridView2.DataBind();
}
}