private void OnExecute(Document document)
{
ResultSets.Clear();
try
{
IEnumerable <DataTable> results;
if (string.IsNullOrEmpty(ActiveDocumentSelectedText))
{
results = _sqliteService.ExecuteQuery(_activeDocument.Text, _activeDocument.DatabasePath);
}
else
{
results = _sqliteService.ExecuteQuery(ActiveDocumentSelectedText, _activeDocument.DatabasePath);
}
foreach (DataTable table in results)
{
ResultSets.Add(table);
}
}
catch (Exception ex)
{
WpfMessageBox.ShowDialog("Execution Error", ex.Message, MessageBoxButton.OK, MessageIcon.Error);
}
}
/// <summary>
/// Call a stored procedure and get the results back.
/// </summary>
/// <param name="instance">Database Context to use for the call.</param>
/// <param name="storedProcedureName">The name of the stored procedure to execute.</param>
/// <param name="token">Cancellation token (optional).</param>
/// <param name="inputParameters">An instance of an <see cref="Object"/> containing data to be sent to the stored procedure (INPUT and OUTPUT parameters).</param>
/// <param name="commandTimeout">The wait time before terminating the attempt to execute a command and generating an error.</param>
/// <param name="returnedTypes">The data types of resultsets returned by the stored procedure. Order is important.</param>
/// <returns>A list of lists containing result data from the stored procedure.</returns>
/// <exception cref="ArgumentNullException"><paramref name="instance"/> is NULL or <paramref name="storedProcedureName"/> is NULL.</exception>
/// <exception cref="ArgumentException"><paramref name="storedProcedureName"/> is empty or whitespace.</exception>
/// <exception cref="Exception">An <see cref="Exception"/> occured while reading the result from the stored procedure.</exception>
internal static async Task <ResultSets> ReadFromStoredProcedureAsync(this DbContext instance, string storedProcedureName, CancellationToken token, IEnumerable <SqlParameter> inputParameters = null, int?commandTimeout = null, params Type[] returnedTypes)
{
if (instance == null)
{
throw new ArgumentNullException(nameof(instance));
}
if (storedProcedureName == null)
{
throw new ArgumentNullException(nameof(storedProcedureName));
}
if (String.IsNullOrWhiteSpace(storedProcedureName))
{
throw new ArgumentException(nameof(storedProcedureName));
}
var isConnectionOpen = false;
var results = new ResultSets();
var currentType = (returnedTypes == null) ? new Type[0].GetEnumerator() : returnedTypes.GetEnumerator();
var connection = (SqlConnection)instance.Database.GetDbConnection();
try
{
if (connection.State != ConnectionState.Open)
{
connection.Open();
isConnectionOpen = true;
}
using (var sqlCommand = connection.CreateCommand())
{
sqlCommand.CommandText = storedProcedureName;
sqlCommand.CommandType = CommandType.StoredProcedure;
sqlCommand.CommandTimeout = commandTimeout ?? sqlCommand.CommandTimeout;
// Add input parameters
if (inputParameters != null)
{
foreach (var inputParameter in inputParameters)
{
sqlCommand.Parameters.Add(inputParameter);
}
}
// Execute the stored procedure
var sqlDataReader = await sqlCommand.ExecuteReaderAsync(CommandBehavior.SequentialAccess, token);
// Get the type we're expecting for the first result. If no types specified, ignore all results.
if (currentType.MoveNext())
{
#region Process results
// Repeat this loop for each result set returned by the stored procedure for which we have a result type specified.
do
{
var mappedProperties = ((Type)currentType.Current).GetMappedProperties().ToList(); // Get properties to save for the current destination type.
var currentResult = new List <object>(); // Create a destination for our results.
while (sqlDataReader.Read())
{
// Create an object to hold this result.
var constructorInfo = ((Type)currentType.Current).GetConstructor(Type.EmptyTypes);
if (constructorInfo != null)
{
var item = constructorInfo.Invoke(new object[0]);
sqlDataReader.ReadRecord(item, mappedProperties.ToArray()); // Copy data elements by parameter name from result to destination object.
currentResult.Add(item); // Add newly populated item to our output list.
}
}
// Add this result set to our return list.
results.Add(currentResult);
}while (sqlDataReader.NextResult() && currentType.MoveNext());
#endregion
}
// If we opened the reader, then close up the reader, we're done saving results.
if (isConnectionOpen)
{
sqlDataReader.Close();
}
}
}
catch (Exception ex)
{
throw new Exception(String.Format("Error reading from stored procedure {0}: {1}", storedProcedureName, ex.Message), ex);
}
finally
{
connection.Close();
}
return(results);
}
void IXmlSerializable.ReadXml(XmlReader reader)
{
(_xmlSettings as IXmlSerializable).ReadXml(reader);
int depth1 = reader.Depth + 1;
if (reader.ReadToFirstChildElement())
{
while (reader.Depth >= depth1)
{
if (reader.NodeType == XmlNodeType.Element && reader.Depth == depth1)
{
switch (reader.Name)
{
case "ResultSets":
if (ResultSets == null)
{
ResultSets = new List <IList <BindableDynamicObject> >();
}
else if (ResultSets.Count > 0)
{
ResultSets.Clear();
}
int depthResultSet = reader.Depth + 1;
if (reader.ReadToFirstChildElement())
{
while (reader.Depth >= depthResultSet)
{
if (reader.NodeType == XmlNodeType.Element && reader.Depth == depthResultSet && reader.Name == "ResultSet")
{
List <BindableDynamicObject> resultSet = new List <BindableDynamicObject>();
int depthRecord = reader.Depth + 1;
if (reader.ReadToFirstChildElement())
{
while (reader.Depth >= depthRecord)
{
if (reader.NodeType == XmlNodeType.Element && reader.Depth == depthRecord && reader.Name == "Record")
{
resultSet.Add(reader.ReadDynamicObject(_xmlSettings));
}
else
{
reader.Read();
}
}
}
ResultSets.Add(resultSet);
}
else
{
reader.Read();
}
}
}
break;
case "OutputParameters":
OutputParameters = reader.ReadDynamicObject(_xmlSettings);
break;
case "ReturnValue":
ReturnValue = reader.ReadValue(_xmlSettings);
break;
default:
reader.Read();
break;
}
}
else
{
reader.Read();
}
}
}
}
/// <summary>
/// Saves pending changes
/// </summary>
/// <param name="acceptOption">Defines when changes should be accepted locally</param>
/// <param name="clearErrors">If set to true, all error entries are cleared before saving chnages</param>
/// <param name="failOnValidationErrors">If set to true, exception is thrown if IsValid=false, otherwise invalid entities are skipped from saving silently</param>
/// <param name="clientInfo">Optional client info object, to be submitted to the server</param>
public override void SaveChanges(AcceptOption acceptOption = AcceptOption.Default, bool clearErrors = true, bool failOnValidationErrors = true, ClientInfo clientInfo = null)
{
if (acceptOption == AcceptOption.Default)
{
acceptOption = System.Transactions.Transaction.Current != null ? AcceptOption.AcceptChangesOnTransactionCompleted : AcceptOption.AcceptChangesAfterSave;
}
if (clearErrors)
{
ClearErrors();
}
if (failOnValidationErrors && !IsValid)
{
throw new ValidationException("Validation failed for one or more entities.");
}
// submit data
TResultSet resultSet;
try
{
resultSet = SubmitChanges(clientInfo ?? ClientInfo);
}
catch (FaultException ex)
{
var faultExceptionType = ex.GetType();
if (faultExceptionType.IsGenericType)
{
var detailType = faultExceptionType.GetGenericArguments()[0];
var detailProperty = typeof(FaultException <>).MakeGenericType(detailType).GetProperty("Detail");
var detail = detailProperty.GetValue(ex, null);
if (detail is OptimisticConcurrencyFault)
{
var stateEntries = GetStateEntries(((OptimisticConcurrencyFault)detail).Entities);
throw new OptimisticConcurrencyException(null, stateEntries);
}
if (detail is UpdateFault)
{
var stateEntries = GetStateEntries(((UpdateFault)detail).Entities);
throw new UpdateException(null, stateEntries);
}
}
throw;
}
// accept changes
switch (acceptOption)
{
case AcceptOption.AcceptChangesAfterSave:
{
// refresh local data
var hasValidationErrors = IsServerValidationExceptionSuppressed ? false : resultSet.Any(entity => entity.Errors.Any(e => e.IsError));
Refresh(resultSet);
if (hasValidationErrors)
{
throw new ServerValidationException();
}
AcceptChanges(true);
}
break;
case AcceptOption.None:
{
// store result in private list
// allowes user to apply result (accept changes) asynchronousely
ResultSets.Add(resultSet);
// remove result from private list in case of a transaction rollback
var transaction = System.Transactions.Transaction.Current;
if (transaction != null)
{
transaction.TransactionCompleted += (sender, e) =>
{
if (e.Transaction.TransactionInformation.Status != TransactionStatus.Committed)
{
ResultSets.Remove(resultSet);
}
};
}
}
break;
case AcceptOption.AcceptChangesOnTransactionCompleted:
{
var transaction = System.Transactions.Transaction.Current;
if (transaction == null)
{
throw new Exception(string.Format("{0}.{1} requires an active transaction scope.", acceptOption.GetType().Name, acceptOption));
}
// accept changes upon successfull completion of transaction
transaction.TransactionCompleted += (sender, e) =>
{
if (e.Transaction.TransactionInformation.Status == TransactionStatus.Committed)
{
// refresh local data
var hasValidationErrors = IsServerValidationExceptionSuppressed ? false : resultSet.Any(entity => entity.Errors.Any(error => error.IsError));
Refresh(resultSet);
if (hasValidationErrors)
{
throw new ServerValidationException();
}
}
};
}
break;
default:
throw new Exception(string.Format("This {0} is not implemented: {1}", acceptOption.GetType().Name, acceptOption));
}
//if (resultSet.HasConcurrencyConflicts)
//{
// HandleConcurrencyConflicts(resultSet);
//}
}
public string GenerateScript(ScriptingOptions so)
{
StringBuilder sql = new StringBuilder();
int resultSetCount = 0;
if (so.PartitionFunctions)
{
sql.Append(GetResourceScript("ObjectHelper.SQL.PartitionFunctions.sql"));
sql.AppendLine();
ResultSets.Add("PartitionFunctionCollection", resultSetCount++);
ResultSets.Add("PartitionFunctionRangeValuesCollection", resultSetCount++);
}
if (so.Tables)
{
sql.Append("SELECT COUNT(*) FROM sys.tables;");
sql.AppendLine();
ResultSets.Add("TableCount", resultSetCount++);
sql.Append(GetResourceScript("ObjectHelper.SQL.Tables_" + so.ServerMajorVersion.ToString() + ".sql"));
sql.AppendLine();
ResultSets.Add("TableCollection", resultSetCount++);
if (so.DataCompression)
{
if (so.ServerMajorVersion >= 10)
{
sql.Append(GetResourceScript("ObjectHelper.SQL.TableDataCompression_" + so.ServerMajorVersion.ToString() + ".sql"));
sql.AppendLine();
ResultSets.Add("TableDataCompressionCollection", resultSetCount++);
}
else
{
so.DataCompression = false;
}
}
if (so.DefaultConstraints)
{
sql.Append(GetResourceScript("ObjectHelper.SQL.DefaultConstraints.sql"));
sql.AppendLine();
ResultSets.Add("DefaultConstraintCollection", resultSetCount++);
}
if (so.CheckConstraints)
{
sql.Append(GetResourceScript("ObjectHelper.SQL.CheckConstraints.sql"));
sql.AppendLine();
ResultSets.Add("CheckConstraintCollection", resultSetCount++);
}
if (so.ForeignKeys)
{
sql.Append(GetResourceScript("ObjectHelper.SQL.ForeignKeys.sql"));
sql.AppendLine();
ResultSets.Add("ForeignKeyCollection", resultSetCount++);
ResultSets.Add("ForeignKeyColumnCollection", resultSetCount++);
}
if (so.FullTextIndexes)
{
sql.Append(GetResourceScript("ObjectHelper.SQL.FullTextIndexes_" + so.ServerMajorVersion + ".sql"));
sql.AppendLine();
ResultSets.Add("FullTextIndexCollection", resultSetCount++);
ResultSets.Add("FullTextIndexColumnCollection", resultSetCount++);
}
sql.Append(GetResourceScript("ObjectHelper.SQL.Columns_" + so.ServerMajorVersion.ToString() + ".sql"));
sql.AppendLine();
ResultSets.Add("ColumnCollection", resultSetCount++);
}
if (so.Contracts)
{
sql.Append(GetResourceScript("ObjectHelper.SQL.Contracts.sql"));
sql.AppendLine();
ResultSets.Add("ContractCollection", resultSetCount++);
ResultSets.Add("ContractMessageTypeCollection", resultSetCount++);
}
if (so.MessageTypes)
{
sql.Append(GetResourceScript("ObjectHelper.SQL.MessageTypes.sql"));
sql.AppendLine();
ResultSets.Add("MessageTypeCollection", resultSetCount++);
}
if (so.XMLSchemaCollections)
{
sql.Append(GetResourceScript("ObjectHelper.SQL.XMLSchemaCollections.sql"));
sql.AppendLine();
ResultSets.Add("XMLSchemaCollection", resultSetCount++);
}
if (so.UniqueConstraints || so.PrimaryKeys || so.ClusteredIndexes || so.NonClusteredIndexes)
{
sql.Append(GetResourceScript("ObjectHelper.SQL.Indexes_" + so.ServerMajorVersion + ".sql"));
sql.AppendLine();
ResultSets.Add("IndexCollection", resultSetCount++);
sql.Append(GetResourceScript("ObjectHelper.SQL.IndexColumns.sql"));
sql.AppendLine();
ResultSets.Add("IndexColumnCollection", resultSetCount++);
}
if (so.DMLTriggers || so.DDLTriggers)
{
sql.Append(GetResourceScript("ObjectHelper.SQL.Triggers_" + so.ServerMajorVersion + ".sql"));
sql.AppendLine();
ResultSets.Add("TriggerCollection", resultSetCount++);
}
if (so.CLRTriggers)
{
sql.Append(GetResourceScript("ObjectHelper.SQL.CLRTriggers_" + so.ServerMajorVersion + ".sql"));
sql.AppendLine();
ResultSets.Add("CLRTriggerCollection", resultSetCount++);
ResultSets.Add("CLRTriggerEventCollection", resultSetCount++);
}
if (so.StoredProcedures)
{
sql.Append(GetResourceScript("ObjectHelper.SQL.StoredProcedures.sql"));
sql.AppendLine();
ResultSets.Add("SPCollection", resultSetCount++);
}
if (so.Aggregates || so.StoredProcedures || so.SQLUserDefinedFunctions || so.CLRUserDefinedFunctions)
{
sql.Append(GetResourceScript("ObjectHelper.SQL.Parameters_" + so.ServerMajorVersion + ".sql"));
sql.AppendLine();
ResultSets.Add("ParameterCollection", resultSetCount++);
}
if (so.Aggregates)
{
sql.Append(GetResourceScript("ObjectHelper.SQL.Aggregates.sql"));
sql.AppendLine();
ResultSets.Add("AggregateCollection", resultSetCount++);
}
if (so.Views)
{
sql.Append(GetResourceScript("ObjectHelper.SQL.Views.sql"));
sql.AppendLine();
ResultSets.Add("ViewCollection", resultSetCount++);
}
if (so.ApplicationRoles || so.DatabaseRoles || so.Users) //DOROBIT DALSIE
{
sql.Append(GetResourceScript("ObjectHelper.SQL.Principals.sql"));
sql.AppendLine();
ResultSets.Add("PrincipalCollection", resultSetCount++);
}
if (so.Assemblies)
{
sql.Append(GetResourceScript("ObjectHelper.SQL.Assemblies_" + so.ServerMajorVersion.ToString() + ".sql"));
sql.AppendLine();
ResultSets.Add("AssemblyCollection", resultSetCount++);
}
if (so.Defaults)
{
sql.Append(GetResourceScript("ObjectHelper.SQL.Defaults.sql"));
sql.AppendLine();
ResultSets.Add("DefaultCollection", resultSetCount++);
}
if (so.Synonyms)
{
sql.Append(GetResourceScript("ObjectHelper.SQL.Synonyms.sql"));
sql.AppendLine();
ResultSets.Add("SynonymCollection", resultSetCount++);
}
if (so.ServiceQueues)
{
sql.Append(GetResourceScript("ObjectHelper.SQL.ServiceQueues.sql"));
sql.AppendLine();
ResultSets.Add("QueueCollection", resultSetCount++);
}
if (so.FullTextCatalogs)
{
sql.Append(GetResourceScript("ObjectHelper.SQL.FullTextCatalogs.sql"));
sql.AppendLine();
ResultSets.Add("FullTextCatalogCollection", resultSetCount++);
}
if (so.FullTextStopLists)
{
if (so.ServerMajorVersion >= 10)
{
sql.Append(GetResourceScript("ObjectHelper.SQL.FullTextStopLists.sql"));
sql.AppendLine();
ResultSets.Add("FullTextStopListCollection", resultSetCount++);
ResultSets.Add("FullTextStopWordCollection", resultSetCount++);
}
else
{
so.FullTextStopLists = false;
}
}
if (so.Services)
{
sql.Append(GetResourceScript("ObjectHelper.SQL.Services.sql"));
sql.AppendLine();
ResultSets.Add("ServiceCollection", resultSetCount++);
ResultSets.Add("ServiceContractCollection", resultSetCount++);
}
if (so.BrokerPriorities)
{
if (so.ServerMajorVersion >= 10)
{
sql.Append(GetResourceScript("ObjectHelper.SQL.BrokerPriorities.sql"));
sql.AppendLine();
ResultSets.Add("BrokerPriorityCollection", resultSetCount++);
}
else
{
so.BrokerPriorities = false;
}
}
if (so.PartitionSchemes)
{
sql.Append(GetResourceScript("ObjectHelper.SQL.PartitionSchemes.sql"));
sql.AppendLine();
ResultSets.Add("PartitionSchemeCollection", resultSetCount++);
ResultSets.Add("PartitionSchemeFileGroupCollection", resultSetCount++);
}
if (so.RemoteServiceBindings)
{
sql.Append(GetResourceScript("ObjectHelper.SQL.RemoteServiceBindings.sql"));
sql.AppendLine();
ResultSets.Add("RemoteServiceBindingCollection", resultSetCount++);
}
if (so.Rules)
{
sql.Append(GetResourceScript("ObjectHelper.SQL.Rules.sql"));
sql.AppendLine();
ResultSets.Add("RuleCollection", resultSetCount++);
}
if (so.Routes)
{
sql.Append(GetResourceScript("ObjectHelper.SQL.Routes.sql"));
sql.AppendLine();
ResultSets.Add("RouteCollection", resultSetCount++);
}
if (so.Schemas)
{
sql.Append(GetResourceScript("ObjectHelper.SQL.Schemas.sql"));
sql.AppendLine();
ResultSets.Add("SchemaCollection", resultSetCount++);
}
if (so.SQLUserDefinedFunctions)
{
sql.Append(GetResourceScript("ObjectHelper.SQL.SQLUserDefinedFunctions.sql"));
sql.AppendLine();
ResultSets.Add("UserDefinedFunctionCollection", resultSetCount++);
}
if (so.CLRUserDefinedFunctions)
{
sql.Append(GetResourceScript("ObjectHelper.SQL.CLRUserDefinedFunctions_" + so.ServerMajorVersion + ".sql"));
sql.AppendLine();
ResultSets.Add("CLRUserDefinedFunctionCollection", resultSetCount++);
ResultSets.Add("CLRUserDefinedFunctionColumnCollection", resultSetCount++);
}
if (so.UserDefinedDataTypes)
{
sql.Append(GetResourceScript("ObjectHelper.SQL.UserDefinedDataTypes_" + so.ServerMajorVersion + ".sql"));
sql.AppendLine();
ResultSets.Add("UserDefinedDataTypeCollection", resultSetCount++);
}
if (so.UserDefinedTypes)
{
sql.Append(GetResourceScript("ObjectHelper.SQL.UserDefinedTypes.sql"));
sql.AppendLine();
ResultSets.Add("UserDefinedTypeCollection", resultSetCount++);
}
if (so.UserDefinedTableTypes)
{
if (so.ServerMajorVersion >= 10)
{
sql.Append(GetResourceScript("ObjectHelper.SQL.UserDefinedTableTypes.sql"));
sql.AppendLine();
ResultSets.Add("UserDefinedTableTypeCollection", resultSetCount++);
ResultSets.Add("UserDefinedTableTypeColumnCollection", resultSetCount++);
ResultSets.Add("UserDefinedTableTypeIndexCollection", resultSetCount++);
ResultSets.Add("UserDefinedTableTypeIndexColumnCollection", resultSetCount++);
ResultSets.Add("UserDefinedTableTypeCheckConstraintCollection", resultSetCount++);
}
else
{
so.UserDefinedTableTypes = false;
}
}
return(sql.ToString());
}
/// <summary>
/// Saves pending changes
/// </summary>
/// <param name="acceptOption">Defines when changes should be accepted locally</param>
/// <param name="clearErrors">If set to true, all error entries are cleared before saving chnages</param>
/// <param name="clientInfo">Optional client info object, to be submitted to the server</param>
public override void SaveChangesAsync(AcceptOption acceptOption = AcceptOption.Default, bool clearErrors = true, bool failOnValidationErrors = true, ClientInfo clientInfo = null, Action <Exception> callback = null)
{
if (acceptOption == AcceptOption.Default)
{
//acceptOption = Transaction.Current != null ? AcceptOption.AcceptChangesOnTransactionCompleted : AcceptOption.AcceptChangesAfterSave;
acceptOption = AcceptOption.AcceptChangesAfterSave;
}
if (clearErrors)
{
ClearErrors();
}
if (failOnValidationErrors && !IsValid)
{
throw new ValidationException("Validation failed for one or more entities.");
}
SubmitChangesAsync(
clientInfo ?? ClientInfo,
(resultSet, exception) =>
{
var error = exception;
if (error == null)
{
try
{
// accept changes
switch (acceptOption)
{
case AcceptOption.AcceptChangesAfterSave:
// refresh local data
var hasValidationErrors = IsServerValidationExceptionSuppressed ? false : resultSet.Any(entity => entity.Errors.Any(e => e.IsError));
Refresh(resultSet);
if (hasValidationErrors)
{
throw new ServerValidationException();
}
AcceptChanges(true);
break;
case AcceptOption.None:
// store result in private list
// allowes user to apply result (accept changes) asynchronousely
ResultSets.Add(resultSet);
break;
default:
throw new Exception(string.Format("This {0} is not implemented: {1}", acceptOption.GetType().Name, acceptOption));
}
}
catch (Exception ex)
{
error = ex;
}
}
if (error is FaultException)
{
var ex = (FaultException)error;
var faultExceptionType = ex.GetType();
if (faultExceptionType.IsGenericType)
{
var detailType = faultExceptionType.GetGenericArguments()[0];
var detailProperty = typeof(FaultException <>).MakeGenericType(detailType).GetProperty("Detail");
var detail = detailProperty.GetValue(ex, null);
if (detail is OptimisticConcurrencyFault)
{
var stateEntries = GetStateEntries(((OptimisticConcurrencyFault)detail).Entities);
error = new OptimisticConcurrencyException(null, stateEntries);
}
if (detail is UpdateFault)
{
var stateEntries = GetStateEntries(((UpdateFault)detail).Entities);
error = new UpdateException(null, stateEntries);
}
}
}
if (callback != null)
{
Invoke(delegate
{
callback(error);
});
}
OnSaveChangesCompleted(error);
}
);
}