public virtual void Visit(SqlRow node) { foreach (SqlExpression expression in node) { VisitInternal(expression); } }
private SqlSelect CreateSelectForUniqueMatchNoneOrFull(SqlRow row, SqlSelect query) { /* * select exists(select 1 * from (original subquery) x * where x.a1=r.a1 and x.a2=r.a2 * group by x.a1, x.a2 * having count(*)=1 * ) } */ SqlSelect q0 = SqlDml.Select(); { SqlQueryRef originalQuery = SqlDml.QueryRef(query); SqlSelect q1 = SqlDml.Select(originalQuery); q1.Columns.Add(1); q1.Where = true; //initially true { int index = 0; foreach (SqlColumn col in originalQuery.Columns) { q1.Where = q1.Where && col == row[index]; q1.GroupBy.Add(col); index++; } q1.Having = SqlDml.Count(SqlDml.Asterisk) == 1; } q0.Columns.Add(SqlDml.Exists(q1)); } return(q0); }
public void SqlRowReplacingTest() { SqlRow r = SqlDml.Row(1, 2, 4); SqlRow rReplacing = SqlDml.Row(1, 2, 4, "text", 'c'); r.ReplaceWith(rReplacing); bool passed = false; try { r.ReplaceWith(1); } catch { passed = true; } Assert.IsTrue(passed); Assert.AreNotSame(r, rReplacing); Assert.AreEqual(r.NodeType, rReplacing.NodeType); Assert.AreEqual(r.Count, rReplacing.Count); for (int i = 0, l = r.Count; i < l; i++) { Assert.AreEqual(r[i], rReplacing[i]); } }
public void VerifyResultsWithEmptyColumnName() { int expectedOne = 99; string expectedTwo = "OneResult"; int expectedThree = 42; List <SqlParameter> parms = new List <SqlParameter>(); SqlParameter parm = new SqlParameter("@RequiredParameter", expectedOne); parms.Add(parm); parm = new SqlParameter("@OptionalParameter", "OneResult"); parms.Add(parm); SqlProcessor.ExecuteReader("uspTestParameters", CommandType.StoredProcedure, parms); Assert.AreEqual(1, SqlProcessor.Rows.Count); SqlRow row = SqlProcessor.Rows[0]; Assert.AreEqual(3, row.Columns.Count); Assert.AreEqual(expectedOne, row.ColumnOrdinal[0].IntValue); Assert.AreEqual("ReturnedInt", row.ColumnOrdinal[0].ColumnName); Assert.AreEqual(expectedTwo, row.ColumnOrdinal[1].StringValue); Assert.AreEqual("ReturnedVarChar", row.ColumnOrdinal[1].ColumnName); Assert.AreEqual(expectedThree, row.ColumnOrdinal[2].IntValue); Assert.AreEqual("", row.ColumnOrdinal[2].ColumnName); }
internal override SqlRow VisitRow(SqlRow row) { foreach (SqlColumn c in row.Columns) { c.Alias = alias; } return(base.VisitRow(row)); }
internal override SqlRow VisitRow(SqlRow row) { foreach (SqlColumn col in row.Columns) { this.VisitColumn(col); } return(row); }
internal override SqlRow VisitRow(SqlRow row) { foreach (SqlColumn column in row.Columns) { this.Visit(column.Expression); } return(row); }
internal bool HasRowNumber(SqlRow row) { foreach (SqlColumn column in row.Columns) { if (this.HasRowNumber(column)) { return true; } } return false; }
internal override SqlRow VisitRow(SqlRow row) { int num = 0; int count = row.Columns.Count; while (num < count) { SqlColumn key = row.Columns[num]; if (num > 0) { sb.Append(", "); } Visit(key.Expression); string name = key.Name; string str2 = InferName(key.Expression, null); if (name == null) { name = str2; } if ((name == null) && !names.TryGetValue(key, out name)) { name = "C" + names.Count; names[key] = name; } if (key.Expression != null && key.Expression.NodeType == SqlNodeType.FunctionCall && ((SqlFunctionCall)key.Expression).Name.StartsWith("GEN_ID")) { name = null; } if (!string.IsNullOrEmpty(str2)) { str2 = SqlIdentifier.UnquoteIdentifier(str2); } if (name != str2 && !string.IsNullOrEmpty(name)) { if (!string.IsNullOrEmpty(str2)) { sb.Append(" AS "); } if (SqlIdentifier.NeedToQuote(name)) { WriteName(SqlIdentifier.QuoteIdentifier(name)); } else { WriteName(name); } } num++; } return(row); }
internal override SqlRow VisitRow(SqlRow row) { SqlRow nrow = new SqlRow(row.SourceExpression); foreach (SqlColumn c in row.Columns) { nrow.Columns.Add((SqlColumn)this.Visit(c)); } return(nrow); }
public IComparable CheckOperation(SqlRow row) { int colIndex = row.GetColumnIndex(ColumnDirectory); if (colIndex >= 0) { return(row.Cells[colIndex].Value); } return(null); }
public IComparable CheckOperation(SqlRow row) { IComparable leftOperationCheck = LeftChild.CheckOperation(row); IComparable rightOperationCheck = RightChild.CheckOperation(row); if (leftOperationCheck != null && rightOperationCheck != null) { return(OperationFunction?.Invoke(leftOperationCheck, rightOperationCheck)); } return(null); }
internal bool HasRowNumber(SqlRow row) { foreach (SqlColumn column in row.Columns) { if (HasRowNumber(column)) { return(true); } } return(false); }
internal override SqlRow VisitRow(SqlRow row) { for (int i = 0, n = row.Columns.Count; i < n; i++) { row.Columns[i].Expression = this.VisitExpression(row.Columns[i].Expression); if (this.hasRowNumber) { this.CurrentColumn = row.Columns[i]; break; } } return row; }
internal override SqlRow VisitRow(SqlRow row) { for (int i = 0, n = row.Columns.Count; i < n; i++) { SqlColumn col = row.Columns[i]; SqlExpression expr = this.VisitExpression(col.Expression); if (expr != null) { if (TypeSystem.GetNonNullableType(col.ClrType) != TypeSystem.GetNonNullableType(expr.ClrType)) { throw Error.ColumnClrTypeDoesNotAgreeWithExpressionsClrType(); } } } return row; }
protected override void BuildSqlRow(SqlRow expr, bool buildTableName, bool checkParentheses, bool throwExceptionIfTableNotFound) { // Informix needs ROW(1,2) syntax instead of BasicSqlBuilder default (1,2) StringBuilder.Append("ROW ("); foreach (var value in expr.Values) { BuildExpression(value, buildTableName, checkParentheses, throwExceptionIfTableNotFound); StringBuilder.Append(InlineComma); } StringBuilder.Length -= InlineComma.Length; // Note that SqlRow are never empty StringBuilder.Append(')'); }
internal override SqlRow VisitRow(SqlRow row) { for (int i = 0, n = row.Columns.Count; i < n; i++) { row.Columns[i].Expression = this.VisitExpression(row.Columns[i].Expression); if (this.hasRowNumber) { this.CurrentColumn = row.Columns[i]; break; } } return(row); }
internal override SqlSelect VisitSelect(SqlSelect select) { SqlSource from = this.VisitSource(@select.From); List <SqlExpression> gex = null; if (@select.GroupBy.Count > 0) { gex = new List <SqlExpression>(@select.GroupBy.Count); foreach (SqlExpression sqlExpr in @select.GroupBy) { gex.Add((SqlExpression)this.Visit(sqlExpr)); } } SqlExpression having = (SqlExpression)this.Visit(@select.Having); List <SqlOrderExpression> lex = null; if (@select.OrderBy.Count > 0) { lex = new List <SqlOrderExpression>(@select.OrderBy.Count); foreach (SqlOrderExpression sox in @select.OrderBy) { SqlOrderExpression nsox = new SqlOrderExpression(sox.OrderType, (SqlExpression)this.Visit(sox.Expression)); lex.Add(nsox); } } SqlExpression top = (SqlExpression)this.Visit(@select.Top); SqlExpression where = (SqlExpression)this.Visit(@select.Where); SqlRow row = (SqlRow)this.Visit(@select.Row); SqlExpression selection = this.VisitExpression(@select.Selection); SqlSelect n = new SqlSelect(selection, @from, @select.SourceExpression); if (gex != null) { n.GroupBy.AddRange(gex); } n.Having = having; if (lex != null) { n.OrderBy.AddRange(lex); } n.OrderingType = @select.OrderingType; n.Row = row; n.Top = top; n.IsDistinct = @select.IsDistinct; n.IsPercent = @select.IsPercent; n.Where = @where; n.DoNotOutput = @select.DoNotOutput; return(n); }
private void ConvertColumnsToMax(SqlSelect select, out bool changed, out bool containsLongExpressions) { SqlRow row = select.Row; changed = false; containsLongExpressions = false; foreach (SqlColumn col in row.Columns) { bool columnChanged; containsLongExpressions = containsLongExpressions || col.SqlType.IsLargeType; col.Expression = ConvertToMax(col.Expression, out columnChanged); changed = changed || columnChanged; } }
public void SqlRowCloneTest() { SqlRow r = SqlDml.Row(1, 2, 4, SqlDml.Literal(6) + 5); SqlRow rClone = (SqlRow)r.Clone(); Assert.AreNotEqual(r, rClone); Assert.AreEqual(r.NodeType, rClone.NodeType); Assert.AreEqual(r.Count, rClone.Count); for (int i = 0, l = r.Count; i < l; i++) { Assert.AreNotEqual(r[i], rClone[i]); Assert.AreEqual(r[i].NodeType, rClone[i].NodeType); } }
internal override SqlRow VisitRow(SqlRow row) { foreach (SqlColumn c in row.Columns) { if (this.RefersToColumn(c, this.match)) { if (this.found != null) { throw Error.ColumnIsDefinedInMultiplePlaces(GetColumnName(this.match)); } this.found = c; break; } } return(row); }
internal override SqlRow VisitRow(SqlRow row) { for (int i = 0, n = row.Columns.Count; i < n; i++) { SqlColumn col = row.Columns[i]; SqlExpression expr = this.VisitExpression(col.Expression); if (expr != null) { if (TypeSystem.GetNonNullableType(col.ClrType) != TypeSystem.GetNonNullableType(expr.ClrType)) { throw Error.ColumnClrTypeDoesNotAgreeWithExpressionsClrType(); } } } return(row); }
// Methods internal override SqlRow VisitRow(SqlRow row) { int num = 0; int count = row.Columns.Count; while (num < count) { row.Columns[num].Expression = VisitExpression(row.Columns[num].Expression); if (hasRowNumber) { CurrentColumn = row.Columns[num]; return(row); } num++; } return(row); }
// Methods internal override SqlRow VisitRow(SqlRow row) { int num = 0; int count = row.Columns.Count; while (num < count) { SqlColumn column = row.Columns[num]; SqlExpression expression = this.VisitExpression(column.Expression); if ((expression != null) && (TypeSystem.GetNonNullableType(column.ClrType) != TypeSystem.GetNonNullableType(expression.ClrType))) { throw Error.ColumnClrTypeDoesNotAgreeWithExpressionsClrType(); } num++; } return(row); }
public void VerifyResults() { int expected = 1; List <SqlParameter> parms = new List <SqlParameter>(); SqlParameter parm = new SqlParameter("@RequiredParameter", expected); parms.Add(parm); parm = new SqlParameter("@OptionalParameter", "Ignore"); parms.Add(parm); SqlProcessor.ExecuteReader("uspTestParameters", CommandType.StoredProcedure, parms); Assert.AreEqual(expected, SqlProcessor.Rows.Count); SqlRow row = SqlProcessor.Rows[0]; Assert.AreEqual(2, row.Columns.Count); Assert.AreEqual(1, row.ColumnOrdinal[0].IntValue); Assert.AreEqual("ReturnedInt", row.ColumnOrdinal[0].ColumnName); Assert.IsTrue(row.ColumnOrdinal[1].IsNull); Assert.AreEqual("ReturnedVarChar", row.ColumnOrdinal[1].ColumnName); }
/// <summary> /// Executes the TSQL query provided. /// </summary> /// <param name="query">The TSQL query text to execute.</param> /// <param name="connectionString">The connection string of the database to execute the script against.</param> /// <param name="log">The event log to log execptions to. May be null for no logging.</param> /// <returns>A list of rows of data returned from the query.</returns> override public List <SqlRow> ExecuteQuery(String query, String connectionString, EventLog.EventLog log) { // Check that the query and connection string are supplied. if (!string.IsNullOrWhiteSpace(query) && !string.IsNullOrWhiteSpace(connectionString)) { using (SqlConnection connection = new SqlConnection(connectionString)) { Server server = new Server(new ServerConnection(connection)); try { // Execute the query. List <SqlRow> results = new List <SqlRow>(); using (SqlDataReader reader = server.ConnectionContext.ExecuteReader(query)) { while (reader.Read()) { // Add each row's data to the results list. SqlRow row = new SqlRow(); for (int i = 0; i < reader.FieldCount; i++) { row.Add(i, reader.GetName(i), reader.GetValue(i)); } results.Add(row); } } return(results); } catch (ExecutionFailureException e) { // A non-SQL statement was included in the script. LogException(e, log); return(new List <SqlRow>()); } } } else { // The script file was not read. return(new List <SqlRow>()); } }
private void ForceLocal(SqlRow row, string name) { bool flag = false; foreach (SqlColumn column in row.Columns) { if (RefersToColumn(column, this.found)) { this.found = column; flag = true; break; } } if (!flag) { SqlColumn item = new SqlColumn(this.found.ClrType, this.found.SqlType, name, this.found.MetaMember, new SqlColumnRef(this.found), row.SourceExpression); row.Columns.Add(item); this.found = item; } }
private void ForceLocal(SqlRow row, string name) { bool isLocal = false; // check to see if it already exists locally foreach (SqlColumn c in row.Columns) { if (this.RefersToColumn(c, this.found)) { this.found = c; isLocal = true; break; } } if (!isLocal) { // need to put this in the local projection list to bubble it up SqlColumn c = new SqlColumn(found.ClrType, found.SqlType, name, this.found.MetaMember, new SqlColumnRef(this.found), row.SourceExpression); row.Columns.Add(c); this.found = c; } }
internal override SqlRow VisitRow(SqlRow row) { int num = 0; int count = row.Columns.Count; while (num < count) { SqlColumn key = row.Columns[num]; if (num > 0) { this.sb.Append(", "); } this.Visit(key.Expression); //Key为关键字,不能作为Column名称。 string name = key.Name;// == "Key" ? "KeyColumn" : key.Name; string str2 = this.InferName(key.Expression, null); if (name == null) { name = str2; } if ((name == null) && !this.names.TryGetValue(key, out name)) { name = "C" + this.names.Count; this.names[key] = name; } if ((name != str2) && !string.IsNullOrEmpty(name)) { this.sb.Append(" AS "); this.WriteName(name); } num++; } return(row); }
internal virtual SqlRow VisitRow(SqlRow row) { for (int i = 0, n = row.Columns.Count; i < n; i++) { row.Columns[i].Expression = this.VisitExpression(row.Columns[i].Expression); } return row; }
internal override SqlRow VisitRow(SqlRow row) { foreach (SqlColumn c in row.Columns) { if (this.RefersToColumn(c, this.match)) { if (this.found != null) { throw Error.ColumnIsDefinedInMultiplePlaces(GetColumnName(this.match)); } this.found = c; break; } } return row; }
/// <summary> /// Executes the TSQL query provided. /// </summary> /// <param name="query">The TSQL query text to execute.</param> /// <param name="connectionString">The connection string of the database to execute the script against.</param> /// <param name="log">The event log to log execptions to. May be null for no logging.</param> /// <returns>A list of rows of data returned from the query.</returns> public override List<SqlRow> ExecuteQuery(String query, String connectionString, EventLog.EventLog log) { // Check that the query and connection string are supplied. if (!string.IsNullOrWhiteSpace(query) && !string.IsNullOrWhiteSpace(connectionString)) { using (SqlConnection connection = new SqlConnection(connectionString)) { Server server = new Server(new ServerConnection(connection)); try { // Execute the query. List<SqlRow> results = new List<SqlRow>(); using (SqlDataReader reader = server.ConnectionContext.ExecuteReader(query)) { while (reader.Read()) { // Add each row's data to the results list. SqlRow row = new SqlRow(); for (int i = 0; i < reader.FieldCount; i++) { row.Add(i, reader.GetName(i), reader.GetValue(i)); } results.Add(row); } } return results; } catch (ExecutionFailureException e) { // A non-SQL statement was included in the script. LogException(e, log); return new List<SqlRow>(); } } } else { // The script file was not read. return new List<SqlRow>(); } }
internal SelectionFlattener(SqlRow row, Dictionary<SqlColumn, SqlColumn> map, bool isInput) { this.row = row; this.map = map; this.isInput = isInput; }
private SqlExpression FlattenSelection(SqlRow row, bool isInput, SqlExpression selection) { selection = this.columnizer.ColumnizeSelection(selection); return new SelectionFlattener(row, this.map, isInput).VisitExpression(selection); }
public IComparable CheckOperation(SqlRow row) { return(Data); }
internal override SqlRow VisitRow(SqlRow row) { foreach(SqlColumn col in row.Columns) { this.VisitColumn(col); } return row; }
internal override SqlRow VisitRow(SqlRow row) { foreach (SqlColumn c in row.Columns) { c.Alias = alias; } return base.VisitRow(row); }
private SqlExpression FlattenSelection(SqlRow row, bool isInput, SqlExpression selection) { selection = this.columnizer.ColumnizeSelection(selection); return(new SelectionFlattener(row, this.map, isInput).VisitExpression(selection)); }
internal override SqlRow VisitRow(SqlRow row) { SqlRow nrow = new SqlRow(row.SourceExpression); foreach (SqlColumn c in row.Columns) { nrow.Columns.Add((SqlColumn)this.Visit(c)); } return nrow; }
internal override SqlRow VisitRow(SqlRow row) { foreach (SqlColumn c in row.Columns) { this.Visit(c.Expression); } return row; }
internal override SqlRow VisitRow(SqlRow row) { for (int i = 0, n = row.Columns.Count; i < n; i++) { SqlColumn c = row.Columns[i]; if (i > 0) sb.Append(", "); this.Visit(c.Expression); string name = c.Name; string inferredName = this.InferName(c.Expression, null); if (name == null) name = inferredName; if (name == null) { if (!this.names.TryGetValue(c, out name)) { name = "C" + this.names.Count; this.names[c] = name; } } if (name != inferredName && !String.IsNullOrEmpty(name)) { sb.Append(" AS "); this.WriteName(name); } } return row; }
internal SelectionFlattener(SqlRow row, Dictionary <SqlColumn, SqlColumn> map, bool isInput) { this.row = row; this.map = map; this.isInput = isInput; }
/// <summary> /// Executes the PL/SQL query provided. /// </summary> /// <param name="query">The PL/SQL query text to execute.</param> /// <param name="connectionString">The connection string of the database to execute the script against.</param> /// <param name="log">The event log to log execptions to. May be null for no logging.</param> /// <param name="exception">Outputs an exception if there was one during the processing of the query. Null otherwise.</param> /// <returns>A list of rows of data returned from the query.</returns> public List<SqlRow> ExecuteQuery(String query, String connectionString, EventLog.EventLog log, out Exception exception) { // Check that the query and connection string are supplied. if (!string.IsNullOrWhiteSpace(query) && !string.IsNullOrWhiteSpace(connectionString)) { // Get the connection to the database. using (OracleConnection connection = new OracleConnection(connectionString)) { try { // Open the connection to the server. connection.Open(); // Create a command that contains the text of the script. using (OracleCommand queryCommand = new OracleCommand()) { queryCommand.Connection = connection; queryCommand.CommandText = query; queryCommand.CommandType = CommandType.Text; // Execute the query. List<SqlRow> results = new List<SqlRow>(); using (OracleDataReader reader = queryCommand.ExecuteReader()) { if (reader.HasRows) { // Add each row's data to the results list. while (reader.Read()) { SqlRow row = new SqlRow(); for (int columnNum = 0; columnNum < reader.FieldCount; columnNum++) { row.Add(columnNum, reader.GetName(columnNum), reader.GetValue(columnNum)); } results.Add(row); } } } exception = null; return results; } } catch (OracleException e) { // There was an error executing the script. LogException(e, log); exception = e; return new List<SqlRow>(); } catch (Exception e) { // There was an error executing the script. LogException(e, log); exception = e; return new List<SqlRow>(); } } } else { // The script file was not read. exception = null; return new List<SqlRow>(); } }