Introduction
This article introduces a simple database viewer that manipulates SQL Server database data. It allows to copy one row to another in an easy and familiar copy and paste manner, even if the row contains some binary data. This is particularly handy when one is trying to generate some sample data in the database using the already existing valid records. For the sake of the example, this article considers image columns in DB as real images (and not as any kind of BLOBs), thus allowing to load/view an image to/from a specific record in the database.
Background
During generation of sample data in the database, arises the need to operate images, binary data, ntext and text data types more easily when executing them in T-SQL. The following article introduces a simple database viewer that should accomplish this task.
DBViewer Description
The DBViewer application consists of two main parts. The first is the tree view of SQL Servers and databases which were registered by the application (use the right mouse context menu to register a SQL Server). The second is a custom DataGrid representing the DB's table. If a row in the table contains binary info, our custom DataGrid will represent it as a binary string in contrary to the ordinary default value of a binary type: "Byte[]". In addition, if a row contains an image - our DataGrid will show a small thumbnail picture of the underlined image, allowing double clicking the cell in order to view/load other images as well.
Please refer to the Class Diagram which is provided with the source code.
Code Overview
There are four layers in the application: DAL, BL, UI and the Common layer.
Common - Common Layer
The common layer contains some constant definitions for the DBViewer application.
It also contains a simple implementation of logging operations which uses .NET framework's namespace for debugging and tracing: System.Diagnostics.
The framework provides the Trace class in the System.Diagnostics namespace that can suit our simple logging operations and allow us to add a listener to the Listeners collection of the Trace class. After adding a listener to the Listeners collection, we can write any message using the Trace.WriteLine method which loops over the listeners in the Listeners collection and writes the desired message according to the listener's type.
We will add a TextWriterTraceListener (which redirects its output to an instance of the TextWriter class or to anything that is a Stream class) to the Trace.Listeners collection, as follows:
public sealed class Log
{
private static readonly string LogFileName =
DBViewerConstants.ApplicationPath + "\\log.txt";
...
static Log()
{
try
{
StreamWriter log;
if(File.Exists(LogFileName))
log = File.AppendText(LogFileName);
else
log = File.CreateText(LogFileName);
Trace.Listeners.Add(new TextWriterTraceListener(log));
}
catch
{
}
}
...
}
In order to output an error to the log in the DBViewer application, the following method is used (the method seeks the caller method's name in order to write it to the log):
public static void WriteErrorToLog(string message)
{
try
{
int index = 1;
string cls;
string method;
StackFrame frame;
StackTrace stack = new StackTrace();
do
{
frame = stack.GetFrame(index++);
cls = frame.GetMethod().ReflectedType.FullName;
}
while(cls == Log.LogInstanceName);
method = frame.GetMethod().Name;
StringBuilder logMessage = new StringBuilder(LogMessageLength);
logMessage.Append(DateTime.Now.ToShortDateString());
logMessage.Append("-");
logMessage.Append(DateTime.Now.ToLongTimeString());
logMessage.Append(":: ");
logMessage.Append(cls);
logMessage.Append(".");
logMessage.Append(method);
logMessage.Append("()- ");
logMessage.Append(message);
Trace.WriteLine(logMessage.ToString(), TraceLevel.Error.ToString());
}
catch
{
}
}
Although the message was sent to its listener, it won't be written until the Trace.Flush method is called. Instead of calling the Flush method, I prefer to configure the .config file to flush the trace automatically.
This is how it's done:
="1.0" ="utf-8"
<configuration>
-->
<system.diagnostics>
<trace autoflush="true" indentsize="3" />
</system.diagnostics>
</configuration>
DAL - Data Access Layer
The main class in this layer is the DBViewerDAL which is responsible for connecting to the SQL Server for retrieving/updating data.
Connecting to the DB and manipulating the data is done by SqlConnection and SqlCommand respectively, which are defined as members of the above mentioned class:
private SqlConnection connection;
private SqlCommand command;
Constructing the SQL connection string is done by the following method (pay attention that the method uses the StringBuilder object in order to gain performance for concatenating strings):
private void ConnectToDatabase(string server, string database,
string user, string password)
{
try
{
StringBuilder sqlConnectionStr =
new StringBuilder(ConnectionStringLength);
sqlConnectionStr.Append("Server=");
sqlConnectionStr.Append(server);
sqlConnectionStr.Append("; ");
if(database.Length != 0)
{
sqlConnectionStr.Append("DataBase=");
sqlConnectionStr.Append(database);
sqlConnectionStr.Append("; ");
}
if(user.Length != 0)
{
sqlConnectionStr.Append("User Id=");
sqlConnectionStr.Append(user);
sqlConnectionStr.Append("; Password=");
sqlConnectionStr.Append(password);
sqlConnectionStr.Append(";");
}
else
{
sqlConnectionStr.Append("Integrated Security=SSPI;");
}
connection = new SqlConnection(sqlConnectionStr.ToString());
command = new SqlCommand();
}
catch(Exception e)
{
Log.WriteErrorToLog(e.Message);
throw;
}
}
The application should reflect all available databases in the given SQL Server along with their data tables and data.
The basic unit of data that will be passed across the tiers will be a DataTable. As some types (e.g., images and binaries) will be referred to in a "special" way, in order to reformat them easily later - the result DataTable will be constructed manually using SqlDataReader and without any schema.
Extracting data from the DB is done by the following methods:
public DataTable GetData(string query)
{
try
{
command.CommandType = CommandType.Text;
command.CommandText = query;
command.Connection = connection;
connection.Open();
SqlDataReader reader = command.ExecuteReader();
DataTable dataTable = ConstructData(reader);
reader.Close();
return dataTable;
}
catch(Exception e)
{
Log.WriteErrorToLog(e.Message);
throw;
}
finally
{
connection.Close();
}
}
private static DataTable ConstructData(SqlDataReader reader)
{
try
{
if(reader.IsClosed)
throw new
InvalidOperationException("Attempt to" +
" use a closed SqlDataReader");
DataTable dataTable = new DataTable();
for(int i=0; i<reader.FieldCount; i++)
dataTable.Columns.Add(reader.GetName(i),
reader.GetFieldType(i));
while(reader.Read())
{
object[] row = new object[reader.FieldCount];
reader.GetValues(row);
dataTable.Rows.Add(row);
}
dataTable.Locale = CultureInfo.InvariantCulture;
dataTable.AcceptChanges();
return dataTable;
}
catch(Exception e)
{
Log.WriteErrorToLog(e.Message);
throw;
}
}
In order to update a table in the DB (i.e., delete a row, add a row or change the row's values), appropriate commands should be constructed. As the DataTable to be updated doesn't have any schema, SqlCommandBuilder is used in order to construct Delete, Update and Insert commands on the fly.
(Now, as SqlCommandBuilder isn't a preferable class to be used, we use it here for the sake of our example only, as it is the most easy way to generate SqlCommands on the fly. When developing for commercial products, try to avoid using this class as it really affects performance.)
Avoiding inconsistence in the DB is reached by using transactions.
(Note that DBViewer only works on DataTables with primary keys.)
public void Save(DataTable table)
{
try
{
string query = "SELECT * FROM " + table.TableName;
command.CommandType = CommandType.Text;
command.CommandText = query;
command.Connection = connection;
connection.Open();
SqlTransaction transaction =
connection.BeginTransaction(IsolationLevel.RepeatableRead);
command.Transaction = transaction;
SqlDataAdapter dataAdapter = new SqlDataAdapter();
dataAdapter.SelectCommand = command;
SqlCommandBuilder commandBuilder =
new SqlCommandBuilder(dataAdapter);
try
{
DataTable changes;
changes = table.GetChanges(DataRowState.Deleted);
if(changes != null)
dataAdapter.Update(changes);
changes = table.GetChanges(DataRowState.Modified);
if(changes != null)
dataAdapter.Update(changes);
changes = table.GetChanges(DataRowState.Added);
if(changes != null)
dataAdapter.Update(changes);
transaction.Commit();
}
catch
{
transaction.Rollback();
throw;
}
}
catch(Exception e)
{
Log.WriteErrorToLog(e.Message);
throw;
}
}
BL - Business Logic Layer
The business logic is responsible for retrieving the data from the DB, updating the data in the DB, and caching user credentials/data in order to work better with the SQL Server. For brevity, I won't talk here about caching, please refer to the source code for further information. The main point of interest is how to retrieve the information about the databases and their tables in the SQL Server.
For example, in order to retrieve the databases in the registered SQL Server and display it to the user, the following method is being executed:
...
private const string DatabasesQuery =
"SELECT CATALOG_NAME FROM INFORMATION_SCHEMA.SCHEMATA";
...
public void DatabasesInfoRequested(string server, string user, string password)
{
try
{
if(server == null || user == null || password == null)
throw new ArgumentNullException();
DBViewerDAL.Instance().SetConnection(server, user, password);
DataTable data = DBViewerDAL.Instance().GetData(DatabasesQuery);
data.TableName = server;
DBViewerCache.Instance().AddCredentials(server, user, password);
DBViewerGui.Instance().ShowDatabasesInfo(data);
}
catch(Exception e)
{
Log.WriteErrorToLogAndNotifyUser(e.Message);
}
}
DBViewerDAL.Instance().SetConnection(server, user, password); calls internally to the ConnectToDatabase method in the DAL we have seen before. Tables information for a particular database is retrieved in a similar way. After the info has been retrieved, it is passed to the GUI layer: for example, the info about the available databases in a given SQL Server will be passed to the TreeView while a specific table with its data will be passed to the DataGrid control in order to represent it to the user. As you can see, the BL also caches the user's credentials for a particular server.
UI - User Interface Layer
The UI layer consists of the following three classes: DBViewerGui, DatabaseTreeViewer and DBViewerDataGrid.
The DBViewerGui is a MDI container which contains the DatabaseTreeViewer docked to its left and contains DBViewerDataGrid as its MDI child. The DatabaseTreeViewer represents the SQL Servers and their databases and allows to select a data table which is represented by the DBViewerDataGrid.
DBViewerDataGrid contains a custom control which inherits from the DataGrid and is called DatabaseDataGrid.
The DatabaseDataGrid control represents a binary data as a binary string in contrary to the usual default representation value of a binary type: "Byte[]". The DatabaseDataGrid control represents an image column as a small thumbnail picture of the underlined DataSource's image, allowing to double click the cell in order to view/load other images as well. It also allows to copy/paste a row with its binary data using the common "CTRL-C" and "CTRL-V" key combinations.
This article will discuss only the main DatabaseDataGrid issues.
- How to represent the data differently from its underlined source?
In order to represent the data differently from its underlined source - DataGridTableStyle is used.
Each DataGrid may have many table styles which are defined in the DataGrid's TableStyles collection. Each DataGridTableStyle in its turn consists of a lot of DataGridColumnStyle which are defined in the table's style GridColumnStyles collection. Each DataGrid column style is the one that actually defines how a specific column will be represented to the user.
In this way, it is easy to represent different views of the same underlined data.
- In order to represent an image when a column of type "
image" exists, we will define a custom DataGridImageColumn which inherits from DataGridColumnStyle and overrides its Paint method.
This is how it is done:
public class DataGridImageColumn : DataGridColumnStyle
{
...
protected override void Paint(Graphics g, Rectangle bounds,
CurrencyManager source, int rowNum, bool alignToRight)
{
PaintImage(g, bounds, source, rowNum);
}
...
private void PaintImage(Graphics g,
Rectangle bounds, CurrencyManager manager, int rowNum)
{
SolidBrush backBrush = new SolidBrush(Color.White);
try
{
Image thumbnailImage;
byte[] img = (GetColumnValueAtRow(manager, rowNum) as byte[]);
if(img == null)
{
thumbnailImage = defaultImage;
}
else
{
Image cellImage = Image.FromStream(new MemoryStream(img));
thumbnailImage = cellImage.GetThumbnailImage(ThumbnailSize,
ThumbnailSize, new
System.Drawing.Image.GetThumbnailImageAbort(ThumbnailCallback),
IntPtr.Zero);
}
g.FillRectangle(backBrush, bounds);
g.DrawImage(thumbnailImage, bounds);
}
catch(ArgumentException e)
{
g.FillRectangle(backBrush, bounds);
g.DrawImage(unknownImage, bounds);
Log.WriteErrorToLog(e.Message);
}
catch(Exception e)
{
Log.WriteErrorToLog(e.Message);
throw;
}
}
...
}
Pay attention to the fact that sometimes we won't be able to get the Image from the Stream, that's why I have added the ArgumentException handling which draws an unknown image as we may have failed to recognize the image's format.
- In order to represent a binary data when a column of type "
binary" or "varbinary" exists, we will define another custom DataGridImageColumn which inherits DataGridTextBoxColumn. DataGridTextBoxColumn is supplied by the .NET Framework and allows to represent an editable column. This is actually what we want, but in addition to that we wish to see the binary info too. Thus we will override DataGridTextBoxColumn's SetColumnValueAtRow and GetColumnValueAtRow that affect how those rows are updated and represented respectively.
This is how it is done:
public class DataGridBinaryColumn : DataGridTextBoxColumn
{
...
protected override void
SetColumnValueAtRow(CurrencyManager source,
int rowNum, object value)
{
string strValue = value.ToString();
byte[] data = new byte[strValue.Length];
for(int i=0; i < strValue.Length; i++)
data[i] = Convert.ToByte(strValue[i]);
base.SetColumnValueAtRow (source, rowNum, data);
}
protected override object
GetColumnValueAtRow(CurrencyManager source, int rowNum)
{
object value = base.GetColumnValueAtRow(source, rowNum);
byte[] data = value as byte[];
if(data == null)
return value;
else
{
StringBuilder binaryRepresentation = new StringBuilder();
int i=0;
while(i < data.Length)
binaryRepresentation.Append(data[i++]);
return binaryRepresentation.ToString();
}
}
- How to perform "copy"-"paste" operations on the
DataGrid?
In order to allow "CTRL+C" combination, we should first define that this combination isn't a control combination.
Then we should catch the KeyDown event and use Clipboard object to handle the "CTRL+C" and "CTRL+V" combinations respectively.
It is done as described below:
...
protected override bool IsInputKey(Keys keyData)
{
if(keyData == ( Keys.Control | Keys.C))
return true;
return base.IsInputKey (keyData);
}
...
protected override void OnKeyDown(KeyEventArgs e)
{
if(e.KeyData == (Keys.C | Keys.Control))
{
DataTable table = (DataTable)this.DataSource;
if(selectedDataGridRow < table.Rows.Count)
{
DataFormats.Format format =
DataFormats.GetFormat(this.ToString());
IDataObject data = new DataObject();
DataRow row = table.Rows[selectedDataGridRow];
data.SetData(format.Name, false, row.ItemArray);
Clipboard.SetDataObject(data, false);
}
}
else if(e.KeyData == (Keys.V | Keys.Control))
{
IDataObject data = Clipboard.GetDataObject();
string format = this.ToString();
if(data.GetDataPresent(format))
{
object[] row = data.GetData(format) as object[];
DataTable table = (DataTable)this.DataSource;
if(table.Rows.Count < (selectedDataGridRow + 1))
table.Rows.Add(row);
else
table.Rows[selectedDataGridRow].ItemArray = row;
}
}
else if(e.KeyData == (Keys.S | Keys.Control))
{
SaveDatabaseTable();
}
else
base.OnKeyDown (e);
}
As you can see, when "CTRL+C" combination is hit, the info of the current row selectedDataGridRow is saved to the Clipboard using Clipboard.SetDataObject method. When "CTRL+V" combination is hit, we get the data from the Clipboard using IDataObject data = Clipboard.GetDataObject(); and check whether the extracted data is our data, using data.GetDataPresent which returns true if the extracted data is of our type. Finally, we save the data to the newly selected row: selectedDataGridRow.
Remarks
- For the sake of the example, I have considered the Image column as a column containing only images binary info, but it can easily transform to represent any binary data using the
DataGridBinaryColumn style which was explained in this article.
- Any suggestions, improvements and bug reports will be very much appreciated.
