//******************************************************************************************************//  AdoDataConnection.cs - Gbtc////  Copyright © 2012, Grid Protection Alliance.  All Rights Reserved.////  Licensed to the Grid Protection Alliance (GPA) under one or more contributor license agreements. See//  the NOTICE file distributed with this work for additional information regarding copyright ownership.//  The GPA licenses this file to you under the MIT License (MIT), the "License"; you may//  not use this file except in compliance with the License. You may obtain a copy of the License at:////      http://www.opensource.org/licenses/MIT////  Unless agreed to in writing, the subject software distributed under the License is distributed on an//  "AS-IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. Refer to the//  License for the specific language governing permissions and limitations.////  Code Modification History://  ----------------------------------------------------------------------------------------------------//  04/07/2011 - J. Ritchie Carroll//       Generated original version of source code.//  09/19/2011 - Stephen C. Wills//       Added database awareness and Oracle database compatibility.//  10/18/2011 - J. Ritchie Carroll//       Modified ADO database class to allow directly instantiated instances, as well as configured.//  12/14/2012 - Starlynn Danyelle Gilliam//       Modified Header.////****************************************************************************************************** using System;using System.Collections.Concurrent;using System.Collections.Generic;using System.Data;using System.Diagnostics.CodeAnalysis;using System.IO;using System.Linq;using System.Reflection;using System.Runtime.CompilerServices;using GSF.Annotations;using GSF.Configuration;using GSF.Diagnostics; namespace GSF.Data{    /// <summary>    /// Specifies the database type underlying an <see cref="AdoDataConnection"/>.    /// </summary>    public enum DatabaseType    {        /// <summary>        /// Underlying ADO database type is Microsoft Access.        /// </summary>        Access,         /// <summary>        /// Underlying ADO database type is SQL Server.        /// </summary>        SQLServer,         /// <summary>        /// Underlying ADO database type is MySQL.        /// </summary>        MySQL,         /// <summary>        /// Underlying ADO database type is Oracle.        /// </summary>        Oracle,         /// <summary>        /// Underlying ADO database type is SQLite.        /// </summary>        SQLite,         /// <summary>        /// Underlying ADO database type is PostgreSQL.        /// </summary>        PostgreSQL,         /// <summary>        /// Underlying ADO database type is unknown.        /// </summary>        Other    }     /// <summary>    /// Creates a new <see cref="IDbConnection"/> from any specified or configured ADO.NET data source.    /// </summary>    /// <remarks>    /// Example connection and data provider strings:    /// <list type="table">    ///     <listheader>    ///         <term>Database type</term>    ///         <description>Example connection string / data provider string</description>    ///     </listheader>    ///     <item>    ///         <term>SQL Server</term>    ///         <description>    ///         ConnectionString = "Data Source=serverName; Initial Catalog=databaseName; User ID=userName; Password=password"<br/>    ///         DataProviderString = "AssemblyName={System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089}; ConnectionType=System.Data.SqlClient.SqlConnection; AdapterType=System.Data.SqlClient.SqlDataAdapter"    ///         </description>    ///     </item>    ///     <item>    ///         <term>Oracle</term>    ///         <description>    ///         ConnectionString = "Data Source=tnsName; User ID=schemaUserName; Password=schemaPassword"<br/>    ///         DataProviderString = "AssemblyName={Oracle.DataAccess, Version=2.112.2.0, Culture=neutral, PublicKeyToken=89b483f429c47342}; ConnectionType=Oracle.DataAccess.Client.OracleConnection; AdapterType=Oracle.DataAccess.Client.OracleDataAdapter"    ///         </description>    ///     </item>    ///     <item>    ///         <term>MySQL</term>    ///         <description>    ///         ConnectionString = "Server=serverName; Database=databaseName; Uid=root; Pwd=password; allow user variables = true"<br/>    ///         DataProviderString = "AssemblyName={MySql.Data, Version=6.3.6.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d}; ConnectionType=MySql.Data.MySqlClient.MySqlConnection; AdapterType=MySql.Data.MySqlClient.MySqlDataAdapter"    ///         </description>    ///     </item>    ///     <item>    ///         <term>SQLite</term>    ///         <description>    ///         ConnectionString = "Data Source=databaseName.db; Version=3; Foreign Keys=True; FailIfMissing=True"<br/>    ///         DataProviderString = "AssemblyName={System.Data.SQLite, Version=1.0.109.0, Culture=neutral, PublicKeyToken=db937bc2d44ff139}; ConnectionType=System.Data.SQLite.SQLiteConnection; AdapterType=System.Data.SQLite.SQLiteDataAdapter"    ///         </description>    ///     </item>    ///     <item>    ///         <term>SQLite (Mono native driver)</term>    ///         <description>    ///         ConnectionString = "Data Source=./databaseName.db; Version=3; Foreign Keys=True; FailIfMissing=True"<br/>    ///         DataProviderString = "AssemblyName={Mono.Data.Sqlite, Version=4.0.0.0, Culture=neutral, PublicKeyToken=0738eb9f132ed756}; ConnectionType=Mono.Data.Sqlite.SqliteConnection; AdapterType=Mono.Data.Sqlite.SqliteDataAdapter"    ///         </description>    ///     </item>    ///     <item>    ///         <term>Access / OleDB</term>    ///         <description>    ///         ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=databaseName.mdb"<br/>    ///         DataProviderString = "AssemblyName={System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089}; ConnectionType=System.Data.OleDb.OleDbConnection; AdapterType=System.Data.OleDb.OleDbDataAdapter"    ///         </description>    ///     </item>    ///     <item>    ///         <term>ODBC Connection</term>    ///         <description>    ///         ConnectionString = "Driver={SQL Server Native Client 10.0}; Server=serverName; Database=databaseName; Uid=userName; Pwd=password"<br/>    ///         DataProviderString = "AssemblyName={System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089}; ConnectionType=System.Data.Odbc.OdbcConnection; AdapterType=System.Data.Odbc.OdbcDataAdapter"    ///         </description>    ///     </item>    /// </list>    /// Example configuration file that defines connection settings:    /// <code>    /// <![CDATA[    /// <?xml version="1.0" encoding="utf-8"?>    /// <configuration>    ///   <configSections>    ///     <section name="categorizedSettings" type="GSF.Configuration.CategorizedSettingsSection, GSF.Core" />    ///   </configSections>    ///   <categorizedSettings>    ///     <systemSettings>    ///       <add name="ConnectionString" value="Data Source=localhost\SQLEXPRESS; Initial Catalog=MyDatabase; Integrated Security=SSPI" description="ADO database connection string" encrypted="false" />    ///       <add name="DataProviderString" value="AssemblyName={System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089}; ConnectionType=System.Data.SqlClient.SqlConnection; AdapterType=System.Data.SqlClient.SqlDataAdapter" description="ADO database provider string" encrypted="false" />    ///     </systemSettings>    ///   </categorizedSettings>    ///   <startup>    ///     <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5" />    ///   </startup>    /// </configuration>    /// ]]>    /// </code>    /// </remarks>    public class AdoDataConnection : IDisposable    {        #region [ Members ]         // Fields        private readonly string m_connectionString;        private readonly Type m_connectionType;        private readonly bool m_disposeConnection;        private bool m_disposed;         #endregion         #region [ Constructors ]         /// <summary>        /// Creates and opens a new <see cref="AdoDataConnection"/> based on connection settings in configuration file.        /// </summary>        /// <param name="settingsCategory">Settings category to use for connection settings.</param>        [SuppressMessage("Microsoft.Reliability", "CA2000:Dispose objects before losing scope")]        public AdoDataConnection(string settingsCategory)        {            if (string.IsNullOrWhiteSpace(settingsCategory))                throw new ArgumentNullException(nameof(settingsCategory), "Parameter cannot be null or empty");             // Only need to establish data types and load settings once per defined section since they are being loaded from config file            if (!s_configuredConnections.TryGetValue(settingsCategory, out AdoDataConnection configuredConnection))            {                string connectionString, dataProviderString;                 try                {                    // Load connection settings from the system settings category				                    ConfigurationFile config = ConfigurationFile.Current;                    CategorizedSettingsElementCollection configSettings = config.Settings[settingsCategory];                     connectionString = configSettings["ConnectionString"].Value;                    dataProviderString = configSettings["DataProviderString"].Value;                     if (string.IsNullOrWhiteSpace(connectionString))                        throw new NullReferenceException("ConnectionString setting is not defined in the configuration file.");                     if (string.IsNullOrWhiteSpace(dataProviderString))                        throw new NullReferenceException("DataProviderString setting is not defined in the configuration file.");                }                catch (Exception ex)                {                    throw new InvalidOperationException("Failed to load ADO database connection settings from configuration file: " + ex.Message, ex);                }                 // Define connection settings without opening a connection                configuredConnection = new AdoDataConnection(connectionString, dataProviderString, false);                s_configuredConnections.TryAdd(settingsCategory, configuredConnection);            }             try            {                // Copy static instance data to member variables                DatabaseType = configuredConnection.DatabaseType;                m_connectionString = configuredConnection.m_connectionString;                m_connectionType = configuredConnection.m_connectionType;                AdapterType = configuredConnection.AdapterType;                m_disposeConnection = true;                 // Open ADO.NET provider connection                Connection = (IDbConnection)Activator.CreateInstance(m_connectionType);                Connection.ConnectionString = m_connectionString;                 using (Logger.SuppressFirstChanceExceptionLogMessages())                {                    Connection.Open();                }            }            catch (Exception ex)            {                throw new InvalidOperationException("Failed to open ADO data connection, verify \"ConnectionString\" in configuration file: " + ex.Message, ex);            }        }         /// <summary>        /// Creates and opens a new <see cref="AdoDataConnection"/> from specified <paramref name="connectionString"/> and <paramref name="dataProviderString"/>.        /// </summary>        /// <param name="connectionString">Database specific ADO connection string.</param>        /// <param name="dataProviderString">Key/value pairs that define which ADO assembly and types to load.</param>        public AdoDataConnection(string connectionString, string dataProviderString)            : this(connectionString, dataProviderString, true)        {        }         /// <summary>        /// Creates and opens a new <see cref="AdoDataConnection"/> from specified <paramref name="connectionString"/>,        /// <paramref name="connectionType"/>, and <paramref name="adapterType"/>.        /// </summary>        /// <param name="connectionString">Database specific ADO connection string.</param>        /// <param name="connectionType">The ADO type used to establish the database connection.</param>        /// <param name="adapterType">The ADO type used to load data into <see cref="DataTable"/>s.</param>        public AdoDataConnection(string connectionString, Type connectionType, Type adapterType)        {            if (!typeof(IDbConnection).IsAssignableFrom(connectionType))                throw new ArgumentException("Connection type must implement the IDbConnection interface", nameof(connectionType));             if (!typeof(IDbDataAdapter).IsAssignableFrom(adapterType))                throw new ArgumentException("Adapter type must implement the IDbDataAdapter interface", nameof(adapterType));             m_connectionString = connectionString;            m_connectionType = connectionType;            AdapterType = adapterType;            DatabaseType = GetDatabaseType();            m_disposeConnection = true;             try            {                // Open ADO.NET provider connection                Connection = (IDbConnection)Activator.CreateInstance(m_connectionType);                Connection.ConnectionString = m_connectionString;                 using (Logger.SuppressFirstChanceExceptionLogMessages())                {                    Connection.Open();                }            }            catch (Exception ex)            {                throw new InvalidOperationException("Failed to open ADO data connection, verify \"ConnectionString\": " + ex.Message, ex);            }        }         /// <summary>        /// Creates a new <see cref="AdoDataConnection"/> from specified <paramref name="connection"/>        /// and <paramref name="adapterType"/>.        /// </summary>        /// <param name="connection">The database connection.</param>        /// <param name="adapterType">The ADO type used to load data into <see cref="DataTable"/>s.</param>        /// <param name="disposeConnection">True if the database connection should be closed when the <see cref="AdoDataConnection"/> is disposed; false otherwise.</param>        public AdoDataConnection(IDbConnection connection, Type adapterType, bool disposeConnection)        {            if (!typeof(IDbDataAdapter).IsAssignableFrom(adapterType))                throw new ArgumentException("Adapter type must implement the IDbDataAdapter interface", nameof(adapterType));             Connection = connection;            m_connectionString = connection.ConnectionString;            m_connectionType = connection.GetType();            AdapterType = adapterType;            DatabaseType = GetDatabaseType();            m_disposeConnection = disposeConnection;        }         // Creates a new AdoDataConnection, optionally opening connection.        private AdoDataConnection(string connectionString, string dataProviderString, bool openConnection)        {            if (string.IsNullOrWhiteSpace(connectionString))                throw new ArgumentNullException(nameof(connectionString), "Parameter cannot be null or empty");             if (string.IsNullOrWhiteSpace(dataProviderString))                throw new ArgumentNullException(nameof(dataProviderString), "Parameter cannot be null or empty");             // Cache connection string as member level variable            m_connectionString = connectionString;             try            {                // Attempt to load configuration from an ADO.NET database connection                Dictionary<string, string> settings = dataProviderString.ParseKeyValuePairs();                string assemblyName = settings["AssemblyName"].ToNonNullString();                string connectionTypeName = settings["ConnectionType"].ToNonNullString();                string adapterTypeName = settings["AdapterType"].ToNonNullString();                 if (string.IsNullOrEmpty(connectionTypeName))                    throw new NullReferenceException("ADO database connection type was undefined.");                 if (string.IsNullOrEmpty(adapterTypeName))                    throw new NullReferenceException("ADO database adapter type was undefined.");                 Assembly assembly = Assembly.Load(new AssemblyName(assemblyName));                m_connectionType = assembly.GetType(connectionTypeName);                AdapterType = assembly.GetType(adapterTypeName);                DatabaseType = GetDatabaseType();                m_disposeConnection = true;            }            catch (Exception ex)            {                throw new InvalidOperationException("Failed to load ADO data provider, verify \"DataProviderString\": " + ex.Message, ex);            }             if (!openConnection)                return;             try            {                // Open ADO.NET provider connection                Connection = (IDbConnection)Activator.CreateInstance(m_connectionType);                Connection.ConnectionString = m_connectionString;                 using (Logger.SuppressFirstChanceExceptionLogMessages())                {                    Connection.Open();                }            }            catch (Exception ex)            {                throw new InvalidOperationException("Failed to open ADO data connection, verify \"ConnectionString\": " + ex.Message, ex);            }        }         /// <summary>        /// Releases the unmanaged resources before the <see cref="AdoDataConnection"/> object is reclaimed by <see cref="GC"/>.        /// </summary>        ~AdoDataConnection()        {            Dispose(false);        }         #endregion         #region [ Properties ]         /// <summary>        /// Gets an open <see cref="IDbConnection"/> to configured ADO.NET data source.        /// </summary>        public IDbConnection  Connection { get; private set; }         /// <summary>        /// Gets the type of data adapter for configured ADO.NET data source.        /// </summary>        public Type AdapterType { get; }         /// <summary>        /// Gets or sets the type of the database underlying the <see cref="AdoDataConnection"/>.        /// </summary>        /// <remarks>        /// This value is automatically assigned based on the adapter type specified in the data provider string, however,        /// if the database type cannot be determined it will be set to <see cref="Data.DatabaseType.Other"/>. In this        /// case, if you know the behavior of your custom ADO database connection matches that of another defined database        /// type, you can manually assign the database type to allow for database interaction interoperability.        /// </remarks>        public DatabaseType DatabaseType { get; set; }         /// <summary>        /// Gets or sets default timeout for <see cref="AdoDataConnection"/> data operations.        /// </summary>        public int DefaultTimeout { get; set; } = DataExtensions.DefaultTimeoutDuration;         /// <summary>        /// Gets current UTC date-time in an implementation that is proper for the connected <see cref="AdoDataConnection"/> database type.        /// </summary>        public object UtcNow        {            get            {                if (IsJetEngine)                    return DateTime.UtcNow.ToOADate();                 return IsSqlite ?                     new DateTime(DateTime.UtcNow.Ticks, DateTimeKind.Unspecified) :                    DateTime.UtcNow;            }        }         /// <summary>        /// Gets the default <see cref="IsolationLevel"/> for the connected <see cref=" AdoDataConnection"/> database type.        /// </summary>        public IsolationLevel DefaultIsolationLevel => IsSQLServer ? IsolationLevel.ReadUncommitted : IsolationLevel.Unspecified;         /// <summary>        /// Gets a value to indicate whether source database is Microsoft Access.        /// </summary>        public bool IsJetEngine => DatabaseType == DatabaseType.Access;         /// <summary>        /// Gets a value to indicate whether source database is Microsoft SQL Server.        /// </summary>        public bool IsSQLServer => DatabaseType == DatabaseType.SQLServer;         /// <summary>        /// Gets a value to indicate whether source database is MySQL.        /// </summary>        public bool IsMySQL => DatabaseType == DatabaseType.MySQL;         /// <summary>        /// Gets a value to indicate whether source database is Oracle.        /// </summary>        public bool IsOracle => DatabaseType == DatabaseType.Oracle;         /// <summary>        /// Gets a value to indicate whether source database is SQLite.        /// </summary>        public bool IsSqlite => DatabaseType == DatabaseType.SQLite;         /// <summary>        /// Gets a value to indicate whether source database is PostgreSQL.        /// </summary>        public bool IsPostgreSQL => DatabaseType == DatabaseType.PostgreSQL;         #endregion         #region [ Methods ]         /// <summary>        /// Executes the given SQL script using <see cref="Connection"/>.        /// </summary>        /// <param name="scriptPath">The path to the SQL script.</param>        public void ExecuteScript(string scriptPath)        {            using TextReader scriptReader = File.OpenText(scriptPath);            ExecuteScript(scriptReader);        }         /// <summary>        /// Executes the given SQL script using <see cref="Connection"/>.        /// </summary>        /// <param name="scriptReader">The reader used to extract SQL statements to be executed.</param>        public void ExecuteScript(TextReader scriptReader)        {            if (Connection is null)                throw new NullReferenceException("Cannot execute script, no connection established.");             // ReSharper disable once SwitchStatementHandlesSomeKnownEnumValuesWithDefault            switch (DatabaseType)            {                case DatabaseType.SQLServer:                    // For the standard way to execute a TSQL script, refer to the following.                    //                    // http://stackoverflow.com/questions/650098/how-to-execute-an-sql-script-file-using-c-sharp                    //                    // This solution was not used here because useLegacyV2RuntimeActivationPolicy                    // needs to be added to the app.config file for it to work. This is not                    // ideal for a core library since it has no control over an application's                    // config, and it can make no assertion about the flag's suitability to the                    // application. For more information, refer to the following.                    //                    // http://web.archive.org/web/20130128072944/http://www.marklio.com/marklio/PermaLink,guid,ecc34c3c-be44-4422-86b7-900900e451f9.aspx                    Connection.ExecuteTSQLScript(scriptReader);                    break;                 case DatabaseType.Oracle:

Lines 1–500 of 1075. Load more