public void Test001()
{
string nativeSql = @"SELECT DISTINCT
employee.FirstName
FROM
employee
WHERE
employee.Title = 'IT Staff'";
var p = sqlCommand.CreateParameter();
p.ParameterName = "p10";
p.DbType = DbType.String;
p.Value = "IT Staff";
sqlCommand.Parameters.Add(p);
SqlTableRef employees = SqlDml.TableRef(schema.Tables["employee"]);
SqlSelect select = SqlDml.Select(employees);
select.Distinct = true;
select.Columns.AddRange(employees["FirstName"]);
select.Where = employees["Title"] == SqlDml.ParameterRef(p.ParameterName);
Assert.IsTrue(CompareExecuteDataReader(nativeSql, select));
}
protected List <SqlExpression> ExtractColumnExpressions(SqlSelect query)
{
var result = new List <SqlExpression>(query.Columns.Count);
result.AddRange(query.Columns.Select(ExtractColumnExpression));
return(result);
}
public void Test000()
{
string nativeSql = @"SELECT
employee.EmployeeID,
employee.FirstName,
employee.LastName,
employee.BirthDate
FROM
employee
WHERE
employee.FirstName = 'Robert'
ORDER BY
employee.LastName";
var p = sqlCommand.CreateParameter();
p.ParameterName = "p1";
p.DbType = DbType.String;
p.Value = "Robert";
sqlCommand.Parameters.Add(p);
SqlTableRef employees = SqlDml.TableRef(schema.Tables["employee"]);
SqlSelect select = SqlDml.Select(employees);
select.Columns.AddRange(employees["EmployeeId"], employees["FirstName"], employees["LastName"], employees["BirthDate"]);
select.Where = employees["FirstName"] == SqlDml.ParameterRef(p.ParameterName);
select.OrderBy.Add(employees["LastName"]);
Assert.IsTrue(CompareExecuteDataReader(nativeSql, select));
}
protected SqlProvider CreateProvider(SqlSelect statement, QueryParameterBinding extraBinding,
CompilableProvider origin, params ExecutableProvider[] sources)
{
var extraBindings = extraBinding != null?Enumerable.Repeat(extraBinding, 1) : null;
return(CreateProvider(statement, extraBindings, origin, sources));
}
protected SqlProvider CreateProvider(SqlSelect statement, IEnumerable <QueryParameterBinding> extraBindings,
CompilableProvider origin, params ExecutableProvider[] sources)
{
var allowBatching = true;
var parameterBindings = extraBindings ?? Enumerable.Empty <QueryParameterBinding>();
foreach (var provider in sources.OfType <SqlProvider>())
{
var queryRequest = provider.Request;
allowBatching &= queryRequest.CheckOptions(QueryRequestOptions.AllowOptimization);
parameterBindings = parameterBindings.Concat(queryRequest.ParameterBindings);
}
var tupleDescriptor = origin.Header.TupleDescriptor;
var options = QueryRequestOptions.Empty;
if (allowBatching)
{
options |= QueryRequestOptions.AllowOptimization;
}
if (statement.Columns.Count < origin.Header.TupleDescriptor.Count)
{
tupleDescriptor = origin.Header.TupleDescriptor.Head(statement.Columns.Count);
}
var request = CreateQueryRequest(Driver, statement, parameterBindings, tupleDescriptor, options);
return(new SqlProvider(Handlers, request, origin, sources));
}
public void Test018()
{
string nativeSql = @"SELECT
c.CustomerId,
c.CompanyName,
c.LastName,
SUM(i.commission) AS Total
FROM
customer c
INNER JOIN invoice i ON (c.CustomerID = i.CustomerID)
GROUP BY
c.CustomerId,
c.CompanyName,
c.LastName
HAVING SUM(i.commission) > 140";
SqlTableRef customer = SqlDml.TableRef(schema.Tables["customer"], "c");
SqlTableRef invoice = SqlDml.TableRef(schema.Tables["invoice"], "i");
SqlSelect select = SqlDml.Select(customer.InnerJoin(invoice, customer["CustomerId"] == invoice["CustomerId"]));
select.Columns.AddRange(customer["CustomerID"], customer["CompanyName"], customer["LastName"]);
select.Columns.Add(SqlDml.Sum(invoice["Commission"]), "Total");
select.GroupBy.AddRange(customer["CustomerID"], customer["CompanyName"], customer["LastName"]);
select.Having = SqlDml.Sum(invoice["Commission"]) > 140;
Assert.IsTrue(CompareExecuteDataReader(nativeSql, select));
}
private void PushDown(SqlColumn column)
{
SqlSelect select = new SqlSelect(new SqlNop(column.ClrType, column.SqlType, column.SourceExpression), this.currentSelect.From, this.currentSelect.SourceExpression);
this.currentSelect.From = new SqlAlias(select);
select.Row.Columns.Add(column);
}
public void TotalTest()
{
var countFld = SqlField <Person> .Count(p => p.LastName);
var select = new SqlSelect <Person>()
.AddFields(p => p.LastName, p => p.Name)
.AddFields <Passport>(p => p.Number)
.AddFields(countFld)
.InnerJoin <Person, Passport>((person, passport) => person.Id == passport.PersonId)
.Where(SqlFilter <Passport> .From(p => p.Number).IsNotNull().And(p => p.Number).NotEqualTo("3812-808316"))
.GroupBy(p => p.LastName)
.Having(SqlFilter <Person> .From <int>(countFld).GreaterThan(2))
.OrderBy(p => p.LastName);
var expected =
@"SELECT
pe.LastName, pe.Name, pa.Number, COUNT(pe.LastName)
FROM
Person pe
INNER JOIN
Passport pa ON pe.Id = pa.PersonId
WHERE
pa.Number IS NOT NULL AND pa.Number <> '3812-808316'
GROUP BY
pe.LastName
HAVING
COUNT(pe.LastName) > 2
ORDER BY
pe.LastName";
Assert.Equal(expected, select.RawSql);
}
internal override SqlSelect VisitSelect(SqlSelect select)
{
/*
* We're travelling through <expression> of something like:
*
* SELECT <expression>
* FROM <alias>
*
* Inside the expression there may be a reference to <alias> that
* represents the dynamic type that we're trying to discover.
*
* In this case, the type relationship between AliasRef and Alias is
* T to IEnumerable<T>.
*
* We need to remember to 'unpivot' the type of IEnumerable<T> to
* get the correct dynamic type.
*
* Since SELECTs may be nested, we use a stack of pivots.
*
*/
this.UnwrapSequences = new UnwrapStack(this.UnwrapSequences, true);
VisitExpression(select.Selection);
this.UnwrapSequences = this.UnwrapSequences.Last;
return(select);
}
private SqlSource PushSourceDown(SqlSource sqlSource, List <SqlColumn> cols)
{
SqlSelect ns = new SqlSelect(new SqlNop(cols[0].ClrType, cols[0].SqlType, sqlSource.SourceExpression), sqlSource, sqlSource.SourceExpression);
ns.Row.Columns.AddRange(cols);
return(new SqlAlias(ns));
}
private SqlJoin GetLeftOuterWithUnreferencedSingletonOnLeft(SqlSource source)
{
SqlAlias alias = source as SqlAlias;
if (alias != null)
{
SqlSelect select = alias.Node as SqlSelect;
if (select != null &&
select.Where == null &&
select.Top == null &&
select.GroupBy.Count == 0 &&
select.OrderBy.Count == 0)
{
return(this.GetLeftOuterWithUnreferencedSingletonOnLeft(select.From));
}
}
SqlJoin join = source as SqlJoin;
if (join == null || join.JoinType != SqlJoinType.LeftOuter)
{
return(null);
}
if (!this.IsSingletonSelect(join.Left))
{
return(null);
}
HashSet <SqlAlias> p = SqlGatherProducedAliases.Gather(join.Left);
HashSet <SqlAlias> c = SqlGatherConsumedAliases.Gather(join.Right);
if (p.Overlaps(c))
{
return(null);
}
return(join);
}
internal override SqlSelect VisitSelect(SqlSelect select)
{
select.From = this.VisitSource(select.From);
select.Where = this.VisitPredicate(select.Where);
int num = 0;
int count = select.GroupBy.Count;
while (num < count)
{
select.GroupBy[num] = this.VisitExpression(select.GroupBy[num]);
num++;
}
select.Having = this.VisitPredicate(select.Having);
int num3 = 0;
int num4 = select.OrderBy.Count;
while (num3 < num4)
{
select.OrderBy[num3].Expression = this.VisitExpression(select.OrderBy[num3].Expression);
num3++;
}
select.Top = this.VisitExpression(select.Top);
select.Row = (SqlRow)this.Visit(select.Row);
return(select);
}
public override string Translate(SqlCompilerContext context, SqlSelect node, SelectSection section)
{
switch (section)
{
case SelectSection.HintsEntry:
return(string.Empty);
case SelectSection.HintsExit:
if (node.Hints.Count == 0)
{
return(string.Empty);
}
var hints = new List <string>(node.Hints.Count);
foreach (SqlHint hint in node.Hints)
{
if (hint is SqlNativeHint)
{
hints.Add(QuoteIdentifier((hint as SqlNativeHint).HintText));
}
}
return(hints.Count > 0 ? "USE INDEX (" + string.Join(", ", hints.ToArray()) + ")" : string.Empty);
default:
return(base.Translate(context, node, section));
}
}
public virtual void Visit(SqlSelect node)
{
VisitInternal(node.Asterisk);
foreach (SqlColumn column in node.Columns)
{
VisitInternal(column);
}
VisitInternal(node.From);
foreach (SqlColumn column in node.GroupBy)
{
VisitInternal(column);
}
VisitInternal(node.Having);
foreach (SqlHint hint in node.Hints)
{
VisitInternal(hint);
}
VisitInternal(node.Limit);
VisitInternal(node.Offset);
foreach (SqlOrder order in node.OrderBy)
{
VisitInternal(order);
}
VisitInternal(node.Where);
}
public void Test008()
{
string nativeSql = @"SELECT
t.TrackId,
t.[Name],
a.[Title]
FROM
Track t
INNER JOIN Album a ON (t.AlbumId = a.AlbumId)
ORDER BY
t.TrackId";
SqlTableRef track = SqlDml.TableRef(schema.Tables["Track"], "t");
SqlTableRef album = SqlDml.TableRef(schema.Tables["Album"], "a");
SqlSelect select = SqlDml.Select(track.InnerJoin(album, track["AlbumId"] == album["AlbumId"]));
select.Columns.Add(track["TrackId"]);
select.Columns.Add(track["Name"]);
select.Columns.Add(album["Title"]);
select.OrderBy.Add(track["TrackId"]);
Assert.IsTrue(CompareExecuteDataReader(nativeSql, select));
}
internal static bool CanBigJoin(SqlSelect select)
{
Visitor v = new Visitor();
v.Visit(select);
return(v.canBigJoin);
}
public void Test012()
{
string nativeSql = @"SELECT
CASE il.TrackId
WHEN 1 THEN 'STAFF_1'
WHEN 2 THEN 'STAFF_2'
ELSE 'STAFF_OTHER'
END AS shippers,
SUM(il.UnitPrice) AS TotalUnits
FROM [invoiceline] il
GROUP BY il.TrackId";
SqlTableRef invoiceLine = SqlDml.TableRef(schema.Tables["invoiceline"], "il");
SqlSelect select = SqlDml.Select(invoiceLine);
SqlCase totalPayment = SqlDml.Case(invoiceLine["TrackId"]);
totalPayment[1] = SqlDml.Literal("STAFF_1");
totalPayment[2] = SqlDml.Literal("STAFF_2");
totalPayment.Else = SqlDml.Literal("STAFF_OTHER");
select.Columns.Add(totalPayment, "shippers");
select.Columns.Add(SqlDml.Sum(invoiceLine["UnitPrice"]), "TotalUnits");
select.GroupBy.AddRange(invoiceLine["TrackId"]);
Assert.IsTrue(CompareExecuteDataReader(nativeSql, select));
}
public void Test019()
{
string nativeSql = @"SELECT
c.CustomerId,
c.CompanyName,
c.LastName
FROM
customer c
WHERE c.CustomerId IN (SELECT r.CustomerId FROM invoice r WHERE r.DesignatedEmployeeId = 8)
GROUP BY c.CustomerID,
c.CompanyName,
c.LastName";
SqlTableRef customer = SqlDml.TableRef(schema.Tables["customer"], "c");
SqlTableRef invoice = SqlDml.TableRef(schema.Tables["invoice"], "r");
SqlSelect innerSelect = SqlDml.Select(invoice);
innerSelect.Columns.Add(invoice["CustomerId"]);
innerSelect.Where = invoice["DesignatedEmployeeId"] == 8;
SqlSelect select = SqlDml.Select(customer);
select.Columns.Add(customer["CustomerId"]);
select.Columns.Add(customer["CompanyName"]);
select.Columns.Add(customer["LastName"]);
select.Where = SqlDml.In(customer["CustomerId"], innerSelect);
select.GroupBy.Add(customer["CustomerID"]);
select.GroupBy.Add(customer["CompanyName"]);
select.GroupBy.Add(customer["LastName"]);
Assert.IsTrue(CompareExecuteDataReader(nativeSql, select));
}
protected override SqlNode VisitFirst(Expression sequence, LambdaExpression lambda, bool isFirst)
{
SqlSelect select = this.LockSelect(this.VisitSequence(sequence));
if (lambda != null)
{
this.map[lambda.Parameters[0]] = select.Selection;
select.Where = this.VisitExpression(lambda.Body);
}
if (isFirst)
{
var valueType = typeof(int);
var sqlType = typeProvider.From(valueType);
var skipExp = sql.ValueFromObject(0, false, dominatingExpression);
var takeExp = sql.ValueFromObject(1, false, dominatingExpression);
IEnumerable <SqlExpression> expressions = new[] { takeExp, skipExp };
select.Top = new SqlFunctionCall(valueType, sqlType, "Limit", expressions, takeExp.SourceExpression);
}
if (this.outerNode)
{
return(select);
}
SqlNodeType nt = this.typeProvider.From(select.Selection.ClrType).CanBeColumn
? SqlNodeType.ScalarSubSelect
: SqlNodeType.Element;
return(this.sql.SubSelect(nt, select, sequence.Type));
}
/// <inheritdoc/>
protected override SqlProvider VisitSeek(SeekProvider provider)
{
var compiledSource = Compile(provider.Source);
SqlSelect source = compiledSource.Request.Statement;
var query = source.ShallowClone();
var parameterBindings = new List <QueryParameterBinding>();
var typeIdColumnName = Handlers.NameBuilder.TypeIdColumnName;
Func <KeyValuePair <int, Direction>, bool> filterNonTypeId =
pair => ((MappedColumn)provider.Header.Columns[pair.Key]).ColumnInfoRef.ColumnName != typeIdColumnName;
var keyColumns = provider.Header.Order
.Where(filterNonTypeId)
.ToList();
for (int i = 0; i < keyColumns.Count; i++)
{
int columnIndex = keyColumns[i].Key;
var sqlColumn = query.Columns[columnIndex];
var column = provider.Header.Columns[columnIndex];
TypeMapping typeMapping = Driver.GetTypeMapping(column.Type);
var binding = new QueryParameterBinding(typeMapping, GetSeekKeyElementAccessor(provider.Key, i));
parameterBindings.Add(binding);
query.Where &= sqlColumn == binding.ParameterReference;
}
return(CreateProvider(query, parameterBindings, provider, compiledSource));
}
private void GetLoyaltyInfoFromDB(LoyaltyItem loyaltyItem)
{
SqlSelect sql = new SqlSelect("RETAILLOYALTYMSRCARDTABLE M "
+ "INNER JOIN RETAILLOYALTYCUSTTABLE C ON M.LOYALTYCUSTID = C.LOYALTYCUSTID "
+ "INNER JOIN RETAILLOYALTYSCHEMESTABLE S ON S.LOYALTYSCHEMEID = M.LOYALTYSCHEMEID ");
sql.Select("M.LOYALTYSCHEMEID");
sql.Select("M.LOYALTYCUSTID");
sql.Select("ACCOUNTNUM");
sql.Select("EXPIRATIONTIMEUNIT");
sql.Select("EXPIRATIONTIMEVALUE");
sql.Where("M.DATAAREAID", Application.Settings.Database.DataAreaID, true);
sql.Where("M.CARDNUMBER", loyaltyItem.LoyaltyCardNumber, true); // Sale Unit of Measure
DataTable dataTable = new DBUtil(Application.Settings.Database.Connection).GetTable(sql);
if (dataTable.Rows.Count > 0)
{
loyaltyItem.SchemeID = Utility.ToString(dataTable.Rows[0]["LOYALTYSCHEMEID"]);
loyaltyItem.LoyaltyCustID = Utility.ToString(dataTable.Rows[0]["LOYALTYCUSTID"]);
loyaltyItem.CustID = Utility.ToString(dataTable.Rows[0]["ACCOUNTNUM"]);
loyaltyItem.ExpireUnit = Utility.ToInt(dataTable.Rows[0]["EXPIRATIONTIMEUNIT"]);
loyaltyItem.ExpireValue = Utility.ToInt(dataTable.Rows[0]["EXPIRATIONTIMEVALUE"]);
}
}
/// <inheritdoc/>
protected override SqlProvider VisitCalculate(CalculateProvider provider)
{
var source = Compile(provider.Source);
SqlSelect sqlSelect;
if (provider.Source.Header.Length == 0)
{
SqlSelect sourceSelect = source.Request.Statement;
sqlSelect = sourceSelect.ShallowClone();
sqlSelect.Columns.Clear();
}
else
{
sqlSelect = ExtractSqlSelect(provider, source);
}
var sourceColumns = ExtractColumnExpressions(sqlSelect);
var allBindings = EnumerableUtils <QueryParameterBinding> .Empty;
foreach (var column in provider.CalculatedColumns)
{
var result = ProcessExpression(column.Expression, sourceColumns);
var predicate = result.First;
var bindings = result.Second;
if (column.Type.StripNullable() == typeof(bool))
{
predicate = GetBooleanColumnExpression(predicate);
}
AddInlinableColumn(provider, column, sqlSelect, predicate);
allBindings = allBindings.Concat(bindings);
}
return(CreateProvider(sqlSelect, allBindings, provider, source));
}
public void Test026()
{
string nativeSql = @"SELECT
p.CustomerId,
p.Commission
FROM
invoice p
WHERE
p.Commission = (SELECT MIN(Commission) AS LowestCommission FROM invoice)";
SqlTableRef invoice1 = SqlDml.TableRef(schema.Tables["invoice"], "p1");
SqlTableRef invoice2 = SqlDml.TableRef(schema.Tables["invoice"], "p2");
SqlSelect innerSelect = SqlDml.Select(invoice2);
innerSelect.Columns.Add(SqlDml.Min(invoice2["Commission"]));
SqlSelect select = SqlDml.Select(invoice1);
select.Columns.Add(invoice1["CustomerId"]);
select.Columns.Add(invoice1["Commission"]);
select.Where = SqlDml.Equals(invoice1["Commission"], innerSelect);
Assert.IsTrue(CompareExecuteDataReader(nativeSql, select));
}
internal override SqlSelect VisitSelect(SqlSelect select)
{
bool topLevel = this.topLevel;
this.topLevel = false;
select.Top = this.VisitExpression(select.Top);
select.Row = (SqlRow)this.Visit(select.Row);
select.From = (SqlSource)this.Visit(select.From);
select.Where = this.VisitExpression(select.Where);
int num = 0;
int count = select.GroupBy.Count;
while (num < count)
{
select.GroupBy[num] = this.VisitExpression(select.GroupBy[num]);
num++;
}
select.Having = this.VisitExpression(select.Having);
int num3 = 0;
int num4 = select.OrderBy.Count;
while (num3 < num4)
{
select.OrderBy[num3].Expression = this.VisitExpression(select.OrderBy[num3].Expression);
num3++;
}
this.topLevel = topLevel;
select.Selection = this.VisitExpression(select.Selection);
return(select);
}
internal override SqlExpression VisitMultiset(SqlSubSelect sms) {
// allow one big-join per query?
if ((this.options & Options.EnableBigJoin) != 0 &&
!this.hasBigJoin && this.canJoin && this.isTopLevel && this.outerSelect != null
&& !MultisetChecker.HasMultiset(sms.Select.Selection)
&& BigJoinChecker.CanBigJoin(sms.Select)) {
sms.Select = this.VisitSelect(sms.Select);
SqlAlias alias = new SqlAlias(sms.Select);
SqlJoin join = new SqlJoin(SqlJoinType.OuterApply, this.outerSelect.From, alias, null, sms.SourceExpression);
this.outerSelect.From = join;
this.outerSelect.OrderingType = SqlOrderingType.Always;
// make joined expression
SqlExpression expr = (SqlExpression)SqlDuplicator.Copy(sms.Select.Selection);
// make count expression
SqlSelect copySelect = (SqlSelect)SqlDuplicator.Copy(sms.Select);
SqlAlias copyAlias = new SqlAlias(copySelect);
SqlSelect countSelect = new SqlSelect(sql.Unary(SqlNodeType.Count, null, sms.SourceExpression), copyAlias, sms.SourceExpression);
countSelect.OrderingType = SqlOrderingType.Never;
SqlExpression count = sql.SubSelect(SqlNodeType.ScalarSubSelect, countSelect);
// make joined collection
SqlJoinedCollection jc = new SqlJoinedCollection(sms.ClrType, sms.SqlType, expr, count, sms.SourceExpression);
this.hasBigJoin = true;
return jc;
}
else {
return QueryExtractor.Extract(sms, this.parentParameters);
}
}
private void frm_grant_sa_Load(object sender, EventArgs e)
{
SqlConnection con = new SqlConnection(SqlLink.linkmethod());
SqlCommand cmd = new SqlCommand(SqlSelect.GrantLoadlocalusermethod(), con);
cmd.Parameters.AddWithValue("@username", Account);
con.Open();
SqlDataAdapter adapt = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
adapt.Fill(ds);
con.Close();
dgv_grant_sa_localuser.DataSource = ds.Tables[0];
SqlConnection con1 = new SqlConnection(SqlLink.linkmethod());
SqlCommand cmd1 = new SqlCommand(SqlSelect.GrantLoadAllusermethod(), con1);
con1.Open();
SqlDataAdapter adapt1 = new SqlDataAdapter(cmd1);
DataSet ds1 = new DataSet();
adapt1.Fill(ds1);
con.Close();
dgv_grant_sa_alluser.DataSource = ds1.Tables[0];
}
internal override SqlSelect VisitSelect(SqlSelect select) {
Scope save = this.current;
this.current = new Scope(select.Where, this.current);
SqlSelect result = base.VisitSelect(select);
bool stopHoisting =
select.IsDistinct ||
select.GroupBy.Count > 0 ||
this.aggregateChecker.HasAggregates(select) ||
select.Top != null ||
this.rowNumberChecker.HasRowNumber(select);
// Shift as much of the current WHERE to the parent as possible.
if (this.current != null) {
if (this.current.Parent != null && !stopHoisting) {
this.current.Parent.Where = sql.AndAccumulate(this.current.Parent.Where, this.current.Where);
this.current.Where = null;
}
select.Where = this.current.Where;
}
this.current = save;
return result;
}
protected override SqlSelect VisitSqlSelect(SqlSelect sqlSelect)
{
_sql.Append("SELECT ");
//SELECT
this.GenerateSelection(sqlSelect);
//FROM
_sql.AppendLine();
_sql.Append("FROM ");
this.Visit(sqlSelect.From);
//WHERE
if (sqlSelect.Where != null)
{
_sql.AppendLine();
_sql.Append("WHERE ");
this.Visit(sqlSelect.Where);
}
//ORDER BY
if (!sqlSelect.IsCounting && sqlSelect.OrderBy != null && sqlSelect.OrderBy.Count > 0)
{
_sql.AppendLine();
this.Visit(sqlSelect.OrderBy);
}
return(sqlSelect);
}
protected Result <T[]> Query <T>(SqlSelect select, Action <ApiQueryOptions <T> > options, Func <SqlRow, T> create)
{
if (select == null)
{
return(NotFound());
}
var opt = new ApiQueryOptions <T>();
options?.Invoke(opt);
Values <string> fields = null;
List <string> displayFields = null;
if (create != null)
{
}
else
{
var idBuilderStack = select.GetBuilderStack(Sql.Id);
if (idBuilderStack == null)
{
return(Unavailable($"Builder with the identifier key ({Sql.Id}) is not found in the current builders. Please review the select function for this method."));
}
fields = Parameters.Fields;
displayFields = new();
if (Parameters.Queries != null && Parameters.Queries.Length > 0)
{
Dictionary <string, (SqlQueryType, string)[]> queries = new();
private void btn_accountcheck_Click(object sender, EventArgs e)
{
SqlConnection con = new SqlConnection(SqlLink.linkmethod());
SqlCommand cmd = new SqlCommand(SqlSelect.UserCheckmethod(), con);
cmd.Parameters.AddWithValue("@username", tb_account.Text);
con.Open();
SqlDataReader dataReader = cmd.ExecuteReader();
DataSet ds = new DataSet();
con.Close();
if (dataReader.Read())
{
if (dataReader[0].ToString() == "0")
{
MessageBox.Show("帳號可以使用");
return;
}
else
{
MessageBox.Show("帳號已被使用");
return;
}
}
}
public void Test027()
{
string nativeSql = @"SELECT
c.CustomerId,
c.CompanyName
FROM
customer c
WHERE EXISTS
(SELECT * FROM invoice i WHERE i.Commission < 1.00 AND i.CustomerId = c.CustomerId )";
SqlTableRef customer = SqlDml.TableRef(schema.Tables["customer"], "c");
SqlTableRef invoice = SqlDml.TableRef(schema.Tables["invoice"], "i");
SqlSelect innerSelect = SqlDml.Select(invoice);
SqlSelect select = SqlDml.Select(customer);
innerSelect.Columns.Add(SqlDml.Asterisk);
innerSelect.Where = invoice["Commission"] < 11.00 && invoice["CustomerId"] == customer["CustomerId"];
select.Columns.Add(customer["CustomerId"]);
select.Columns.Add(customer["CompanyName"]);
select.Where = SqlDml.Exists(innerSelect);
Assert.IsTrue(CompareExecuteDataReader(nativeSql, select));
}
/// <inheritdoc/>
protected override SqlProvider VisitUnion(UnionProvider provider)
{
var left = Compile(provider.Left);
var right = Compile(provider.Right);
var leftSelect = left.Request.Statement;
var keepOrderForLeft = (leftSelect.HasLimit || leftSelect.HasOffset) && providerInfo.Supports(ProviderFeatures.PagingRequiresOrderBy);
if (!keepOrderForLeft)
{
leftSelect.OrderBy.Clear();
}
var rightSelect = right.Request.Statement;
var keepOrderForRight = (leftSelect.HasLimit || leftSelect.HasOffset) && providerInfo.Supports(ProviderFeatures.PagingRequiresOrderBy);
if (!keepOrderForRight)
{
rightSelect.OrderBy.Clear();
}
var result = SqlDml.Union(leftSelect, rightSelect);
var queryRef = SqlDml.QueryRef(result);
SqlSelect query = SqlDml.Select(queryRef);
query.Columns.AddRange(queryRef.Columns.Cast <SqlColumn>());
return(CreateProvider(query, provider, left, right));
}
internal override SqlSelect VisitSelect(SqlSelect select) {
select = base.VisitSelect(select);
string[] names = new string[select.Row.Columns.Count];
for (int i = 0, n = names.Length; i < n; i++) {
SqlColumn c = select.Row.Columns[i];
string name = c.Name;
if (name == null) {
name = SqlNamer.DiscoverName(c);
}
names[i] = name;
c.Name = null;
}
var reservedNames = this.GetColumnNames(select.OrderBy);
for (int i = 0, n = select.Row.Columns.Count; i < n; i++) {
SqlColumn c = select.Row.Columns[i];
string rootName = names[i];
string name = rootName;
if (this.makeUnique) {
int iName = 1;
while (!this.IsUniqueName(select.Row.Columns, reservedNames, c, name)) {
iName++;
name = rootName + iName;
}
}
c.Name = name;
c.Ordinal = i;
}
return select;
}
private void AnalyzeSelectStmt(TSelectSqlStatement pSqlstmt)
{
SqlSelect q = new SqlSelect();
// where clause this query is rule Select
if (pSqlstmt.WhereClause != null)
{
Console.WriteLine(pSqlstmt.WhereClauseText);
q.WhereClause = pSqlstmt.WhereClauseText;
}
//column here, when column != *, indep project
List<string> names = new List<string>();
foreach (TLzField fld in pSqlstmt.Fields)
{
var lcstr ="\n\tFullname:" + fld.FieldFullname;
lcstr = lcstr + "\n\tPrefix:" + fld.FieldPrefix;
lcstr = lcstr + "\tColumn:" + fld.FieldName;
lcstr = lcstr + "\talias:" + fld.FieldAlias;
Console.WriteLine(lcstr);
names.Add(fld.FieldName);
}
q.SelectClause = names;
// join
names = new List<string>();
foreach (var table in pSqlstmt.JoinTables)
{
Console.WriteLine(table.JoinTable.TableName);
Console.WriteLine(table.JoinItems.Count());
names.Add(table.JoinTable.TableName);
}
q.SelectClause = names;
switch (pSqlstmt.SelectSetType)
{
case TSelectSetType.sltNone:
break;
case TSelectSetType.sltUnion:
// indep union rule
Console.WriteLine(pSqlstmt.SelectClauseText);
break;
case TSelectSetType.sltUnionAll:
break;
case TSelectSetType.sltMinus:
break;
case TSelectSetType.sltIntersect:
break;
case TSelectSetType.sltIntersectAll:
break;
case TSelectSetType.sltExcept:
// indep negation rule
Console.WriteLine(pSqlstmt.FromClauseText);
break;
case TSelectSetType.sltExceptAll:
break;
default:
throw new ArgumentOutOfRangeException();
}
}
internal override SqlSelect VisitSelect(SqlSelect select) {
SqlSelect save = this.currentSelect;
try {
this.currentSelect = select;
return base.VisitSelect(select);
}
finally {
this.currentSelect = save;
}
}
internal override SqlSelect VisitSelectCore(SqlSelect select) {
bool saveIsTopLevel = this.isTopLevel;
this.isTopLevel = false;
try {
return base.VisitSelectCore(select);
}
finally {
this.isTopLevel = saveIsTopLevel;
}
}
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;
}
}
internal override SqlSelect VisitSelect(SqlSelect select) {
if (select.IsDistinct) {
bool changed;
bool containsLongExpressions;
ConvertColumnsToMax(select, out changed, out containsLongExpressions);
if (containsLongExpressions) {
this.annotations.Add(select, new SqlServerCompatibilityAnnotation(
Strings.TextNTextAndImageCannotOccurInDistinct(select.SourceExpression), SqlProvider.ProviderMode.Sql2000, SqlProvider.ProviderMode.SqlCE));
}
}
return base.VisitSelect(select);
}
internal override SqlSelect VisitSelect(SqlSelect select) {
// DevDiv 179191
if (select.Where != null && select.Where.NodeType == SqlNodeType.Coalesce) {
SqlBinary bin = (SqlBinary)select.Where;
if (bin.Right.NodeType == SqlNodeType.Value) {
SqlValue value = (SqlValue)bin.Right;
if (value.Value != null && value.Value.GetType() == typeof(bool) && (bool)value.Value == false) {
select.Where = bin.Left;
}
}
}
return base.VisitSelect(select);
}
internal override SqlSelect VisitSelect(SqlSelect select) {
base.VisitSelect(select);
if (select.Top != null) {
if (select.Top.NodeType == SqlNodeType.Value) {
SqlValue val = (SqlValue)select.Top;
// convert to literal value for SQL2K compatibility
if (val.IsClientSpecified) {
select.Top = sql.Value(val.ClrType, val.SqlType, val.Value, false, val.SourceExpression);
}
}
else {
// cannot be converted to literal value. note that this select is not SQL2K compatible
this.annotations.Add(select.Top, new SqlServerCompatibilityAnnotation(Strings.SourceExpressionAnnotation(select.Top.SourceExpression), SqlProvider.ProviderMode.Sql2000));
}
}
return select;
}
internal override SqlSelect VisitSelect(SqlSelect select) {
select.From = this.VisitSource(select.From);
select.Where = this.VisitPredicate(select.Where);
for (int i = 0, n = select.GroupBy.Count; i < n; i++) {
select.GroupBy[i] = this.VisitExpression(select.GroupBy[i]);
}
select.Having = this.VisitPredicate(select.Having);
for (int i = 0, n = select.OrderBy.Count; i < n; i++) {
select.OrderBy[i].Expression = this.VisitExpression(select.OrderBy[i].Expression);
}
select.Top = this.VisitExpression(select.Top);
select.Row = (SqlRow)this.Visit(select.Row);
// don't visit selection
//select.Selection = this.VisitExpression(select.Selection);
return select;
}
internal override SqlSelect VisitSelect(SqlSelect select) {
select = base.VisitSelect(select);
select.Selection = this.FlattenSelection(select.Row, false, select.Selection);
if (select.GroupBy.Count > 0) {
this.FlattenGroupBy(select.GroupBy);
}
if (select.OrderBy.Count > 0) {
this.FlattenOrderBy(select.OrderBy);
}
if (!this.isTopLevel) {
select.Selection = new SqlNop(select.Selection.ClrType, select.Selection.SqlType, select.SourceExpression);
}
return select;
}
internal override SqlSelect VisitSelect(SqlSelect select) {
SelectScope s = expressionSink;
// Don't lift through a TOP.
if (select.Top != null) {
expressionSink = null;
}
// Don't lift through a GROUP BY (or implicit GROUP BY).
if (select.GroupBy.Count > 0 || this.aggregateChecker.HasAggregates(select)) {
expressionSink = null;
}
// Don't lift through DISTINCT
if (select.IsDistinct) {
expressionSink = null;
}
if (expressionSink != null) {
List<SqlColumn> keep = new List<SqlColumn>();
List<SqlColumn> lift = new List<SqlColumn>();
foreach (SqlColumn sc in select.Row.Columns) {
bool referencesLeftsideAliases = SqlAliasesReferenced.ReferencesAny(sc.Expression, expressionSink.LeftProduction);
bool isLockedExpression = expressionSink.ReferencedExpressions.Contains(sc);
if (referencesLeftsideAliases && !isLockedExpression) {
lift.Add(sc);
} else {
keep.Add(sc);
}
}
select.Row.Columns.Clear();
select.Row.Columns.AddRange(keep);
if (lift.Count > 0) {
expressionSink.Lifted.Push(lift);
}
}
SqlSelect sel = base.VisitSelect(select);
expressionSink = s;
return sel;
}
internal override SqlSelect VisitSelect(SqlSelect select) {
int i = 0;
List<SqlOrderExpression> orders = select.OrderBy;
while (i < orders.Count) {
SqlExpression expr = orders[i].Expression;
while (expr.NodeType == SqlNodeType.DiscriminatedType) {
expr = ((SqlDiscriminatedType)expr).Discriminator;
}
switch (expr.NodeType) {
case SqlNodeType.Value:
case SqlNodeType.Parameter:
orders.RemoveAt(i);
break;
default:
++i;
break;
}
}
return base.VisitSelect(select);
}
internal override SqlSelect VisitSelect(SqlSelect select) {
base.VisitSelect(select);
// enforce exact ordering of columns in union selects
SqlUnion union = this.GetUnion(select.From);
if (union != null) {
SqlSelect sleft = union.Left as SqlSelect;
SqlSelect sright = union.Right as SqlSelect;
if (sleft != null & sright != null) {
// preset ordinals to high values (so any unreachable column definition is ordered last)
for (int i = 0, n = sleft.Row.Columns.Count; i < n; i++) {
sleft.Row.Columns[i].Ordinal = select.Row.Columns.Count + i;
}
for (int i = 0, n = sright.Row.Columns.Count; i < n; i++) {
sright.Row.Columns[i].Ordinal = select.Row.Columns.Count + i;
}
// next assign ordinals to all direct columns in subselects
for (int i = 0, n = select.Row.Columns.Count; i < n; i++) {
SqlExprSet es = select.Row.Columns[i].Expression as SqlExprSet;
if (es != null) {
for (int e = 0, en = es.Expressions.Count; e < en; e++) {
SqlColumnRef cr = es.Expressions[e] as SqlColumnRef;
if (cr != null && e >= select.Row.Columns.Count) {
cr.Column.Ordinal = i;
}
}
}
}
// next sort columns in left & right subselects
Comparison<SqlColumn> comp = (x,y) => x.Ordinal - y.Ordinal;
sleft.Row.Columns.Sort(comp);
sright.Row.Columns.Sort(comp);
}
}
return select;
}
private SqlSource PushSourceDown(SqlSource sqlSource, List<SqlColumn> cols) {
SqlSelect ns = new SqlSelect(new SqlNop(cols[0].ClrType, cols[0].SqlType, sqlSource.SourceExpression), sqlSource, sqlSource.SourceExpression);
ns.Row.Columns.AddRange(cols);
return new SqlAlias(ns);
}
private SqlSelect VisitDefaultIfEmpty(Expression sequence) {
SqlSelect select = this.VisitSequence(sequence);
SqlAlias alias = new SqlAlias(select);
SqlAliasRef aliasRef = new SqlAliasRef(alias);
SqlExpression opt = new SqlOptionalValue(
new SqlColumn(
"test",
sql.Unary(SqlNodeType.OuterJoinedValue,
sql.Value(typeof(int?), this.typeProvider.From(typeof(int)), 1, false, this.dominatingExpression)
)
),
sql.Unary(SqlNodeType.OuterJoinedValue, aliasRef)
);
SqlSelect optSelect = new SqlSelect(opt, alias, this.dominatingExpression);
alias = new SqlAlias(optSelect);
aliasRef = new SqlAliasRef(alias);
SqlExpression litNull = sql.TypedLiteralNull(typeof(string), this.dominatingExpression);
SqlSelect selNull = new SqlSelect(litNull, null, this.dominatingExpression);
SqlAlias aliasNull = new SqlAlias(selNull);
SqlJoin join = new SqlJoin(SqlJoinType.OuterApply, aliasNull, alias, null, this.dominatingExpression);
return new SqlSelect(aliasRef, join, this.dominatingExpression);
}
internal override SqlSelect VisitSelect(SqlSelect select) {
// big-joins may need to lift PK's out for default ordering, so don't allow big-join if we see these
this.canBigJoin &= select.GroupBy.Count == 0 && select.Top == null && !select.IsDistinct;
if (!this.canBigJoin) {
return select;
}
return base.VisitSelect(select);
}
internal static bool CanBigJoin(SqlSelect select) {
Visitor v = new Visitor();
v.Visit(select);
return v.canBigJoin;
}
internal override SqlSelect VisitSelect(SqlSelect select) {
SqlSelect saveSelect = this.outerSelect;
this.outerSelect = select;
// big-joins may need to lift PK's out for default ordering, so don't allow big-join if we see these
this.canJoin &= select.GroupBy.Count == 0 && select.Top == null && !select.IsDistinct;
bool saveIsTopLevel = this.isTopLevel;
this.isTopLevel = false;
select = this.VisitSelectCore(select);
this.isTopLevel = saveIsTopLevel;
select.Selection = this.VisitExpression(select.Selection);
this.isTopLevel = saveIsTopLevel;
this.outerSelect = saveSelect;
if (select.IsDistinct && HierarchyChecker.HasHierarchy(select.Selection)) {
// distinct across heirarchy is a NO-OP
select.IsDistinct = false;
}
return select;
}
internal override SqlSelect VisitSelect(SqlSelect select) {
foreach (SqlColumn c in select.Row.Columns) {
this.columns.Add(c);
}
return select;
}
private void PushDown(SqlColumn column) {
SqlSelect select = new SqlSelect(new SqlNop(column.ClrType, column.SqlType, column.SourceExpression), this.currentSelect.From, this.currentSelect.SourceExpression);
this.currentSelect.From = new SqlAlias(select);
select.Row.Columns.Add(column);
}
internal override SqlSelect VisitSelect(SqlSelect select) {
bool saveTop = this.topSelect;
bool savePK = this.addPrimaryKeys;
SqlSelect saveSelect = this.currentSelect;
this.currentSelect = select;
if (select.OrderingType == SqlOrderingType.Always) {
this.addPrimaryKeys = true;
}
this.topSelect = false;
// can't forward ordering information through a group-by
if (select.GroupBy.Count > 0) {
this.Visit(select.From);
this.orders = null;
}
else {
this.Visit(select.From);
}
if (select.OrderBy.Count > 0) {
this.PrependOrderExpressions(select.OrderBy);
}
List<SqlOrderExpression> save = this.orders;
this.orders = null;
this.rowNumberOrders = save; // lest orders be null when we need info
/* do all the lower level stuff */
select.Where = this.VisitExpression(select.Where);
for (int i = 0, n = select.GroupBy.Count; i < n; i++) {
select.GroupBy[i] = this.VisitExpression(select.GroupBy[i]);
}
select.Having = this.VisitExpression(select.Having);
for (int i = 0, n = select.OrderBy.Count; i < n; i++) {
select.OrderBy[i].Expression = this.VisitExpression(select.OrderBy[i].Expression);
}
select.Top = this.VisitExpression(select.Top);
select.Selection = this.VisitExpression(select.Selection);
select.Row = (SqlRow)this.Visit(select.Row);
this.topSelect = saveTop;
this.addPrimaryKeys = savePK;
this.orders = save;
// all ordering is blocked for this layer and above
if (select.OrderingType == SqlOrderingType.Blocked) {
this.orders = null;
}
// rebuild orderby expressions, provided this select doesn't contain a SqlRowNumber
// otherwise, replace the orderby with a reference to that column
select.OrderBy.Clear();
var rowNumberChecker = new SqlRowNumberChecker();
if (rowNumberChecker.HasRowNumber(select) && rowNumberChecker.RowNumberColumn != null) {
select.Row.Columns.Remove(rowNumberChecker.RowNumberColumn);
this.PushDown(rowNumberChecker.RowNumberColumn);
this.Orders.Add(new SqlOrderExpression(SqlOrderType.Ascending, new SqlColumnRef(rowNumberChecker.RowNumberColumn)));
}
if ((this.topSelect || select.Top != null) && select.OrderingType != SqlOrderingType.Never && this.orders != null) {
this.orders = new HashSet<SqlOrderExpression>(this.orders).ToList();
SqlDuplicator dup = new SqlDuplicator(true);
foreach (SqlOrderExpression sox in this.orders) {
select.OrderBy.Add(new SqlOrderExpression(sox.OrderType, (SqlExpression)dup.Duplicate(sox.Expression)));
}
}
this.currentSelect = saveSelect;
return select;
}
private SqlSelect VisitGroupJoin(Expression outerSequence, Expression innerSequence, LambdaExpression outerKeySelector, LambdaExpression innerKeySelector, LambdaExpression resultSelector) {
SqlSelect outerSelect = this.VisitSequence(outerSequence);
SqlSelect innerSelect = this.VisitSequence(innerSequence);
SqlAlias outerAlias = new SqlAlias(outerSelect);
SqlAliasRef outerRef = new SqlAliasRef(outerAlias);
SqlAlias innerAlias = new SqlAlias(innerSelect);
SqlAliasRef innerRef = new SqlAliasRef(innerAlias);
this.map[outerKeySelector.Parameters[0]] = outerRef;
SqlExpression outerKey = this.VisitExpression(outerKeySelector.Body);
this.map[innerKeySelector.Parameters[0]] = innerRef;
SqlExpression innerKey = this.VisitExpression(innerKeySelector.Body);
// make multiset
SqlExpression pred = sql.Binary(SqlNodeType.EQ, outerKey, innerKey);
SqlSelect select = new SqlSelect(innerRef, innerAlias, this.dominatingExpression);
select.Where = pred;
SqlSubSelect subquery = sql.SubSelect(SqlNodeType.Multiset, select);
// make outer ref & multiset for result-selector params
this.map[resultSelector.Parameters[0]] = outerRef;
this.dupMap[resultSelector.Parameters[1]] = subquery;
SqlExpression result = this.VisitExpression(resultSelector.Body);
return new SqlSelect(result, outerAlias, this.dominatingExpression);
}
/// <summary>
/// Visits the specified <see cref="SqlSelect"/>.
/// </summary>
/// <param name="expression">
/// The expression to visit.
/// </param>
public virtual void Visit(SqlSelect expression)
{
}
/// <summary>
/// Convert inner expression from C# expression to basic SQL Query.
/// </summary>
/// <param name="node">The expression to convert.</param>
/// <returns>The converted SQL query.</returns>
internal SqlNode ConvertOuter(Expression node) {
this.dominatingExpression = node;
this.outerNode = true;
SqlNode retNode;
if (typeof(ITable).IsAssignableFrom(node.Type)) {
retNode = this.VisitSequence(node);
}
else {
retNode = this.VisitInner(node);
}
if (retNode.NodeType == SqlNodeType.MethodCall) {
// if a tree consists of a single method call expression only, that method
// must be either a mapped stored procedure or a mapped function
throw Error.InvalidMethodExecution(((SqlMethodCall)retNode).Method.Name);
}
// if after conversion the node is an expression, we must
// wrap it in a select
SqlExpression sqlExpression = retNode as SqlExpression;
if (sqlExpression != null) {
retNode = new SqlSelect(sqlExpression, null, this.dominatingExpression);
}
retNode = new SqlIncludeScope(retNode, this.dominatingExpression);
return retNode;
}
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;
}
internal override SqlSelect VisitSelect(SqlSelect select) {
if (select.IsDistinct) {
this.IsDistinct = true;
// get all members from selection
this.AddIdentityMembers(select.Selection.ClrType.GetProperties());
return select;
}
//
//
//
//
//
// We're not distinct, but let's check our sources...
select.From = (SqlSource)base.Visit(select.From);
if (this.IdentityMembers == null || this.IdentityMembers.Count == 0) {
throw Error.SkipRequiresSingleTableQueryWithPKs();
}
else {
switch (select.Selection.NodeType) {
case SqlNodeType.Column:
case SqlNodeType.ColumnRef:
case SqlNodeType.Member: {
// we've got a bare member/column node, eg "select c.CustomerId"
// find out if it refers to the table's PK, of which there must be only 1
if (this.IdentityMembers.Count == 1) {
MemberInfo column = this.IdentityMembers[0];
this.IsValid &= IsColumnMatch(column, select.Selection);
}
else {
this.IsValid = false;
}
break;
}
case SqlNodeType.New:
case SqlNodeType.AliasRef: {
select.Selection = this.VisitExpression(select.Selection);
break;
}
case SqlNodeType.Treat:
case SqlNodeType.TypeCase: {
break;
}
default: {
this.IsValid = false;
break;
}
}
}
return select;
}
internal override SqlSelect VisitSelect(SqlSelect ss) {
if (ss.DoNotOutput) {
return ss;
}
string from = null;
if (ss.From != null) {
StringBuilder savesb = this.sb;
this.sb = new StringBuilder();
if (this.IsSimpleCrossJoinList(ss.From)) {
this.VisitCrossJoinList(ss.From);
} else {
this.Visit(ss.From);
}
from = this.sb.ToString();
this.sb = savesb;
}
sb.Append("SELECT ");
if (ss.IsDistinct) {
sb.Append("DISTINCT ");
}
if (ss.Top != null) {
sb.Append("TOP ");
if (this.parenthesizeTop) {
sb.Append("(");
}
this.Visit(ss.Top);
if (this.parenthesizeTop) {
sb.Append(")");
}
sb.Append(" ");
if (ss.IsPercent) {
sb.Append(" PERCENT ");
}
}
if (ss.Row.Columns.Count > 0) {
this.VisitRow(ss.Row);
} else if (this.isDebugMode) {
this.Visit(ss.Selection);
} else {
sb.Append("NULL AS [EMPTY]");
}
if (from != null) {
this.NewLine();
sb.Append("FROM ");
sb.Append(from);
}
if (ss.Where != null) {
this.NewLine();
sb.Append("WHERE ");
this.Visit(ss.Where);
}
if (ss.GroupBy.Count > 0) {
this.NewLine();
sb.Append("GROUP BY ");
for (int i = 0, n = ss.GroupBy.Count; i < n; i++) {
SqlExpression exp = ss.GroupBy[i];
if (i > 0)
sb.Append(", ");
this.Visit(exp);
}
if (ss.Having != null) {
this.NewLine();
sb.Append("HAVING ");
this.Visit(ss.Having);
}
}
if (ss.OrderBy.Count > 0 && ss.OrderingType != SqlOrderingType.Never) {
this.NewLine();
sb.Append("ORDER BY ");
for (int i = 0, n = ss.OrderBy.Count; i < n; i++) {
SqlOrderExpression exp = ss.OrderBy[i];
if (i > 0)
sb.Append(", ");
this.Visit(exp.Expression);
if (exp.OrderType == SqlOrderType.Descending) {
sb.Append(" DESC");
}
}
}
return ss;
}
private SqlSelect VisitJoin(Expression outerSequence, Expression innerSequence, LambdaExpression outerKeySelector, LambdaExpression innerKeySelector, LambdaExpression resultSelector) {
SqlSelect outerSelect = this.VisitSequence(outerSequence);
SqlSelect innerSelect = this.VisitSequence(innerSequence);
SqlAlias outerAlias = new SqlAlias(outerSelect);
SqlAliasRef outerRef = new SqlAliasRef(outerAlias);
SqlAlias innerAlias = new SqlAlias(innerSelect);
SqlAliasRef innerRef = new SqlAliasRef(innerAlias);
this.map[outerKeySelector.Parameters[0]] = outerRef;
SqlExpression outerKey = this.VisitExpression(outerKeySelector.Body);
this.map[innerKeySelector.Parameters[0]] = innerRef;
SqlExpression innerKey = this.VisitExpression(innerKeySelector.Body);
this.map[resultSelector.Parameters[0]] = outerRef;
this.map[resultSelector.Parameters[1]] = innerRef;
SqlExpression result = this.VisitExpression(resultSelector.Body);
SqlExpression condition = sql.Binary(SqlNodeType.EQ, outerKey, innerKey);
SqlSelect select = null;
if ((this.converterStrategy & ConverterStrategy.CanUseJoinOn) != 0) {
SqlJoin join = new SqlJoin(SqlJoinType.Inner, outerAlias, innerAlias, condition, this.dominatingExpression);
select = new SqlSelect(result, join, this.dominatingExpression);
} else {
SqlJoin join = new SqlJoin(SqlJoinType.Cross, outerAlias, innerAlias, null, this.dominatingExpression);
select = new SqlSelect(result, join, this.dominatingExpression);
select.Where = condition;
}
return select;
}
