[archive] Creating System Dsn's dynamically

[Migrated content. Thread originally posted on 14 March 2005]

Hello,

I have a situation where I need to create System DSN's using the AccuODBC. I don't need a UI in fact I don't want to use the UI to create the System DSN. I need to have my application that will dynamically create the System dsn base on parameters that are passed to it and use the newly created DSN. After it has completed its task then Delete it again. We are using the .Net Framework and either VB.net or C#.net. Any Ideas or suggestion would be appreciated.


Thanks

The simplest way would be to use a DSN less connection string like this:

Driver={AcuODBC Driver};DBQ=C:\\ACUDATA\\XFD;FILE PREFIX=C:\\ACUDATA\\FILES;FILE SUFFIX=FIL;

but it doesn't seem to work with the AcuODBC driver. In .NET you get the error:

Unhandled Exception: System.Data.Odbc.OdbcException: ERROR [08001] [AcuCorp, Inc.][AcuODBC Driver]Please specify an XFD (data dictionary) directory.
at System.Data.Odbc.OdbcConnection.Open()

So, next option is to create the sytem DSN in the registry. The Registry key is HKEY_LOCAL_MACHINE\\SOFTWARE\\ODBC\\ODBC.INI
If you look at this key on your PC you will find subkey entries for your AcuODBC DSN's. Within those DSN subkeys are values and their respective data. In your .NET program use the same values, but just change the data to fit the needs of the user. Use the Microsoft.Win32 namespace for reading/writing/deleting in the registry.

Example here:
http://www.thecodeproject.com/csharp/modifyregistry.asp

[Migrated content. Thread originally posted on 14 March 2005]

Hello,

I have a situation where I need to create System DSN's using the AccuODBC. I don't need a UI in fact I don't want to use the UI to create the System DSN. I need to have my application that will dynamically create the System dsn base on parameters that are passed to it and use the newly created DSN. After it has completed its task then Delete it again. We are using the .Net Framework and either VB.net or C#.net. Any Ideas or suggestion would be appreciated.


Thanks

Thanks yes...I have to use a System DSN...I came just found another example as follows:


using System;
using System.Runtime.InteropServices;

    ///
    /// The ODBC Library class uses the ODBCCP32.dll API to create and remove ODBC System DSN's
    ///
    public class OdbcLib
    {

        #region Private constants
            //Module Constants used in creating DSN's
            private const string ODBC_INI_DSN_KEY_NAME = "DSN=";
            private const string ODBC_INI_FILE_PREFIX_NAME = "\\0File Prefix=";
            private const string ODBC_INI_UID_NAME = "\\0Uid=";
            private const string ODBC_INI_PWD_NAME = "\\0pwd=";
            private const string ODBC_INI_DBQ_NAME = "\\0DBQ=";
            private const string ODBC_INI_ACUODBC_DRIVER = "AcuODBC Driver";    //The Default Driver
            private const string ODBC_INI_READONLY = "\\0Read Only=";
        
        #endregion

        #region Constructors
            ///
            /// The constructor.
            ///
            public OdbcLib()
            {
            }
        #endregion

        #region Public methods
        ///
        /// Creates a User DSN.
        ///
        /// Name of the ODBC Driver
        /// Name of the DSN
        /// Path to database
        /// True if successfull
        public bool AddUserDSN(string OdbcDriverName, string DSName, string DBPath)
        {
            return SQLConfigDataSource((IntPtr)0, 1, OdbcDriverName "\\0",
                ODBC_INI_DSN_KEY_NAME DSName ODBC_INI_UID_NAME "Admin" ODBC_INI_PWD_NAME "" ODBC_INI_DBQ_NAME DBPath "\\0");
        }
        
        ///
        /// Modify a User DSN.
        ///
        /// Name of the ODBC Driver
        /// Name of the DSN
        /// Path to database
        /// True if successfull
        public bool EditUserDSN(string OdbcDriverName, string DSName, string DBPath)
        {
            return SQLConfigDataSource((IntPtr)0, 2, OdbcDriverName "\\0",
                ODBC_INI_DSN_KEY_NAME DSName ODBC_INI_UID_NAME "Admin" ODBC_INI_PWD_NAME "" ODBC_INI_DBQ_NAME DBPath "\\0");

        }

        ///
        /// Remove a User DSN.
        ///
        /// Name of the ODBC Driver
        /// Name of the DSN
        /// True if successfull
        public bool RemoveUserDSN(string OdbcDriverName, string DSName)
        {
            return SQLConfigDataSource((IntPtr)0, 3, OdbcDriverName "\\0",
                ODBC_INI_DSN_KEY_NAME DSName ODBC_INI_UID_NAME "Admin" ODBC_INI_PWD_NAME "" ODBC_INI_DBQ_NAME "\\0");
        }


        ///
        /// Add a System DSN.
        ///
        /// Name of the ODBC Driver
        /// Name of the DSN
        /// FilePrefix
        /// DBQ
        /// True if successfull
        public bool AddSystemDSN(string OdbcDriverName, string DSName, string FilePrefix ,string DBQ)
        {
            return SQLConfigDataSource((IntPtr)0, 4, OdbcDriverName,
                ODBC_INI_DSN_KEY_NAME DSName ODBC_INI_FILE_PREFIX_NAME FilePrefix ODBC_INI_DBQ_NAME DBQ ODBC_INI_READONLY "FALSE" "\\0");

        }

        ///
        /// Add a System DSN.
        ///
        /// Name of the DSN
        /// FilePrefix
        /// DBQ
        /// True if successfull
        public bool AddSystemDSN(string DSName, string FilePrefix ,string DBQ)
        {
            return AddSystemDSN(ODBC_INI_ACUODBC_DRIVER, DSName,FilePrefix,DBQ);

        }

        ///
        /// Modify a System DSN
        ///
        /// Name of the ODBC Driver
        /// Name of DSN
        /// FilePrefix
        /// DBQ
        /// True if successfull
        public bool EditSystemDSN(string OdbcDriverName, string DSName, string FilePrefix ,string DBQ)
        {

            return SQLConfigDataSource((IntPtr)0, 5, ODBC_INI_ACUODBC_DRIVER,
                ODBC_INI_DSN_KEY_NAME DSName ODBC_INI_FILE_PREFIX_NAME FilePrefix ODBC_INI_DBQ_NAME DBQ "\\0");

        }

        ///
        /// Modify a System DSN
        ///
        /// Name of DSN
        /// FilePrefix
        /// DBQ
        /// True if successfull
        public bool EditSystemDSN(string DSName, string FilePrefix ,string DBQ)
        {

            return EditSystemDSN(ODBC_INI_ACUODBC_DRIVER, DSName,FilePrefix, DBQ);

        }
        
        ///
        /// Remove a System DSN
        ///
        /// Name of the ODBC Driver
        /// Name of DSN
        /// True if successfull
        public  bool RemoveSystemDSN(string OdbcDriverName, string DSName)
        {

            return SQLConfigDataSource((IntPtr)0, 6, ODBC_INI_ACUODBC_DRIVER,
                ODBC_INI_DSN_KEY_NAME DSName ODBC_INI_FILE_PREFIX_NAME ODBC_INI_DBQ_NAME "\\0");

        }

        ///
        /// Remove a System DSN
        ///
        /// Name of DSN
        /// True if successfull
        public  bool RemoveSystemDSN(string DSName)
        {

            return RemoveSystemDSN(ODBC_INI_ACUODBC_DRIVER,DSName);

        }
        #endregion

        #region Win32 API Import
        ///
        /// Win32 API Import
        ///
        [DllImport("ODBCCP32.dll")]
        private static extern bool SQLConfigDataSource(IntPtr parent, int request, string driver, string attributes);
        #endregion
    
    }



Thanks

[Migrated content. Thread originally posted on 14 March 2005]

Hello,

I have a situation where I need to create System DSN's using the AccuODBC. I don't need a UI in fact I don't want to use the UI to create the System DSN. I need to have my application that will dynamically create the System dsn base on parameters that are passed to it and use the newly created DSN. After it has completed its task then Delete it again. We are using the .Net Framework and either VB.net or C#.net. Any Ideas or suggestion would be appreciated.


Thanks

Thanks yes...I have to use a System DSN...I came just found another example as follows:


using System;
using System.Runtime.InteropServices;

    ///
    /// The ODBC Library class uses the ODBCCP32.dll API to create and remove ODBC System DSN's
    ///
    public class OdbcLib
    {

        #region Private constants
            //Module Constants used in creating DSN's
            private const string ODBC_INI_DSN_KEY_NAME = "DSN=";
            private const string ODBC_INI_FILE_PREFIX_NAME = "\\0File Prefix=";
            private const string ODBC_INI_UID_NAME = "\\0Uid=";
            private const string ODBC_INI_PWD_NAME = "\\0pwd=";
            private const string ODBC_INI_DBQ_NAME = "\\0DBQ=";
            private const string ODBC_INI_ACUODBC_DRIVER = "AcuODBC Driver";    //The Default Driver
            private const string ODBC_INI_READONLY = "\\0Read Only=";
        
        #endregion

        #region Constructors
            ///
            /// The constructor.
            ///
            public OdbcLib()
            {
            }
        #endregion

        #region Public methods
        ///
        /// Creates a User DSN.
        ///
        /// Name of the ODBC Driver
        /// Name of the DSN
        /// Path to database
        /// True if successfull
        public bool AddUserDSN(string OdbcDriverName, string DSName, string DBPath)
        {
            return SQLConfigDataSource((IntPtr)0, 1, OdbcDriverName "\\0",
                ODBC_INI_DSN_KEY_NAME DSName ODBC_INI_UID_NAME "Admin" ODBC_INI_PWD_NAME "" ODBC_INI_DBQ_NAME DBPath "\\0");
        }
        
        ///
        /// Modify a User DSN.
        ///
        /// Name of the ODBC Driver
        /// Name of the DSN
        /// Path to database
        /// True if successfull
        public bool EditUserDSN(string OdbcDriverName, string DSName, string DBPath)
        {
            return SQLConfigDataSource((IntPtr)0, 2, OdbcDriverName "\\0",
                ODBC_INI_DSN_KEY_NAME DSName ODBC_INI_UID_NAME "Admin" ODBC_INI_PWD_NAME "" ODBC_INI_DBQ_NAME DBPath "\\0");

        }

        ///
        /// Remove a User DSN.
        ///
        /// Name of the ODBC Driver
        /// Name of the DSN
        /// True if successfull
        public bool RemoveUserDSN(string OdbcDriverName, string DSName)
        {
            return SQLConfigDataSource((IntPtr)0, 3, OdbcDriverName "\\0",
                ODBC_INI_DSN_KEY_NAME DSName ODBC_INI_UID_NAME "Admin" ODBC_INI_PWD_NAME "" ODBC_INI_DBQ_NAME "\\0");
        }


        ///
        /// Add a System DSN.
        ///
        /// Name of the ODBC Driver
        /// Name of the DSN
        /// FilePrefix
        /// DBQ
        /// True if successfull
        public bool AddSystemDSN(string OdbcDriverName, string DSName, string FilePrefix ,string DBQ)
        {
            return SQLConfigDataSource((IntPtr)0, 4, OdbcDriverName,
                ODBC_INI_DSN_KEY_NAME DSName ODBC_INI_FILE_PREFIX_NAME FilePrefix ODBC_INI_DBQ_NAME DBQ ODBC_INI_READONLY "FALSE" "\\0");

        }

        ///
        /// Add a System DSN.
        ///
        /// Name of the DSN
        /// FilePrefix
        /// DBQ
        /// True if successfull
        public bool AddSystemDSN(string DSName, string FilePrefix ,string DBQ)
        {
            return AddSystemDSN(ODBC_INI_ACUODBC_DRIVER, DSName,FilePrefix,DBQ);

        }

        ///
        /// Modify a System DSN
        ///
        /// Name of the ODBC Driver
        /// Name of DSN
        /// FilePrefix
        /// DBQ
        /// True if successfull
        public bool EditSystemDSN(string OdbcDriverName, string DSName, string FilePrefix ,string DBQ)
        {

            return SQLConfigDataSource((IntPtr)0, 5, ODBC_INI_ACUODBC_DRIVER,
                ODBC_INI_DSN_KEY_NAME DSName ODBC_INI_FILE_PREFIX_NAME FilePrefix ODBC_INI_DBQ_NAME DBQ "\\0");

        }

        ///
        /// Modify a System DSN
        ///
        /// Name of DSN
        /// FilePrefix
        /// DBQ
        /// True if successfull
        public bool EditSystemDSN(string DSName, string FilePrefix ,string DBQ)
        {

            return EditSystemDSN(ODBC_INI_ACUODBC_DRIVER, DSName,FilePrefix, DBQ);

        }
        
        ///
        /// Remove a System DSN
        ///
        /// Name of the ODBC Driver
        /// Name of DSN
        /// True if successfull
        public  bool RemoveSystemDSN(string OdbcDriverName, string DSName)
        {

            return SQLConfigDataSource((IntPtr)0, 6, ODBC_INI_ACUODBC_DRIVER,
                ODBC_INI_DSN_KEY_NAME DSName ODBC_INI_FILE_PREFIX_NAME ODBC_INI_DBQ_NAME "\\0");

        }

        ///
        /// Remove a System DSN
        ///
        /// Name of DSN
        /// True if successfull
        public  bool RemoveSystemDSN(string DSName)
        {

            return RemoveSystemDSN(ODBC_INI_ACUODBC_DRIVER,DSName);

        }
        #endregion

        #region Win32 API Import
        ///
        /// Win32 API Import
        ///
        [DllImport("ODBCCP32.dll")]
        private static extern bool SQLConfigDataSource(IntPtr parent, int request, string driver, string attributes);
        #endregion
    
    }



Thanks

[Migrated content. Thread originally posted on 14 March 2005]

Hello,

I have a situation where I need to create System DSN's using the AccuODBC. I don't need a UI in fact I don't want to use the UI to create the System DSN. I need to have my application that will dynamically create the System dsn base on parameters that are passed to it and use the newly created DSN. After it has completed its task then Delete it again. We are using the .Net Framework and either VB.net or C#.net. Any Ideas or suggestion would be appreciated.


Thanks

Looks good, does it work for you? Hopefully it works correctly with different versions of ODBC.