Click here to Skip to main content
Click here to Skip to main content
Articles » Database » Database » General » Downloads
 
Add your own
alternative version

Parse Transact SQL to Check Syntax

, 25 Jun 2012
This article demonstrates how to implement a parse function within a C# WPF application.
TSqlConsole.zip
TSqlConsole
Images
application_xp_terminal.png
bug.png
bullet_blue.png
bullet_red.png
cut.png
database.png
database_connect.png
database_table.png
disconnect.png
disk.png
folder_page.png
lightning.png
page_white_copy.png
page_white_paste.png
Properties
Settings.settings
TSqlConsole.csproj.user
using System.Windows;
using System.Windows.Input;
using Microsoft.Win32;
using System.IO;
using System.Data.SqlClient;
using System;
using System.Windows.Media.Imaging;
using System.Data;

namespace TestParseTSql
{
    public partial class MainWindow : Window
    {
        private SqlHandler sqlHandler;

        public MainWindow()
        {
            InitializeComponent();
            sqlHandler = new SqlHandler();
        }

        #region Private Methods
        private void Connect()
        {
            try
            {
                ConnectWindow connWin = new ConnectWindow();
                connWin.ConnectionString = sqlHandler.ConnectionString;
                if (connWin.ShowDialog().Value == false)
                    return;

                sqlHandler.Connect(connWin.ConnectionString);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Connection Error", MessageBoxButton.OK, MessageBoxImage.Error);
            }
            finally
            {
                UpdateUIStatus();
            }
        }

        private void UpdateUIStatus()
        {
            if (sqlHandler.IsConnected)
            {
                BitmapImage logo = new BitmapImage();
                logo.BeginInit();
                logo.UriSource = new Uri("Images/bullet_blue.png", UriKind.Relative);
                logo.EndInit();
                connStatusIcon.Source = logo;
                txtStatus.Text = "Connected:  ";
                txtConnection.Text = sqlHandler.ConnectionString;
            }
            else
            {
                BitmapImage logo = new BitmapImage();
                logo.BeginInit();
                logo.UriSource = new Uri("Images/bullet_red.png", UriKind.Relative);
                logo.EndInit();
                connStatusIcon.Source = logo;
                txtStatus.Text = "Not Connected";
                txtConnection.Text = string.Empty;
            }
        }
        #endregion

        #region Events
        private void Window_Loaded(object sender, RoutedEventArgs e)
        {
            Connect();
        }
        #endregion

        #region Commands
        private void Open_Executed(object sender, ExecutedRoutedEventArgs e)
        {
            OpenFileDialog fileChooser = new OpenFileDialog();
            fileChooser.Filter = "Sql files|*.sql|Text files|*.txt|All files|*.*";
            fileChooser.CheckPathExists = true;
            fileChooser.Multiselect = false;
            fileChooser.Title = "Choose a file containing T-SQL code to open";
            if (fileChooser.ShowDialog().Value == false)
                return;

            using (StreamReader sr = new StreamReader(fileChooser.FileName))
            {
                txtCode.Text = sr.ReadToEnd();
                sr.Close();
            }
        }

        private void Save_Executed(object sender, ExecutedRoutedEventArgs e)
        {
            SaveFileDialog fileChooser = new SaveFileDialog();
            fileChooser.Filter = "Sql files|*.sql|Text files|*.txt|All files|*.*";
            fileChooser.AddExtension = true;
            fileChooser.OverwritePrompt = true;
            fileChooser.Title = "Choose a path and file name";
            if (fileChooser.ShowDialog().Value == false)
                return;

            using (StreamWriter sw = new StreamWriter(fileChooser.FileName))
            {
                sw.Write(txtCode.Text);
                sw.Close();
            }
        }

        private void Connect_Executed(object sender, ExecutedRoutedEventArgs e)
        {
            Connect();
        }

        private void Disconnect_Executed(object sender, ExecutedRoutedEventArgs e)
        {
            try
            {
                sqlHandler.Disconnect();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Connection Error", MessageBoxButton.OK, MessageBoxImage.Error);
            }
            finally
            {
                UpdateUIStatus();
            }
        }

        private void Parse_Executed(object sender, ExecutedRoutedEventArgs e)
        {
            try
            {
                SqlError[] errors = sqlHandler.Parse(txtCode.Text);
                errorsGrid.ItemsSource = errors;
                errorsExpander.IsExpanded = (errors.Length != 0);
                if (errors.Length == 0)
                    MessageBox.Show("Parse successful", "Information", MessageBoxButton.OK, MessageBoxImage.Information);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Execute Error", MessageBoxButton.OK, MessageBoxImage.Error);
            }
        }

        private void Execute_Executed(object sender, ExecutedRoutedEventArgs e)
        {
            try
            {
                SqlError[] errors;
                DataTable result = sqlHandler.Execute(txtCode.Text, out errors);
                errorsGrid.ItemsSource = errors;
                errorsExpander.IsExpanded = (errors.Length != 0);

                if (result.Rows.Count > 0)
                    new ResultWindow(result).Show();
                else if (errors == null)
                    MessageBox.Show("No results returned.", "Query Executed", MessageBoxButton.OK, MessageBoxImage.Information);
            }
            catch (Exception ex)
            {
                /* Please Note:
                 * This catch block is essentially redundant because the SqlConnection object's property FireInfoMessageEventOnUserErrors within SqlHandler is set to true.
                 * When FireInfoMessageEventOnUserErrors = false:  Errors will be raised as Exceptions (and caught by this catch block, in this instance).
                 * When FireInfoMessageEventOnUserErrors = true:  Errors will be raised as an Event (InfoMessage).
                 * SqlHandler encapsulates and hides the implementation of the event, and both the SqlHandler.Execute and SqlHandler.Parse methods will return these errors caught through the event
                */
                MessageBox.Show(ex.Message, "Execute Error", MessageBoxButton.OK, MessageBoxImage.Error);
            }
        }

        private void IsConnected_Executed(object sender, CanExecuteRoutedEventArgs e)
        {
            if (IsLoaded)
                e.CanExecute = sqlHandler.IsConnected;
        }

        private void Close_Executed(object sender, ExecutedRoutedEventArgs e)
        {
            this.Close();
        }
        #endregion
    }
}

By viewing downloads associated with this article you agree to the Terms of Service and the article's licence.

If a file you wish to view isn't highlighted, and is a text file (not binary), please let us know and we'll add colourisation support for it.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

About the Author

Andre Pieterse
Software Developer
South Africa South Africa
I hold a batchelor's degree in BCOM Economics and Informatics.
 
I am currently working for a software company developing and supporting business applications for major financial institutions.
 
My interests lie in expanding my knowledge of object oriented design, C# .Net, WPF, WCF and web technologies.

| Advertise | Privacy | Mobile
Web01 | 2.8.140721.1 | Last Updated 25 Jun 2012
Article Copyright 2012 by Andre Pieterse
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid