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--