#region Apache License
//
// Licensed to the Apache Software Foundation (ASF) under one or more
// contributor license agreements. See the NOTICE file distributed with
// this work for additional information regarding copyright ownership.
// The ASF licenses this file to you under the Apache License, Version 2.0
// (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.apache.org/licenses/LICENSE-2.0
//
// Unless required by applicable law or agreed to in writing, software
// distributed under the License is distributed on an "AS IS" BASIS,
// WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
// See the License for the specific language governing permissions and
// limitations under the License.
//
#endregion
// SSCLI 1.0 has no support for ADO.NET
#if !SSCLI
using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.IO;
using log4net.Util;
using log4net.Layout;
using log4net.Core;
namespace log4net.Appender
{
///
/// Appender that logs to a database.
///
///
///
/// appends logging events to a table within a
/// database. The appender can be configured to specify the connection
/// string by setting the property.
/// The connection type (provider) can be specified by setting the
/// property. For more information on database connection strings for
/// your specific database see http://www.connectionstrings.com/.
///
///
/// Records are written into the database either using a prepared
/// statement or a stored procedure. The property
/// is set to (System.Data.CommandType.Text) to specify a prepared statement
/// or to (System.Data.CommandType.StoredProcedure) to specify a stored
/// procedure.
///
///
/// The prepared statement text or the name of the stored procedure
/// must be set in the property.
///
///
/// The prepared statement or stored procedure can take a number
/// of parameters. Parameters are added using the
/// method. This adds a single to the
/// ordered list of parameters. The
/// type may be subclassed if required to provide database specific
/// functionality. The specifies
/// the parameter name, database type, size, and how the value should
/// be generated using a .
///
///
///
/// An example of a SQL Server table that could be logged to:
///
/// CREATE TABLE [dbo].[Log] (
/// [ID] [int] IDENTITY (1, 1) NOT NULL ,
/// [Date] [datetime] NOT NULL ,
/// [Thread] [varchar] (255) NOT NULL ,
/// [Level] [varchar] (20) NOT NULL ,
/// [Logger] [varchar] (255) NOT NULL ,
/// [Message] [varchar] (4000) NOT NULL
/// ) ON [PRIMARY]
///
///
///
/// An example configuration to log to the above table:
///
///
///
///
///
///
///
///
///
///
///
///
///
///
///
///
///
///
///
///
///
///
///
///
///
///
///
///
///
///
///
///
///
///
///
///
///
/// Julian Biddle
/// Nicko Cadell
/// Gert Driesen
/// Lance Nehring
public class AdoNetAppender : BufferingAppenderSkeleton
{
#region Public Instance Constructors
///
/// Initializes a new instance of the class.
///
///
/// Public default constructor to initialize a new instance of this class.
///
public AdoNetAppender()
{
ConnectionType = "System.Data.OleDb.OleDbConnection, System.Data, Version=1.0.3300.0, Culture=neutral, PublicKeyToken=b77a5c561934e089";
UseTransactions = true;
CommandType = System.Data.CommandType.Text;
m_parameters = new ArrayList();
ReconnectOnError = false;
}
#endregion // Public Instance Constructors
#region Public Instance Properties
///
/// Gets or sets the database connection string that is used to connect to
/// the database.
///
///
/// The database connection string used to connect to the database.
///
///
///
/// The connections string is specific to the connection type.
/// See for more information.
///
///
/// Connection string for MS Access via ODBC:
/// "DSN=MS Access Database;UID=admin;PWD=;SystemDB=C:\data\System.mdw;SafeTransactions = 0;FIL=MS Access;DriverID = 25;DBQ=C:\data\train33.mdb"
///
/// Another connection string for MS Access via ODBC:
/// "Driver={Microsoft Access Driver (*.mdb)};DBQ=C:\Work\cvs_root\log4net-1.2\access.mdb;UID=;PWD=;"
///
/// Connection string for MS Access via OLE DB:
/// "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Work\cvs_root\log4net-1.2\access.mdb;User Id=;Password=;"
///
public string ConnectionString
{
get { return m_connectionString; }
set { m_connectionString = value; }
}
///
/// The appSettings key from App.Config that contains the connection string.
///
public string AppSettingsKey
{
get { return m_appSettingsKey; }
set { m_appSettingsKey = value; }
}
#if NET_2_0
///
/// The connectionStrings key from App.Config that contains the connection string.
///
///
/// This property requires at least .NET 2.0.
///
public string ConnectionStringName
{
get { return m_connectionStringName; }
set { m_connectionStringName = value; }
}
#endif
///
/// Gets or sets the type name of the connection
/// that should be created.
///
///
/// The type name of the connection.
///
///
///
/// The type name of the ADO.NET provider to use.
///
///
/// The default is to use the OLE DB provider.
///
///
/// Use the OLE DB Provider. This is the default value.
/// System.Data.OleDb.OleDbConnection, System.Data, Version=1.0.3300.0, Culture=neutral, PublicKeyToken=b77a5c561934e089
///
/// Use the MS SQL Server Provider.
/// System.Data.SqlClient.SqlConnection, System.Data, Version=1.0.3300.0, Culture=neutral, PublicKeyToken=b77a5c561934e089
///
/// Use the ODBC Provider.
/// Microsoft.Data.Odbc.OdbcConnection,Microsoft.Data.Odbc,version=1.0.3300.0,publicKeyToken=b77a5c561934e089,culture=neutral
/// This is an optional package that you can download from
/// http://msdn.microsoft.com/downloads
/// search for ODBC .NET Data Provider.
///
/// Use the Oracle Provider.
/// System.Data.OracleClient.OracleConnection, System.Data.OracleClient, Version=1.0.3300.0, Culture=neutral, PublicKeyToken=b77a5c561934e089
/// This is an optional package that you can download from
/// http://msdn.microsoft.com/downloads
/// search for .NET Managed Provider for Oracle.
///
public string ConnectionType
{
get { return m_connectionType; }
set { m_connectionType = value; }
}
///
/// Gets or sets the command text that is used to insert logging events
/// into the database.
///
///
/// The command text used to insert logging events into the database.
///
///
///
/// Either the text of the prepared statement or the
/// name of the stored procedure to execute to write into
/// the database.
///
///
/// The property determines if
/// this text is a prepared statement or a stored procedure.
///
///
/// If this property is not set, the command text is retrieved by invoking
/// .
///
///
public string CommandText
{
get { return m_commandText; }
set { m_commandText = value; }
}
///
/// Gets or sets the command type to execute.
///
///
/// The command type to execute.
///
///
///
/// This value may be either (System.Data.CommandType.Text) to specify
/// that the is a prepared statement to execute,
/// or (System.Data.CommandType.StoredProcedure) to specify that the
/// property is the name of a stored procedure
/// to execute.
///
///
/// The default value is (System.Data.CommandType.Text).
///
///
public CommandType CommandType
{
get { return m_commandType; }
set { m_commandType = value; }
}
///
/// Should transactions be used to insert logging events in the database.
///
///
/// true if transactions should be used to insert logging events in
/// the database, otherwise false. The default value is true.
///
///
///
/// Gets or sets a value that indicates whether transactions should be used
/// to insert logging events in the database.
///
///
/// When set a single transaction will be used to insert the buffered events
/// into the database. Otherwise each event will be inserted without using
/// an explicit transaction.
///
///
public bool UseTransactions
{
get { return m_useTransactions; }
set { m_useTransactions = value; }
}
///
/// Gets or sets the used to call the NetSend method.
///
///
/// The used to call the NetSend method.
///
///
///
/// Unless a specified here for this appender
/// the is queried for the
/// security context to use. The default behavior is to use the security context
/// of the current thread.
///
///
public SecurityContext SecurityContext
{
get { return m_securityContext; }
set { m_securityContext = value; }
}
///
/// Should this appender try to reconnect to the database on error.
///
///
/// true if the appender should try to reconnect to the database after an
/// error has occurred, otherwise false. The default value is false,
/// i.e. not to try to reconnect.
///
///
///
/// The default behaviour is for the appender not to try to reconnect to the
/// database if an error occurs. Subsequent logging events are discarded.
///
///
/// To force the appender to attempt to reconnect to the database set this
/// property to true.
///
///
/// When the appender attempts to connect to the database there may be a
/// delay of up to the connection timeout specified in the connection string.
/// This delay will block the calling application's thread.
/// Until the connection can be reestablished this potential delay may occur multiple times.
///
///
public bool ReconnectOnError
{
get { return m_reconnectOnError; }
set { m_reconnectOnError = value; }
}
#endregion // Public Instance Properties
#region Protected Instance Properties
///
/// Gets or sets the underlying .
///
///
/// The underlying .
///
///
/// creates a to insert
/// logging events into a database. Classes deriving from
/// can use this property to get or set this . Use the
/// underlying returned from if
/// you require access beyond that which provides.
///
protected IDbConnection Connection
{
get { return m_dbConnection; }
set { m_dbConnection = value; }
}
#endregion // Protected Instance Properties
#region Implementation of IOptionHandler
///
/// Initialize the appender based on the options set
///
///
///
/// This is part of the delayed object
/// activation scheme. The method must
/// be called on this object after the configuration properties have
/// been set. Until is called this
/// object is in an undefined state and must not be used.
///
///
/// If any of the configuration properties are modified then
/// must be called again.
///
///
override public void ActivateOptions()
{
base.ActivateOptions();
if (SecurityContext == null)
{
SecurityContext = SecurityContextProvider.DefaultProvider.CreateSecurityContext(this);
}
InitializeDatabaseConnection();
}
#endregion
#region Override implementation of AppenderSkeleton
///
/// Override the parent method to close the database
///
///
///
/// Closes the database command and database connection.
///
///
override protected void OnClose()
{
base.OnClose();
DiposeConnection();
}
#endregion
#region Override implementation of BufferingAppenderSkeleton
///
/// Inserts the events into the database.
///
/// The events to insert into the database.
///
///
/// Insert all the events specified in the
/// array into the database.
///
///
override protected void SendBuffer(LoggingEvent[] events)
{
if (ReconnectOnError && (Connection == null || Connection.State != ConnectionState.Open))
{
LogLog.Debug(declaringType, "Attempting to reconnect to database. Current Connection State: " + ((Connection == null) ? SystemInfo.NullText : Connection.State.ToString()));
InitializeDatabaseConnection();
}
// Check that the connection exists and is open
if (Connection != null && Connection.State == ConnectionState.Open)
{
if (UseTransactions)
{
// Create transaction
// NJC - Do this on 2 lines because it can confuse the debugger
using (IDbTransaction dbTran = Connection.BeginTransaction())
{
try
{
SendBuffer(dbTran, events);
// commit transaction
dbTran.Commit();
}
catch (Exception ex)
{
// rollback the transaction
try
{
dbTran.Rollback();
}
catch (Exception)
{
// Ignore exception
}
// Can't insert into the database. That's a bad thing
ErrorHandler.Error("Exception while writing to database", ex);
}
}
}
else
{
// Send without transaction
SendBuffer(null, events);
}
}
}
#endregion // Override implementation of BufferingAppenderSkeleton
#region Public Instance Methods
///
/// Adds a parameter to the command.
///
/// The parameter to add to the command.
///
///
/// Adds a parameter to the ordered list of command parameters.
///
///
public void AddParameter(AdoNetAppenderParameter parameter)
{
m_parameters.Add(parameter);
}
#endregion // Public Instance Methods
#region Protected Instance Methods
///
/// Writes the events to the database using the transaction specified.
///
/// The transaction that the events will be executed under.
/// The array of events to insert into the database.
///
///
/// The transaction argument can be null if the appender has been
/// configured not to use transactions. See
/// property for more information.
///
///
virtual protected void SendBuffer(IDbTransaction dbTran, LoggingEvent[] events)
{
// string.IsNotNullOrWhiteSpace() does not exist in ancient .NET frameworks
if (CommandText != null && CommandText.Trim() != "")
{
using (IDbCommand dbCmd = Connection.CreateCommand())
{
// Set the command string
dbCmd.CommandText = CommandText;
// Set the command type
dbCmd.CommandType = CommandType;
// Send buffer using the prepared command object
if (dbTran != null)
{
dbCmd.Transaction = dbTran;
}
// prepare the command, which is significantly faster
dbCmd.Prepare();
// run for all events
foreach (LoggingEvent e in events)
{
// clear parameters that have been set
dbCmd.Parameters.Clear();
// Set the parameter values
foreach (AdoNetAppenderParameter param in m_parameters)
{
param.Prepare(dbCmd);
param.FormatValue(dbCmd, e);
}
// Execute the query
dbCmd.ExecuteNonQuery();
}
}
}
else
{
// create a new command
using (IDbCommand dbCmd = Connection.CreateCommand())
{
if (dbTran != null)
{
dbCmd.Transaction = dbTran;
}
// run for all events
foreach (LoggingEvent e in events)
{
// Get the command text from the Layout
string logStatement = GetLogStatement(e);
LogLog.Debug(declaringType, "LogStatement [" + logStatement + "]");
dbCmd.CommandText = logStatement;
dbCmd.ExecuteNonQuery();
}
}
}
}
///
/// Formats the log message into database statement text.
///
/// The event being logged.
///
/// This method can be overridden by subclasses to provide
/// more control over the format of the database statement.
///
///
/// Text that can be passed to a .
///
virtual protected string GetLogStatement(LoggingEvent logEvent)
{
if (Layout == null)
{
ErrorHandler.Error("AdoNetAppender: No Layout specified.");
return "";
}
else
{
StringWriter writer = new StringWriter(System.Globalization.CultureInfo.InvariantCulture);
Layout.Format(writer, logEvent);
return writer.ToString();
}
}
///
/// Creates an instance used to connect to the database.
///
///
/// This method is called whenever a new IDbConnection is needed (i.e. when a reconnect is necessary).
///
/// The of the object.
/// The connectionString output from the ResolveConnectionString method.
/// An instance with a valid connection string.
virtual protected IDbConnection CreateConnection(Type connectionType, string connectionString)
{
IDbConnection connection = (IDbConnection)Activator.CreateInstance(connectionType);
connection.ConnectionString = connectionString;
return connection;
}
///
/// Resolves the connection string from the ConnectionString, ConnectionStringName, or AppSettingsKey
/// property.
///
///
/// ConnectiongStringName is only supported on .NET 2.0 and higher.
///
/// Additional information describing the connection string.
/// A connection string used to connect to the database.
virtual protected string ResolveConnectionString(out string connectionStringContext)
{
if (ConnectionString != null && ConnectionString.Length > 0)
{
connectionStringContext = "ConnectionString";
return ConnectionString;
}
#if NET_2_0
if (!String.IsNullOrEmpty(ConnectionStringName))
{
ConnectionStringSettings settings = ConfigurationManager.ConnectionStrings[ConnectionStringName];
if (settings != null)
{
connectionStringContext = "ConnectionStringName";
return settings.ConnectionString;
}
else
{
throw new LogException("Unable to find [" + ConnectionStringName + "] ConfigurationManager.ConnectionStrings item");
}
}
#endif
if (AppSettingsKey != null && AppSettingsKey.Length > 0)
{
connectionStringContext = "AppSettingsKey";
string appSettingsConnectionString = SystemInfo.GetAppSetting(AppSettingsKey);
if (appSettingsConnectionString == null || appSettingsConnectionString.Length == 0)
{
throw new LogException("Unable to find [" + AppSettingsKey + "] AppSettings key.");
}
return appSettingsConnectionString;
}
connectionStringContext = "Unable to resolve connection string from ConnectionString, ConnectionStrings, or AppSettings.";
return string.Empty;
}
///
/// Retrieves the class type of the ADO.NET provider.
///
///
///
/// Gets the Type of the ADO.NET provider to use to connect to the
/// database. This method resolves the type specified in the
/// property.
///
///
/// Subclasses can override this method to return a different type
/// if necessary.
///
///
/// The of the ADO.NET provider
virtual protected Type ResolveConnectionType()
{
try
{
return SystemInfo.GetTypeFromString(ConnectionType, true, false);
}
catch (Exception ex)
{
ErrorHandler.Error("Failed to load connection type [" + ConnectionType + "]", ex);
throw;
}
}
#endregion // Protected Instance Methods
#region Private Instance Methods
///
/// Connects to the database.
///
private void InitializeDatabaseConnection()
{
string connectionStringContext = "Unable to determine connection string context.";
string resolvedConnectionString = string.Empty;
try
{
DiposeConnection();
// Set the connection string
resolvedConnectionString = ResolveConnectionString(out connectionStringContext);
Connection = CreateConnection(ResolveConnectionType(), resolvedConnectionString);
using (SecurityContext.Impersonate(this))
{
// Open the database connection
Connection.Open();
}
}
catch (Exception e)
{
// Sadly, your connection string is bad.
ErrorHandler.Error("Could not open database connection [" + resolvedConnectionString + "]. Connection string context [" + connectionStringContext + "].", e);
Connection = null;
}
}
///
/// Cleanup the existing connection.
///
///
/// Calls the IDbConnection's method.
///
private void DiposeConnection()
{
if (Connection != null)
{
try
{
Connection.Close();
}
catch (Exception ex)
{
LogLog.Warn(declaringType, "Exception while disposing cached connection object", ex);
}
Connection = null;
}
}
#endregion // Private Instance Methods
#region Protected Instance Fields
///
/// The list of objects.
///
///
///
/// The list of objects.
///
///
protected ArrayList m_parameters;
#endregion // Protected Instance Fields
#region Private Instance Fields
///
/// The security context to use for privileged calls
///
private SecurityContext m_securityContext;
///
/// The that will be used
/// to insert logging events into a database.
///
private IDbConnection m_dbConnection;
///
/// Database connection string.
///
private string m_connectionString;
///
/// The appSettings key from App.Config that contains the connection string.
///
private string m_appSettingsKey;
#if NET_2_0
///
/// The connectionStrings key from App.Config that contains the connection string.
///
private string m_connectionStringName;
#endif
///
/// String type name of the type name.
///
private string m_connectionType;
///
/// The text of the command.
///
private string m_commandText;
///
/// The command type.
///
private CommandType m_commandType;
///
/// Indicates whether to use transactions when writing to the database.
///
private bool m_useTransactions;
///
/// Indicates whether to reconnect when a connection is lost.
///
private bool m_reconnectOnError;
#endregion // Private Instance Fields
#region Private Static Fields
///
/// The fully qualified type of the AdoNetAppender class.
///
///
/// Used by the internal logger to record the Type of the
/// log message.
///
private readonly static Type declaringType = typeof(AdoNetAppender);
#endregion Private Static Fields
}
///
/// Parameter type used by the .
///
///
///
/// This class provides the basic database parameter properties
/// as defined by the interface.
///
/// This type can be subclassed to provide database specific
/// functionality. The two methods that are called externally are
/// and .
///
///
public class AdoNetAppenderParameter
{
#region Public Instance Constructors
///
/// Initializes a new instance of the class.
///
///
/// Default constructor for the AdoNetAppenderParameter class.
///
public AdoNetAppenderParameter()
{
Precision = 0;
Scale = 0;
Size = 0;
}
#endregion // Public Instance Constructors
#region Public Instance Properties
///
/// Gets or sets the name of this parameter.
///
///
/// The name of this parameter.
///
///
///
/// The name of this parameter. The parameter name
/// must match up to a named parameter to the SQL stored procedure
/// or prepared statement.
///
///
public string ParameterName
{
get { return m_parameterName; }
set { m_parameterName = value; }
}
///
/// Gets or sets the database type for this parameter.
///
///
/// The database type for this parameter.
///
///
///
/// The database type for this parameter. This property should
/// be set to the database type from the
/// enumeration. See .
///
///
/// This property is optional. If not specified the ADO.NET provider
/// will attempt to infer the type from the value.
///
///
///
public DbType DbType
{
get { return m_dbType; }
set
{
m_dbType = value;
m_inferType = false;
}
}
///
/// Gets or sets the precision for this parameter.
///
///
/// The precision for this parameter.
///
///
///
/// The maximum number of digits used to represent the Value.
///
///
/// This property is optional. If not specified the ADO.NET provider
/// will attempt to infer the precision from the value.
///
///
///
public byte Precision
{
get { return m_precision; }
set { m_precision = value; }
}
///
/// Gets or sets the scale for this parameter.
///
///
/// The scale for this parameter.
///
///
///
/// The number of decimal places to which Value is resolved.
///
///
/// This property is optional. If not specified the ADO.NET provider
/// will attempt to infer the scale from the value.
///
///
///
public byte Scale
{
get { return m_scale; }
set { m_scale = value; }
}
///
/// Gets or sets the size for this parameter.
///
///
/// The size for this parameter.
///
///
///
/// The maximum size, in bytes, of the data within the column.
///
///
/// This property is optional. If not specified the ADO.NET provider
/// will attempt to infer the size from the value.
///
///
/// For BLOB data types like VARCHAR(max) it may be impossible to infer the value automatically, use -1 as the size in this case.
///
///
///
public int Size
{
get { return m_size; }
set { m_size = value; }
}
///
/// Gets or sets the to use to
/// render the logging event into an object for this
/// parameter.
///
///
/// The used to render the
/// logging event into an object for this parameter.
///
///
///
/// The that renders the value for this
/// parameter.
///
///
/// The can be used to adapt
/// any into a
/// for use in the property.
///
///
public IRawLayout Layout
{
get { return m_layout; }
set { m_layout = value; }
}
#endregion // Public Instance Properties
#region Public Instance Methods
///
/// Prepare the specified database command object.
///
/// The command to prepare.
///
///
/// Prepares the database command object by adding
/// this parameter to its collection of parameters.
///
///
virtual public void Prepare(IDbCommand command)
{
// Create a new parameter
IDbDataParameter param = command.CreateParameter();
// Set the parameter properties
param.ParameterName = ParameterName;
if (!m_inferType)
{
param.DbType = DbType;
}
if (Precision != 0)
{
param.Precision = Precision;
}
if (Scale != 0)
{
param.Scale = Scale;
}
if (Size != 0)
{
param.Size = Size;
}
// Add the parameter to the collection of params
command.Parameters.Add(param);
}
///
/// Renders the logging event and set the parameter value in the command.
///
/// The command containing the parameter.
/// The event to be rendered.
///
///
/// Renders the logging event using this parameters layout
/// object. Sets the value of the parameter on the command object.
///
///
virtual public void FormatValue(IDbCommand command, LoggingEvent loggingEvent)
{
// Lookup the parameter
IDbDataParameter param = (IDbDataParameter)command.Parameters[ParameterName];
// Format the value
object formattedValue = Layout.Format(loggingEvent);
// If the value is null then convert to a DBNull
if (formattedValue == null)
{
formattedValue = DBNull.Value;
}
param.Value = formattedValue;
}
#endregion // Public Instance Methods
#region Private Instance Fields
///
/// The name of this parameter.
///
private string m_parameterName;
///
/// The database type for this parameter.
///
private DbType m_dbType;
///
/// Flag to infer type rather than use the DbType
///
private bool m_inferType = true;
///
/// The precision for this parameter.
///
private byte m_precision;
///
/// The scale for this parameter.
///
private byte m_scale;
///
/// The size for this parameter.
///
private int m_size;
///
/// The to use to render the
/// logging event into an object for this parameter.
///
private IRawLayout m_layout;
#endregion // Private Instance Fields
}
}
#endif // !SSCLI