Introduction
This is the fourth part of my article about Reflection Studio. In this chapter, I am describing the database module: schema object to represent database entities, the plugin system, SQL Server 200x as a provider example and all the user controls.
Reflection Studio is hosted on http://reflectionstudio.codeplex.com/. It is completely written in C# under the NET/WPF platform. I recently moved to Visual Studio 2010 and NET 4. Please, have a look to the CodePlex project because it is huge to describe everything in detail. Here under is a screenshot of the application.
Article Content
As previously said, the subject is quite big, I will (try to) write this article in several parts:
- Part 1 - Introduction: Architecture and design
- Part 2 - User interface: themes, dialogs, controls, external libraries
- Part 3 - Assembly: schema, provider and parsing, threads and parallelism, controls
- Part 4 - Database: schema, providers and plugins, controls
- Part 5 - Code generation: template, engine, controls
- Part 6 - Performance: injection, capture and reports
- Part 7 - The project - putting all together
Part 4 - Database
This module is mainly used to discover database content. It allow the following functionalities:
- Explore a database: from Database to columns and datatype
- Scripting: The provider can create DB script for
create/drop/alter + select, update, insert and delete
- Query: Allow to run query against the database
- Checking: Allow to check naming and performance rules
4.1 Schema Objects
Schema objects, stored in ReflectionStudio.Core.Database.Schema namespace, are entity classes that represent all needed element to describe the database. Below are the class and dependency diagrams. The namespace contains:
SchemaObjectBase is the common abstract class DataObjectBase is the common abstract class that represent data DatabaseSchema is holding information like connection string and the provider. It represent a single database ITabularObjectBase is an abstraction for entity having row results like Table and View TableSchema and ViewSchema represents view and table ColumnBaseSchema, TableColumnSchema and ViewColunmSchema represents columns TableKeySchema, PrimaryKeySchema, IndexSchema represent additional and well known objects FK, PK, IX CommandSchema, ParameterSchema, CommandResultSchema represents stored procedures ExtendedProperty is used to hold properties that are not common to different database; like "Description" or "Comments"
As you can see below, dependencies between all schema objects are complex and it's not always easy to work on them. I am not going to describe each entity and their properties. Have a look at the code but it is like DB objects.
4.2 Plugin System and Providers
The namespace ReflectionStudio.Core.Database contains the provider definition and a simple plugin system that allows to load any assembly by reflection if it matches the provider type.
4.2.1 - Provider Interfaces
The provider schema is described by the following interfaces. If you want to write a provider, you will have to implement the first one to make the assembly loadable.
IDbSchemaProvider: The main interface used by the plugin system. It provides us with the database schema IDbSourcePanel: Interface that provides the dialog panel when creating new DataSource IDbExecuteQuery: Interface that fits query need for SQL editor IDbWriter: Interface for creating DB object script IDbVerifier: Interface for checking quality compliance against naming and performance (under development)
Additionally, we have the DataSource class that represents a connection. It is holding the provider that can be used in case of restoring and the main DB schema object: a DatabaseSchema.
Note that you don't need to use the IDbSchemaProvider because it is automatically used by the schema objects to autofill their properties. For example, the DatabaseSchema returns the table through the provider (see below) and so on till the end of the tree:
private List<TableSchema> _Tables;
[Browsable(false)]
public ReadOnlyCollection<TableSchema> Tables
{
get
{
if (this._Tables == null)
{
this.Check();
this._Tables = this.Provider.GetTables
(this.ConnectionString, this);
}
return this._Tables.AsReadOnly();
}
}
4.2.2 - Plugin System
The plugin system is implemented by the DatabaseService class. It is kept simple on purpose because I do not want (now) to implement a complex plugin system like the new one provided by Microsoft like described here.
The class has got a property, that if empty will scan folders to get all the compliant assemblies, will load them and create a IDbSchemaProvider object for each. Note that for an assembly to be loaded, it has to be named xxx.Provider.dll.
List<IDbSchemaProvider> _Providers;
public ReadOnlyCollection<IDbSchemaProvider> Providers
{
get
{
if (_Providers == null)
_Providers = LoadProviders();
return _Providers.AsReadOnly();
}
}
...
private List<IDbSchemaProvider> LoadProviders()
{
List<IDbSchemaProvider> list = new List<IDbSchemaProvider>();
List<String> fileProvider = new List<String>();
foreach (string folder in this.ProviderSearchDirectories)
{
fileProvider.AddRange(Directory.GetFiles(folder, "*Provider.dll"));
}
foreach (string str2 in fileProvider)
{
try
{
foreach (Type type in Assembly.LoadFrom(str2).GetTypes())
{
if (type.GetInterface
(typeof(IDbSchemaProvider).FullName) != null)
{
IDbSchemaProvider provider =
(IDbSchemaProvider)Activator.CreateInstance
(type);
if (list.Where(p => p.Name ==
provider.Name).Count() == 0)
{
list.Add(provider);
}
}
}.....
When we get all files that match the first requirement (file name) in all our scan directories, we load each assembly to check if a type is implementing the IDbSchemaProvider interface. If it is the case, then we create it and add it to the provider list.
4.2.3 - Helper
QueryResourceManager is a very useful class that allows to retrieve queries from an XML embedded resource file. It can store all the queries needed to discover the schema in your specialized provider. The XML looks like below. Each query has got Key and Version attributes, because you can query a specific version depending on the database server version you are working on.
="1.0" ="utf-8"
<Queries>
<ListOfQuery>
<Query Key="Dependencies" Version="2008">
<Content>
CREATE TABLE #tempdep (objid int NOT NULL, objtype smallint NOT NULL)
BEGIN TRANSACTION
INSERT INTO #tempdep
SELECT
Instantiate a QueryManager in your provider and use it like below:
QueryManager _Queries = new QueryManager
("ReflectionStudio.Core.Database.SQL2KProvider", "Queries.xml");
[...}
string str = string.Format(_Queries.GetQuery("CommandText"),
command.Database.Name, command.Name);
using (SQLQueryHelper query = new SQLQueryHelper(connectionString))
{
SqlDataReader reader = query.ExecuteReader(str);
4.2.4 - SQL 2K Provider
This provider (used to be compliant with all versions of Microsoft SQL Server from version 2000 up to 2008) implements all the interfaces described before. It is actually a bit in a draft state - I have to improve the coding - but it serves the basic need. Further description will be given in the next article updates.
4.2.5 - Other Provider
An ORACLE provider is actually under development by one team member and the documentation update will come soon. Feel free to contribute to the project, but do it by subscribing to CodePlex and use the TFS because there are a lot of improvement in the pipeline.
4.3 - Associated User Interface
This chapter presents the database module related user interface elements that you can find in Reflection Studio. The list is:
- The
DatabaseExplorer (on the left)
- New, refresh and delete commands to work on
DataSource
DataSource selector to change from one DB to another - The
treeview to browse schema
- The context menu to access commands
QueryDocument (in the middle)
- Editor part that is color syntaxed and zoomable
- Result pane that displays results for the executed query
- Status Bar that displays specific execution information
- Ribbon tabs (on top)
- SQL Query commands
- Text commands displayed when query a document is active
- The
PropertyExplorer (on the right)
- Display active business object properties
- The
propertyGrid will be re-developed
- Provider selection and new datasource dialogs
- Dependency dialog
ProjectExplorer (later)
4.3.1 - Treeview and Database Explorer
Allow to trace all development steps through classical function like in Microsoft trace or debug system: Error, Info, Verbose. I generally use a template like the following one and it's very useful when writing code and testing as you can see trace directly in the UI:
public bool Open( string fileName )
{
Tracer.Verbose("ProjectService:Open", "START");
try
{
Current = new ProjectEntity(fileName);
return LoadProject();
}
catch (Exception err)
{
Tracer.Error("ProjectService.Open", err);
return false;
}
finally
{
Tracer.Verbose("ProjectService:Open", "END");
}
}
4.3.2 - Provider Choice and Datasource Creation
In the application, when we answer the datasource creation command (held by the DB explorer), we first check that we have provider in our list...If it is the case, we display the ProviderChoiceDialog with a DataContext set to the provider list (rest is just binding and templating). If this dialog is validated, we use his property SelectedProvider to retrieve the IDbSourcePanel control so we can continue with the NewDatasourceDialog. When we set his property SourcePanel, this dialog is changing its content to our new panel. In case of success, we retrieve the connection string property, ask the DatabaseService to create the DataSource and we also update the workspace collection.
public void AddDataSourceCommandHandler(object sender, ExecutedRoutedEventArgs e)
{
e.Handled = true;
if (Providers.Count > 0)
{
ProviderChoiceDialog providerDlg = new ProviderChoiceDialog();
providerDlg.Owner = Application.Current.MainWindow;
providerDlg.DataContext = Providers;
if (providerDlg.ShowDialog() == true)
{
IDbSourcePanel providerNewSourcePanel =
providerDlg.SelectedProvider.GetSourcePanelInterface()
as IDbSourcePanel;
NewDataSourceDialog sourceDlg = new NewDataSourceDialog();
sourceDlg.Owner = Application.Current.MainWindow;
sourceDlg.SourcePanel = providerNewSourcePanel;
if (sourceDlg.ShowDialog() == true)
{
AddSource(providerNewSourcePanel.SourceName,
providerDlg.SelectedProvider,
providerNewSourcePanel.ConnectionString);
}
}
}
else [...]
}
Below is the new datasource dialog that hosts the provider panel from SQL2KProvider:
4.3.3 - Ribbon Tabs
The SQL query ribbon tab is always visible and contains the following commands:
- New, Refresh, Remove and check (
Datasource) - commands from the explorer
- New, Output, Check, Execute, Stop (
Query) - commands from a query document
- later...
We also have a contextual tab that comes up with any rich text document like query and template. It allows to format the text, clipboard and undo/redo functions that are contained in the AvalonEdit control.
4.3.4 - Others
The application provides one dialog related to the database module. The dependency dialog that displays objects that depend on or from the selected object. The provider just gives a relation table that the dialog has to process the right way to display ON or FROM values.
4.4 - Documents
The application actually contains a query document - but will perhaps have others like designers for creation or retro-analyse.
4.4.1 - Query Document
The query document is based on a ZoomDocument which is derivated from a DocumentContent in AvalonDock. This user control contains a SyntaxedTextEditor, a personalized class derived from TextEditor contained in ICSharpCode.AvalonEdit to support template and SQL langue syntax colorizing.
Added to that, we have a tabbed pane to display results in grid or text form, plus a message part. Under that, I had a status part to display the current datasource, number of line the query returns, the time it takes to execute and messages. It is a bit redundant with the application status bar...
We can ask for the creation of a QueryDocument through the DocumentFactory class (see Part 2):
internal QueryDocument CreateQueryEditor()
{
DataSource ds = Data;
if (ds != null)
{
return (QueryDocument)DocumentFactory.Instance.CreateDocument(
DocumentFactory.Instance.SupportedDocuments.Find
(p => p.DocumentContentType == typeof(QueryDocument)),
new DocumentDataContext() { Entity = ds });
}
else
return null;
}
The QueryDocument class provides 4 commands:
- Check to control the SQL code
- Execute to start the SQL query in the current window
- Stop (Execute) to cancel the current query
- Output mode to change from grid to text and vice-versa
The check SQL query command is very easy and just surrounds the current editor text with SET PARSEONLY ON/OFF tag. It asks the provider to execute it and when the result comes out without error, the command is ok.
To implement the SQL query execution, I want it to be asynchronous, but just the native SQL server classes provide such BeginExecuteReader methods. All ADO and other drivers are synchronous. But all this was not compliant to my SQL provider schema and the plugin system that needed to work for every database engine.
So I chose a compromise: The execution can be synchronous - means we have to wait for the database query to finish its execution, but we need to be able to cancel the data retrieving.
From that point of view, I decided to implement a QueryContext that holds the information needed for execution and a QueryWorker based on WorkerBase from ReflectionStudio.Core.Helpers namespace, which is a helper class to manage a background worker.
As illustrated bellow, in the execute command of the document, we create a QueryContext and start a new thread that runs the Execute method of the QueryWorker:
public void ExecuteQueryCommandHandler(object sender, ExecutedRoutedEventArgs e)
{
e.Handled = true;
try
{
ShowResultPane();
this.dataGridResult.Columns.Clear();
EventDispatcher.Instance.RaiseStatus
("Executing query !", StatusEventType.StartProgress);
QueryContext context = new QueryContext()
{
UIDispatcher = this.Dispatcher,
Command = SyntaxEditor.Text,
Source = this.DataSource,
StartTime = DateTime.Now
};
if (OutputMode == QueryOutputMode.Grid)
{
context.AddColumn = new QueryContext.AddColumnDelegate
(GridAddColumnHandler);
context.AddData = new QueryContext.AddRowDelegate
(GridAddDataHandler);
}
else
{
context.AddColumn = new QueryContext.AddColumnDelegate
(TextAddColumnHandler);
context.AddData = new QueryContext.AddRowDelegate
(TextAddDataHandler);
}
_Worker = new BackgroundWorker();
_Worker.WorkerSupportsCancellation = true;
_Worker.DoWork += new DoWorkEventHandler(bw_RequestWork);
_Worker.RunWorkerCompleted += new RunWorkerCompletedEventHandler
(bw_RequestCompleted);
_Worker.ProgressChanged += new ProgressChangedEventHandler
(bw_RequestProgressChanged);
_Worker.RunWorkerAsync(context);
}
catch (Exception all)
{
}
}
and we start the thread with that code. Note the override for the result value, because the WorkerBase class uses a boolean as the thread result value by default.
protected void bw_RequestWork(object sender, DoWorkEventArgs e)
{
QueryWorker qw = new QueryWorker((BackgroundWorker)sender, e);
qw.ExecuteQuery();
e.Result = qw.Context;
}
An interesting point is that the QueryContext class uses two delegates that can be changed depending on the output type we need: grid or text. And to solve the problem of variable results in terms of columns and rows, I use a new Framework 4 object: the ExpandoObject
The algorithm used below can be summarized by:
- Retrieve the
IDataReader from the context and the provider
- Loop though the fields to add the columns (as a
datagrid column or simple text, depending on the delegate)
- Then loop through the row to call the
AddDataHandler
public void ExecuteQuery()
{
Tracer.Verbose("QueryWorker.ExecuteQuery", "START");
IDataReader reader = null;
try
{
reader = Context.Source.Database.Provider.GetSQLQueryInterface().
ExecuteReader(Context.Source.ConnectionString, Context.Command);
for (int i = 0; i < reader.FieldCount; i++)
Context.UIDispatcher.Invoke
(Context.AddColumn, reader.GetName(i));
int counter = 0;
while (!CancelPending() && reader.Read() )
{
dynamic data = new ExpandoObject();
for (int i = 0; i < reader.FieldCount; i++)
((IDictionary<String, Object>)data).Add
(reader.GetName(i), reader.GetValue(i));
Context.UIDispatcher.Invoke(Context.AddData, data);
counter++;
}
Context.LineCount = counter;
Context.Message = "Query ok";
}
[...]
}
Here is the miracle! ExpandoObject is exposing dynamic properties that you can create on the fly! So, the two handlers for the grid are very simple... because we just add the object to the collection (in the grid DataContext) so the grid will discover the properties and the values with the binding.
protected void GridAddColumnHandler(string headerText)
{
if (this.dataGridResult.ItemsSource == null)
this.dataGridResult.ItemsSource = new ObservableCollection<dynamic>();
DataGridTextColumn dt = new DataGridTextColumn();
dt.Header = headerText;
dt.Binding = new Binding(headerText);
this.dataGridResult.Columns.Add(dt);
}
protected void GridAddDataHandler(dynamic data)
{
(this.dataGridResult.ItemsSource as ObservableCollection<dynamic>).Add(data);
}
Note that performance needs to be improved. I noticed that it is very slow when the grid is updating its interface and much more quicker after when just the scrollbar needs to be adjusted.
You have noticed that we loop the reader and always check the CancelPending method. It comes from the WorkerBase and can interrupt the thread.
Conclusion / Feedback
See you in the next article of this series. Do not hesitate to give me feedback about it either on Codeplex or CodeProject. As the team is growing, I hope that we are getting faster and do not hesitate to join us!
Article / Software History
- Initial release - Version BETA 0.2
- Initial version that contains "nearly" everything, and is available for download on Part 1, or CodePlex as Release
- Version BETA 0.3
- Update on skin/color management and the Database module
- Part 4 - Database module - is published
