public DbCommand CreateCommand()
{
try
{
DbCommand cmd = null;
cmd = Conn.CreateCommand();
return(cmd);
}
catch (Exception)
{
throw;
}
}
/// <summary>
/// Logs an error to the database.
/// </summary>
/// <remarks>
/// Use the stored procedure called by this implementation to set a
/// policy on how long errors are kept in the log. The default
/// implementation stores all errors for an indefinite time.
/// </remarks>
public override string Log(Error error)
{
if (error == null)
{
throw new ArgumentNullException("error");
}
string errorXml = ErrorXml.EncodeString(error);
using (VistaDBConnection connection = new VistaDBConnection(this.ConnectionString))
using (VistaDBCommand command = connection.CreateCommand())
{
connection.Open();
command.CommandText = @"INSERT INTO ELMAH_Error
(Application, Host, Type, Source,
Message, [User], AllXml, StatusCode, TimeUtc)
VALUES
(@Application, @Host, @Type, @Source,
@Message, @User, @AllXml, @StatusCode, @TimeUtc);
SELECT @@IDENTITY";
command.CommandType = CommandType.Text;
VistaDBParameterCollection parameters = command.Parameters;
parameters.Add("@Application", VistaDBType.NVarChar, _maxAppNameLength).Value = ApplicationName;
parameters.Add("@Host", VistaDBType.NVarChar, 30).Value = error.HostName;
parameters.Add("@Type", VistaDBType.NVarChar, 100).Value = error.Type;
parameters.Add("@Source", VistaDBType.NVarChar, 60).Value = error.Source;
parameters.Add("@Message", VistaDBType.NVarChar, 500).Value = error.Message;
parameters.Add("@User", VistaDBType.NVarChar, 50).Value = error.User;
parameters.Add("@AllXml", VistaDBType.NText).Value = errorXml;
parameters.Add("@StatusCode", VistaDBType.Int).Value = error.StatusCode;
parameters.Add("@TimeUtc", VistaDBType.DateTime).Value = error.Time.ToUniversalTime();
return(Convert.ToString(command.ExecuteScalar(), CultureInfo.InvariantCulture));
}
}
public void CreateStoredProcedures()
{
try
{
using (var cn = new VistaDBConnection(Properties.Settings.Default.ConnectionString))
{
cn.Open();
using (var cmd = cn.CreateCommand())
{
foreach (
var sql in
Regex.Split(Properties.Resources.DatabaseReset, @"^\s*GO\s*$", RegexOptions.Multiline))
{
cmd.CommandText = sql;
cmd.ExecuteNonQuery();
}
}
}
}
catch (Exception ex)
{
Trace.WriteLine(ex.Message);
}
}
private void InitializeDatabase()
{
string connectionString = ConnectionString;
Debug.AssertStringNotEmpty(connectionString);
if (File.Exists(_databasePath))
return;
//
// Make sure that we don't have multiple threads all trying to create the database
//
lock (_lock)
{
//
// Just double check that no other thread has created the database while
// we were waiting for the lock
//
if (File.Exists(_databasePath))
return;
VistaDBConnectionStringBuilder builder = new VistaDBConnectionStringBuilder(connectionString);
using (VistaDBConnection connection = new VistaDBConnection())
using (VistaDBCommand command = connection.CreateCommand())
{
string passwordClause = string.Empty;
if (!string.IsNullOrEmpty(builder.Password))
passwordClause = " PASSWORD '" + EscapeApostrophes(builder.Password) + "',";
// create the database using the webserver's default locale
command.CommandText = "CREATE DATABASE '" + EscapeApostrophes(_databasePath) + "'" + passwordClause + ", PAGE SIZE 1, CASE SENSITIVE FALSE;";
command.ExecuteNonQuery();
const string ddlScript = @"
CREATE TABLE [ELMAH_Error]
(
[ErrorId] INT NOT NULL,
[Application] NVARCHAR (60) NOT NULL,
[Host] NVARCHAR (50) NOT NULL,
[Type] NVARCHAR (100) NOT NULL,
[Source] NVARCHAR (60) NOT NULL,
[Message] NVARCHAR (500) NOT NULL,
[User] NVARCHAR (50) NOT NULL,
[StatusCode] INT NOT NULL,
[TimeUtc] DATETIME NOT NULL,
[AllXml] NTEXT NOT NULL,
CONSTRAINT [PK_ELMAH_Error] PRIMARY KEY ([ErrorId])
)
GO
ALTER TABLE [ELMAH_Error]
ALTER COLUMN [ErrorId] INT NOT NULL IDENTITY (1, 1)
GO
CREATE INDEX [IX_ELMAH_Error_App_Time_Id] ON [ELMAH_Error] ([TimeUtc] DESC, [ErrorId] DESC)";
foreach (string batch in ScriptToBatches(ddlScript))
{
command.CommandText = batch;
command.ExecuteNonQuery();
}
}
}
}
/// <summary>
/// Returns the specified error from the database, or null
/// if it does not exist.
/// </summary>
public override ErrorLogEntry GetError(string id)
{
if (id == null)
throw new ArgumentNullException("id");
if (id.Length == 0)
throw new ArgumentException(null, "id");
int errorId;
try
{
errorId = int.Parse(id, CultureInfo.InvariantCulture);
}
catch (FormatException e)
{
throw new ArgumentException(e.Message, "id", e);
}
catch (OverflowException e)
{
throw new ArgumentException(e.Message, "id", e);
}
string errorXml;
using (VistaDBConnection connection = new VistaDBConnection(this.ConnectionString))
using (VistaDBCommand command = connection.CreateCommand())
{
command.CommandText = @"SELECT AllXml
FROM ELMAH_Error
WHERE ErrorId = @ErrorId";
command.CommandType = CommandType.Text;
VistaDBParameterCollection parameters = command.Parameters;
parameters.Add("@ErrorId", VistaDBType.Int).Value = errorId;
connection.Open();
// NB this has been deliberately done like this as command.ExecuteScalar
// is not exhibiting the expected behaviour in VistaDB at the moment
using (VistaDBDataReader dr = command.ExecuteReader())
{
if (dr.Read())
errorXml = dr[0] as string;
else
errorXml = null;
}
}
if (errorXml == null)
return null;
Error error = ErrorXml.DecodeString(errorXml);
return new ErrorLogEntry(this, id, error);
}
/// <summary>
/// Logs an error to the database.
/// </summary>
/// <remarks>
/// Use the stored procedure called by this implementation to set a
/// policy on how long errors are kept in the log. The default
/// implementation stores all errors for an indefinite time.
/// </remarks>
public override string Log(Error error)
{
if (error == null)
throw new ArgumentNullException("error");
string errorXml = ErrorXml.EncodeString(error);
using (VistaDBConnection connection = new VistaDBConnection(this.ConnectionString))
using (VistaDBCommand command = connection.CreateCommand())
{
connection.Open();
command.CommandText = @"INSERT INTO ELMAH_Error
(Application, Host, Type, Source,
Message, [User], AllXml, StatusCode, TimeUtc)
VALUES
(@Application, @Host, @Type, @Source,
@Message, @User, @AllXml, @StatusCode, @TimeUtc);
SELECT @@IDENTITY";
command.CommandType = CommandType.Text;
VistaDBParameterCollection parameters = command.Parameters;
parameters.Add("@Application", VistaDBType.NVarChar, _maxAppNameLength).Value = ApplicationName;
parameters.Add("@Host", VistaDBType.NVarChar, 30).Value = error.HostName;
parameters.Add("@Type", VistaDBType.NVarChar, 100).Value = error.Type;
parameters.Add("@Source", VistaDBType.NVarChar, 60).Value = error.Source;
parameters.Add("@Message", VistaDBType.NVarChar, 500).Value = error.Message;
parameters.Add("@User", VistaDBType.NVarChar, 50).Value = error.User;
parameters.Add("@AllXml", VistaDBType.NText).Value = errorXml;
parameters.Add("@StatusCode", VistaDBType.Int).Value = error.StatusCode;
parameters.Add("@TimeUtc", VistaDBType.DateTime).Value = error.Time.ToUniversalTime();
return Convert.ToString(command.ExecuteScalar(), CultureInfo.InvariantCulture);
}
}
public void ParseVistaDB(string vistaTableName, string qualifiedTableName, DataTable dataTable)
{
Program objProgram = new Program();
//Create a connection to VistaDB
string connectionString = @"Data Source=C:\superpay\Superpay4.vdb6;Open Mode=SingleProcessReadWrite";
//Create string connection to Oracle
string conString = "User Id=dwrrhh; password=milenaok;" +
//How to connect to an Oracle DB without SQL*Net configuration file
//also known as tnsnames.ora.
"Data Source=cocgsa016.cupagroup.com:1521/CUPIRE; Pooling=false;";
try
{
//Read data from VistaDb and create variable with Values
using (VistaDBConnection dbConn = new VistaDBConnection(connectionString))
{
dbConn.Open();
VistaDBCommand command = dbConn.CreateCommand();
command.CommandText = "SELECT * FROM " + vistaTableName;
using (VistaDBDataReader dr = command.ExecuteReader())
{
int count = 0;
while (dr.Read())
{
count++;
DataRow dataRow = dataTable.NewRow();
for (int i = 0; dataTable.Columns.Count > i; i++)
{
string colName = dataTable.Columns[i].ColumnName.ToString();
int colLength = dr[colName].ToString().Length;
if (colLength == 0)
{
dataRow[colName] = 0.0;
}
else
{
string s1 = dr[colName].ToString();
string s2 = s1.Replace(",", ".");
dataRow[colName] = s2;
}
//Console.WriteLine("Columna Nº: " + count + "-- Nombre COL: " + colName + " -- Valor: " + dataRow[colName]);
}
dataTable.Rows.Add(dataRow);
}
// Send parse data to Oracle
objProgram.WriteToOracle(qualifiedTableName, conString, dataTable);
}
// Just a little pause for viewing
//Console.ReadLine();
}
}
catch (Exception e)
{
Console.WriteLine("Error encountered during parsed data from VistaDB table name "
+ vistaTableName + " con error " + e.Message);
throw;
}
finally
{
Console.WriteLine("Parse data from Table " + vistaTableName + " of VistaDb was successfully");
}
}
private void InitializeDatabase()
{
string connectionString = ConnectionString;
Debug.AssertStringNotEmpty(connectionString);
if (File.Exists(_databasePath))
{
return;
}
//
// Make sure that we don't have multiple threads all trying to create the database
//
lock (_lock)
{
//
// Just double check that no other thread has created the database while
// we were waiting for the lock
//
if (File.Exists(_databasePath))
{
return;
}
VistaDBConnectionStringBuilder builder = new VistaDBConnectionStringBuilder(connectionString);
using (VistaDBConnection connection = new VistaDBConnection())
using (VistaDBCommand command = connection.CreateCommand())
{
string passwordClause = string.Empty;
if (!string.IsNullOrEmpty(builder.Password))
{
passwordClause = " PASSWORD '" + EscapeApostrophes(builder.Password) + "',";
}
// create the database using the webserver's default locale
command.CommandText = "CREATE DATABASE '" + EscapeApostrophes(_databasePath) + "'" + passwordClause + ", PAGE SIZE 1, CASE SENSITIVE FALSE;";
command.ExecuteNonQuery();
const string ddlScript = @"
CREATE TABLE [ELMAH_Error]
(
[ErrorId] INT NOT NULL,
[Application] NVARCHAR (60) NOT NULL,
[Host] NVARCHAR (50) NOT NULL,
[Type] NVARCHAR (100) NOT NULL,
[Source] NVARCHAR (60) NOT NULL,
[Message] NVARCHAR (500) NOT NULL,
[User] NVARCHAR (50) NOT NULL,
[StatusCode] INT NOT NULL,
[TimeUtc] DATETIME NOT NULL,
[AllXml] NTEXT NOT NULL,
CONSTRAINT [PK_ELMAH_Error] PRIMARY KEY ([ErrorId])
)
GO
ALTER TABLE [ELMAH_Error]
ALTER COLUMN [ErrorId] INT NOT NULL IDENTITY (1, 1)
GO
CREATE INDEX [IX_ELMAH_Error_App_Time_Id] ON [ELMAH_Error] ([TimeUtc] DESC, [ErrorId] DESC)";
foreach (string batch in ScriptToBatches(ddlScript))
{
command.CommandText = batch;
command.ExecuteNonQuery();
}
}
}
}
/// <summary>
/// Returns the specified error from the database, or null
/// if it does not exist.
/// </summary>
public override ErrorLogEntry GetError(string id)
{
if (id == null)
{
throw new ArgumentNullException("id");
}
if (id.Length == 0)
{
throw new ArgumentException(null, "id");
}
int errorId;
try
{
errorId = int.Parse(id, CultureInfo.InvariantCulture);
}
catch (FormatException e)
{
throw new ArgumentException(e.Message, "id", e);
}
catch (OverflowException e)
{
throw new ArgumentException(e.Message, "id", e);
}
string errorXml;
using (VistaDBConnection connection = new VistaDBConnection(this.ConnectionString))
using (VistaDBCommand command = connection.CreateCommand())
{
command.CommandText = @"SELECT AllXml
FROM ELMAH_Error
WHERE ErrorId = @ErrorId";
command.CommandType = CommandType.Text;
VistaDBParameterCollection parameters = command.Parameters;
parameters.Add("@ErrorId", VistaDBType.Int).Value = errorId;
connection.Open();
// NB this has been deliberately done like this as command.ExecuteScalar
// is not exhibiting the expected behaviour in VistaDB at the moment
using (VistaDBDataReader dr = command.ExecuteReader())
{
if (dr.Read())
{
errorXml = dr[0] as string;
}
else
{
errorXml = null;
}
}
}
if (errorXml == null)
{
return(null);
}
Error error = ErrorXml.DecodeString(errorXml);
return(new ErrorLogEntry(this, id, error));
}
