/// <summary>
/// Reads all the records in a database
/// </summary>
/// <typeparam name="T"></typeparam>
/// <returns></returns>
public virtual async Task <List <T> > ReadAll <T>() where T : new()
{
var dest = new List <T>();
try
{
using (OdbcConnection cn = new OdbcConnection(this.ConnectionString))
{
using (OdbcCommand cmd = new OdbcCommand(this.Query, cn))
{
cn.Open();
using (DbDataReader dr = await cmd.ExecuteReaderAsync())
{
while (dr.Read())
{
dest.Add(SqlHelper.TranslateResults <T>(dr));
}
}
}
}
return(dest);
}
catch (Exception ex)
{
throw ex;
}
}
//.. Await = venter på at den er færdig samt skifter context.
//-- Kan køre i baggrunden uden at stoppe resterende ting i programmets flow.
//-- Hvis ting som tager sin tid, kan brugeren forsætte med andre ting uden at skulle begrænses.
//-- Er den færdig, kan vi derefter benytte den.
public async override Task <DataTable> FetchTableList(CancellationToken cancellationToken, CancellationTokenSource source)
{
var cmd = new OdbcCommand("SELECT * FROM user_tables", connection);
var RecapUpdatedTable = new DataTable();
///--------------------------------------------------
var OdbcDataReader = await cmd.ExecuteReaderAsync(cancellationToken);
//-- Pop up beskeden med fremskridt på importering af alle rækker.
await Task.Run(async() =>
{
//-- sætter kolonnerne
for (int i = 0; i < OdbcDataReader.FieldCount; i++)
{
RecapUpdatedTable.Columns.Add(OdbcDataReader.GetName(i));
}
//-- Sætter rækkerne
while ((await OdbcDataReader.ReadAsync()) && !cancellationToken.IsCancellationRequested)
{
object[] o = new object[OdbcDataReader.FieldCount];
for (int j = 0; j < OdbcDataReader.FieldCount; j++)
{
o[j] = OdbcDataReader[j].ToString();
}
RecapUpdatedTable.Rows.Add(o);
}
}, cancellationToken);
OdbcDataReader.Close();
return(RecapUpdatedTable);
}
public async Task <bool> AutorizeAsync(string token, Chat chat)
{
if (string.IsNullOrEmpty(token) || chat == null)
{
return(false);
}
try
{
var command = new OdbcCommand($"SELECT managerid, telegramchatid, token FROM telegram_managers where token='{token.Replace("\'", "\'\'")}';".Replace("\'\'", "NULL").Replace("NULLNULL", "\'\'\'\'"), Connection);
var reader = await command.ExecuteReaderAsync();
if (!reader.Read())
{
return(false);
}
var updcommand = new OdbcCommand($"UPDATE telegram_managers SET telegramchatid ={chat.Id}, token=null where token='{token?.Replace("\'", "\'\'")}'; ".Replace("\'\'", "NULL").Replace("NULLNULL", "\'\'\'\'"), Connection);
await updcommand.ExecuteNonQueryAsync();
return(true);
}
catch (Exception e)
{
Log.Add(new Log.LogMessage(Log.MessageType.ERROR, "AutorizeAsync: " + e.Message));
#if DEBUG
throw;
#endif
return(false);
}
}
public override async Task <T> GetAsT <T>()
{
var query = $"SELECT * FROM [{this.SheetName}]";
using (var conn = new OdbcConnection(this.ConnStr))
{
conn.Open();
var cmd = new OdbcCommand(query, conn);
using (var dr = await cmd.ExecuteReaderAsync())
return(this.ReadAsT <T>(dr));
}
}
/// <summary>
/// Write query results to json string or file.
/// </summary>
/// <param name="command"></param>
/// <param name="output"></param>
/// <param name="cancellationToken"></param>
/// <returns></returns>
public static async Task <string> ToJsonAsync(this OdbcCommand command, OutputProperties output, CancellationToken cancellationToken)
{
using (var reader = await command.ExecuteReaderAsync(cancellationToken))
{
var culture = string.IsNullOrWhiteSpace(output.JsonOutput.CultureInfo) ? CultureInfo.InvariantCulture : new CultureInfo(output.JsonOutput.CultureInfo);
// UTF-8 as default encoding.
var encoding = string.IsNullOrWhiteSpace(output.OutputFile?.Encoding) ? Encoding.UTF8 : Encoding.GetEncoding(output.OutputFile.Encoding);
// Create json result.
using (var fileWriter = output.OutputToFile ? new StreamWriter(output.OutputFile.Path, false, encoding) : null)
using (var writer = output.OutputToFile ? new JsonTextWriter(fileWriter) : new JTokenWriter() as JsonWriter)
{
writer.Formatting = Newtonsoft.Json.Formatting.Indented;
writer.Culture = culture;
// Start array.
await writer.WriteStartArrayAsync(cancellationToken);
cancellationToken.ThrowIfCancellationRequested();
while (reader.Read())
{
// Start row object.
await writer.WriteStartObjectAsync(cancellationToken);
for (var i = 0; i < reader.FieldCount; i++)
{
// Add row element name.
await writer.WritePropertyNameAsync(reader.GetName(i), cancellationToken);
await writer.WriteValueAsync(reader.GetValue(i) ?? string.Empty, cancellationToken);
cancellationToken.ThrowIfCancellationRequested();
}
// End row object.
await writer.WriteEndObjectAsync(cancellationToken);
cancellationToken.ThrowIfCancellationRequested();
}
// End array.
await writer.WriteEndArrayAsync(cancellationToken);
return(output.OutputToFile ? output.OutputFile.Path : ((JTokenWriter)writer).Token.ToString());
}
}
}
public async Task <string> GetApplicationUserListJsonAsync()
{
try
{
string selectStatement = $"select au.id, au.username from application_user au where au.is_del = 0";
com.CommandText = selectStatement;
if (con.State == ConnectionState.Closed)
{
con.Open();
}
List <ApplicationUserViewModel> applicationUserList = new List <ApplicationUserViewModel>();
using (var reader = await com.ExecuteReaderAsync())
{
while (await reader.ReadAsync())
{
string userId = reader["id"].ToString();
string username = reader["username"].ToString();
ApplicationUserViewModel appUser = new ApplicationUserViewModel
{
UserId = userId,
Username = username
};
applicationUserList.Add(appUser);
}
}
string appUserListJson = JsonConvert.SerializeObject(applicationUserList);
return(appUserListJson);
}
catch (Exception ex)
{
throw ex;
}
finally
{
con.Close();
}
return(string.Empty);
}
public async Task <string> GetCategoriesAsync()
{
string comboItemsJson = null;
try
{
string selectStatement = $"select c.id, c.name from category c where c.is_del = 0";
com.CommandText = selectStatement;
if (con.State == ConnectionState.Closed)
{
con.Open();
}
List <CategoryViewModel> comboItems = new List <CategoryViewModel>();
using (var reader = await com.ExecuteReaderAsync())
{
while (await reader.ReadAsync())
{
string id = reader["id"].ToString();
string name = reader["name"].ToString();
CategoryViewModel comboBoxItemViewModel = new CategoryViewModel
{
Id = id,
Name = name,
};
comboItems.Add(comboBoxItemViewModel);
}
}
comboItemsJson = JsonConvert.SerializeObject(comboItems);
}
catch (Exception ex)
{
//implement logger
}
finally
{
con.Close();
}
return(comboItemsJson);
}
public async Task <string> GetItemListJsonAsync()
{
try
{
string selectStatement = "select i.id, i.name from item i where i.is_del = 0";
com.CommandText = selectStatement;
if (con.State == ConnectionState.Closed)
{
await con.OpenAsync();
}
List <ItemViewModel> itemList = new List <ItemViewModel>();
using (var reader = await com.ExecuteReaderAsync())
{
while (await reader.ReadAsync())
{
string itemId = reader["id"].ToString();
string name = reader["name"].ToString();
ItemViewModel itemViewModel = new ItemViewModel
{
itemId = itemId,
Name = name,
};
itemList.Add(itemViewModel);
}
}
string itemListJson = JsonConvert.SerializeObject(itemList);
return(itemListJson);
}
catch (Exception ex)
{
throw ex;
}
finally
{
con.Close();
}
}
public async Task <List <Opportunity> > GetAllOpportunitiesAsync()
{
try
{
var opportunities = new DataTable();
opportunities.Load(await new OdbcCommand("SELECT id, clientid, description FROM opportunities;", Connection).ExecuteReaderAsync());
var result = new List <Opportunity>(opportunities.AsEnumerable().Count());
foreach (var row in opportunities.AsEnumerable())
{
var o = new Opportunity
{
Description = row[2].ToString(),
Contact = new Contact()
};
var command = new OdbcCommand($"SELECT phone, telegramuserid FROM telegram_clients where id={row[1]} limit 1;", Connection);
var reader = await command.ExecuteReaderAsync();
if (reader.Read())
{
o.Contact.PhoneNumber = reader.GetString(0);
var userc = new OdbcCommand($"SELECT first_name, last_name FROM telegram_users where id={reader[1]} limit 1;", Connection);
var reader2 = await userc.ExecuteReaderAsync();
if (reader2.Read())
{
o.Contact.FirstName = reader2[0].ToString();
o.Contact.LastName = reader2[1].ToString();
}
}
result.Add(o);
}
return(result);
}
catch (Exception e)
{
Log.Add(new Log.LogMessage(Log.MessageType.ERROR, "GetAllOpportunitiesAsync: " + e.Message));
#if DEBUG
throw;
#endif
return(new List <Opportunity>(0));
}
}
public async Task <(DateTime?, bool)> GetDueDateInfo(string fullWorkOrderNumber)
{
string workOrderNumber;
string workOrderNumberDash = string.Empty;
if (fullWorkOrderNumber.Contains("-"))
{
string[] workOrderSplit = fullWorkOrderNumber.Split('-');
workOrderNumber = workOrderSplit[0].Trim();
workOrderNumberDash = workOrderSplit[1].Trim();
}
else
{
workOrderNumber = fullWorkOrderNumber;
}
DateTime?dueDate = null;
bool expedite = false;
await using (OdbcConnection conn = new OdbcConnection())
{
conn.ConnectionString = @"Driver={Microsoft Access Driver (*.mdb, *.accdb)}; DBQ=database_location\database;";
await conn.OpenAsync();
string query = $"select [Date Due], Expedite from [Work Orders] where [Work Order No] = ? and [WO Dash] = ?";
var cmd = new OdbcCommand(query, conn);
cmd.Parameters.Add(new OdbcParameter("work order no", OdbcType.Int)).Value = workOrderNumber;
cmd.Parameters.Add(new OdbcParameter("wo dash", OdbcType.Text)).Value = workOrderNumberDash;
cmd.Prepare();
var reader = await cmd.ExecuteReaderAsync() as OdbcDataReader;
if (await reader.ReadAsync())
{
dueDate = reader.GetDate(0);
expedite = reader.GetBoolean(1);
}
else
{
}
}
return(dueDate, expedite);
}
/// <summary>
/// Write query results to csv string or file.
/// </summary>
/// <param name="command"></param>
/// <param name="output"></param>
/// <param name="cancellationToken"></param>
/// <returns></returns>
public static async Task <string> ToXmlAsync(this OdbcCommand command, OutputProperties output, CancellationToken cancellationToken)
{
// UTF-8 as default encoding.
var encoding = string.IsNullOrWhiteSpace(output.OutputFile?.Encoding) ? Encoding.UTF8 : Encoding.GetEncoding(output.OutputFile.Encoding);
using (var writer = output.OutputToFile ? new StreamWriter(output.OutputFile.Path, false, encoding) : new StringWriter() as TextWriter)
using (var reader = await command.ExecuteReaderAsync(cancellationToken))
{
using (var xmlWriter = XmlWriter.Create(writer, new XmlWriterSettings {
Async = true, Indent = true
}))
{
await xmlWriter.WriteStartDocumentAsync();
await xmlWriter.WriteStartElementAsync("", output.XmlOutput.RootElementName, "");
while (await reader.ReadAsync(cancellationToken))
{
// Single row element container.
await xmlWriter.WriteStartElementAsync("", output.XmlOutput.RowElementName, "");
for (int i = 0; i < reader.FieldCount; i++)
{
await xmlWriter.WriteElementStringAsync("", reader.GetName(i), "", reader.GetValue(i).ToString());
}
// Close single row element container.
await xmlWriter.WriteEndElementAsync();
// Write only complete elements, but stop if process was terminated.
cancellationToken.ThrowIfCancellationRequested();
}
await xmlWriter.WriteEndElementAsync();
await xmlWriter.WriteEndDocumentAsync();
}
return(output.OutputToFile ? output.OutputFile.Path : writer.ToString());
}
}
/// <summary>
/// Reads a single record from the database
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="Id"></param>
/// <returns></returns>
public virtual async Task <T> Read <T>(int Id) where T : new()
{
try
{
using (OdbcConnection cn = new OdbcConnection(this.ConnectionString))
{
using (OdbcCommand cmd = new OdbcCommand(this.Query, cn))
{
cn.Open();
using (DbDataReader dr = await cmd.ExecuteReaderAsync())
{
return(SqlHelper.TranslateResults <T>(dr));
}
}
}
}
catch (Exception ex)
{
throw ex;
}
}
public async Task <bool> IsAutorizedAsync(Chat chat)
{
if (chat == null)
{
return(false);
}
try
{
var command = new OdbcCommand($"SELECT * FROM telegram_managers where telegramchatid={chat.Id};", Connection);
var reader = await command.ExecuteReaderAsync();
return(reader.Read());
}
catch (Exception e)
{
Log.Add(new Log.LogMessage(Log.MessageType.ERROR, "IsAutorizedAsync: " + e.Message));
#if DEBUG
throw;
#endif
return(false);
}
}
public async Task <bool> IsDialogWhithManagerOpenedAsync(Chat chat)
{
if (chat == null)
{
return(false);
}
try
{
var command = new OdbcCommand($"SELECT is_dialog_opened FROM telegram_chats where id={chat.Id};", Connection);
var reader = await command.ExecuteReaderAsync();
return(reader.Read() && reader.GetBoolean(0));
}
catch (Exception e)
{
Log.Add(new Log.LogMessage(Log.MessageType.ERROR, "IsDialogWhithManagerOpenedAsync: " + e.Message));
#if DEBUG
throw;
#endif
return(false);
}
}
/// <summary>
/// Write query results to csv string or file.
/// </summary>
/// <param name="command"></param>
/// <param name="output"></param>
/// <param name="cancellationToken"></param>
/// <returns></returns>
public static async Task <string> ToCsvAsync(this OdbcCommand command, OutputProperties output, CancellationToken cancellationToken)
{
// UTF-8 as default encoding.
var encoding = string.IsNullOrWhiteSpace(output.OutputFile?.Encoding) ? Encoding.UTF8 : Encoding.GetEncoding(output.OutputFile.Encoding);
using (var reader = await command.ExecuteReaderAsync(cancellationToken))
using (var w = output.OutputToFile ? new StreamWriter(output.OutputFile.Path, false, encoding) : new StringWriter() as TextWriter)
{
bool headerWritten = false;
while (await reader.ReadAsync(cancellationToken))
{
// Write csv header if necessary.
if (!headerWritten && output.CsvOutput.IncludeHeaders)
{
var fieldNames = new object[reader.FieldCount];
for (int i = 0; i < reader.FieldCount; i++)
{
fieldNames[i] = reader.GetName(i);
}
await w.WriteLineAsync(string.Join(output.CsvOutput.CsvSeparator, fieldNames));
headerWritten = true;
}
var fieldValues = new object[reader.FieldCount];
for (int i = 0; i < reader.FieldCount; i++)
{
fieldValues[i] = reader.GetValue(i);
}
await w.WriteLineAsync(string.Join(output.CsvOutput.CsvSeparator, fieldValues));
// Write only complete rows, but stop if process was terminated.
cancellationToken.ThrowIfCancellationRequested();
}
return(output.OutputToFile ? output.OutputFile.Path : w.ToString());
}
}
public async Task <List <string> > GetCustomers()
{
var customers = new List <string>();
await using (OdbcConnection conn = new OdbcConnection())
{
conn.ConnectionString = @"Driver={Microsoft Access Driver (*.mdb, *.accdb)}; DBQ=database_location\database;";
await conn.OpenAsync();
string query = "select [FolderName] from [customers] order by [FolderName]";
OdbcCommand cmd = new OdbcCommand(query, conn);
OdbcDataReader reader = await cmd.ExecuteReaderAsync() as OdbcDataReader;
while (await reader.ReadAsync())
{
string customer = reader[0].ToString();
customers.Add(customer);
}
conn.Close();
}
return(customers);
}
public static async Task <IEnumerable <Product> > GetProductsAsync()
{
if (DateTime.Now < cacheExpiry)
{
return(products);
}
string connectionString = "DSN=Swords ODBC Driver";
string query = "select pbd_prod_code, TRIM(pbd_prod_name) + ' ' + pbd_prod_unit, pbd_prod_unit, pbd_prod_pack, (SELECT TOP 1 DATEADD(day,-1, A.bsj_putaway_date) FROM admin.sffbsj A WHERE A.bsj_prod_ptr=P.pbd_prod_ptr AND A.bsj_type=1 ORDER BY A.bsj_move_date DESC), pxd_ean_code, psd_stk_qty, psd_stk_qty+psd_booked_qty, psd_booked_qty, psd_po_qty from admin.sffpbd p left join admin.sffpsd on psd_prod_ptr = pbd_prod_ptr and psd_stock_loc = 1 left join admin.sffpxd_details on pxd_prod_ptr = pbd_prod_ptr";
OdbcConnection odbcConnection = new OdbcConnection(connectionString);
OdbcCommand odbcCommand = new OdbcCommand(query, odbcConnection)
{
CommandTimeout = 90
};
DbDataReader reader = null;
List <Product> output = null;
try
{
odbcConnection.Open();
reader = await odbcCommand.ExecuteReaderAsync();
if (reader.HasRows)
{
while (reader.Read())
{
output.Add(new Product
{
ProductId = reader.GetString(0).Trim() ?? default,
Description = reader.GetString(1).Trim() ?? default,
Size = reader.GetString(2).Trim() ?? default,
CaseSize = reader.GetInt32(3),
ExpiryDate = reader.IsDBNull(4) ? default : reader.GetDateTime(4),
Barcode = reader.GetString(5).Trim() ?? default,
FreeStock = (short)reader.GetDecimal(6),
Stock = (short)reader.GetDecimal(7),
AllocatedStock = (short)reader.GetDecimal(8),
StockOnOrder = (short)reader.GetDecimal(9)
});
}
private async void LoadNewMessages()
{
string queryString = string.Format("SELECT * FROM dbo.ServerMessages WHERE Id > {0}", LastMsgId);
OdbcCommand command = new OdbcCommand(queryString);
using (OdbcConnection connection = new OdbcConnection(CONNECTION_STRING))
{
command.Connection = connection;
connection.Open();
using (var reader = await command.ExecuteReaderAsync())
{
while (await reader.ReadAsync())
{
var data = reader["Message"].ToString();
var sender = reader["SenderId"].ToString();
LastMsgId = Int32.Parse(reader["Id"].ToString());
Messages += string.Format("{0}: {1}\n", sender, data);
}
}
}
}
/// <summary>
/// Verify if a given context is supported for current secure store
/// Tries to connect to the DB given by the context
/// </summary>
/// <returns></returns>
public async Task ValidateContextAsync(string context)
{
// verify context is able to be deserialized
SqlPassContext config = DeserializeContext(context);
// verify connection can be established
string connectionString = BuildConnectionString(config);
using (OdbcConnection connection = new OdbcConnection(connectionString))
using (OdbcCommandBuilder builder = new OdbcCommandBuilder())
{
await connection.OpenAsync();
string escapedTableName = builder.QuoteIdentifier(config.TableName, connection);
try
{
using (OdbcCommand command = new OdbcCommand($"SELECT TOP 1 * FROM {escapedTableName}", connection))
{
await command.ExecuteReaderAsync();
}
}
catch (OdbcException ex)
{
if (!ex.Message.Contains($"Invalid object name '{config.TableName}'"))
{
throw;
}
string queryStringCreateTable = $@"CREATE TABLE {escapedTableName} (
name varchar(96) NOT NULL PRIMARY KEY,
value varchar(max) NOT NULL)";
using (OdbcCommand command = new OdbcCommand(queryStringCreateTable, connection))
{
await command.ExecuteNonQueryAsync();
}
}
}
}
/// <summary>
/// Await = venter på at den er færdig samt skifter context.
/// Kan køre i baggrunden uden at stoppe resterende ting i programmets flow.
///Hvis ting som tager sin tid, kan brugeren forsætte med andre ting uden at skulle begrænses.
/// Er den færdig, kan vi derefter benytte den.
/// </summary>
/// <param name="command"></param>
/// <param name="progress"></param>
/// <param name="cancellationToken"></param>
/// <param name="source"></param>
/// <param name="rowlimiter"></param>
/// <returns></returns>
public async override Task <DataTable> Execute(string command, IProgress <int> progress, CancellationToken cancellationToken, CancellationTokenSource source, int rowlimiter)
{
var cmd = new OdbcCommand(command, connection);
var RecapUpdatedTable = new DataTable();
var OdbcDataReader = await cmd.ExecuteReaderAsync(cancellationToken);
//-- Pop up beskeden med fremskridt på importering af alle rækker.
var RecordCount = 0;
await Task.Run(async() =>
{
//-- sætter kolonnerne
for (int i = 0; i < OdbcDataReader.FieldCount; i++)
{
RecapUpdatedTable.Columns.Add(OdbcDataReader.GetName(i));
}
//-- Sætter rækkerne
while ((await OdbcDataReader.ReadAsync()) && !cancellationToken.IsCancellationRequested && RecordCount != rowlimiter)
{
object[] o = new object[OdbcDataReader.FieldCount];
for (int j = 0; j < OdbcDataReader.FieldCount; j++)
{
o[j] = OdbcDataReader[j].ToString();
}
RecapUpdatedTable.Rows.Add(o);
RecordCount++;
progress.Report(RecordCount);
LimitRecordFetching(source, RecordCount);
}
}, cancellationToken);
OdbcDataReader.Close();
return(RecapUpdatedTable);
}
public async Task <string> GetJobYear(string fullWorkOrderNumber)
{
string workOrderNumber;
string workOrderNumberDash = string.Empty;
if (fullWorkOrderNumber.Contains("-"))
{
string[] workOrderSplit = fullWorkOrderNumber.Split('-');
workOrderNumber = workOrderSplit[0];
workOrderNumberDash = workOrderSplit[1];
}
else
{
workOrderNumber = fullWorkOrderNumber;
}
string dateReceivedYear = string.Empty;
using (var conn = new OdbcConnection())
{
conn.ConnectionString = @"Driver={Microsoft Access Driver (*.mdb, *.accdb)}; DBQ=database_location\database;";
await conn.OpenAsync();
string query = $"select [Date Received] from [Work Orders] where [Work Order No] = ? and [WO Dash] = ?";
var cmd = new OdbcCommand(query, conn);
cmd.Parameters.Add(new OdbcParameter("work order no", OdbcType.Int)).Value = workOrderNumber;
cmd.Parameters.Add(new OdbcParameter("wo dash", OdbcType.Text)).Value = workOrderNumberDash;
cmd.Prepare();
var reader = await cmd.ExecuteReaderAsync() as OdbcDataReader;
dateReceivedYear = await reader.ReadAsync() ? ((DateTime)reader[0]).Year.ToString() : string.Empty;
//dateReceivedYear = string.IsNullOrEmpty(reader[0].ToString()) ? string.Empty : ((DateTime)reader[0]).Year.ToString();
conn.Close();
}
return(dateReceivedYear);
}
/// <summary>
/// Execute one or more SQL statements as a transaction. Roll back the transaction if any of them fail.
/// </summary>
public async Task Execute(
IUi ui,
int rowLimit,
string connectionString)
{
List <string> statements = SqlQuery.SplitIntoStatements(Query);
if (statements.Count == 0)
{
return;
}
var sw = new System.Diagnostics.Stopwatch();
var currentStatement = "";
try
{
using (var cn = new OdbcConnection(connectionString))
{
// START CONNECT
ui.SetStatusLabel(ExecuteStatus.Connecting.Description());
sw.Restart();
cn.Open();
Duration_Connect = sw.Elapsed;
// END CONNECT
using (var cmd = new OdbcCommand())
{
cmd.Connection = cn;
// START EXECUTE
ui.SetStatusLabel(ExecuteStatus.Executing.Description());
sw.Restart();
using (var t = cn.BeginTransaction())
{
cmd.Transaction = t; // Assign transaction object for a pending local transaction.
foreach (var statement in statements)
{
if (string.IsNullOrEmpty(statement))
{
continue;
}
currentStatement = statement.Trim();
cmd.CommandText = currentStatement;
var statementResult = new StatementResult();
if (!currentStatement.StartsWith("select", StringComparison.OrdinalIgnoreCase))
{
statementResult.RowsAffected = await cmd.ExecuteNonQueryAsync();
statementResult.Duration_Execute = sw.Elapsed;
// END EXECUTE
}
else
{
using (var reader = await cmd.ExecuteReaderAsync())
{
statementResult.Duration_Execute = sw.Elapsed;
// END EXECUTE
// BEGIN STREAM
ui.SetStatusLabel(ExecuteStatus.Streaming.Description());
sw.Restart();
statementResult.Data.Add(GetColumns(reader));
int readCount = 1;
while (readCount <= rowLimit && reader.Read())
{
statementResult.Data.Add(GetData(reader));
readCount += 1;
}
statementResult.Duration_Stream = sw.Elapsed;
// END STREAM
ui.SetStatusLabel("");
statementResult.RowsAffected = reader.RecordsAffected;
}
}
Results.Add(statementResult);
}
try
{
t.Commit();
}
catch
{
t.Rollback();
throw;
}
}
}
}
}
catch (ArgumentOutOfRangeException ex)
{
if (ex.Message == "Year, Month, and Day parameters describe an un-representable DateTime.")
{
// filemaker allows importing incorrect data into fields, so we need to catch these errors!
Error = ExecuteError.UnrepresentableDateTimeValue.Description();
}
else
{
Error = ex.Message;
}
Error += Environment.NewLine + Environment.NewLine + currentStatement;
}
catch (Exception ex)
{
Error = ex.Message + Environment.NewLine + Environment.NewLine + currentStatement;
}
}
private async Task getHousing()
{
IEnumerable <Housing> housingCollection = new List <Housing>();
using (OdbcConnection conn = new OdbcConnection(_connectionString))
{
conn.OpenAsync().Wait();
OdbcCommand cmd = conn.CreateCommand();
cmd.CommandText = _tableHousing;
DbDataReader dr = await cmd.ExecuteReaderAsync();
Housing housing = new Housing();
while (dr.Read())
{
housing = new Housing();
if (dr["people_total"] != null)
{
housing.HouseHoldIncome = int.Parse(dr["people_total"].ToString());
}
if (dr["spending_per_month"] != null)
{
housing.SpedningPerMonth = decimal.Parse(dr["spending_per_month"].ToString());
}
if (dr["hh_income"] != null)
{
housing.SpedningPerMonth = decimal.Parse(dr["hh_income"].ToString());
}
if (dr["person_income"] != null)
{
housing.PersonIncome = decimal.Parse(dr["person_income"].ToString());
}
if (dr["pct_lt5k"] != null)
{
housing.HouseHoldIncomeUnder5K = decimal.Parse(dr["pct_lt5k"].ToString());
}
if (dr["pct_5k_lt10k"] != null)
{
housing.HouseHoldIncomeBetween5KAnd10K = decimal.Parse(dr["pct_5k_lt10k"].ToString());
}
if (dr["pct_10k_lt15k"] != null)
{
housing.HouseHoldIncomeBetween10KAnd15K = decimal.Parse(dr["pct_10k_lt15k"].ToString());
}
if (dr["pct_15k_lt20k"] != null)
{
housing.HouseHoldIncomeBetween15KAnd20K = decimal.Parse(dr["pct_15k_lt20k"].ToString());
}
if (dr["pct_ge20k"] != null)
{
housing.HouseHoldIncomeAbove20K = decimal.Parse(dr["pct_ge20k"].ToString());
}
if (dr["STD_ZIP5"] != null)
{
housing.StdZip = decimal.Parse(dr["STD_ZIP5"].ToString());
}
if (dr["longitude"] != null)
{
housing.Longitude = decimal.Parse(dr["longitude"].ToString());
}
if (dr["latitude"] != null)
{
housing.Latitude = decimal.Parse(dr["latitude"].ToString());
}
(housingCollection as List <Housing>).Add(housing);
}
}
_housings = housingCollection;
}
public async Task <IReader> ExecuteReaderAsync()
{
return(new Reader(await _cmd.ExecuteReaderAsync()));
}
public async Task <ConnectedOdbcDataReader> RunCommandAsync(OdbcConnection connection, OdbcCommand command)
{
return(new ConnectedOdbcDataReader(connection, await command.ExecuteReaderAsync() as OdbcDataReader));
}
