I've posted a few code snippets and some people noticed that there are references to unresolved methods.
I am using a few Helper Classes .. This post describes my DataHelper class.
namespace FSHelperLib
{
using Microsoft.ApplicationBlocks.Data;
using Microsoft.VisualBasic;
using Microsoft.VisualBasic.CompilerServices;////VB .Net Runtime
using System;
using System.Collections;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Runtime.CompilerServices;
using System.Text;
using System.Windows.Forms;
public class DataHelper
{
// Methods
public DataHelper()
{
}
#region "DbNull,Nothing and Empty"
// 'TODO there are too many similar function 'review and re-factor them
//'Generic method to set not NULL values, similar to SQL Coalesce, the same as Coalesce
public static object Nz(object oItemValue, object oDefault)
{
return DataHelper.Coalesce(RuntimeHelpers.GetObjectValue(oItemValue), RuntimeHelpers.GetObjectValue(oDefault));
}
// 'Generic method to set not NULL values, similar to SQL Coalesce,the same as Nz
public static object Coalesce(object oItemValue, object oDefault)
{
if (oItemValue == null)
{
return RuntimeHelpers.GetObjectValue(oDefault);
}
if (Information.IsDBNull(RuntimeHelpers.GetObjectValue(oItemValue)))
{
return RuntimeHelpers.GetObjectValue(oDefault);
}
//'TODO add DateTime = Nothing as in IsDBNullOrEmpty
return RuntimeHelpers.GetObjectValue(oItemValue);
}
// 'Method similar to Nz, but empty strings also replaced with Default
public static object DefaultIfDBNullOrEmpty(object oItemValue, object oDefault)
{
if (DataHelper.IsDBNullOrEmpty(RuntimeHelpers.GetObjectValue(oItemValue)))
{
return RuntimeHelpers.GetObjectValue(oDefault);
}
return RuntimeHelpers.GetObjectValue(oItemValue);
}
// ' returns true if DBNull Or Empty String or nothing
public static bool IsDBNullOrEmpty(object oItemValue)
{
bool bRet = false;
if (oItemValue == null)
{
return true;
}
if (Information.IsDBNull(RuntimeHelpers.GetObjectValue(oItemValue)))
{
return true;
}
if (oItemValue.GetType() == Type.GetType("System.DateTime"))
{
if (ObjectType.ObjTst(oItemValue, null, false) == 0)
{
bRet = true;
}
return bRet;
}
if (StringType.StrCmp(oItemValue.ToString(), "", false) == 0)
{
bRet = true;
}
return bRet;
}
// 'simple function for string 10/8/2004
public static bool IsNullOrEmpty(string oItemValue)
{
bool bRet = ((oItemValue == null)||(oItemValue.Length==0));
return bRet;
}
public static bool IsEmpty(DateTime dt)
{
return (dt==DateTime.MinValue );
}
// 'THe folowing 2 functions were not tested
// 'Public Shared Function SetIfTrue(ByRef LValue As Object, ByVal condition As Boolean, ByVal Value As Object) As Boolean
// ' Dim bRet As Boolean = False
// ' If condition Then
// ' LValue = Value
// ' ' Debug.WriteLine(" SetIfNotEmpty:" & LValue.ToString())
// ' bRet = True
// ' End If
// ' Return bRet
// 'End Function
// ''LValue must be ReferenceType to be return back
// 'Public Shared Function SetIfValueChanged(ByRef LValue As Object, ByVal NewValue As Object) As Boolean
// ' Debug.Assert(LValue.GetType.IsClass())
// ' Dim bRet As Boolean = False
// ' If LValue <> NewValue Then
// ' LValue = NewValue
// ' ' Debug.WriteLine(" SetIfNotEmpty:" & LValue.ToString())
// ' bRet = True
// ' End If
// ' Return bRet
// 'End Function
public static bool SetDBNullIfEmpty(ref object LValue)
{
bool bRet = false;
if (DataHelper.IsDBNullOrEmpty(RuntimeHelpers.GetObjectValue(LValue)))
{
LValue = DBNull.Value;
bRet = true;
}
return bRet;
}
public static string ToString_EmptyIfNull(object Value)
{
if (DataHelper.IsDBNullOrEmpty(RuntimeHelpers.GetObjectValue(Value)))
{
return "";
}
return Value.ToString();
}
public static object DBNullIfEmpty(object Value)
{
//bool bRet = false;
if (DataHelper.IsDBNullOrEmpty(RuntimeHelpers.GetObjectValue(Value)))
{
Value = DBNull.Value;
}
return Value;
}
public static object NothingIfEmpty(object Value)
{
//bool bRet = false;
if (DataHelper.IsDBNullOrEmpty(RuntimeHelpers.GetObjectValue(Value)))
{
Value = null;
}
return Value;
}
public static bool SetIfNotEmpty(ref object LValue, object Value)
{
DebugHelper.TracedLine("not sure that it returns valid ref");
bool bRet = false;
if (!DataHelper.IsDBNullOrEmpty(RuntimeHelpers.GetObjectValue(Value)))
{
LValue = RuntimeHelpers.GetObjectValue(Value);
bRet = true;
}
return bRet;
}
//(Guid)TypeDescriptor.GetConverter(typeof(Guid)).ConvertFromString(sClassId) doesn't convert empty string
public static Guid GetGUIDOrNullIfEmpty(string Value)
{ Guid oRet=new Guid();// default guid struct
if (!DataHelper.IsDBNullOrEmpty(Value))
{
// if (Value is Guid)
// return Value;
// else
{
oRet=new Guid(Value);
}
}
return oRet;
}
public static string StringFromObject(object Value)
{
string sRet=null;
if (Value!=null)
{
//special case for Guid because is is not supported by Microsoft.VisualBasic.CompilerServices.StringType.FromObject
if (Value.GetType()==typeof(Guid))
sRet= Value.ToString();
else
sRet=StringType.FromObject(Value);
}
return sRet;
}
#endregion "DbNull,Nothing and Empty"
#region " SQL String Manipulations "
public static string Quoted(char arg)
{
return DataHelper.Quoted(StringType.FromChar(arg));
}
// 'To use in SQL queries
public static string Quoted(string arg)
{
return ("'" + Strings.Replace(arg, "'", "''", 1, -1, CompareMethod.Binary) + "'");
}
// 'Used in some cases when Quoted is not appropriate
public static string EscapeSQL(string arg)
{
return Strings.Replace(arg, "'", "''", 1, -1, CompareMethod.Binary);
}
// 'Should be called inside Quoted, eg " LIKE " & Quoted(SafeLikeLiteral(sInput) & "%")
public static string SafeLikeLiteral(string inputSQL)
{
// ' Make the following replacements:
// ' [ becomes [[]
// ' % becomes [%]
// ' _ becomes [_]
// ' s = inputSQL.Replace("'", "''")' ' ' becomes ''
string text1 = inputSQL;
text1 = text1.Replace("[", "[[]");
text1 = text1.Replace("%", "[%]");
return text1.Replace("_", "[_]");
}
// 'Sample code
// ' sWhere = sSQLAppendWhereOrAnd(sWhere)
// ' sWhere &= " IdentityKey <> " & ExcludeIdentityKey
public static string sSQLAppendWhereOrAnd(string strWhere)
{
if (Strings.Len(strWhere) == 0)
{
strWhere = " Where ";
return strWhere;
}
strWhere = strWhere + " and ";
return strWhere;
}
// 'Not very relyable, because it can be 'where' in the data, not as keyword
public static string PrefixWhereIfRequired(string strWhere)
{
if ((Strings.Len(strWhere.Trim()) > 0) && (Strings.InStr(strWhere, "Where", CompareMethod.Text) <= 0))
{
strWhere = " Where " + strWhere;
}
return strWhere;
}
public static string SQLAppendAndIfNotEmpty(string strWhere)
{
if (Strings.Len(strWhere) > 0)
strWhere = strWhere + " and ";
return strWhere;
}
///
///
///
///
existing Where string
///
additional "and" condition
///
public static string SQLAppendCondition(string strWhere,string Condition)
{
if (Strings.Len(strWhere) > 0)
{
strWhere = strWhere + " and ";
}
strWhere+=Condition;
return strWhere;
}
// 'corresponds to MS Access AcFindMatch constants acAnywhere ,acEntire and acStart
public enum FindMatchEnum
{
// Fields
Anywhere = 0,//'Any part of Field -like '%x%'
Entire = 1,
Start = 2, //'Start of Field
CommaSeparatedList = 3
}
public static string SQLLikeCriteria(FindMatchEnum matcher, string sFind)
{
return " LIKE " + SQLQuotedLikeValue(matcher,sFind);
}
public static string SQLQuotedLikeValue(FindMatchEnum matcher, string sFind)
{
string sRet="";
// 'Select case for matcher to create something similar to Like '%" & Titlefinder & "%'))
switch (matcher)
{
case FindMatchEnum.Anywhere:
sRet= DataHelper.Quoted("%" + sFind + "%");
break;
case FindMatchEnum.Entire:
sRet= DataHelper.Quoted(sFind);
break;
case FindMatchEnum.Start:
sRet= DataHelper.Quoted(sFind + "%");
break;
}
return sRet;
}
#endregion " SQL String Manipulations "
#region "DataSet and DataTable Manipulation"
// 'TODO See also and probably move to DatasetHelper.cs
//
// 'function replace existing table if exist
// 'Changed ds returns both as function return and as a parameter
public static DataSet DatasetAddTable(DataSet ds, DataTable tblSrc, string sTableName, bool bReplaceIfExist)
{
// 'http://support.microsoft.com/default.aspx?scid=kb%3Ben-us%3B305346. HOW TO: Copy DataRows Between DataTables
// ' Copy the entire DataTable.
DataTable table1 = tblSrc.Copy();
table1.TableName = sTableName;
if (bReplaceIfExist)
{
DataHelper.DatasetRemoveTable(ds, sTableName);
}
ds.Tables.Add(table1);
return ds;
}
//'Remove if exists
public static bool DatasetRemoveTable(DataSet ds, string sTableName)
{
DataTable table1 = ds.Tables[sTableName];
if (!Information.IsNothing(table1))
{
ds.Tables.Remove(table1);
return true;
}
return false;
}
public static DataColumn[] SetTablePrimaryKey(DataTable tbl, string sColumnName)
{
if (Information.IsNothing(tbl))
{
return null;
}
DataColumn[] columnArray1 = new DataColumn[2];
columnArray1[0] = tbl.Columns[sColumnName];
tbl.PrimaryKey = columnArray1;
return columnArray1;
}
//safe rows count
public static int RowsCount(DataTable tbl)
{ int nRet=0;
if (tbl!=null) { nRet=tbl.Rows.Count;}
return nRet;
}
public static bool IsNullOrEmpty(DataTable tbl)
{
return (RowsCount( tbl)==0);
}
#endregion //"DataSet and DataTable Manipulation"
#region "Column Manipulation"
public static string TableColumnToString(DataTable tbl, string sColumnName, string delimeter)
{
StringBuilder builder1 = new StringBuilder();
foreach (DataRow row1 in tbl.Rows )
{
builder1.Append(RuntimeHelpers.GetObjectValue(row1[sColumnName]));
builder1.Append(delimeter);
}
return StringHelper.LeftBeforeLast(builder1.ToString(), delimeter);
}
//Attempt to use CType(DataHelper.TableColumnToArray(ds.Tables(0), "IndexTable"), String()) causes
// InvalidCastException:Specified cast is not valid
//to workaround see TableColumnToStringArray
public static Object[] TableColumnToArray(DataTable tbl, string sColumnName)
{
ArrayList arr= new ArrayList(tbl.Rows.Count);
foreach (DataRow row1 in tbl.Rows )
{
arr.Add(row1[sColumnName]);
}
return arr.ToArray();
}
public static string[] TableColumnToStringArray(DataTable tbl, string sColumnName)
{
ArrayList arr= new ArrayList(tbl.Rows.Count);
foreach (DataRow row1 in tbl.Rows )
{
arr.Add(row1[sColumnName]);
}
return (string [])arr.ToArray(typeof(System.String));
}
// 'After concatenating it closes DataReader
public static string TableColumnToString(IDataReader rdr, string sColumnName, string delimeter)
{
StringBuilder builder1 = new StringBuilder();
try
{
while (rdr.Read())
{
builder1.Append(RuntimeHelpers.GetObjectValue(rdr[sColumnName]));
builder1.Append(delimeter);
}
}
finally
{
if(rdr!=null) rdr.Close();
}
return StringHelper.LeftBeforeLast(builder1.ToString(), delimeter);
}
public static bool TableColumnAddIfNotExists(DataTable tbl, string sColumnName)
{
bool bRet=false;
if (tbl.Columns[sColumnName] == null)
{
tbl.Columns.Add(sColumnName);
bRet=true;
}
return bRet;
}
public static void Table_TrimColumnNames(DataTable tbl)
{
// string text1;
foreach(DataColumn clmn in tbl.Columns)
{
clmn.ColumnName=clmn.ColumnName.Trim();
}
//return text1;
}
public static bool TableColumnAllNulls(DataTable tbl, string sColumnName)
{
bool bRet = true;
foreach (DataRow row1 in tbl.Rows)
{
if (!Information.IsDBNull(RuntimeHelpers.GetObjectValue(row1[sColumnName])))
{
return false;
}
}
return bRet;
}
public static bool TableColumnAtLeastOneNull(DataTable tbl, string sColumnName)
{
foreach(DataRow row in tbl.Rows)
{
if (Information.IsDBNull(row[sColumnName]))
{
return true;
}
}
return