internal static object ExecuteScalarFromSql(SqlCeDatabase database, string query, FieldValue[] fieldValueArray)
        {
            SqlCeCommand dbCommand;
            Object obj = null;

            dbCommand = database.GetSqlStringCommand(query);

            if (dbCommand != null)
            {
                try
                {

                    SetCommandProperties(dbCommand);

                    if (fieldValueArray != null && fieldValueArray.Length > 0)
                    {
                        dbCommand.Parameters.AddRange(GetParameters(fieldValueArray));
                    }

                    obj = database.ExecuteScalar(dbCommand);
                    if (obj is DBNull)
                    {
                        obj = null;
                    }

                }
                finally
                {
                    dbCommand.Dispose();
                }
            }

            return obj;
        }
        private static SqlCeParameter[] GetParameters(FieldValue[] fieldValueArray)
        {
            List<SqlCeParameter> parameterList = new List<SqlCeParameter>();
            SqlCeParameter parameter;

            for (int index = 0; index < fieldValueArray.Length; index++)
            {

                parameter = new SqlCeParameter();

                parameter.Direction = ParameterDirection.Input;
                parameter.ParameterName = "@" + fieldValueArray[index].FieldDefinition.Name;
                parameter.SqlDbType = (SqlDbType)Enum.Parse(typeof(SqlDbType)
                    , fieldValueArray[index].FieldDefinition.DataTypeName, true);

                parameter.Size = fieldValueArray[index].FieldDefinition.SourceColumnMaxLength;
                parameter.Precision = fieldValueArray[index].FieldDefinition.SourceColumnPrecision;
                parameter.Scale = fieldValueArray[index].FieldDefinition.SourceColumnScale;

                if (fieldValueArray[index].Value == null)
                {
                    parameter.Value = DBNull.Value;
                }
                else
                {
                    parameter.Value = fieldValueArray[index].Value;
                    if (parameter.SqlDbType == SqlDbType.Text)
                    {
                        parameter.Size = fieldValueArray[index].Value.ToString().Length;
                    }
                }

                parameterList.Add(parameter);

            }

            return parameterList.ToArray();
        }
        internal static IDataReader ExecuteReaderFromSql(SqlCeDatabase database, string query, FieldValue[] fieldValueArray)
        {
            SqlCeCommand dbCommand;
            IDataReader rdr = null;

            dbCommand = database.GetSqlStringCommand(query);

            if (dbCommand != null)
            {
                try
                {

                    SetCommandProperties(dbCommand);

                    if (fieldValueArray != null && fieldValueArray.Length > 0)
                    {
                        dbCommand.Parameters.AddRange(GetParameters(fieldValueArray));
                    }

                    rdr = database.ExecuteReader(dbCommand);

                }
                finally
                {
                    dbCommand.Dispose();
                }
            }

            return rdr;
        }
        internal static object ExecuteScalarFromSql(string query, FieldValue[] fieldValueArray)
        {
            SqlCeDatabase db;
            Object obj = null;

            db = GetDatabase();

            obj = ExecuteScalarFromSql(db, query, fieldValueArray);

            db.Close();
            db.Dispose();

            return obj;
        }
        internal static int ExecuteNonQueryFromSql(SqlCeDatabase database, string query, FieldValue[] fieldValueArray)
        {
            SqlCeCommand dbCommand;
            int rtnValue = -1;

            dbCommand = database.GetSqlStringCommand(query);

            if (dbCommand != null)
            {
                try
                {

                    SetCommandProperties(dbCommand);

                    if (fieldValueArray != null && fieldValueArray.Length > 0)
                    {
                        dbCommand.Parameters.AddRange(GetParameters(fieldValueArray));
                    }

                    rtnValue = database.ExecuteNonQuery(dbCommand, false);

                }
                finally
                {
                    dbCommand.Dispose();
                }
            }

            return rtnValue;
        }
        internal static IDataReader ExecuteReaderFromSql(string query, FieldValue[] fieldValueArray)
        {
            SqlCeDatabase db;

            db = GetDatabase();
            return ExecuteReaderFromSql(db, query, fieldValueArray);
        }
        internal static SqlCeResultSet ExecuteDataSetFromSql(SqlCeDatabase database, string query, FieldValue[] fieldValueArray)
        {
            SqlCeCommand dbCommand;
            SqlCeResultSet dataSet = null;

            dbCommand = database.GetSqlStringCommand(query);

            if (dbCommand != null)
            {
                try
                {

                    SetCommandProperties(dbCommand);

                    if (fieldValueArray != null && fieldValueArray.Length > 0)
                    {
                        dbCommand.Parameters.AddRange(GetParameters(fieldValueArray));
                    }

                    dataSet = database.ExecuteDataSet(dbCommand);

                }
                finally
                {
                    dbCommand.Dispose();
                }
            }

            return dataSet;
        }
        internal static int ExecuteNonQueryFromSql(string query, FieldValue[] fieldValueArray)
        {
            SqlCeDatabase db;
            int rtnValue = -1;

            db = GetDatabase();

            rtnValue = ExecuteNonQueryFromSql(db, query, fieldValueArray);

            db.Close();
            db.Dispose();

            return rtnValue;
        }
        internal static FieldDefinition[] CreatePrimaryKeysList(FieldValue[] fieldValues)
        {
            List<FieldDefinition> pkList = new List<FieldDefinition>();

            // iterate through list of fieldDefainitions
            for (int index = 0; index < fieldValues.Length; index++)
            {

                // Is this field a PrimaryKey
                if (fieldValues[index].FieldDefinition.IsPrimaryKey)
                {

                    // incrment primaryKeyCount
                    pkList.Add(fieldValues[index].FieldDefinition);
                }

            }

            // return value
            return pkList.ToArray();
        }
        internal static SqlCeResultSet ExecuteDataSetFromSql(string query, FieldValue[] fieldValueArray)
        {
            SqlCeDatabase db;
            SqlCeResultSet dataSet = null;

            db = GetDatabase();

            dataSet = ExecuteDataSetFromSql(db, query, fieldValueArray);

            db.Close();
            db.Dispose();

            return dataSet;
        }
        internal static string BuildWhereClause(FieldValue[] fieldValueArray)
        {
            string whereClause = "";

            foreach (FieldValue fieldValue in fieldValueArray)
            {
                if (fieldValue.Value == null)
                {
                    whereClause += m_FieldPrefix + fieldValue.FieldDefinition.SourceColumnName + m_FieldSuffix
                        + " IS NULL And ";
                }
                else
                {
                    whereClause += m_FieldPrefix + fieldValue.FieldDefinition.SourceColumnName + m_FieldSuffix
                        + " = @" + fieldValue.FieldDefinition.Name + " And ";
                }
            }

            if (whereClause.Length > 0)
            {
                whereClause = "WHERE " + whereClause.Substring(0, whereClause.Length - 5);
            }

            return whereClause;
        }
        internal static string BuildUpdatePortion(string tableName, FieldValue[] fieldValues)
        {
            StringBuilder query = new StringBuilder();
            StringBuilder fields = new StringBuilder();

            #region add each dirty field

            if (fieldValues.Length > 0)
            {

                for (int index = 0; index < fieldValues.Length; index++)
                {
                    if (fieldValues[index].IsDirty)
                    {
                        fields.Append(m_FieldPrefix + fieldValues[index].FieldDefinition.SourceColumnName + m_FieldSuffix + " = @");
                        fields.AppendLine(fieldValues[index].FieldDefinition.Name + ",");
                    }
                }

            }

            #endregion

            if (fields.Length > 0)
            {

                // remove the last comma
                fields.Remove(fields.Length - 3, 3);

                query.Append("UPDATE ");
                query.Append(tableName);
                query.AppendLine(" SET ");

                query.AppendLine(fields.ToString());

            }

            return query.ToString();
        }
        internal static object ExecuteScalarFromStoredProcedure(string storedProcedureName, FieldValue[] fieldValueArray)
        {
            Database db;
            DbCommand dbCommand;
            Object obj = null;

            db = GetDatabase();

            dbCommand = db.GetStoredProcCommand(storedProcedureName);

            if( dbCommand != null ) {
                try {

                    SetCommandProperties(dbCommand);

                    if( fieldValueArray != null && fieldValueArray.Length > 0 ) {
                        dbCommand.Parameters.AddRange(GetParameters(fieldValueArray));
                    }

                    obj = db.ExecuteScalar(dbCommand);
                    if( obj is DBNull){
                        obj = null;
                    }

                } finally {
                    dbCommand.Dispose();
                }
            }

            return obj;
        }
        internal static IDataReader ExecuteReaderFromStoredProcedure(string storedProcedureName
            , FieldValue[] fieldValueArray)
        {
            Database db;
            DbCommand dbCommand;
            IDataReader rdr = null;

            db = GetDatabase();

            dbCommand = db.GetStoredProcCommand(storedProcedureName);

            if( dbCommand != null ) {
                try {

                    SetCommandProperties(dbCommand);

                    if( fieldValueArray != null && fieldValueArray.Length > 0 ) {
                        dbCommand.Parameters.AddRange(GetParameters(fieldValueArray));
                    }

                    rdr = db.ExecuteReader(dbCommand);

                } finally {
                    dbCommand.Dispose();
                }
            }

            return rdr;
        }
        internal static int ExecuteNonQueryFromStoredProcedure(string storedProcedureName, FieldValue[] fieldValueArray)
        {
            Database db;
            DbCommand dbCommand;
            int rtnValue = -1;

            db = GetDatabase();

            dbCommand = db.GetStoredProcCommand(storedProcedureName);

            if( dbCommand != null ) {
                try {

                    SetCommandProperties(dbCommand);

                    if( fieldValueArray != null && fieldValueArray.Length > 0 ) {
                        dbCommand.Parameters.AddRange(GetParameters(fieldValueArray));
                    }

                    rtnValue = db.ExecuteNonQuery(dbCommand);

                } finally {
                    dbCommand.Dispose();
                }
            }

            return rtnValue;
        }