Application for uploading modified Files to a FTP Server

namespace FtpSync.Common
{
	using System;
	using System.Collections;
	using System.Configuration;

	/// <summary>
	/// Helper class with, dealing with databases accessed through normal ADO.
	/// </summary>
	public class AdoHelper
	{
		// Initialize the class with the connection string.
		private static string ConnectionString
		{
			get
			{
				return ConfigurationSettings.AppSettings["connectionString"];
			}
		}

		// the current connection to the database.
		public static ADODB.Connection Connection
		{
			get
			{
				if ( Connection_==null )
				{
					Connection_ = new ADODB.ConnectionClass();
					Connection_.Open( ConnectionString, "", "", 0 );
				}

				return Connection_;
			}
		}
		private static ADODB.Connection Connection_ = null;

		// --

		// simply creates.
		// backward_cursor can be true or false.
		public static ADODB.Recordset CreateRs()
		{
			return CreateRs( false );
		}

		public static ADODB.Recordset CreateRs( bool backward_cursor )
		{
			ADODB.Recordset rs = new ADODB.RecordsetClass();

			rs.ActiveConnection = Connection;
			rs.CursorLocation = ADODB.CursorLocationEnum.adUseClient;
			// also Michael Steppe told to use adUserServer,
			// the adUseClient is damned fast.
			rs.LockType = ADODB.LockTypeEnum.adLockReadOnly;
			if ( backward_cursor )
				rs.CursorType = ADODB.CursorTypeEnum.adOpenStatic;
			else
				rs.CursorType = ADODB.CursorTypeEnum.adOpenForwardOnly;

			return rs;
		}

		// opens read-only.
		public static ADODB.Recordset OpenRs( string sql )
		{
			return OpenRs( sql, false );
		}

		// opens read-only. Reads a single value.
		public static object OpenScalar( string sql )
		{
			ADODB.Recordset rs = OpenRs( sql, false );

			object result;
			if ( rs.EOF )
				result = null;
			else
				result = rs.Fields[0].Value;

			rs.Close();
			rs = null;

			return result;
		}

		// just executes.
		public static void Execute( string sql )
		{
			ADODB.Recordset rs = OpenRs( sql, false );
			rs = null;
		}

		public static ADODB.Recordset OpenRs( string sql, bool backward_cursor )
		{
			ADODB.Recordset rs = CreateRs( backward_cursor );
			rs.Open( sql, rs.ActiveConnection, rs.CursorType, rs.LockType, 
				(int)ADODB.RecordOpenOptionsEnum.adOpenRecordUnspecified );

			return rs;
		}

		// functions to format date dd.mm.yyyy
		public static string FmtSqlDate( DateTime dt )
		{
			return
				StringHelper.MakeNumLen( dt.Day  , 2 ) + "." + 
				StringHelper.MakeNumLen( dt.Month, 2 ) + "." + 
				StringHelper.MakeNumLen( dt.Year , 4 );
		}

		// returns the last inserted ID value.
		public static int GetIdentity( 
			ADODB.Recordset rs, 
			string field_name,
			string table_name,
			bool is_new )
		{
			return GetIdentityEx( Connection, rs, field_name, table_name, is_new );
		}

		public static int GetIdentityEx( 
			ADODB.Connection connection, 
			ADODB.Recordset rs_,
			string field_name,
			string table_name,
			bool is_new )
		{
			if ( is_new )
			{
				object null_object = null;
				ADODB.Recordset rs = connection.Execute(
					"SELECT IDENT_CURRENT('" + table_name + "') AS theid", 
					out null_object, (int)ADODB.ExecuteOptionEnum.adOptionUnspecified );

				int result = Convert.ToInt32(rs.Fields["theid"].Value);

				rs.Close();
				rs = null;

				return result;
			}
			else
				return Convert.ToInt32(rs_.Fields[field_name].Value);
		}

		// --
		// read a value from db, even if Null.

		public static int ReadField_Integer( ADODB.Field field )
		{
			return ReadField_Integer( field.Value );
		}

		public static ulong ReadField_ULong( ADODB.Field field )
		{
			return ReadField_ULong( field.Value );
		}

		public static string ReadField_String( ADODB.Field field )
		{
			return ReadField_String( field.Value );
		}

		public static decimal ReadField_Decimal( ADODB.Field field )
		{
			return ReadField_Decimal( field.Value );
		}

		public static object ReadField_Object( ADODB.Field field )
		{
			return ReadField_Object( field.Value );
		}

		public static DateTime ReadField_DateTime( ADODB.Field field )
		{
			return ReadField_DateTime( field.Value );
		}

		public static bool ReadField_Boolean( ADODB.Field field )
		{
			return ReadField_Boolean( field.Value );
		}

		// --

		public static int ReadField_Integer( object field_value )
		{
			if ( field_value==null || field_value==DBNull.Value || 
				!StringHelper.IsNumeric(field_value.ToString()) )
				return 0;
			else
				return Convert.ToInt32(field_value);
		}

		public static ulong ReadField_ULong( object field_value )
		{
			if ( field_value==null || field_value==DBNull.Value || 
				!StringHelper.IsNumeric(field_value.ToString()) )
				return 0;
			else
				return Convert.ToUInt32(field_value);
		}

		public static string ReadField_String( object field_value )
		{
			return field_value==null || field_value==DBNull.Value?"":Convert.ToString(field_value);
		}

		public static decimal ReadField_Decimal( object field_value )
		{
			return field_value==null || field_value==DBNull.Value?0:Convert.ToDecimal(field_value);
		}

		public static object ReadField_Object( object field_value )
		{
			return field_value==null || field_value==DBNull.Value?null:field_value;
		}

		public static DateTime ReadField_DateTime( object field_value )
		{
			return field_value==null || field_value==DBNull.Value?DateTime.MinValue:Convert.ToDateTime(field_value);
		}

		public static bool ReadField_Boolean( object field_value )
		{
			return field_value==null || field_value==DBNull.Value?false:Convert.ToBoolean(field_value);
		}

		// --
		// set a value for db, convert for null.

		public static object WriteField_Integer( int field_value )
		{
			return field_value;
		}

		public static object WriteField_Integer( string field_value )
		{
			return ConvertHelper.ToInt32(field_value);
		}

		public static object WriteField_ULong( ulong field_value )
		{
			return field_value;
		}

		public static object WriteField_Integer( int field_value, int if_this, object then_this )
		{
			if ( field_value==if_this )
				return then_this;
			else
				return field_value;
		}

		public static object WriteField_String( string field_value )
		{
			if ( field_value=="" )
				return DBNull.Value;
			else
				return field_value;
		}

		public static object WriteField_Decimal( decimal field_value )
		{
			/*
			if ( field_value==0 )
				return DBNull.Value;
			else
			*/
			return field_value;
		}

		public static object WriteField_Object( object field_value )
		{
			if ( field_value==null )
				return DBNull.Value;
			else
				return field_value;
		}

		public static object WriteField_DateTime( DateTime field_value )
		{
			return field_value;
		}

		public static object WriteField_Boolean( bool field_value )
		{
			return field_value;
		}

		// --

		// converts for persistant storage/later usage.
		public static Hashtable FieldsToMap( ADODB.Fields fields )
		{
			Hashtable result = new Hashtable();

			foreach ( ADODB.Field field in fields )
			{
				result[field.Name] = field.Value;
			}

			return result;
		}
	}
}