public void TestGetStoredProcedureDoesNotLoadFunction()
{
string createProcedureStatement = GetProcedureStatement(this.connectionType);
string createFunctionStatement = GetFunctionStatement(this.connectionType);
DatabaseObjectCreation.CreateTestTable("CREATE TABLE TestTable ( IntValue INT )", this.connectionType);
DatabaseObjectCreation.CreateTestProc(createProcedureStatement, this.connectionType);
DatabaseObjectCreation.CreateTestFunction(createFunctionStatement, this.connectionType);
try
{
DatabaseAssistant databaseAssistant = DatabaseAssistant.GetDatabaseAssistant(this.connectionType);
using (var connection = databaseAssistant.CreateConnection(DatabaseHelpers.GetDefaultConnectionString(this.connectionType)))
{
connection.Open();
Assert.IsTrue(databaseAssistant.GetStoredProcedureNames(connection).Any(p => p.Contains("Test Proc")));
Assert.IsFalse(databaseAssistant.GetStoredProcedureNames(connection).Any(p => p.Contains("TestFunction")));
}
}
finally
{
DatabaseObjectCreation.RemoveTestProc(this.connectionType);
DatabaseObjectCreation.RemoveTestFunction(this.connectionType);
DatabaseObjectCreation.RemoveTestTable(this.connectionType);
}
}
public static DatabaseModel.ProcedureParameters CreateTestProc(string createProcedureStatement, ConnectionType connectionType)
{
try
{
RemoveTestProc(connectionType);
}
catch { }
try
{
DatabaseHelpers.ExecuteSqlStatement(createProcedureStatement, connectionType);
DatabaseAssistant databaseAssistant = DatabaseAssistant.GetDatabaseAssistant(connectionType);
using (var connection = databaseAssistant.CreateConnection(DatabaseHelpers.GetDefaultConnectionString(connectionType)))
{
connection.Open();
return(new DatabaseModel.ProcedureParameters(databaseAssistant.GetStoredProcedureParameters(connection, "Test Proc")));
}
}
catch
{
try
{
RemoveTestProc(connectionType);
}
catch { }
throw;
}
}
public void TestGetResultSetsForStoredProcedureWithDynamicSQL()
{
string createProcedureStatement = GetProcedureWithDynamicSQL(this.connectionType);
DatabaseObjectCreation.CreateTestProc(createProcedureStatement, this.connectionType);
DatabaseModel.ResultSets resultSets = null;
try
{
DatabaseAssistant databaseAssistant = DatabaseAssistant.GetDatabaseAssistant(this.connectionType);
using (var connection = databaseAssistant.CreateConnection(DatabaseHelpers.GetDefaultConnectionString(this.connectionType)))
{
connection.Open();
Assert.IsTrue(databaseAssistant.GetStoredProcedureNames(connection).Any(p => p.Contains("Test Proc")));
resultSets = databaseAssistant.GetResultSets(connection, "Test Proc");
}
}
finally
{
DatabaseObjectCreation.RemoveTestProc(this.connectionType);
}
Assert.AreEqual(1, resultSets.Count);
var fields = resultSets[0].Fields;
Assert.AreEqual(1, fields.Count);
var field = fields[0];
Assert.AreEqual("Rowcount_Returned", field.ColumnName);
Assert.AreEqual("Rowcount_Returned", field.OutputName);
}
public static DatabaseModel.Columns CreateTestTable(string createTableStatement, ConnectionType connectionType)
{
try
{
RemoveTestTable(connectionType);
}
catch { }
try
{
DatabaseHelpers.ExecuteSqlStatement(createTableStatement, connectionType);
DatabaseAssistant databaseAssistant = DatabaseAssistant.GetDatabaseAssistant(connectionType);
using (var connection = databaseAssistant.CreateConnection(DatabaseHelpers.GetDefaultConnectionString(connectionType)))
{
connection.Open();
return(new DatabaseModel.Columns(databaseAssistant.GetTableColumns(connection, "TESTTABLE")));
}
}
catch
{
try
{
RemoveTestTable(connectionType);
}
catch { }
throw;
}
}
internal static DbCommand CreateCommand(ConnectionType connectionType, string connectionString, string sql, object[] sqlValues, int timeout)
{
var assistant = DatabaseAssistant.GetDatabaseAssistant(connectionType);
var connection = assistant.CreateConnection(connectionString);
return(CreateCommand(connectionType, connection, sql, sqlValues, timeout));
}
private void TestConnection()
{
IsBusy = true;
Task.Factory.StartNew(() =>
{
try
{
using (IDbConnection connection = DatabaseAssistant.GetDatabaseAssistant(connectionType).CreateConnection(ConnectionString))
{
connection.Open();
}
if (ShowMessage != null)
{
Application.Current.Dispatcher.Invoke(() => ShowMessage(this, new ShowMessageEventArgs("Success!", "Connect to database", MessageBoxButton.OK, MessageBoxImage.Information)));
}
}
catch (Exception exc)
{
if (ShowMessage != null)
{
Application.Current.Dispatcher.Invoke(() => ShowMessage(this, new ShowMessageEventArgs(exc.Message, "Error connecting", MessageBoxButton.OK, MessageBoxImage.Error)));
}
}
finally
{
IsBusy = false;
}
});
}
public void RefreshConnectionParameters(bool retainUnknownParameters = false)
{
foreach (var nextParameter in ConnectionParameters)
{
if (parameterValueCache.ContainsKey(nextParameter.Name.ToLower()))
{
parameterValueCache[nextParameter.Name.ToLower()] = nextParameter.TextValue;
}
else
{
parameterValueCache.Add(nextParameter.Name.ToLower(), nextParameter.TextValue);
}
}
var databaseAssistant = DatabaseAssistant.GetDatabaseAssistant(connectionType);
var convertedParameters = databaseAssistant.RefreshConnectionParameters(ConnectionParameters.Where(p => !p.IsDefault).ToLookup(p => p.Name, p => p.TextValue), retainUnknownParameters);
ConnectionParameters = new ObservableCollection <ConnectionParameter>(convertedParameters.Select(p => new ConnectionParameter(this, p.Key, p.Value)));
foreach (var nextParameter in ConnectionParameters)
{
if ((parameterValueCache.ContainsKey(nextParameter.Name.ToLower())) && (nextParameter.TextValue == nextParameter.DefaultTextValue))
{
string cachedValue = parameterValueCache[nextParameter.Name.ToLower()];
if (!string.IsNullOrEmpty(cachedValue))
{
nextParameter.TextValue = cachedValue;
}
}
}
}
private static DatabaseModel.ResultSets FetchResultSets(ConnectionType connectionType)
{
var databaseAssistant = DatabaseAssistant.GetDatabaseAssistant(connectionType);
using (var connection = databaseAssistant.CreateConnection(DatabaseHelpers.GetDefaultConnectionString(connectionType)))
{
connection.Open();
return(databaseAssistant.GetResultSets(connection, "Test Proc"));
}
}
private ObservableCollection <NodeViewModel> GetDatabaseAssistantObjectsAsync()
{
ObservableCollection <NodeViewModel> databaseObjects = new ObservableCollection <NodeViewModel>();
IDbConnection connection = null;
try
{
var assistant = DatabaseAssistant.GetDatabaseAssistant(this.connectionType);
connection = assistant.CreateConnection(this.connectionString);
connection.Open();
var tableContainer = new ComplexNodeViewModel("Tables");
databaseObjects.Add(tableContainer);
foreach (var tableName in assistant.GetTableNames(connection).OrderBy(t => t))
{
tableContainer.Children.Add(new TableNodeViewModel(this.connectionType, this.connectionString, tableName, tableContainer, SetSqlAction, true));
}
var viewContainer = new ComplexNodeViewModel("Views");
databaseObjects.Add(viewContainer);
foreach (var viewName in assistant.GetViewNames(connection).OrderBy(v => v))
{
viewContainer.Children.Add(new TableNodeViewModel(this.connectionType, this.connectionString, viewName, viewContainer, null, false));
}
if (this.connectionType != ConnectionType.Oracle)
{
var storedProcedureContainer = new ComplexNodeViewModel("Stored Procedures");
databaseObjects.Add(storedProcedureContainer);
foreach (var spName in assistant.GetStoredProcedureNames(connection).OrderBy(sp => sp))
{
storedProcedureContainer.Children.Add(new StoredProcedureNodeViewModel(assistant, connection, spName, storedProcedureContainer));
}
}
}
catch (Exception exception)
{
databaseObjects.Clear();
databaseObjects.Add(new SimpleNodeViewModel(exception.Message));
}
finally
{
if (connection != null)
{
connection.Close();
connection.Dispose();
}
}
return(databaseObjects);
}
public void TestTableData()
{
var columns = DatabaseObjectCreation.CreateTestTable(this.connectionType);
DatabaseAssistant databaseAssistant = DatabaseAssistant.GetDatabaseAssistant(this.connectionType);
try
{
using (var connection = databaseAssistant.CreateConnection(DatabaseHelpers.GetDefaultConnectionString(this.connectionType)))
{
connection.Open();
Assert.IsTrue(databaseAssistant.GetTableNames(connection).Any(t => t.IndexOf("TestTable", StringComparison.InvariantCultureIgnoreCase) != -1));
}
}
finally
{
DatabaseObjectCreation.RemoveTestTable(this.connectionType);
}
Assert.AreEqual(5, columns.Count);
var column = columns[0];
Assert.AreEqual("INTVALUE", column.Name.ToUpper());
Assert.AreEqual(DatabaseModel.DataType.Int32, column.DataType);
Assert.IsFalse(column.IsNullable);
column = columns[1];
Assert.AreEqual("DOUBLEVALUE", column.Name.ToUpper());
Assert.AreEqual(DatabaseModel.DataType.Double, column.DataType);
Assert.IsTrue(column.IsNullable);
column = columns[2];
Assert.AreEqual("STRINGVALUE", column.Name.ToUpper());
Assert.AreEqual(DatabaseModel.DataType.String, column.DataType);
Assert.IsTrue(column.IsNullable);
column = columns[3];
Assert.AreEqual("DATEVALUE", column.Name.ToUpper());
Assert.AreEqual(this.connectionType == ConnectionType.OleDb ? DatabaseModel.DataType.String : DatabaseModel.DataType.Date,
column.DataType);
Assert.IsTrue(column.IsNullable);
column = columns[4];
Assert.AreEqual("BYTESVALUE", column.Name.ToUpper());
Assert.AreEqual(DatabaseModel.DataType.Binary, column.DataType);
Assert.IsTrue(column.IsNullable);
}
private IDbCommand CreateCommand()
{
var databaseAssistant = DatabaseAssistant.GetDatabaseAssistant(connectionType);
if (transaction == null)
{
IDbConnection connection = databaseAssistant.CreateConnection(connectionString);
Log(string.Format("Opening connection <{0}>", connectionString));
connection.Open();
Log("Opened connection.");
return(databaseAssistant.BuildStoredProcedureCommand(connection, storedProcedureName, parameters));
}
else
{
IDbCommand command = databaseAssistant.BuildStoredProcedureCommand(transaction.Connection, storedProcedureName, parameters);
command.Transaction = transaction;
return(command);
}
}
private FunctionResult Execute(DbBulkCopy.ConnectionType connectionType, string connectionString, string tableName)
{
DatabaseModel.Columns columns;
var databaseAssistant = DatabaseAssistant.GetDatabaseAssistant(ToCommonConnectionType(connectionType));
using (var connection = databaseAssistant.CreateConnection(connectionString))
{
connection.Open();
columns = new DatabaseModel.Columns(databaseAssistant.GetTableColumns(connection, tableName));
}
var tester = (new FunctionTester <DbBulkCopy.DbBulkCopy>()).Compile(
new PropertyValue(DbBulkCopyShared.ConnectionTypePropertyName, connectionType),
new PropertyValue(DbBulkCopyShared.ColumnsPropertyName, columns),
new PropertyValue(DbBulkCopyShared.TableNamePropertyName, tableName));
return(tester.Execute(
new ParameterValue(DbShared.ConnectionStringPropertyName, connectionString)));
}
public void TestGetResultSetForStoredProcedureDataDoesNotRunProcedure()
{
string createProcedureStatement = GetProcedureStatement(this.connectionType);
DatabaseObjectCreation.CreateTestTable("CREATE TABLE TestTable ( IntValue INT )", this.connectionType);
DatabaseObjectCreation.CreateTestProc(createProcedureStatement, this.connectionType);
DatabaseModel.ResultSets resultSets;
try
{
DatabaseAssistant databaseAssistant = DatabaseAssistant.GetDatabaseAssistant(this.connectionType);
using (var connection = databaseAssistant.CreateConnection(DatabaseHelpers.GetDefaultConnectionString(this.connectionType)))
{
connection.Open();
Assert.IsTrue(databaseAssistant.GetStoredProcedureNames(connection).Any(p => p.Contains("Test Proc")));
resultSets = databaseAssistant.GetResultSets(connection, "Test Proc");
}
DataTable results = DatabaseHelpers.GetDataTable("SELECT * FROM TestTable", this.connectionType);
Assert.AreEqual(0, results.Rows.Count);
}
finally
{
DatabaseObjectCreation.RemoveTestProc(this.connectionType);
DatabaseObjectCreation.RemoveTestTable(this.connectionType);
}
Assert.AreEqual(1, resultSets.Count);
var fields = resultSets[0].Fields;
Assert.AreEqual(1, fields.Count);
var field = fields[0];
Assert.AreEqual("StringValue", field.ColumnName);
Assert.AreEqual("StringValue", field.OutputName);
}
private bool GetCachedTableColumns(out DatabaseModel.Column[] columns, out string error)
{
if (this.cachedTableColumns == null)
{
try
{
var assistant = DatabaseAssistant.GetDatabaseAssistant(this.connectionType);
using (IDbConnection connection = assistant.CreateConnection(this.connectionString))
{
connection.Open();
this.cachedTableColumns = assistant.GetTableColumns(connection, Text).ToArray();
}
}
catch (Exception exception)
{
this.cachedTableColumns = new DatabaseModel.Column[0];
this.cachedTableColumnsError = exception.Message;
}
}
columns = this.cachedTableColumns;
error = this.cachedTableColumnsError;
return(string.IsNullOrEmpty(this.cachedTableColumnsError));
}
public void TestGetResultSetsForStoredProcedure()
{
var parameters = DatabaseObjectCreation.CreateParameterAndResultSetsTestProc(this.connectionType).Where(p => (p.Direction != DatabaseModel.ParameterDirection.ReturnValue) && (p.DataType != DatabaseModel.DataType.RefCursor)).ToList();
DatabaseModel.ResultSets resultSets;
DatabaseAssistant databaseAssistant = DatabaseAssistant.GetDatabaseAssistant(this.connectionType);
try
{
using (var connection = databaseAssistant.CreateConnection(DatabaseHelpers.GetDefaultConnectionString(this.connectionType)))
{
connection.Open();
Assert.IsTrue(databaseAssistant.GetStoredProcedureNames(connection).Any(p => p.Contains("Test Proc")));
resultSets = databaseAssistant.GetResultSets(connection, "Test Proc");
}
}
finally
{
DatabaseObjectCreation.RemoveTestProc(this.connectionType);
}
Assert.AreEqual(8, parameters.Count);
var parameter = parameters[0];
Assert.AreEqual("@IntValue", parameter.Name);
Assert.AreEqual(DatabaseModel.ParameterDirection.In, parameter.Direction);
Assert.AreEqual(DatabaseModel.DataType.Int32, parameter.DataType);
parameter = parameters[1];
Assert.AreEqual("@DoubleValue", parameter.Name);
Assert.AreEqual(DatabaseModel.ParameterDirection.In, parameter.Direction);
Assert.AreEqual(DatabaseModel.DataType.Double, parameter.DataType);
parameter = parameters[2];
Assert.AreEqual("@StringValue", parameter.Name);
Assert.AreEqual(DatabaseModel.ParameterDirection.In, parameter.Direction);
Assert.AreEqual(DatabaseModel.DataType.String, parameter.DataType);
if (this.connectionType != ConnectionType.Oracle)
{
Assert.AreEqual(20, parameter.Size);
}
parameter = parameters[3];
Assert.AreEqual("@StringValueOut", parameter.Name);
Assert.AreEqual(DatabaseModel.ParameterDirection.InOut, parameter.Direction);
Assert.AreEqual(DatabaseModel.DataType.String, parameter.DataType);
if (this.connectionType != ConnectionType.Oracle)
{
Assert.AreEqual(20, parameter.Size);
}
parameter = parameters[4];
Assert.AreEqual("@DateValue", parameter.Name);
Assert.AreEqual(DatabaseModel.ParameterDirection.In, parameter.Direction);
Assert.AreEqual(DatabaseModel.DataType.Date, parameter.DataType);
parameter = parameters[5];
Assert.AreEqual("@DateValueOut", parameter.Name);
Assert.AreEqual(DatabaseModel.ParameterDirection.InOut, parameter.Direction);
Assert.AreEqual(DatabaseModel.DataType.Date, parameter.DataType);
parameter = parameters[6];
Assert.AreEqual("@BytesValue", parameter.Name);
Assert.AreEqual(DatabaseModel.ParameterDirection.In, parameter.Direction);
Assert.AreEqual(DatabaseModel.DataType.Binary, parameter.DataType);
if (this.connectionType != ConnectionType.Oracle)
{
Assert.AreEqual(50, parameter.Size);
}
parameter = parameters[7];
Assert.AreEqual("@BytesValueOut", parameter.Name);
Assert.AreEqual(DatabaseModel.ParameterDirection.InOut, parameter.Direction);
Assert.AreEqual(DatabaseModel.DataType.Binary, parameter.DataType);
if (this.connectionType != ConnectionType.Oracle)
{
Assert.AreEqual(50, parameter.Size);
}
Assert.AreEqual(2, resultSets.Count);
var fields = resultSets[0].Fields;
Assert.AreEqual(1, fields.Count);
var field = fields[0];
Assert.AreEqual("StringValue", field.ColumnName);
Assert.AreEqual("StringValue", field.OutputName);
fields = resultSets[1].Fields;
Assert.AreEqual(2, fields.Count);
field = fields[0];
Assert.AreEqual("Counter", field.ColumnName);
Assert.AreEqual("Counter", field.OutputName);
field = fields[1];
Assert.AreEqual("StringValue", field.ColumnName);
Assert.AreEqual("StringValue", field.OutputName);
Assert.AreEqual(DatabaseModel.DataType.String, field.DataType);
}