public Hashtable Read_Values(string[] Tag_Name, DateTime Time_Stamp)
{
Hashtable Result = new Hashtable();
string Tag_cond = "(FALSE"; // Makes it easier to add OR conditions.
foreach (string Tag in Tag_Name)
{
if (Sanitize(Tag))
{
Tag_cond += "\n OR NAME = '" + Tag + "'";
}
}
Tag_cond += ")";
Cmdr.CommandText =
@"SELECT
NAME, VALUE, STATUS
FROM
History
WHERE
" + Tag_cond + @"
AND TS > CAST('" + Time_Stamp.AddSeconds(-1).ToString("yyyy-MM-dd HH:mm:ss") + @"' AS TIMESTAMP FORMAT 'YYYY-MM-DD HH:MI:SS')
AND TS < CAST('" + Time_Stamp.AddSeconds(1).ToString("yyyy-MM-dd HH:mm:ss") + @"' AS TIMESTAMP FORMAT 'YYYY-MM-DD HH:MI:SS')
AND PERIOD = 000:0:01.0;
";
#if DEBUG
DateTime Start_Time = DateTime.Now;
#endif
System.Data.Odbc.OdbcDataReader DR = Cmdr.ExecuteReader(System.Data.CommandBehavior.SingleResult);
while (DR.Read())
{
if (DR.GetValue(2).ToString() == "0") // if status is good
{
Result.Add(DR.GetValue(0).ToString(), DR.GetValue(1));
}
}
DR.Close();
#if DEBUG
double Run_Time = (DateTime.Now - Start_Time).TotalMilliseconds;
Console.WriteLine("Read {0} tags from IP21 in {1} ms", Tag_Name.Length, Run_Time);
foreach (DictionaryEntry p in Result)
{
Console.WriteLine("{0}: {1}", p.Key, p.Value);
}
#endif
return(Result);
}
public static void LVLoad(System.Windows.Forms.ListView LV,
System.Data.Odbc.OdbcDataReader reader,
bool bUpdateHeaders)
{
int i = 0, nFields = 0, nRows = 0;
string s;
try
{
LV.BeginUpdate();
LV.Sorting = SortOrder.None;
LV.Items.Clear();
if (bUpdateHeaders == true)
{
LV.Columns.Clear();
nFields = reader.FieldCount;
for (i = 0; i < nFields; i++)
{
LV.Columns.Add(reader.GetName(i));
}
}
nRows = 0;
while (reader.Read())
{
s = reader.GetValue(0).ToString();
LV.Items.Add(s);
for (i = 1; i < nFields; i++)
{
s = reader.GetValue(i).ToString();
LV.Items[nRows].SubItems.Add(s);
}
nRows += 1;
}
LV.AutoResizeColumns(ColumnHeaderAutoResizeStyle.ColumnContent);
LV.EndUpdate();
}catch (Exception ex) { MessageBox.Show(ex.Message); }
finally
{
}
}
public XDocument formatTasks(OdbcDataReader r)
{
xdoc = new XDocument();
XElement title, notes, startdatetime, enddatetime, place, id;
XElement root = new XElement("message");
XAttribute type = new XAttribute("type", "tasks");
XAttribute errorStatus = new XAttribute("error_status", "0");
root.Add(type);
XElement tasks = new XElement("tasks");
while (r.Read())
{
XElement task = new XElement("task");
id = new XElement("id", r.GetValue(0).ToString());
task.Add(id);
title = new XElement("title", r.GetValue(1).ToString());
task.Add(title);
notes = new XElement("notes", r.GetValue(2).ToString());
task.Add(notes);
startdatetime = new XElement("startdatetime", r.GetValue(3).ToString());
task.Add(startdatetime);
enddatetime = new XElement("enddatetime", r.GetValue(4).ToString());
task.Add(enddatetime);
place = new XElement("place", r.GetValue(5).ToString());
task.Add(place);
tasks.Add(task);
}
root.Add(tasks);
root.Add(errorStatus);
xdoc.Add(root);
return xdoc;
}
private static void PrintResults(OdbcDataReader reader) {
for (int ii = 0; ii < reader.FieldCount; ii += 1) {
System.Console.Write("{0}{1}",
reader.GetName(ii),
ii + 1 < reader.FieldCount ? "\t" : "\n");
}
while (reader.Read()) {
for (int ii = 0; ii < reader.FieldCount; ii += 1) {
System.Console.Write("{0}{1}",
reader.GetValue(ii),
ii + 1 < reader.FieldCount ? "\t" : "\n");
}
}
}
private OpenIdMembershipUser GetUserFromReader(OdbcDataReader reader)
{
string username = reader.GetString(0);
string openId = reader.IsDBNull(1) ? string.Empty : reader.GetString(1);
string email = reader.IsDBNull(2) ? string.Empty : reader.GetString(2);
string passwordQuestion = "";
if (reader.GetValue(3) != DBNull.Value)
passwordQuestion = reader.GetString(3);
string comment = "";
if (reader.GetValue(4) != DBNull.Value)
comment = reader.GetString(4);
bool isApproved = reader.GetBoolean(5);
DateTime creationDate = reader.GetDateTime(6);
DateTime lastLoginDate = new DateTime();
if (reader.GetValue(7) != DBNull.Value)
lastLoginDate = reader.GetDateTime(7);
DateTime lastActivityDate = reader.GetDateTime(8);
DateTime lastPasswordChangedDate = reader.GetDateTime(9);
object providerUserKey = reader.GetValue(10);
bool isLockedOut = reader.GetBoolean(11);
DateTime lastLockedOutDate = new DateTime();
if (reader.GetValue(12) != DBNull.Value)
lastLockedOutDate = reader.GetDateTime(12);
OpenIdMembershipUser u = new OpenIdMembershipUser(
this.Name,
openId,
username,
providerUserKey,
email,
passwordQuestion,
comment,
isApproved,
isLockedOut,
creationDate,
lastLoginDate,
lastActivityDate,
lastPasswordChangedDate,
lastLockedOutDate);
return u;
}
/// <summary>
/// DisplayRecords: This function displays the records in the view if any.
/// </summary>
static void DisplayRecords()
{
Console.Write("\tRecord in getpatientbalance stored procedure");
try
{
reader = (OdbcDataReader)command.ExecuteReader();
// read the returned resultset
while (reader.Read())
{
/*
* This view consists the following fields:
* pat_id
* pat_0_30_days
* pat_31_60_days
* pat_61_90_days
* pat_91_120_days
* pat_121_plus_days
* last_pmt_date
* last_pmt_amt
* last_pmt_type
* last_pmt_desc
*/
Console.WriteLine("\npat_id:{0}\npat_0_30_days: {1}\npat_31_60_days: {2} \npat_61_90_days: {3}\npat_91_120_plus_days: {4} \npat_121_plus_days: {5}\nlast_pmt_date: {6} \nlast_pmt_amt: {7}\nlast_pmt_type: {8}\nlast_pmt_desc: {9}",
reader.GetValue(0), reader.GetValue(1), reader.GetValue(2), reader.GetValue(3), reader.GetValue(4), reader.GetValue(5), reader.GetValue(6), reader.GetValue(7), reader.GetValue(8), reader.GetValue(9));
}
// close the reader
reader.Close();
}
catch (Exception e)
{
ExceptionDisplay(e);
}
}
//
// GetUserFromReader
// A helper function that takes the current row from the OdbcDataReader
// and hydrates a MembershiUser from the values. Called by the
// MembershipUser.GetUser implementation.
//
private MembershipUser GetUserFromReader(OdbcDataReader reader)
{
object providerUserKey = reader.GetValue(0);
string username = reader.GetString(1);
string email = reader.GetString(2);
string passwordQuestion = "";
if (reader.GetValue(3) != DBNull.Value)
passwordQuestion = reader.GetString(3);
string comment = "";
if (reader.GetValue(4) != DBNull.Value)
comment = reader.GetString(4);
bool isApproved = reader.GetBoolean(5);
bool isLockedOut = reader.GetBoolean(6);
DateTime creationDate = reader.GetDateTime(7);
DateTime lastLoginDate = new DateTime();
if (reader.GetValue(8) != DBNull.Value)
lastLoginDate = reader.GetDateTime(8);
DateTime lastActivityDate = reader.GetDateTime(9);
DateTime lastPasswordChangedDate = reader.GetDateTime(10);
DateTime lastLockedOutDate = new DateTime();
if (reader.GetValue(11) != DBNull.Value)
lastLockedOutDate = reader.GetDateTime(11);
MembershipUser u = new MembershipUser((this.Name == null) ? "SimpleMysqlMembershipProvider" : this.Name,
username,
providerUserKey,
email,
passwordQuestion,
comment,
isApproved,
isLockedOut,
creationDate,
lastLoginDate,
lastActivityDate,
lastPasswordChangedDate,
lastLockedOutDate);
return u;
}
private string ReturnString(OdbcDataReader dr, int i)
{
if (dr.Read()) {
if (dr.IsDBNull(i)) {
return string.Empty;
} else {
string returnString = dr.GetValue(i).ToString();
return returnString;
}
}
return string.Empty;
}
/*
* Generates the create table command using the schema table, and
* runs it in the sql database.
*/
private bool CreateTableInDatabase(OdbcDataReader dtSchemaTable, string tableOwner, string tableName, string connectionString)
{
try
{
//***********************************
// déclaration des requêtes SQL *
//**********************************
string ctStr = "INSERT INTO [" + tableOwner + "].[" + tableName + "] (archiver,nom,prenom,date_naissance,tel_eleve,tel_parent,tier_temps,commentaire_sante,id_classe) VALUES ('0','";
string controlNom = " SELECT count(*) FROM ELEVES WHERE UPPER(nom) = '";
string updateId_classe = "UPDATE ELEVES SET archiver ='0', id_classe = '";
string reset_archive = "UPDATE ELEVES SET archiver = '1'";
int valRet = 0;
//***********************************************
// initialisation des attributs archiver à 1
//**********************************************
SqlConnection connreset = new SqlConnection(connectionString);
SqlCommand commandreset = connreset.CreateCommand();
commandreset.CommandText = reset_archive;
connreset.Open();
commandreset.ExecuteNonQuery();
connreset.Close();
//**********************************
// début de l'import *
//*********************************
while (dtSchemaTable.Read())
{
//************************************************************
// completion de la requête sql de verification (controlNom)*
//**********************************************************
controlNom += dtSchemaTable.GetValue(0).ToString();
controlNom += "' AND UPPER(prenom) ='" + dtSchemaTable.GetValue(1).ToString() + "';";
//*******************************************************************
//utilisation de la requête controlnom pour recuperer l'id_classe *
//*****************************************************************
SqlConnection conn = new SqlConnection(connectionString);
SqlCommand command1 = conn.CreateCommand();
command1.CommandText = controlNom;
conn.Open();
valRet = (int)command1.ExecuteScalar();
//****************************************
// si un eleve existe déjà on enleve l'archivage et on modifie sa classe
//****************************************
if (valRet > 0)
{
updateId_classe += dtSchemaTable.GetValue(7).ToString() + "' WHERE UPPER(nom) = '" + dtSchemaTable.GetValue(0).ToString() + "' AND UPPER(prenom) ='" + dtSchemaTable.GetValue(1).ToString() + "';";
SqlConnection connec = new SqlConnection(connectionString);
SqlCommand command2 = conn.CreateCommand();
command2.CommandText = updateId_classe;
connec.Open();
command2.ExecuteNonQuery();
}
//********************************************
// dans le cas contraire on ajoute l'élève *
//******************************************
else
{
for (int i = 0; i < 8; i++)
{
ctStr += dtSchemaTable.GetValue(i).ToString();
if (i < 7)
{
ctStr += "', '";
}
else
{
ctStr += "');";
}
}
//ajout de l'élève en base
SqlConnection conne = new SqlConnection(connectionString);
SqlCommand command = conne.CreateCommand();
command.CommandText = ctStr;
conne.Open();
command.ExecuteNonQuery();
conne.Close();
}
conn.Close();
// Réinitialisation des variables de requetes SQL
ctStr = "INSERT INTO [" + tableOwner + "].[" + tableName + "] (archiver,nom,prenom,date_naissance,tel_eleve,tel_parent,tier_temps,commentaire_sante,id_classe) VALUES ('0','";
controlNom = " SELECT count(*) FROM ELEVES WHERE UPPER(nom) = '";
updateId_classe = "UPDATE ELEVES SET archiver ='0', id_classe = '";
}
return true;
}
catch (Exception e)
{
MessageBox.Show(e.Message, "CreateTableInDatabase", MessageBoxButtons.OK, MessageBoxIcon.Error);
return false;
}
}
//
// GetProfileInfoFromReader
// Takes the current row from the OdbcDataReader
// and populates a ProfileInfo object from the values.
//
private ProfileInfo GetProfileInfoFromReader(OdbcDataReader reader)
{
string username = reader.GetString(0);
DateTime lastActivityDate = new DateTime();
if (reader.GetValue(1) != DBNull.Value)
lastActivityDate = reader.GetDateTime(1);
DateTime lastUpdatedDate = new DateTime();
if (reader.GetValue(2) != DBNull.Value)
lastUpdatedDate = reader.GetDateTime(2);
bool isAnonymous = reader.GetBoolean(3);
// ProfileInfo.Size not currently implemented.
ProfileInfo p = new ProfileInfo(username,
isAnonymous, lastActivityDate, lastUpdatedDate, 0);
return p;
}
public DataSet ConvertDataReaderToDataSet(System.Data.Odbc.OdbcDataReader reader)
{
DataSet dataSet = new DataSet();
do
{
// Create data table in runtime
DataTable schemaTable = reader.GetSchemaTable();
DataTable dataTable = new DataTable();
if (schemaTable != null)
{
DataColumn column1 = new DataColumn("id", typeof(int));
dataTable.Columns.Add(column1);
DataColumn column2 = new DataColumn("dato", typeof(DateTime));
dataTable.Columns.Add(column2);
DataColumn column3 = new DataColumn("editor", typeof(string));
dataTable.Columns.Add(column3);
DataColumn column4 = new DataColumn("origin", typeof(int));
dataTable.Columns.Add(column4);
DataColumn column5 = new DataColumn("medarbejderid", typeof(string));
dataTable.Columns.Add(column5);
DataColumn column6 = new DataColumn("jobid", typeof(int));
dataTable.Columns.Add(column6);
DataColumn column7 = new DataColumn("aktnavn", typeof(string));
dataTable.Columns.Add(column7);
DataColumn column8 = new DataColumn("timer", typeof(double));
dataTable.Columns.Add(column8);
DataColumn column9 = new DataColumn("tdato", typeof(DateTime));
dataTable.Columns.Add(column9);
DataColumn column10 = new DataColumn("lto", typeof(string));
dataTable.Columns.Add(column10);
DataColumn column11 = new DataColumn("timerkom", typeof(string));
dataTable.Columns.Add(column11);
dataSet.Tables.Add(dataTable);
// Fill the data table from reader data
while (reader.Read())
{
DataRow dataRow = dataTable.NewRow();
dataRow["id"] = reader.GetValue(0);
dataRow["dato"] = reader.GetValue(1);
dataRow["editor"] = reader.GetValue(2);
dataRow["origin"] = reader.GetValue(3);
dataRow["medarbejderid"] = reader.GetValue(4);
dataRow["jobid"] = reader.GetValue(5);
dataRow["aktnavn"] = reader.GetValue(6);
dataRow["timer"] = reader.GetValue(7);
dataRow["tdato"] = reader.GetValue(8);
dataRow["lto"] = reader.GetValue(9); //10 Overfort omited here
dataRow["timerkom"] = reader.GetValue(10); //11
dataTable.Rows.Add(dataRow);
}
}
else
{
// No records were returned
DataColumn column = new DataColumn("RowsAffected");
dataTable.Columns.Add(column);
dataSet.Tables.Add(dataTable);
DataRow dataRow = dataTable.NewRow();
dataRow[0] = reader.RecordsAffected;
dataTable.Rows.Add(dataRow);
}
}while (reader.NextResult());
return(dataSet);
}
public string loginProperty(string x)
{
openConnection();
string value = "";
rder = new OdbcCommand("SELECT IDENTITY FROM UserIds WHERE USERID='"+x+"'",con);
dr = rder.ExecuteReader();
while (dr.Read())
{
value = dr.GetValue(0).ToString();
}
con.Close();
Console.WriteLine("Value :" + value);
return value;
}
