之前没有用PetaPoco调用过存储过程,今天发现问题
Sql sql = ); var list = sqlserverDB.GetInstance().Fetch<AdminGroup>(sql); foreach (var adminGroup in list) { Console.WriteLine(adminGroup.GroupName); } Console.WriteLine("--------------------------------------"); Sql sql1 = );//和上面相比就EXCUTE变成了简写 var list1 = sqlserverDB.GetInstance().Fetch<AdminGroup>(sql1); foreach (var adminGroup in list1) { Console.WriteLine(adminGroup.GroupName); }
在sql server查询一样的语句,结果在程序中竟然不一样...
存储过程:
CREATE PROCEDURE GetAdminGroup @groupId int AS BEGIN SELECT * FROM dbo.AdminGroup WHERE GroupId>@groupId END GO
经过检查,发现在下面的那个list1组装sql的时候sql变成了
public IEnumerable<T> Query<T>(string sql, params object[] args) { if (EnableAutoSelect) sql = AddSelectClause<T>(sql);//在这个方法里面重新编写了sql ......... }
经过排查,终于发现正则 rxSelect 里面没有匹配exec
Regex rxSelect = new Regex(@"\A\s*(SELECT|EXECUTE|CALL)\s", RegexOptions.Compiled | RegexOptions.Singleline | RegexOptions.IgnoreCase | RegexOptions.Multiline); Regex rxFrom = new Regex(@"\A\s*FROM\s", RegexOptions.Compiled | RegexOptions.Singleline | RegexOptions.IgnoreCase | RegexOptions.Multiline); string AddSelectClause<T>(string sql) { if (sql.StartsWith(";")) ); if (!rxSelect.IsMatch(sql))//没有匹配exec, { var pd = PocoData.ForType(typeof(T)); var tableName = EscapeTableName(pd.TableInfo.TableName); string cols = string.Join(", ", (from c in pd.QueryColumns select tableName + "." + EscapeSqlIdentifier(c)).ToArray()); if (!rxFrom.IsMatch(sql)) sql = string.Format("SELECT {0} FROM {1} {2}", cols, tableName, sql);//自动生成了sql并且加在前面 else sql = string.Format("SELECT {0} {1}", cols, sql); } return sql; }
解决方法:需要将PetaPoco.cs 第 583行修改加上 |exec