Click here to Skip to main content
16,017,297 members
Articles / Database Development / SQL Server
Article

A simple Database Viewer - DBViewer

Rate me:
Please Sign up or sign in to vote.
4.75/5 (39 votes)
18 Apr 2005CDDL8 min read 177.7K   10.2K   113   12
A simple database viewer to manipulate SQL Server data types (in particular: image, binary, varbinary and text).

DBViewer

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:

C#
/// <summary>
/// Log - logs the errors in the application.
/// </summary>
public sealed class Log
{

    /// <summary>
    /// Log file name.
    /// </summary>
    /// <remarks> DBViewerConstants.ApplicationPath
    ///  - just returns the current application's path </remarks> 
    private static readonly string LogFileName = 
            DBViewerConstants.ApplicationPath + "\\log.txt";

    ...

    /// <summary>
    /// Constructor.
    /// </summary>
    static Log()
    {
        try
        {
            // creates the log or appends the text at the end.
            StreamWriter log;
            if(File.Exists(LogFileName))
                log = File.AppendText(LogFileName);
            else
                log = File.CreateText(LogFileName);

            Trace.Listeners.Add(new TextWriterTraceListener(log));
        }
        catch
        {
            //writing to log shouldn't raise an exception.
        }
    }
    ...
}

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):

C#
/// <summary>
/// Writes the error to the log.
/// </summary>
/// <param name="message">error message</param>
public static void WriteErrorToLog(string message)
{
    try
    {
        // variables.
        int index = 1;
        // current class name
        string cls;
        string method;
        StackFrame frame;

        // gets the info of the calling method.
        StackTrace stack = new StackTrace();
        // while the class's name is the Log
        // continue to extract the callers from the stack.
        do
        {
            frame = stack.GetFrame(index++);
                cls = frame.GetMethod().ReflectedType.FullName;
        }
        while(cls == Log.LogInstanceName);
        // gets the caller method's name.
        method = frame.GetMethod().Name;

        // constructs the message.
        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);

        // writes the message to the log.
        Trace.WriteLine(logMessage.ToString(), TraceLevel.Error.ToString());
    }
    catch
    {
        //writing to log shouldn't raise an exception.
    }
}

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:

XML
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <!-- automatically writes the trace messages -->
    <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:

C#
/// <summary>
/// SQL Connection.
/// </summary>
private SqlConnection connection;
/// <summary>
/// SQL Command.
///  </summary>
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):

C#
/// <summary>
/// Connects to the Database using the following parameters.
/// </summary>
/// <param name="server">server</param>
/// <param name="database">database</param>
/// <param name="user">user</param>
/// <param name="password">password</param>
private void ConnectToDatabase(string server, string database, 
                                 string user, string password)
{
    try
    {
        StringBuilder sqlConnectionStr = 
           new StringBuilder(ConnectionStringLength);

        // sets the server.
        sqlConnectionStr.Append("Server=");
        sqlConnectionStr.Append(server);
        sqlConnectionStr.Append("; ");

        // sets the database.
        if(database.Length != 0)
        {
            sqlConnectionStr.Append("DataBase=");
            sqlConnectionStr.Append(database);
            sqlConnectionStr.Append("; ");
        }
        /* sets the user name and the password.
        * (the password isn't required, 
        * but if the name exists then the user
        * tries to authenticate throught sql authentication)
        **/ 
        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:

C#
/// <summary>
/// Gets the data from the database according to the user's query.
/// </summary>
/// <param name="query">query to extract the data from the database.</param>
/// <returns>Queried data in DataTable</returns>
public DataTable GetData(string query)
{
    try
    {
        // opens connection. 
        command.CommandType = CommandType.Text;
        command.CommandText = query;
        command.Connection = connection;

        connection.Open();
        
        // executes the query.
        SqlDataReader reader = command.ExecuteReader();
        DataTable dataTable = ConstructData(reader); 

        // closes connection.
        reader.Close();
        
        return dataTable;
    }
    catch(Exception e)
    {
        Log.WriteErrorToLog(e.Message);
        throw;
    }
    finally
    {
        connection.Close();
    }
}
C#
/// <summary>
/// Constructs the data which was extracted
/// from the database according to user's query.
/// </summary>
/// <param name="reader">SqlReader - holds the queried data.</param>
///<returns>Queried data in DataTable.</returns>
private static DataTable ConstructData(SqlDataReader reader)
{
    try
    {
        if(reader.IsClosed)
            throw new 
              InvalidOperationException("Attempt to" + 
                       " use a closed SqlDataReader");

        DataTable dataTable = new DataTable();

        // constructs the columns data.
        for(int i=0; i<reader.FieldCount; i++)
            dataTable.Columns.Add(reader.GetName(i), 
                            reader.GetFieldType(i));

        // constructs the table's data.
        while(reader.Read())
        {
            object[] row = new object[reader.FieldCount];
            reader.GetValues(row);
            dataTable.Rows.Add(row);
        }
        // Culture info.
        dataTable.Locale = CultureInfo.InvariantCulture;
        // Accepts changes.
        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.)

C#
/// <summary>
/// Saves the table to the DB.
/// </summary>
/// <param name="table">database table.</param>
public void Save(DataTable table)
{
    try
    {
        // prepares select command.
        string query = "SELECT * FROM " + table.TableName;

        command.CommandType = CommandType.Text;
        command.CommandText = query;
        command.Connection = connection;
        // opens connection.
        connection.Open();
        // gets transaction context.
        SqlTransaction transaction = 
          connection.BeginTransaction(IsolationLevel.RepeatableRead);
        command.Transaction = transaction;
        // sets the SqlCommandBuilder
        // that constructs update, delete, insert commands.
        SqlDataAdapter dataAdapter = new SqlDataAdapter();
        dataAdapter.SelectCommand = command;
        SqlCommandBuilder commandBuilder = 
          new SqlCommandBuilder(dataAdapter);

        try
        {
            DataTable changes;

            // The specific order of this execution is very important.
            // Consider the case that the user
            // first deletes the row with primary key X, 
            // then adds a new row with primary key X
            // - by executing the update in the following order it won't fail.
            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.

  • In order to get the databases (catalogs), the following statement should be executed in SQL Server 2000:
    SQL
    SELECT CATALOG_NAME FROM INFORMATION_SCHEMA.SCHEMATA
  • In order to get the tables in a given database, the following statement should be executed in SQL Server 2000:
    SQL
    SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES

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:

C#
...

private const string DatabasesQuery    = 
   "SELECT CATALOG_NAME FROM INFORMATION_SCHEMA.SCHEMATA";

...
/// <summary>
/// Retrieves the list of databases from the SQL server.
/// </summary>
/// <param name="server">server</param>
/// <param name="user">user</param>
/// <param name="password">password</param>
public void DatabasesInfoRequested(string server, string user, string password)
{
    try
    {
        if(server == null || user == null || password == null)
            throw new ArgumentNullException();

        // gets the list of all databases in the given sql server.
        DBViewerDAL.Instance().SetConnection(server, user, password);
        DataTable data = DBViewerDAL.Instance().GetData(DatabasesQuery);
        data.TableName = server;

        // saves user's credentials.
        DBViewerCache.Instance().AddCredentials(server, user, password);

        // represents the data in the DatabaseTreeView.
        DBViewerGui.Instance().ShowDatabasesInfo(data);
    }
    catch(Exception e)
    {
        // all exception catched.
        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:

      C#
      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
           {
            // thumbnail image from the cell's image.
            Image thumbnailImage;
            // gets the img from the DataSource.
            byte[] img = (GetColumnValueAtRow(manager, rowNum) as byte[]);
            // if no image in the current cell - displays the default image.
            if(img == null)
            {
              thumbnailImage = defaultImage;
            }
            else
            {
             Image cellImage = Image.FromStream(new MemoryStream(img));
             // creates thumbnail image from cell's
             // image with default size : thumbnailSize
             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:

      C#
      public class DataGridBinaryColumn : DataGridTextBoxColumn
      {
      ...
      
        protected override void 
          SetColumnValueAtRow(CurrencyManager source, 
          int rowNum, object value)
        {
      
          // converts the value to string.
          string strValue = value.ToString();
      
          // constructs the binary data from the value.
          byte[] data = new byte[strValue.Length];
          for(int i=0; i < strValue.Length; i++)
              data[i] = Convert.ToByte(strValue[i]);
      
          // saves the data.
          base.SetColumnValueAtRow (source, rowNum, data);
      
        }    
      
        protected override object 
           GetColumnValueAtRow(CurrencyManager source, int rowNum)
        {
          // gets the binary data if available
          object value = base.GetColumnValueAtRow(source, rowNum);
          // converts the data to binary if possible.
          byte[] data = value as byte[];
          // if the conversion failed then returns the base value.
          if(data == null)
              return value;
          // else
          else
          {
              // constructs binary data representation from the value.
              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:

    C#
    ...
    
    /// <summary>
    /// IsInputKey
    /// </summary>
    /// <remarks>
    /// Marks Ctrl+C combination as InputKey in order to catch its event later.
    /// </remarks>
    /// <param name= "keyData"> </param> 
    ///<returns> </returns>
    protected override bool IsInputKey(Keys keyData) 
    {
        if(keyData == ( Keys.Control | Keys.C)) 
            return true;
    
        return base.IsInputKey (keyData); 
    }
    
    ...
    
    ///<summary>
    ///OnKeyDown 
    ///</summary> 
    ///<remarks> 
    ///1. on Ctrl+C copies the DataRow into the Clipboard object. 
    ///2. on Ctrl+V pasts the data from the Clipboard
    ///      object into the DataSource = DataTable. 
    ///</remarks> 
    ///<param name= "e"> </param>
    protected override void OnKeyDown(KeyEventArgs e)
    {
        // if Ctrl+C
        if(e.KeyData == (Keys.C | Keys.Control))
        {
            DataTable table = (DataTable)this.DataSource;
            if(selectedDataGridRow < table.Rows.Count)
            {
                // saves the DataRow's data
                // under the name of the DatabaseDataGrid class.
                DataFormats.Format format = 
                  DataFormats.GetFormat(this.ToString());
    
                // copies the data to the clipboard.
                IDataObject data = new DataObject();
    
                DataRow row = table.Rows[selectedDataGridRow];
                data.SetData(format.Name, false, row.ItemArray);
                Clipboard.SetDataObject(data, false);
            }
        }
        // else if Ctrl+V
        else if(e.KeyData == (Keys.V | Keys.Control))
        {
            // retrieves the data from the clipboard
            IDataObject data = Clipboard.GetDataObject();
            string format = this.ToString();
    
            if(data.GetDataPresent(format))
            {
                object[] row = data.GetData(format) as object[];
                //adds new row to the underline 
                //DataSoruce - DataTable if needed.
                DataTable table = (DataTable)this.DataSource;
                if(table.Rows.Count < (selectedDataGridRow + 1))
                    table.Rows.Add(row);
                else
                    table.Rows[selectedDataGridRow].ItemArray = row;
            }
    
        }
        // else if Ctrl+S
        else if(e.KeyData == (Keys.S | Keys.Control))
        {
            SaveDatabaseTable();
        }
        // else
        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.

License

This article, along with any associated source code and files, is licensed under The Common Development and Distribution License (CDDL)


Written By
Other
Israel Israel
Uri Lavi is a development lead with extensive experience in Data Intensive, Business Compound, Distributed and Scalable Software Systems. Uri specializes in mentoring, coaching and consulting for complex software engineering topics, among which: Software Architecture, Design Patterns & Refactoring.

Comments and Discussions

 
GeneralMy vote of 5 Pin
Kanasz Robert26-Sep-12 4:58
professionalKanasz Robert26-Sep-12 4:58 
GeneralNew version from DBViewer Pin
sheyenrath15-Apr-10 5:39
sheyenrath15-Apr-10 5:39 
GeneralRe: New version from DBViewer Pin
Uri Lavi15-Apr-10 8:25
Uri Lavi15-Apr-10 8:25 
Generalsql2005 Pin
JM ® - DE CASTRO11-Apr-07 9:01
JM ® - DE CASTRO11-Apr-07 9:01 
GeneralRe: sql2005 Pin
Uri Lavi12-Apr-07 0:23
Uri Lavi12-Apr-07 0:23 
GeneralRe: sql2005 Pin
Muneeb Awan27-Apr-07 1:25
Muneeb Awan27-Apr-07 1:25 
QuestionIs there a simple fix for the named instance bug? [modified] Pin
wws3580115-Mar-07 5:48
wws3580115-Mar-07 5:48 
AnswerRe: Is there a simple fix for the named instance bug? [modified] Pin
Uri Lavi15-Mar-07 10:46
Uri Lavi15-Mar-07 10:46 
GeneralAppreciate it Pin
Soumiyah5-Mar-07 21:35
Soumiyah5-Mar-07 21:35 
Really helpful code. Helped my projet immensely

Soumiya
GeneralVery nice! Pin
David Roh30-Sep-05 9:15
David Roh30-Sep-05 9:15 
GeneralNamed Instances Bug Pin
Brian P. Adams18-Apr-05 7:37
Brian P. Adams18-Apr-05 7:37 
GeneralRe: Named Instances Bug Pin
Uri Lavi19-Apr-05 10:30
Uri Lavi19-Apr-05 10:30 

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.