[原创]对DataSet和DataTable内的数据进行分析更新等操作

梳子の技术专栏Tag标签: 文件存取

紧接上一篇写Excel写入数据库的内容,需要在读取了Excel内容的ds或dt里对无用数据进行剔除和进行检查,比如把ds的内容distinct的最简便方法,在已经生成的ds中添加新列,更新ds中某值等等

下片段参考自http://www.cnblogs.com/simhare/archive/2007/07/21/826465.html

/*在DataTable中更新数据
 * 确定了要在DataTable中更新的记录后，
 * 只要把记录的一个或多个字段替换成要更新的值即可，
 * 下面的例子选择Pubs数据库的Titles表中所有ytd_sales大于10000的记录
 * 把它们的价格加10.00
 */
SqlDataAdapter adapter = new SqlDataAdapter(strSql, strConn);
DataSet ds = new DataSet();
adapter.Fill(ds, "Titles");

DataTable table = ds.Tables["Titles"];
DataRow[] arrRows = table.Select("ytd_sales>10000");
foreach (DataRow row in arrRows)
{
    row["price"] = (decimal)row["price"] + 10.00m;
}

同理,删除操作

/*从DataTable中删除记录
 * 对每个要删除的行调用Delete就行了            
 * 下面删除ytd_sales小于10000或等于空的记录
 */
SqlDataAdapter adapter = new SqlDataAdapter(strSql, strConn);
DataSet ds = new DataSet();
adapter.Fill(ds, "Titles");

DataTable table = ds.Tables["Titles"];
DataRow[] arrRows = table.Select("ytd_sales<10000 or isnull(ytd_sales,0)=0");
foreach (DataRow row in arrRows)
{
    row.Delete();
}

这是我的使用代码,包括在已经从Excel生成的dt中添加新的列,在某行内容中更新某列的值,是上边的实际应用,运行通过,仅供参考.
          #region 检查分类,并分配ID
          DataSet dsUpload = Excel_To_Dataset();
          if (dsUpload.Tables.Count > 0)
          {
              System.Data.DataTable TitleDT = BuaaLibFAQClassLibrary.Manager.CategoryManager.SelectID_Title();
              DataView dv = dsUpload.Tables["[Sheet1$]"].DefaultView;
              System.Data.DataTable checkTitleDT = dv.ToTable(true, "category");
              DataColumn myDataColumn = new DataColumn();//添加新列
              myDataColumn.ColumnName = "checkedID";
              myDataColumn.DataType = System.Type.GetType("System.Int32");
              checkTitleDT.Columns.Add(myDataColumn);

              string noTitle = "";//结果说明
              for (int i = 0; i < checkTitleDT.Rows.Count; i++)
              {
                  DataRow[] rows = TitleDT.Select("c_title = '" + checkTitleDT.Rows[i][0].ToString() + "'");

                  if (!(rows.Length <= 0))
                  {
                      DataRow row = checkTitleDT.Rows[i];
                      row["checkedID"] = Int32.Parse(rows[0]["id"].ToString());
                  }
                  else
                  {
                      noTitle += "'" + checkTitleDT.Rows[i][0].ToString() + "',";
                      DataRow row = checkTitleDT.Rows[i];
                      row["checkedID"] = -1;
                  }
              }
              if (noTitle.Length > 0)
              {
                  noTitle += "---上述分类尚未添加,请手动添加分类信息后再次导入数据!";
                  Message_lab.Text = noTitle;
              }
          #endregion