csharp: DataTable结构相同的比较

1        /// <summary>
        /// DataTable分页
        /// </summary>
        /// <param name="dt">DataTable</param>
        /// <param name="PageIndex">页索引,注意：从1开始</param>
        /// <param name="PageSize">每页大小</param>
        /// <returns></returns>
        public static DataTable GetPagedTable(DataTable dt, int currentPageIndex, int pageSize)
        {

            //1
            //判断当前索引
            if (currentPageIndex == 0)
                return dt;
            //从数据集合拷贝数据
            DataTable newdt = dt.Copy();
            //数据清空
            newdt.Clear();
            //开始数据索引 = 当前页-1 x 每页大小
            int rowbegin = (currentPageIndex - 1) * pageSize;
            //结束数据索引 = 当前页 x 每页大小
            int rowend = currentPageIndex * pageSize;
            //开始数据索引 大于等于 当前数据集合大小
            if (rowbegin >= dt.Rows.Count)
                return newdt;
            //结束数据索引 大于 当前数据集合大小
            if (rowend > dt.Rows.Count)
                rowend = dt.Rows.Count;
            //遍历数据
            for (int i = rowbegin; i <= rowend - 1; i++)
            {
                DataRow newdr = newdt.NewRow();
                DataRow dr = dt.Rows[i];
                foreach (DataColumn column in dt.Columns)
                {
                    newdr[column.ColumnName] = dr[column.ColumnName];
                }
                newdt.Rows.Add(newdr);
            }

            return newdt;


            //2
            //if (currentPageIndex == 0)
            //{
            //return dt;
            //}

            //DataTable newdt = dt.Clone();// dt.Copy();

            //int rowbegin = (currentPageIndex - 1) * pageSize;//当前页的第一条数据在dt中的位置
            //int rowend = currentPageIndex * pageSize;//当前页的最后一条数据在dt中的位置

            //if (rowbegin >= dt.Rows.Count)
            //{
            //    return newdt;
            //}

            //if (rowend > dt.Rows.Count)
            //{
            //    rowend = dt.Rows.Count;
            //}

            //DataView dv = dt.DefaultView;
            //for (int i = rowbegin; i <= rowend - 1; i++)
            //{
            //    newdt.ImportRow(dv[i].Row);
            //}

            //return newdt;

        }

        /// <summary>
2         /// DataTable 结构相同的比较
        /// 
        /// C# datatable comparison  Same  Different
        /// 涂聚文
        /// </summary>
        /// <param name="first"></param>
        /// <param name="second"></param>
        /// <returns></returns>
        public DataTable CompareTables(DataTable first, DataTable second)
        {
            first.TableName = "FirstTable";

            second.TableName = "SecondTable";

            //Create Empty Table

            DataTable table = new DataTable("Difference");

            try
            {
                //Must use a Dataset to make use of a DataRelation object
                using (DataSet ds = new DataSet())
                {
                    ds.Tables.AddRange(new DataTable[] { first.Copy(), second.Copy() });

                    //Get Columns for DataRelation

                    DataColumn[] firstcolumns = new DataColumn[ds.Tables[0].Columns.Count];


                    for (int i = 0; i < firstcolumns.Length; i++)
                    {

                        firstcolumns[i] = ds.Tables[0].Columns[i];

                    }


                    DataColumn[] secondcolumns = new DataColumn[ds.Tables[1].Columns.Count];

                    for (int i = 0; i < secondcolumns.Length; i++)
                    {

                        secondcolumns[i] = ds.Tables[1].Columns[i];

                    }


                    //Create DataRelation

                    DataRelation r = new DataRelation(string.Empty, firstcolumns, secondcolumns, false);

                    ds.Relations.Add(r);

                    //Create columns for return table

                    for (int i = 0; i < first.Columns.Count; i++)
                    {

                        table.Columns.Add(first.Columns[i].ColumnName, first.Columns[i].DataType);

                    }


                    //If First Row not in Second, Add to return table.

                    table.BeginLoadData();



                    foreach (DataRow parentrow in ds.Tables[0].Rows)
                    {

                        DataRow[] childrows = parentrow.GetChildRows(r);

                        if (childrows == null || childrows.Length == 0)

                            table.LoadDataRow(parentrow.ItemArray, true);

                    }

                    table.EndLoadData();

                }
            }
            catch (Exception ex)
            {

                throw ex;

            }

            return table;
        }

 

USE pubs
GO

--使用带有简单 CASE 函数的 SELECT 语句
SELECT   Category = 
      CASE type
         WHEN 'popular_comp' THEN 'Popular Computing'
         WHEN 'mod_cook' THEN 'Modern Cooking'
         WHEN 'business' THEN 'Business'
         WHEN 'psychology' THEN 'Psychology'
         WHEN 'trad_cook' THEN 'Traditional Cooking'
         ELSE 'Not yet categorized'
      END,
   CAST(title AS varchar(25)) AS 'Shortened Title',
   price AS Price
FROM titles
WHERE price IS NOT NULL
ORDER BY type, price
COMPUTE AVG(price) BY type
GO

--使用带有简单 CASE 函数和 CASE 搜索函数的 SELECT 语句
 SELECT    'Price Category' = 
      CASE 
         WHEN price IS NULL THEN 'Not yet priced'
         WHEN price < 10 THEN 'Very Reasonable Title'
         WHEN price >= 10 and price < 20 THEN 'Coffee Table Title'
         ELSE 'Expensive book!'
      END,
   CAST(title AS varchar(20)) AS 'Shortened Title'
FROM titles
ORDER BY price
GO

--使用带有 SUBSTRING 和 SELECT 的 CASE 函数
SELECT SUBSTRING((RTRIM(a.au_fname) + ' '+ 
   RTRIM(a.au_lname) + ' '), 1, 25) AS Name, a.au_id, ta.title_id,
   Type = 
  CASE 
    WHEN SUBSTRING(ta.title_id, 1, 2) = 'BU' THEN 'Business'
    WHEN SUBSTRING(ta.title_id, 1, 2) = 'MC' THEN 'Modern Cooking'
    WHEN SUBSTRING(ta.title_id, 1, 2) = 'PC' THEN 'Popular Computing'
    WHEN SUBSTRING(ta.title_id, 1, 2) = 'PS' THEN 'Psychology'
    WHEN SUBSTRING(ta.title_id, 1, 2) = 'TC' THEN 'Traditional Cooking'
  END
FROM titleauthor ta JOIN authors a ON ta.au_id = a.au_id

--