DataTable行列转换

 

DataTable行列转换的函数，找遍了网上都没找到，还是自己写了，这个函数在ContrastTable函数将被调用，ContrastTable用于实现在视图中对比数据，类似于pcoline的产品比较功能。是DataTable版的，DataSet版的就不贴了。

 

public  DataTable cgDataTable(DataTable oldTable)
  {
   try
   {
    int h=oldTable.Rows.Count+1;
    int l=oldTable.Columns.Count;   
    DataTable newTable = new DataTable();

    for(int i=0; i<h; i++)
    {
     if (i==0)
     {
      newTable.Columns.Add(oldTable.Columns[0].ToString());
     }
     else
     {
      newTable.Columns.Add(oldTable.Rows[i-1][0].ToString ());
     }
    }        
    
    for(int i=0;i<l;i++)
    {    
     DataRow r;
     r=newTable.NewRow();        

     //oldTable.Columns.Count;
     for(int j=0;j<h;j++)   
     {      
      if (j==0)
      {
       r[j] = oldTable.Columns[i].ToString();        
      }
      else
      {
       r[j] = oldTable.Rows[j-1][i].ToString();        
      }

     }
     if (i==0)
     {
     //
     }
     else
     {
      newTable.Rows.Add(r);
     }
    }
    
     
    return newTable;
   }  
   catch(Exception ex)
   {
    throw new Exception(ex.ToString());
   }
  
  }

 

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

public DataTable ContrastTable(string conn,string TableName,string KeyColumn,IList Columns,IList RowID,string OrderBy,bool direction)
  {
  
   SqlConnection con = new SqlConnection(conn);     

   string strColumns = "";  
   for   (int   i=0;i<Columns.Count;i++)
   {  
    if (i==Columns.Count-1)
    {
     strColumns+=Columns[i];  
    }
    else
    {
     strColumns+=Columns[i]+",";  
    }
   }  

   string strRowID = "";  
   /*for (int j=0;j<RowID.Count;j++)
   {  
    if (j==Columns.Count-1)
    {
     strRowID+=RowID[j];  
    }
    else
    {
     strRowID+=RowID[j]+",";  
    }
   } */
   foreach (object o in RowID)
   {
    strRowID+= ""+KeyColumn+" = '"+o.ToString()+"' or ";  
   }
   
   strRowID=strRowID.Substring(0,strRowID.Length-3);

   string sql="";
   sql="select "+strColumns+" from "+TableName+" where "+strRowID+" order by "+OrderBy+"";

   //Trace.Write("sql",sql);
      

   try
   {
    SqlDataAdapter da = new SqlDataAdapter(sql, con);
    DataSet ds = new DataSet();
    con.Open();
    da.Fill(ds);

    if (direction==true)
    {
     return ds.Tables[0];
    }
    else
    {
     return cgDataTable(ds.Tables[0]);
     
    }

       
   }
   catch(SqlException se)
   {
    throw new Exception(se.ToString());
   }
   finally
   {
    con.Close();
    con.Dispose();
   }

  }

 

-----------

调用方法（这里的代码效率有些问题）：

   ArrayList a=new ArrayList();
   foreach(ListItem li in CheckBoxList1.Items)
   {
    if(li.Selected)
    {
     a.Add(li.Value);
    }
   }
   ArrayList b=new ArrayList();
   foreach(ListItem li in CheckBoxList2.Items)
   {
    if(li.Selected)
    {
     b.Add(li.Value);
    }
   }
   

   DataGrid1.DataSource = ContrastTable(ConfigurationSettings.AppSettings["LocationOfCode"],TextBox1.Text,"PersonID",a,b,"PersonID",true).DefaultView;
   DataGrid1.DataBind();