×

Loading...
Ad by
  • 最优利率和cashback可以申请特批,好信用好收入offer更好。请点链接扫码加微信咨询,Scotiabank -- Nick Zhang 6478812600。
Ad by
  • 最优利率和cashback可以申请特批,好信用好收入offer更好。请点链接扫码加微信咨询,Scotiabank -- Nick Zhang 6478812600。

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

本文发表在 rolia.net 枫下论坛使用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}

现在本插件基本已经可以满足一些日常的操作,可到 www.spdevelop.com下载。更多精彩文章及讨论,请光临枫下论坛 rolia.net
Report

Replies, comments and Discussions:

  • 工作学习 / 专业技术讨论 / 带有智能完成功能的万能查询分析器的开发心得
    本文发表在 rolia.net 枫下论坛使用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}

    现在本插件基本已经可以满足一些日常的操作,可到 www.spdevelop.com下载。更多精彩文章及讨论,请光临枫下论坛 rolia.net
    • Your site doesn't support Firefox.
    • 广告贴。产品级别:低级。技术含量:初级。潜在市场:没有。鉴定完毕。
      • 兄弟别这样打击人吧,至少自主研发的精神是值得肯定的。
        • 不好意思,忠言逆耳呀。
    • 我只用UltraEdit, 一天写两千行程序不成问题。文本编辑器可大块拷贝,用熟练了比什么工具都好。
      • because your code is "copy-paste"-style....
        • that's true for people writing SQL all day
        • 用Visual Studio的是Drag & Drop style。:)
          • VS 2005还有Code Snippet功能,怎么也是比UltraEdit强。
      • 是啊,我也觉得我住的这口井是天底下最棒的地方
        • whatever...I dare to compare with anyone working with fancy tools
          • 这点我支持你。好的程序员不是用鼠标编程的。那些fancy tool只会制造大量junk code,严重影响程序的效率,制造升级硬件的冠冕堂皇的理由。:)
            • 请问Visual Studio 2005是不是你所谓的Fancy Tool?如果是,请问它制造了那些垃圾代码?请举例说明。
              • 呵呵,随便找一段machine generated code看看就行了。
                • 这些machine generated code难道不需要的吗?请给我看一段真的不需要的代码,我就服你。还有你懂.Net吗?如果不懂,就不和你不谈了。还有随便评论一个不懂的技术,或工具,是不成熟的行为。
                  • 不是说不需要,而是不精简。顺便告诉你,Visual Studio不是只能用来做.NET的。:)
                    • 我在谈VS 2005, VS 2005是用来做围绕.Net的项目。.Net里面有包含许多东西,VS 2005就是为开发.net的项目服务的。还有什么叫精简,我看VS 2005产生的代码够精简了,请举例说那段代码是不精简的。
                      • 呵呵,俺服了你了,行不?您自个儿enjoy吧。
                        • 不是自己Enjoy的问题,因为看到许多人好像在说用Notepad编程就怎么样好,水平高什么的,然后贬低那些使用所谓的Fancy Tools的人。其实现在程序开发,这些所谓的Fancy Tools是很好的工具,程序厂商化很多钱开发的,熟练使用它们,可以省很多时间。
                          • 很多时候,如果缺省提供的选项不恰好满足需要,简单的solution需要绕一大圈来实现,变成coding nightmare。能用notepad编程的人肯定技术上比不会的人要强多了,毋庸置疑。善于利用现成的工具也是好的程序员所必须的。如果是完全依赖于
                            那些fancy tool写程序,那根本就不能算做程序员。呵呵,我见过不用鼠标就不会复制文件的程序员;也见过因为只会用工具编程,调用现成object和object method导致出现recursive query的。

                            不多说了,大家都是混饭吃,爱用啥用啥。不过,话说回来,我面试人的时候,如果不会用text editor编程的人,是不会被考虑的。:(
                            • 我是针对你说“fancy tool只会制造大量junk code”问你的。至于你所的一些现象不是工具问题,是程序员水平的问题。在对Text Editor和所谓的Fancy Tools熟练程度一样的情况下,Fancy Tools优势应该是明显的。
                              • 同意后面2个句号。我通常在需要的时候,用那些tool生产模板,然后在text editor下面手工删除那些没用的东西,精简有用的,然后继续剩下的编程。
                                • 好了,到此为之吧。大家都保留自己的看法吧。很高兴和你交流,谢谢。
            • 忘了说我只作后台开发,没有界面设计,我用过的工具要么反应迟钝,要么格式化风格不符合我的习惯,所以我还是用文本编辑器顺手。我同事还有只用vi的呢。
    • 不错不错,兄弟佩服一下,rolia的红眼病实在是太多
    • 太多代码,都没怎么看明白到底是做什么的,不过如果是 code insight for sql,这个已经不是新东西了
      早就有内嵌在 sql server 里的 plug in 可以实现 code insight of sql query

      另外,如果是在 vs.net 里,你这个东西前途不大, linq 出来后估计就没有太大的意义了。

      再者,vs.net project source code 里含有大量的 sql query 不太可能吧,大点的项目多数都用 sp 啦

      你的网站是在太简单,第一眼看上去完全不知道你的东西是干嘛的。如果你真的想在 shareware 上继续,需要考虑把网站的布局和内容。(btw, in FF, your website is screwed up)