Office Systems Developer

[ComVisible(true)]
[Guid("82382232-F1D7-4048-A1E0-F2879BCA0610")]
[ClassInterface(ClassInterfaceType.None)]
[ProgId("M3CommonFunctions")]
public class M3CommonFunctions : IM3CommonFunctions

Assembly asm = Assembly.GetExecutingAssembly();

     public Version GetMACLVersion
     {   
         get {  return asm.GetName().Version; }
     }

[Guid("4ECAAC7B-711C-4ac0-BBFC-58C4E3E8EE56")]
   [InterfaceType(ComInterfaceType.InterfaceIsIDispatch)]
   public interface IM3CommonFunctions
   {
       [DispId(1)]
       Version GetMACLVersion  { get; }
   }

<ComClass(Namespace_Comuns.ClassId, Namespace_Comuns.InterfaceId, Namespace_Comuns.EventsId)> _
Public Class Namespace_Comuns

    Private m_VersaoBiblioteca As String

#Region "COM GUIDs"
    ' These  GUIDs provide the COM identity for this class 
    ' and its COM interfaces. If you change them, existing 
    ' clients will no longer be able to access the class.
    Public Const ClassId As String = "6b6c0965-e6a5-4089-afbb-0ec787efe024"
    Public Const InterfaceId As String = "d8c53404-58b4-4f28-9d2f-4912f8cb6723"
    Public Const EventsId As String = "165be10c-a5a6-4d57-ac49-0ad7a8bd1cd5"
#End Region

    ' A creatable COM class must have a Public Sub New() 
    ' with no parameters, otherwise, the class will not be 
    ' registered in the COM registry and cannot be created 
    ' via CreateObject.
    Public Sub New()
        MyBase.New()
    End Sub

    Public ReadOnly Property VersaoBiblioteca() As String
        Get
            m_VersaoBiblioteca = My.Application.Info.Version.ToString
            Return m_VersaoBiblioteca
        End Get
    End Property
End Class

Private Sub Command0_Click()
    Dim obj As New Ukase.M3CommonFunctions
    MsgBox obj.GetMACLVersion
End Sub

static ADOX.DataTypeEnum GetAccessFormats(DataColumn col)
        {
            switch (col.DataType.Name)
            {
                case "Boolean":
                    return ADOX.DataTypeEnum.adBoolean;
                case "Byte":
                    return ADOX.DataTypeEnum.adUnsignedTinyInt;
                case "Char":
                    return ADOX.DataTypeEnum.adVarWChar;
                case "DateTime":
                    return ADOX.DataTypeEnum.adDate;
                case "Decimal":
                    return ADOX.DataTypeEnum.adDecimal;
                case "Double":
                    return ADOX.DataTypeEnum.adDouble;
                case "Int16":
                    return ADOX.DataTypeEnum.adSmallInt;
                case "Int32":
                    return ADOX.DataTypeEnum.adInteger;
                case "Int64":
                    return ADOX.DataTypeEnum.adInteger;
                case "SByte":
                    return ADOX.DataTypeEnum.adUnsignedSmallInt;
                case "Single":
                    return ADOX.DataTypeEnum.adSingle;
                case "String":
                    return ADOX.DataTypeEnum.adVarWChar;
                case "TimeStamp":
                    return ADOX.DataTypeEnum.adGUID;
                default:
                    return ADOX.DataTypeEnum.adUserDefined;
            }
        }

[Flags]
        public enum ADOXFields
        {
            AccessText = DataTypeEnum.adVarWChar,
            AccessMemo = DataTypeEnum.adLongVarWChar,
            AccessNumericByte = DataTypeEnum.adUnsignedTinyInt,
            AccessNumericInteger  = DataTypeEnum.adSmallInt,
            AccessNumericLongInteger = DataTypeEnum.adInteger,
            AccessNumericSinglePrecision = DataTypeEnum.adSingle,
            AccessNumericDoublePrecision = DataTypeEnum.adDouble,
            AccessNumericReplicatieId = DataTypeEnum.adGUID,
            AccessNumericDecimal = DataTypeEnum.adNumeric,
            AccessDateTime = DataTypeEnum.adDate,
            AccessCurrency = DataTypeEnum.adCurrency,
            AccessAutoNumber = DataTypeEnum.adInteger,
            AccessYesNo = DataTypeEnum.adBoolean,
            AccessHyperLink = DataTypeEnum.adLongVarWChar
        }

Public Shared Function GetAccessFormats(ByVal col As DataColumn) As ADOX.DataTypeEnum
        Select Case (col.DataType.Name)
            Case "Boolean"
                Return ADOX.DataTypeEnum.adBoolean
            Case "Byte"
                Return ADOX.DataTypeEnum.adUnsignedTinyInt
            Case "Char"
                Return ADOX.DataTypeEnum.adVarWChar
            Case "DateTime"
                Return ADOX.DataTypeEnum.adDate
            Case "Decimal"
                Return ADOX.DataTypeEnum.adDecimal
            Case "Double"
                Return ADOX.DataTypeEnum.adDouble
            Case "Int16"
                Return ADOX.DataTypeEnum.adSmallInt
            Case "Int32"
                Return ADOX.DataTypeEnum.adInteger
            Case "Int64"
                Return ADOX.DataTypeEnum.adInteger
            Case "SByte"
                Return ADOX.DataTypeEnum.adUnsignedSmallInt
            Case "Single"
                Return ADOX.DataTypeEnum.adSingle
            Case "String"
                Return ADOX.DataTypeEnum.adVarWChar
            Case "TimeStamp"
                Return ADOX.DataTypeEnum.adGUID
            Case Else
                Return ADOX.DataTypeEnum.adUserDefined
        End Select
    End Function

public string ConvertFieldToUpper(string MyTable, string MyField)
        {
            Access.Application oAccess = ((Access.Application)
                (Marshal.GetActiveObject("Access.Application")));
            try
            {
                string strSQL;
                strSQL = String.Format("UPDATE {0} SET {1} = UCase([{1}])", MyTable, MyField);

                oAccess.DoCmd.RunSQL(strSQL.ToString(),null);
                return "OK";
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                Marshal.ReleaseComObject(oAccess);
            }
        }


        public string ConvertFieldToLower(string MyTable, string MyField)
        {
            Access.Application oAccess = ((Access.Application)
                (Marshal.GetActiveObject("Access.Application")));
            try
            {
                string strSQL;
                strSQL = String.Format("UPDATE {0} SET {1} = LCase([{1}])", MyTable, MyField);

                oAccess.DoCmd.RunSQL(strSQL.ToString(), null);
                return "OK";
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                Marshal.ReleaseComObject(oAccess);
            }
        }


        public string ConvertFieldToProper(string MyTable, string MyField)
        {
            Access.Application oAccess = ((Access.Application)
                (Marshal.GetActiveObject("Access.Application")));
            try
            {
                string strSQL;
                strSQL = String.Format("UPDATE {0} SET {1} = STRCONV([{1},3])", MyTable, MyField);

                oAccess.DoCmd.RunSQL(strSQL.ToString(), null);
                return "OK";
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                Marshal.ReleaseComObject(oAccess);                
            }
        }    

/// <summary>
        /// 
        /// </summary>
        /// <param name="hideNavigationPane"></param>
        /// <param name="myUIName"></param>
        /// <param name="myUIXML"></param>
        /// <returns>True/False</returns>
        public bool InitializeDatabase(bool hideNavigationPane, 
            string myUIName, string myUIXML)
        {   
            Access.Application oAccess = ((Access.Application)
                   (Marshal.GetActiveObject("Access.Application")));
            try
            {                   
                oAccess.SetOption("DesignWithData", false);                
                oAccess.SetOption("Perform Name AutoCorrect", false);
                oAccess.SetOption("Track Name AutoCorrect Info", false);
                oAccess.SetOption("Auto Compact", false);                                
                oAccess.SetOption("Confirm Record Changes", false);
                oAccess.SetOption("Confirm Document Deletions", false);
                oAccess.SetOption("Confirm Action Queries", false);

                oAccess.LoadCustomUI(myUIName, myUIXML);

                if (hideNavigationPane == true)
                {
                    oAccess.DoCmd.RunCommand(Access.AcCommand.acCmdWindowHide);
                }                
                return true;                
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                Marshal.ReleaseComObject(oAccess);
            }
        }

public bool ConvertReportToXPS(string myReport, string XPSFileName)
        {
            Access.Application oAccess = ((Access.Application)
                (Marshal.GetActiveObject("Access.Application")));
            try
            {
                oAccess.DoCmd.OutputTo(Access.AcOutputObjectType.acOutputReport,
                    myReport, Access.Constants.acFormatXPS, XPSFileName,
                    System.Type.Missing, System.Type.Missing, 
                        System.Type.Missing);
                return true;
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                Marshal.ReleaseComObject(oAccess);
            }
        }

public bool TablesAreEqual(string MyTableSource, string 
            MyTableToCompare, string MyIndexField)
        {                          
            try
            {
                OleDbConnection cnn;
                using (DataSet ds = new DataSet())
                {
                    OleDbDataAdapter da;
                    string cs = 
                        oAccess.CurrentProject.Connection.ToString();
                    string strSQL = 
                        String.Format("SELECT * FROM {0} LEFT JOIN {1} ON {0}.{2} = {1}.{2}WHERE {1}.{2} Is Null"
                        , MyTableSource, MyTableToCompare, MyIndexField);
                    cnn = new OleDbConnection(cs);
                    da = new OleDbDataAdapter(strSQL, cnn);
                    da.Fill(ds);
                    if (ds.Tables[0].Rows.Count <= 0)
                        return true;
                    else
                        return false;
                }
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                Marshal.ReleaseComObject(oAccess);
            }
        }

Access.Application oAccess = ((Access.Application)
                (Marshal.GetActiveObject("Access.Application")));
// ----------------------------------------------------------------------------------------
// Author:                        Joao Tito Livio
// Company:                       MACL
// Assembly version:              0.0.*
// Date:                          21-04-2009
// Time:                          23:47
// Project Item Name:             M3DatabaseUtilities.cs
// Project Item Filename:         M3DatabaseUtilities.cs
// Project Item Kind:             Code
// Class FullName:                Macl.M3DatabaseUtilities
// Class Name:                    M3DatabaseUtilities
// Class Kind Description:        Class
// Class Kind Keyword:            class
// Procedure FullName:            Macl.M3DatabaseUtilities.BackupCurrentDatabase
// Procedure Name:                BackupCurrentDatabase
// Procedure Kind Description:    Function
// Procedure Kind Keyword:        
// Function Return Type Name:     Boolean
// Function Return Type FullName: System.Boolean
// Function Return Type Alias:    bool
// Purpose:                       Backup the Current database, CAN BE IN USE
// Parameters:
//    - MyDestinationpathAndFile (string)() :  Path and File (c:\backup.accdb")
// ----------------------------------------------------------------------------------------
public bool BackupCurrentDatabase(string MyDestinationpathAndFile)
{               
    try
    {
       Access.Application oAccess = ((Access.Application)
        (Marshal.GetActiveObject("Access.Application")));

        File.Copy(oAccess.CurrentProject.FullName, 
            MyDestinationpathAndFile);
        return true;                
    }
    catch (Exception)
    {
        throw;
    }
    finally
    {
        Marshal.ReleaseComObject(oAccess);
    }
}

public bool ExecuteSqlFromFile(String MyConnectionString, String MyTextFilePath)
        {
            using (OleDbConnection sqlConnection = new OleDbConnection(MyConnectionString))
            {
                OleDbCommand cmd = new OleDbCommand();
                try
                {
                    using (StreamReader fh = new StreamReader(MyTextFilePath))
                    {
                        String MyText;
                        string s;
                        while ((s = fh.ReadLine()) != null)
                            MyText = s;
                        fh.Close();
                        cmd.CommandText = s;
                    }
                    cmd.CommandType = CommandType.Text;
                    cmd.Connection = sqlConnection;
                    sqlConnection.Open();
                    cmd.ExecuteNonQuery();
                    sqlConnection.Close();
                    return true;
                }
                catch (Exception)
                {
                    throw;
                }
                finally
                {
                    sqlConnection.Close();
                    cmd.Dispose();
                }
            }
        }

// ----------------------------------------------------------------------------------------
        // Author:                        Joao Tito Livio
        // Company:                       MACL
        // Assembly version:              0.0.*
        // Date:                          21-04-2009
        // Time:                          23:55
        // Project Item Name:             M3Data.cs
        // Project Item Filename:         M3Data.cs
        // Project Item Kind:             Code
        // Class FullName:                Macl.M3Data
        // Class Name:                    M3Data
        // Class Kind Description:        Class
        // Class Kind Keyword:            class
        // Procedure FullName:            Macl.M3Data.ReturnRecordset
        // Procedure Name:                ReturnRecordset
        // Procedure Kind Description:    Function
        // Procedure Kind Keyword:        
        // Function Return Type Name:     Recordset
        // Function Return Type FullName: ADODB.Recordset
        // Function Return Type Alias:    Recordset
        // Purpose:                       Return a recordset from any OleDb provider
        // Parameters:
        //    - MyConnectionString (string)()   : Connection strin for the OleDb Conenction
        //    - MyTableOrSQL (string)()         : Can be a Table or a SQL statement
        //    - MyCursorType (CursorTypeEnum)() : http://msdn.microsoft.com/en-us/library/ms677593(VS.85).aspx
        // ----------------------------------------------------------------------------------------
        public ADODB.Recordset ReturnRecordset(String MyConnectionString, String MyTableOrSQL,
                                               ADODB.CursorTypeEnum MyCursorType)
        {
            ADODB.Connection cnn = new ADODB.Connection();
            ADODB.Recordset rs = new ADODB.Recordset();
            
            try
            {
                cnn.ConnectionString = MyConnectionString;                
                cnn.Open(null, null, null, 0);
                //}                
                rs.Open(MyTableOrSQL, cnn, MyCursorType, ADODB.LockTypeEnum.adLockOptimistic, -1);
                return rs;
            }
            catch (Exception)
            {
                throw;
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="MyPath"></param>
        /// <returns>True/False</returns>
        public bool GenerateLogDatabase(string myPath)
        {   
            CatalogClass cat = new CatalogClass();
            string strSQL;
            string cs;

            try
            {
                cs = "Provider=Microsoft.Jet.OLEDB.4.0;" +
                     "Data Source=" + myPath + ";" +
                     "Jet OLEDB:Engine Type=5";

                strSQL = "CREATE TABLE Issues (mID AUTOINCREMENT, mUser TEXT(100) NOT NULL " +
                         ", mError TEXT(100) NOT NULL, " +
                         "mDescription TEXT(100) NOT NULL, mDate DATETIME NOT NULL)";

                cat.Create(cs);

                using (OleDbConnection cnn = new OleDbConnection(cs))
                {
                    OleDbCommand cmd = new OleDbCommand();
                    try
                    {
                        cmd.CommandText = strSQL;
                        cmd.CommandType = CommandType.Text;
                        cmd.Connection = cnn;
                        cnn.Open();
                        cmd.ExecuteNonQuery();
                        cnn.Close();
                        return true;
                    }
                    catch (Exception)
                    {
                        throw;
                    }
                    finally
                    {
                        cnn.Close();
                        cmd.Dispose();
                    }
                }
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {   
                Marshal.FinalReleaseComObject(cat);
            }
        }