ERP 高级查询(Advanced Query)设计与实现 SQL语句解析成LLBL Gen ORM代码
对于开始接触基于ORM技术开发的ERP程序，在相当长的时间内还是会考虑SQL语句，而不是ORM查询。即便是在很熟悉ORM查询，也不如对SQL语句的了解程度。于是想做出一个查询工具，把SQL语句转化为C#代码，用于查询。
这样的程序片段在很多地方都需用用到。
比如SQL语句
SELECT RECNUM ,CCY ,DESCRIPTION ,SUSPENDED ,DEFAULT_RATE FROM [Currency]
查询当前的货币及其名称，默认汇率。打开高级查询功能，把这几个字段拖动到SQL语句窗口中，点击按钮Execute即可在结果窗格中看到生成的ORM语句片段。
生成的LLBL Gen Pro代码片段
ICurrencyManager currencyManager = ClientProxyFactory.CreateProxyInstance<ICurrencyManager>(); ExcludeIncludeFieldsList fieldlist = new ExcludeIncludeFieldsList(false); fieldlist.Add(CurrencyFields.RECNUM); fieldlist.Add(CurrencyFields.CCY); fieldlist.Add(CurrencyFields.DESCRIPTION); fieldlist.Add(CurrencyFields.SUSPENDED); fieldlist.Add(CurrencyFields.DEFAULT_RATE); CurrencyEntity currency = currencyManager.GetCurrency(this., null, fieldlist);
再来分析一下，如何实现这个过程。
1 树结点多选功能
如上图中所示，我选中树节点Currency中的多个字段，然后把它拖动到SQL语句窗口中，自动生成SQL查询语句。这里要实现树节点多选功能。WinForms内置的树控件不支持此功能，需要另找控件。
这里，我选用CodeProject上的控件MultiSelectTreeView，它的功能用法如下介绍所示
Summary description for MultiSelectTreeView. The MultiSelectTreeView inherits from System.Windows.Forms.TreeView to allow user to select multiple nodes. The underlying comctl32 TreeView doesn't support multiple selection. Hence this MultiSelectTreeView listens for the BeforeSelect && AfterSelect events to dynamically change the BackColor of the individual treenodes to denote selection. It then adds the TreeNode to the internal arraylist of currently selectedNodes after validation checks. The MultiSelectTreeView supports 1) Select + Control will add the current node to list of SelectedNodes 2) Select + Shitft will add the current node and all the nodes between the two (if the start node and end node is at the same level) 3) Control + A when the MultiSelectTreeView has focus will select all Nodes.
2 鼠标拖动编程
树结点中设置AllowDrag，加入事件响应方法ItemDrag
private void treeTables_ItemDrag(object sender, ItemDragEventArgs e) { if (e.Button == MouseButtons.Left) { DoDragDrop(e.Item, DragDropEffects.Copy); } }
要拖进的SQL TextEdtor，则对它加入事件响应方法
private void txtSqlScript_DragDrop(object sender, DragEventArgs e) { TreeNode draggedNode = (TreeNode)e.Data.GetData(typeof(TreeNode)); if (draggedNode.Tag == "Column") { List<string> columns=new List<string>(); foreach(TreeNode node in treeTables.SelectedNodes) { columns.Add(node.Text.Substring(0, node.Text.IndexOf("(") )); } string tableName = draggedNode.Parent.Text; string sql = string.Format("SELECT {0} FROM [{1}] ", string.Join(",",columns), tableName); txtSqlScript.InsertText(sql); } else if (draggedNode.Tag == "Table") { txtSqlScript.InsertText(string.Format("[{0}]",draggedNode.Text)); } }
这几句话，根据节点代表的含义(字段，表名)，来构造SQL查询语句。
3 SQL语句解析
Visual Studio Database Edition提供了SQL语句解析功能，引用这两个程序集，实现类似的代码
public static string Execute(QueryDataSource dataSource,string sql) { string csharpCode = string.Empty; IList<ParseError> Errors; var parser = new TSql100Parser(false); StringReader reader = new StringReader(sql); IScriptFragment result = parser.Parse(reader, out Errors); var Script = result as TSqlScript; foreach (var ts in Script.Batches) { foreach (var st in ts.Statements) { IterateStatement(st,ref csharpCode,dataSource); } } return csharpCode; }
此方法根据传入的SQL语句，返回C#代码。LLBL Gen 是一项ORM技术，ORM首要解决的问题是C#代码如何转化为对数据库操作的SQL语句。在这里，我把这个过程反过来，根据SQL语句，得到C#代码。
数据表Currency 对应的程序中的实体类型是CurrencyEntity，它的字段RECNUM对应于C#实体类型CurrencyEntity中的Recnum，这个映射关系存储于生成的C#代码中。
/// <summary>Inits CurrencyEntity's mappings</summary> private void InitCurrencyEntityMappings() { this.AddElementMapping( "CurrencyEntity", "MIS", @"dbo", "Currency", 29 ); this.AddElementFieldMapping( "CurrencyEntity", "AcctApForex", "ACCT_AP_FOREX", true, "NVarChar", 30, 0, 0, false, "", null, typeof(System.String), 0 ); this.AddElementFieldMapping( "CurrencyEntity", "AcctArForex", "ACCT_AR_FOREX", true, "NVarChar", 30, 0, 0, false, "", null, typeof(System.String), 1 ); this.AddElementFieldMapping( "CurrencyEntity", "ApInvoBal", "AP_INVO_BAL", true, "Decimal", 0, 2, 16, false, "", null, typeof(System.Decimal), 2 ); this.AddElementFieldMapping( "CurrencyEntity", "ApLinvoBal", "AP_LINVO_BAL", true, "Decimal", 0, 2, 16, false, "", null, typeof(System.Decimal), 3 ); this.AddElementFieldMapping( "CurrencyEntity", "ApLnetBal", "AP_LNET_BAL", true, "Decimal", 0, 2, 16, false, "", null, typeof(System.Decimal), 4 ); this.AddElementFieldMapping( "CurrencyEntity", "ApLopenBal", "AP_LOPEN_BAL", true, "Decimal", 0, 2, 16, false, "", null, typeof(System.Decimal), 5 ); this.AddElementFieldMapping( "CurrencyEntity", "ApNetBal", "AP_NET_BAL", true, "Decimal", 0, 2, 16, false, "", null, typeof(System.Decimal), 6 ); }
运行时，只需要调用此方法即可得到它们的映射关系，以实现将SQL语句的object(table,column)转化为C#程序对应的object(Entity,property)。
4 Debug功能的实现
为了实现即使运行效果，将上面的生成的SQL语句，编译成C#程序集，再执行程序集，得到返回的结果。
CodeDomProvider codeProvider = null; if (Language == LanguageType.CSharp) codeProvider = new CSharpCodeProvider(); else if (Language == LanguageType.VB) codeProvider = new VBCodeProvider(); //create the language specific code compiler ICodeCompiler compiler = codeProvider.CreateCompiler(); //add compiler parameters CompilerParameters compilerParams = new CompilerParameters(); compilerParams.CompilerOptions = "/target:library"; // you can add /optimize compilerParams.GenerateExecutable = false; compilerParams.GenerateInMemory = true; compilerParams.IncludeDebugInformation = false; // add some basic references compilerParams.ReferencedAssemblies.Add("mscorlib.dll"); compilerParams.ReferencedAssemblies.Add("System.dll"); compilerParams.ReferencedAssemblies.Add("System.Data.dll"); compilerParams.ReferencedAssemblies.Add("System.Drawing.dll"); compilerParams.ReferencedAssemblies.Add("System.Xml.dll"); compilerParams.ReferencedAssemblies.Add("System.Windows.Forms.dll");
如果团队中允许多语言并行开发，此功能也可以实现将生成的C#代码，转化为VB代码。
在这里，需要构造一个Main方法，把生成的代码嵌入到这个Main方法中去，返回一个对象，把这个对象反射到Debug的网格窗口中。反射调用的入口方法如下所示
private object CallEntry(Assembly assembly, string entryPoint) { object result = null; try { //Use reflection to call the static Main function Module[] mods = assembly.GetModules(false); Type[] types = mods[0].GetTypes(); foreach (Type type in types) { MethodInfo mi = type.GetMethod(entryPoint, BindingFlags.Public | BindingFlags.NonPublic | BindingFlags.Static); if (mi != null) { if (mi.GetParameters().Length == 1) { if (mi.GetParameters()[0].ParameterType.IsArray) { string[] par = new string[1]; // if Main has string [] arguments result=mi.Invoke(null, par); } } else { result= mi.Invoke(null, null); } } } LogErrMsgs("Engine could not find the public static " + entryPoint); } catch (Exception ex) { LogErrMsgs("Error: An exception occurred", ex); } return result; }
最后一步，绑定结果到网格中
void BindEntity2Grid(IEntity2 entity) { grid.RowHeadersVisible = false; grid.AutoGenerateColumns = false; grid.Columns.Clear(); Type type = entity.GetType(); PropertyInfo[] propertyInfos = type.GetProperties(); for (int i = 0; i < propertyInfos.Length; i++) { PropertyInfo property = propertyInfos[i]; if (excludeColumns.Contains(property.Name)) continue; DataGridViewTextBoxColumn column = new DataGridViewTextBoxColumn(); column.HeaderText = property.Name; column.DataPropertyName = property.Name; column.Name = property.Name; grid.Columns.Add(column); } for (int i = 0; i < propertyInfos.Length; i++) { PropertyInfo property = propertyInfos[i]; if (excludeColumns.Contains(property.Name)) continue; grid.Rows[0].Cells[property.Name].Value = ReflectionHelper.GetPropertyValue(entity, property.Name); } }
这里的目的是读取对象的属性，生成Grid的列，并填充值。
这里使用的SQL语法高亮控件来自于CodeProject的FastColoredTextBox。 读取一个数据库中所有的表及表的字段用到如下的SQL语句，供您参考。
SELECT name FROM sysobjects WHERE xtype='U' SELECT syscolumns.NAME AS [ColumnName], systypes.NAME AS [ColumnType], syscolumns.length AS [ColumnLength],syscolumns.isnullable AS [Nullable] FROM syscolumns left join systypes on syscolumns.xusertype=systypes.xusertype WHERE id=(select id from sysobjects where name='Currency‘
此功能有个明显的Bug，没有把功能编码显示在地址栏中，因为它继承于FormBase，需要改成FunctionFormBase可达到目的，界面可能需要重新排版一下，改变继承的基类会让窗体设计器重新加载基类控件，子类的控件会丢失。
[FunctionCode("SAUMAQ")] public partial class AdvancedQuery : FormBase { private string _fileName; public AdvancedQuery() { InitializeComponent(); } }