www.pudn.com > Tustena_CRM_OS_3.0.2_Stable.zip > DataManipulation.cs

/// TUSTENA PUBLIC LICENSE v1.0 
///  
/// Portions Copyright (c) 2003-2005 Digita S.r.l. All Rights Reserved. 
/// 
/// Tustena CRM is a trademark of: 
/// Digita S.r.l. 
/// Viale Enrico Fermi 14/z 
/// 31011 Asolo (Italy) 
/// Tel. +39-0423-951251 
/// Mail. info@digita.it 
/// 
/// This file contains Original Code and/or Modifications of Original Code 
/// as defined in and that are subject to the Tustena Public Source License 
/// Version 1.0 (the 'License'). You may not use this file except in 
/// compliance with the License. Please obtain a copy of the License at 
/// http://www.tustena.com/TPL/ and read it before using this 
// file. 
/// 
/// The Original Code and all software distributed under the License are 
/// distributed on an 'AS IS' basis, WITHOUT WARRANTY OF ANY KIND, EITHER 
/// EXPRESS OR IMPLIED, AND DIGITA S.R.L. HEREBY DISCLAIMS ALL SUCH WARRANTIES, 
/// INCLUDING WITHOUT LIMITATION, ANY WARRANTIES OF MERCHANTABILITY, 
/// FITNESS FOR A PARTICULAR PURPOSE, QUIET ENJOYMENT OR NON-INFRINGEMENT. 
/// Please see the License for the specific language governing rights and 
/// limitations under the License. 
/// 
/// YOU MAY NOT REMOVE OR ALTER THIS COPYRIGHT NOTICE! 
///  
 
using System; 
using System.Collections; 
using System.Data; 
using System.Data.SqlClient; 
using System.Web; 
 
namespace Digita.Tustena.Database 
{ 
	public  sealed class DataManipulation 
	{ 
 
		private DataManipulation(){} 
 
		public static void AddToDataTable(ref DataTable t, string sql, SqlConnection cn) 
		{ 
			SqlDataAdapter da = new SqlDataAdapter(sql, cn); 
			da.Fill(t); 
		} 
 
		public static DataTable Union(DataTable first, DataTable second) 
		{ 
			DataTable table = new DataTable("Union"); 
 
			DataColumn[] newcolumns = new DataColumn[first.Columns.Count]; 
			for (int i = 0; i < first.Columns.Count; i++) 
			{ 
				newcolumns[i] = new DataColumn(first.Columns[i].ColumnName, first.Columns[i].DataType); 
			} 
 
 
			table.Columns.AddRange(newcolumns); 
			table.BeginLoadData(); 
 
			foreach (DataRow row in first.Rows) 
			{ 
				table.LoadDataRow(row.ItemArray, true); 
			} 
 
			foreach (DataRow row in second.Rows) 
			{ 
				table.LoadDataRow(row.ItemArray, true); 
			} 
			table.EndLoadData(); 
			return table; 
		} 
 
		public static void JoinTableByID(DataTable first, DataTable second, string firstColumnID, string secondColumnID, bool firstmatch) 
		{ 
			DataColumn[] newcolumns = new DataColumn[second.Columns.Count - 1]; 
			for (int i = 0; i < second.Columns.Count - 1; i++) 
			{ 
				newcolumns[i] = new DataColumn(second.Columns[i].ColumnName, second.Columns[i].DataType); 
			} 
 
			first.Columns.AddRange(newcolumns); 
 
			string datarowindex = String.Empty; 
			for (int i = 0; i < first.Rows.Count; i++) 
			{ 
				DataRow f = first.Rows[i]; 
				DataRow[] sf = second.Select(secondColumnID + "=" + f[firstColumnID].ToString()); 
				if (sf.Length > 0) 
				{ 
					foreach (DataRow sfdt in sf) 
					{ 
						foreach (DataColumn ccc in sfdt.Table.Columns) 
						{ 
							try 
							{ 
								f[ccc.ColumnName] = sfdt[ccc.ColumnName]; 
							} 
							catch 
							{ 
							} 
						} 
					} 
				} 
				else 
				{ 
					if (!firstmatch) 
					{ 
						datarowindex += i + "|"; 
					} 
				} 
			} 
			if (datarowindex.Length > 0) 
			{ 
				string[] indextodel = datarowindex.Split('|'); 
				foreach (string s in indextodel) 
					if (s.Length > 0) first.Rows[Convert.ToInt32(s)].Delete(); 
			} 
		} 
 
		public static void Join(DataTable first, DataTable second) 
		{ 
			DataColumn[] newcolumns = new DataColumn[second.Columns.Count]; 
			for (int i = 0; i < second.Columns.Count; i++) 
			{ 
				newcolumns[i] = new DataColumn(second.Columns[i].ColumnName, second.Columns[i].DataType); 
			} 
 
			int firstcolcount = first.Columns.Count; 
			first.Columns.AddRange(newcolumns); 
			first.BeginLoadData(); 
 
			for (int ci = 0; ci < first.Rows.Count; ci++) 
			{ 
				for (int i = firstcolcount; i < first.Columns.Count; i++) 
				{ 
					first.Rows[ci][i] = second.Rows[ci][i - firstcolcount]; 
				} 
 
			} 
			first.EndLoadData(); 
		} 
 
		public static void RemoveDuplicates(DataTable tbl, DataColumn[] keyColumns) 
		{ 
			int rowNdx = 0; 
			while(rowNdx < tbl.Rows.Count-1) 
			{ 
				DataRow[] dups = FindDups(tbl, rowNdx, keyColumns); 
				if(dups.Length>0) 
				{ 
					foreach(DataRow dup in dups) 
					{ 
						tbl.Rows.Remove(dup); 
					} 
				} 
				else 
				{ 
					rowNdx++; 
				} 
			} 
		} 
 
 
		public static DataRow[] FindDups(DataTable tbl,int sourceNdx, DataColumn[] keyColumns) 
		{ 
			ArrayList retVal = new ArrayList(); 
			DataRow sourceRow = tbl.Rows[sourceNdx]; 
			for(int i=sourceNdx + 1; i