public static async Task<long> NrRows(string pathToExcel,string SheetName= "[Sheet1$]")
{
using (var m = new OleDbConnection())
{
m.ConnectionString = ExcelHelpers.BuildExcelConnectionString(pathToExcel, false);
await m.OpenAsync();
var query = @"Select count(*) From " + SheetName;
using (var cmd = new OleDbCommand(query, m))
{
var dr = await cmd.ExecuteScalarAsync();
return long.Parse(dr.ToString());
}
}
}
/// <summary>
/// Tests the connection asynchronously.
/// </summary>
/// <returns></returns>
public override async Task TestConnectionAsync()
{
using (var con = await CreateConnectionAsync())
using (var cmd = new OleDbCommand("SELECT 1", con))
await cmd.ExecuteScalarAsync();
}
/// <summary>
/// Receives file bytes from database by given file name
/// </summary>
/// <param name="name">Name of file to load</param>
public static async Task<byte[]> LoadFileAsync(string name)
{
string connectionString = defaultConnectionString;
using (var connection = new System.Data.OleDb.OleDbConnection(connectionString))
{
try
{
await connection.OpenAsync();
string query = "SELECT FileData FROM Files WHERE name = @name;";
using (var command = new System.Data.OleDb.OleDbCommand(query, connection))
{
command.Parameters.AddWithValue("@name", name);
return (byte[])await command.ExecuteScalarAsync();
}
}
catch(Exception ex)
{
TextEditor.WPFMessageBoxException(ex);
return null;
}
}
}
internal async Task ReloadAsync(OleDbConnection connection, OleDbTransaction transaction)
{
using (var cmd = new OleDbCommand("SELECT @@Options") { Connection = connection, Transaction = transaction })
m_Options = (int)(await cmd.ExecuteScalarAsync());
}
/// <summary>
/// Executes the specified operation asynchronously.
/// </summary>
/// <param name="executionToken"></param>
/// <param name="implementation"></param>
/// <param name="cancellationToken"></param>
/// <param name="state"></param>
/// <returns></returns>
protected override async Task<int?> ExecuteAsync(CommandExecutionToken<OleDbCommand, OleDbParameter> executionToken, CommandImplementationAsync<OleDbCommand> implementation, CancellationToken cancellationToken, object state)
{
if (executionToken == null)
throw new ArgumentNullException("executionToken", "executionToken is null.");
if (implementation == null)
throw new ArgumentNullException("implementation", "implementation is null.");
var currentToken = executionToken as AccessCommandExecutionToken;
if (currentToken == null)
throw new ArgumentNullException("executionToken", "only AccessCommandExecutionToken is supported.");
var startTime = DateTimeOffset.Now;
try
{
using (var con = await CreateConnectionAsync(cancellationToken).ConfigureAwait(false))
{
int? rows = null;
while (currentToken != null)
{
OnExecutionStarted(currentToken, startTime, state);
using (var cmd = new OleDbCommand())
{
cmd.Connection = con;
if (DefaultCommandTimeout.HasValue)
cmd.CommandTimeout = (int)DefaultCommandTimeout.Value.TotalSeconds;
cmd.CommandText = currentToken.CommandText;
cmd.CommandType = currentToken.CommandType;
foreach (var param in currentToken.Parameters)
cmd.Parameters.Add(param);
currentToken.ApplyCommandOverrides(cmd);
if (currentToken.ExecutionMode == AccessCommandExecutionMode.Materializer)
rows = await implementation(cmd);
else if (currentToken.ExecutionMode == AccessCommandExecutionMode.ExecuteScalarAndForward)
currentToken.ForwardResult(await cmd.ExecuteScalarAsync());
else
rows = await cmd.ExecuteNonQueryAsync();
executionToken.RaiseCommandExecuted(cmd, rows);
OnExecutionFinished(currentToken, startTime, DateTimeOffset.Now, rows, state);
}
currentToken = currentToken.NextCommand;
}
return rows;
}
}
catch (Exception ex)
{
if (cancellationToken.IsCancellationRequested) //convert Exception into a OperationCanceledException
{
var ex2 = new OperationCanceledException("Operation was canceled.", ex, cancellationToken);
OnExecutionError(executionToken, startTime, DateTimeOffset.Now, ex2, state);
throw ex2;
}
else
{
OnExecutionError(executionToken, startTime, DateTimeOffset.Now, ex, state);
throw;
}
}
}