Click here to Skip to main content
15,867,453 members
Articles / Desktop Programming / WPF

Reflection Studio - Part 4 - Database: Schema, Provider and Plugin, Controls

Rate me:
Please Sign up or sign in to vote.
4.86/5 (7 votes)
22 Sep 2010GPL310 min read 39K   29   5
Reflection Studio is an "developer" application for assembly, database, performance and code generation written in C# under WPF 4.0

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.

ReflectionStudio

Article Content

As previously said, the subject is quite big, I will (try to) write this article in several parts:

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"

Image 2

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.

Schema objects dependencies

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.

Image 4

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:

C#
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.

C#
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.

XML
<?xml version="1.0" encoding="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:

C#
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.

Schema objects dependencies

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:

  1. 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
  2. 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
  3. Ribbon tabs (on top)
    • SQL Query commands
    • Text commands displayed when query a document is active
  4. The PropertyExplorer (on the right)
    • Display active business object properties
    • The propertyGrid will be re-developed
  5. Provider selection and new datasource dialogs
  6. Dependency dialog
  7. ProjectExplorer (later)

Image 6

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:

C#
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");
}
}
Image 7

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.

C#
public void AddDataSourceCommandHandler(object sender, ExecutedRoutedEventArgs e)
{
	e.Handled = true;

	//load the providers?
	if (Providers.Count > 0)
	{
		//display the dialog
		ProviderChoiceDialog providerDlg = new ProviderChoiceDialog();
		providerDlg.Owner = Application.Current.MainWindow;
		providerDlg.DataContext = Providers;

		//if ok, display the dialog from selected provider
		if (providerDlg.ShowDialog() == true)
		{
			//if ok, create the source
			IDbSourcePanel providerNewSourcePanel =
			providerDlg.SelectedProvider.GetSourcePanelInterface()
				as IDbSourcePanel;

			//display the dialog
			NewDataSourceDialog sourceDlg = new NewDataSourceDialog();
			sourceDlg.Owner = Application.Current.MainWindow;
			sourceDlg.SourcePanel = providerNewSourcePanel;

			if (sourceDlg.ShowDialog() == true)
			{
				// will add a new source to the workspace
				AddSource(providerNewSourcePanel.SourceName,
				providerDlg.SelectedProvider,
				providerNewSourcePanel.ConnectionString);
			}
		}
	}
	else [...]
}

Below is the new datasource dialog that hosts the provider panel from SQL2KProvider:

Image 8

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...
Image 9

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.

Image 10

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.

Image 11

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...

Image 12

We can ask for the creation of a QueryDocument through the DocumentFactory class (see Part 2):

C#
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:

  1. Check to control the SQL code
  2. Execute to start the SQL query in the current window
  3. Stop (Execute) to cancel the current query
  4. 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:

C#
public void ExecuteQueryCommandHandler(object sender, ExecutedRoutedEventArgs e)
{
	e.Handled = true;

	//execute the thread that run the query on the database
         //as ExecuteReader is blocking
	try
	{
		//display the context options
		ShowResultPane();
		this.dataGridResult.Columns.Clear();

		//display the progress,
		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);
		}

		// Start the asynchronous operation.
		_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.

C#
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:

  1. Retrieve the IDataReader from the context and the provider
  2. Loop though the fields to add the columns (as a datagrid column or simple text, depending on the delegate)
  3. Then loop through the row to call the AddDataHandler
C#
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.

C#
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

License

This article, along with any associated source code and files, is licensed under The GNU General Public License (GPLv3)


Written By
Architect
France France
WPF and MVVM fan, I practice C # in all its forms from the beginning of the NET Framework without mentioning C ++ / MFC and other software packages such as databases, ASP, WCF, Web & Windows services, Application, and now Core and UWP.
In my wasted hours, I am guilty of having fathered C.B.R. and its cousins C.B.R. for WinRT and UWP on the Windows store.
But apart from that, I am a great handyman ... the house, a rocket stove to heat the jacuzzi and the last one: a wood oven for pizza, bread, and everything that goes inside

https://guillaumewaser.wordpress.com/
https://fouretcompagnie.wordpress.com/

Comments and Discussions

 
GeneralMy vote of 5 Pin
niuzj19-Jun-11 17:46
niuzj19-Jun-11 17:46 
My vote of 5
Excellent work
GeneralMy vote of 5 Pin
sjelen8-Dec-10 2:30
professionalsjelen8-Dec-10 2:30 
GeneralVery Interesting... Pin
CreF22-Sep-10 21:10
professionalCreF22-Sep-10 21:10 
GeneralRe: Very Interesting... Pin
Guillaume Waser22-Sep-10 21:18
Guillaume Waser22-Sep-10 21:18 
GeneralRe: Very Interesting... Pin
CreF22-Sep-10 21:52
professionalCreF22-Sep-10 21:52 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.