/// <summary> /// Map to an existing instance /// </summary> /// <typeparam name="T"></typeparam> /// <param name="connection"></param> /// <param name="query"></param> /// <param name="ignoreExtraRows"></param> /// <param name="target"></param> /// <param name="parameters"></param> /// <returns></returns> protected bool TryGetSingle <T>( object query, T target, bool ignoreExtraRows, params object[] parameters) { var wrapper = new SqlQueryParser <T>(QueryType.Select, query, Transaction, parameters); wrapper.ExpectSelectQuery(); bool looped = false; using (IDataReader reader = wrapper.RunQuery(StorageController)) { DataReaderWrapper drWrap = new DataReaderWrapper(reader); MapperWrapper <T> mapWrap = new MapperWrapper <T>(drWrap); foreach (var item in mapWrap) { if (looped) { throw new IQException("More than one record was returned by the Single<T> query"); } target = item; looped = true; if (ignoreExtraRows) { break; } } } return(looped); }
static void Main(string[] args) { var services = new ServiceCollection(); services.AddSingleton <ITest1, TestClass1>(); services.AddSingleton <ITest, TestClass>(); var serviceProvider = services.BuildServiceProvider(); ActivatorUtilities.CreateInstance <TestClass>(serviceProvider); var select = new SelectQueryBuilder() .Select(Tables.U.ID, Tables.U.Username, Tables.P.Email) .Where(Tables.U.Age > 30 | Tables.P.Email.Like("*****@*****.**")) .Where(Tables.U.ID != null) .From(Tables.Users.As("U").InnerJoin(Tables.UserProfile.As("P")).On(Tables.U.ID == Tables.P.ID)) .Build(); var insert = new InsertQueryBuilder() .InsertInto(Tables.Users, Names.ID, Names.Username, Names.Email) .WithValues(ValuesClause.Values((1, "Ivony", "*****@*****.**"))) .Build(); var parser = new SqlQueryParser(); ShowQuery(parser.ParseSelectQuery(select)); ShowQuery(parser.ParseInsertQuery(insert)); Console.ReadKey(); }
/// <summary> /// Executa as várias consultas. /// </summary> /// <param name="session"></param> /// <param name="queryInfo"></param> /// <returns></returns> protected virtual IQueryResult Execute(GDASession session, QueryInfo queryInfo) { if (session.State == GDASessionState.Closed) { throw new QueryException("Database connection is closed to execute query"); } queryInfo.Require("queries").NotNull(); string providerName = ProviderLocator.GetProviderName(queryInfo); var providerConfiguration = GDASettings.GetProviderConfiguration(providerName); if (queryInfo.StoredProcedureName == null) { SqlQueryParser parser = CreateParser(queryInfo); string queryString = parser.GetText(); try { return(ExecuteQuery(session, queryString, queryInfo)); } catch (GDAException ex) { throw new QueryException(string.Format("{0}. Query: {1}", ex.Message, queryInfo.ToString()), ex.InnerException); } } else { return(ExecuteStoredProcedure(session, queryInfo)); } }
public RbacEngineWebResponse Post([FromBody] RbacEngineWebRequest request) { RbacEngineWebResponse response = new RbacEngineWebResponse(); try { response.UserName = request.UserName; response.RoleName = request.RoleName; using (Rbac ctx = new Rbac(request.UserName, request.RbacName, request.RoleName)) { response.RbacName = request.RbacName; SqlQueryParser parser = new SqlQueryParser(ctx, request.SkipParsing); parser.Parse(request.Query); using (RbacSqlQueryEngine eng = new RbacSqlQueryEngine(parser, request.DebugMode)) { eng.SkipExecution = request.SkipExecution; eng.Execute(); response.SetResult(eng); } } } catch (Exception ex) { response.SetResult(ex.Message); } return(response); }
private static void ParseUsingMangedParser(SqlQuerySpec sqlQuerySpec) { if (!SqlQueryParser.TryParse(sqlQuerySpec.QueryText, out SqlQuery sqlQuery)) { throw new InvalidOperationException("FAILED TO PARSE QUERY."); } }
public void Untouched(string sql) { _params.AddWithValue(":param", "foo"); SqlQueryParser.ParseRawQuery(sql, true, _params, _queries); Assert.That(_queries.Single().SQL, Is.EqualTo(sql)); Assert.That(_queries.Single().InputParameters, Is.Empty); }
public void ParamSimple() { _params.AddWithValue(":p1", "foo"); _params.AddWithValue(":p2", "bar"); SqlQueryParser.ParseRawQuery("SELECT :p1, :p2", true, _params, _queries); Assert.That(_queries.Single().InputParameters, Is.EqualTo(_params)); }
public void NonConformantStrings() { var parser = new SqlQueryParser(false); parser.ParseRawQuery(@"SELECT 'abc\':str''a:str'", _params, _queries); Assert.That(_queries.Single().SQL, Is.EqualTo(@"SELECT 'abc\':str''a:str'")); Assert.That(_queries.Single().InputParameters, Is.Empty); }
public void MissingParamIsIgnored() { SqlQueryParser.ParseRawQuery("SELECT @p; SELECT 1", true, _params, _queries); Assert.That(_queries[0].SQL, Is.EqualTo("SELECT @p")); Assert.That(_queries[1].SQL, Is.EqualTo("SELECT 1")); Assert.That(_queries[0].InputParameters, Is.Empty); Assert.That(_queries[1].InputParameters, Is.Empty); }
public void NoOutputParameters() { var p = new NpgsqlParameter("p", DbType.String) { Direction = ParameterDirection.Output }; _params.Add(p); Assert.That(() => SqlQueryParser.ParseRawQuery("SELECT @p", true, _params, _queries), Throws.Exception); }
List <NpgsqlBatchCommand> ParseCommand(string sql, NpgsqlParameter[] parameters, bool standardConformingStrings) { var cmd = new NpgsqlCommand(sql); cmd.Parameters.AddRange(parameters); var parser = new SqlQueryParser(); parser.ParseRawQuery(cmd, standardConformingStrings); return(cmd.InternalBatchCommands); }
private void IsAllowedToInsertOrUpdateOrDelete(string query = null) { using (Rbac rbac = new Rbac("essie")) //<-- you should pass the logged in user name from the context { using (SqlQueryParser parser = new SqlQueryParser(rbac)) { parser.Parse(query); //<-- this will throw error if not permitted and silent is false } } }
private void IsAllowedToInsertOrUpdateOrDelete(string query = null) { using (Rbac rbac = new Rbac("essie")) //<-- you should pass the logged in user name from the context { using (SqlQueryParser parser = new SqlQueryParser(rbac)) { parser.Parse(query); //<-- this will throw exception if not permitted //<-- if you are here, you are goood. Just perform basic insert/update/delete } } }
private void LoadAssemblies() { //this will load the assembly into memory, so that 2nd call is more efficient try { Rbac rbac = new Rbac("Lashawn", "Books", "role_city_mgr"); SqlQueryParser parser = new SqlQueryParser(rbac); parser.Parse("select * from Author"); } catch { } }
private void btnExecute_Click(object sender, EventArgs e) { SetStatusText("Parsing..."); txtErrors.Text = string.Empty; txtParsedQuerys1.Text = string.Empty; txtParsedQuery.Text = string.Empty; txtErrors.Visible = false; RbacEngineWebResponse response = new RbacEngineWebResponse(); this.Cursor = Cursors.WaitCursor; try { _Request.RbacName = ((Rbac)cbInstances.SelectedItem).Name; _Request.UserName = ((RbacUser)cbUsers.SelectedItem).UserName; _Request.RoleName = ((RbacRole)cbRoles.SelectedItem).Name; _Request.Query = txtQuery.Text; engineInput.SelectedObject = _Request; using (Rbac ctx = new Rbac(_Request.UserName, _Request.RbacName, _Request.RoleName)) { SqlQueryParser parser = new SqlQueryParser(ctx, _Request.SkipParsing); parser.Parse(_Request.Query); response.SetResult(parser); BindResult(response); SetStatusText("Parsing...Done.", response); if (parser.QueryType == RbacQueryTypes.Select) { SetStatusText("Parsing...Done. Executing...", response); using (RbacSqlQueryEngine eng = new RbacSqlQueryEngine(parser, _Request.DebugMode)) { eng.SkipExecution = _Request.SkipExecution; eng.Execute(); response.SetResult(eng); SetStatusText("Parsing...Done. Executing...Done.", response); } } } } catch (RbacException ex) { txtErrors.Text = ex.Message; txtErrors.Visible = true; SetStatusText("Done."); } BindResult(response); tabControl1.SelectedIndex = 0; this.Cursor = Cursors.Default; }
protected bool TryGetSingle <T>( object query, out T target, bool ignoreExtraRows, params object[] parameters) { var wrapper = new SqlQueryParser <T>(QueryType.Select, query, Transaction, parameters); wrapper.ExpectSelectQuery(); //wrapper.Query.Top = 1; return(TryGetSingle(wrapper, ignoreExtraRows, out target)); }
public void ReduceNumberOfStatements() { var parser = new SqlQueryParser(); var cmd = new NpgsqlCommand("SELECT 1; SELECT 2"); parser.ParseRawQuery(cmd); Assert.That(cmd.InternalBatchCommands, Has.Count.EqualTo(2)); cmd.CommandText = "SELECT 1"; parser.ParseRawQuery(cmd); Assert.That(cmd.InternalBatchCommands, Has.Count.EqualTo(1)); }
private void btnExecuteAll_Click(object sender, EventArgs e) { if (lvwQueries.Tag != null) { DataTable table = lvwQueries.Tag as DataTable; if (table.Columns["ParsedQueryStage1"] == null) { table.Columns.Add("ParsedQueryStage1"); table.Columns.Add("ParsedQuery"); table.Columns.Add("Errors"); } foreach (DataRow row in table.Rows) { try { Rbac rbac = new Rbac(row["User"].ToString(), "Books", row["Role"].ToString()); SqlQueryParser parser = new SqlQueryParser(rbac); parser.Parse(row["Query"].ToString()); RbacSqlQueryEngine engine = new RbacSqlQueryEngine(parser, true); engine.Execute(); row["ParsedQueryStage1"] = parser.ParsedQueryStage1; row["ParsedQuery"] = parser.ParsedQuery; row["Errors"] = parser.AllErrors + Environment.NewLine; } catch (Exception ex) { row["Errors"] = ex.Message; } } string fileName = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, table.TableName + "_out.csv"); try { table.ToCsv(fileName); MessageBox.Show("Test results are saved on " + fileName + "!", "Success", MessageBoxButtons.OK, MessageBoxIcon.Information); } catch (Exception ex) { MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error); } } }
public void MultiqueryWithParams() { var p1 = new NpgsqlParameter("p1", DbType.String); _params.Add(p1); var p2 = new NpgsqlParameter("p2", DbType.String); _params.Add(p2); var p3 = new NpgsqlParameter("p3", DbType.String); _params.Add(p3); SqlQueryParser.ParseRawQuery("SELECT @p3, @p1; SELECT @p2, @p3", true, _params, _queries); Assert.That(_queries, Has.Count.EqualTo(2)); Assert.That(_queries[0].InputParameters[0], Is.SameAs(p3)); Assert.That(_queries[0].InputParameters[1], Is.SameAs(p1)); Assert.That(_queries[1].InputParameters[0], Is.SameAs(p2)); Assert.That(_queries[1].InputParameters[1], Is.SameAs(p3)); }
// POST: api/Rbac public string Post([FromBody] RbacWebRequest request) { RbacWebResponse response = new RbacWebResponse(request.RbacId); if (request.RbacId == 0) { response.SetResult(string.Format("Rbac instance with rbac id '{0}' not found!", request.RbacId)); return(JsonConvert.SerializeObject(response)); } RbacUser user = new Rbac(request.RbacId).GetUser(request.UserName); if (user == null) { response.SetResult(string.Format("User '{0}' not found!", request.UserName)); return(JsonConvert.SerializeObject(response)); } if (!string.IsNullOrEmpty(request.RoleName)) { user.Role = new RbacRole(request.RoleName); //dangerous code, only to be used for role testing if (user.Role == null) { response.SetResult(string.Format("Role '{0}' not found!", request.RoleName)); return(JsonConvert.SerializeObject(response)); } } SqlQueryParser parser = new SqlQueryParser(new RbacContext(user), request.SkipParsing); parser.Parse(request.Query); RbacSqlQueryEngine engine = new RbacSqlQueryEngine(parser, request.DebugMode); engine.SkipExecution = request.SkipExecution; engine.Execute(); response.SetResult(engine); return(JsonConvert.SerializeObject(response)); }
public void SetUp() { _parser = new SqlQueryParser(); _queries = new List <NpgsqlStatement>(); _params = new NpgsqlParameterCollection(); }
public void TestBatch() { GenericParserAdapter genParser = new GenericParserAdapter(Path.Combine(_rootDir, "Books", "tests.csv")); genParser.FirstRowHasHeader = true; DataTable table = genParser.GetDataTable(); if (table.Columns["ParsedQueryStage1"] == null) { table.Columns.Add("ParsedQueryStage1"); table.Columns.Add("ParsedQuery"); table.Columns.Add("Records"); table.Columns.Add("Errors"); table.Columns.Add("TestResult"); } bool cleaned = false; foreach (DataRow row in table.Rows) { //if (row["Id"].ToString() == "11") // Debugger.Break(); Rbac rbac = new Rbac(row["User"].ToString()); RbacRole role = Rbac.GetRole(row["Role"].ToString()); if (!cleaned) { CleanDataFromDb(rbac.ConnectionString); cleaned = true; } SqlQueryParser parser = new SqlQueryParser(rbac); try { parser.Parse(row["Query"].ToString()); } catch (Exception ex) { row["Errors"] = ex.Message; if (row["Expected"].ToString().Equals(row["Errors"].ToString())) { row["TestResult"] = "Passed"; } else { row["TestResult"] = "Failed"; } continue; } row["ParsedQueryStage1"] = parser.ParsedQueryStage1; row["ParsedQuery"] = parser.ParsedQuery; row["Errors"] += parser.AllErrors; if (string.IsNullOrEmpty(parser.AllErrors)) { RbacSqlQueryEngine engine = new RbacSqlQueryEngine(parser, true); engine.Execute(); if (engine.IsErrored) { row["Records"] = "Errored"; } else if ((parser.QueryType == RbacQueryTypes.Select) && (engine.Table == null)) { row["Records"] = "Errored"; } else if ((parser.QueryType == RbacQueryTypes.Select) && (engine.Table != null)) { row["Records"] = engine.Table.Rows.Count + " record(s)"; } if (!string.IsNullOrEmpty(parser.AllErrors)) { row["Errors"] += parser.AllErrors + Environment.NewLine; } if (!string.IsNullOrEmpty(engine.AllErrors)) { row["Errors"] += engine.AllErrors + Environment.NewLine; } } if (row["Expected"].ToString().Equals(row["Errors"].ToString())) { row["TestResult"] = "Passed"; } else { row["TestResult"] = "Failed"; } CleanDataFromDb(rbac.ConnectionString); } string outFile = Path.Combine(_rootDir, "Books", "tests_result.csv"); table.ToCsv(outFile); WriteColor(ConsoleColor.Green, outFile + " is generated!"); Console.WriteLine(); ToCsvMarkdownFormat(table, Path.Combine(_rootDir, "Books", "tests_result.md")); }
/// <summary> /// Executa as várias consultas. /// </summary> /// <param name="queries"></param> /// <returns></returns> public override IEnumerable <IQueryResult> Execute(QueryInfo[] queries) { queries.Require("queries").NotNull(); var result = new IQueryResult[queries.Length]; Dictionary <QueryExecutionGroup, IList <int> > providerDictionary = new Dictionary <QueryExecutionGroup, IList <int> >(QueryExecutionGroup.Comparer); for (int i = 0; i < queries.Length; i++) { string providerName = ProviderLocator.GetProviderName(queries[i]); var group = new QueryExecutionGroup(providerName, queries[i].IsolationLevel); IList <int> indexes; if (providerDictionary.TryGetValue(group, out indexes)) { indexes.Add(i); } else { indexes = new List <int>(); indexes.Add(i); providerDictionary.Add(group, indexes); } } IStoredProcedureTransaction storedProcedureTransaction = null; foreach (var provider in providerDictionary) { using (var session = CreateSession(provider.Key)) { RegisterSession(session); foreach (var index in provider.Value) { IQueryResult queryResult = null; try { if (queries[index].StoredProcedureName == null) { SqlQueryParser parser = CreateParser(queries[index]); string queryString = parser.GetText(); try { queryResult = ExecuteQuery(session, queryString, queries[index]); } catch (GDAException ex) { throw new GDAException(string.Format("{0}. Query: {1}", ex.Message, queries[index].ToString()), ex.InnerException); } } else { if (storedProcedureTransaction == null) { storedProcedureTransaction = new GDAStoredProcedureTransaction(session, provider.Key.ProviderName); if (!queries[index].IgnoreRegisterUserInfo) { RegisterUserInfo(storedProcedureTransaction); } } queryResult = ExecuteStoredProcedure(session, queries[index]); } } catch { if (session is GDATransaction) { ((GDATransaction)session).Rollback(); } throw; } yield return(queryResult); } if (session is GDATransaction) { ((GDATransaction)session).Commit(); } } } }
public int Delete <T>(object query, params object[] parameters) { var wrapper = new SqlQueryParser <T>(QueryType.Delete, query, Transaction, parameters); return(wrapper.RunQueryScalar(StorageController)); }
public int QueryScalar(string query, params object[] parameters) { var wrapper = new SqlQueryParser <object>(0, query, Transaction, parameters); return(wrapper.RunQueryScalar(StorageController)); }
public int Count <T>(object query, params object[] parameters) { var wrapper = new SqlQueryParser <T>(QueryType.Select, query, Transaction, parameters); return(StorageController.Count(wrapper.Connection, wrapper.Query)); }
private void btnExecuteAll_Click(object sender, EventArgs e) { Cursor = Cursors.WaitCursor; toolStripProgressBar1.Visible = true; if (lvwQueries.Tag != null) { DataTable table = lvwQueries.Tag as DataTable; toolStripProgressBar1.Maximum = table.Rows.Count; if (table.Columns["ParsedQueryStage1"] == null) { table.Columns.Add("ParsedQueryStage1"); table.Columns.Add("ParsedQuery"); table.Columns.Add("Errors"); } foreach (DataRow row in table.Rows) { try { Rbac rbac = new Rbac(row["User"].ToString(), "Books", row["Role"].ToString()); RbacEngineWebResponse response = new RbacEngineWebResponse(); this.Cursor = Cursors.WaitCursor; _Request.RbacName = rbac.Name; _Request.UserName = rbac.User.UserName; _Request.RoleName = rbac.User.Role.Name; _Request.Query = row["Query"].ToString(); SqlQueryParser parser = new SqlQueryParser(rbac); parser.Parse(_Request.Query); response.SetResult(parser); SetStatusText("Parsing...Done.", response); if (parser.QueryType == RbacQueryTypes.Select) { SetStatusText("Parsing...Done. Executing...", response); RbacSqlQueryEngine engine = new RbacSqlQueryEngine(parser, true); engine.Execute(); response.SetResult(engine); SetStatusText("Parsing...Done. Executing...Done.", response); } row["ParsedQueryStage1"] = parser.ParsedQueryStage1; row["ParsedQuery"] = parser.ParsedQuery; row["Errors"] = parser.AllErrors + Environment.NewLine; SetStatusText("Done.", response); } catch (Exception ex) { row["Errors"] = ex.Message; } toolStripProgressBar1.PerformStep(); Application.DoEvents(); } toolStripProgressBar1.Visible = false; string fileName = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, table.TableName + "_out.csv"); try { table.ToCsv(fileName); MessageBox.Show("Test results are saved on " + fileName + "!", "Success", MessageBoxButtons.OK, MessageBoxIcon.Information); } catch (Exception ex) { Cursor = Cursors.Default; MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error); } tabControl1.SelectedIndex = 0; Cursor = Cursors.Default; } }
public IEnumerable <T> Select <T>(object query, params object[] parameters) { var wrapper = new SqlQueryParser <T>(QueryType.Select, query, Transaction, parameters); return(MapAll <T>(wrapper.RunQuery(StorageController), wrapper.Buffered)); }
public string GetText(SqlQueryParser a, Colosoft.Query.TakeParameters b) { return(new GDA.Provider.Oracle.OracleProvider().SQLCommandLimit(null, a.Text, b.Skip, b.Take)); }
/// <summary> /// Return true of anything matches the criteria /// </summary> /// <typeparam name="T"></typeparam> /// <param name="where"></param> /// <param name="parameters"></param> /// <returns></returns> //public bool Any<T>(string where, params object[] parameters) //{ // var wrapper = new SqlQueryParser<T>(QueryType.Where, where, Transaction, parameters); // wrapper.ExpectSelectQuery(); // //wrapper.QueryFull.Top = 1; // wrapper.QueryFull.Select = "1"; // int target; // return TryGetSingle<int>(wrapper,false,out target); //} public IEnumerable <T> Where <T>(string where, params object[] parameters) { var wrapper = new SqlQueryParser <T>(QueryType.Where, where, Transaction, parameters); return(MapAll <T>(wrapper.RunQuery(StorageController), wrapper.Buffered)); }