本文整理汇总了C#中System.Data.Common.DbConnection.Query方法的典型用法代码示例。如果您正苦于以下问题：C# DbConnection.Query方法的具体用法？C# DbConnection.Query怎么用？C# DbConnection.Query使用的例子？那么恭喜您, 这里精选的方法代码示例或许可以为您提供帮助。您也可以进一步了解该方法所在类System.Data.Common.DbConnection的用法示例。
在下文中一共展示了DbConnection.Query方法的15个代码示例，这些例子默认根据受欢迎程度排序。您可以为喜欢或者感觉有用的代码点赞，您的评价将有助于系统推荐出更棒的C#代码示例。
示例1: GetAllActive
public IEnumerable<Veto> GetAllActive()
{
using (_connection = Utilities.GetProfiledOpenConnection())
{
return _connection.Query<Veto>(@"SELECT * FROM Vetoes WHERE Used = 0").ToList();
}
}示例2: GetAll
public IEnumerable<Veto> GetAll(int userId)
{
using (_connection = Utilities.GetProfiledOpenConnection())
{
return _connection.Query<Veto>(@"SELECT * FROM Vetoes WHERE Used = 0 AND UserId = @userId", new { userId }).ToList();
}
}示例3: GenerateInitialDatabase
public void GenerateInitialDatabase(string connectionstring)
{
//if database has versioninfo table ABORT
using (_connection = Utilities.GetOpenConnection(connectionstring))
{
if (_connection.Query<string>("SELECT table_name FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbo' AND TABLE_NAME = 'dbversions'").Any())
throw new Exception("What are you doing? The database already exists!");
}
//Regular expression that finds multiline block comments.
Regex findComments = new Regex(@"\/\*.*?\*\/", RegexOptions.Singleline | RegexOptions.Compiled);
using (_connection = Utilities.GetOpenConnection(connectionstring))
{
var statements = Lunch.Data.Install.SQLresources._0001_InitialLoad;
if (string.IsNullOrEmpty(statements))
{
throw new Exception("The sql statement to execute is empty.");
}
string sqlBatch = string.Empty;
foreach (string line in statements.Split(new string[2] {"\n", "\r"}, StringSplitOptions.RemoveEmptyEntries))
{
if (line.ToUpperInvariant().Trim() == "GO")
{
_connection.Execute(sqlBatch);
sqlBatch = string.Empty;
}
else
{
sqlBatch += line + "\n";
}
}
}
}示例4: GetLastVersion
public DBVersion GetLastVersion()
{
using (_connection = Utilities.GetProfiledOpenConnection())
{
const string query = "SELECT TOP(1) * FROM DBVersions ORDER BY ID DESC";
return _connection.Query<DBVersion>(query).FirstOrDefault();
}
}示例5: GetItemsByLast30Days
public IList<Vote> GetItemsByLast30Days(DateTime date)
{
using (_connection = Utilities.GetProfiledOpenConnection())
{
return _connection.Query<Vote>("SELECT * FROM Votes " +
"WHERE VoteDate > @date",
new {date = date.AddDays(-30)}).ToList();
}
}示例6: GetItems
public IList<SearchHistoryLog> GetItems(IList<long> searchIds)
{
IList<SearchHistoryLog> results;
using (_connection = Utilities.Database.GetProfiledOpenConnection())
{
const string query = "SELECT * FROM SearchHistoryLogs WHERE SearchId IN @SearchIds";
results = _connection.Query<SearchHistoryLog>(query, new {SearchIds = searchIds.ToArray()}).ToList();
}
return results;
}示例7: GetItemsByMonthAndYear
public IList<Vote> GetItemsByMonthAndYear(int month, int year)
{
using (_connection = Utilities.GetProfiledOpenConnection())
{
return _connection.Query<Vote>("SELECT * FROM Votes " +
"WHERE DATEPART(mm, VoteDate) = @Month " +
"AND DATEPART(yyyy, VoteDate) = @Year",
new {Month = month, Year = year}).ToList();
}
}示例8: Get
public Veto Get(DateTime usedAt)
{
using (_connection = Utilities.GetProfiledOpenConnection())
{
return _connection.Query<Veto>(@"SELECT * FROM Vetoes V WHERE
DATEPART(mm, V.UsedAt) = DATEPART(mm, @usedAt)
AND DATEPART(dd, V.UsedAt) = DATEPART(dd, @usedAt)
AND DATEPART(yyyy, V.UsedAt) = DATEPART(yyyy, @usedAt)", new { usedAt = usedAt }).FirstOrDefault();
}
}示例9: GetItem
public Vote GetItem(int userID, DateTime date)
{
using (_connection = Utilities.GetProfiledOpenConnection())
{
return _connection.Query<Vote>("SELECT * FROM Votes " +
"WHERE UserID = @UserID " +
"AND DATEPART(mm, VoteDate) = DATEPART(mm, @VoteDate) " +
"AND DATEPART(dd, VoteDate) = DATEPART(dd, @VoteDate) " +
"AND DATEPART(yyyy, VoteDate) = DATEPART(yyyy, @VoteDate)",
new Vote {UserId = userID, VoteDate = date}).FirstOrDefault();
}
}示例10: GetAllByVote
public IEnumerable<Restaurant> GetAllByVote(DateTime dateTime)
{
using (_connection = Utilities.GetProfiledOpenConnection())
{
var result = _connection.Query<Restaurant>(
@"SELECT R.*, (SELECT COUNT(*) AS Votes FROM Votes V WHERE V.RestaurantId = R.Id AND V.VoteDate > @start AND V.VoteDate < @end) Votes
FROM Restaurants R
ORDER BY Votes DESC
", new {start = dateTime.Date.AddDays(-1),
end = dateTime.Date.AddDays(1)});
return result;
}
}示例11: GetAllDetailed
public IEnumerable<RestaurantDetails> GetAllDetailed(int? categoryId)
{
using (_connection = Utilities.GetProfiledOpenConnection())
{
var result =_connection.Query<RestaurantDetails>(
@"Select Restaurants.Id, Restaurants.RestaurantName, Restaurants.PreferredDayOfWeek, RestaurantTypes.Id as RestaurantTypeId, RestaurantTypes.TypeName as TypeName
from Restaurants
LEFT OUTER JOIN RestaurantTypes
ON Restaurants.RestaurantTypeId = RestaurantTypes.Id");
if (categoryId != null)
result = result.Where(f => f.RestaurantTypeId == categoryId);
return result;
}
}示例12: GetAll
public IEnumerable<RestaurantOption> GetAll()
{
using (_connection = Utilities.GetProfiledOpenConnection())
{
IEnumerable<RestaurantOption> results =
_connection.Query<RestaurantOption, Restaurant, RestaurantOption>(@"SELECT * FROM RestaurantOptions RO
INNER JOIN Restaurants R ON R.Id = RO.RestaurantId ",
(ro, r) =>
{
ro.Restaurant = r;
return ro;
});
return results;
}
}示例13: GetCounterIdByKey
public static CySequenceCounterEntity GetCounterIdByKey(string sequenceKey, DbConnection conn)
{
const string sql = @"SELECT CounterId,UpdateTime
FROM [dbo].[CySequenceCounter](nolock)
WHERE SequenceKey = @SequenceKey";
try
{
var model = conn.Query<CySequenceCounterEntity>(sql, new { SequenceKey = sequenceKey }).FirstOrDefault<CySequenceCounterEntity>();
return model;
}
catch (Exception ex)
{
throw ;
}
}示例14: GetAll
public IEnumerable<RestaurantRating> GetAll()
{
using (_connection = Utilities.GetProfiledOpenConnection())
{
var results =
_connection.Query<RestaurantRating>(
@"SELECT RR.Id, U.Id AS UserId, R.Id AS RestaurantId,
COALESCE((SELECT Rating FROM RestaurantRatings RR WHERE RR.UserId = U.Id and RR.RestaurantId = R.Id), 5) AS Rating
FROM Restaurants R
CROSS JOIN Users U
FULL OUTER JOIN RestaurantRatings RR ON RR.UserId = U.Id
ORDER BY R.Id, U.Id");
return results;
}
}示例15: GetAllByDate
public IEnumerable<RestaurantOption> GetAllByDate(DateTime dateTime)
{
using (_connection = Utilities.GetProfiledOpenConnection())
{
IEnumerable<RestaurantOption> results =
_connection.Query<RestaurantOption, Restaurant, RestaurantOption>(@"SELECT RO.*, (SELECT COUNT (*) FROM Votes WHERE Votes.RestaurantId = R.Id AND DATEPART(dd, VoteDate) = DATEPART(dd, @date) AND DATEPART(mm, VoteDate) = DATEPART(mm, @date) AND DATEPART(yyyy, VoteDate) = DATEPART(yyyy, @date)) AS Votes, R.* FROM RestaurantOptions RO
INNER JOIN Restaurants R ON R.Id = RO.RestaurantId
WHERE SelectedDate = @date",
(ro, r) =>
{
ro.Restaurant = r;
return ro;
}, new {date = dateTime.Date});
return results;
}
}