本文整理汇总了C#中SqlQuery类的典型用法代码示例。如果您正苦于以下问题：C# SqlQuery类的具体用法？C# SqlQuery怎么用？C# SqlQuery使用的例子？那么恭喜您, 这里精选的类代码示例或许可以为您提供帮助。
SqlQuery类属于命名空间，在下文中一共展示了SqlQuery类的15个代码示例，这些例子默认根据受欢迎程度排序。您可以为喜欢或者感觉有用的代码点赞，您的评价将有助于系统推荐出更棒的C#代码示例。
示例1: BuildCommandForSqlQueryWithSqlText
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);
}示例2: BuildCommandForSqlQueryWithStoredProcedureWithParameters
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);
}示例3: PageQuery
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);
}示例4: WhenCallingBindOrderBy
public WhenCallingBindOrderBy()
{
this.sqlQuery = OrderByBinder.BindOrderBy(
new OrderByQueryOption("$orderby=Status desc,Name"),
ObjectInfo.For(typeof(Customer)),
SqlBuilder.Select("*").From(typeof(Customer))).ToSqlQuery();
}示例5: GetCommentsBySearch
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;
}
}
}示例6: ParseMethodCall
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);
}示例7: Test
public void Test()
{
var da = new SqlQuery();
var p = (Person)da.SelectByKey(typeof(Person), 1);
Assert.AreEqual("Pupkin", p.Name.LastName);
}示例8: GetPropLogs
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;
}
}
}示例9: ConstructorSetsArgumentsToEmptyArrayIfNoneSpecified
public void ConstructorSetsArgumentsToEmptyArrayIfNoneSpecified()
{
var sqlQuery = new SqlQuery(string.Empty);
Assert.NotNull(sqlQuery.Arguments);
Assert.Empty(sqlQuery.Arguments);
}示例10: GetLastChatMessages
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;
}
}示例11: DeletePropLogs
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();
}
}示例12: GetWebsites
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;
}
}
}示例13: FromQuery
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()
});
}
}示例14: BuildCommandForSqlQueryWithSqlTextWhichUsesSameParameterTwice
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);
}示例15: findUsersByNameAndProjection
public User[] findUsersByNameAndProjection()
{
SqlQuery<User> query = new SqlQuery<User>( "Name = ?"){Projections = new []{"Name"}};
query.SetParameter (1, "John Dow");
return proxy.ReadMultiple<User>(query);
}