/// <summary>
/// Asynchronously executes the query.
/// </summary>
/// <typeparam name="TResult">The type of the elements in the returned collection.</typeparam>
/// <param name="dataContext">The data context.</param>
/// <param name="query">The query.</param>
/// <param name="cancellationToken">The cancellation token.</param>
/// <returns>A collection of objects returned by the query.</returns>
public static async Task <IEnumerable <TResult> > ExecuteQueryAsync <TResult>(
this DataContext dataContext,
IQueryable <TResult> query,
CancellationToken cancellationToken = default)
{
if (dataContext == null)
{
throw new ArgumentNullException(nameof(dataContext));
}
if (query == null)
{
throw new ArgumentNullException(nameof(query));
}
var command = dataContext.GetCommand(query);
if (command is SqlCommand sqlCommand)
{
// Microsoft SQL Server data provider natively supports async operations.
var connection = sqlCommand.Connection;
if (connection.State == ConnectionState.Closed)
{
await connection.OpenAsync(cancellationToken).ConfigureAwait(false);
}
var reader = await sqlCommand.ExecuteReaderAsync(cancellationToken).ConfigureAwait(false);
// Execute Translate via TaskBridge because it uses synchronous operations
// like Read() and NextResult() on a specified reader instance.
// This allows to avoid possible thread pool pollution on a large number of concurrent operations.
// In essence, this is an async polyfill but there is no better way without modifying the stock LINQ to SQL implementation.
return(await
TaskBridge.ExecuteAsync(
() => dataContext.Translate <TResult>(reader),
cancellationToken)
.ConfigureAwait(false));
}
else
{
// Use TaskBridge-based async polyfill for other providers.
return(await
TaskBridge.ExecuteAsync(
() =>
{
var connection = command.Connection;
if (connection.State == ConnectionState.Closed)
{
connection.Open();
}
var reader = command.ExecuteReader();
return dataContext.Translate <TResult>(reader);
},
cancellationToken)
.ConfigureAwait(false));
}
}
/// <summary>
/// Execute sql query contains @ParamName and result is List of type T
/// </summary>
/// <param name="query"></param>
/// <param name="sqlParameters"></param>
/// <returns></returns>
public List <T> ExecuteSqlQueryToList <T>(string query, params SqlParameter[] sqlParameters)
{
if (IsTransactionError())
{
return(null);
}
try
{
var dbCmd = DataContext.Connection.CreateCommand();
dbCmd.CommandType = CommandType.Text;
dbCmd.CommandText = query;
dbCmd.Parameters.AddRange(sqlParameters);
OpenConnection();
var dr = dbCmd.ExecuteReader();
return(_dataContext.Translate <T>(dr).ToList());
}
catch (Exception ex)
{
RollbackTransaction();
Exception = ex;
IsSuccess = false;
return(null);
}
finally
{
if (_dataContext.Transaction == null)
{
CloseConnection();
}
}
}
/// <summary>
/// Return IEnumerable of SQL Server query results
/// </summary>
/// <returns></returns>
private static IEnumerable<ServerEvent> GetEvents()
{
//define connection string
string connString = "Data Source=.;Initial Catalog=DemoDb;Integrated Security=SSPI;";
//create enumerable to hold results
IEnumerable<ServerEvent> result;
//define dataconext object which is used later for translating results to objects
DataContext dc = new DataContext(connString);
//initiate and open connection
conn = (SqlConnection)dc.Connection;
conn.Open();
//return all events stored in the SQL Server table
SqlCommand command = new SqlCommand("select ID, ServerName, Level, Timestamp From ServerEvent", conn);
//get the database results and set the connection to close after results are read
SqlDataReader dataReader = command.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
//use "translate" to flip the reader stream to an Enumerable of my custom object type
result = dc.Translate<ServerEvent>(dataReader);
return result;
}
/// <summary>
/// Return IEnumerable of SQL Server query results
/// </summary>
/// <returns></returns>
private static IEnumerable <ServerEvent> GetEvents()
{
//define connection string
string connString = "Data Source=.;Initial Catalog=DemoDb;Integrated Security=SSPI;";
//create enumerable to hold results
IEnumerable <ServerEvent> result;
//define dataconext object which is used later for translating results to objects
DataContext dc = new DataContext(connString);
//initiate and open connection
conn = (SqlConnection)dc.Connection;
conn.Open();
//return all events stored in the SQL Server table
SqlCommand command = new SqlCommand("select ID, ServerName, Level, Timestamp From ServerEvent", conn);
//get the database results and set the connection to close after results are read
SqlDataReader dataReader = command.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
//use "translate" to flip the reader stream to an Enumerable of my custom object type
result = dc.Translate <ServerEvent>(dataReader);
return(result);
}
public static IEnumerable <T> EndQuery <T>(this DataContext ctx,
IAsyncResult result)
{
AsyncResult localResult = (AsyncResult)result;
if (localResult.Exception != null)
{
throw localResult.Exception;
}
return(ctx.Translate <T>(localResult.Reader));
}
public static List <T> ExecuteQuery <T>(this DataContext dataContext, IQueryable query, bool withNoLock)
{
DbCommand command = dataContext.GetCommand(query, withNoLock);
dataContext.OpenConnection();
using (DbDataReader reader = command.ExecuteReader())
{
return(dataContext.Translate <T>(reader).ToList());
}
}
protected static async Task <IEnumerable <T> > ExecuteAsync <T>(IQueryable <T> query,
DataContext ctx,
CancellationToken token = default(CancellationToken))
{
var cmd = (SqlCommand)ctx.GetCommand(query);
if (cmd.Connection.State == ConnectionState.Closed)
{
await cmd.Connection.OpenAsync(token);
}
var reader = await cmd.ExecuteReaderAsync(token);
return(ctx.Translate <T>(reader));
}
private static async Task <List <T> > internalToListAsync <T>(IQueryable <T> query, DataContext context)
{
using (var sqlCommand = context.GetCommand(query))
{
sqlCommand.Connection = context.Connection;
if (sqlCommand.Connection.State == ConnectionState.Closed)
{
await sqlCommand.Connection.OpenAsync();
}
var result = await sqlCommand.ExecuteReaderAsync();
return(context.Translate <T>(result).ToList());
}
}
public static IEnumerable ExecuteQuery(this DataContext dataContext, Type elementType, IQueryable query, int?commandTimeout)
{
using (var command = CreateSelectCommand(dataContext, query))
{
PrepareCommand(dataContext, command, commandTimeout);
using (var reader = command.ExecuteReader())
{
var result = dataContext.Translate(elementType, reader);
foreach (var item in result)
{
yield return(item);
}
}
}
}
private void UseDbDataReader()
{
var con = new SqlConnection(ConnectionString);
var ctx = new DataContext(con);
var cmd = new SqlCommand("SELECT * FROM dbo.test WHERE Name LIKE '张%'", con);
con.Open();
var reader = cmd.ExecuteReader();
//Translate 将现有 System.Data.Common.DbDataReader 转换为对象
GridView1.DataSource = ctx.Translate <test>(reader);
GridView1.DataBind();
con.Close();
}
public static List <T> EndExecuteQuery <T>(this DataContext dataContext, IAsyncResult ar)
{
AsyncResult <DbDataReader> asyncResult = (AsyncResult <DbDataReader>)ar;
if (!asyncResult.IsCompleted)
{
asyncResult.AsyncWaitHandle.WaitOne();
}
if (asyncResult.Exception != null)
{
throw asyncResult.Exception;
}
using (DbDataReader reader = asyncResult.Result)
{
return(dataContext.Translate <T>(reader).ToList());
}
}
/// <summary>
/// Batches together multiple <see cref="IQueryable"/> queries into a single <see cref="DbCommand"/> and returns all data in
/// a single round trip to the database.
/// </summary>
/// <param name="context">The <see cref="DataContext"/> to execute the batch select against.</param>
/// <param name="commands">The list of commands to execute.</param>
/// <returns>Returns an <see cref="IMultipleResults"/> object containing all results.</returns>
/// <exception cref="ArgumentNullException">Thrown when context or queries are null.</exception>
/// <exception cref="InvalidOperationException">Thrown when context.Connection is invalid.</exception>
public static IMultipleResults ExecuteQuery(this DataContext context, IEnumerable <DbCommand> commands)
{
using (DbCommand batchCommand = CombineCommands(context, commands))
{
LogCommand(context, batchCommand);
batchCommand.Connection = context.Connection;
if (batchCommand.Connection == null)
{
throw new InvalidOperationException("The DataContext must contain a valid SqlConnection.");
}
if (context.Transaction != null)
{
batchCommand.Transaction = context.Transaction;
}
DbDataReader dr;
if (batchCommand.Connection.State == ConnectionState.Closed)
{
batchCommand.Connection.Open();
dr = batchCommand.ExecuteReader(CommandBehavior.CloseConnection);
}
else
{
dr = batchCommand.ExecuteReader();
}
if (dr == null)
{
return(null);
}
return(context.Translate(dr));
}
}
private static void CreateSqlEnumerable()
{
string connString = "Data Source=.;Initial Catalog=DemoDb;Integrated Security=SSPI;";
using (DataContext dc = new DataContext(connString))
{
SqlConnection conn = (SqlConnection)dc.Connection;
conn.Open();
SqlCommand command = new SqlCommand("select ID, ServerName, Level, Timestamp From ServerEvent", conn);
SqlDataReader dataReader = command.ExecuteReader();
var result = dc.Translate <ServerEvent>(dataReader);
foreach (ServerEvent evt in result)
{
Console.WriteLine("***************");
Console.WriteLine(evt.Id);
Console.WriteLine(evt.ServerName);
Console.WriteLine(evt.Level);
}
}
Console.ReadLine();
}
private static IEnumerable <SourceEvent> GetEvents(string connString, string sqlCommand)
{
Console.WriteLine("GetEvents() called");
//SQL Connection and Query Setup
//string connString = connString;
//string sqlCommand = SQL_COMMANDS.ACCOUNT_BASE;
//string connString = SQL_COMMANDS.CAPTURE_CHANGES_CONN;
//string sqlCommand = SQL_COMMANDS.SALES_HISTORY;
//create enumerable to hold results
IEnumerable <SourceEvent> result;
//define dataconext object which is used later for translating results to objects
DataContext dc = new DataContext(connString);
//initiate and open connection
SqlConnection conn = (SqlConnection)dc.Connection;
conn.Open();
SqlCommand command = new SqlCommand(sqlCommand, conn);
//get the database results and set the connection to close after results are read
SqlDataReader dataReader = command.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
//use "translate" to flip the reader stream to an Enumerable of my custom object type
result = dc.Translate <SourceEvent>(dataReader);
//Console.WriteLine("numResults: " + result.Count());
return(result);
}
public IEnumerable <T> GetEvents(DateTime mostRecentTransactionTime)
{
Console.WriteLine("GetEvents() called");
//define connection string
string connString = this.CONNECTION_STRING;
//create enumerable to hold results
IEnumerable <T> result;
//define dataconext object which is used later for translating results to objects
DataContext dc = new DataContext(connString);
//initiate and open connection
SqlConnection conn = (SqlConnection)dc.Connection;
conn.Open();
//return all events stored in the SQL Server table
string mostRecentTransactionString = mostRecentTransactionTime.ToString("yyyy-MM-dd HH:mm:ss.fff");
string sqlCommand = this.SQL_COMMAND + "'" + mostRecentTransactionString + "'";
SqlCommand command = new SqlCommand(sqlCommand, conn);
//get the database results and set the connection to close after results are read
SqlDataReader dataReader = command.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
//use "translate" to flip the reader stream to an Enumerable of my custom object type
result = dc.Translate <T>(dataReader);
return(result);
}
public void Translate_ReaderNull()
{
context.Translate(typeof(Person), null);
}
private static void CreateSqlEnumerable()
{
string connString = "Data Source=.;Initial Catalog=DemoDb;Integrated Security=SSPI;";
using (DataContext dc = new DataContext(connString))
{
SqlConnection conn = (SqlConnection)dc.Connection;
conn.Open();
SqlCommand command = new SqlCommand("select ID, ServerName, Level, Timestamp From ServerEvent", conn);
SqlDataReader dataReader = command.ExecuteReader();
var result = dc.Translate<ServerEvent>(dataReader);
foreach (ServerEvent evt in result)
{
Console.WriteLine("***************");
Console.WriteLine(evt.Id);
Console.WriteLine(evt.ServerName);
Console.WriteLine(evt.Level);
}
}
Console.ReadLine();
}
