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 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) {
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>();
}
}
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;
}
}
