带有智能完成功能的万能查询分析器的开发心得

 使用Microsoft Vistual Stdio最让人赞不绝口的是它的智能完成，而我们在写SQL语句的时候却还是最原始的方法一个一个字母的输入，我们必须记住每一张表名、字段名、表之间的关联字段信息等等，如何有效的提高开发速度，答案就是实现类似MS的智能完成功能。
        SpDevelop已经实现了数据库设计和基本的查询分析器，在这个基础上开发继续开发，现在已经实现了一下几个功能：

一、智能完成功能
         * 表/视图名称完成
         * 列名称完成
         * JOIN/JOIN ON 完成
         * 自动把关键字转换成大写
         * 关键字自动弹出

二、支持多种数据库
        查询分析器可以支持对MsSQL,Oracle,Sybase,MySQL,ProstgreSQL等多种数据库的操作。

三、多种导入导出功能
        可以将查询出来的结果导出成XML,Text,Excel等格式的数据文件

开发心得:
1、我们采用SharpDevelop中的开源编辑器ICSharpCode.TextEditor为编辑器，并指定为TSQL格式，这个开源的编辑器虽然比不上一些商用的，但基本够用而且速度比较快（打开一兆的SQL语句速度基本不影响), 应用代码如下：

1            TextEditorControl txtEditor = new TextEditorControl();
2            txtEditor.SetHighlighting("TSQL");  //实现SQL着色
3            txtEditor.ShowEOLMarkers = false;
4            txtEditor.ShowSpaces = false;
5            txtEditor.ShowTabs = false;
6            txtEditor.ShowInvalidLines = false;
7            txtEditor.Dock = DockStyle.Fill;
8            txtEditor.VRulerRow = 200;

2、SQL语句的分析，由于SQL语法比较有限，自己编写一个语法分析器。这个语法分析器的难点在于性能的优化上。
3、SpDevelop框架和其中数据库建模插件中已经提供了一些编程接口，获取数据库建模中数据库信息或者连接上各种数据库获取数据库信息。为了实现能够从各种数据库中灵活的获取信息，注意使用继承机制和单一模式来编程
4、导出数据,我们这里就没有再做重复的工作，采用了Rama Krishna一个比较好的导出类

  1# region Includes
  2
  3using System;
  4using System.Data;
  5using System.Web;
  6using System.Web.SessionState;
  7using System.IO;
  8using System.Text;
  9using System.Xml;
 10using System.Xml.Xsl;
 11using System.Threading;
 12
 13# endregion // Includes
 14
 15namespace MyLib.ExportData
 16{
 17    # region Summary
 18
 19    /**//// <summary>
 20    /// Exports datatable to CSV or Excel format.
 21    /// This uses DataSet's XML features and XSLT for exporting.
 22    /// 
 23    /// C#.Net Example to be used in WebForms
 24    /// ------------------------------------- 
 25    /// using MyLib.ExportData;
 26    /// 
 27    /// private void btnExport_Click(object sender, System.EventArgs e)
 28    /// {
 29    ///   try
 30    ///   {
 31    ///     // Declarations
 32    ///     DataSet dsUsers =  ((DataSet) Session["dsUsers"]).Copy( );
 33    ///     MyLib.ExportData.Export oExport = new MyLib.ExportData.Export("Web"); 
 34    ///     string FileName = "UserList.csv";
 35    ///     int[] ColList = {2, 3, 4, 5, 6};
 36    ///     oExport.ExportDetails(dsUsers.Tables[0], ColList, Export.ExportFormat.CSV, FileName);
 37    ///   }
 38    ///   catch(Exception Ex)
 39    ///   {
 40    ///     lblError.Text = Ex.Message;
 41    ///   }
 42    /// }    
 43    /// </summary>
 44
 45    # endregion // Summary
 46
 47    public class ExportData
 48    {        
 49        public enum ExportFormat : int {CSV = 1, Excel = 2}; // Export format enumeration            
 50        System.Web.HttpResponse response;
 51        private string appType;    
 52            
 53        public ExportData()
 54        {
 55            appType = "Web";
 56            response = System.Web.HttpContext.Current.Response;
 57        }
 58
 59            public ExportData(string ApplicationType)
 60        {
 61            appType = ApplicationType;
 62            if(appType != "Web" && appType != "Win") throw new Exception("Provide valid application format (Web/Win)");
 63            if (appType == "Web") response = System.Web.HttpContext.Current.Response;
 64        }
 65        
 66        ExportDetails OverLoad : Type#1#region ExportDetails OverLoad : Type#1
 67        
 68        // Function  : ExportDetails 
 69        // Arguments : DetailsTable, FormatType, FileName
 70        // Purpose     : To get all the column headers in the datatable and 
 71        //               exorts in CSV / Excel format with all columns
 72
 73        public void ExportDetails(DataTable DetailsTable, ExportFormat FormatType, string FileName)
 74        {
 75            try
 76            {                
 77                if(DetailsTable.Rows.Count == 0) 
 78                    throw new Exception("There are no details to export.");                
 79                
 80                // Create Dataset
 81                DataSet dsExport = new DataSet("Export");
 82                DataTable dtExport = DetailsTable.Copy();
 83                dtExport.TableName = "Values"; 
 84                dsExport.Tables.Add(dtExport);    
 85                
 86                // Getting Field Names
 87                string[] sHeaders = new string[dtExport.Columns.Count];
 88                string[] sFileds = new string[dtExport.Columns.Count];
 89                
 90                for (int i=0; i < dtExport.Columns.Count; i++)
 91                {
 92                    sHeaders[i] = dtExport.Columns[i].ColumnName;
 93                    sFileds[i] = dtExport.Columns[i].ColumnName;                    
 94                }
 95
 96                if(appType == "Web")
 97                    Export_with_XSLT_Web(dsExport, sHeaders, sFileds, FormatType, FileName);
 98                else if(appType == "Win")
 99                    Export_with_XSLT_Windows(dsExport, sHeaders, sFileds, FormatType, FileName);
100            }            
101            catch(Exception Ex)
102            {
103                throw Ex;
104            }            
105        }
106
107        #endregion // ExportDetails OverLoad : Type#1
108
109        ExportDetails OverLoad : Type#2#region ExportDetails OverLoad : Type#2
110
111        // Function  : ExportDetails 
112        // Arguments : DetailsTable, ColumnList, FormatType, FileName        
113        // Purpose     : To get the specified column headers in the datatable and
114        //               exorts in CSV / Excel format with specified columns
115
116        public void ExportDetails(DataTable DetailsTable, int[] ColumnList, ExportFormat FormatType, string FileName)
117        {
118            try
119            {
120                if(DetailsTable.Rows.Count == 0)
121                    throw new Exception("There are no details to export");
122                
123                // Create Dataset
124                DataSet dsExport = new DataSet("Export");
125                DataTable dtExport = DetailsTable.Copy();
126                dtExport.TableName = "Values"; 
127                dsExport.Tables.Add(dtExport);
128
129                if(ColumnList.Length > dtExport.Columns.Count)
130                    throw new Exception("ExportColumn List should not exceed Total Columns");
131                
132                // Getting Field Names
133                string[] sHeaders = new string[ColumnList.Length];
134                string[] sFileds = new string[ColumnList.Length];
135                
136                for (int i=0; i < ColumnList.Length; i++)
137                {
138                    if((ColumnList[i] < 0) || (ColumnList[i] >= dtExport.Columns.Count))
139                        throw new Exception("ExportColumn Number should not exceed Total Columns Range");
140                    
141                    sHeaders[i] = dtExport.Columns[ColumnList[i]].ColumnName;
142                    sFileds[i] = dtExport.Columns[ColumnList[i]].ColumnName;                    
143                }
144
145                if(appType == "Web")
146                    Export_with_XSLT_Web(dsExport, sHeaders, sFileds, FormatType, FileName);
147                else if(appType == "Win")
148                    Export_with_XSLT_Windows(dsExport, sHeaders, sFileds, FormatType, FileName);
149            }            
150            catch(Exception Ex)
151            {
152                throw Ex;
153            }            
154        }
155        
156        #endregion // ExportDetails OverLoad : Type#2
157
158        ExportDetails OverLoad : Type#3#region ExportDetails OverLoad : Type#3
159
160        // Function  : ExportDetails 
161        // Arguments : DetailsTable, ColumnList, Headers, FormatType, FileName    
162        // Purpose     : To get the specified column headers in the datatable and    
163        //               exorts in CSV / Excel format with specified columns and 
164        //               with specified headers
165
166        public void ExportDetails(DataTable DetailsTable, int[] ColumnList, string[] Headers, ExportFormat FormatType, 
167            string FileName)
168        {
169            try
170            {
171                if(DetailsTable.Rows.Count == 0)
172                    throw new Exception("There are no details to export");
173                
174                // Create Dataset
175                DataSet dsExport = new DataSet("Export");
176                DataTable dtExport = DetailsTable.Copy();
177                dtExport.TableName = "Values"; 
178                dsExport.Tables.Add(dtExport);
179
180                if(ColumnList.Length != Headers.Length)
181                    throw new Exception("ExportColumn List and Headers List should be of same length");
182                else if(ColumnList.Length > dtExport.Columns.Count || Headers.Length > dtExport.Columns.Count)
183                    throw new Exception("ExportColumn List should not exceed Total Columns");
184                
185                // Getting Field Names
186                string[] sFileds = new string[ColumnList.Length];
187                
188                for (int i=0; i < ColumnList.Length; i++)
189                {
190                    if((ColumnList[i] < 0) || (ColumnList[i] >= dtExport.Columns.Count))
191                        throw new Exception("ExportColumn Number should not exceed Total Columns Range");
192                    
193                    sFileds[i] = dtExport.Columns[ColumnList[i]].ColumnName;                    
194                }
195
196                if(appType == "Web")
197                    Export_with_XSLT_Web(dsExport, Headers, sFileds, FormatType, FileName);
198                else if(appType == "Win")
199                    Export_with_XSLT_Windows(dsExport, Headers, sFileds, FormatType, FileName);
200            }            
201            catch(Exception Ex)
202            {
203                throw Ex;
204            }            
205        }
206        
207        #endregion // ExportDetails OverLoad : Type#3
208
209        ExportDetails OverLoad : Type#3#region ExportDetails OverLoad : Type#3
210        
211        // Function  : ExportDetails 
212        // Arguments : DetailsTable, FormatType, FileName
213        // Purpose     : To get all the column headers in the datatable and 
214        //               exorts in CSV / Excel format with all columns
215
216        public void ExportDetails(DataTableCollection DetailsTables, ExportFormat FormatType, string FileName)
217        {
218            try
219            {
220                string NewFileName;
221        
222                foreach(DataTable DetailsTable in DetailsTables)
223                {
224                    if(DetailsTable.Rows.Count == 0) 
225                        throw new Exception("There are no details to export.");                
226                    
227                    NewFileName = FileName.Substring(0,FileName.LastIndexOf("."));
228                    NewFileName+= " - " + DetailsTable.TableName;
229                    NewFileName+= FileName.Substring(FileName.LastIndexOf("."));
230                                
231                    // Create Dataset
232                    DataSet dsExport = new DataSet("Export");
233                    DataTable dtExport = DetailsTable.Copy();
234                    dtExport.TableName = "Values"; 
235                    dsExport.Tables.Add(dtExport);    
236                
237                    // Getting Field Names
238                    string[] sHeaders = new string[dtExport.Columns.Count];
239                    string[] sFileds = new string[dtExport.Columns.Count];
240                
241                    for (int i=0; i < dtExport.Columns.Count; i++)
242                    {
243                        sHeaders[i] = dtExport.Columns[i].ColumnName;
244                        sFileds[i] = dtExport.Columns[i].ColumnName;                    
245                    }
246
247                    if(appType == "Web")
248                        Export_with_XSLT_Web(dsExport, sHeaders, sFileds, FormatType, NewFileName);
249                    else if(appType == "Win")
250                        Export_with_XSLT_Windows(dsExport, sHeaders, sFileds, FormatType, NewFileName);
251                }
252            }            
253            catch(Exception Ex)
254            {
255                throw Ex;
256            }            
257        }
258
259        #endregion //ExportDetails OverLoad : Type#4
260
261        Export_with_XSLT_Web#region Export_with_XSLT_Web
262
263        // Function  : Export_with_XSLT_Web 
264        // Arguments : dsExport, sHeaders, sFileds, FormatType, FileName
265        // Purpose   : Exports dataset into CSV / Excel format
266
267        private void Export_with_XSLT_Web(DataSet dsExport, string[] sHeaders, string[] sFileds, ExportFormat FormatType, string FileName)
268        {
269            try
270            {                
271                // Appending Headers
272                response.Clear();
273                response.Buffer= true;
274                
275                if(FormatType == ExportFormat.CSV)
276                {
277                    response.ContentType = "text/csv";
278                    response.AppendHeader("content-disposition", "attachment; filename=" + FileName);
279                }        
280                else
281                {
282                    response.ContentType = "application/vnd.ms-excel";
283                    response.AppendHeader("content-disposition", "attachment; filename=" + FileName);
284                }
285
286                // XSLT to use for transforming this dataset.                        
287                MemoryStream stream = new MemoryStream( );
288                XmlTextWriter writer = new XmlTextWriter(stream, Encoding.Default);
289                
290                CreateStylesheet(writer, sHeaders, sFileds, FormatType);
291                writer.Flush( ); 
292                stream.Seek( 0, SeekOrigin.Begin); 
293
294                XmlDataDocument xmlDoc = new XmlDataDocument(dsExport);
295                XslTransform xslTran = new XslTransform();                
296                xslTran.Load(new XmlTextReader(stream), null, null);
297                                
298                System.IO.StringWriter  sw = new System.IO.StringWriter();            
299                xslTran.Transform(xmlDoc, null, sw, null);
300                                    
301                //Writeout the Content                
302                response.Write(sw.ToString());                
303                sw.Close();    
304                writer.Close();
305                stream.Close();            
306                response.End();
307            }
308            catch(ThreadAbortException Ex)
309            {
310                string ErrMsg = Ex.Message;
311            }
312            catch(Exception Ex)
313            {
314                throw Ex;
315            }
316        }        
317        
318        #endregion // Export_with_XSLT 
319
320        Export_with_XSLT_Windows#region Export_with_XSLT_Windows 
321
322        // Function  : Export_with_XSLT_Windows 
323        // Arguments : dsExport, sHeaders, sFileds, FormatType, FileName
324        // Purpose   : Exports dataset into CSV / Excel format
325
326        private void Export_with_XSLT_Windows(DataSet dsExport, string[] sHeaders, string[] sFileds, ExportFormat FormatType, string FileName)
327        {
328            
329            try
330            {                
331                // XSLT to use for transforming this dataset.                        
332                MemoryStream stream = new MemoryStream( );
333                XmlTextWriter writer = new XmlTextWriter(stream, Encoding.UTF8);
334                
335                CreateStylesheet(writer, sHeaders, sFileds, FormatType);
336                writer.Flush( ); 
337                stream.Seek( 0, SeekOrigin.Begin); 
338
339                XmlDataDocument xmlDoc = new XmlDataDocument(dsExport);
340                XslTransform xslTran = new XslTransform();                
341                xslTran.Load(new XmlTextReader(stream), null, null);
342                                
343                System.IO.StringWriter  sw = new System.IO.StringWriter();            
344                xslTran.Transform(xmlDoc, null, sw, null);
345                                    
346                //Writeout the Content                                    
347                StreamWriter strwriter =  new StreamWriter(FileName,false,Encoding.Default);
348                strwriter.WriteLine(sw.ToString());
349                strwriter.Close();
350                
351                sw.Close();    
352                writer.Close();
353                stream.Close();    
354            }            
355            catch(Exception Ex)
356            {
357                throw Ex;
358            }
359        }        
360        
361        #endregion // Export_with_XSLT 
362
363        CreateStylesheet#region CreateStylesheet 
364
365        // Function  : WriteStylesheet 
366        // Arguments : writer, sHeaders, sFileds, FormatType
367        // Purpose   : Creates XSLT file to apply on dataset's XML file 
368
369        private void CreateStylesheet(XmlTextWriter writer, string[] sHeaders, string[] sFileds, ExportFormat FormatType)
370        {
371            try
372            {
373                // xsl:stylesheet
374                string ns = "http://www.w3.org/1999/XSL/Transform";    
375                writer.Formatting = Formatting.Indented;
376                writer.WriteStartDocument( );                
377                writer.WriteStartElement("xsl","stylesheet",ns);
378                writer.WriteAttributeString("version","1.0");
379                writer.WriteStartElement("xsl:output");
380                writer.WriteAttributeString("method","text");
381                writer.WriteAttributeString("version","4.0");
382                writer.WriteEndElement( );
383                
384                // xsl-template
385                writer.WriteStartElement("xsl:template");
386                writer.WriteAttributeString("match","/");
387
388                // xsl:value-of for headers
389                for(int i=0; i< sHeaders.Length; i++)
390                {
391                    writer.WriteString("\"");
392                    writer.WriteStartElement("xsl:value-of");
393                    writer.WriteAttributeString("select", "'" + sHeaders[i] + "'");
394                    writer.WriteEndElement( ); // xsl:value-of
395                    writer.WriteString("\"");
396                    if (i != sFileds.Length - 1) writer.WriteString( (FormatType == ExportFormat.CSV ) ? "," : "    " );
397                }
398                                
399                // xsl:for-each
400                writer.WriteStartElement("xsl:for-each");
401                writer.WriteAttributeString("select","Export/Values");
402                writer.WriteString("\r\n");                
403                
404                // xsl:value-of for data fields
405                for(int i=0; i< sFileds.Length; i++)
406                {                    
407                    writer.WriteString("\"");
408                    writer.WriteStartElement("xsl:value-of");
409                    writer.WriteAttributeString("select", sFileds[i]);
410                    writer.WriteEndElement( ); // xsl:value-of
411                    writer.WriteString("\"");
412                    if (i != sFileds.Length - 1) writer.WriteString( (FormatType == ExportFormat.CSV ) ? "," : "    " );
413                }
414                                
415                writer.WriteEndElement( ); // xsl:for-each
416                writer.WriteEndElement( ); // xsl-template
417                writer.WriteEndElement( ); // xsl:stylesheet
418                writer.WriteEndDocument( );                    
419            }
420            catch(Exception Ex)
421            {
422                throw Ex;
423            }
424        }
425        
426        #endregion // WriteStylesheet
427
428    }
429}

SOS:
1、如何在SQL Server Management Studio Express 2005上开发插件。

如果有开发这方面兴趣的哥们，一块到本园的SpDevelop开发团队来混吧(感谢dudu)，欢迎大家的加入！

       现在本插件基本已经可以满足一些日常的操作，可到 www.spdevelop.com下载。