Introduction
From time to time programmers come across users who, for whatever reason, seem to actually want to code T-SQL in order to find the information that they need. In an SQL Server environment, a common way to give them this capability is to install the SQL Query Analyzer product. Beginning with SQL Server 2005 however, Query Analyzer has been rolled in to the SQL Server Management Studio product which introduces a slew of possibilities that can put the enterprise data at risk.
This tool provides the users with their query capability, while minimizing the database exposure to those "accidental" issues.
Please note that this code does not contain any copyright because it cannot be copyrighted. The reason for this is that this code is an obvious, logical implementation utilizing the .NET 2.0 Framework to accomplish its task. Many programmers before me have created virtually identical tools (using a myriad of languages) to accomplish precisely the same thing, and there will be many after who never read this article who will create a similar tools as well.
Background
The "inspiration" for this tool came from observing users at one of my work sites who were originally given SQL Query Analyzer on the desktop to perform lookups from the enterprise database. Although I'm opposed to giving users a tool such as this when other ("better") options exist, this user community has a lot of history surrounding the decision to go with SQL Query Analyzer.
Unfortunately, the training they receive in relational databases comes from a book on T-SQL that they are given when they first arrive in the job position. Note that these are users, not IT folks of any kind. Most of them are terrified of learning SQL so they use a brief library of sample SQL code that was handed to them by one of the kinder IT folks.
If I can get on my soap box for a moment, I would like to point out that the supporting IT unit should be concentrating on providing the user community with the information that they need without resorting to the users writing their own programs. Ok, off the soap box.
Using the code
Once you dive into the code you'll see that it's really quite simple. AnSQLConnectionobject handles the database connection, anSqlCommandobject handles the command to be executed, and the execution of the command is handled through theSqlDataAdapterobject.
The only bit of trickery was in allowing "batch" command execution such as:
USEdatabasenameGOSELECTsomedataFROMatableWHEREafield = aValueICareAboutSince the wordGO(case insensitive) is not an actual SQL language element, the database will throw an Exception which in turn causes theSqlCommandobject to throw an Exception. My solution around this one was to break the input into individual commands that were separated with theGOcommand, then feed them to the database one at a time. The results are each collected in their ownDataSetobject, and all of the resultDataSetobjects are combined into the final returnDataSetobject, which is then sent on for display either by Microsoft Excel or a result window instance. Here's the code to split the commands:
///Split a query batch into individual queries.//////Query to process///Array of strings containing queries.privatestring[] FragmentQuery(stringQuery){//Split the query into individual linesRegex r =newRegex("\r\n");string[] lines = r.Split(Query);//Count the number of GO'sintgocnt =0;foreach(stringsinlines)if(s.Trim().ToLower() =="go") gocnt++;//Create an array of string of the appropriate sizestring[] ret =newstring[gocnt +1]; gocnt =0;//Construct the queriesStringBuilder sb =newStringBuilder();for(intn =0; n < lines.Length; n++) {strings = lines[n];if(s.Trim().ToLower() =="go") {if(sb.Length >0) { ret[gocnt++] = sb.ToString(); sb =newStringBuilder(); s =string.Empty; } }if(s.Trim().Length >0) { sb.Append(s); sb.Append("\r\n"); } }//Catch the last oneif(sb.Length >0) ret[gocnt] = sb.ToString();returnret;}Once the commands have been split, the following code is used for execution:
///Execute a database query.//////Query to execute.///DataSet containing resultspublicDataSet ExecuteQuery(stringQuery){//Separate the queriesstring[] cmds = FragmentQuery(Query);//Get our return DataSetDataSet ret =newDataSet();//Get our execution DataSetsDataSet[] exec =newDataSet[cmds.Length];for(inti =0; i < exec.Length; i++) exec[i] =newDataSet(); Exception e =null;using(SqlCommand cm =newSqlCommand()) {//Set up the commandcm.Connection = _Conn; cm.CommandType = CommandType.Text; SqlDataAdapter adap =newSqlDataAdapter(cm);//Open the connectiontry{if(_Conn.State != ConnectionState.Open) _Conn.Open();//Spin the commandsfor(intn =0; n < cmds.Length; n++) { cm.CommandText = cmds[n]; adap.Fill(exec[n]); } }catch(Exception ex) { e = ex; }finally{if(_Conn.State == ConnectionState.Open) _Conn.Close(); } }//Re-throw the exception if we had oneif(e !=null)thrownewException("Error during execute", e);//Package the resultsfor(intn =0; n < exec.Length; n++) {for(intj =0; j < exec[n].Tables.Count; j++) { DataTable T = exec[n].Tables[j].Copy(); T.TableName =string.Format("Query{0}-Result{1}", (n +1), (j +1)); ret.Tables.Add(T); } }//Return the resultsif(ret ==null)returnnull;returnret.Copy();}Points of Interest
The program has the capability of keeping multiple query result windows open at one time. That's because each of the result windows renders aDataSetobject within multipleDataGridViewobjects located each on their own tab, easily accomplished by:
//Spin through the tablesforeach(DataTable tin_DS.Tables){ rescnt++; TabPage tp =newTabPage(t.TableName); DataGridView tpdg =newDataGridView(); tpdg.AllowDrop =false; tpdg.AllowUserToAddRows =false; tpdg.AllowUserToDeleteRows =false; tp.Controls.Add(tpdg); tpdg.Dock = DockStyle.Fill; tpdg.DataSource = t; tpdg.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells; tabA.TabPages.Add(tp);}Which gives us:
One of the most common activities is to copy the results of a query into a Microsoft Excel spreadsheet. Rather than have the users do this, I utilize the Excel PIA (Primary Interop Assembly) from Microsoft for (in the case of my users) Microsoft Office 2003, which allows the program to write the output directly to an Excel workbook instead of the regular results window using this code:
///Save the results in an Excel workbook.//////DataSet to transform.privatevoidPlantItInExcel(DataSet DSource){ Excel.Application oExcel; Excel._Workbook oWB; Excel._Worksheet oWS; Excel.Range oRange;try{//Start Excel and get application objectoExcel =newExcel.Application(); oExcel.Visible =true;//Get a new workbookoWB = (Excel._Workbook) (oExcel.Workbooks.Add(System.Reflection.Missing.Value));//Remove the default worksheetsfor(intn = oWB.Worksheets.Count; n >1; n--) ((Excel.Worksheet)oWB.Sheets[n]).Delete();//Spin the tablesboolFirstIn =true;for(intn = DSource.Tables.Count -1; n > (-1); n--) {//Pick up the tableDataTable t = DSource.Tables[n];if(FirstIn) { oWS = (Excel._Worksheet)oWB.ActiveSheet; FirstIn =false; }else{ oWS = (Excel._Worksheet)(oExcel.Worksheets.Add (Missing.Value, Missing.Value, Missing.Value, Missing.Value)); } oWS.Name = t.TableName;//Set the table headers from the column namesintbaserow =1;for(intc =0; c < t.Columns.Count; c++) oWS.Cells<BASEROW, class=code-digit (c +1)> = t.Columns[c].ColumnName;//Assign the headers as boldoRange = oWS.get_Range(oWS.Cells<BASEROW, class=code-digit1>, oWS.Cells<BASEROW, t.Columns.Count>); oRange.Font.Bold =true;//Create a string array to hold the data rowsstring[,] tData =newstring[t.Rows.Count, t.Columns.Count]; baserow++;introw =0;intcol =0;foreach(DataRow drint.Rows) { col =0;foreach(DataColumn dcint.Columns) { tData[row, col] = (dr[col] == DBNull.Value) ?string.Empty : Convert.ToString(dr[col]); col++; } row++; } oRange = oWS.get_Range(oWS.Cells<BASEROW, class=code-digit1>, oWS.Cells<BASEROW class=code-digit +1, t.Columns.Count>); oRange.Value2 = tData;//Autofit the columnsoRange.EntireColumn.AutoFit(); }//Make sure Excel is visible and give the user controloExcel.Visible =true; oExcel.UserControl =true; }catch(Exception ex) { MessageBox.Show(ex.Message); }}Which gives us:
I've also incorporated drag & drop for the query text box so that the user can cut and paste text from another application or drag a file directly to the textbox as well as execution of selected text from within the textbox. All drag & drop operations append to the text box contents rather than replace it.
Of course the user has the standard file operations (open, save, save as) from the file menu.
Because of the intended use of this tool, I've also included a list of SQL language elements that are not permitted:
///The Bad Words list.///privateconststring_BadWords ="(\b|^)(create|alter|insert|truncate| delete|drop|update|grant|revoke|dbcc|exec|execute)(\b|$)";This Regular Expression will locate the target word at the beginning of a line, end of a line, or anywhere in the line if it's bordered by a whitespace character. Readers familiar with SQL will instantly recognize the danger of having a untrained user issuing these commands. Any SQL scripts to be deployed with the tool can be placed in the Scripts folder. The download source contains only theAdventureWorksquery example shown above.
Deployment of the tool
The download uses anApp.configfile for the primary database connection, and you should definitely change it. If you were to actually deploy this tool to users, you should have the connection hard-coded (or even further obfuscated) to avoid any "explorative" users from changing the connection string.
Error handling in the demo is very simplistic. Errors are presented in a dialog box that the user simply closes. Not a best practice for a production deployment, but on that note, see my comments above.
History
- v1.0 - addendum - Added compiled program as requested.
- v1.0 - Initial release.
License
This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.
A list of licenses authors might use can be foundhere
About the Author
| KChandos |
I am a Senior Technical Consultant (or any other silly title that you'd like) for a small consulting firm in Washington State. As of this writing (2007) I've been programming professionally for just shy of 26 years and programming in general for just shy of 34 years. I first started programming when I was 10 (yes, there were computers back then, just not personal computers) and I have experience in 8 dialects of BASIC, 3 generations of FORTRAN, 4 dialects of COBOL, C, C++, C#, FOCUS, SQL, a whole slew of scripting languages, Mark IV, 6502 and 80x86 assembly, BAL (both with and without ASSIST), and a few others ranging on systems from home-built on up to IBM mainframes (I've been around the block more than once). Now .Net pays the bills and I'm having even more fun than when I got started!
|
