public async Task <bool> AutorizeAsync(string token, Chat chat)
{
if (string.IsNullOrEmpty(token) || chat == null)
{
return(false);
}
try
{
var command = new OleDbCommand($"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 OleDbCommand($"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);
}
}
/// <summary>
/// Can be used to get a teacher of this topic.
/// </summary>
/// <returns></returns>
public async Task <Teacher> GetTeacherAsync()
{
OleDbConnection conn;
OleDbCommand command;
DbDataReader reader;
Guid id = this.ID;
conn = new OleDbConnection(AppConfiguration.connectionString);
conn.Open();
command = new OleDbCommand("SELECT Teachers.*, Users.FirstName, Users.SecondName, Users.BirthDate, Users.Password FROM ((Teachers INNER JOIN Topics ON Topics.TEACHER_ID=Teachers.ID) INNER JOIN Users ON Teachers.USER_ID=Users.ID) WHERE Topics.ID=@ID'",
conn);
command.Parameters.AddWithValue("ID", ID);
reader = await command.ExecuteReaderAsync();
if (reader.Read())
{
BasicUserProperties properties;
Teacher teacher;
properties = new BasicUserProperties();
properties.FirstName = reader.GetString(2);
properties.SecondName = reader.GetString(3);
properties.Password = reader.GetString(4);
teacher = new Teacher(properties, reader.GetGuid(1), reader.GetGuid(0));
reader.Close();
conn.Close();
return(teacher);
}
else
{
throw new Exception("Query result not correct");
}
}
/// <summary>
/// Get All call per UserID
/// </summary>
/// <returns></returns>
public async Task <ObservableCollection <Caller> > GetCallsByIdAsync(int userid)
{
ObservableCollection <Caller> Calls = new ObservableCollection <Caller>();
await Conn.OpenAsync();
Cmd = new OleDbCommand();
Cmd.Connection = Conn;
Cmd.CommandText = "Select * From [Details$] where UserID='" + userid + "'";
var Reader = await Cmd.ExecuteReaderAsync();
while (Reader.Read())
{
Calls.Add(new Caller()
{
Outbound = Reader[0].ToString(),
Extension = Reader[1].ToString(),
CallerNumber = Reader["Caller Number"].ToString(),
Recording = Convert.ToInt32(Reader["Recording"]),
Date = Convert.ToDateTime(Reader["Date"]),
Time = Convert.ToDateTime(Reader["Time"]),
Duration = Convert.ToDateTime(Reader["Duration"]),
TimeBetweenCalls = Convert.ToDateTime(Reader["Time Between Calls"]),
UserId = Convert.ToInt32(Reader["UserID"])
});
}
Reader.Close();
Conn.Close();
return(Calls);
}
/// <summary>
/// Retrieves the question associated with this answer.
/// </summary>
/// <returns></returns>
public async Task <Question> GetQuestionAsync()
{
OleDbConnection conn;
OleDbCommand command;
DbDataReader reader;
conn = new OleDbConnection(AppConfiguration.connectionString);
conn.Open();
command = new OleDbCommand("SELECT Questions.* FROM Answers INNER JOIN Questions ON Answers.QUESTION_ID=Questions.ID WHERE Answers.ID=@ID",
conn);
command.Parameters.AddWithValue("ID", ID);
reader = await command.ExecuteReaderAsync();
if (reader.Read())
{
Question question = new Question(reader.GetString(1), reader.GetGuid(2), reader.GetGuid(0));
reader.Close();
return(question);
}
else
{
throw new Exception("Query result not correct");
}
}
//should check if exists / is used
private async Task <bool> CodeExists(string code)
{
try
{
connection.Open();
OleDbCommand command = new OleDbCommand();
command.Connection = connection;
command.CommandText = $@"select ID, Code from Discounts where Code = {code}";
var reader = await command.ExecuteReaderAsync();
try
{
return(reader.HasRows);
}
finally
{
reader.Close();
}
}
finally
{
connection.Close();
}
}
/// <summary>
/// Get all Students
/// </summary>
/// <returns></returns>
public async static Task <List <Student> > GetAllAsync()
{
OleDbConnection conn;
OleDbCommand command;
DbDataReader reader;
BasicUserProperties properties;
Class sClass;
List <Student> students = new List <Student>();
conn = new OleDbConnection(AppConfiguration.connectionString);
conn.Open();
command = new OleDbCommand("SELECT Students.ID, Classes.ID, Users.ID, Users.FirstName, Users.SecondName, Users.BirthDate, Classes.SchoolYear, Classes.Course FROM (Students INNER JOIN Users ON Students.USER_ID=Users.ID)INNER JOIN Classes ON Students.CLASS_ID=Classes.ID",
conn);
reader = await command.ExecuteReaderAsync();
while (reader.Read())
{
properties = new BasicUserProperties();
properties.FirstName = reader.GetString(3);
properties.SecondName = reader.GetString(4);
properties.BirthDate = reader.GetDateTime(5);
sClass = new Class(reader.GetString(6), reader.GetString(7), reader.GetGuid(1));
students.Add(new Student(properties, sClass, reader.GetGuid(2), reader.GetGuid(0)));
}
reader.Close();
conn.Close();
return(students);
}
/// <summary>
/// Retrives the topic associated with this answer.
/// </summary>
/// <returns></returns>
public async Task <Topic> GetTopicAsync()
{
OleDbConnection conn;
OleDbCommand command;
DbDataReader reader;
Guid id = this.ID;
conn = new OleDbConnection(AppConfiguration.connectionString);
conn.Open();
command = new OleDbCommand("SELECT Topics.* FROM Questions INNER JOIN Topics ON Questions.TOPIC_ID=Topics.ID WHERE Questions.ID = @ID",
conn);
command.Parameters.AddWithValue("ID", id);
reader = await command.ExecuteReaderAsync();
if (reader.Read())
{
Topic topic = new Topic(reader.GetString(1), reader.GetGuid(2), reader.GetGuid(0));
reader.Close();
return(topic);
}
else
{
throw new Exception("Query result not correct");
}
}
/// <summary>
/// Retrives the student associated with this class.
/// </summary>
/// <returns></returns>
public async Task <List <Student> > GetStudentsAsync()
{
OleDbConnection conn;
OleDbCommand command;
List <Student> students = new List <Student>();
DbDataReader reader;
Guid id = this.ID;
conn = new OleDbConnection(AppConfiguration.connectionString);
conn.Open();
command = new OleDbCommand("SELECT Students.*, Users.FirstName, Users.SecondName, Users.BirthDate, Users.Password FROM (( Students INNER JOIN Users ON Students.USER_ID=Users.ID) INNER JOIN Classes ON Students.CLASS_ID=Classes.ID) WHERE Classes.ID = @ID",
conn);
command.Parameters.AddWithValue("ID", id);
reader = await command.ExecuteReaderAsync();
while (reader.Read())
{
BasicUserProperties properties;
properties = new BasicUserProperties();
properties.FirstName = reader.GetString(3);
properties.SecondName = reader.GetString(4);
properties.BirthDate = reader.GetDateTime(5);
students.Add(new Student(properties, this, reader.GetGuid(2), reader.GetGuid(0)));
}
reader.Close();
conn.Close();
return(students);
}
/// <summary>
/// Retrives the student associated with this evaluation.
/// </summary>
/// <returns></returns>
public async Task <Student> GetStudentAsync()
{
OleDbConnection conn;
OleDbCommand command;
Guid id = this.ID;
conn = new OleDbConnection(AppConfiguration.connectionString);
conn.Open();
command = new OleDbCommand("SELECT Students.*, Users.FirstName, Users.SecondName, Users.BirthDate, Users.Password FROM ((Students INNER JOIN Users ON Students.USER_ID=Users.ID) INNER JOIN Evaluations ON Evaluations.STUDENT_ID=Students.ID) WHERE Evaluations.ID = @ID",
conn);
command.Parameters.AddWithValue("ID", id);
DbDataReader reader = await command.ExecuteReaderAsync();
if (reader.Read())
{
BasicUserProperties properties;
Student student;
properties = new BasicUserProperties();
properties.FirstName = reader.GetString(3);
properties.SecondName = reader.GetString(4);
properties.BirthDate = reader.GetDateTime(5);
student = new Student(properties, reader.GetGuid(1), reader.GetGuid(2), reader.GetGuid(0));
reader.Close();
conn.Close();
return(student);
}
else
{
throw new Exception("Query result not correct");
}
}
/// <summary>
/// Method to Get All the Records from Excel
/// </summary>
/// <returns></returns>
public async Task <ObservableCollection <Student> > ReadRecordFromEXCELAsync()
{
ObservableCollection <Student> Students = new ObservableCollection <Student>();
await Conn.OpenAsync();
Cmd = new OleDbCommand();
Cmd.Connection = Conn;
Cmd.CommandText = "Select * from [Sheet1$]";
var Reader = await Cmd.ExecuteReaderAsync();
while (Reader.Read())
{
Students.Add(new Student()
{
StudentID = Convert.ToInt32(Reader["StudentID"]),
Name = Reader["Name"].ToString(),
Email = Reader["Email"].ToString(),
Class = Reader["Class"].ToString(),
Address = Reader["Address"].ToString()
});
}
Reader.Close();
Conn.Close();
return(Students);
}
/// <summary>
/// Retrives the teacher associated with this class.
/// </summary>
/// <returns></returns>
public async Task <List <Teacher> > GetTeachersAsync()
{
OleDbConnection conn;
OleDbCommand command;
List <Teacher> teacher = new List <Teacher>();
DbDataReader reader;
Guid id = this.ID;
conn = new OleDbConnection(AppConfiguration.connectionString);
conn.Open();
command = new OleDbCommand("SELECT Teachers.*, Users.FirstName, Users.SecondName, Users.BirthDate, Users.Password FROM (((Teachers INNER JOIN Users ON Teacher.USER_ID=Teachers.ID) INNER JOIN TeachesClass ON TeachesClass.TEACHER_ID=Teachers.ID) INNER JOIN Classes ON TeachesClass.CLASS_ID=Classes.ID) WHERE Classes.ID=@ID'",
conn);
command.Parameters.AddWithValue("ID", id);
reader = await command.ExecuteReaderAsync();
while (reader.Read())
{
BasicUserProperties properties;
properties = new BasicUserProperties(reader.GetString(3), reader.GetString(4), reader.GetDateTime(5), reader.GetString(6));
teacher.Add(new Teacher(properties, reader.GetGuid(1), reader.GetGuid(0)));
}
reader.Close();
conn.Close();
return(teacher);
}
/// <summary>
/// Executes a query inside the database and returns a <see cref="QueryResult"/> object.
/// </summary>
/// <param name="query">The full query to execute.</param>
/// <returns>Detailed info about the executed query.</returns>
public async Task <QueryResult> ExecuteQuery(string query)
{
//Throws an exception if the object is already disposed
if (IsDisposed)
{
throw new InvalidOperationException("Connection is closed.");
}
//Creates the command object which holds the query
using var command = new OleDbCommand(query, _connection);
//Tries to execute the query and save its info
DbDataReader reader = null;
QueryResult res;
try
{
reader = await command.ExecuteReaderAsync();
res = new QueryResult(query, reader);
}
finally
{
//Disposes the used objects
reader?.Close();
command.Dispose();
}
return(res);
}
/// <summary>
/// Retrives the classes associated with this teacher.
/// </summary>
/// <returns></returns>
public async Task <List <Class> > GetClassesAsync()
{
OleDbConnection conn;
OleDbCommand command;
DbDataReader reader;
List <Class> classes = new List <Class>();
Guid id = this.ID;
conn = new OleDbConnection(AppConfiguration.connectionString);
conn.Open();
command = new OleDbCommand("SELECT Classes.* FROM ((Classes INNER JOIN TeachesClass ON TeachesClass.CLASS_ID=Classes.ID)INNER JOIN Teachers ON TeachesClass.TEACHER_ID=Teachers.ID) WHERE Teachers.ID=@ID",
conn);
command.Parameters.AddWithValue("ID", id);
reader = await command.ExecuteReaderAsync();
while (reader.Read())
{
classes.Add(new Class(reader.GetString(1), reader.GetString(2), reader.GetGuid(0)));
}
reader.Close();
conn.Close();
return(classes);
}
/// <summary>
/// Retrieve the questionnaire session created by this teacher.
/// </summary>
/// <returns></returns>
public async Task <List <QuestionnaireSession> > GetQuestionnaireSessionsAsync()
{
using (OleDbConnection conn = new OleDbConnection(AppConfiguration.connectionString))
using (OleDbCommand command = new OleDbCommand())
{
List <QuestionnaireSession> questionnaireSessions = new List <QuestionnaireSession>();
conn.Open();
command.Connection = conn;
command.CommandText = @"SELECT QuestionnaireSessions.ID, QuestionnaireSessions.SessionDate, QuestionnaireSessions.Description
FROM (Topics
INNER JOIN Evaluations ON Topics.ID=Evaluations.TOPIC_ID)
INNER JOIN QuestionnaireSessions ON Evaluations.QUESTIONNAIRESESSION_ID=QuestionnaireSessions.ID
WHERE Topics.TEACHER_ID=@TeacherID";
command.Parameters.AddWithValue("TeacherID", ID);
using (DbDataReader reader = await command.ExecuteReaderAsync())
{
while (reader.Read())
{
questionnaireSessions.Add(new QuestionnaireSession(reader.GetGuid(0), reader.GetString(2), reader.GetDateTime(1)));
}
return(questionnaireSessions);
}
}
}
/// <summary>
/// Get all Teachers
/// </summary>
/// <returns></returns>
public async static Task <List <Teacher> > GetAllAsync()
{
OleDbConnection conn;
OleDbCommand command;
DbDataReader reader;
List <Teacher> teachers = new List <Teacher>();
conn = new OleDbConnection(AppConfiguration.connectionString);
conn.Open();
command = new OleDbCommand("SELECT Teachers.ID, Users.ID, Users.FirstName, Users.SecondName, Users.BirthDate, Users.Password FROM Teachers INNER JOIN Users ON Teachers.USER_ID=Users.ID",
conn);
reader = await command.ExecuteReaderAsync();
while (reader.Read())
{
BasicUserProperties properties;
properties = new BasicUserProperties();
properties.FirstName = reader.GetString(2);
properties.SecondName = reader.GetString(3);
properties.BirthDate = reader.GetDateTime(4);
properties.Password = reader.GetString(5);
teachers.Add(new Teacher(properties, reader.GetGuid(1), reader.GetGuid(0)));
}
reader.Close();
conn.Close();
return(teachers);
}
/// <summary>
/// Retrives the topic associated with this teacher.
/// </summary>
/// <returns></returns>
public async Task <List <Topic> > GetTopicsAsync()
{
OleDbConnection conn;
OleDbCommand command;
DbDataReader reader;
List <Topic> topics = new List <Topic>();
Guid id = this.ID;
conn = new OleDbConnection(AppConfiguration.connectionString);
conn.Open();
command = new OleDbCommand("SELECT Topics.* FROM Topics INNER JOIN Teachers ON Topics.TEACHER_ID=Teachers.ID WHERE Teachers.ID = @ID",
conn);
command.Parameters.AddWithValue("ID", id);
reader = await command.ExecuteReaderAsync();
while (reader.Read())
{
topics.Add(new Topic(reader.GetString(1), reader.GetGuid(2), reader.GetGuid(0)));
}
reader.Close();
conn.Close();
return(topics);
}
/// <summary>
/// Retrives the evaluation associated with this student.
/// </summary>
/// <returns></returns>
public async Task <List <Evaluation> > GetEvaluationAsync()
{
OleDbConnection conn;
OleDbCommand command;
List <Evaluation> evaluation = new List <Evaluation>();
DbDataReader reader;
Guid id = this.ID;
conn = new OleDbConnection(AppConfiguration.connectionString);
conn.Open();
command = new OleDbCommand("SELECT Evaluations.* FROM Evaluations INNER JOIN Students ON Evaluations.STUDENT_ID=Students.ID WHERE Students.ID = @ID",
conn);
command.Parameters.AddWithValue("ID", id);
reader = await command.ExecuteReaderAsync();
while (reader.Read())
{
evaluation.Add(new Evaluation(reader.GetDateTime(1), reader.GetDouble(2), reader.GetGuid(3), reader.GetGuid(4), reader.GetGuid(0)));
}
reader.Close();
conn.Close();
return(evaluation);
}
public async Task <ObservableCollection <MyClassEmpProperty> > ReadRecordFromEXCELAsync()
{
ObservableCollection <MyClassEmpProperty> myClassEmpProperty = new ObservableCollection <MyClassEmpProperty>();
await Conn.OpenAsync();
Cmd = new OleDbCommand();
Cmd.Connection = Conn;
Cmd.CommandText = "Select * from [Sheet1$]";
var Reader = await Cmd.ExecuteReaderAsync();
while (Reader.Read())
{
myClassEmpProperty.Add(new MyClassEmpProperty()
{
EmpName = Reader["EmpName"].ToString(),
EmpPosition = Reader["EmpPosition"].ToString(),
EmpGender = Reader["EmpGender"].ToString(),
EmpAge = Reader["EmpAge"].ToString(),
EmpAddress = Reader["EmpAddress"].ToString(),
EmpCity = Reader["EmpCity"].ToString(),
EmpEmailID = Reader["EmpEmailID"].ToString(),
EmpContactNo = Reader["EmpContactNo"].ToString()
});
}
Reader.Close();
Conn.Close();
return(myClassEmpProperty);
}
internal async Task <IEnumerable <DriverDAL> > SelectByDateSpeed(DateTime dt, float speed)
{
List <DriverDAL> drivers = new List <DriverDAL>();
if (!CheckDBExists())
{
return(drivers);
}
using (var connection = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + _directoryPath + @";Extended Properties=text"))
{
string commandStr = $"SELECT * FROM [{_fileName}] WHERE Speed > @speed AND Date BETWEEN #{dt.ToString("MM/dd/yyyy")} 00:00:00# AND #{dt.ToString("MM/dd/yyyy")} 23:59:59#";
await connection.OpenAsync();
OleDbCommand command = new OleDbCommand(commandStr, connection);
command.Parameters.AddWithValue("@speed", speed).OleDbType = OleDbType.Double;
var reader = await command.ExecuteReaderAsync();
while (await reader.ReadAsync())
{
var model = new DriverDAL(reader[2].ToString(), DateTime.Parse(reader[0].ToString()), float.Parse(reader[1].ToString()));
drivers.Add(model);
}
}
return(drivers);
}
public async override Task <DaoReader> ExecQueryAsync(string sql, params object[] objects)
{
LinkedList <Dictionary <string, object> > resultSet = new LinkedList <Dictionary <string, object> >();
OleDbCommand command = this.CreateCommand(sql, objects);
using (DbDataReader reader = await command.ExecuteReaderAsync()) {
// フィールド名の取得
List <string> fieldNameList = new List <string>();
Parallel.For(0, reader.FieldCount - 1, (i) => {
string tmp = reader.GetName(i);
lock (this) {
fieldNameList.Add(tmp);
}
});
// レコードの取得
while (reader.Read())
{
Dictionary <string, object> result = new Dictionary <string, object>();
Parallel.ForEach(fieldNameList, (fieldName) => {
object tmp = reader[fieldName];
lock (this) {
result.Add(fieldName, tmp);
}
});
resultSet.AddLast(result);
}
}
return(new DaoReader(sql, resultSet));
}
/// <summary>
/// SUCKS
/// </summary>
public async void UpdateStudentAsync(Class newClass)
{
OleDbConnection conn;
OleDbCommand command;
DbDataReader reader;
conn = new OleDbConnection(AppConfiguration.connectionString);
conn.Open();
if (Class.ID != new Guid())
{
command = new OleDbCommand("SELECT ID FROM Classes WHERE SchoolYear=@SchoolYear AND Course=@Course",
conn);
command.Parameters.AddWithValue("SchoolYear", newClass.SchoolYear);
command.Parameters.AddWithValue("Course", newClass.Course);
reader = await command.ExecuteReaderAsync();
if (reader.Read())
{
Class.ID = reader.GetGuid(0);
}
reader.Close();
}
command = new OleDbCommand("UPDATE Students CLASS_ID=@ClassID WHERE ID=@ID",
conn);
command.Parameters.AddWithValue("ClassID", Class.ID);
command.Parameters.AddWithValue("ID", ID);
await command.ExecuteNonQueryAsync();
conn.Close();
}
/// <summary>
/// Can be used to get correct and/or wrong answers of this question.
/// </summary>
/// <returns>The list of queried answers.</returns>
public async Task <List <Answer> > GetAnswersAsync(bool includeCorrect = true, bool includeFalse = true)
{
OleDbConnection conn;
OleDbCommand command;
DbDataReader reader;
List <Answer> answers = new List <Answer>();
Guid id = this.ID;
conn = new OleDbConnection(AppConfiguration.connectionString);
conn.Open();
command = new OleDbCommand("SELECT Answers.* FROM Answers INNER JOIN Questions ON Answers.QUESTION_ID=Questions.ID WHERE Questions.ID = @ID " + ((includeCorrect && includeFalse) ? "" : (includeCorrect) ? "AND Answers.Correct = -1" : "AND Answers.Correct = 0"),
conn);
command.Parameters.AddWithValue("ID", id);
reader = await command.ExecuteReaderAsync();
while (reader.Read())
{
answers.Add(new Answer(reader.GetString(1), reader.GetBoolean(2), reader.GetGuid(3), reader.GetGuid(0)));
}
reader.Close();
conn.Close();
return(answers);
}
public async Task <ObservableCollection <Patient> > ReadAllPatientsFromExcelAsync()
{
try
{
var patients = new ObservableCollection <Patient>();
await conn.OpenAsync();
var cmd = new OleDbCommand
{
Connection = conn,
CommandText = "SELECT * FROM [Patienten$]"
};
var reader = await cmd.ExecuteReaderAsync();
while (reader.Read())
{
var tempPatient = new Patient();
tempPatient.Id = Convert.ToInt32(reader["Id"]);
tempPatient.Name = reader["Name"].ToString();
tempPatient.Vorname = reader["Vorname"].ToString();
tempPatient.Strasse = reader["Strasse"].ToString();
tempPatient.Plz = Convert.ToInt32(reader["Plz"]);
tempPatient.Ort = reader["Ort"].ToString();
tempPatient.Geburtsdatum = Convert.ToDateTime(reader["Geburtsdatum"]);
//tempPatient.Geburtsdatum = (DateTime?)reader["Geburtsdatum"];
tempPatient.Geschlecht = Convert.ToString(reader["Geschlecht"]).ToUpper().Equals("M")
? GeschlechtType.M
: GeschlechtType.W;
tempPatient.PatientenNr = reader["PatientenNr"].ToString();
tempPatient.AhvNr = reader["AhvNr"].ToString();
tempPatient.VekaNr = reader["VekaNr"].ToString();
tempPatient.VersichertenNr = reader["VersichertenNr"].ToString();
tempPatient.Kanton = reader["Kanton"].ToString();
tempPatient.Kopie = Convert.ToBoolean(reader["Kopie"]);
tempPatient.VerguetungsArt = CastVerguetungsArt(reader["VerguetungsArt"]);
tempPatient.VertragsNr = reader["VertragsNr"].ToString();
patients.Add(tempPatient);
}
reader.Close();
return(patients);
}
catch (Exception ex)
{
throw new Exception($"Error occured while reading patient records from database.", ex);
}
finally
{
conn.Close();
}
}
public override async Task <DataTable> GetAsDataTable()
{
var query = $"SELECT * FROM [{this.SheetName}]";
using (var conn = new OleDbConnection(this.ConnStr)) {
conn.Open();
var cmd = new OleDbCommand(query, conn);
using (var dr = await cmd.ExecuteReaderAsync())
return(this.ReadAsDataTable(dr));
}
}
public async Task ExecuteSqlAsync(
string sql,
string connectionString,
Stream output)
{
using var connection = new OleDbConnection(connectionString);
using var command = new OleDbCommand(sql, connection);
connection.Open();
using var reader = await command.ExecuteReaderAsync();
WriteResultsToCsv(reader, output);
}
public override async Task <RopDocument[]> FillDate()
{
List <RopData> data = new List <RopData>();
var dd = new DownloadData();
var dataBytes = await dd.Data(rdBase.PathDocument);
var path = Path.Combine(Path.GetTempPath(), Path.GetTempFileName() + ".xls");
File.WriteAllBytes(path, dataBytes);
//var path = "D:\\a.xls";
var dt = new DataTable();
using (var m = new OleDbConnection())
{
m.ConnectionString = ExcelHelpers.BuildExcelConnectionString(path, true);
m.Open();
var query = @"Select * From [Sheet1$]";
using (var cmd = new OleDbCommand(query, m))
{
using (var dr = cmd.ExecuteReaderAsync().Result)
{
dt.Load(dr);
}
}
}
foreach (DataRow row in dt.Rows)
{
int nr;
var arr = row.ItemArray;
//if (arr[0] == null || string.IsNullOrWhiteSpace(arr[0].ToString()))
if (string.IsNullOrWhiteSpace(arr[0]?.ToString()))
{
continue;
}
if (!int.TryParse(arr[0].ToString(), out nr))
{
continue;
}
var numeJudet = arr[1].ToString().Trim().ToLower();
var judet = judetFinder.Find(numeJudet);
var rd = new RopData();
rd.Judet = judet;
rd.Valoare = int.Parse(arr[2].ToString());
rd.Oras = null;
data.Add(rd);
}
rdBase.Data = data.ToArray();
return(new [] { rdBase });
}
/// <summary>
/// Получение списка выплат и удержаний.
/// </summary>
private async Task <List <Pay> > GetPayAsync(OleDbConnection oleDbConnection, CancellationToken token)
{
token.ThrowIfCancellationRequested();
var sql = $"SELECT Snu_rn, Num, Code, Nick, Name FROM Zsnu";
var result = new List <Pay>();
using (OleDbCommand cmd = new OleDbCommand()
{
CommandText = "SELECT COUNT(*) FROM Zsnu", Connection = oleDbConnection
})
{
AddCount = 0;
int allRecords = Convert.ToInt32(await cmd.ExecuteScalarAsync());
LoadData?.Invoke(this, new Tuple <string, int, bool>("Выплаты и удержания:", allRecords, true));
}
using (OleDbCommand cmd = new OleDbCommand()
{
CommandText = sql, Connection = oleDbConnection
})
{
using (var reader = await cmd.ExecuteReaderAsync(token).ConfigureAwait(false))
{
while (await reader.ReadAsync(token).ConfigureAwait(false))
{
var rn = reader[0].ToString().Trim();
var number = Convert.ToInt32(reader[1]);
var mnemo = reader[2].ToString().Trim();
var abbreviatedName = reader[3].ToString().Trim();
var name = reader[4].ToString().Trim();
result.Add(new Pay()
{
Rn = rn,
Number = number,
Memo = mnemo,
AbbreviatedName = abbreviatedName,
Name = name
});
AddCount++;
ReaderEvent?.Invoke(this, AddCount);
}
}
}
LoadData?.Invoke(this, new Tuple <string, int, bool>(string.Empty, 0, false));
return(result);
}
public async Task <List <DataType> > GetDataCollectionAsync()
{
await connection.OpenAsync();
OleDbCommand command = connection.CreateCommand();
command.CommandText = CreateSqlQuery(SQLEnums.QueryTypes.SELECT, TableName);
DbDataReader asyncReader = await command.ExecuteReaderAsync();
List <DataType> result = await GetCollectionFromReaderAsync(asyncReader);
connection.Close();
return(result);
}
public async Task GetDataAsync(CancellationToken ct)
{
int CurrentRecord = 0;
using (OleDbConnection cn = new OleDbConnection(Builder.ConnectionString))
{
using (OleDbCommand cmd = new OleDbCommand("SELECT Identifier, FirstName, LastName FROM People;", cn))
{
await cn.OpenAsync();
using (DbDataReader reader = await cmd.ExecuteReaderAsync())
{
while (await reader.ReadAsync())
{
if (ct.IsCancellationRequested)
{
break;
}
int PrimaryKey = await reader.GetFieldValueAsync <int>(0);
Object[] ItemArray =
{
PrimaryKey,
await reader.GetFieldValueAsync <string>(1),
await reader.GetFieldValueAsync <string>(2)
};
Caller.Invoke(new EventHandler(delegate(object sender, EventArgs e)
{
dtPeople.Rows.Add(ItemArray);
if (Label != null)
{
if (Label.InvokeRequired == false)
{
Label.Text = string.Format("{0} of {1}", CurrentRecord, this.RecordCount);
}
}
}), new object[2] {
ItemArray, null
}
);
CurrentRecord += 1;
}
}
}
}
}
/// <summary>
/// The method to check if the record is already available
/// in the workgroup
/// </summary>
/// <param name="emp"></param>
/// <returns></returns>
private async Task <bool> CheckIfRecordExistAsync(Employee emp)
{
bool IsRecordExist = false;
Cmd.CommandText = "Select * from [Sheet1$] where EmpNo=@EmpNo";
var Reader = await Cmd.ExecuteReaderAsync();
if (Reader.HasRows)
{
IsRecordExist = true;
}
Reader.Close();
return(IsRecordExist);
}
