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 CPOL
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
TSqlConsole.suo
using System;
using System.Windows.Input;
using System.Data.SqlClient;
using System.Data;
using System.Collections.Generic;

namespace TestParseTSql
{
    public class SqlHandler
    {
        #region Commands
        public static RoutedUICommand ParseCommand;
        public static RoutedUICommand ExecuteCommand;
        public static RoutedUICommand ConnectCommand;
        public static RoutedUICommand DisconnectCommand;

        static SqlHandler()
        {
            InputGestureCollection parseGestures = new InputGestureCollection();
            parseGestures.Add(new KeyGesture(Key.F6));
            ParseCommand = new RoutedUICommand("Parse", "Parse", typeof(SqlHandler), parseGestures);

            InputGestureCollection executeGestures = new InputGestureCollection();
            executeGestures.Add(new KeyGesture(Key.F5));
            ExecuteCommand = new RoutedUICommand("Execute", "Execute", typeof(SqlHandler), executeGestures);

            ConnectCommand = new RoutedUICommand("Connect", "Connect", typeof(SqlHandler));

            DisconnectCommand = new RoutedUICommand("Disconnect", "Disconnect", typeof(SqlHandler));
        }
        #endregion

        private SqlConnection conn;
        private SqlCommand cmd;
        private SqlDataAdapter adapter;
        private List<SqlError> errors;

        public string ConnectionString
        {
            get { return conn.ConnectionString; }
            private set { conn.ConnectionString = value; }
        }

        public bool IsConnected
        {
            get { return conn.State == ConnectionState.Open; }
        }

        public SqlHandler()
        {
            conn = new SqlConnection();
            cmd = new SqlCommand("", conn);
            adapter = new SqlDataAdapter(cmd);
            errors = new List<SqlError>(5);

            ConnectionString = "Data Source=; Initial Catalog=; Integrated Security=SSPI";
            conn.FireInfoMessageEventOnUserErrors = true; //when true, the SqlCommand object will not throw an Exception when errors occur
            conn.InfoMessage += new SqlInfoMessageEventHandler(conn_InfoMessage);
        }

        public void Connect(string connStr)
        {
            if (IsConnected)
                Disconnect();
            conn.ConnectionString = connStr;
            conn.Open();
        }

        public void Disconnect()
        {
            conn.Close();
        }

        public DataTable Execute(string sqlText, out SqlError[] errorsArray)
        {
            if (!IsConnected)
                throw new InvalidOperationException("Can not execute Sql query while the connection is closed!");

            errors.Clear();
            cmd.CommandText = sqlText;
            DataTable tbl = new DataTable();
            adapter.Fill(tbl);
            errorsArray = errors.ToArray();
            return tbl;
        }

        public SqlError[] Parse(string sqlText)
        {
            if (!IsConnected)
                throw new InvalidOperationException("Can not parse Sql query while the connection is closed!");

            errors.Clear();
            cmd.CommandText = "SET PARSEONLY ON";
            cmd.ExecuteNonQuery();

            cmd.CommandText = sqlText;
            cmd.ExecuteNonQuery(); //conn_InfoMessage is invoked for every error, e.g. 2 times for 2 errors
            
            cmd.CommandText = "SET PARSEONLY OFF";
            cmd.ExecuteNonQuery();

            return errors.ToArray();
        }

        private void conn_InfoMessage(object sender, SqlInfoMessageEventArgs e)
        {
            //ensure that all errors are caught
            SqlError[] errorsFound = new SqlError[e.Errors.Count];
            e.Errors.CopyTo(errorsFound, 0);
            errors.AddRange(errorsFound);
        }
    }
}

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)

Share

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 | Terms of Use | Mobile
Web01 | 2.8.150327.1 | Last Updated 25 Jun 2012
Article Copyright 2012 by Andre Pieterse
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid