public override DataBaseInfo GetDataBaseInfo()
{
string version = "";
using (SqlQuery query = new SqlQuery())
{
query.CommandText = "SELECT size*8,(status & 0x40) as type FROM sysfiles;SELECT @@VERSION";
DataBaseInfo dataBaseInfo = new DataBaseInfo();
using (XSqlDataReader reader = query.ExecuteReader())
{
int dataSize = 0;
int logoSize = 0;
while (reader.Read())
{
if (reader.GetInt32(1) == 0)
dataSize += reader.GetInt32(0);
else
logoSize += reader.GetInt32(0);
}
if (reader.NextResult())
{
while (reader.Read())
version += "(" + reader.GetString(0) + ")";
}
dataBaseInfo.DataSize = dataSize * 1024;
dataBaseInfo.LogSize = logoSize * 1024;
dataBaseInfo.Version = version;
}
return dataBaseInfo;
}
}
public void Test2()
{
var sb = new SqlQuery();
sb
.Select
.Field(Order["ID"])
.Field(OrderItem.All)
.Field(OrderItem4["ID"])
.From
.Table(Order,
OrderItem. Join ().Field(Order["ID"]).Equal.Field(OrderItem["OrderID"]),
OrderItem2.WeakJoin(),
OrderItem3.WeakJoin(
OrderItem5.WeakLeftJoin(),
OrderItem4.LeftJoin ().Field(OrderItem4["ID"]).Equal.Field(OrderItem3["ID"]))
)
.Where
.Not.Field(Order["ID"]).Like("1234").Or
.Field(Order["ID"]).Equal.Value("!%")
.GroupBy
.Field(Order["ID"])
.OrderBy
.Field(Order["ID"])
;
Assert.AreEqual(1, sb.From.Tables.Count);
Assert.AreEqual(3, sb.From.Tables[0].Joins.Count);
Assert.AreEqual(2, sb.From.Tables[0].Joins[2].Table.Joins.Count);
sb.FinalizeAndValidate(true, true);
Assert.AreEqual(2, sb.From.Tables[0].Joins.Count);
Assert.AreEqual(1, sb.From.Tables[0].Joins[1].Table.Joins.Count);
}
public override int AddFriendGroups(int userID, List<KeyValuePair<int, string>> friendGroup)
{
using (SqlQuery query = new SqlQuery())
{
StringBuilder sbSql = new StringBuilder();
int i=0;
string deleteExistGroupSql = "DELETE FROM bx_FriendGroups WHERE GroupID IN(";
foreach (KeyValuePair<int, string> group in friendGroup)
{
string groupName = "@GroupName_"+i;
string groupID = "@GroupID_"+i;
sbSql.AppendFormat(" INSERT INTO bx_FriendGroups( GroupID, GroupName, UserID) VALUES({0},{1},@UserID);", groupID, groupName);
deleteExistGroupSql = string.Concat(deleteExistGroupSql,group.Key, ",");
query.CreateParameter<int>(groupID, group.Key, SqlDbType.Int);
query.CreateParameter<string>(groupName, group.Value, SqlDbType.NVarChar, 50);
i++;
}
deleteExistGroupSql = deleteExistGroupSql.TrimEnd(',') + ");";
sbSql.Insert(0, deleteExistGroupSql); //删除已经存在的相同ID的好友分组--
query.CommandText =sbSql.ToString();
query.CreateParameter<int>("@UserID", userID, SqlDbType.Int);
return query.ExecuteNonQuery();
}
}
public override int CreateFriends(int userID, List<KeyValuePair<int, int>> groupAndFriendUserID)
{
using (SqlQuery query = new SqlQuery())
{
StringBuilder sbSql = new StringBuilder();
StringBuilder sbClear = new StringBuilder();
sbClear.Append(" DELETE FROM bx_Friends WHERE UserID = @UserID AND FriendUserID IN(");
int i = 0;
foreach (KeyValuePair<int, int> group in groupAndFriendUserID)
{
string friendUserID = "@FriendUserID_" + i;
string groupID = "@GroupID_" + i;
sbSql.AppendFormat("INSERT INTO bx_Friends( UserID , FriendUserID , GroupID) VALUES(@UserID , {0}, {1});", friendUserID, groupID);
sbClear.Append(group.Value).Append(",");
query.CreateParameter<int>(groupID, group.Key, SqlDbType.Int);
query.CreateParameter<int>(friendUserID, group.Value, SqlDbType.Int);
i++;
}
if (groupAndFriendUserID.Count > 0)
{
sbClear.Remove(sbClear.Length - 1, 1);
sbClear.Append(");");
sbSql.Insert(0, sbClear);
}
query.CommandText = sbSql.ToString();
query.CreateParameter<int>("@UserID", userID, SqlDbType.Int);
return query.ExecuteNonQuery();
}
}
public void EqualsReturnsFalseIfCommandTextMatchesButArgumentsDiffer()
{
var sqlQuery1 = new SqlQuery("SELECT * FROM Table WHERE Id = @p0", 10);
var sqlQuery2 = new SqlQuery("SELECT * FROM Table WHERE Id = @p0", 35);
Assert.False(sqlQuery1.Equals(sqlQuery2));
}
public void EqualsReturnsTrueIfCommandTextMatchesAndArgumentsMatch()
{
var sqlQuery1 = new SqlQuery("SELECT * FROM Table WHERE Id = @p0", 10);
var sqlQuery2 = new SqlQuery("SELECT * FROM Table WHERE Id = @p0", 10);
Assert.True(sqlQuery1.Equals(sqlQuery2));
}
public override PropLogCollection GetPropLogs(int userID, PropLogType type, int pageNumber, int pageSize)
{
using(SqlQuery query = new SqlQuery())
{
query.Pager.TableName = "bx_PropLogs";
query.Pager.SortField = "PropLogID";
query.Pager.PageNumber = pageNumber;
query.Pager.PageSize = pageSize;
query.Pager.SelectCount = true;
query.Pager.Condition = "UserID = @UserID";
query.CreateParameter<int>("@UserID", userID, SqlDbType.Int);
if(type != PropLogType.All)
{
query.CreateParameter<PropLogType>("@Type", type, SqlDbType.TinyInt);
query.Pager.Condition += " AND Type = @Type";
}
using (XSqlDataReader reader = query.ExecuteReader())
{
PropLogCollection result = new PropLogCollection(reader);
if(reader.NextResult() && reader.Read())
result.TotalRecords = reader.Get<int>(0);
return result;
}
}
}
public void ConstructorSetsArgumentsToEmptyArrayIfNoneSpecified()
{
var sqlQuery = new SqlQuery(string.Empty);
Assert.NotNull(sqlQuery.Arguments);
Assert.Empty(sqlQuery.Arguments);
}
public override ChatMessageCollection GetLastChatMessages(int userID, int targetUserID, int lastMessageID, int MessageCount)
{
ChatMessageCollection result;
using (SqlQuery query = new SqlQuery())
{
query.CommandText = "bx_Chat_GetLastMessages";
query.CommandType = CommandType.StoredProcedure;
query.CreateParameter<int>("@UserID", userID, SqlDbType.Int);
query.CreateParameter<int>("@TargetUserID", targetUserID, SqlDbType.Int);
query.CreateParameter<int>("@LastMessageID", lastMessageID, SqlDbType.Int);
using (XSqlDataReader reader = query.ExecuteReader())
{
result = new ChatMessageCollection();
while (reader.Read())
{
result.Insert(0, new ChatMessage(reader));
}
}
return result;
}
}
public override void DeletePropLogs(JobDataClearMode clearMode, DateTime dateTime, int saveRows)
{
using (SqlQuery query = new SqlQuery())
{
switch(clearMode)
{
case JobDataClearMode.ClearByDay:
query.CommandText = "DELETE FROM bx_PropLogs WHERE CreateDate <= @Time;";
query.CreateParameter<DateTime>("@Time", dateTime, SqlDbType.DateTime);
break;
case JobDataClearMode.ClearByRows:
query.CommandText = "DELETE FROM bx_PropLogs WHERE PropLogID < (SELECT MIN(O.PropLogID) FROM (SELECT TOP(@TopCount) PropLogID FROM bx_PropLogs ORDER BY PropLogID DESC) AS O)";
query.CreateTopParameter("@TopCount", saveRows);
break;
case JobDataClearMode.CombinMode:
query.CommandText = "DELETE FROM bx_PropLogs WHERE PropLogID < (SELECT MIN(O.PropLogID) FROM (SELECT TOP(@TopCount) PropLogID FROM bx_PropLogs ORDER BY PropLogID DESC) AS O) AND CreateDate >= @Time";
query.CreateTopParameter("@TopCount", saveRows);
query.CreateParameter<DateTime>("@Time", dateTime, SqlDbType.DateTime);
break;
}
query.ExecuteNonQuery();
}
}
public FromQuery(SqlQuery oQuery, String sName)
: base()
{
_Cmd = oQuery.GetCommand();
Name = sName;
Provider = _Cmd.Provider;
Columns = oQuery.SelectColumns.Select((X) =>
(IColumn)new DatabaseColumn(X.Name, this)
{
DataType = X.DataType,
DefaultSetting = X.DefaultSetting,
ForeignKeyTo = X.ForeignKeyTo,
IsForeignKey = X.IsForeignKey,
FriendlyName = X.FriendlyName
}).ToList();
foreach (Aggregate oAggregate in oQuery.Aggregates)
{
bool hasAlais = !String.IsNullOrEmpty(oAggregate.Alias);
Columns.Add(new DatabaseColumn(hasAlais ? oAggregate.Alias : oAggregate.ColumnName, this)
{
DataType = oAggregate.GetDataType()
});
}
}
public override List<Website> GetWebsites(int pageSize, int pageNumber, out int totalCount)
{
using (SqlQuery query = new SqlQuery())
{
query.Pager.TableName = "Chinaz_Websites";
query.Pager.PageNumber = pageNumber;
query.Pager.PageSize = pageSize;
query.Pager.PrimaryKey = "WebsiteID";
query.Pager.SortField = "WebsiteID";
query.Pager.SelectCount = true;
query.Pager.IsDesc = true;
using (XSqlDataReader reader = query.ExecuteReader())
{
totalCount = 0;
List<Website> results = new List<Website>();
while (reader.Next)
{
results.Add(new Website(reader));
}
if (reader.NextResult())
while (reader.Next)
totalCount = reader.Get<int>(00);
return results;
}
}
}
public void BuildCommandForSqlQueryWithSqlTextWhichUsesSameParameterTwice()
{
var sqlQuery = new SqlQuery(
"SELECT * FROM [Table] WHERE [Table].[Id] = @p0 AND [Table].[Value1] = @p1 OR @p1 IS NULL",
100, "hello");
var command = new OleDbCommand();
var mockDbDriver = new Mock<DbDriver>(SqlCharacters.Empty);
mockDbDriver.CallBase = true;
mockDbDriver.Object.BuildCommand(command, sqlQuery);
Assert.Equal(sqlQuery.CommandText, command.CommandText);
Assert.Equal(CommandType.Text, command.CommandType);
Assert.Equal(2, command.Parameters.Count);
var parameter1 = (IDataParameter)command.Parameters[0];
Assert.Equal(DbType.Int32, parameter1.DbType);
Assert.Equal(ParameterDirection.Input, parameter1.Direction);
Assert.Equal("@p0", parameter1.ParameterName);
Assert.Equal(sqlQuery.Arguments[0].Value, parameter1.Value);
var parameter2 = (IDataParameter)command.Parameters[1];
Assert.Equal(DbType.String, parameter2.DbType);
Assert.Equal(ParameterDirection.Input, parameter2.Direction);
Assert.Equal("@p1", parameter2.ParameterName);
Assert.Equal(sqlQuery.Arguments[1].Value, parameter2.Value);
}
public void BuildCommandForSqlQueryWithSqlText()
{
var command = new OleDbCommand();
var sqlQuery = new SqlQuery(
"SELECT * FROM Table WHERE Table.Id = ? AND Table.Value1 = ? AND Table.Value2 = ?",
100, "hello", null);
var mockDbDriver = new Mock<DbDriver>(SqlCharacters.Empty);
mockDbDriver.CallBase = true;
mockDbDriver.Object.BuildCommand(command, sqlQuery);
Assert.Equal(sqlQuery.CommandText, command.CommandText);
Assert.Equal(CommandType.Text, command.CommandType);
Assert.Equal(3, command.Parameters.Count);
var parameter1 = (IDataParameter)command.Parameters[0];
Assert.Equal(DbType.Int32, parameter1.DbType);
Assert.Equal(ParameterDirection.Input, parameter1.Direction);
Assert.Equal("Parameter0", parameter1.ParameterName);
Assert.Equal(sqlQuery.Arguments[0].Value, parameter1.Value);
var parameter2 = (IDataParameter)command.Parameters[1];
Assert.Equal(DbType.String, parameter2.DbType);
Assert.Equal(ParameterDirection.Input, parameter2.Direction);
Assert.Equal("Parameter1", parameter2.ParameterName);
Assert.Equal(sqlQuery.Arguments[1].Value, parameter2.Value);
var parameter3 = (IDataParameter)command.Parameters[2];
Assert.Equal(default(DbType), parameter3.DbType);
Assert.Equal(ParameterDirection.Input, parameter3.Direction);
Assert.Equal("Parameter2", parameter3.ParameterName);
Assert.Equal(DBNull.Value, parameter3.Value);
}
public override SqlQuery PageQuery(SqlQuery sqlQuery, PagingOptions pagingOptions)
{
if (sqlQuery == null)
{
throw new ArgumentNullException("sqlQuery");
}
var arguments = new SqlArgument[sqlQuery.Arguments.Count + 2];
Array.Copy(sqlQuery.ArgumentsArray, 0, arguments, 0, sqlQuery.Arguments.Count);
arguments[arguments.Length - 2] = new SqlArgument(pagingOptions.Offset, DbType.Int32);
arguments[arguments.Length - 1] = new SqlArgument(pagingOptions.Count, DbType.Int32);
var sqlString = SqlString.Parse(sqlQuery.CommandText, Clauses.OrderBy);
var commandText = string.IsNullOrEmpty(sqlString.OrderBy)
? sqlQuery.CommandText + " ORDER BY GETDATE()"
: sqlQuery.CommandText;
var stringBuilder = new StringBuilder(commandText)
.Replace(Environment.NewLine, string.Empty)
.Append(" OFFSET ")
.Append(this.SqlCharacters.GetParameterName(arguments.Length - 2))
.Append(" ROWS FETCH NEXT ")
.Append(this.SqlCharacters.GetParameterName(arguments.Length - 1))
.Append(" ROWS ONLY");
return new SqlQuery(stringBuilder.ToString(), arguments);
}
public override SqlQuery PageQuery(SqlQuery sqlQuery, PagingOptions pagingOptions)
{
if (sqlQuery == null)
{
throw new ArgumentNullException("sqlQuery");
}
var arguments = new object[sqlQuery.Arguments.Count + 2];
Array.Copy(sqlQuery.ArgumentsArray, 0, arguments, 0, sqlQuery.Arguments.Count);
arguments[arguments.Length - 2] = pagingOptions.Offset + 1;
arguments[arguments.Length - 1] = pagingOptions.Offset + pagingOptions.Count;
var sqlString = SqlString.Parse(sqlQuery.CommandText, Clauses.Select | Clauses.From | Clauses.Where | Clauses.OrderBy);
var qualifiedTableName = sqlString.From;
var position = qualifiedTableName.LastIndexOf('.') + 1;
var tableName = position > 0 ? qualifiedTableName.Substring(position, qualifiedTableName.Length - position) : qualifiedTableName;
var whereClause = !string.IsNullOrEmpty(sqlString.Where) ? " WHERE " + sqlString.Where : string.Empty;
var orderByClause = !string.IsNullOrEmpty(sqlString.OrderBy) ? sqlString.OrderBy : "(SELECT NULL)";
var stringBuilder = new StringBuilder(sqlQuery.CommandText.Length * 2)
.Append("SELECT ")
.Append(sqlString.Select)
.Append(" FROM")
.AppendFormat(CultureInfo.InvariantCulture, " (SELECT {0},ROW_NUMBER() OVER(ORDER BY {1}) AS RowNumber FROM {2}{3}) AS {4}", sqlString.Select, orderByClause, qualifiedTableName, whereClause, tableName)
.AppendFormat(CultureInfo.InvariantCulture, " WHERE (RowNumber >= {0} AND RowNumber <= {1})", this.SqlCharacters.GetParameterName(arguments.Length - 2), this.SqlCharacters.GetParameterName(arguments.Length - 1));
return new SqlQuery(stringBuilder.ToString(), arguments);
}
/// <summary>
/// ctor
/// </summary>
public QueryRunner(SqlQuery query, IEnumerable<WorkerDb> databases)
{
_query = query;
_results = new ConcurrentBag<WorkerResult>();
_databases = new List<WorkerDb>();
_databases.AddRange(databases);
}
public override List<Instruct> LoadClientInstruct(int clientID, int loadCount, out int laveCount)
{
List<Instruct> results = new List<Instruct>();
laveCount = 0;
using (SqlQuery query = new SqlQuery())
{
query.CommandText = @"SELECT TOP (@LoadCount) * FROM bx_Instructs WHERE ClientID = @ClientID;
SELECT Count(*) FROM bx_Instructs WHERE ClientID = @ClientID;";
query.CreateParameter<int>("@ClientID", clientID, SqlDbType.Int);
query.CreateTopParameter("@LoadCount", loadCount);
using (XSqlDataReader reader = query.ExecuteReader())
{
while (reader.Next)
{
results.Add(new Instruct(reader));
}
reader.NextResult();
if (reader.Next)
laveCount = reader.Get<int>(0);
laveCount-=loadCount;
if(laveCount<=0)
laveCount=0;
}
return results;
}
}
public void BuildCommandForSqlQueryWithSqlTextWhichUsesSameParameterTwice()
{
var command = new OleDbCommand();
var sqlQuery = new SqlQuery(
"SELECT * FROM \"Table\" WHERE \"Table\".\"Id\" = @p0 AND \"Table].\"Value1\" = @p1 OR @p1 IS NULL",
100, "hello");
var dbDriver = new PostgreSqlDbDriver();
dbDriver.BuildCommand(command, sqlQuery);
Assert.Equal(sqlQuery.CommandText, command.CommandText);
Assert.Equal(CommandType.Text, command.CommandType);
Assert.Equal(2, command.Parameters.Count);
var parameter1 = (IDataParameter)command.Parameters[0];
Assert.Equal(DbType.Int32, parameter1.DbType);
Assert.Equal(ParameterDirection.Input, parameter1.Direction);
Assert.Equal("@p0", parameter1.ParameterName);
Assert.Equal(sqlQuery.Arguments[0].Value, parameter1.Value);
var parameter2 = (IDataParameter)command.Parameters[1];
Assert.Equal(DbType.String, parameter2.DbType);
Assert.Equal(ParameterDirection.Input, parameter2.Direction);
Assert.Equal("@p1", parameter2.ParameterName);
Assert.Equal(sqlQuery.Arguments[1].Value, parameter2.Value);
}
public void BuildCommandForSqlQueryWithStoredProcedureWithParameters()
{
var command = new OleDbCommand();
var sqlQuery = new SqlQuery(
"SELECT GetTableContents (@identifier, @Cust_Name)",
100, "hello");
var dbDriver = new PostgreSqlDbDriver();
dbDriver.BuildCommand(command, sqlQuery);
// The command text should only contain the stored procedure name.
Assert.Equal("GetTableContents", command.CommandText);
Assert.Equal(CommandType.StoredProcedure, command.CommandType);
Assert.Equal(2, command.Parameters.Count);
var parameter1 = (IDataParameter)command.Parameters[0];
Assert.Equal(DbType.Int32, parameter1.DbType);
Assert.Equal(ParameterDirection.Input, parameter1.Direction);
Assert.Equal("@identifier", parameter1.ParameterName);
Assert.Equal(sqlQuery.Arguments[0].Value, parameter1.Value);
var parameter2 = (IDataParameter)command.Parameters[1];
Assert.Equal(DbType.String, parameter2.DbType);
Assert.Equal(ParameterDirection.Input, parameter2.Direction);
Assert.Equal("@Cust_Name", parameter2.ParameterName);
Assert.Equal(sqlQuery.Arguments[1].Value, parameter2.Value);
}
public override CommentCollection GetLastestCommentsForSomeone(int targetUserID, CommentType type, int top)
{
using (SqlQuery query = new SqlQuery())
{
string getTargetNameSql = null;
switch (type)
{
case CommentType.Blog:
getTargetNameSql = "(SELECT [Subject] FROM [bx_BlogArticles] WHERE [ArticleID]=TargetID) AS [TargetName] ";
break;
case CommentType.Photo:
getTargetNameSql = "(SELECT [Name] FROM [bx_Photos] WHERE [PhotoID]=TargetID) AS [TargetName] ";
break;
default:
getTargetNameSql = string.Empty;
break;
}
query.CommandText = "SELECT TOP (@TopCount) *, " + getTargetNameSql + " FROM bx_Comments WHERE [TargetUserID]=@TargetUserID AND [Type]=@Type ORDER BY [CommentID] DESC";
query.CommandType = CommandType.Text;
query.CreateParameter<int>("@TargetUserID", targetUserID, SqlDbType.Int);
query.CreateParameter<CommentType>("@Type", type, SqlDbType.TinyInt);
query.CreateTopParameter("@TopCount", top);
using (XSqlDataReader reader = query.ExecuteReader())
{
return new CommentCollection(reader);
}
}
}
public override CommentCollection GetCommentsBySearch(int operatorID, Guid[] excludeRoleIDs, AdminCommentFilter filter, int pageNumber)
{
using (SqlQuery query = new SqlQuery())
{
string conditions = BuildConditionsByFilter(query, filter, false, operatorID, excludeRoleIDs);
query.Pager.TableName = "[bx_Comments]";
query.Pager.SortField = filter.Order.ToString();
query.Pager.IsDesc = filter.IsDesc;
query.Pager.PageNumber = pageNumber;
query.Pager.PageSize = filter.PageSize;
query.Pager.SelectCount = true;
query.Pager.Condition = conditions.ToString();
using (XSqlDataReader reader = query.ExecuteReader())
{
CommentCollection comments = new CommentCollection(reader);
if (reader.NextResult())
{
if (reader.Read())
{
comments.TotalRecords = reader.Get<int>(0);
}
}
return comments;
}
}
}
public void SqlIgnoreAttributeTest()
{
var da = new SqlQuery();
var p = (Person)da.SelectByKey(typeof(Person), 1);
Assert.IsNull(p.Gender);
}
public WhenCallingBindOrderBy()
{
this.sqlQuery = OrderByBinder.BindOrderBy(
new OrderByQueryOption("$orderby=Status desc,Name"),
ObjectInfo.For(typeof(Customer)),
SqlBuilder.Select("*").From(typeof(Customer))).ToSqlQuery();
}
public override CreateClubResult CreateClub(int operatorID, int categoryID, string clubName, bool isApproved, string operatorIP, out int newClubID)
{
using (SqlQuery db = new SqlQuery())
{
db.CommandText = "bx_Club_CreateClub";
db.CommandType = System.Data.CommandType.StoredProcedure;
db.CreateParameter<int>("@UserID", operatorID, SqlDbType.Int);
db.CreateParameter<int>("@CategoryID", categoryID, SqlDbType.Int);
db.CreateParameter<bool>("@IsApproved", isApproved, SqlDbType.Bit);
db.CreateParameter<string>("@CreateIP", operatorIP, SqlDbType.VarChar, 50);
db.CreateParameter<string>("@Name", clubName, SqlDbType.NVarChar, 50);
SqlParameter newID = db.CreateParameter<int>("@NewClubID", SqlDbType.Int, ParameterDirection.Output);
SqlParameter result = db.CreateParameter<int>("@Result", SqlDbType.Int, ParameterDirection.ReturnValue);
db.ExecuteNonQuery();
switch ((int)result.Value)
{
case 0:
newClubID = 0;
return CreateClubResult.HasSameNameClub;
default:
newClubID = (int)newID.Value;
return CreateClubResult.Succeed;
}
}
}
public void Test()
{
var da = new SqlQuery();
var p = (Person)da.SelectByKey(typeof(Person), 1);
Assert.AreEqual("Pupkin", p.Name.LastName);
}
protected override IParseContext ParseMethodCall(ExpressionParser parser, IParseContext parent, MethodCallExpression methodCall, SqlQuery sqlQuery)
{
var sequence = parser.ParseSequence(parent, methodCall.Arguments[0], sqlQuery);
var defaultValue = methodCall.Arguments.Count == 1 ? null : methodCall.Arguments[1].Unwrap();
return new DefaultIfEmptyContext(sequence, defaultValue);
}
public User[] findUsersByRegularExpression()
{
// Match all entries of type User that have a name that starts with J or
// R:
SqlQuery<User> query = new SqlQuery<User>( "Name rlike '(J|R).*'");
return proxy.ReadMultiple<User>(query);
}
public User[] findUsersByNameAndProjection()
{
SqlQuery<User> query = new SqlQuery<User>( "Name = ?"){Projections = new []{"Name"}};
query.SetParameter (1, "John Dow");
return proxy.ReadMultiple<User>(query);
}
public override MaxSerial CreateSerial(int ownerUserId, DateTime expriseDate, SerialType type, string data,out bool success)
{
using (SqlQuery query = new SqlQuery())
{
Guid serial = Guid.NewGuid();
DateTime createDate = DateTimeUtil.Now;
query.CommandText = "bx_CreateSerial";
query.CommandType = System.Data.CommandType.StoredProcedure;
query.CreateParameter<int>("@UserID", ownerUserId, SqlDbType.Int);
query.CreateParameter<DateTime>("@ExpiresDate", expriseDate, SqlDbType.DateTime);
query.CreateParameter<byte>("@Type", (byte)type, SqlDbType.TinyInt);
query.CreateParameter<string>("@Data", data, SqlDbType.NVarChar, 1000);
SqlParameter outputParam = query.CreateParameter<bool>("@Success", SqlDbType.Bit, ParameterDirection.Output);
MaxSerial newSerial = null;
using (XSqlDataReader reader = query.ExecuteReader())
{
while (reader.Next)
{
newSerial = new MaxSerial(reader);
}
}
success = (bool)outputParam.Value;
return newSerial;
}
}
public static Exp In(string column, SqlQuery subQuery)
{
return(new InExp(column, subQuery));
}
public static Exp EqColumns(string column1, SqlQuery query)
{
return(new EqColumnsExp(column1, query));
}
protected override IBuildContext BuildMethodCall(ExpressionBuilder builder, MethodCallExpression methodCall, BuildInfo buildInfo)
{
var sequence = builder.BuildSequence(new BuildInfo(buildInfo, methodCall.Arguments[0]));
var collectionSelector = (LambdaExpression)methodCall.Arguments[1].Unwrap();
var resultSelector = (LambdaExpression)methodCall.Arguments[2].Unwrap();
if (!sequence.SqlQuery.GroupBy.IsEmpty)
{
sequence = new SubQueryContext(sequence);
}
var context = new SelectManyContext(buildInfo.Parent, collectionSelector, sequence);
var expr = collectionSelector.Body.Unwrap();
var collectionInfo = new BuildInfo(context, expr, new SqlQuery());
var collection = builder.BuildSequence(collectionInfo);
var leftJoin = collection is DefaultIfEmptyBuilder.DefaultIfEmptyContext;
var sql = collection.SqlQuery;
var sequenceTable = sequence.SqlQuery.From.Tables[0].Source;
var newQuery = null != new QueryVisitor().Find(sql, e => e == collectionInfo.SqlQuery);
var crossApply = null != new QueryVisitor().Find(sql, e =>
e == sequenceTable ||
e.ElementType == QueryElementType.SqlField && sequenceTable == ((SqlField)e).Table ||
e.ElementType == QueryElementType.Column && sequenceTable == ((SqlQuery.Column)e).Parent);
if (collection is JoinBuilder.GroupJoinSubQueryContext)
{
var groupJoin = ((JoinBuilder.GroupJoinSubQueryContext)collection).GroupJoin;
groupJoin.SqlQuery.From.Tables[0].Joins[0].JoinType = SqlQuery.JoinType.Inner;
groupJoin.SqlQuery.From.Tables[0].Joins[0].IsWeak = false;
}
if (!newQuery)
{
context.Collection = new SubQueryContext(collection, sequence.SqlQuery, false);
return(new SelectContext(buildInfo.Parent, resultSelector, sequence, context));
}
if (!crossApply)
{
if (!leftJoin)
{
context.Collection = new SubQueryContext(collection, sequence.SqlQuery, true);
return(new SelectContext(buildInfo.Parent, resultSelector, sequence, context));
}
else
{
var join = SqlQuery.OuterApply(sql);
sequence.SqlQuery.From.Tables[0].Joins.Add(join.JoinedTable);
context.Collection = new SubQueryContext(collection, sequence.SqlQuery, false);
return(new SelectContext(buildInfo.Parent, resultSelector, sequence, context));
}
}
if (collection is TableBuilder.TableContext)
{
var table = (TableBuilder.TableContext)collection;
var join = table.SqlTable.TableArguments != null && table.SqlTable.TableArguments.Length > 0 ?
leftJoin ? SqlQuery.OuterApply(sql) : SqlQuery.CrossApply(sql) :
leftJoin?SqlQuery.LeftJoin(sql) : SqlQuery.InnerJoin(sql);
join.JoinedTable.Condition.Conditions.AddRange(sql.Where.SearchCondition.Conditions);
sql.Where.SearchCondition.Conditions.Clear();
var collectionParent = collection.Parent as TableBuilder.TableContext;
// Association.
//
if (collectionParent != null && collectionInfo.IsAssociationBuilt)
{
var ts = (SqlQuery.TableSource) new QueryVisitor().Find(sequence.SqlQuery.From, e =>
{
if (e.ElementType == QueryElementType.TableSource)
{
var t = (SqlQuery.TableSource)e;
return(t.Source == collectionParent.SqlTable);
}
return(false);
});
ts.Joins.Add(join.JoinedTable);
}
else
{
sequence.SqlQuery.From.Tables[0].Joins.Add(join.JoinedTable);
}
context.Collection = new SubQueryContext(collection, sequence.SqlQuery, false);
return(new SelectContext(buildInfo.Parent, resultSelector, sequence, context));
}
else
{
var join = leftJoin ? SqlQuery.OuterApply(sql) : SqlQuery.CrossApply(sql);
sequence.SqlQuery.From.Tables[0].Joins.Add(join.JoinedTable);
context.Collection = new SubQueryContext(collection, sequence.SqlQuery, false);
return(new SelectContext(buildInfo.Parent, resultSelector, sequence, context));
}
}
public static Exp Exists(SqlQuery query)
{
return(new ExistsExp(query));
}
