csharp: DataTable结构相同的比较

8/3/2015来源:C#应用人气:1803

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 pubsGO--使用带有简单 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 PriceFROM titlesWHERE price IS NOT NULLORDER BY type, priceCOMPUTE AVG(price) BY typeGO--使用带有简单 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 titlesORDER BY priceGO--使用带有 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'  ENDFROM titleauthor ta JOIN authors a ON ta.au_id = a.au_id--